Ringkasan
Untuk jumlah demi bulan dalam lajur, anda boleh menggunakan fungsi SUMIFS bersama dengan fungsi EOMONTH. Dalam contoh yang ditunjukkan, formula dalam G5 adalah:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Rumus ini menggunakan tiga julat bernama: "jumlah" (D5: D15), "pelanggan" (B5: B15), dan "tarikh" (C5: C15).
Penjelasan
Fungsi SUMIFS dirancang untuk menjumlahkan nilai dalam julat berdasarkan satu atau lebih kriteria. Dalam kes ini, kita memerlukan tiga kriteria:
- Nama pelanggan = pelanggan di lajur F
- Tarikh> = pertama bulan (dari tarikh di baris 4)
- Tarikh <= akhir bulan (dari tarikh di baris 4)
Perkara utama: nama bulan di G4: I4 sebenarnya adalah tarikh yang sah, diformat dengan format nombor tersuai "mmm". Ini membolehkan kami menggunakan nilai tarikh di G4: I4 secara langsung untuk kriteria # 2 dan # 3 di atas.
Kembali ke SUMIFS, pasangan julat / kriteria pertama digunakan untuk memeriksa nama pelanggan:
client,$F5
Perhatikan F5 adalah rujukan campuran, dengan lajur terkunci. Ini membolehkan baris berubah kerana formula disalin melalui jadual, tetapi nama pelanggan selalu berasal dari lajur F.
Pasangan julat / kriteria kedua digunakan untuk memeriksa tarikh berbanding yang pertama bulan:
date,">="&G$4
Seperti yang disebutkan di atas, nilai dalam G4: I4 sebenarnya adalah tarikh: 1 Jan 2019, 1 Februari 2019, dan 1 Mac 2019. Oleh itu, kriteria ini hanya memeriksa tarikh yang lebih besar daripada tarikh bulan pertama di baris 4. Perhatikan bahawa ini adalah rujukan campuran lain, kali ini dengan baris terkunci Ini membolehkan lajur berubah ketika formula disalin di seluruh jadual, tetapi tetap mengekalkan bilangan baris. Gabungan dengan ampersand (&) diperlukan ketika membina kriteria yang menggunakan operator logik dan nilai dari sel lain.
Pasangan julat / kriteria ketiga digunakan untuk memeriksa tarikh berbanding hari terakhir dalam bulan:
date,"<="&EOMONTH(G$4,0)
Untuk mendapatkan hari terakhir setiap bulan, kami menggunakan fungsi EOMONTH pada tarikh dari tajuk lajur di baris 4. Dengan sifar yang disediakan untuk argumen bulan, EOMONTH mengembalikan hari terakhir pada bulan yang sama. Sekali lagi, rujukan ke G4 dicampur untuk mengelakkan baris berubah.
Penyelesaian Jadual Pangsi
Jadual pangsi akan menjadi penyelesaian yang sangat baik untuk masalah ini, kerana ia dapat dikelompokkan secara automatik mengikut bulan tanpa formula sama sekali. Untuk perbandingan formula-formula lawan jadual pangsi secara bersebelahan, lihat video ini: Mengapa jadual pangsi.
