Bendroji formulė
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Santrauka
Norėdami išbandyti langelį vienoje iš kelių eilučių ir grąžinti tinkintą pirmosios rastos atitikties rezultatą, galite naudoti INDEX / MATCH formulę, pagrįstą funkcija PAIEŠKA. Parodytame pavyzdyje formulė C5 yra:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kur daiktai (E5: E8) ir rezultatai (F5: F8) įvardijami diapazonai.
Tai yra masyvo formulė, kurią reikia įvesti paspaudus „Control + Shift + Enter“.
Paaiškinimas
Šioje formulėje naudojami du įvardyti diapazonai: dalykai ir rezultatai . Jei tiesiogiai perkeliate šią formulę, nepamirškite naudoti pavadintų diapazonų su tais pačiais pavadinimais (apibrėžtais pagal jūsų duomenis). Jei nenorite naudoti pavadintų diapazonų, vietoj to naudokite absoliučias nuorodas.
Šios formulės esmė yra šis fragmentas:
ISNUMBER(SEARCH(things,B5)
Tai pagrįsta kita formule (išsamiai paaiškinta čia), kuri tikrina, ar langelyje nėra vieno pakraščio. Jei langelyje yra poskyris, formulė grąžina TRUE. Jei ne, formulė pateikia FALSE.
Kadangi mes suteikiame paieškos funkcija daugiau nei vieną dalyką ieškoti, per pavadintą diapazoną dalykų , jis duos mums daugiau vieną rezultatą, masyvo, kad atrodo taip:
(#VALUE!;9;#VALUE!;#VALUE!)
Skaičiai reiškia daiktų atitikmenis , klaidos - nerastus daiktus.
Norėdami supaprastinti masyvą, mes naudojame funkciją ISNUMBER, kad visi masyvo elementai būtų konvertuojami į TRUE arba FALSE. Bet koks galiojantis numeris tampa TIESA, o bet kokia klaida (ty dalykas, kurio nerasta) tampa NETIESA. Rezultatas yra toks masyvas:
(FALSE;TRUE;FALSE;FALSE)
kuris eina į funkciją MATCH kaip „lookup_array“ argumentas, su „true“ lookup_value:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
Tada MATCH grąžina pirmosios rastos tiesos padėtį, šiuo atveju 2.
Galiausiai mes naudojame funkciją INDEX, kad gautume rezultatą iš pavadintų diapazono rezultatų toje pačioje vietoje:
=INDEX(results,2) // returns "found red"
Rezultatų diapazoną galite tinkinti naudodami bet kokias reikšmes.
Klaidingų atitikmenų prevencija
Viena iš požiūrio ISNUMBER + SEARCH problemų yra ta, kad ilgesnių žodžių viduje galite gauti klaidingą atitiktį iš dalinių atitikmenų. Pavyzdžiui, jei bandote suderinti „dr“, taip pat galite rasti „Andrea“, „gėrė“, „lašelinė“ ir kt., Nes šiuose žodžiuose yra „dr“. Taip atsitinka todėl, kad „SEARCH“ automatiškai atlieka atitikimą „yra tipo“.
Norėdami greitai išspręsti problemą, galite įvesti paieškos žodžius tarpo rašmenimis (pvz., „Dr“ arba „dr“), kad nerastumėte „dr“ kitame žodyje. Bet tai nepavyks, jei langelyje „dr“ pasirodys pirmas arba paskutinis.
Jei jums reikia patikimesnio sprendimo, viena iš galimybių yra pirmiausia normalizuoti tekstą pagalbiniame stulpelyje ir pridėti priekinę ir galinę tarpą. Tada naudokite šio puslapio formulę pagalbiniame stulpelyje esančiame tekste, o ne pradiniame tekste.
