Excel

Bunka Získať prvý zápas obsahuje

Get First Match Cell Contains

Vzorec programu Excel: Získaj bunku prvej zhodyGenerický vzorec | _+_ | Zhrnutie

Ak chcete v bunke skontrolovať jednu z niekoľkých vecí a vrátiť prvú zhodu, ktorá sa nachádza v zozname, môžete použiť vzorec INDEX / MATCH, ktorý na vyhľadanie zhody používa funkciu VYHĽADÁVAŤ alebo NÁJSŤ. V uvedenom príklade vzorec v C5 je:



{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,A1)),0))}

kde „veci“ sú pomenovaný rozsah E5: E9.

ako vypočítať prvý kvartil v programe Excel

Poznámka: toto je an maticový vzorec a je potrebné ho zadať kombináciou klávesov Control + Shift + Enter.





Vysvetlenie

V tomto prípade máme zoznam farieb v pomenovanom rozsahu nazývaný „veci“ (E5: E9). Chceme skontrolovať text v stĺpci B, aby sme zistili, či obsahuje niektorú z týchto farieb. Ak je to tak, chceme vrátiť názov prvej nájdenej farby.

Tento vzorec, ktorý funguje zvnútra von, používa príponu Funkcia ISNUMBER a Funkcia SEARCH na vyhľadanie textu v B5 pre každú farbu uvedenú v „veciach“ takto:



 
{= INDEX (things, MATCH (TRUE, ISNUMBER ( SEARCH (things,B5)),0))}

Tento výraz je založený na vzorci ( podrobne vysvetlené tu ), ktorá kontroluje v bunke jeden podreťazec. Ak bunka obsahuje podreťazec, výraz vráti hodnotu TRUE. Ak nie, výraz vráti hodnotu FALSE.

Keď dáme tomuto VYHĽADÁVANIE a zoznam vecí (namiesto jednej veci) nám vráti množstvo výsledkov. Každá nájdená farba vygeneruje číselnú pozíciu a farby, ktoré sa nenašli, vygenerujú chybu:

 
 ISNUMBER ( SEARCH (things,B5)

Funkcia ISNUMBER potom prevádza výsledky na TRUE / FALSE hodnoty. Akékoľvek číslo sa stane PRAVDIVÉ a akákoľvek chyba (nenájdená) sa stane NEPRAVDNOU. Výsledkom je pole ako toto:

 
{#VALUE!#VALUE!20#VALUE!#VALUE!}

Toto pole sa vráti do súboru Funkcia MATCH ako argument poľa. Hodnota vyhľadávania je PRAVDA a typ zhody je nastavený na nulu, aby sa vynútila presná zhoda. Keď existuje zhodná farba, MATCH vráti pozíciu ako prvú PRAVDU nájdenú. Táto hodnota je vložená do Funkcia INDEX ako číslo riadka, pričom ako pole je uvedený pomenovaný rozsah „vecí“. Keď je aspoň jedna zhoda, INDEX vráti farbu na tejto pozícii. Ak sa nenájde žiadna zhoda, tento vzorec vráti chybu #N/A.

S pevne zakódovanými hodnotami

Ak nechcete v tomto príklade nastaviť externý pomenovaný rozsah ako „veci“, môžete hodnoty do vzorca naprogramovať ako „konštanty poľa“ takto:

porovnať dva stĺpce údajov v programe Excel
 
{FALSEFALSETRUEFALSEFALSE}

Získajte prvý zápas v bunke

Jazyk je tu dosť mätúci, ale vyššie uvedený vzorec vráti prvú zhodu nájdenú v súbore zoznam vecí, ktoré treba hľadať . Ak namiesto toho chcete vrátiť prvú nájdenú zhodu v testovanej bunke Môžete skúsiť vzorec takto:

 
{= INDEX ({'red','green','blue'}, MATCH (TRUE, ISNUMBER ( SEARCH ({'red','green','blue'},B5)),0))}

V tejto verzii vzorca je funkcia MATCH nastavená tak, aby hľadala výsledok tohto úryvku:

 
= INDEX (things, MATCH ( AGGREGATE (15,6, SEARCH (things,A1),1), SEARCH (things,A1),0))

ktorý používa Funkcia AGGREGATE na získanie minimálnej hodnoty vo výsledkoch vrátených VYHĽADÁVANÍM. Tu potrebujeme AGGREGATE, pretože prichádzajúce pole bude pravdepodobne obsahovať chyby (vráti ho funkcia SEARCH, keď sa veci nenájdu) a potrebujeme funkciu, ktorá tieto chyby bude ignorovať a napriek tomu 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 pomocou SEARCH. Konečným výsledkom je prvá zhoda nájdená v bunke, nie prvá zhoda v zozname vecí.

Autor Dave Bruns


^