Splošna formula
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Povzetek
Če želite poudariti celice, ki vsebujejo enega od številnih besedilnih nizov, lahko uporabite formulo, ki temelji na funkcijah ŠTEVILO in ISKANJE, skupaj s funkcijo SUMPRODUCT. V prikazanem primeru pogojno oblikovanje, uporabljeno za B4: B11, temelji na tej formuli:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Pojasnilo
Ta del formule deluje od znotraj navzven in v vsaki celici v B4: B11 poišče vse vrednosti v imenovanem obsegu "stvari":
--ISNUMBER(SEARCH(things,B4)
Funkcija SEARCH vrne položaj vrednosti, če je bila najdena, in napaka #VARUE, če ni najdena. Za B4 se rezultati vrnejo v takem nizu:
(8;#VALUE!;#VALUE!)
Funkcija ISNUMBER spremeni vse rezultate v TRUE ali FALSE:
(TRUE;FALSE;FALSE)
Dvojni negativ pred ISNUMBER sili TRUE / FALSE na 1/0:
(1;0;0)
Nato funkcija SUMPRODUCT sešteje rezultate, ki se preskusijo glede na nič:
=SUMPRODUCT((1;0;0))>0
Vsak rezultat, ki ni nič, pomeni, da je bila najdena vsaj ena vrednost, zato formula vrne TRUE in sproži pravilo.
Ne upoštevajte praznih stvari
Če želite prezreti prazne celice v imenovanem obsegu "stvari", lahko poskusite s spremenjeno formulo, kot je ta:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
To deluje, če besedilne vrednosti, ki jih preizkušate, ne vsebujejo niza "FALSE". Če se pojavijo, lahko funkcijo IF razširite tako, da vključi vrednost, če je znano, da se v besedilu ne pojavlja (npr. "Zzzz", "####" itd.)
Možnost, ki razlikuje med velikimi in malimi črkami
ISKANJE ne razlikuje med velikimi in malimi črkami. Če želite preveriti tudi primer, ISKANJE zamenjajte z FIND tako:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Preprečevanje lažnih ujemanj
Ena od težav pri tem pristopu je, da lahko vidite napačna ujemanja, ki jih povzročajo podnizi, ki se pojavijo znotraj daljših besed. Če na primer poskušate ujemati "dr", boste morda našli tudi "Andrea", "pijačo", "suho" itd., Ker se v teh besedah pojavlja "dr". To se zgodi, ker SEARCH samodejno izvede ujemanje "vsebuje".
Za delni popravek lahko dodate prostor okoli iskalnih besed (npr. "Dr" ali "dr"), da ne boste ujeli besede "dr" z drugo besedo. Vendar to ne bo uspelo, če se bo "dr" pojavil prvi ali zadnji v celici ali poleg ločil. To lahko delno rešite tako, da dodate prostor tudi okoli izvirnega besedila. Če želite hkrati dodati prostor na začetku in koncu obeh, lahko poskusite s formulo, kot je ta:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Vendar s tem ne boste odpravili težav, ki jih povzročajo ločila.
Če potrebujete popolnejšo rešitev, je ena možnost, da besedilo najprej normalizirate v pomožnem stolpcu, pri čemer pazite, da dodate tudi vodilni in zadnji prostor. Nato lahko iščete cele besede, obdane s presledki.
