Formula generik
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Ringkasan
Untuk jumlahnya setiap bulan, anda boleh menggunakan formula berdasarkan fungsi SUMIFS, dengan bantuan fungsi EOMONTH. Dalam contoh yang ditunjukkan, formula dalam F4 adalah:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Rumus ini menggunakan julat bernama "jumlah" (C4: C9) dan "tarikh" (B4: B9).
Penjelasan
Fungsi SUMIFS dapat menjumlahkan nilai dalam julat berdasarkan beberapa kriteria.
Dalam kes ini, kami mengkonfigurasi SUMIFS untuk menjumlahkan jumlah mengikut bulan menggunakan dua kriteria: satu untuk mencocokkan tarikh yang lebih besar daripada atau sama dengan hari pertama bulan itu, satu untuk mencocokkan tarikh kurang dari atau sama dengan hari terakhir bulan tersebut. Dengan kata lain, kita memerlukan formula seperti ini:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Atau alternatif yang lebih kalis peluru:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Versi ini lebih mantap kerana tarikh dibina dengan nilai tahun, bulan, dan hari yang terpisah, dan tidak dimasukkan sebagai teks yang perlu ditafsirkan oleh Excel.
Biasanya, ini menyakitkan, kerana jika anda menambahkan nama bulan sebagai teks (iaitu "Januari", "Februari", "Mac", dan lain-lain) di lajur E, 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 E, bukannya menaip nama bulan, kami menambah tarikh sebenar (1/1/2016, 2/1/2016, 3/1/2016), kemudian gunakan format tarikh tersuai ("mmmm") untuk memaparkan nama bulan.
Ini memudahkan kita membina kriteria yang kita perlukan untuk SUMIFS. 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 ini, kami menggunakan ini:
"<="&EOMONTH(E4,0)
Dengan sifar selama berbulan-bulan, fungsi EOMONTH secara automatik mengembalikan hari terakhir bulan yang disediakan mengikut tarikh.
Gabungan dengan ampersand (&) diperlukan ketika membina kriteria yang menggunakan operator logik dengan nilai berangka.
Penyelesaian Jadual Pangsi
Jadual pangsi adalah penyelesaian yang sangat baik apabila anda perlu meringkaskan data mengikut tahun, bulan, suku, dan seterusnya, kerana mereka akan melakukan pengelompokan semacam ini untuk anda tanpa formula sama sekali. Untuk perbandingan formula-formula lawan jadual pangsi secara bersebelahan, lihat video ini: Mengapa jadual pangsi.
