Všeobecný vzorec
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)
Zhrnutie
Ak chcete otestovať, či bunka obsahuje všetky položky v zozname, môžete to urobiť pomocou vzorca, ktorý používa funkciu VYHĽADÁVANIE, s pomocou funkcií ISNUMBER, SUMPRODUCT a COUNTA.
Kontext
Povedzme, že máte zoznam textových reťazcov v rozsahu B5: B8 a chcete zistiť, či tieto bunky obsahujú všetky slová v inom rozsahu, E5: E7.
Môžete vytvoriť vzorec, ktorý pomocou vnorených príkazov IF skontroluje každú položku, ale nebude mať mierku, ak by ste mali hľadať veľa vecí. Zakaždým, keď pridáte hľadané slovo, musíte pridať ďalšie vnorené IF a upraviť zátvorky.
Riešenie
Riešením je vytvorenie vzorca, ktorý počíta všetky zápasy naraz. Keď to máme, jednoducho porovnáme tento počet s počtom položiek, ktoré hľadáme. Ak sa zhodujú, vieme, že bunka obsahuje všetky položky.
V zobrazenom príklade je použitý vzorec:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)
Vysvetlenie
Kľúčom je tento úryvok:
ISNUMBER(SEARCH(things,B5)
Toto je založené na inom vzorci (podrobne vysvetlenom tu), ktorý jednoducho kontroluje bunku na jediný podreťazec. Ak bunka obsahuje podreťazec, vráti vzorec hodnotu TRUE. Ak nie, vzorec vráti FALSE.
Ak však rovnakému vzorcu dáme zoznam vecí (v tomto prípade používame pomenovaný rozsah s názvom „things“, E5: E7), vráti nám zoznam hodnôt TRUE / FALSE, jednu pre každú položku v . Výsledkom je pole, ktoré vyzerá takto:
(PRAVDA; PRAVDA; PRAVDA)
Kde každé TRUE predstavuje nájdenú položku a každé FALSE predstavuje položku, ktorá sa nenašla.
Hodnoty TRUE / FALSE môžeme vynútiť na 1 s a 0 s dvojitým záporom (-, ktorý sa tiež nazýva dvojitý unár):
--ISNUMBER(SEARCH(things,B5))
ktorý dáva pole ako je toto:
(1; 1; 1)
Ďalej spracujeme toto pole s SUMPRODUCT, ktorý nám dá celkovú sumu. Ak sa tento súčet rovná počtu položiek v pomenovanom rozsahu „things“, vieme, že sme našli všetky veci a môžeme vrátiť TRUE. Spôsob, akým to urobíme, je priame porovnanie týchto dvoch čísel. Získame počet neprázdnych buniek vo „veciach“ pomocou funkcie COUNTA:
=COUNTA(things)
S pevne zakódovaným zoznamom
Nie je potrebné, aby ste pre svoj zoznam vecí používali rozsah. Ak hľadáte iba malý počet vecí, môžete použiť zoznam vo formáte poľa, ktorý sa nazýva konštanta poľa. Napríklad, ak hľadáte iba červenú, modrú a zelenú farbu, môžete použiť farbu („červená“, „modrá“, „zelená“) takto:
=SUMPRODUCT(--ISNUMBER(SEARCH(("yellow","green","dog"),B5)))=COUNTA(things)
