Bendroji formulė
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Santrauka
Norėdami suskirstyti tekstą naudodami raktinius žodžius, kurių atitikmuo yra „sudėtyje“, galite naudoti funkciją PAIEŠKA su INDEX ir MATCH pagalba. Parodytame pavyzdyje formulė C5 yra:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
kur raktiniai žodžiai yra pavadintas diapazonas E5: E14, o kategorijos - pavadintas diapazonas F5: F14.
Pastaba: tai yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
Paaiškinimas
Iš esmės tai yra „INDEX“ ir „MATCH“ funkcija.
Funkcijos MATCH viduje mes naudojame funkciją PAIEŠKA, norėdami ieškoti langelių B stulpelyje kiekvienam išvardytam raktiniam žodžiui, nurodytam diapazono raktiniams žodžiams (E5: E14):
SEARCH(keywords,B5)
Kadangi ieškome kelių elementų (pavadintuose diapazono raktiniuose žodžiuose ), mes pateiksime kelis tokius rezultatus:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Vertė! klaida įvyksta, kai PAIEŠKA neranda teksto. Kai SEARCH randa atitikimą, jis pateikia skaičių, atitinkantį teksto padėtį langelio viduje.
Norėdami pakeisti šiuos rezultatus naudingesniu formatu, naudojame funkciją ISNUMBER, kuri visas reikšmes paverčia TRUE / FALSE taip:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Šis masyvas patenka į funkciją MATCH kaip lookup_array, o lookup_value nustatyta kaip TRUE. MATCH grąžina pirmosios TIESOS, kurią randa masyvas, padėtį (šiuo atveju 7), kuri funkcijai INDEX pateikiama kaip eilutė_num .:
=INDEX(categories,7)
INDEX grąžina 7-ą kategorijų elementą „Auto“ kaip galutinį rezultatą.
Su XLOOKUP
Naudojant XLOOKUP funkciją, šią formulę galima šiek tiek supaprastinti. „XLOOKUP“ gali naudoti tą pačią logiką, naudojamą aukščiau esančioje funkcijoje MATCH, todėl ekvivalentinė formulė yra:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP nustato pirmąją masyvo tiesą ir grąžina atitinkamą kategorijų vertę .
Klaidingų atitikmenų prevencija
Viena iš šio požiūrio problemų yra tai, kad galite gauti klaidingų atitikmenų iš pakraščių, rodomų ilgesnių žodžių viduje. Pavyzdžiui, jei bandote suderinti „dr“, taip pat galite rasti „Andrea“, „gėrimas“, „sausas“ ir pan., Nes šiuose žodžiuose yra „dr“. Taip atsitinka todėl, kad „SEARCH“ automatiškai atlieka atitikimą „yra“.
Norėdami greitai įsilaužti, galite pridėti vietos aplink paieškos žodžius (pvz., „Dr“ arba „dr“), kad išvengtumėte „dr“ kitame žodyje. Bet tai nepavyks, jei langelyje „dr“ pasirodys pirmas arba paskutinis, arba bus rodomas skyryba ir t. T.
Jei jums reikia tikslesnio sprendimo, vienas iš variantų yra pirmiausia normalizuoti tekstą pagalbiniame stulpelyje, pasirūpinant, kad būtų pridėta ir priekinė ir galinė tarpai. Tada galite ieškoti ištisų žodžių, kuriuos supa tarpai.
