Bendroji formulė
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,A1)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,A1)))=0)
Santrauka
Norėdami išbandyti langelį vienoje iš daugelio eilučių, išskyrus kitas, galite naudoti formulę, pagrįstą funkcijomis PAIEŠKA, ISNUMBER ir SUMPRODUCT. Parodytame pavyzdyje formulė C5 yra:
=(SUMPRODUCT(--ISNUMBER(SEARCH(include,B5)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,B5)))=0)
kur „įtraukti“ yra pavadintas diapazonas E5: E9, o „neįtraukti“ - pavadintas diapazonas G5: G6.
Paaiškinimas
Iš esmės ši formulė naudoja funkciją PAIEŠKA, kad ieškotų kelių eilučių ląstelės viduje. Kairiajame SUMPRODUCT viduje SEARCH ieško visų eilučių, nurodytų diapazone „apima“.
Dešiniajame SUMPRODUCT sistemoje SEARCH ieškoma visų eilučių, nurodytų diapazone „išskirti“.
Abiejose formulės dalyse SEARCH pateikia skaitines pozicijas, kai randamos eilutės, o klaidas, kai ne. Funkcija ISNUMBER paverčia skaičius į TRUE, o klaidos - FALSE, o dvigubas neigiamas - TRUE FALSE reikšmes į 1 ir 0.
Rezultatas šiuo metu atrodo taip:
=(SUMPRODUCT((1;0;0;0;0))>0)*(SUMPRODUCT((0;0))=0)
Tada:
=(1>0)*(0=0) =TRUE*TRUE =1
Pastaba: ši formulė grąžina 1 arba nulį, kurios formulėse, sąlyginiame formatavime ar duomenų tikrinime yra tvarkomos kaip TRUE ir FALSE.
