Formula umum
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Ringkasan
Untuk menghitung rata-rata 3 nilai numerik terakhir dalam suatu rentang, Anda dapat menggunakan rumus larik berdasarkan kombinasi fungsi untuk memasukkan n nilai numerik terakhir ke dalam fungsi AVERAGE. Dalam contoh yang diperlihatkan, rumus di D6 adalah:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
di mana "data" adalah rentang bernama B5: B13.
Catatan: ini adalah rumus array, dan harus dimasukkan dengan control + shift + enter.
Penjelasan
Fungsi AVERAGE akan menghitung rata-rata angka yang disajikan dalam larik, jadi hampir semua pekerjaan dalam rumus ini adalah menghasilkan larik dari 3 nilai numerik terakhir dalam satu rentang. Bekerja dari dalam ke luar, fungsi IF digunakan untuk "memfilter" nilai numerik:
IF(ISNUMBER(data),ROW(data))
Fungsi ISNUMBER mengembalikan TRUE untuk nilai numerik, dan FALSE untuk nilai lain (termasuk kosong), dan fungsi ROW mengembalikan nomor baris, sehingga hasil dari operasi ini adalah nomor baris array yang sesuai dengan entri numerik:
(5;6;FALSE;8;9;10;FALSE;12;13)
Array ini masuk ke fungsi LARGE dengan konstanta array (1,2,3) untuk k. LARGE secara otomatis mengabaikan nilai FALSE dan mengembalikan larik dengan 3 angka terbesar, yang sesuai dengan 3 baris terakhir dengan nilai numerik:
(13,12,10)
Array ini masuk ke fungsi LOOKUP sebagai nilai pencarian. Array pencarian disediakan oleh fungsi ROW, dan larik hasil adalah rentang bernama "data":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP kemudian mengembalikan larik yang berisi nilai terkait dalam "data", yang dimasukkan ke dalam AVERAGE:
=AVERAGE((100,92,90))
Menangani lebih sedikit nilai
Jika jumlah nilai numerik turun di bawah 3, rumus ini akan mengembalikan kesalahan #NUM karena LARGE tidak dapat mengembalikan 3 nilai seperti yang diminta. Salah satu cara untuk menangani ini adalah dengan mengganti konstanta array hard-coded (1,2,3) dengan array dinamis yang dibuat menggunakan INDIRECT seperti ini:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Di sini, MIN digunakan untuk menetapkan batas atas larik menjadi 3 atau jumlah sebenarnya dari nilai numerik, mana yang lebih kecil.
Catatan: Saya menemukan pendekatan pintar ini di chandoo.org, dalam jawaban Sajan untuk pertanyaan serupa.
