Formula umum
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Ringkasan
Untuk rata-rata per bulan, Anda dapat menggunakan rumus berdasarkan fungsi AVERAGEIFS, dengan bantuan fungsi EOMONTH. Dalam contoh yang ditunjukkan, rumus di F4 adalah:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Rumus ini menggunakan rentang bernama "jumlah" (D5: D104) dan "tanggal" (C5: C104).
Penjelasan
Fungsi AVERAGEIFS dapat menghitung rata-rata rentang berdasarkan beberapa kriteria. Dalam kasus ini, kami mengonfigurasi AVERAGEIFS ke jumlah rata-rata berdasarkan bulan menggunakan dua kriteria: (1) mencocokkan tanggal lebih besar atau sama dengan hari pertama bulan itu, (2) mencocokkan tanggal kurang dari atau sama dengan hari terakhir bulan itu. Jika kami 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))
Tapi kami tidak ingin tanggal hard-code, kami ingin Excel membuat tanggal ini untuk kami. Biasanya, ini merepotkan, karena jika Anda menambahkan nama bulan sebagai teks (yaitu "Januari", "Februari", "Maret", dll.) Di kolom F Anda harus bersusah payah membuat tanggal yang dapat Anda gunakan untuk kriteria .
Namun, dalam kasus ini, kami menggunakan trik sederhana untuk mempermudah: Di kolom F, alih-alih 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.
Ini membuatnya mudah untuk membangun kriteria yang kita butuhkan untuk AVERAGEIFS. Untuk mencocokkan tanggal yang lebih besar dari atau sama dengan tanggal pertama setiap bulan, kami menggunakan:
">="&E4
Dan untuk mencocokkan tanggal kurang dari atau sama dengan hari terakhir setiap bulan, kami menggunakan:
"<="&EOMONTH(E4,0)
EOMONTH secara otomatis mengembalikan hari terakhir di bulan yang sama karena kami menyediakan nol untuk argumen bulan.
Catatan: penggabungan dengan ampersand (&) diperlukan saat membangun kriteria berdasarkan referensi sel.
Solusi Tabel Pivot
Tabel pivot adalah solusi yang sangat 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.
