Vispārēja formula
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Kopsavilkums
Lai pārbaudītu šūnu vienai no daudzajām virknēm, vienlaikus izslēdzot citas, varat izmantot formulu, kuras pamatā ir funkcijas MEKLĒŠANA, ISNUMBER un SUMPRODUCT. Parādītajā piemērā formula C5 ir:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
kur "iekļaut" ir nosauktais diapazons E5: E9 un "izslēgt" ir nosauktais diapazons G5: G6.
Paskaidrojums
Būtībā šī formula izmanto funkciju MEKLĒŠANA, lai šūnā meklētu vairākas virknes. Kreisajā SUMPRODUCT iekšpusē SEARCH meklē visas virknes nosauktajā diapazonā "include".
Pareizajā SUMPRODUCT sadaļā SEARCH tiek meklētas visas virknes nosauktajā diapazonā "izslēgt".
Abās formulas daļās SEARCH atgriež skaitliskās pozīcijas, kad tiek atrastas virknes, un kļūdas, ja nav. Funkcija ISNUMBER pārveido skaitļus par TRUE un kļūdas - FALSE, un dubultnegatīvs TRUE FALSE vērtības pārvērš par 1 un 0.
Rezultāts šajā brīdī izskatās šādi:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Tad:
=(1>0)*(0=0) =TRUE*TRUE =1
Piezīme. Šī formula atgriež vai nu 1, vai nulle, kas formulās, nosacītā formatēšanā vai datu validācijā tiek apstrādāti kā TRUE un FALSE.
