Obecný vzorec
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
souhrn
Chcete-li testovat buňku a zjistit, zda obsahuje všechny položky v seznamu, můžete tak učinit pomocí vzorce, který používá funkci HLEDAT, s pomocí funkcí ISNUMBER, SUMPRODUCT a COUNTA.
Kontext
Řekněme, že máte seznam textových řetězců v rozsahu B5: B8 a chcete zjistit, zda tyto buňky obsahují všechna slova v jiném rozsahu, E5: E7.
Můžete vytvořit vzorec, který používá vnořené příkazy IF ke kontrole každé položky, ale to nebude dobře škálovat, pokud máte spoustu věcí, které byste měli hledat. Pokaždé, když přidáte slovo, které hledáte, musíte přidat další vnořený IF a upravit závorky.
Řešení
Řešením je vytvořit vzorec počítá všechny shody najednou. Jakmile to máme, jednoduše porovnáme tento počet s počtem položek, které hledáme. Pokud se shodují, víme, že buňka obsahuje všechny položky.
V zobrazeném příkladu je vzorec, který používáme:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Vysvětlení
Klíčem je tento úryvek:
ISNUMBER(SEARCH(things,B5)
Toto je založeno na jiném vzorci (podrobně vysvětleném zde), který jednoduše kontroluje buňku na jediný podřetězec. Pokud buňka obsahuje podřetězec, vrátí vzorec hodnotu TRUE. Pokud ne, vrátí vzorec FALSE.
Pokud však stejnému vzorci dáme seznam věcí (v tomto případě používáme pojmenovaný rozsah s názvem „things“, E5: E7), vrátí nám seznam hodnot TRUE / FALSE, jeden pro každou položku v . Výsledkem je pole, které vypadá takto:
(PRAVDA; PRAVDA; PRAVDA)
Kde každá PRAVDA představuje nalezenou položku a každá NEPRAVDA představuje položku nenalezenou.
Hodnoty TRUE / FALSE můžeme vynutit na 1 s a 0 s dvojitým záporem (-, také nazývaným dvojitý unár):
--ISNUMBER(SEARCH(things,B5))
což dává takové pole:
(1; 1; 1)
Dále zpracujeme toto pole pomocí SUMPRODUCT, což nám dá celkovou částku. Pokud se tento součet rovná počtu položek v pojmenovaném rozsahu „things“, víme, že jsme našli všechny věci a můžeme vrátit TRUE. Způsob, jakým to děláme, je přímé porovnání obou čísel. Dostaneme počet neprázdných buněk ve „věcech“ pomocí COUNTA:
=COUNTA(things)
S pevně zakódovaným seznamem
Neexistuje žádný požadavek, abyste pro svůj seznam věcí použili rozsah. Pokud hledáte jen malý počet věcí, můžete použít seznam ve formátu pole, který se nazývá konstanta pole. Pokud například hledáte pouze červené, modré a zelené barvy, můžete použít („červená“, „modrá“, „zelená“) takto:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
