Všeobecný vzorec
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Zhrnutie
Ak chcete v bunke skontrolovať jednu z niekoľkých vecí a vrátiť prvú zhodu nájdenú v zozname, môžete na vyhľadanie zhody použiť vzorec INDEX / MATCH, ktorý používa VYHĽADÁVANIE alebo NÁJDENIE. V zobrazenom príklade je vzorec v C5:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
kde „veci“ je pomenovaný rozsah E5: E9.
Poznámka: toto je vzorec poľa a musí byť zadaný pomocou klávesov Control + Shift + Enter.
Vysvetlenie
V tomto príklade máme zoznam farieb v pomenovanom rozsahu, ktorý sa volá „things“ (veci) (E5: E9). Chceme skontrolovať text v stĺpci B a zistiť, či obsahuje niektorú z týchto farieb. Ak je to tak, chceme vrátiť názov prvej nájdenej farby.
Tento vzorec pracuje zvnútra, pomocou funkcií ISNUMBER a SEARCH na vyhľadanie textu v B5 pre každú farbu uvedenú v „veciach“, ako je táto:
ISNUMBER(SEARCH(things,B5)
Tento výraz je založený na vzorci (podrobne vysvetlenom tu), ktorý kontroluje bunku na jediný podreťazec. Ak bunka obsahuje podreťazec, vráti výraz TRUE. Ak nie, vráti výraz FALSE.
Keď dáme tomuto VYHĽADÁVANÍM zoznam vecí (namiesto jednej veci), vráti nám späť rad výsledkov. Každá nájdená farba vygeneruje číselnú pozíciu a farby, ktoré sa nenašli, vygenerujú chybu:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Funkcia ISNUMBER potom prevádza výsledky na hodnoty TRUE / FALSE. Akékoľvek číslo sa stane PRAVDIVÝM a akákoľvek chyba (nenájdená) sa stane NEPRAVDA. Výsledkom je pole ako je toto:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Toto pole je vrátené funkcii MATCH ako argument poľa. Vyhľadávacia hodnota je TRUE a typ zhody je nastavený na nulu, aby sa vynútila presná zhoda. Ak existuje zhoda farieb, funkcia MATCH vráti pozíciu ako prvú nájdenú hodnotu PRAVDA. Táto hodnota sa vkladá do funkcie INDEX ako číslo riadku, pričom ako pole sa uvádza pomenovaný rozsah „things“. Ak existuje aspoň jedna zhoda, vráti INDEX farbu na tejto pozícii. Ak sa nenájde zhoda, vráti tento vzorec chybu # N / A.
S pevne zakódovanými hodnotami
Ak nechcete v tomto príklade nastaviť externý pomenovaný rozsah, ako napríklad „things“, môžete hodnoty napevno napísať do vzorca ako „konštanty poľa“, ako je tento:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Získajte prvý zápas v bunke
Jazyk, ktorý tu uvádzate, je dosť mätúci, ale vzorec vyššie vráti prvú zhodu, ktorá sa nachádza v zozname hľadaných vecí. Ak namiesto toho chcete vrátiť prvú zhodu nájdenú v testovanej bunke, môžete vyskúšať nasledujúci vzorec:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
V tejto verzii vzorca je nastavená funkcia MATCH na vyhľadanie výsledku tohto úryvku:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
ktorá používa funkciu AGGREGATE na získanie minimálnej hodnoty vo výsledkoch vrátených funkciou SEARCH. Potrebujeme tu AGGREGÁT, pretože prichádzajúce pole pravdepodobne bude obsahovať chyby (vrátené príkazom SEARCH, keď sa veci nenájdu) a potrebujeme funkciu, ktorá bude tieto chyby ignorovať a stále nám poskytne minimálnu číselnú hodnotu.
Výsledok z AGGREGATE sa vráti priamo do MATCH ako vyhľadávacia hodnota spolu s rovnakým poľom vráteným SEARCH. Konečným výsledkom je prvá zhoda nájdená v bunke, nie prvá zhoda nájdená v zozname vecí.
