V tomto článku se naučíme najít počet pomocí více kritérií s logikou ISNA v Excelu.
Scénář:
Jednoduše řečeno, při práci s dlouhými neřízenými daty. Potřebujeme extrahovat počet s více kritérii bez logických hodnot nebo hodnot, které se mají vyloučit, pomocí vzorce v aplikaci Excel.
Jak problém vyřešit?
Pro tento článek budeme potřebovat použít funkci MATCH & SUMPRODUCT. Nyní ze zmíněné funkce vytvoříme vzorec. Zde dáme seznam smíšených hodnot. Musíme najít počet hodnot, které splňují podmínky, a pomocí funkce ISNA hodnoty vyloučit.
Použijte vzorec:
| = SUMPRODUCT ((rozsah = kritéria) * (ISNA (MATCH (rng, cri_rng, 0))) |
range: first range to match first criteria
kritéria: kritéria pro shodu s prvním rozsahem
rng: druhý rozsah, kde je třeba vyloučit kritéria
cri_rng: hodnoty, které se mají vyloučit z druhého rozsahu
Příklad:
Pochopit to všechno může být matoucí. Pojďme tedy tento vzorec otestovat spuštěním na níže uvedeném příkladu.
Zde máme tabulku a musíme spočítat hodnoty odpovídající určitým ne logickým podmínkám. Podmínky jsou následující.
- Musí to být zaměstnankyně, takže pole pohlaví odpovídá F.
- Nesmí zahrnovat zaměstnance ze skupiny A a B.
Nyní použijeme níže uvedený vzorec k získání počtu hodnot v tabulce splňujících uvedená kritéria.
Vzorec:
| = SUMPRODUCT ((Pohlaví = H5) * (ISNA (MATCH (Skupiny, I5: I6, 0))) |
Vysvětlení:
- MATCH (Skupiny, I5: I6,0) odpovídá všem hodnotám skupiny s danými hodnotami kritérií a vrací pole čísel a hodnot #NA.
- Funkce MATCH vrací index pro odpovídající hodnoty a #NA! Chyba pro bezkonkurenční hodnoty.
- ISNA vrací PRAVDU hodnotu pro odpovídající odpovídající #NA! Hodnoty a FALSE pro vrácené hodnoty indexu.
- Pohlaví = H5, toto vrátí všechny odpovídající hodnoty jako PRAVDA a bezkonkurenční jako NEPRAVDA.
- Funkce SUMPRODUCT vrací počet odpovídajících hodnot PRAVDA odpovídajících oběma poli.
Zde je pole funkce uvedeno jako pojmenované rozsahy, jak je uvedeno ve žlutě zbarvených buňkách.
Pojmenované rozsahy použité ve vzorci:
Pohlaví (E4: E17)
ID (D4: D17)
Skupiny (F4: F17)
Hodnota jednoho argumentu poskytnuta jako odkaz na buňku. Stisknutím klávesy Enter získáte výsledek.
Jak můžete vidět na výše uvedeném snímku, počet vychází na 3. Existují 3 zaměstnanci, kteří jsou ženy a nepatří ani do skupiny A, ani do skupiny B.
Nyní můžeme výsledek zkontrolovat pomocí filtrování obsahu, viz níže uvedený gif. Stačí filtrovat pole tabulky a spočítat počet odpovídajících hodnot nebo k získání počtu můžete použít výše uvedený vzorec.
Zde jsou všechny pozorovací poznámky týkající se používání vzorce.
Poznámky:
- Vzorec funguje pouze s čísly a textovými hodnotami.
- Pole Gender a skupiny musí mít stejnou délku, jinak funkce SUMPRODUCT vrací chybu.
- Operátoři mají rádi ( = ), menší než rovno ( <= ), větší než ( > ) nebo není rovno () lze provést v rámci funkce aplikované pouze s čísly.
Doufám, že tento článek o tom, jak najít počet s více kritérii pomocí logiky ISNA v aplikaci Excel, je vysvětlující. Zde najdete další články o funkcích COUNTIF. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail
Najděte poslední řádek dat s čísly v Excelu : V řadě textových hodnot najděte poslední data v Excelu.
Jak používat funkci SUMPRODUCT v Excelu: Vrací SUMU po vynásobení hodnot ve více polích v Excelu.
COUNTIFS s rozsahem dynamických kritérií : Počítejte buňky, které se liší od ostatních hodnot buněk v aplikaci Excel.
COUNTIFS odpovídá dvěma kritériím : Počet buněk odpovídajících dvěma různým kritériím v seznamu v aplikaci Excel.
COUNTIFS S NEBO Pro více kritérií : Pomocí funkce NEBO spočítejte buňky s více kritérii.
Funkce COUNTIFS v aplikaci Excel : Počet buněk závislých na jiných hodnotách buněk.
Jak používat Countif ve VBA v Microsoft Excelu : Počítejte buňky pomocí kódu Visual Basic for Applications.
Jak používat zástupné znaky v aplikaci Excel : Počítejte buňky odpovídající frázím pomocí zástupných znaků v aplikaci Excel
Populární články
50 Zkratka aplikace Excel pro zvýšení produktivity : Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat tFunkce VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.