Všeobecný vzorec
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Zhrnutie
Ak chcete otestovať bunku na jeden z niekoľkých reťazcov a vrátiť vlastný výsledok pre prvú nájdenú zhodu, môžete použiť vzorec INDEX / MATCH založený na funkcii SEARCH. V zobrazenom príklade je vzorec v C5:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kde veci (E5: E8) a výsledky (F5: F8) sú pomenované rozsahmi.
Toto je vzorec poľa a musí sa zadať pomocou kombinácie klávesov Control + Shift + Enter.
Vysvetlenie
Tento vzorec používa dva pomenované rozsahy: veci a výsledky . Ak prenášate tento vzorec priamo, nezabudnite použiť pomenované rozsahy s rovnakými názvami (definované na základe vašich údajov). Ak nechcete používať pomenované rozsahy, použite namiesto toho absolútne referencie.
Jadrom tohto vzorca je tento úryvok:
ISNUMBER(SEARCH(things,B5)
Toto je založené na inom vzorci (podrobne vysvetlenom tu), ktorý kontroluje bunku na jediný podreťazec. Ak bunka obsahuje podreťazec, vráti vzorec hodnotu TRUE. Ak nie, vzorec vráti FALSE.
Pretože dávame funkcii SEARCH viac vecí , ktoré by sme mali hľadať, v pomenovaných veciach rozsahu nám dá viac jedného výsledku v poli, ktoré vyzerá takto:
(#VALUE!;9;#VALUE!;#VALUE!)
Čísla predstavujú zhody vecí , chyby predstavujú položky, ktoré sa nenašli.
Na zjednodušenie poľa používame funkciu ISNUMBER na prevod všetkých položiek v poli na hodnotu TRUE alebo FALSE. Akékoľvek platné číslo sa stáva PRAVDIVÝM a každá chyba (tj. Vec, ktorá sa nenašla) sa stane NEPRAVDA. Výsledkom je pole ako je toto:
(FALSE;TRUE;FALSE;FALSE)
ktorý ide do funkcie MATCH ako argument lookup_array s hodnotou lookup_value TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH potom vráti pozíciu prvého nájdeného PRAVDA, v tomto prípade 2.
Nakoniec použijeme funkciu INDEX na získanie výsledku z pomenovaných výsledkov rozsahu na tej istej pozícii:
=INDEX(results,2) // returns "found red"
Rozsah výsledkov môžete prispôsobiť ľubovoľným hodnotám, ktoré majú vo vašom prípade použitia zmysel.
Predchádzanie nesprávnym zhodám
Jedným z problémov tohto prístupu v prístupe ISNUMBER + SEARCH je, že z dlhších slov môžete dostať nepravdivé zhody z čiastkových zhody. Ak sa napríklad pokúsite nájsť zhodu s výrazom „dr“, môžete v týchto slovách nájsť aj výrazy „Andrea“, „pili“, „kvapkajte“ atď., Pretože „dr“ sa nachádza v týchto slovách. Stáva sa to preto, lebo SEARCH automaticky priradí zhodu typu „obsahuje“.
Pre rýchlu opravu môžete hľadané slová zalomiť medzerami (napr. „Dr“ alebo „dr“), aby ste predišli hľadaniu výrazu „dr“ v inom slove. To však zlyhá, ak sa „dr“ v bunke objaví ako prvý alebo posledný.
Ak potrebujete robustnejšie riešenie, jednou z možností je normalizovať text najskôr v stĺpci pomocníka a pridať úvodný a zadný priestor. Potom použite vzorec na tejto stránke v texte v stĺpci Pomocník namiesto pôvodného textu.
