Formula generik
=SUMPRODUCT((MONTH(dates)=month)*amounts)
Ringkasan
Untuk menjumlahkan data berdasarkan bulan, mengabaikan tahun, anda boleh menggunakan formula berdasarkan fungsi SUMPRODUCT dan BULAN. Dalam contoh yang ditunjukkan, formula dalam H6 adalah:
=SUMPRODUCT((MONTH(dates)=3)*amounts)
Hasilnya adalah jumlah keseluruhan penjualan pada bulan Mac, mengabaikan tahun.
Penjelasan
Kumpulan data ini mengandungi lebih dari 2900 rekod, dan formula di atas menggunakan dua julat bernama:
dates = B5:B2932 amounts = E5:E2932
Di dalam fungsi SUMPRODUCT, fungsi BULAN digunakan untuk mengekstrak nombor bulan untuk setiap tarikh dalam set data, dan membandingkannya dengan nombor 3:
(MONTH(dates)=3)
Sekiranya kita menganggap satu set data kecil yang menyenaraikan 3 tarikh masing-masing pada bulan Januari, Februari, dan Mac (mengikut urutan itu), hasilnya akan menjadi susunan yang mengandungi sembilan nombor seperti ini:
(1;1;1;2;2;2;3;3;3)
di mana setiap nombor adalah "nombor bulan" untuk tarikh. Apabila nilai dibandingkan dengan 3, hasilnya adalah susunan seperti ini:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE)
Susunan ini kemudian didarabkan dengan nilai jumlah yang berkaitan dengan setiap tarikh Mac. Sekiranya kita menganggap semua sembilan jumlahnya sama dengan 100, operasi seperti ini:
(0;0;0;0;0;0;1;1;1) * (100;100;100;100;100;100;100;100;100)
Perhatikan operasi matematik mengubah nilai BENAR PALSU menjadi satu dan nol. Setelah pendaraban, kita mempunyai satu array dalam SUMPRODUCT:
=SUMPRODUCT((0;0;0;0;0;0;100;100;100))
Perhatikan bahawa jumlah yang masih ada berkaitan dengan bulan Mac, selebihnya adalah sifar.
Akhirnya, SUMPRODUCT mengembalikan jumlah semua item - 300 dalam contoh yang disingkat di atas, dan 25,521 dalam tangkapan skrin dengan data sebenar.
Kira mengikut bulan mengabaikan tahun
Untuk mendapatkan kiraan mengikut bulan mengabaikan tahun, anda boleh menggunakan SUMPRODUCT seperti ini:
=SUMPRODUCT(--(MONTH(dates)=3))
Rata-rata mengikut bulan mengabaikan tahun
Untuk mengira dan rata-rata mengikut bulan mengabaikan tahun, anda menggabungkan dua formula SUMPRODUCT di atas seperti ini:
=SUMPRODUCT((MONTH(dates)=3)*amounts)/SUMPRODUCT(--(MONTH(dates)=3))
