VZOREC PROGRAMU EXCEL: SUMIFS VS. OSTATNÉ VZORCE VYHĽADÁVANIA

Zhrnutie

V niektorých prípadoch môžete na získanie číselnej hodnoty použiť SUMIFS ako vyhľadávací vzorec. V uvedenom príklade vzorec v G6 je:

 = SUMIFS (sales,region,G4,quarter,G5)

kde regiónu (B5: B20), štvrťrok (C5: C20) a predaj (D5: D20) sú pomenované rozsahy .

Výsledkom sú tržby za 3. štvrťrok pre stredný región, 127 250.

Vysvetlenie

Ak ste vo funkcii SUMIFS noví, môžete nájsť a základný prehľad s mnohými príkladmi tu .

Funkcia SUMIFS je navrhnutá na súčet číselných hodnôt na základe jedného alebo viacerých kritérií. V špecifických prípadoch však môžete použiť SUMIFS na „vyhľadanie“ číselnej hodnoty, ktorá spĺňa požadované kritériá. Hlavnými dôvodmi, prečo to urobiť, sú jednoduchosť a rýchlosť.

V uvedenom príklade máme štvrťročné údaje o predaji pre štyri regióny. Začneme tým, že SUMIFS dáme rozsah súčtov a prvú podmienku, ktorá testuje oblasť na hodnotu v G4, „stred“:

 = SUMIFS (sales,region,G4 // sum range, region is 'Central'

Rozsah súčtov je predaj (D5: D20)
Rozsah kritérií 1 je regiónu (B5: B20)
Kritérium 1 je G4 („centrálny“)

Potom pridáme druhý pár rozsahov/kritérií, ktorý kontroluje štvrťrok:

ako uchovať sig figy v exceli

 = SUMIFS (sales,region,G4,quarter,G5) // and quarter is 'Q3'

Rozsah kritérií 2 je štvrťrok (C5: C20)
Kritérium 2 je G5 („Q3“)

Pri týchto kritériách vráti SUMIFS 127 250, centrálne predajné číslo Q3.

Správanie SUMIFS je súčet všetky zodpovedajúce hodnoty . Pretože však existuje len jedna zhodná hodnota , výsledok je rovnaký ako samotná hodnota.

Ďalej sa pozrieme na niekoľko možností vzorcov vyhľadávania.

Možnosti vyhľadávacieho vzorca

Táto časť stručne hodnotí ďalšie možnosti vzorca, ktoré poskytujú rovnaký výsledok. S výnimkou SUMPRODUCT (v spodnej časti) ide o tradičnejšie vzorce vyhľadávania, ktoré vyhľadávajú polohu cieľovej hodnoty a vracajú hodnotu na tomto mieste.

S funkciou 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, ktorý bude zodpovedať viacerým kritériám ( príklad tu ), ale je to nepríjemný proces, ktorý vyžaduje, aby ste sa pohrávali so zdrojovými údajmi.

S INDEX a MATCH

INDEX a ZHODA 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. S indexami INDEX a MATCH môžeme vyhľadávať tržby podľa regiónov a štvrťrokov pomocou nasledujúceho vzorca:

 {= INDEX (sales, MATCH (1,(region=G4)*(quarter=G5),0))}

Poznámka: toto je an maticový vzorec , a je potrebné ho zadať pomocou ovládača + shift + enter.

Trik s týmto prístupom je použiť booleovská logika s operáciami poľa vo funkcii MATCH na vytvorenie poľa 1 s a 0 s ako vyhľadávacieho poľa. Potom sa môžeme opýtať Funkcia MATCH nájdite číslo 1. Po vytvorení vyhľadávacieho poľa sa vzorec vyrieši takto:

počet dní medzi dvoma dátumami vyniká

 = INDEX (sales, MATCH (1,{0000000000100000},0))

Keďže vo vyhľadávacom poli zostáva iba 1, funkcia MATCH vráti pozíciu 11 do súboru Funkcia 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 XLOOKUP

XLOOKUP je flexibilná nová funkcia v programe Excel, ktorá dokáže natívne spracovávať polia. S XLOOKUP môžeme použiť úplne rovnaký prístup ako s INDEX a MATCH, pričom na vytvorenie vyhľadávacieho poľa použijeme boolovskú logiku a maticové operácie:

 = XLOOKUP (1,(region=G4)*(quarter=G5),sales)

Po spustení operácií poľa sa vzorec vyrieši takto:

 = XLOOKUP (1,{0000000000100000},sales)

A XLOOKUP vráti rovnaký výsledok ako vyššie, 127 250.

Viac: XLOOKUP s viacerými kritériami

Vzorce dynamického poľa sú k dispozícii v Office 365 iba.

So ZOBRAZENÍM

The Funkcia LOOKUP je staršia funkcia v Exceli, o ktorej veľa ľudí ani nevie. Jednou z kľúčových silných stránok LOOKUP je, že dokáže spracovať polia natívne. LOOKUP má však niekoľko výrazných slabých stránok:

Nedá sa uzamknúť v „režime presnej zhody“
Vždy predpokladá, že údaje vyhľadávania sú zoradené, A-Z
Vždy vráti približnú zhodu (ak presnú zhodu nemožno nájsť)

Napriek tomu je možné LOOKUP použiť na vyriešenie tohto problému pekne takto:

 = LOOKUP (2,1/((region=G4)*(quarter=G5)),sales)

čo zjednodušuje:

 = 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)

Ak sa pozriete pozorne, v mori #DIV/0 uvidíte jediné číslo 1! chyby. Predstavuje hodnotu, ktorú chceme získať.

Používame vyhľadávaciu hodnotu 2, pretože nemôžeme zaručiť, že je pole zoradené. Preto prinútime všetky nezhodné riadky k chybám a požiadame LOOKUP, aby našiel 2. LOOKUP chyby ignoruje a poslušne prehľadá celé pole a hľadá 2. Ak číslo 2 nemožno nájsť, LOOKUP „zazálohuje“ a sa zhoduje s poslednou hodnotou bez chyby, ktorá je 1 na 11. pozícii. Výsledok je rovnaký ako vyššie, 127 250.

Podrobnejšie vysvetlenie tu .

S SUMPRODUCT

Ako obvykle môžete použiť aj švajčiarsky nôž Funkcia SUMPRODUCT vyriešiť aj tento problém. Ide o to, použiť booleovskú logiku a maticové operácie na „vynulovanie“ všetkých okrem jednej hodnoty, ktorú chceme:

 = SUMPRODUCT (sales*((region=G4)*(quarter=G5)))

Po dokončení matematiky poľa v programe SUMPRODUCT sa vzorec zjednoduší na:

 = SUMPRODUCT ({000000000012725000000})

Toto nie je technicky vzorec na vyhľadávanie, ale správa sa tak. Len s jedným poľom na spracovanie funkcia SUMPRODUCT vráti súčet poľa 12 7250.

Viď tento príklad pre úplnejšie vysvetlenie.

Excel extrahuje jedinečné položky zo zoznamu

V duchu je možnosť SUMPRODUCT najbližšie k vzorcu SUMIFS, pretože sme zhrnutie hodnoty založené na viacerých kritériách. Rovnako ako predtým funguje dobre, ak existuje iba jeden zodpovedajúci výsledok.

Zhrnutie

SUMIF možno skutočne použiť ako vyhľadávací vzorec a konfigurácia môže byť jednoduchšia ako bežnejší vyhľadávací vzorec. Navyše, ak pracujete s veľkým súborom údajov, SUMIFS bude veľmi rýchla možnosť. Musíte však mať na pamäti dve kľúčové požiadavky: