Sažetak
U određenim slučajevima možete koristiti SUMIFS poput formule za pretraživanje da biste dohvatili numeričku vrijednost. U prikazanom primjeru formula u G6 je:
=SUMIFS(sales,region,G4,quarter,G5)
gdje su regija (B5: B20), tromjesečje (C5: C20) i prodaja (D5: D20) imenovani rasponi.
Rezultat je prodaja u trećem kvartalu za središnju regiju, 127.250.
Obrazloženje
Ako ste novi u funkciji SUMIFS, ovdje možete pronaći osnovni pregled s mnogo primjera.
Funkcija SUMIFS dizajnirana je za zbrajanje numeričkih vrijednosti na temelju jednog ili više kriterija. Međutim, u određenim slučajevima možda ćete moći koristiti SUMIFS za "traženje" numeričke vrijednosti koja zadovoljava tražene kriterije. Glavni razlozi za to su jednostavnost i brzina.
U prikazanom primjeru imamo tromjesečne podatke o prodaji za četiri regije. Počinjemo s davanjem SUMIFS-a raspona zbroja, a prvi uvjet, koji ispituje regiju za vrijednost u G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Zbirni raspon je prodaja (D5: D20)
- Raspon kriterija 1 je regija (B5: B20)
- Kriterij 1 je G4 ("Središnji")
Zatim dodajemo drugi par raspona / kriterija koji provjerava četvrtinu:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Raspon kriterija 2 je četvrtina (C5: C20)
- Kriterij 2 je G5 ("Q3")
S tim kriterijima, SUMIFS vraća 127.250, središnji Q3 prodajni broj.
Ponašanje SUMIFS-a je zbrajanje svih odgovarajućih vrijednosti. Međutim, budući da postoji samo jedna odgovarajuća vrijednost, rezultat je isti kao i sama vrijednost.
U nastavku ćemo pogledati nekoliko opcija formule za pretraživanje.
Opcije formule za traženje
Ovaj odjeljak ukratko daje pregled ostalih opcija formule koje daju isti rezultat. Osim SUMPRODUCT-a (na dnu), ovo su tradicionalnije formule pretraživanja koje pronalaze položaj ciljne vrijednosti i vraćaju vrijednost na tom mjestu.
Uz VLOOKUP
Nažalost, VLOOKUP nije dobro rješenje za ovaj problem. Pomoću pomoćnog stupca moguće je izgraditi VLOOKUP formulu koja se podudara s više kriterija (primjer ovdje), ali to je neugodan postupak koji zahtijeva petljanje s izvornim podacima.
Uz INDEKS i UTAKMICU
INDEX i MATCH vrlo je fleksibilna kombinacija pretraživanja koja se može koristiti za sve vrste problema pretraživanja, a ovaj primjer nije iznimka. Pomoću INDEX-a i MATCH-a možemo pretraživati prodaju po regijama i tromjesečjima s formulom niza poput ove:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Trik s ovim pristupom je u korištenju logičke logike s operacijama niza unutar funkcije MATCH za izgradnju niza od 1s i 0s kao niza pretraživanja. Tada možemo tražiti od funkcije MATCH da pronađe broj 1. Jednom kada se stvori pretraživački niz, formula se rješava na:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Sa samo 1 preostalim u nizu pretraživanja, MATCH vraća položaj 11 funkciji INDEX, a INDEX vraća prodajni broj na tom mjestu, 127.250.
Za više pojedinosti pogledajte: INDEKS i UMAKNICA s više kriterija
Uz 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
Ako situacija ne ispunjava oba zahtjeva, SUMIFS nije dobar izbor.
