Povzetek
V nekaterih primerih lahko uporabite SUMIFS kot iskalno formulo za pridobivanje številske vrednosti. V prikazanem primeru je formula v G6:
=SUMIFS(sales,region,G4,quarter,G5)
pri čemer so območja (B5: B20), četrtletje (C5: C20) in prodaja (D5: D20) imenovana obsegi.
Rezultat je prodaja Q3 za osrednjo regijo, 127.250.
Pojasnilo
Če funkcije SUMIFS ne poznate, lahko tukaj najdete osnovni pregled s številnimi primeri.
Funkcija SUMIFS je namenjena seštevanju številskih vrednosti na podlagi enega ali več meril. V posebnih primerih pa boste morda lahko s SUMIFS "poiskali" številčno vrednost, ki ustreza zahtevanim merilom. Glavna razloga za to sta preprostost in hitrost.
V prikazanem primeru imamo četrtletne podatke o prodaji za štiri regije. Začnemo tako, da damo vsoto vsote SUMIFS in prvi pogoj, ki preskuša regijo za vrednost v G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Razpon vsote je prodaja (D5: D20)
- Območje meril 1 je regija (B5: B20)
- Merila 1 je G4 ("Central")
Nato dodamo drugi obseg / kriterij, ki preveri četrtino:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Razpon meril 2 je četrtina (C5: C20)
- Kriterij 2 je G5 ("Q3")
S temi kriteriji SUMIFS vrne 127.250, osrednjo prodajno številko Q3.
Obnašanje SUMIFS je seštevanje vseh ujemajočih se vrednosti. Ker pa obstaja le ena ujemajoča se vrednost, je rezultat enak vrednosti.
Spodaj si ogledujemo več možnosti iskalnih formul.
Možnosti iskalne formule
Ta odsek na kratko pregleduje druge možnosti formule, ki dajejo enak rezultat. Z izjemo SUMPRODUCT (na dnu) so to bolj tradicionalne iskalne formule, ki poiščejo položaj ciljne vrednosti in vrnejo vrednost na tej lokaciji.
Z VLOOKUP
Na žalost VLOOKUP ni dobra rešitev za to težavo. S pomožnim stolpcem je mogoče zgraditi formulo VLOOKUP, ki se ujema z več merili (primer tukaj), vendar je neprijeten postopek, ki zahteva, da se poigravate z izvornimi podatki.
Z KAZALOM in UTEKOM
INDEX in MATCH je zelo prilagodljiva kombinacija iskanja, ki jo lahko uporabimo za vse vrste težav pri iskanju, in ta primer ni nobena izjema. Z indeksoma in ujemanjem lahko s pomočjo matrične formule, kot je ta, poiščemo prodajo po regijah in četrtletjih:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter.
Trik pri tem pristopu je v uporabi logične logike z operacijami matrike znotraj funkcije MATCH za gradnjo matrike 1s in 0s kot iskalne matrike. Potem lahko funkcijo MATCH prosimo, da poišče številko 1. Ko je iskalna matrika ustvarjena, se formula razreši na:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Ko v iskalnem polju ostane samo 1, MATCH vrne položaj 11 funkciji INDEX, INDEX pa prodajno številko na tem mestu, 127.250.
Za več podrobnosti glejte: INDEX in MATCH z več merili
Z XLOOKUP
XLOOKUP is a flexible new function in Excel that can handle arrays natively. With XLOOKUP, we can use exactly the same approach as with INDEX and MATCH, using boolean logic and array operations to create a lookup array:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Once the array operations have run, the formula resolves to:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
And XLOOKUP returns the same result as above, 127,250.
More: XLOOKUP with multiple criteria
With LOOKUP
The LOOKUP function is an older function in Excel that many people don't even know about. One of LOOKUP's key strengths is that it can handle arrays natively. However, LOOKUP has a few distinct weaknesses:
- Can't be locked in "exact match mode"
- Always assumes lookup data is sorted, A-Z
- Always returns an approximate match (if exact match can't be found)
Nonetheless, LOOKUP can be used to solve this problem nicely like this:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
which simplifies to:
=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
Če situacija ne izpolnjuje obeh zahtev, SUMIFS ni dobra izbira.
