Ringkasan
Untuk mengira purata bergerak atau bergulir, anda boleh menggunakan formula mudah berdasarkan fungsi AVERAGE dengan rujukan relatif. Dalam contoh yang ditunjukkan, formula di E7 adalah:
=AVERAGE(C5:C7)
Semasa formula disalin ke bawah, ia mengira purata bergerak 3 hari berdasarkan nilai penjualan untuk hari semasa dan dua hari sebelumnya.
Di bawah ini adalah pilihan yang lebih fleksibel berdasarkan fungsi OFFSET yang menangani tempoh berubah.
Mengenai purata bergerak
Purata bergerak (juga disebut purata bergulir) adalah purata berdasarkan subkumpulan data pada selang masa yang ditentukan. Mengira purata pada selang waktu tertentu melicinkan data dengan mengurangkan kesan turun naik secara rawak. Ini menjadikannya lebih mudah untuk melihat trend keseluruhan, terutamanya dalam carta. Semakin besar selang yang digunakan untuk mengira purata bergerak, semakin lancar yang berlaku, kerana lebih banyak titik data dimasukkan dalam setiap rata-rata yang dikira.
Penjelasan
Rumus yang ditunjukkan dalam contoh semuanya menggunakan fungsi RATA-RATA dengan susunan rujukan relatif untuk setiap selang waktu tertentu. Purata bergerak 3 hari di E7 dikira dengan memberi nilai rata-rata yang merangkumi hari semasa dan dua hari sebelumnya seperti ini:
=AVERAGE(C5:C7) // 3-day average
Purata 5-hari dan 7-hari dikira dengan cara yang sama. Dalam setiap kes, rentang yang disediakan untuk AVERAGE diperbesar untuk memasukkan jumlah hari yang diperlukan:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Semua formula menggunakan rujukan relatif untuk julat yang dibekalkan ke fungsi AVERAGE. Ketika formula disalin ke bawah lajur, julat berubah pada setiap baris untuk memasukkan nilai yang diperlukan untuk setiap rata-rata.
Apabila nilainya dicantumkan dalam carta garis, kesan melicinkan jelas:
Data tidak mencukupi
Sekiranya anda memulakan formula di baris pertama jadual, beberapa formula pertama tidak akan mempunyai data yang mencukupi untuk mengira purata yang lengkap, kerana julat akan meluas di atas baris data pertama:
Ini mungkin atau tidak menjadi masalah, bergantung pada struktur lembaran kerja, dan sama ada penting bahawa semua purata didasarkan pada jumlah nilai yang sama. Fungsi AVERAGE secara automatik akan mengabaikan nilai teks dan sel kosong, jadi ia akan terus mengira rata-rata dengan nilai yang lebih sedikit. Inilah sebabnya mengapa ia "berfungsi" pada E5 dan E6.
Salah satu cara untuk menunjukkan data yang tidak mencukupi adalah dengan memeriksa nombor baris semasa dan membatalkan dengan #NA apabila terdapat nilai kurang dari n. Sebagai contoh, untuk purata 3 hari, anda boleh menggunakan:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Bahagian pertama formula hanya menghasilkan nombor baris "dinormalisasi", bermula dengan 1:
ROW()-ROW($C$5)+1 // relative row number
Pada baris 5, hasilnya adalah 1, di baris 6 hasilnya adalah 2, dan seterusnya.
Apabila nombor baris semasa kurang dari 3, formula mengembalikan # N / A. Jika tidak, formula mengembalikan purata bergerak seperti sebelumnya. Ini meniru tingkah laku Analisis Toolpak versi Moving Average, yang menghasilkan # N / A sehingga tempoh lengkap pertama tercapai.
Namun, apabila bilangan tempoh meningkat, anda akhirnya akan kehabisan baris di atas data dan tidak dapat memasukkan julat yang diperlukan di dalam RATA-RATA. Sebagai contoh, anda tidak dapat menetapkan rata-rata 7 hari yang bergerak dengan lembaran kerja seperti yang ditunjukkan, kerana anda tidak dapat memasukkan julat yang memanjang 6 baris di atas C5.
Tempoh berubah dengan OFFSET
Kaedah yang lebih fleksibel untuk mengira purata bergerak adalah dengan fungsi OFFSET. OFFSET dapat membuat julat dinamik, yang bermaksud kita dapat menyiapkan formula di mana bilangan tempoh berubah-ubah. Bentuk umum adalah:
=AVERAGE(OFFSET(A1,0,0,-n,1))
di mana n adalah bilangan tempoh yang perlu disertakan dalam setiap purata Seperti di atas, OFFSET mengembalikan julat yang diteruskan ke fungsi RATA-RATA. Di bawah ini anda dapat melihat formula ini dalam tindakan, di mana "n" adalah julat bernama E2. Bermula di sel C5, OFFSET membina julat yang meluas ke baris sebelumnya. Ini dicapai dengan menggunakan ketinggian sama dengan n negatif. Apabila E5 ditukar menjadi nombor lain, purata bergerak dikira semula pada semua baris:
Rumus dalam E5, disalin, adalah:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Seperti formula asal di atas, versi dengan OFFSET juga akan mempunyai masalah data yang tidak mencukupi dalam beberapa baris pertama, bergantung pada berapa tempoh yang diberikan dalam E5.
Dalam contoh yang ditunjukkan, rata-rata berjaya dikira kerana fungsi AVERAGE secara automatik mengabaikan nilai teks dan sel kosong, dan tidak ada nilai angka lain di atas C5. Jadi, sementara julat yang masuk ke RATA-RATA di E5 adalah C1: C5, hanya ada satu nilai menjadi rata-rata, 100. Namun, ketika tempoh meningkat, OFFSET akan terus membuat julat yang meluas di atas permulaan data, akhirnya mengalami bahagian atas lembaran kerja dan mengembalikan ralat #REF.
Salah satu penyelesaiannya adalah dengan "membatasi" ukuran julat hingga jumlah titik data yang ada. Ini dapat dilakukan dengan menggunakan fungsi MIN untuk membatasi jumlah yang digunakan untuk ketinggian seperti yang terlihat di bawah:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Ini kelihatan sangat menakutkan, tetapi sebenarnya agak sederhana. Kami mengehadkan ketinggian yang dilewatkan ke OFFSET dengan fungsi MIN:
MIN(ROW()-ROW($C$5)+1,n)
Di dalam MIN, nilai pertama adalah nombor baris relatif, dikira dengan:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Nilai kedua yang diberikan kepada MIN adalah bilangan tempoh, n. Apabila nombor baris relatif kurang dari n, MIN mengembalikan nombor baris semasa ke OFFSET untuk ketinggian. Apabila nombor baris lebih besar daripada n, MIN mengembalikan n. Dengan kata lain, MIN hanya mengembalikan yang lebih kecil dari dua nilai.
Ciri bagus dari pilihan OFFSET ialah n dapat diubah dengan mudah. Sekiranya kita menukar n ke 7 dan memplot hasilnya, kita mendapat carta seperti ini:
Catatan: Satu kebiasaan dengan formula OFFSET di atas adalah bahawa ia tidak akan berfungsi di Helaian Google, kerana fungsi OFFSET dalam Helaian tidak akan membenarkan nilai negatif untuk tinggi atau lebar. Spreadsheet yang dilampirkan mempunyai formula penyelesaian untuk helaian Google.
