Formula umum
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Ringkasan
Untuk meratakan 5 nilai data terakhir dalam rentang kolom, Anda dapat menggunakan fungsi AVERAGE bersama dengan fungsi COUNT dan OFFSET. Dalam contoh yang ditampilkan, rumus di F6 adalah:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Penjelasan
Fungsi OFFSET dapat digunakan untuk membangun rentang dinamis menggunakan sel awal, dan baris, kolom, tinggi, dan lebar tertentu.
Argumen baris dan kolom berfungsi seperti "offset" dari referensi awal. Argumen tinggi dan lebar, keduanya opsional, menentukan berapa banyak baris dan kolom yang termasuk dalam rentang akhir. Kami ingin OFFSET mengembalikan rentang yang dimulai pada entri terakhir dan memperluas "mundur" sehingga kami menyediakan argumen sebagai berikut:
referensi - referensi awal adalah D5 - sel langsung di sebelah kanan rumus, dan sel pertama dalam rentang nilai yang kita kerjakan.
baris - kami menggunakan 0 untuk argumen baris, karena kami ingin tetap di baris yang sama.
kolom - untuk argumen kolom, kami menggunakan fungsi COUNT untuk menghitung semua nilai dalam rentang, lalu mengurangi 5. Ini menggeser awal rentang 5 kolom ke kiri.
tinggi - kami menggunakan 1 karena kami ingin rentang 1 baris sebagai hasil akhir.
lebar - kami menggunakan 5, karena kami ingin rentang akhir dengan 5 kolom.
Untuk rumus di C5, OFFSET mengembalikan kisaran akhir F5: J5. Ini masuk ke fungsi AVERAGE yang mengembalikan rata-rata dari 5 nilai dalam rentang tersebut
Kurang dari 5 nilai
Jika ada kurang dari 5 nilai, rumus akan mengembalikan kesalahan referensi melingkar, karena rentang akan diperpanjang kembali ke sel yang berisi rumus. Untuk mencegah kesalahan ini, Anda dapat mengadaptasi rumus sebagai berikut:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Di sini kami menggunakan fungsi MIN untuk "menangkap" situasi di mana ada kurang dari 5 nilai, dan menggunakan hitungan aktual bila ada.
