Yleinen kaava
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Yhteenveto
Jos haluat testata solua yhdellä useista merkkijonoista ja palauttaa mukautetun tuloksen ensimmäiseltä löydetyltä vastaavuudelta, voit käyttää HAKU-funktioon perustuvaa INDEX / MATCH-kaavaa. Esitetyssä esimerkissä kaava kohdassa C5 on:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
missä asiat (E5: E8) ja tulokset (F5: F8) on nimetty alueiksi.
Tämä on matriisikaava ja se on syötettävä painamalla Control + Vaihto + Enter.
Selitys
Tässä kaavassa käytetään kahta nimettyä aluetta: asiat ja tulokset . Jos siirrät tätä kaavaa suoraan, muista käyttää nimettyjä alueita samoilla nimillä (määritetty tietojesi perusteella). Jos et halua käyttää nimettyjä alueita, käytä sen sijaan absoluuttisia viitteitä.
Tämän kaavan ydin on tämä katkelma:
ISNUMBER(SEARCH(things,B5)
Tämä perustuu toiseen kaavaan (selitetty yksityiskohtaisesti tässä), joka tarkistaa solun yhden alimerkkijonon varalta. Jos solu sisältää alimerkkijonon, kaava palauttaa TOSI. Jos ei, kaava palauttaa EPÄTOSI.
Koska annamme SEARCH-funktiolle useamman kuin yhden etsimisen, nimettyjen alueiden asioissa se antaa meille enemmän yhden tuloksen taulukossa, joka näyttää tältä:
(#VALUE!;9;#VALUE!;#VALUE!)
Numerot edustavat osumia asioissa , virheet edustavat kohteita, joita ei löydy.
Taulukon yksinkertaistamiseksi käytämme ISNUMBER-funktiota muuntaaksemme kaikki matriisin kohteet TOSI- tai EPÄTOSI. Mistä tahansa kelvollisesta numerosta tulee TOSI ja virheestä (ts. Asiasta, jota ei löydy) tulee EPÄTOSI. Tuloksena on tällainen taulukko:
(FALSE;TRUE;FALSE;FALSE)
joka menee MATCH-funktioon haku_array-argumenttina, haun arvo on TOSI:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH palauttaa sitten ensimmäisen löydetyn TOSIN sijainnin, tässä tapauksessa 2.
Lopuksi käytämme INDEX-toimintoa saadaksesi tuloksen nimetyistä alueiden tuloksista samasta sijainnista:
=INDEX(results,2) // returns "found red"
Voit muokata tulokset vaihtelevat kanssa, mitä arvoja järkeä käyttöpyyntösi.
Väärien osumien estäminen
Yksi ongelma tässä lähestymistavassa ISNUMBER + SEARCH -menetelmällä on, että saatat saada vääriä osumia osittaisista vastaavuuksista pidempien sanojen sisällä. Esimerkiksi, jos yrität sovittaa sanan "dr", saatat löytää myös "Andrea", "joi", "tippua" jne., Koska "dr" esiintyy näiden sanojen sisällä. Tämä tapahtuu, koska HAKU tekee automaattisesti "sisältää-tyyppi" -hakun.
Nopean korjauksen saat, kun etsit sanoja välilyönteihin (esim. "Dr" tai "dr") välttääksesi sanan "dr" löytämisen toisesta sanasta. Mutta tämä epäonnistuu, jos "dr" ilmestyy ensimmäisenä tai viimeisenä solussa.
Jos tarvitset vankemman ratkaisun, yksi vaihtoehto on normalisoida teksti ensin apusarakkeessa ja lisätä etu- ja lopputila. Käytä sitten tämän sivun kaavaa auttajasarakkeen tekstissä alkuperäisen tekstin sijaan.
