Yleinen kaava
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Yhteenveto
Voit testata solun sen selvittämiseksi, sisältääkö se yhden monista merkkijonoista, käyttämällä kaavaa, joka perustuu HAKU-, ISNUMBER- ja SUMPRODUCT-funktioihin. C5: n kaava kopioituna alaspäin on:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
missä asiat on nimetty alue E5: E9.
Selitys
Haluamme testata jokainen solu B5: B11 nähdä, jos se on jokin jouset on nimetty alue asiat (E5: E9). Kaavassa, jota käytämme C5: ssä, kopioitu alaspäin on:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Tämä kaava perustuu kaavaan (selitetty tässä), joka tarkistaa solun yhden alimerkkijonon suhteen. Jos solu sisältää alimerkkijonon, kaava palauttaa TOSI. Jos ei, kaava palauttaa EPÄTOSI:
ISNUMBER(SEARCH(things,B5))
Tässä tapauksessa annamme SEARCH-luettelon jousista. Koska asioissa on 5 merkkijonoa , SEARCH palauttaa 5 tulosta tällaisessa taulukossa:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Kun SEARCH löytää merkkijonon, se palauttaa merkkijonon sijainnin. Jos HAKU ei löydä merkkijonoa, se palauttaa #ARVO! virhe. Koska "keltainen" esiintyy B5: n ensimmäisenä sanana, näemme 1. Koska muita merkkijonoja ei löydy, muut 4 kohdetta ovat virheitä.
Tämä taulukko palautetaan suoraan ISNUMBER-funktioon. ISNUMBER palauttaa sitten TOSI / EPÄTOSI-arvot:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Jos taulukossa on jopa yksi TOSI, tiedämme, että solu sisältää ainakin yhden etsimistäsi merkkijonoista. Helpoin tapa tarkistaa TOSI on lisätä kaikki arvot yhteen. Voimme tehdä sen SUMPRODUCT-toiminnolla, mutta ensin meidän on pakotettava TOSI / EPÄTOSI-arvot arvoon 1 ja 0 kaksinkertaisella negatiivisella (-), kuten tämä:
--ISNUMBER(SEARCH(things,B5))
Tämä antaa uuden taulukon, joka sisältää vain 1s ja 0s:
(1;0;0;0;0)
toimitetaan suoraan SUMPRODUCT: lle
=SUMPRODUCT((1;0;0;0;0))
Vain yksi taulukko käsiteltäväksi SUMPRODUCT lisää taulukon kohteet ja palauttaa tuloksen. Mikä tahansa nollasta poikkeava tulos tarkoittaa, että meillä on "osuma", joten lisätään> 0 pakottaaksemme TOSI tai EPÄTOSI lopputuloksen:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Koodatulla luettelolla
Etuja merkkijonoluettelossa ei tarvitse käyttää aluetta. Voit myös käyttää matriisivakiota. Esimerkiksi "punaisen", "sinisen" ja "vihreän" tarkistamiseksi voit käyttää seuraavaa kaavaa:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Väärien osumien estäminen
Yksi ongelma tässä lähestymistavassa on, että saatat saada vääriä vastaavuuksia 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 tekee automaattisesti "sisältää" -vasteen.
Nopeasta hakkeroinnista voit lisätä tilaa hakusanojen (esim. "Dr" tai "dr") ympärille välttääksesi "dr": n tarttumisen toiseen sanaan. Mutta tämä epäonnistuu, jos "dr" näkyy solussa ensin tai viimeisenä tai ilmestyy välimerkkien kanssa.
Jos tarvitset tarkemman ratkaisun, yksi vaihtoehto on normalisoida ensin teksti auttaja-sarakkeessa ja lisätä myös etu- ja lopputila. Sitten käytät tämän sivun kaavaa tuloksena olevassa tekstissä.
