Formula generik
=AVERAGE(OFFSET(A1,COUNT(A:A),0,-N))
Ringkasan
Untuk rata-rata 5 titik data terakhir, anda boleh menggunakan fungsi AVERAGE bersama dengan fungsi COUNT dan OFFSET. Anda boleh menggunakan pendekatan ini untuk rata-rata titik data N terakhir: 3 hari terakhir, 6 pengukuran terakhir, dll. Dalam contoh yang ditunjukkan, formula dalam F6 adalah:
=AVERAGE(OFFSET(C3,COUNT(C:C),0,-5))
Catatan: nilai negatif untuk ketinggian tidak akan berfungsi di helaian Google. Lihat di bawah untuk maklumat lebih lanjut.
Penjelasan
Fungsi OFFSET dapat digunakan untuk membangun rentang segi empat tepat dinamik berdasarkan rujukan awal dan diberi baris, lajur, tinggi, dan lebar. Argumen baris dan lajur berfungsi seperti "offset" dari rujukan awal. Argumen tinggi dan lebar (kedua-duanya pilihan), menentukan berapa baris dan lajur yang merangkumi julat akhir. Untuk contoh ini, OFFSET dikonfigurasi seperti ini:
- rujukan = C3
- baris = COUNT (A: A)
- cols = 0
- tinggi = -5
- lebar = (tidak disediakan)
Rujukan permulaan disediakan sebagai C3 sel di atas data sebenar. Oleh kerana kami mahu OFFSET mengembalikan julat yang berasal dari entri terakhir di lajur C, kami menggunakan fungsi COUNT untuk mengira semua nilai di lajur C untuk mendapatkan pengimbangan baris yang diperlukan. COUNT hanya mengira nilai berangka, jadi tajuk di baris 3 secara automatik diabaikan.
Dengan 8 nilai angka di lajur C, formula OFFSET memutuskan untuk:
OFFSET(C3,8,0,-5)
Dengan nilai-nilai ini, OFFSET bermula pada C3, mengimbangi 8 baris ke C11, kemudian menggunakan -5 untuk memperluas julat segi empat tepat ke atas "ke belakang" 5 baris untuk membuat julat C7: C11.
Akhirnya, OFFSET mengembalikan julat C7: C11 ke fungsi AVERAGE, yang mengira nilai purata dalam julat itu.
Excel vs Helaian
Satu keanehan dengan formula ini ialah ia tidak akan berfungsi dengan Helaian Google, kerana fungsi OFFSET dalam Helaian tidak akan membenarkan nilai negatif untuk argumen tinggi atau lebar. Dokumentasi Excel juga menyatakan tinggi atau lebar tidak boleh negatif tetapi nampaknya nilai negatif telah berfungsi dengan baik di Excel sejak tahun 1990-an.
Untuk mengelakkan nilai tinggi atau lebar negatif, anda boleh menggunakan formula seperti ini:
=OFFSET(C4,COUNT(C:C)-5,0,5)
Notis C4 adalah rujukan permulaan dalam kes ini. Bentuk umum adalah:
=AVERAGE(OFFSET(A1,COUNT(A:A)-N,0,N))
di mana A1 adalah sel pertama dalam nombor yang anda mahu purata.
