Obecný vzorec
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
souhrn
Chcete-li otestovat buňku na jeden z několika řetězců a vrátit vlastní výsledek pro první nalezenou shodu, můžete použít vzorec INDEX / MATCH založený na funkci SEARCH. V zobrazeném příkladu je vzorec v C5:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kde věci (E5: E8) a výsledky (F5: F8) jsou pojmenovány rozsahy.
Toto je vzorec pole a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Tento vzorec používá dva pojmenované rozsahy: věci a výsledky . Pokud přenášíte tento vzorec přímo, nezapomeňte použít pojmenované rozsahy se stejnými názvy (definované na základě vašich dat). Pokud nechcete používat pojmenované rozsahy, použijte místo toho absolutní odkazy.
Jádrem tohoto vzorce je tento úryvek:
ISNUMBER(SEARCH(things,B5)
To je založeno na jiném vzorci (podrobně vysvětleném zde), který kontroluje buňku na jediný podřetězec. Pokud buňka obsahuje podřetězec, vrátí vzorec hodnotu TRUE. Pokud ne, vrátí vzorec FALSE.
Protože funkci SEARCH dáváme hledat více než jednu věc, v pojmenovaných věcech rozsahu nám dá více jeden výsledek v poli, které vypadá takto:
(#VALUE!;9;#VALUE!;#VALUE!)
Čísla představují shody ve věcech , chyby představují položky, které nebyly nalezeny.
Pro zjednodušení pole používáme funkci ISNUMBER k převodu všech položek v poli na TRUE nebo FALSE. Jakékoli platné číslo se stane PRAVDU a jakákoli chyba (tj. Věc, která nebyla nalezena) se stane NEPRAVDA. Výsledkem je pole jako toto:
(FALSE;TRUE;FALSE;FALSE)
který jde do funkce MATCH jako argument lookup_array s lookup_value TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH pak vrátí pozici prvního nalezeného PRAVDA, v tomto případě 2.
Nakonec použijeme funkci INDEX k načtení výsledku z pojmenovaných výsledků rozsahu na stejné pozici:
=INDEX(results,2) // returns "found red"
Rozsah výsledků můžete přizpůsobit libovolným hodnotám, které dávají smysl vašemu případu použití.
Předcházení falešným shodám
Jedním z problémů tohoto přístupu s přístupem ISNUMBER + SEARCH je, že můžete získat falešné shody z částečných shod uvnitř delších slov. Pokud se například pokusíte najít shodu s výrazem „dr“, můžete také najít slova „Andrea“, „pili“, „odkapávat“ atd., Protože uvnitř těchto slov se zobrazuje „dr“. K tomu dochází, protože SEARCH automaticky provede shodu typu „obsahuje“.
Pro rychlou opravu můžete hledaná slova zalomit mezerami (tj. „Dr“ nebo „dr“), abyste předešli hledání slova „dr“ v jiném slově. To se ale nezdaří, pokud se „dr“ objeví v buňce jako první nebo poslední.
Pokud potřebujete robustnější řešení, jednou z možností je normalizovat text nejprve ve sloupci pomocníka a přidat úvodní a koncový prostor. Potom použijte vzorec na této stránce v textu ve sloupci pomocníka namísto původního textu.
