Sammendrag
I visse tilfeller kan du bruke SUMIFS som en oppslagsformel for å hente en numerisk verdi. I eksemplet vist er formelen i G6:
=SUMIFS(sales,region,G4,quarter,G5)
der region (B5: B20), kvartal (C5: C20) og salg (D5: D20) er navngitt områder.
Resultatet er Q3 salg for Sentralregionen, 127 250.
Forklaring
Hvis du er ny i SUMIFS-funksjonen, kan du finne en grunnleggende oversikt med mange eksempler her.
SUMIFS-funksjonen er designet for å oppsummere numeriske verdier basert på ett eller flere kriterier. I spesifikke tilfeller kan du imidlertid bruke SUMIFS til å "slå opp" en numerisk verdi som oppfyller de nødvendige kriteriene. Hovedårsakene til dette er enkelhet og hastighet.
I eksemplet som er vist, har vi kvartalsvise salgsdata for fire regioner. Vi begynner med å gi SUMIFS et sumområde, og den første betingelsen som tester region for verdien 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)
- Kriterier 1 er G4 ("Sentral")
Vi legger deretter til det andre området / kriterieparet, som sjekker kvartalet:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Kriterieområde 2 er kvart (C5: C20)
- Kriterier 2 er G5 ("Q3")
Med disse kriteriene returnerer SUMIFS 127 250, det sentrale Q3-salgsnummeret.
Oppførselen til SUMIFS er å summere alle samsvarende verdier. Men fordi det bare er en samsvarende verdi, er resultatet det samme som selve verdien.
Nedenfor ser vi på flere alternativer for oppslagsformler.
Alternativer for oppslagsformel
Denne delen gjennomgår kort andre formelalternativer som gir samme resultat. Med unntak av SUMPRODUCT (nederst), er dette mer tradisjonelle oppslagsformler som lokaliserer målverdien, og returnerer verdien på det stedet.
Med VLOOKUP
Dessverre er VLOOKUP ikke en god løsning på dette problemet. Med en hjelpekolonne er det mulig å bygge en VLOOKUP-formel som samsvarer med flere kriterier (eksempel her), men det er en vanskelig prosess som krever at du tukler med kildedataene.
Med INDEX og MATCH
INDEX og MATCH er en veldig fleksibel oppslagskombinasjon som kan brukes til alle slags oppslagsproblemer, og dette eksemplet er ikke noe unntak. Med INDEX og MATCH kan vi slå opp salg etter region og kvartal med en matriseformel som denne:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Merk: dette er en matriseformel, og må angis med kontroll + skift + enter.
Trikset med denne tilnærmingen er å bruke boolsk logikk med matrixoperasjoner i MATCH-funksjonen for å bygge en matrise på 1 og 0 som oppslagsmatrise. Deretter kan vi be MATCH-funksjonen finne tallet 1. Når oppslagsmatrisen er opprettet, løser formelen seg å:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Med bare 1 igjen i oppslagsmatrisen, returnerer MATCH en posisjon på 11 til INDEX-funksjonen, og INDEX returnerer salgsnummeret på den posisjonen, 127 250.
For mer informasjon, se: INDEKS og MATCH med flere kriterier
Med XLOOKUP
XLOOKUP er en fleksibel ny funksjon i Excel som kan håndtere arrays naturlig. Med XLOOKUP kan vi bruke nøyaktig samme tilnærming som med INDEX og MATCH, ved hjelp av boolsk logikk og array-operasjoner for å lage et oppslagsarray:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Når arrayoperasjonene har kjørt, løser formelen seg å:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
Og XLOOKUP returnerer det samme resultatet som ovenfor, 127.250.
Mer: XLOOKUP med flere kriterier
Med LOOKUP
LOOKUP-funksjonen er en eldre funksjon i Excel som mange ikke engang vet om. En av LOOKUPs viktigste styrker er at den kan håndtere arrays naturlig. LOOKUP har imidlertid noen tydelige svakheter:
- Kan ikke låses i "eksakt samsvar-modus"
- Forutsetter alltid at oppslagsdata er sortert, AZ
- Returnerer alltid en omtrentlig kamp (hvis eksakt samsvar ikke finnes)
Likevel kan LOOKUP brukes til å løse dette problemet pent slik:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
som forenkler å:
=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 situasjonen ikke oppfyller begge kravene, er ikke SUMIFS et godt valg.
