Formula generik
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Ringkasan
Rata-rata mengikut bulan, anda boleh menggunakan formula berdasarkan fungsi AVERAGEIFS, dengan bantuan fungsi EOMONTH. Dalam contoh yang ditunjukkan, formula dalam F4 adalah:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Rumus ini menggunakan julat bernama "jumlah" (D5: D104) dan "tarikh" (C5: C104).
Penjelasan
Fungsi AVERAGEIFS dapat julat purata berdasarkan pelbagai kriteria. Dalam kes ini, kami mengkonfigurasi AVERAGEIFS menjadi jumlah rata-rata setiap bulan menggunakan dua kriteria: (1) tarikh perlawanan lebih besar daripada atau sama dengan hari pertama bulan itu, (2) tarikh perlawanan kurang dari atau sama dengan hari terakhir bulan tersebut. Sekiranya kita memasukkan tarikh untuk Januari 2016 dalam formula menggunakan fungsi DATE, ia akan kelihatan seperti ini.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
Tetapi kami tidak mahu memasukkan tarikh yang keras, kami mahu Excel menghasilkan tarikh ini untuk kami. Biasanya, ini menyakitkan, kerana jika anda menambahkan nama bulan sebagai teks (iaitu "Januari", "Februari", "Mac", dan lain-lain) di lajur F, anda harus menghadapi masalah tambahan untuk membuat tarikh yang boleh anda gunakan untuk kriteria .
Namun, dalam kes ini, kami menggunakan tipuan mudah untuk mempermudah: Di lajur F, bukannya menaip nama bulan, kami menambahkan tarikh sebenar untuk pertama setiap bulan (1/1/2016, 2/1/2016, 3 / 1/2016, dll.), Dan gunakan format tarikh khusus ("mmm") untuk memaparkan nama bulan.
Ini memudahkan kita membina kriteria yang kita perlukan untuk AVERAGEIFS. Untuk memadankan tarikh yang lebih besar daripada atau sama dengan yang pertama bulan, kami menggunakan:
">="&E4
Dan untuk mencocokkan tarikh kurang dari atau sama dengan hari terakhir bulan itu, kami menggunakan:
"<="&EOMONTH(E4,0)
EOMONTH secara automatik mengembalikan hari terakhir bulan yang sama kerana kami membekalkan sifar untuk argumen bulan.
Catatan: gabungan dengan ampersand (&) diperlukan semasa membina kriteria berdasarkan rujukan sel.
Penyelesaian Jadual Pangsi
Jadual pangsi adalah penyelesaian yang sangat baik apabila anda perlu meringkaskan data mengikut tahun, bulan, suku, dan seterusnya, kerana jadual pangsi menyediakan kawalan untuk pengelompokan automatik mengikut tarikh. Untuk perbandingan formula-formula lawan jadual pangsi secara bersebelahan, lihat video ini: Mengapa jadual pangsi.
