Formula umum
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Ringkasan
Untuk waktu respons rata-rata per bulan, Anda bisa menggunakan rumus berdasarkan fungsi AVERAGEIFS, bersama dengan fungsi EOMONTH.
Pada contoh yang ditunjukkan, rumus di G5 adalah:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Penjelasan
Rumus ini menggunakan rentang bernama "tanggal" (B5: B25) dan "durasi" (D5: D25). Kolom durasi D dalam menit, dihitung dengan mengurangkan tanggal buka dari tanggal tutup.
Fungsi AVERAGEIFS dirancang untuk rentang rata-rata berdasarkan beberapa kriteria. Dalam kasus ini, kami mengonfigurasi AVERAGEIFS ke durasi 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.
Untuk mengurung tanggal menurut bulan, kami menggunakan trik sederhana untuk mempermudah: Di kolom F, alih-alih mengetik nama bulan ("Jan", "Feb", Mar ", dll.) Kami menambahkan, kami menambahkan tanggal sebenarnya untuk tanggal pertama setiap bulan (1/1/2016, 2/1/2016, 3/1/2016, dll.), Kemudian, kami menggunakan format tanggal kustom ("mmm") untuk menampilkan nama bulan.
Ini membuatnya jauh lebih mudah untuk membangun kriteria yang kita butuhkan untuk AVERAGEIFS menggunakan nilai di kolom F. Untuk mencocokkan tanggal yang lebih besar dari atau sama dengan tanggal pertama bulan itu, kita menggunakan:
">="&F5
Untuk mencocokkan tanggal kurang dari atau sama dengan hari terakhir bulan itu, kami menggunakan:
"<="&EOMONTH(F5,0)
Kami mendapatkan EOMONTH untuk mengembalikan hari terakhir di bulan yang sama dengan memberikan nol untuk argumen bulan .
Catatan: penggabungan dengan ampersand (&) diperlukan saat membangun kriteria berdasarkan referensi sel.
Solusi Tabel Pivot
Tabel pivot adalah solusi luar biasa saat Anda perlu meringkas atau rata-rata data menurut tahun, bulan, kuartal, dan seterusnya, karena tabel pivot menyediakan kontrol untuk mengelompokkan tanggal secara otomatis. Untuk perbandingan berdampingan dari rumus vs. tabel pivot, lihat video ini: Mengapa tabel pivot.
