Üldine valem
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Kokkuvõte
Lahtrite esiletõstmiseks, mis sisaldavad ühte paljudest tekstistringidest, saate kasutada valemit, mis põhineb funktsioonidel ISNUMBER ja SEARCH koos funktsiooniga SUMPRODUCT. Näidatud näites põhineb B4: B11-le rakendatud tingimuslik vormindamine sellel valemil:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Selgitus
Seestpoolt töötades otsib see valemi osa B4: B11 igast lahtrist kõiki nimevahemikus "asjad" olevaid väärtusi:
--ISNUMBER(SEARCH(things,B4)
Funktsioon SEARCH tagastab väärtuse asukoha, kui see on leitud, ja vea #VALUE, kui seda ei leita. B4 puhul tulevad tulemused tagasi sellise massiivina:
(8;#VALUE!;#VALUE!)
Funktsioon ISNUMBER muudab kõik tulemused väärtuseks TRUE või FALSE:
(TRUE;FALSE;FALSE)
ISNUMBERi ees olev topeltnegatiiv sunnib TÕENE / VALE väärtusele 1/0:
(1;0;0)
Seejärel liidetakse funktsiooniga SUMPRODUCT tulemused, mida testitakse nulliga:
=SUMPRODUCT((1;0;0))>0
Mis tahes nullist erinev tulemus tähendab, et leiti vähemalt üks väärtus, nii et valem tagastab TÕENE, käivitades reegli.
Ignoreeri tühje asju
Nimetatud vahemikus "asjad" tühjade lahtrite eiramiseks võite proovida muudetud valemit:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
See töötab seni, kuni testitavad tekstiväärtused ei sisalda stringi "FALSE". Kui nad seda teevad, saate funktsiooni IF laiendada väärtusele, kui vale teadaolevalt tekstis ei esine (nt "zzzz", "####" jne)
Tõstutundlik valik
OTSING ei ole tõstutundlik. Ka juhtumi kontrollimiseks asendage SEARCH järgmisega:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Vale vastete vältimine
Selle lähenemisviisi üks probleem on see, et võite näha valede vastete põhjustamist alamstringidest, mis ilmuvad pikemate sõnade sees. Näiteks kui proovite sobitada "dr", võite leida ka "Andrea", "juua", "kuiv" jne, kuna nende sõnade sees on "dr". See juhtub, kuna SEARCH täidab automaatselt vaste "sisaldab".
Osalise paranduse jaoks võite otsingusõnade (st "dr" või "dr") ümber lisada tühiku, et vältida teise sõna "dr" püüdmist. Kuid see ebaõnnestub, kui "dr" ilmub lahtrisse esimesena või viimasena või ilmub kirjavahemärkide kõrvale. Seda saab osaliselt lahendada, lisades tühiku ka originaalteksti ümber. Mõlema algusesse ja lõppu korraga ruumi lisamiseks võite proovida järgmist valemit:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Kuid see ei lahenda kirjavahemärkidest tingitud probleeme.
Kui vajate terviklikumat lahendust, on üks võimalus teksti normaliseerimine kõigepealt abistaja veerus, lisades kindlasti ka ees- ja tagaosa. Seejärel saate otsida terveid sõnu, mis on ümbritsetud tühikutega.
