Ringkasan
Untuk membina jadual ringkasan dua hala yang menjumlahkan data berangka dengan lebih dari satu kriteria, anda boleh menggunakan fungsi SUMIF. Dalam contoh yang ditunjukkan, formula dalam H5, yang disalin pada julat H5: K7, adalah:
=SUMIFS(value,name,$G5,stage,H$4)
di mana nilai (C5: C15), nama (B5: B15), dan tahap (D5: D15) dinamakan julat. Hasilnya adalah jadual yang menunjukkan jumlah ringkasan untuk setiap nama mengikut peringkat.
Penjelasan
Fungsi SUMIFS dirancang untuk menjumlahkan nilai numerik menggunakan pelbagai kriteria.
Dalam contoh yang ditunjukkan, data dalam rentang B5: E15 menunjukkan saluran penjualan di mana setiap baris adalah peluang yang dimiliki oleh seorang jurujual, pada tahap tertentu. Formula dalam H5 adalah:
=SUMIFS(value,name,$G5,stage,H$4)
Bahagian pertama formula merangkumi peluang oleh jurujual:
=SUMIFS(value,name,$G5 // sum by name
- Julat jumlah adalah nilai julat yang dinamakan
- Julat kriteria 1 julat yang dinamakan nama
- Kriteria 1 berasal dari sel G5
Perhatikan $ G5 adalah rujukan campuran, dengan lajur terkunci dan baris relatif. Ini membolehkan formula berubah seperti yang diperlukan semasa formula disalin ke seluruh jadual.
Pasangan julat / kriteria seterusnya dalam SUMIFS, dijumlahkan berdasarkan peringkat:
stage,H$4 // sum by stage
- Julat kriteria 2 adalah peringkat julat yang dinamakan
- Kriteria 2 ialah H $ 4
Sekali lagi, H $ 4 adalah rujukan campuran, dengan lajur relatif dan baris terkunci. Ini membolehkan kriteria untuk mengambil nilai-nilai tahap di baris 4 ketika formula disalin di seberang dan di bawah jadual.
Dengan kedua-dua kriteria bersama, fungsi SUMIFS dengan tepat merangkum peluang berdasarkan nama dan peringkat.
Tanpa julat nama
Contoh ini menggunakan julat bernama untuk kemudahan sahaja. Tanpa julat yang dinamakan, formula setara adalah:
=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)
Rujukan pemberitahuan untuk nama, nilai, dan tahap sekarang adalah rujukan mutlak untuk mencegah perubahan kerana formula disalin di seberang dan di bawah jadual.
Catatan: jadual pangsi juga merupakan kaedah terbaik untuk menyelesaikan masalah ini.
