Zhrnutie
V určitých prípadoch môžete na získanie číselnej hodnoty použiť SUMIFS ako vyhľadávací vzorec. V zobrazenom príklade je vzorec v G6:
=SUMIFS(sales,region,G4,quarter,G5)
kde región (B5: B20), štvrťrok (C5: C20) a tržby (D5: D20) sú pomenované rozsahmi.
Výsledkom sú tržby za 3. štvrťrok v centrálnom regióne, 127 250.
Vysvetlenie
Ak ste vo funkcii SUMIFS nováčikom, nájdete tu základný prehľad s mnohými príkladmi.
Funkcia SUMIFS je navrhnutá na sčítanie číselných hodnôt na základe jedného alebo viacerých kritérií. V špecifických prípadoch však môžete byť schopní použiť SUMIFS na „vyhľadanie“ číselnej hodnoty, ktorá spĺňa požadované kritériá. Hlavnými dôvodmi sú jednoduchosť a rýchlosť.
V zobrazenom príklade máme štvrťročné údaje o predaji pre štyri regióny. Začneme tým, že zadáme SUMIFS rozsah súčtov a prvú podmienku, ktorá testuje oblasť na hodnotu v G4, „stredná“:
=SUMIFS(sales,region,G4 // sum range, region is "Central"
- Rozsah súčtov predstavuje tržby (D5: D20)
- Rozsah kritérií 1 je oblasť (B5: B20)
- Kritérium 1 je G4 („centrálne“)
Potom pridáme druhý pár rozsah / kritériá, ktorý kontroluje štvrťrok:
=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ériami vráti SUMIFS 127 250, čo je centrálne predajné číslo Q3.
Správanie SUMIFS je spočítať všetky zodpovedajúce hodnoty. Pretože však existuje iba jedna zhodná hodnota, výsledok je rovnaký ako samotná hodnota.
Ďalej sa pozrieme na niekoľko možností vyhľadávacieho vzorca.
Možnosti vyhľadávacieho vzorca
Táto časť stručne popisuje ďalšie možnosti vzorca, ktoré poskytujú rovnaký výsledok. S výnimkou SUMPRODUCT (dole) sú to tradičnejšie vyhľadávacie vzorce, ktoré lokalizujú polohu cieľovej hodnoty a vrátia hodnotu na danom mieste.
Vďaka VLOOKUP
VLOOKUP bohužiaľ nie je dobrým riešením tohto problému. Pomocou pomocného stĺpca je možné vytvoriť vzorec VLOOKUP tak, aby zodpovedal viacerým kritériám (príklad tu), ale je to nepríjemný proces, ktorý vyžaduje, aby ste si pohrali so zdrojovými údajmi.
S INDEXOM a ZÁPASOM
INDEX a MATCH je veľmi flexibilná kombinácia vyhľadávania, ktorú je možné použiť na všetky druhy problémov s vyhľadávaním, a tento príklad nie je výnimkou. Pomocou indexov INDEX a MATCH môžeme vyhľadávať tržby podľa regiónu a štvrťroka pomocou vzoru poľa, ako je tento:
(=INDEX(sales,MATCH(1,(region=G4)*(quarter=G5),0)))
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Trik v tomto prístupe spočíva v použití logickej logiky s operáciami poľa vo vnútri funkcie MATCH na zostavenie poľa 1s a 0s ako vyhľadávacieho poľa. Potom môžeme požiadať funkciu MATCH, aby našla číslo 1. Po vytvorení vyhľadávacieho poľa vzorec vyrieši:
=INDEX(sales,MATCH(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),0))
Keď vo vyhľadávacom poli zostane iba 1, MATCH vráti pozíciu 11 funkcii INDEX a INDEX vráti predajné číslo na tejto pozícii, 127 250.
Viac podrobností nájdete na: INDEX a MATCH s viacerými kritériami
S XLOOKUPOM
XLOOKUP je nová flexibilná funkcia v programe Excel, ktorá dokáže natívne spracovať polia. V prípade XLOOKUP môžeme použiť úplne rovnaký prístup ako v prípade INDEX a MATCH, pričom na vytvorenie vyhľadávacieho poľa môžeme použiť logickú logiku a operácie s poľami:
=XLOOKUP(1,(region=G4)*(quarter=G5),sales)
Po spustení operácií poľa sa vzorec vyrieši takto:
=XLOOKUP(1,(0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0),sales)
A XLOOKUP vráti rovnaký výsledok ako vyššie, 127 250.
Viac: XLOOKUP s viacerými kritériami
S VYHĽADÁVANÍM
Funkcia LOOKUP je staršou funkciou v programe Excel, o ktorej mnoho ľudí ani nevie. Jednou z kľúčových predností LOOKUPU je, že dokáže natívne spracovať polia. LOOKUP má však niekoľko výrazných slabin:
- Nedá sa uzamknúť v režime „presnej zhody“
- Vždy predpokladá, že vyhľadávacie údaje sú zoradené, AZ
- Vždy vráti približnú zhodu (ak nie je možné nájsť presnú zhodu)
LOOKUP sa dá napriek tomu použiť na pekné vyriešenie tohto problému takto:
=LOOKUP(2,1/((region=G4)*(quarter=G5)),sales)
čo zjednodušuje na:
=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
Ak situácia nespĺňa obidve požiadavky, SUMIFS nie je dobrou voľbou.
