Formula generik
=AVERAGE(OFFSET(firstcell,0,COUNT(rng)-N,1,N))
Ringkasan
Untuk rata-rata 5 nilai data terakhir dalam julat lajur, anda boleh menggunakan fungsi RATA-RATA bersama dengan fungsi COUNT dan OFFSET. Dalam contoh yang ditunjukkan, formula dalam F6 adalah:
=AVERAGE(OFFSET(D5,0,COUNT(D5:J5)-5,1,5))
Penjelasan
Fungsi OFFSET dapat digunakan untuk membangun rentang dinamis menggunakan sel permulaan, dan diberi baris, lajur, tinggi, dan lebar.
Argumen baris dan lajur berfungsi seperti "offset" dari rujukan awal. Argumen tinggi dan lebar, baik pilihan, menentukan berapa baris dan lajur yang merangkumi julat akhir. Kami mahu OFFSET mengembalikan julat yang bermula pada entri terakhir dan mengembang "ke belakang" jadi kami memberikan argumen seperti berikut:
rujukan - rujukan permulaan adalah D5 - sel tepat di sebelah kanan formula, dan sel pertama dalam julat nilai yang kami bekerjasama.
baris - kami menggunakan 0 untuk argumen baris, kerana kami mahu kekal dalam baris yang sama.
lajur - untuk argumen lajur, kami menggunakan fungsi COUNT untuk menghitung semua nilai dalam julat, kemudian tolak 5. Ini mengalihkan permulaan julat 5 lajur ke kiri.
tinggi - kita menggunakan 1 kerana kita mahukan julat 1 baris sebagai hasil akhir.
lebar - kami menggunakan 5, kerana kami mahukan julat terakhir dengan 5 lajur.
Untuk formula di C5, OFFSET mengembalikan julat terakhir F5: J5. Ini masuk ke fungsi AVERAGE yang mengembalikan purata 5 nilai dalam julat
Kurang daripada 5 nilai
Sekiranya terdapat kurang dari 5 nilai, formula akan mengembalikan ralat rujukan bulat, kerana julat akan meluas kembali ke sel yang berisi formula. Untuk mengelakkan ralat ini, anda boleh menyesuaikan formula seperti berikut:
=AVERAGE(OFFSET(first,0,COUNT(rng)-MIN(N,COUNT(rng)),1,MIN(N,COUNT(rng))))
Di sini kita menggunakan fungsi MIN untuk "menangkap" situasi di mana terdapat kurang dari 5 nilai, dan menggunakan kiraan sebenarnya ketika ada.
