souhrn
V určitých případech můžete použít SUMIFS jako vyhledávací vzorec k načtení číselné hodnoty. V zobrazeném příkladu je vzorec v G6:
=SUMIFS(sales,region,G4,quarter,G5)
kde region (B5: B20), čtvrtina (C5: C20) a tržby (D5: D20) jsou pojmenovány rozsahy.
Výsledkem je prodej Q3 pro střední region, 127 250.
Vysvětlení
Pokud jste ve funkci SUMIFS nováčkem, najdete zde základní přehled s mnoha příklady.
Funkce SUMIFS je navržena k součtu číselných hodnot na základě jednoho nebo více kritérií. V konkrétních případech však můžete použít SUMIFS k „vyhledání“ číselné hodnoty, která splňuje požadovaná kritéria. Hlavním důvodem je jednoduchost a rychlost.
V zobrazeném příkladu máme čtvrtletní údaje o prodeji pro čtyři regiony. Začneme tím, že SUMIFS zadáme rozsah součtu a první podmínku, která testuje oblast na hodnotu v G4, „Central“:
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Rozsah součtu je prodej (D5: D20)
- Rozsah kritérií 1 je region (B5: B20)
- Kritérium 1 je G4 („centrální“)
Potom přidáme druhý pár rozsah / kritéria, který kontroluje čtvrtinu:
=SUMIFS(sales,region,G4,quarter,G5) // and quarter is "Q3"
- Rozsah kritérií 2 je čtvrtinový (C5: C20)
- Kritérium 2 je G5 („Q3“)
S těmito kritérii vrátí SUMIFS 127 250, prodejní číslo Central Q3.
Chování SUMIFS je spočítat všechny odpovídající hodnoty. Protože však existuje pouze jedna odpovídající hodnota, je výsledek stejný jako samotná hodnota.
Níže se podíváme na několik možností vyhledávacího vzorce.
Možnosti vyhledávacího vzorce
Tato část stručně kontroluje další možnosti vzorce, které přinášejí stejný výsledek. S výjimkou SUMPRODUCT (dole) se jedná o tradičnější vyhledávací vzorce, které lokalizují pozici cílové hodnoty a vracejí hodnotu na daném místě.
S VLOOKUP
VLOOKUP bohužel není dobrým řešením tohoto problému. S pomocným sloupcem je možné sestavit vzorec VLOOKUP tak, aby odpovídal více kritériím (příklad zde), ale je to nepříjemný proces, který vyžaduje, abyste se pohnuli se zdrojovými daty.
S INDEXEM a ZÁPASEM
INDEX a MATCH je velmi flexibilní kombinace vyhledávání, kterou lze použít pro všechny druhy problémů s vyhledáváním, a tento příklad není výjimkou. S INDEXEM a MATCH můžeme vyhledávat tržby podle regionů a čtvrtletí pomocí maticového vzorce, jako je tento:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Trik s tímto přístupem spočívá v použití logické logiky s operacemi pole uvnitř funkce MATCH k vytvoření pole 1s a 0s jako vyhledávací pole. Pak můžeme požádat funkci MATCH, aby našla číslo 1. Jakmile je vyhledávací pole vytvořeno, vzorec se vyřeší na:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Když ve vyhledávacím poli zbývá jen 1, MATCH vrátí pozici 11 funkci INDEX a INDEX vrátí prodejní číslo na této pozici, 127 250.
Další podrobnosti viz: INDEX a MATCH s více kritérii
S XLOOKUPEM
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
Pokud situace nesplňuje oba požadavky, SUMIFS není dobrá volba.
