Formula generik
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)),ROW(data), data)))
Ringkasan
Untuk rata-rata 3 nilai numerik terakhir dalam julat, Anda dapat menggunakan rumus array berdasarkan kombinasi fungsi untuk memasukkan nilai angka n terakhir ke fungsi RATA-RATA. Dalam contoh yang ditunjukkan, formula dalam D6 adalah:
(=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(data),ROW(data)),(1,2,3)), ROW(data), data)))
di mana "data" adalah julat bernama B5: B13.
Catatan: ini adalah formula larik, dan mesti dimasukkan dengan kawalan + shift + enter.
Penjelasan
Fungsi RATA-RATA akan mengira rata-rata angka yang disajikan dalam array, jadi hampir semua kerja dalam formula ini adalah menghasilkan array dari 3 nilai angka terakhir dalam julat. Berfungsi dari dalam ke luar, fungsi IF digunakan untuk "menyaring" nilai angka:
IF(ISNUMBER(data),ROW(data))
Fungsi ISNUMBER mengembalikan TRUE untuk nilai angka, dan FALSE untuk nilai lain (termasuk kosong), dan fungsi ROW mengembalikan nombor baris, jadi hasil operasi ini adalah nombor baris array yang sesuai dengan entri angka:
(5;6;FALSE;8;9;10;FALSE;12;13)
Susunan ini masuk ke fungsi BESAR dengan pemalar array (1,2,3) untuk k. LARGE secara automatik mengabaikan nilai FALSE dan mengembalikan array dengan 3 nombor terbesar, yang sesuai dengan 3 baris terakhir dengan nilai numerik:
(13,12,10)
Susunan ini masuk ke fungsi LOOKUP sebagai nilai carian. Susunan carian disediakan oleh fungsi ROW, dan susunan hasilnya adalah julat bernama "data":
LOOKUP((13,12,10), ROW(data), data))
LOOKUP kemudian mengembalikan array yang mengandungi nilai yang sesuai dalam "data", yang dimasukkan ke dalam RATA-RATA:
=AVERAGE((100,92,90))
Mengendalikan lebih sedikit nilai
Sekiranya bilangan nilai numerik turun di bawah 3, formula ini akan mengembalikan ralat #NUM kerana LARGE tidak akan dapat mengembalikan 3 nilai seperti yang diminta. Salah satu cara untuk mengatasinya adalah dengan mengganti pemalar susunan keras (1,2,3) dengan tatasusunan dinamik yang dibuat menggunakan INDIRECT seperti ini:
ROW(INDIRECT("1:"&MIN(3,COUNT(data))))
Di sini, MIN digunakan untuk menetapkan had atas array menjadi 3 atau jumlah sebenar nilai numerik, mana yang lebih kecil.
Catatan: Saya menemui pendekatan pintar ini di chandoo.org, dalam jawapan oleh Sajan untuk soalan serupa.
