Ringkasan
Dalam kes tertentu, anda boleh menggunakan SUMIFS seperti formula pencarian untuk mendapatkan nilai berangka. Dalam contoh yang ditunjukkan, formula dalam G6 adalah:
=SUMIFS(sales,region,G4,quarter,G5)
di mana wilayah (B5: B20), suku (C5: C20), dan penjualan (D5: D20) dinamakan julat.
Hasilnya adalah penjualan Q3 untuk wilayah Tengah, 127.250.
Penjelasan
Sekiranya anda baru menggunakan fungsi SUMIFS, anda boleh mendapatkan gambaran keseluruhan asas dengan banyak contoh di sini.
Fungsi SUMIFS dirancang untuk menjumlahkan nilai berangka berdasarkan satu atau lebih kriteria. Walau bagaimanapun, dalam kes tertentu, anda mungkin dapat menggunakan SUMIFS untuk "mencari" nilai angka yang memenuhi kriteria yang diperlukan. Sebab utama untuk melakukan ini adalah kesederhanaan dan kepantasan.
Dalam contoh yang ditunjukkan, kami mempunyai data penjualan setiap suku tahun untuk empat wilayah. Kami mulakan dengan memberikan SUMIFS julat jumlah, dan syarat pertama, yang menguji wilayah untuk nilai dalam G4, "Tengah":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Julat jumlah adalah jualan (D5: D20)
- Julat kriteria 1 adalah wilayah (B5: B20)
- Kriteria 1 adalah G4 ("Pusat")
Kami kemudian menambah pasangan julat / kriteria kedua, yang memeriksa suku:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Julat kriteria 2 adalah suku (C5: C20)
- Kriteria 2 adalah G5 ("Q3")
Dengan kriteria ini, SUMIFS mengembalikan 127,250, nombor jualan Q3 Pusat.
Tingkah laku SUMIFS adalah menjumlahkan semua nilai yang sepadan. Namun, kerana hanya ada satu nilai yang sepadan, hasilnya sama dengan nilai itu sendiri.
Di bawah ini, kita melihat beberapa pilihan formula carian.
Pilihan formula carian
Bahagian ini mengulas secara ringkas pilihan formula lain yang memberikan hasil yang sama. Dengan pengecualian SUMPRODUCT (di bahagian bawah), ini adalah formula pencarian yang lebih tradisional yang menentukan kedudukan nilai sasaran, dan mengembalikan nilai di lokasi tersebut.
Dengan VLOOKUP
Malangnya, VLOOKUP bukanlah penyelesaian yang baik untuk masalah ini. Dengan kolom penolong, adalah mungkin untuk membina formula VLOOKUP untuk dipadankan dengan pelbagai kriteria (contoh di sini), tetapi ini adalah proses yang canggung yang mengharuskan anda memperhatikan data sumber.
Dengan INDEX dan MATCH
INDEX dan MATCH adalah gabungan 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 mengikut wilayah dan suku dengan formula pelbagai seperti ini:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Catatan: ini adalah formula larik, dan mesti dimasukkan dengan kawalan + shift + enter.
Caranya dengan pendekatan ini adalah menggunakan logik boolean dengan operasi array di dalam fungsi MATCH untuk membina array 1s dan 0s sebagai array pencarian. Kemudian kita boleh meminta fungsi MATCH mencari nombor 1. Setelah tatapan carian dibuat, rumus memutuskan untuk:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Dengan hanya tinggal 1 dalam array pencarian, MATCH mengembalikan kedudukan 11 ke fungsi INDEX, dan INDEX mengembalikan nombor penjualan pada kedudukan itu, 127,250.
Untuk keterangan lebih lanjut, lihat: INDEX dan MATCH dengan pelbagai kriteria
Dengan XLOOKUP
XLOOKUP adalah fungsi baru yang fleksibel di Excel yang dapat menangani susunan secara semula jadi. Dengan XLOOKUP, kita dapat menggunakan pendekatan yang sama persis dengan INDEX dan MATCH, menggunakan logik boolean dan operasi array untuk membuat tatapan carian:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Setelah operasi array dijalankan, rumus memutuskan untuk:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Dan XLOOKUP memberikan hasil yang sama seperti di atas, 127.250.
Lagi: XLOOKUP dengan pelbagai kriteria
Dengan MENCARI
Fungsi LOOKUP adalah fungsi lama di Excel yang tidak diketahui oleh banyak orang. Salah satu kelebihan utama LOOKUP ialah ia dapat menguruskan susunan secara semula jadi. Walau bagaimanapun, LOOKUP mempunyai beberapa kelemahan yang berbeza:
- Tidak dapat dikunci dalam "mod padanan tepat"
- Selalu menganggap data carian disusun, AZ
- Sentiasa mengembalikan padanan anggaran (jika pencocokan tepat tidak dapat dijumpai)
Walaupun begitu, LOOKUP dapat digunakan untuk menyelesaikan masalah ini dengan baik seperti ini:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
yang memudahkan untuk:
=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
Sekiranya keadaan tidak memenuhi kedua-dua syarat, SUMIFS bukanlah pilihan yang baik.
