V našem článku Počítat buňky obsahující konkrétní text jsme spočítali každou buňku, která obsahuje konkrétní text. V tomto článku se naučíme, jak počítat buňky, které obsahují buď tu či onu hodnotu. Jinými slovy, počítání s logikou NEBO.
Můžete si myslet, že můžete použít funkci COUNTIF dvakrát a poté je sečíst. Ale to je špatný směr. Budeš vědět proč.
Obecný vzorec
= SUMPRODUCT (-((ISNUMBER (SEARCH ("this", range)) + ISNUMBER (SEARCH ("that", range)))> 0))
"Tento": je to první text, který chcete počítat v rozsahu. Může to být libovolný text.
"Že": je to druhý text, který chcete počítat v dosahu. Může to být libovolný text.
Rozsah: Toto je rozsah nebo pole obsahující text, ve kterém budete počítat pro své konkrétní texty.
Podívejme se na příklad:
Příklad:
Zde mám připomínky v rozsahu A2 až A8. Chci spočítat počet komentářů obsahujících „dobrá“ nebo „hezká“ slova.
Chcete -li počítat buňky obsahující konkrétní texty „dobré“ nebo „pěkné“, napište tento vzorec:
= SUMPRODUCT (-((ISNUMBER (HLEDAT („dobré“, A2: A8)) + ISNUMBER (HLEDAT („pěkné“, A2: A8)))> 0))
Vidíte, že existují čtyři komentáře, které obsahují buď „dobré“ nebo „pěkné“.
Jak to funguje?
Začněme zevnitř.
VYHLEDÁVÁNÍ („dobré“, A2: A8): tato část vrací pole chyby #VALUE a čísla, která představují polohu nalezeného textu. {8; #HODNOTA!;#HODNOTA!;#HODNOTA!;#HODNOTA!; 30; 24}
ISNUMBER (SEARCH („dobrý“, A2: A8)): tato část vzorce kontroluje každou hodnotu v poli return pomocí funkce SEARCH, zda je číslo nebo ne, a vrací pole TRUE a FALSE. V tomto případě vrátí hodnotu {TRUE; FALSE; FALSE; FALSE; FALSE; TRUE; TRUE}.
ISNUMBER (SEARCH („pěkné“, A2: A8)): tato část vzorce dělá totéž, ale tentokrát hledá „pěkné“ slovo v buňce a vrací pole báze PRAVDA a NEPRAVDA v buňce obsahuje „pěkné“ . ”
Dále jsme přidali tato pole pomocí znaménka +. Boolean hodnoty do 1 a 0 a sečte je. Interně to bude vypadat takto: {1; 0; 0; 0; 0; 1; 1}+{0; 1; 0; 0; 0; 1; 0} = {1; 1; 0; 0; 0; 2; 1}.
Můžete vidět, že komentář v A7 má jak texty „pěkné“, tak „dobré“. A tady se to započítává dvakrát. My to nechceme. Provedeme tedy další srovnání pro převod každého čísla na True a False.
((ISNUMBER (SEARCH („dobrý“, A2: A8)) + ISNUMBER (SEARCH („pěkný“, A2: A8)))>0): Tato část vzorce převede výsledné pole na PRAVDA a NEPRAVDA. Pokud je hodnota větší než 0, je TRUE else FALSE. Tady to bude {TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE}.
Číslo TRUE v poli je název řetězce obsahujícího „dobrá“ nebo „hezká“ slova.
SUMPRODUCT (-((ČÍSLO (HLEDAT („dobré“, A2: A8)) + ISNUMBER (HLEDAT („pěkné“, A2: A8)))> 0))
Dále použijeme - záporné symboly k jejich převedení na čísla. A nakonec SUMPRODUCT shrnuje pole a vrací počet buněk obsahujících „dobrý“ nebo „pěkný“.
Můžete také použít funkci SUMA, ale pak budete muset tento vzorec zadat jako maticový vzorec.
Proč nevyužít COUNTIFS?
Kvůli dvojímu počtu.
Pokud buňka obsahuje oba texty, bude započítána dvakrát, což v tomto scénáři není správné.
Ale pokud chcete, aby se to stalo, použijte tento vzorec,
= SUM (COUNTIFS (A2: A8, {"*nice*", "*good*"}))
V našem příkladu vrátí 5. Tady jsem to vysvětlil.
Počítání rozlišující malá a velká písmena
Navrhované řešení počítá daný text bez ohledu na případ písmen. Pokud chcete počítat shody rozlišující malá a velká písmena, nahraďte funkci SEARCH funkcí FIND.
= SUMPRODUCT (-((ISNUMBER (NALÉZT("toto", rozsah)) + ISNUMBER (NALÉZT(„to“, rozsah)))> 0))
TheNALÉZT funkce je funkce rozlišující malá a velká písmena. Vrátí pozici nalezeného textu.
Takže jo, lidi, takhle můžete spočítat počet buněk, které obsahují buď tento text nebo tamto. Chcete -li si o této funkci přečíst, můžete také kliknout na názvy funkcí ve vzorci. Srozumitelně jsem je rozpracoval.
Jak zkontrolovat, zda buňka obsahuje konkrétní text v aplikaci Excel
Jak zkontrolovat seznam textů v řetězci v aplikaci Excel
Získejte v aplikaci Excel shodu dvou kritérií COUNTIFS
Získejte COUNTIFS s NEBO pro více kritérií v aplikaci Excel
Oblíbené č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.