Resumé
I visse tilfælde kan du bruge SUMIFS som en opslagsformel til at hente en numerisk værdi. I det viste eksempel er formlen i G6:
=SUMIFS(sales,region,G4,quarter,G5)
hvor region (B5: B20), kvartal (C5: C20) og salg (D5: D20) er navngivet områder.
Resultatet er 3. kvartalssalg for den centrale region, 127.250.
Forklaring
Hvis du er ny i SUMIFS-funktionen, kan du finde en grundlæggende oversigt med mange eksempler her.
SUMIFS-funktionen er designet til at opsummere numeriske værdier baseret på et eller flere kriterier. I specifikke tilfælde kan du dog muligvis bruge SUMIFS til at "slå" op en numerisk værdi, der opfylder de krævede kriterier. Hovedårsagerne til dette er enkelhed og hastighed.
I det viste eksempel har vi kvartalsvise salgsdata for fire regioner. Vi starter med at give SUMIFS et sumområde og den første betingelse, der tester region for værdien i G4, "Central":
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Sumområdet er salg (D5: D20)
- Kriterieområde 1 er region (B5: B20)
- Kriterium 1 er G4 ("Central")
Vi tilføjer derefter det andet interval / kriteriepar, som kontrollerer kvartal:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Kriterieområde 2 er kvart (C5: C20)
- Kriterium 2 er G5 ("Q3")
Med disse kriterier returnerer SUMIFS 127.250, det centrale Q3-salgsnummer.
SUMIFS's opførsel er at opsummere alle matchende værdier. Men fordi der kun er en matchende værdi, er resultatet det samme som selve værdien.
Nedenfor ser vi på flere muligheder for opslagsformel.
Opslag formel muligheder
Dette afsnit gennemgår kort andre formelindstillinger, der giver det samme resultat. Med undtagelse af SUMPRODUCT (nederst) er dette mere traditionelle opslagsformler, der lokaliserer målværdipositionen og returnerer værdien på det sted.
Med VLOOKUP
Desværre er VLOOKUP ikke en god løsning på dette problem. Med en hjælpekolonne er det muligt at opbygge en VLOOKUP-formel, der passer til flere kriterier (eksempel her), men det er en akavet proces, der kræver, at du tinker med kildedataene.
Med INDEX og MATCH
INDEX og MATCH er en meget fleksibel opslagskombination, der kan bruges til alle slags opslagsproblemer, og dette eksempel er ingen undtagelse. Med INDEX og MATCH kan vi slå op efter region og kvartal med en matrixformel som denne:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.
Tricket med denne tilgang er at bruge boolsk logik med matrixoperationer inde i MATCH-funktionen til at oprette en matrix på 1s og 0s som opslagsarray. Så kan vi bede MATCH-funktionen finde nummeret 1. Når opslagsarrayet er oprettet, løser formlen sig at:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Med kun 1 tilbage i opslagsmatrixen returnerer MATCH en position på 11 til INDEX-funktionen, og INDEX returnerer salgsnummeret på denne position, 127.250.
For flere detaljer, se: INDEX og MATCH med flere kriterier
Med XLOOKUP
XLOOKUP er en fleksibel ny funktion i Excel, der kan håndtere arrays indbygget. Med XLOOKUP kan vi bruge nøjagtig den samme tilgang som med INDEX og MATCH ved hjælp af boolsk logik og array-operationer til at oprette et opslagsarray:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Når arrayoperationerne er kørt, løser formlen sig at:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Og XLOOKUP returnerer det samme resultat som ovenfor, 127.250.
Mere: XLOOKUP med flere kriterier
Med LOOKUP
LOOKUP-funktionen er en ældre funktion i Excel, som mange ikke engang kender til. En af LOOKUPs vigtigste styrker er, at den kan håndtere arrays indbygget. LOOKUP har dog et par forskellige svagheder:
- Kan ikke låses i "nøjagtig matchningstilstand"
- Antager altid, at opslagsdata er sorteret, AZ
- Returnerer altid et omtrentligt match (hvis det nøjagtige match ikke kan findes)
Ikke desto mindre kan LOOKUP bruges til at løse dette problem pænt som dette:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
hvilket forenkler til:
=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
Hvis situationen ikke opfylder begge krav, er SUMIFS ikke et godt valg.
