Excel vzorec: Zkontrolujte, zda buňka obsahuje mnoho věcí
Předpokládejme, že v aplikaci Excel existuje seznam hodnot ve sloupci E, chcete zkontrolovat, zda buňky ve sloupci B obsahují všechny hodnoty ve sloupci E, a vrátit TRUE nebo FALSE, jak je uvedeno níže. Tady v tomto kurzu poskytuje vzorec pro řešení tohoto úkolu.
Obecný vzorec:
| =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) |
Argumenty
| Things: the list of values that you want to use to check if argument text contains. |
| Text: the cell or text string you want to check if containing argument things. |
Návratová hodnota:
Tento vzorec vrací logickou hodnotu, FALSE: buňka neobsahuje všechny věci; PRAVDA cell buňka obsahuje všechny věci.
Jak tento vzorec funguje
Například ve sloupci B je seznam textových řetězců, které chcete zkontrolovat, zda obsahují všechny hodnoty v rozsahu E3: E5, použijte níže uvedený vzorec.
| =SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))=COUNTA($E$3:$E$5) |
Stisknout vstoupit klíč, přetáhněte úchyt výplně přes buňky, které chcete zkontrolovat. FALSE označuje, že buňka neobsahuje všechny hodnoty v rozsahu E3: E5, zatímco TURE označuje, že relativní buňka obsahuje všechny hodnoty.
Vysvětlení
Funkce SEARCH: funkce SEARCH vrátí umístění prvního znaku textového řetězce uvnitř jiného. Pokud funkce SEARCH najde text, vrátí pozici, pokud ne, vrátí #HODNOTA! chyba. Například zde vzorec SEARCH($E$3:$E$5,B4) prohledá každou hodnotu rozsahu E3: E5 v buňce B4 a vrátí umístění každého textového řetězce v buňce B4. Vrátí výsledek pole takto: {1;7;12}
Funkce ISNUMBER: funkce ISNUMBER zkontroluje, zda je hodnota číslo, a vrátí TRUE o False. Tady ISNUMBER(SEARCH($E$3:$E$5,B4)) vrátí výsledek pole jako {true;true;true} protože funkce SEARCH najde 3 čísla.
--ISNUMBER(SEARCH($E$3:$E$5,B4)) převede TRUE hodnotu na 1 a převede FALSE hodnotu na 0, takže tento vzorec změní výsledek pole na {1;1;1}.
Funkce SUMPRODUCT: slouží k násobení rozsahů nebo součtu polí dohromady a vrací součet produktů. The SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) vrátí 1 + 1 + 1 = 3.
Funkce COUNTA: vrací počet neprázdných buněk. COUNTA($E$3:$E$5) vrátí 3, takže výsledek SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) je rovno 3, celý vzorec vrátí TRUE nebo vrátí FALSE.
Poznámky:
Vzorec =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) přesně nekontroluje. Viz snímek obrazovky:
Ukázkový soubor
Kliknutím stáhnete ukázkový soubor
Relativní vzorce
- Počet buněk roven
Pomocí funkce COUNTIF můžete počítat buňky, které se rovnají nebo neobsahují zadanou hodnotu. - Počítat buňky, které se rovnají x nebo y
V některých případech můžete chtít spočítat počet buněk, které splňují jedno ze dvou kritérií, v tomto případě můžete použít funkci COUNTIF. - Počet buněk rovných xay
Zde tento článek představuje vzorec pro počítání buněk, které mezitím odpovídají dvěma kritériím. - Počet buněk se nerovná
Tento článek hovoří o tom, jak pomocí funkce COUNTIF spočítat počet buněk, který se nerovná konkrétní hodnotě
Nejlepší kancelářské nástroje produktivity
Kutools pro Excel - pomůže vám vyniknout před davem
Kutools pro Excel se může pochlubit více než 300 funkcemi, Zajištění toho, že to, co potřebujete, je jen jedno kliknutí...
Záložka Office - Povolte čtení a úpravy na záložkách v Microsoft Office (včetně Excelu)
- Jednu sekundu přepnete mezi desítkami otevřených dokumentů!
- Snižte stovky kliknutí myší každý den, sbohem s myší rukou.
- Zvyšuje vaši produktivitu o 50% při prohlížení a úpravách více dokumentů.
- Přináší efektivní karty do Office (včetně Excelu), stejně jako Chrome, Edge a Firefox.
