Formula generik
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Ringkasan
Untuk purata masa tindak balas mengikut bulan, anda boleh menggunakan formula berdasarkan fungsi AVERAGEIFS, bersama dengan fungsi EOMONTH.
Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Penjelasan
Formula ini menggunakan julat bernama "tarikh" (B5: B25) dan "jangka masa" (D5: D25). Lajur jangka masa D adalah dalam beberapa minit, dikira dengan mengurangkan tarikh dibuka dari tarikh ditutup.
Fungsi AVERAGEIFS dirancang untuk julat purata berdasarkan pelbagai kriteria. Dalam kes ini, kami mengkonfigurasi AVERAGEIFS untuk jangka masa rata-rata mengikut bulan menggunakan dua kriteria: (1) tarikh padanan yang lebih besar daripada atau sama dengan hari pertama bulan itu, (2) tarikh yang sepadan kurang dari atau sama dengan hari terakhir bulan tersebut.
Untuk kurangkan tarikh mengikut bulan, kami menggunakan tipuan mudah untuk mempermudah: Di lajur F, bukannya menaip nama bulan ("Jan", "Feb", Mar ", dll.) Kami tambahkan kami menambahkan tarikh sebenar untuk yang pertama setiap bulan (1/1/2016, 2/1/2016, 3/1/2016, dll.),. Kemudian, kami menggunakan format tarikh tersuai ("mmm") untuk memaparkan nama bulan.
Ini menjadikannya lebih mudah untuk membina kriteria yang kami perlukan untuk AVERAGEIFS menggunakan nilai pada lajur F. Untuk memadankan tarikh yang lebih besar daripada atau sama dengan yang pertama bulan ini, kami menggunakan:
">="&F5
Untuk memadankan tarikh kurang dari atau sama dengan hari terakhir dalam sebulan, kami menggunakan:
"<="&EOMONTH(F5,0)
Kami mendapat EOMONTH untuk mengembalikan hari terakhir pada bulan yang sama dengan membekalkan argumen sifar untuk 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 atau rata-rata data mengikut tahun, bulan, suku, dan seterusnya, kerana jadual pangsi menyediakan kawalan untuk pengelompokan tarikh secara automatik. Untuk perbandingan formula-formula lawan jadual pangsi secara bersebelahan, lihat video ini: Mengapa jadual pangsi.
