30 funkcií Excelu za 30 dní: VYHĽADÁVANIE

Včera na maratóne 30 funkcií Excelu za 30 dní bavili sme sa funkciou REPT (REPEAT) vytvorením grafov vo vnútri bunky a ich použitím na jednoduché počítanie. Je pondelok a opäť je tu čas, aby sme si nasadili mysliteľské klobúky.

V 16. deň maratónu budeme študovať funkciu VYHĽADAŤ (VYHLIADKA). Toto je blízky priateľ VLOOKUP (VLOOKUP) a HLOOKUP (GPR), ale funguje to trochu inak.

Poďme si teda naštudovať teóriu a otestovať funkciu v praxi VYHĽADAŤ (VYHLIADKA). Ak máte ďalšie informácie alebo príklady použitia tejto funkcie, podeľte sa o ne v komentároch.

Funkcia 16: VYHĽADÁVANIE

Funkcie VYHĽADAŤ (LOOKUP) vráti hodnotu z jedného riadka, jedného stĺpca alebo z poľa.

Ako môžem použiť funkciu VYHĽADÁVANIE?

Funkcie VYHĽADAŤ (LOOKUP) vráti výsledok v závislosti od hodnoty, ktorú hľadáte. S jeho pomocou budete môcť:

  • Nájdite poslednú hodnotu v stĺpci.
  • Nájdite posledný mesiac so zápornými predajmi.
  • Preveďte úspech študentov z percent na písmenkové známky.

Syntax LOOKUP

Funkcie VYHĽADAŤ (LOOKUP) má dve syntaktické formy – vektor a pole. Vo vektorovej forme funkcia hľadá hodnotu v danom stĺpci alebo riadku a vo forme poľa hľadá hodnotu v prvom riadku alebo stĺpci poľa.

Vektorová forma má nasledujúcu syntax:

LOOKUP(lookup_value,lookup_vector,result_vector)

ПРОСМОТР(искомое_значение;просматриваемый_вектор;вектор_результатов)

  • lookup_value (lookup_value) – Môže to byť text, číslo, logická hodnota, názov alebo odkaz.
  • lookup_vector (lookup_vector) – Rozsah pozostávajúci z jedného riadka alebo jedného stĺpca.
  • vektor_výsledku (vektor_výsledku) – rozsah pozostávajúci z jedného riadka alebo jedného stĺpca.
  • rozsahy argumentov lookup_vector (lookup_vector) a vektor_výsledku (výsledkový_vektor) musí mať rovnakú veľkosť.

Formulár poľa má nasledujúcu syntax:

LOOKUP(lookup_value,array)

ПРОСМОТР(искомое_значение;массив)

  • lookup_value (lookup_value) – Môže to byť text, číslo, logická hodnota, názov alebo odkaz.
  • vyhľadávanie sa vykonáva podľa rozmeru poľa:
    • ak má pole viac stĺpcov ako riadkov, vyhľadávanie sa uskutoční v prvom riadku;
    • ak je počet riadkov a stĺpcov rovnaký alebo je riadkov viac, vyhľadávanie sa uskutoční v prvom stĺpci.
  • funkcia vráti poslednú hodnotu z nájdeného riadku/stĺpca.

Vyhľadať pasce (ZOBRAZIŤ)

  • Vo funkcii VYHĽADAŤ (BROWSE) nie je možné vyhľadať presnú zhodu, ktorá je v VLOOKUP (VLOOKUP) a in HLOOKUP (GPR). Ak neexistuje žiadna hľadaná hodnota, funkcia vráti maximálnu hodnotu nepresahujúcu hľadanú hodnotu.
  • Vyhľadávané pole alebo vektor musí byť zoradené vzostupne, inak môže funkcia vrátiť nesprávny výsledok.
  • Ak je prvá hodnota v poli/vektore, ktorý sa hľadá, väčšia ako vyhľadávaná hodnota, funkcia vygeneruje chybové hlásenie #AT (#N/A).

Príklad 1: Nájdenie poslednej hodnoty v stĺpci

Vo forme funkcie poľa VYHĽADAŤ (LOOKUP) možno použiť na nájdenie poslednej hodnoty v stĺpci.

Pomocník Excelu uvádza hodnotu 9,99999999999999 E + 307 ako najväčšie číslo, ktoré je možné zapísať do bunky. V našom vzorci bude nastavená ako požadovaná hodnota. Predpokladá sa, že také veľké číslo sa nenájde, preto funkcia vráti poslednú hodnotu v stĺpci D.

V tomto príklade je povolené, aby sa čísla v stĺpci D netriedili, navyše sa môžu vyskytnúť textové hodnoty.

=LOOKUP(9.99999999999999E+307,D:D)

=ПРОСМОТР(9,99999999999999E+307;D:D)

30 funkcií Excelu za 30 dní: VYHĽADÁVANIE

Príklad 2: Nájdite posledný mesiac so zápornou hodnotou

V tomto príklade použijeme vektorový tvar VYHĽADAŤ (VYHLIADKA). Stĺpec D obsahuje hodnoty predaja a stĺpec E obsahuje názvy mesiacov. V niektorých mesiacoch to nefungovalo dobre a v bunkách s hodnotami predaja sa objavili záporné čísla.

Ak chcete nájsť posledný mesiac so záporným číslom, vzorec s VYHĽADAŤ (LOOKUP) skontroluje pri každej predajnej hodnote, či je menšia ako 0 (nerovnosť vo vzorci). Ďalej sa rozdelíme 1 na výsledku skončíme buď 1alebo chybové hlásenie #DIV/0 (#SECTION/0).

Keďže požadovaná hodnota je 2 nenájdený, funkcia vyberie posledné nájdené 1a vráti zodpovedajúcu hodnotu zo stĺpca E.

=LOOKUP(2,1/(D2:D8<0),E2:E8)

=ПРОСМОТР(2;1/(D2:D8<0);E2:E8)

30 funkcií Excelu za 30 dní: VYHĽADÁVANIE

Vysvetlenie: V tomto vzorci namiesto argumentu lookup_vector (lookup_vector) výraz nahradený 1/(D2:D8<0), ktorý tvorí pole v pamäti RAM počítača, pozostávajúce z 1 a chybové hodnoty #DIV/0 (#SECTION/0). 1 označuje, že zodpovedajúca bunka v rozsahu D2:D8 obsahuje hodnotu menšiu ako 0a chyba #DIV/0 (#DIV/0) – čo je väčšie alebo rovné 0. V dôsledku toho je našou úlohou nájsť posledný 1 vo vytvorenom virtuálnom poli a na základe toho vrátiť názov mesiaca z rozsahu E2:E8.

Príklad 3: Prevod výsledkov študentov z percent na známky s písmenami

Predtým sme už podobný problém riešili pomocou funkcie VLOOKUP (VPR). Dnes použijeme funkciu VYHĽADAŤ (VIEW) vo vektorovej forme na prevod študentských úspechov z percent na písmenkové známky. Na rozdiel od VLOOKUP (VLOOKUP) pre funkciu VYHĽADAŤ (POHĽAD) Nezáleží na tom, či sú percentá v prvom stĺpci tabuľky. Môžete si vybrať úplne akýkoľvek stĺpec.

V nasledujúcom príklade sú skóre v stĺpci D, zoradené vzostupne a ich zodpovedajúce písmená sú v stĺpci C naľavo od vyhľadávaného stĺpca.

=LOOKUP(C10,D4:D8,C4:C8)

=ПРОСМОТР(C10;D4:D8;C4:C8)

30 funkcií Excelu za 30 dní: VYHĽADÁVANIE

Nechaj odpoveď