Rumus Excel Mencari Rata-Rata Berdasarkan Bulan

by

rumus excel rata-rata berdasarkan bulan

Rumus Umum

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

Penjelasan

Untuk mencari rumus Excel rata-rata per bulan, dapat menggunakan rumus berdasarkan fungsi AVERAGEIFS, dengan bantuan dari fungsi EOMONTH. Dalam contoh yang ditunjukkan, rumus dalam F4 adalah:

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

Rumus ini menggunakan sebutan jarak “amounts” (D5:D104) dan “dates” (C5:C104)

Cara kerja Rumus

Fungsi AVERAGEIFS dapat merata-ratakan jarak berdasarkan beberapa kriteria. Dalam hal ini, diterapkan AVERAGEIFS ke rumus Excel jumlah rata-rata per bulan menggunakan dua kriteria: (1) tanggal pertandingan lebih besar atau sama dengan hari pertama bulan itu, (2) tanggal pertandingan kurang dari atau sama dengan hari terakhir bulan itu. Jika kita memasukkan tanggal pada Januari 2016 ke formula menggunakan fungsi DATE, maka akan terlihat seperti ini.

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

Tapi tanggal hard-code tidak diinginkan disini, Kita ingin Excel menghasilkan tanggal ini. Pada dasarnya ini cukup sulit, karena jika  menambahkan nama bulan sebagai teks (yaitu “Januari”, “Februari”, “Maret”, dll.) Di kolom F harus mencari masalah tambahan untuk membuat tanggal yang dapat digunakan sesuai kriteria .

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

Cara Ini membuat lebih mudah dalam membangun kriteria yang dibutuhkan untuk AVERAGEIFS. Untuk mencocokkan tanggal yang lebih besar dari atau sama dengan tanggal pertama bulan itu, dapat menggunakan:

“>=” & E4

Untuk mencocokan tanggal kurang atau sama dengan hari terakhir dalam setiap bulan bisa menggunakan:

“<=” & EOMONTH (e4,0)

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

Catatan: penggabungan dengan ampersand (&) diperlukan ketika menambahkan kriteria berdasarkan acuan sel.

Solusi Tabel Pivot

Tabel pivot adalah solusi yang sangat baik ketika  perlu meringkas data berdasarkan tahun, bulan, kuartal, dan seterusnya, karena tabel pivot menyediakan kontrol untuk pengelompokan tanggal secara otomatis. Demikian lah rumus Excel mencari rata-rata berdasarkan bulan.