Obecný vzorec
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
souhrn
Chcete-li otestovat buňku pro jeden z mnoha řetězců a zároveň vyloučit ostatní, můžete použít vzorec založený na funkcích SEARCH, ISNUMBER a SUMPRODUCT. V zobrazeném příkladu je vzorec v C5:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
kde „zahrnout“ je pojmenovaný rozsah E5: E9 a „vyloučit“ je pojmenovaný rozsah G5: G6.
Vysvětlení
Tento vzorec v jádru používá funkci HLEDAT k hledání více řetězců uvnitř buňky. Uvnitř levého SUMPRODUCT SEARCH hledá všechny řetězce v pojmenovaném rozsahu „include“.
V pravém SUMPRODUCT SEARCH hledá všechny řetězce v pojmenovaném rozsahu „vyloučit“.
V obou částech vzorce vrátí funkce SEARCH číselné pozice, když jsou nalezeny řetězce, a chyby, pokud nejsou. Funkce ISNUMBER převede čísla na TRUE a chyby na FALSE a dvojitý zápor převede hodnoty TRUE FALSE na 1 a 0.
Výsledek v tomto okamžiku vypadá takto:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Pak:
=(1>0)*(0=0) =TRUE*TRUE =1
Poznámka: tento vzorec vrací buď 1, nebo nulu, s nimiž se ve vzorcích, podmíněném formátování nebo ověření dat zachází jako s TRUE a FALSE.
