Ringkasan
Untuk menghitung rata-rata bergerak atau bergulir, Anda dapat menggunakan rumus sederhana berdasarkan fungsi AVERAGE dengan referensi relatif. Pada contoh yang ditampilkan, rumus di E7 adalah:
=AVERAGE(C5:C7)
Saat rumus disalin, rumus menghitung rata-rata bergerak 3 hari berdasarkan nilai penjualan untuk hari ini dan dua hari sebelumnya.
Di bawah ini adalah opsi yang lebih fleksibel berdasarkan fungsi OFFSET yang menangani periode variabel.
Tentang rata-rata bergerak
Rata-rata bergerak (juga disebut rata-rata bergulir) adalah rata-rata berdasarkan subkumpulan data pada interval tertentu. Menghitung rata-rata pada interval tertentu memperlancar data dengan mengurangi dampak fluktuasi acak. Ini memudahkan untuk melihat tren secara keseluruhan, terutama dalam grafik. Semakin besar interval yang digunakan untuk menghitung rata-rata bergerak, semakin banyak pemulusan yang terjadi, karena semakin banyak titik data yang disertakan dalam setiap rata-rata yang dihitung.
Penjelasan
Rumus yang ditampilkan dalam contoh semuanya menggunakan fungsi AVERAGE dengan referensi relatif yang disiapkan untuk setiap interval tertentu. Rata-rata pergerakan 3 hari di E7 dihitung dengan memberi makan RATA-RATA kisaran yang mencakup hari ini dan dua hari sebelumnya seperti ini:
=AVERAGE(C5:C7) // 3-day average
Rata-rata 5 hari dan 7 hari dihitung dengan cara yang sama. Dalam setiap kasus, rentang yang disediakan untuk RATA-RATA diperbesar untuk menyertakan jumlah hari yang diperlukan:
=AVERAGE(C5:C7) // 5-day average =AVERAGE(C5:C11) // 7-day average
Semua rumus menggunakan referensi relatif untuk rentang yang disediakan ke fungsi AVERAGE. Saat rumus disalin ke bawah kolom, rentang berubah di setiap baris untuk menyertakan nilai yang diperlukan untuk setiap rata-rata.
Saat nilai diplot dalam bagan garis, efek perataannya jelas:
Data tidak cukup
Jika Anda memulai rumus di baris pertama tabel, beberapa rumus pertama tidak akan memiliki cukup data untuk menghitung rata-rata lengkap, karena rentang akan meluas di atas baris pertama data:
Ini mungkin atau mungkin tidak menjadi masalah, tergantung pada struktur lembar kerja, dan apakah penting bahwa semua rata-rata didasarkan pada jumlah nilai yang sama. Fungsi AVERAGE secara otomatis akan mengabaikan nilai teks dan sel kosong, sehingga akan terus menghitung rata-rata dengan nilai yang lebih sedikit. Inilah mengapa "bekerja" di E5 dan E6.
Salah satu cara untuk menunjukkan dengan jelas data yang tidak mencukupi adalah dengan memeriksa nomor baris saat ini dan membatalkan dengan #NA jika nilai kurang dari n. Misalnya, untuk rata-rata 3 hari, Anda dapat menggunakan:
=IF(ROW()-ROW($C$5)+1<3,NA(),AVERAGE(C3:C5))
Bagian pertama rumus hanya menghasilkan nomor baris "dinormalisasi", dimulai dengan 1:
ROW()-ROW($C$5)+1 // relative row number
Di baris 5 hasilnya 1, baris 6 hasilnya 2, dan seterusnya.
Ketika nomor baris saat ini kurang dari 3, rumus mengembalikan # N / A. Jika tidak, rumus mengembalikan rata-rata bergerak seperti sebelumnya. Ini meniru perilaku Moving Average versi Analysis Toolpak, yang mengeluarkan # N / A hingga periode lengkap pertama tercapai.
Namun, seiring bertambahnya jumlah periode, Anda pada akhirnya akan kehabisan baris di atas data dan tidak dapat memasukkan kisaran yang diperlukan di dalam AVERAGE. Misalnya, Anda tidak dapat menyiapkan rata-rata 7 hari yang bergerak dengan lembar kerja seperti yang diperlihatkan, karena Anda tidak bisa memasukkan rentang yang memanjang 6 baris di atas C5.
Periode variabel dengan OFFSET
Cara yang lebih fleksibel untuk menghitung rata-rata bergerak adalah dengan fungsi OFFSET. OFFSET dapat membuat rentang dinamis, yang berarti kita dapat menyiapkan rumus di mana jumlah periode adalah variabel. Bentuk umumnya adalah:
=AVERAGE(OFFSET(A1,0,0,-n,1))
dengan n adalah jumlah periode yang akan disertakan di setiap rata-rata. Seperti di atas, OFFSET mengembalikan rentang yang dilewatkan ke fungsi AVERAGE. Di bawah ini Anda dapat melihat rumus ini bekerja, di mana "n" adalah rentang bernama E2. Dimulai dari sel C5, OFFSET membuat rentang yang meluas ke baris sebelumnya. Ini dilakukan dengan menggunakan ketinggian yang sama dengan n negatif. Saat E5 diubah ke angka lain, rata-rata bergerak dihitung ulang di semua baris:
Rumus dalam E5, disalin, adalah:
=AVERAGE(OFFSET(C5,0,0,-n,1))
Seperti rumus asli di atas, versi dengan OFFSET juga akan memiliki masalah data yang tidak mencukupi di beberapa baris pertama, tergantung pada berapa banyak periode yang diberikan dalam E5.
Dalam contoh yang ditampilkan, penghitungan rata-rata berhasil karena fungsi AVERAGE secara otomatis mengabaikan nilai teks dan sel kosong, dan tidak ada nilai numerik lain di atas C5. Jadi, meskipun rentang yang diteruskan ke RATA-RATA di E5 adalah C1: C5, hanya ada satu nilai untuk rata-rata, 100. Namun, seiring bertambahnya periode, OFFSET akan terus membuat rentang yang melampaui awal data, yang pada akhirnya mencapai bagian atas lembar kerja dan mengembalikan kesalahan #REF.
Salah satu solusinya adalah "membatasi" ukuran rentang ke jumlah titik data yang tersedia. Ini dapat dilakukan dengan menggunakan fungsi MIN untuk membatasi jumlah yang digunakan untuk ketinggian seperti yang terlihat di bawah ini:
=AVERAGE(OFFSET(C5,0,0,-(MIN(ROW()-ROW($C$5)+1,n)),1))
Ini terlihat cukup menakutkan, tetapi sebenarnya cukup sederhana. Kami membatasi ketinggian yang dilewatkan ke OFFSET dengan fungsi MIN:
MIN(ROW()-ROW($C$5)+1,n)
Di dalam MIN, nilai pertama adalah nomor baris relatif, dihitung dengan:
ROW()-ROW($C$5)+1 // relative row number… 1,2,3, etc.
Nilai kedua yang diberikan untuk MIN adalah jumlah periode, n. Ketika nomor baris relatif kurang dari n, MIN mengembalikan nomor baris saat ini ke OFFSET untuk ketinggian. Ketika nomor baris lebih besar dari n, MIN mengembalikan n. Dengan kata lain, MIN hanya mengembalikan nilai yang lebih kecil dari dua nilai.
Fitur bagus dari opsi OFFSET adalah n dapat dengan mudah diubah. Jika kita mengubah n menjadi 7 dan memplot hasilnya, kita mendapatkan grafik seperti ini:
Catatan: Sebuah kekhasan dengan rumus OFFSET di atas adalah bahwa rumus tersebut tidak akan berfungsi di Google Spreadsheet, karena fungsi OFFSET di Spreadsheet tidak akan mengizinkan nilai negatif untuk tinggi atau lebar. Spreadsheet terlampir memiliki rumus solusi untuk lembar Google.
