Jedním z problémů, které se v aplikaci Excel objevují hodně, je počítání nebo sčítání na základě více podmínek NEBO. Například možná potřebujete analyzovat data a počítat objednávky v Seattlu nebo Denveru u položek, které jsou červené, modré nebo zelené? To může být překvapivě složité, takže to přirozeně dělá dobrou výzvu!
Výzva
Níže uvedená data představují objednávky, jedna objednávka na řádek. Existují tři samostatné výzvy.
Jaké vzorce ve F9, G9 a H9 budou správně počítat objednávky s následujícími podmínkami:
- F9 - tričko nebo mikina
- G9 - (tričko nebo mikina) a (červená, modrá nebo zelená)
- H9 - (tričko nebo mikina) a (červená, modrá nebo zelená) a (Denver nebo Seattle)
Zelené stínování se aplikuje s podmíněným formátováním a označuje odpovídající hodnoty pro každou sadu kritérií NEBO v každém sloupci.
Pro vaše pohodlí jsou k dispozici následující pojmenované rozsahy:
item = B3: B16
color = C3: C16
city = D3: D16
List je přiložen. Níže nechte své odpovědi jako komentáře!
Odpověď (kliknutím rozbalíte)Moje řešení používá 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)) )
Které budou počítat objednávky, kde …
- Položka je (tričko nebo mikina) a
- Barva je (červená, modrá nebo zelená) a
- Město je (Denver nebo Seattle)
Několik lidí také navrhlo stejný přístup. Líbí se mi tato struktura, protože se snadno rozšiřuje, aby zvládla více kritérií, a také pracuje s odkazy na buňky (místo pevně zakódovaných hodnot). S odkazy na buňky je vzorec v H9:
=SUMPRODUCT( ISNUMBER(MATCH(item,F3:F4,0))* ISNUMBER(MATCH(color,G3:G5,0))* ISNUMBER(MATCH(city,H3:H4,0)) )
Klíčem k tomuto vzorci je konstrukce ISNUMBER + MATCH. MATCH je nastavení „zpět“ - vyhledávací hodnoty pocházejí z dat a pro pole se používají kritéria. Výsledkem je pole jednoho sloupce při každém použití MATCH. Toto pole obsahuje buď # N / A chyby (žádná shoda) nebo čísla (shoda), takže ISNUMBER se používá k převodu na booleovské hodnoty TRUE a FALSE. Operace násobení polí společně donutí hodnoty TRUE FALSE na 1 s a 0 s a konečné pole uvnitř SUMPRODUCT obsahuje 1 s, kde řádky splňují kritéria. SUMPRODUCT poté sečte pole a vrátí výsledek.
