Bendroji formulė
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Santrauka
Norėdami patikrinti, ar langelyje nėra vienos iš daugybės eilučių, galite naudoti formulę, pagrįstą funkcijomis PAIEŠKA, ISNUMBER ir SUMPRODUCT. C5 formulė, nukopijuota žemyn, yra tokia:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
kur daiktai yra įvardijamas diapazonas E5: E9.
Paaiškinimas
Mes norime išbandyti kiekvieną ląstelę B5: B11 pamatyti, jei ji yra bet iš pavadintą diapazoną stygos dalykų (E5: E9). Nukopijuota formulė, kurią naudojame C5, yra:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Ši formulė yra pagrįsta formule (paaiškinta čia), kuri tikrina, ar langelyje nėra vieno pakraščio. Jei langelyje yra poskyris, formulė grąžina TRUE. Jei ne, formulė pateikia FALSE:
ISNUMBER(SEARCH(things,B5))
Tačiau šiuo atveju „SEARCH“ pateikiame eilučių sąrašą. Kadangi dalykuose yra 5 eilutės , SEARCH pateikia 5 tokius masyvo rezultatus:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Kai SEARCH randa eilutę, ji grąžina tos eilutės poziciją. Jei SEARCH neranda eilutės, ji grąžina #VALUE! klaida. Kadangi „geltonas“ yra pirmasis žodis B5, matome 1. Kadangi kitos eilutės nerandamos, kiti 4 elementai yra klaidos.
Šis masyvas grąžinamas tiesiai į funkciją ISNUMBER. Tada ISNUMBER pateikia TRUE / FALSE reikšmių masyvą:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Jei masyve turime nors vieną TIESĄ, žinome, kad langelyje yra bent viena iš ieškomų eilučių. Lengviausias būdas patikrinti, ar yra TIKRA, yra pridėti visas vertes kartu. Mes galime tai padaryti naudodami „SUMPRODUCT“, bet pirmiausia turime priversti TRUE / FALSE reikšmes į 1s ir 0s su dvigubu neigiamu (-) taip:
--ISNUMBER(SEARCH(things,B5))
Taip gaunamas naujas masyvas, kuriame yra tik 1s ir 0s:
(1;0;0;0;0)
pristatytas tiesiai į SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Vykdant tik vieną masyvą apdoroti, SUMPRODUCT prideda masyvo elementus ir pateikia rezultatą. Bet koks rezultatas, kuris nėra nulis, reiškia, kad turime „įvykį“, todėl pridedame> 0, kad priverstume galutinį TIKROS ar NETIESOS rezultatą:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Su sunkiai užkoduotu sąrašu
Stygų sąrašui ieškoti nereikia naudoti diapazono. Taip pat galite naudoti masyvo konstantą. Pavyzdžiui, jei norite patikrinti, ar nėra „raudonos“, „mėlynos“ ir „žalios“, galite naudoti tokią formulę:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Klaidingų atitikmenų prevencija
Viena iš šio požiūrio problemų yra tai, kad galite gauti klaidingų atitikmenų iš pakraščių, rodomų ilgesnių žodžių viduje. 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 greitai įsilaužti, galite pridėti vietos aplink paieškos žodžius (pvz., „Dr“ arba „dr“), kad išvengtumėte „dr“ kitame žodyje. Bet tai nepavyks, jei langelyje „dr“ pasirodo pirmas ar paskutinis arba jis rodomas su skyryba.
Jei jums reikia tikslesnio sprendimo, vienas iš variantų yra pirmiausia normalizuoti tekstą pagalbiniame stulpelyje, pasirūpinant, kad būtų pridėta ir priekinė ir galinė tarpai. Tada naudojate šiame puslapyje pateiktą formulę gautame tekste.
