Santrauka
Tam tikrais atvejais galite naudoti SUMIFS, pavyzdžiui, paieškos formulę, kad gautumėte skaitinę vertę. Parodytame pavyzdyje formulė G6 yra:
=SUMIFS(sales,region,G4,quarter,G5)
kur regionai (B5: B20), ketvirtis (C5: C20) ir pardavimai (D5: D20) įvardijami kaip diapazonai.
Rezultatas - trečiojo ketvirčio centrinio regiono pardavimai - 127 250.
Paaiškinimas
Jei dar nesinaudojote funkcija SUMIFS, pagrindinę apžvalgą su daugybe pavyzdžių galite rasti čia.
Funkcija SUMIFS sukurta susumuoti skaitines vertes pagal vieną ar daugiau kriterijų. Tačiau tam tikrais atvejais galite naudoti SUMIFS, norėdami „ieškoti“ skaitinę vertę, atitinkančią reikalaujamus kriterijus. Pagrindinės priežastys tai padaryti yra paprastumas ir greitis.
Parodytame pavyzdyje turime ketvirčio pardavimo duomenis apie keturis regionus. Pradedame pateikdami SUMIFS sumų intervalą ir pirmąją sąlygą, kuri tikrina regiono vertę G4, „Centrinė“:
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Suma yra pardavimai (D5: D20)
- 1 kriterijų diapazonas yra regionas (B5: B20)
- 1 kriterijus yra G4 („centrinis“)
Tada pridedame antrąją diapazono / kriterijų porą, kuri tikrina ketvirtį:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- 2 kriterijų diapazonas yra ketvirtis (C5: C20)
- 2 kriterijus yra G5 („Q3“)
Remiantis šiais kriterijais, SUMIFS pateikia 127 250 - centrinio trečiojo ketvirčio pardavimo numerį.
SUMIFS elgsena yra visų atitinkančių verčių suma. Tačiau kadangi yra tik viena atitinkanti reikšmė, rezultatas yra toks pats kaip pati vertė.
Žemiau apžvelgiame keletą paieškos formulės parinkčių.
Paieškos formulės parinktys
Šiame skyriuje trumpai apžvelgiamos kitos formulės parinktys, kurios duoda tą patį rezultatą. Išskyrus „SUMPRODUCT“ (apačioje), tai yra labiau tradicinės paieškos formulės, kurios nustato tikslinės vertės padėtį ir grąžina vertę toje vietoje.
Su VLOOKUP
Deja, „VLOOKUP“ nėra geras šios problemos sprendimas. Naudojant pagalbinį stulpelį, galima sukurti VLOOKUP formulę, kuri atitiktų kelis kriterijus (pavyzdys čia), tačiau tai yra nepatogus procesas, reikalaujantis tinkinti šaltinio duomenis.
Su INDEX ir MATCH
„INDEX“ ir „MATCH“ yra labai lankstus paieškos derinys, kurį galima naudoti visoms paieškos problemoms spręsti, ir šis pavyzdys nėra išimtis. Naudodami „INDEX“ ir „MATCH“, galime ieškoti pardavimų pagal regioną ir ketvirtį naudodami tokią masyvo formulę:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Šio metodo gudrybė yra naudoti loginę logiką su masyvo operacijomis funkcijos MATCH viduje, kad sukurtumėte 1s ir 0s masyvą kaip paieškos masyvą. Tada galime paprašyti funkcijos MATCH rasti skaičių 1. Kai bus sukurtas paieškos masyvas, formulė išspręs:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Kai paieškos masyve lieka tik 1, MATCH funkcijai INDEX grąžina 11 poziciją, o INDEX grąžina tos vietos pardavimo numerį 127 250.
Norėdami gauti daugiau informacijos, žiūrėkite: INDEKSAS ir SUSITIKIMAS su keliais kriterijais
Su 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
Jei situacija neatitinka abiejų reikalavimų, SUMIFS nėra tinkamas pasirinkimas.
