Všeobecný vzorec
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Zhrnutie
Ak chcete otestovať bunku na jeden z mnohých reťazcov a vylúčiť ďalšie, môžete použiť vzorec založený na funkciách VYHĽADÁVANIE, ISNUMBER a SUMPRODUCT. V zobrazenom príklade je vzorec v C5:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
kde „zahrnúť“ je pomenovaný rozsah E5: E9 a „vylúčiť“ je pomenovaný rozsah G5: G6.
Vysvetlenie
Tento vzorec v jadre používa funkciu VYHĽADÁVANIE na vyhľadanie viacerých reťazcov vo vnútri bunky. V ľavom SUMPRODUCT vyhľadáva SEARCH všetky reťazce v pomenovanom rozsahu „include“.
V pravom SUMPRODUCT vyhľadáva SEARCH všetky reťazce v pomenovanom rozsahu „vylúčiť“.
V oboch častiach vzorca vráti funkcia SEARCH číselné polohy, keď sa nájdu reťazce, a chyby, keď nie. Funkcia ISNUMBER prevádza čísla na TRUE a chyby na FALSE a dvojitý zápor prevádza hodnoty TRUE FALSE na 1 a 0.
Výsledok v tomto okamihu vyzerá takto:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Potom:
=(1>0)*(0=0) =TRUE*TRUE =1
Poznámka: tento vzorec vracia buď 1, alebo nulu, s ktorými sa vo vzorcoch, podmienenom formátovaní alebo overovaní údajov zaobchádza ako s TRUE a FALSE.
