Yleinen kaava
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Yhteenveto
Voit korostaa soluja, jotka sisältävät yhden monista tekstimerkkijonoista, käyttämällä kaavaa, joka perustuu funktioihin ISNUMBER ja SEARCH yhdessä SUMPRODUCT-funktion kanssa. Esitetyssä esimerkissä malliin B4: B11 sovellettu ehdollinen muotoilu perustuu tähän kaavaan:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Selitys
Työskentelemällä sisältä ulospäin kaavan tämä osa etsii jokaisesta solusta kohdassa B4: B11 kaikki nimetyn alueen "asiat" arvot:
--ISNUMBER(SEARCH(things,B4)
HAKU-toiminto palauttaa arvon sijainnin, jos se löytyy, ja #ARVO-virheen, jos sitä ei löydy. B4: n tulokset palautuvat näin:
(8;#VALUE!;#VALUE!)
ISNUMBER-toiminto muuttaa kaikki tulokset TOSI tai EPÄTOSI:
(TRUE;FALSE;FALSE)
ISNUMBERin edessä oleva kaksoisnegatiivi pakottaa TOSI / EPÄTOSI arvoon 1/0:
(1;0;0)
SUMPRODUCT-toiminto laskee yhteen tulokset, jotka testataan nollan suhteen:
=SUMPRODUCT((1;0;0))>0
Mikä tahansa nollasta poikkeava tulos tarkoittaa, että ainakin yksi arvo löydettiin, joten kaava palauttaa arvon TOSI, mikä laukaisee säännön.
Ohita tyhjät asiat
Voit ohittaa tyhjät solut nimetyllä alueella "asiat" kokeilemalla muokattua kaavaa seuraavasti:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Tämä toimii niin kauan kuin testattavat tekstiarvot eivät sisällä merkkijonoa "FALSE". Jos näin tapahtuu, voit laajentaa IF-funktion sisällyttämään arvon, jos väärää tiedetään esiintyvän tekstissä (esim. "Zzzz", "####" jne.)
Kirjainkoko merkitsevä vaihtoehto
HAKU ei eroa kirjainkokoja. Jos haluat tarkistaa myös tapauksen, korvaa SEARCH nimellä ETSI näin:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Väärien osumien estäminen
Yksi ongelma tässä lähestymistavassa on, että saatat nähdä vääriä vastaavuuksia, jotka johtuvat alaosista, jotka näkyvät pidempien sanojen sisällä. Esimerkiksi, jos yrität sovittaa sanan "dr", saatat löytää myös "Andrea", "drink", "dry" jne., Koska sanojen sisällä on "dr". Tämä tapahtuu, koska SEARCH suorittaa automaattisesti "sisältää" -hakun.
Osittaista korjausta varten voit lisätä välilyönnin hakusanojen (esim. "Dr" tai "dr") ympärille välttääksesi sanan "dr" tarttumisen toiseen sanaan. Mutta tämä epäonnistuu, jos "dr" näkyy ensimmäisenä tai viimeisenä solussa tai välimerkkien vieressä. Tähän voidaan puuttua osittain lisäämällä välilyönti myös alkuperäisen tekstin ympärille. Voit lisätä tilaa molempien alkuun ja loppuun samanaikaisesti kokeilemalla seuraavaa kaavaa:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Tämä ei kuitenkaan korjaa välimerkkien aiheuttamia ongelmia.
Jos tarvitset täydellisemmän ratkaisun, yksi vaihtoehto on normalisoida teksti ensin auttajasarakkeessa ja lisätä myös etu- ja lopputila. Sitten voit etsiä kokonaisia sanoja välilyönneillä.
