Rumus Excel Menghitung Rata – Rata Perbulan

Pernakah anda memiliki data tanggal dan nilai tertentu dan ingin menghitung rata – rata perbulan nya? Melakukannya secara manual akan memakan waktu yang lama. Jika kita memiliki range tanggal tertentu dan kita ingin menghituang nilai rata – rata perbulan kita dapat memanfaatkan Rumus Excel berikut ini

Rumus umum

=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))

Kesimpulan

Untuk merata-ratakan perbulan, anda dapat menggunakan rumus dari AVERAGEIFS, dengan bantuan dari fungsi EOMONTH. Pada contoh ditunjukan rumus pada f4:

=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))

Rumus ini menggunakan rantang yang disebut “amounts” (D5:D104) dan “date” (C5:C104).

Penjelasan

Fungsi AVERAGEIFS dapat menghitung rata-rata rentang berdasarkan beberapa kriteria. Dalam kasus ini, kita mengonfigurasi AVERAGEIFS ke jumlah rata-rata berdasarkan bulan menggunakan dua kriteria: (1) mencocokkan tanggal yang lebih besar dari atau sama dengan hari pertama setiap bulan, (2) mencocokkan tanggal kurang dari atau sama dengan hari terakhir bulan itu. Jika kita memasukkan tanggal untuk Januari 2016 ke dalam rumus menggunakan fungsi DATE, akan terlihat seperti ini.

=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))

Bagaimana jika kita tidak ingin tanggal yang tetap? kami ingin Excel membuat tanggal ini untuk kami. Biasanya  ini cukup susah, karena jika Anda menambahkan nama bulan sebagai teks (contoh “Januari”, “Februari”, “Maret”, dll.) Di kolom F anda harus membuat tanggal yang dapat anda gunakan untuk kriteria .

Namun, dalam kasus ini, kita menggunakan trik sederhana untuk mempermudah: Di kolom F, daripada mengetik nama bulan, kami menambahkan tanggal sebenarnya untuk tanggal pertama setiap bulan (1/1/2016, 2/1/2016, 3 / 1/2016, dll.), Dan menggunakan format tanggal khusus (“mmm”) untuk menampilkan nama bulan.

Cara Ini membuat mudah untuk membangun kriteria yang kita butuhkan untuk AVERAGEIFS. Untuk mencocokkan tanggal yang lebih besar dari atau sama dengan tanggal pertama setiap bulan, kita menggunakan:

">="&E4

EOMONTH secara otomatis mengembalikan hari terakhir di bulan yang sama karena kita memberikan nol untuk argumen bulan.

Catatan: penggabungan dengan ampersand (&) diperlukan saat membangun kriteria berdasarkan referensi sel.

Solusi Tabel Pivot

Tabel pivot adalah solusi yang baik saat anda perlu meringkas data menurut tahun, bulan, kuartal, dan seterusnya, karena tabel pivot menyediakan kontrol untuk pengelompokan otomatis menurut tanggal. Untuk perbandingan berdampingan dari rumus vs. tabel pivot, lihat video ini: Mengapa tabel pivot.