Ringkasan
Untuk menyusun tabel ringkasan dua arah yang menjumlahkan data numerik dengan lebih dari satu kriteria, Anda bisa menggunakan fungsi SUMIF. Dalam contoh yang diperlihatkan, rumus di H5, disalin melintasi rentang H5: K7, adalah:
=SUMIFS(value,name,$G5,stage,H$4)
di mana nilai (C5: C15), nama (B5: B15), dan panggung (D5: D15) diberi nama rentang. Hasilnya adalah tabel yang menunjukkan total ringkasan untuk setiap nama berdasarkan tahapan.
Penjelasan
Fungsi SUMIFS dirancang untuk menjumlahkan nilai numerik menggunakan beberapa kriteria.
Dalam contoh yang ditampilkan, data dalam rentang B5: E15 menunjukkan jalur penjualan yang setiap barisnya merupakan peluang yang dimiliki oleh seorang penjual, pada tahap tertentu. Rumus di H5 adalah:
=SUMIFS(value,name,$G5,stage,H$4)
Bagian pertama rumus merangkum peluang menurut penjual:
=SUMIFS(value,name,$G5 // sum by name
- Rentang Sum adalah rentang bernama nilai-nilai
- Kriteria berkisar 1 adalah kisaran bernama nama
- Kriteria 1 berasal dari sel G5
Perhatikan $ G5 adalah referensi campuran, dengan kolom terkunci dan relatif baris. Ini memungkinkan rumus berubah sesuai kebutuhan saat rumus disalin ke seluruh tabel.
Pasangan rentang / kriteria berikutnya dalam SUMIFS, dijumlahkan menurut tahap:
stage,H$4 // sum by stage
- Rentang kriteria 2 adalah tahap rentang bernama
- Kriteria 2 adalah H $ 4
Sekali lagi, H $ 4 adalah referensi campuran, dengan kolom relatif dan baris terkunci. Ini memungkinkan kriteria untuk mengambil nilai tahapan di baris 4 saat rumus disalin ke seberang dan ke bawah tabel.
Dengan kedua kriteria bersama-sama, fungsi SUMIFS menjumlahkan peluang dengan benar berdasarkan nama dan tahapan.
Tanpa rentang nama
Contoh ini menggunakan rentang bernama hanya untuk kenyamanan. Tanpa rentang bernama, rumus ekuivalennya adalah:
=SUMIFS($C$5:$C$15,$B$5:$B$15,$G5,$D$5:$D$15,H$4)
Perhatikan referensi untuk nama, nilai, dan tahapan sekarang menjadi referensi absolut untuk mencegah perubahan saat rumus disalin ke seberang dan ke bawah tabel.
Catatan: tabel pivot juga merupakan cara terbaik untuk mengatasi masalah ini.
