Ringkasan
Dalam kasus tertentu, Anda bisa menggunakan SUMIFS seperti rumus pencarian untuk mengambil nilai numerik. Pada contoh yang ditunjukkan, rumus di G6 adalah:
=SUMIFS(sales,region,G4,quarter,G5)
di mana wilayah (B5: B20), kuartal (C5: C20), dan penjualan (D5: D20) diberi nama rentang.
Hasilnya adalah penjualan Q3 untuk wilayah Tengah, 127.250.
Penjelasan
Jika Anda baru mengenal fungsi SUMIFS, Anda dapat menemukan gambaran umum dasar dengan banyak contoh di sini.
Fungsi SUMIFS dirancang untuk menjumlahkan nilai numerik berdasarkan satu atau beberapa kriteria. Namun dalam kasus tertentu, Anda mungkin dapat menggunakan SUMIFS untuk "mencari" nilai numerik yang memenuhi kriteria yang diperlukan. Alasan utama untuk melakukan ini adalah kesederhanaan dan kecepatan.
Dalam contoh yang ditampilkan, kami memiliki data penjualan triwulanan untuk empat wilayah. Kami memulai dengan memberi SUMIFS rentang jumlah, dan ketentuan pertama, yang menguji wilayah untuk nilai di G4, "Tengah":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Rentang jumlah penjualan (D5: D20)
- Rentang kriteria 1 adalah wilayah (B5: B20)
- Kriteria 1 adalah G4 ("Tengah")
Kami kemudian menambahkan pasangan rentang / kriteria kedua, yang memeriksa kuartal:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Rentang kriteria 2 adalah seperempat (C5: C20)
- Kriteria 2 adalah G5 ("Q3")
Dengan kriteria ini, SUMIFS mengembalikan 127.250, angka penjualan Central Q3.
Perilaku SUMIFS adalah menjumlahkan semua nilai yang cocok. Namun, karena hanya ada satu nilai yang cocok, hasilnya sama dengan nilai itu sendiri.
Di bawah ini, kami melihat beberapa opsi rumus pencarian.
Opsi rumus pencarian
Bagian ini mengulas secara singkat opsi rumus lain yang memberikan hasil yang sama. Dengan pengecualian SUMPRODUCT (di bagian bawah), ini adalah rumus pencarian yang lebih tradisional yang menemukan posisi nilai target, dan mengembalikan nilai di lokasi itu.
Dengan VLOOKUP
Sayangnya, VLOOKUP bukanlah solusi yang baik untuk masalah ini. Dengan kolom pembantu, dimungkinkan untuk membuat rumus VLOOKUP agar sesuai dengan beberapa kriteria (contoh di sini), tetapi ini adalah proses yang canggung yang mengharuskan Anda untuk mengutak-atik data sumber.
Dengan INDEX dan MATCH
INDEX dan MATCH adalah kombinasi pencarian yang sangat fleksibel yang dapat digunakan untuk semua jenis masalah pencarian, dan contoh ini tidak terkecuali. Dengan INDEX dan MATCH, kita dapat mencari penjualan berdasarkan kawasan dan kuartal dengan rumus array seperti ini:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Catatan: ini adalah rumus array, dan harus dimasukkan dengan control + shift + enter.
Trik dengan pendekatan ini adalah menggunakan logika boolean dengan operasi larik di dalam fungsi MATCH untuk membangun larik 1 dan 0 sebagai larik pencarian. Kemudian kita dapat meminta fungsi MATCH untuk menemukan angka 1. Setelah array pencarian dibuat, rumus memutuskan menjadi:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Dengan hanya 1 tersisa dalam larik pencarian, MATCH mengembalikan posisi 11 ke fungsi INDEX, dan INDEX mengembalikan nomor penjualan pada posisi itu, 127.250.
Untuk lebih jelasnya, lihat: INDEX dan MATCH dengan beberapa kriteria
Dengan XLOOKUP
XLOOKUP adalah fungsi baru yang fleksibel di Excel yang dapat menangani array secara native. Dengan XLOOKUP, kita dapat menggunakan pendekatan yang sama persis dengan INDEX dan MATCH, menggunakan logika boolean dan operasi array untuk membuat array pencarian:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Setelah operasi array berjalan, rumus memutuskan menjadi:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Dan XLOOKUP mengembalikan hasil yang sama seperti di atas, 127.250.
Selengkapnya: XLOOKUP dengan berbagai kriteria
Dengan LOOKUP
Fungsi LOOKUP adalah fungsi lama di Excel yang bahkan tidak diketahui banyak orang. Salah satu kekuatan utama LOOKUP adalah dapat menangani array secara native. Namun, LOOKUP memiliki beberapa kelemahan:
- Tidak dapat dikunci dalam "mode pencocokan tepat"
- Selalu menganggap data pencarian diurutkan, AZ
- Selalu mengembalikan kecocokan perkiraan (jika kecocokan persis tidak dapat ditemukan)
Meskipun demikian, LOOKUP dapat digunakan untuk mengatasi masalah ini dengan baik seperti ini:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
yang disederhanakan menjadi:
=LOOKUP(2,(#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!),sales)
If you look closely, you can see a single number 1 in a sea of #DIV/0! errors. This represents the value we want to retrieve.
We use a lookup value of 2 because we can't guarantee the array is sorted. So, we force all non-matching rows to errors, and ask LOOKUP to find a 2. LOOKUP ignores the errors and dutifully scans the entire array looking for 2. When the number 2 can't be found, LOOKUP "backs up" and matches the last non-error value, which is the 1 in the 11th position. The result is the same as above, 127,250.
More detailed explanation here.
With SUMPRODUCT
As usual, you can also use the Swiss Army Knife SUMPRODUCT function to solve this problem as well. The trick is to use boolean logic and array operations to "zero out" all but the one value we want:
=SUMPRODUCT(sales*((region=G4)*(quarter=G5)))
After the array math inside SUMPRODUCT is complete, the formula simplifies to:
=SUMPRODUCT((0;0;0;0;0;0;0;0;0;0;127250;0;0;0;0;0))
This is technically not really a lookup formula, but it behaves like one. With just a single array to process, the SUMPRODUCT function returns the sum of the array, 12,7250.
See this example for a more complete explanation.
In spirit, the SUMPRODUCT option is closest to the SUMIFS formula since we are summing values based on multiple criteria. As before, it works fine as long as there is only one matching result.
Summary
SUMIF can indeed be used like a lookup formula, and configuration may be simpler than a more conventional lookup formula. In addition, if you are working with a large data set, SUMIFS will be a very fast option. However, you must keep in mind two key requirements:
- The result must be numeric data
- Criteria must match only one result
Jika situasinya tidak memenuhi kedua persyaratan tersebut, SUMIFS bukanlah pilihan yang baik.
