Jedným z problémov, ktoré sa v programe Excel často vyskytujú, je počítanie alebo sčítanie na základe viacerých podmienok ALEBO. Možno napríklad potrebujete analyzovať údaje a spočítať objednávky v Seattli alebo Denveri pre položky, ktoré sú červené, modré alebo zelené? To môže byť prekvapivo zložité, takže to samozrejme predstavuje dobrú výzvu!
Výzva
Údaje uvedené nižšie predstavujú objednávky, jedna objednávka na riadok. Existujú tri samostatné výzvy.
Aké vzorce vo F9, G9 a H9 budú správne počítať objednávky za nasledujúcich podmienok:
- F9 - tričko alebo mikina s kapucňou
- G9 - (tričko alebo mikina) a (červená, modrá alebo zelená)
- H9 - (tričko alebo mikina) a (červená, modrá alebo zelená) a (Denver alebo Seattle)
Zelené tieňovanie sa aplikuje s podmieneným formátovaním a označuje zhodné hodnoty pre každú skupinu kritérií ALEBO v každom stĺpci.
Pre vaše pohodlie sú k dispozícii nasledujúce pomenované rozsahy:
položka = B3: B16
farba = C3: C16
mesto = D3: D16
Pracovný list je priložený. Vaše odpovede nechajte nižšie ako komentáre!
Odpoveď (kliknutím rozbalíte)Moje riešenie používa SUMPRODUCT s ISNUMBER a MATCH takto:
=SUMPRODUCT( ISNUMBER(MATCH(item,("Tshirt","Hoodie"),0))* ISNUMBER(MATCH(color,("Red","Blue","Green"),0))* ISNUMBER(MATCH(city,("Denver","Seattle"),0)) )
Ktoré budú počítať objednávky, kde …
- Položka je (tričko alebo mikina) a
- Farba je (červená, modrá alebo zelená) a
- Mesto je (Denver alebo Seattle)
Rovnaký prístup navrhlo aj niekoľko ľudí. Páči sa mi táto štruktúra, pretože sa dá ľahko škálovať, aby zvládla viac kritérií, a pracuje aj s odkazmi na bunky (namiesto pevne zakódovaných hodnôt). S odkazmi na bunky je vzorec v H9:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Kľúčom k tomuto vzorcu je konštrukcia ISNUMBER + MATCH. MATCH je nastavenie „dozadu“ - hodnoty vyhľadávania pochádzajú z údajov a pre pole sa používajú kritériá. Výsledkom je pole jedného stĺpca pri každom použití MATCH. Toto pole obsahuje buď # chýb N / A (žiadna zhoda) alebo čísla (zhoda), takže ISNUMBER sa používa na prevod na boolovské hodnoty TRUE a FALSE. Operácia násobenia polí spoločne vynúti hodnoty TRUE FALSE na 1 s a 0 s a konečné pole vo vnútri SUMPRODUCT obsahuje 1 s, kde riadky spĺňajú kritériá. SUMPRODUCT potom sčíta pole a vráti výsledok.
