Bendroji formulė
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Santrauka
Norėdami paryškinti langelius, kuriuose yra viena iš daugelio teksto eilučių, galite naudoti formulę, pagrįstą funkcijomis ISNUMBER ir SEARCH, kartu su funkcija SUMPRODUCT. Pateiktame pavyzdyje sąlyginis formatavimas, taikomas B4: B11, yra pagrįstas šia formule:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Paaiškinimas
Dirbant iš vidaus, ši formulės dalis ieško kiekvienoje langelyje B4: B11 visų reikšmių, nurodytų diapazone „dalykai“:
--ISNUMBER(SEARCH(things,B4)
Funkcija PAIEŠKA grąžina vertės padėtį, jei ji rasta, ir #VALUE klaidą, jei nerandama. B4 atveju rezultatai pateikiami tokiu masyvu:
(8;#VALUE!;#VALUE!)
Funkcija ISNUMBER pakeičia visus rezultatus į TRUE arba FALSE:
(TRUE;FALSE;FALSE)
Dvigubas neigiamas prieš ISNUMBER verčia TRUE / FALSE iki 1/0:
(1;0;0)
Tada funkcija SUMPRODUCT susumuoja rezultatus, kurie yra tikrinami pagal nulį:
=SUMPRODUCT((1;0;0))>0
Bet koks rezultatas, lygus nuliui, reiškia, kad buvo rasta bent viena reikšmė, todėl formulė pateikia TRUE, suaktyvindama taisyklę.
Nepaisykite tuščių dalykų
Jei norite nepaisyti tuščių langelių pavadintame diapazone „dalykai“, galite išbandyti pakeistą formulę:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Tai veikia tol, kol testuojamose teksto reikšmėse nėra eilutės „FALSE“. Jei taip, galite išplėsti funkciją IF, įtraukdami vertę, jei klaidinga, kad tekste nėra (pvz., „Zzzz“, „####“ ir kt.)
Didžiųjų ir mažųjų raidžių parinktis
PAIEŠKA neskiria didžiųjų ir mažųjų raidžių. Norėdami patikrinti atvejį, taip pat pakeiskite PAIEŠKA į RASTI:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Klaidingų atitikmenų prevencija
Viena iš šio požiūrio problemų yra ta, kad ilgesniuose žodžiuose galite pamatyti klaidingas atitiktis, kurias sukelia pakraščiai. Pavyzdžiui, jei bandote suderinti „dr“, taip pat galite rasti „Andrea“, „gėrimas“, „sausas“ ir pan., Nes šiuose žodžiuose yra „dr“. Taip atsitinka todėl, kad „SEARCH“ automatiškai atlieka atitikimą „yra“.
Norėdami iš dalies pataisyti, galite pridėti vietos aplink paieškos žodžius (ty „dr“ arba „dr“), kad išvengtumėte „dr“ užstrigimo kitame žodyje. Bet tai nepavyks, jei langelyje „dr“ pasirodys pirmas ar paskutinis arba šalia skyrybos ženklų. Iš dalies tai galima išspręsti pridėjus tarpo ir aplink originalų tekstą. Norėdami pridėti vietos abiejų pradžioje ir pabaigoje tuo pačiu metu, galite išbandyti tokią formulę:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Tačiau tai neišspręs skyrybos keliamų problemų.
Jei jums reikia išsamesnio sprendimo, vienas iš variantų yra pirmiausia normalizuoti tekstą pagalbiniame stulpelyje, pasirūpinant, kad būtų pridėta priekinė ir galinė tarpai. Tada galite ieškoti ištisų žodžių, kuriuos supa tarpai.
