Sammanfattning
I vissa fall kan du använda SUMIFS som en uppslagsformel för att hämta ett numeriskt värde. I exemplet som visas är formeln i G6:
=SUMIFS(sales,region,G4,quarter,G5)
där region (B5: B20), kvartal (C5: C20) och försäljning (D5: D20) heter intervall.
Resultatet är Q3-försäljningen för Centralregionen, 127 250.
Förklaring
Om du är ny i SUMIFS-funktionen kan du hitta en grundläggande översikt med många exempel här.
SUMIFS-funktionen är utformad för att summera numeriska värden baserat på ett eller flera kriterier. I specifika fall kan du dock kunna använda SUMIFS för att "slå upp" ett numeriskt värde som uppfyller nödvändiga kriterier. De främsta anledningarna till detta är enkelhet och snabbhet.
I exemplet som visas har vi kvartalsvis försäljningsdata för fyra regioner. Vi börjar med att ge SUMIFS ett summeringsområde och det första villkoret, som testar region för värdet i G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Summan är försäljning (D5: D20)
- Kriterieområde 1 är region (B5: B20)
- Kriterium 1 är G4 ("Central")
Vi lägger sedan till det andra intervallet / kriterieparet, som kontrollerar kvartalet:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Kriterieområde 2 är kvart (C5: C20)
- Kriterium 2 är G5 ("Q3")
Med dessa kriterier returnerar SUMIFS 127 250, det centrala Q3-försäljningsnumret.
SUMIFS beteende är att summera alla matchande värden. Eftersom det bara finns ett matchande värde är resultatet detsamma som själva värdet.
Nedan tittar vi på flera alternativ för uppslagsformler.
Alternativ för sökformel
I det här avsnittet granskas kortfattat andra formelalternativ som ger samma resultat. Med undantag för SUMPRODUCT (längst ner) är dessa mer traditionella uppslagsformler som lokaliserar målvärdets position och returnerar värdet på den platsen.
Med VLOOKUP
Tyvärr är VLOOKUP inte en bra lösning på detta problem. Med en hjälpkolumn är det möjligt att bygga en VLOOKUP-formel för att matcha flera kriterier (exempel här), men det är en besvärlig process som kräver att du tippar med källdata.
Med INDEX och MATCH
INDEX och MATCH är en mycket flexibel uppslagskombination som kan användas för alla typer av uppslagsproblem, och detta exempel är inget undantag. Med INDEX och MATCH kan vi söka efter försäljning efter region och kvartal med en matrisformel så här:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.
Tricket med detta tillvägagångssätt är att använda boolesk logik med matrisoperationer inuti MATCH-funktionen för att bygga en matris med 1 och 0 som uppslagsmatris. Då kan vi fråga MATCH-funktionen för att hitta siffran 1. När uppslagsmatrisen har skapats löser formeln följande:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Med bara 1 kvar i uppslagsmatrisen returnerar MATCH en position på 11 till INDEX-funktionen och INDEX returnerar försäljningsnumret vid den positionen, 127 250.
För mer information, se: INDEX och MATCH med flera kriterier
Med XLOOKUP
XLOOKUP är en flexibel ny funktion i Excel som kan hantera arrays naturligt. Med XLOOKUP kan vi använda exakt samma tillvägagångssätt som med INDEX och MATCH, med hjälp av boolesk logik och array-operationer för att skapa en uppslagsmatris:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
När array-operationerna har körts löser formeln följande:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Och XLOOKUP returnerar samma resultat som ovan, 127 250.
Mer: XLOOKUP med flera kriterier
Med LOOKUP
LOOKUP-funktionen är en äldre funktion i Excel som många inte ens vet om. En av LOOKUP: s viktigaste styrkor är att den kan hantera arrays naturligt. LOOKUP har dock några tydliga svagheter:
- Kan inte låsas i "exakt matchningsläge"
- Antar alltid att uppslagsdata är sorterade, AZ
- Returnerar alltid en ungefärlig matchning (om exakt matchning inte kan hittas)
Icke desto mindre kan LOOKUP användas för att lösa detta problem så här:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
vilket förenklar till:
=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
Om situationen inte uppfyller båda kraven är SUMIFS inte ett bra val.
