Vyhľadajte jednu hodnotu pomocou funkcie VLOOKUP a HLOOKUP v Exceli

Najpopulárnejšie funkcie vyhľadávania v Exceli 2013 sú funkcie HLOOKUP (pre horizontálne vyhľadávanie) a VLOOKUP (pre vertikálne vyhľadávanie). Tieto funkcie sa nachádzajú v rozbaľovacej ponuke Lookup & Reference na karte Vzorce na páse s nástrojmi, ako aj v kategórii Lookup & Reference v dialógovom okne Vložiť funkciu.

Sú súčasťou výkonnej skupiny funkcií, ktoré dokážu vrátiť hodnoty tak, že ich vyhľadajú v dátových tabuľkách.

Funkcia VLOOKUP prehľadáva vertikálne (zhora nadol) stĺpec úplne vľavo vo vyhľadávacej tabuľke, kým program nenájde hodnotu, ktorá sa zhoduje s hodnotou, ktorú hľadáte, alebo ju presahuje. Funkcia HLOOKUP prehľadáva vodorovne (zľava doprava) najvrchnejší riadok vyhľadávacej tabuľky, kým nenájde hodnotu, ktorá sa zhoduje s hodnotou, ktorú hľadáte, alebo ju presahuje.

Funkcia VLOOKUP používa nasledujúcu syntax:

VLOOKUP(vyhľadávacia_hodnota,pole_tabuľky,číslo_indexu stĺpca,[vyhľadávanie_rozsahu])

Funkcia HLOOKUP má takmer identickú syntax:

HLOOKUP(vyhľadávacia_hodnota,pole_tabuľky,index_riadku,[vyhľadávanie_rozsahu])

V oboch funkciách je argument lookup_value hodnotou, ktorú chcete vyhľadať vo vyhľadávacej tabuľke, a table_array je rozsah buniek alebo názov vyhľadávacej tabuľky, ktorý obsahuje hodnotu na vyhľadanie a súvisiacu hodnotu, ktorá sa má vrátiť.

Argument col_index_num vo funkcii VLOOKUP je číslo stĺpca, ktorého hodnoty sa porovnávajú s argumentom lookup_value vo vertikálnej tabuľke. Argument row_index_num vo funkcii HLOOKUP je číslo riadku, ktorého hodnoty sa porovnávajú s hodnotou lookup_value v horizontálnej tabuľke.

Pri zadávaní argumentov col_index_num alebo row_index_num vo funkciách VLOOKUP a HLOOKUP musíte zadať hodnotu väčšiu ako nula, ktorá nepresahuje celkový počet stĺpcov alebo riadkov vo vyhľadávacej tabuľke.

Voliteľný argument typ argumentu v oboch VLOOKUP a VVYHLEDAT je logický true alebo false, ktorá určuje, či chcete Excel nájsť presný alebo približný zápas pre lookup_value v table_array.

Ak zadáte hodnotu PRAVDA alebo vynechať argument typ argument funkcie VLOOKUP alebo VVYHLEDAT, Excel zistí približnú zhodu. Keď zadáte FALSE ako argument range_lookup , Excel nájde iba presné zhody.

Hľadanie približných zhôd sa týka iba vtedy, keď hľadáte číselné položky (namiesto textu) v prvom stĺpci alebo riadku vertikálnej alebo horizontálnej vyhľadávacej tabuľky. Keď Excel nenájde presnú zhodu v tomto vyhľadávacom stĺpci alebo riadku, nájde ďalšiu najvyššiu hodnotu, ktorá nepresahuje argument lookup_value, a potom vráti hodnotu v stĺpci alebo riadku určenom argumentmi col_index_num alebo row_index_num .

Pri používaní funkcií VLOOKUP a HLOOKUP musia byť textové alebo číselné položky v stĺpci alebo riadku vyhľadávania (t. j. v ľavom stĺpci vertikálnej vyhľadávacej tabuľky alebo v hornom riadku horizontálnej vyhľadávacej tabuľky) jedinečné. Tieto položky musia byť tiež usporiadané alebo zoradené vo vzostupnom poradí; to znamená abecedné poradie pre textové položky a od najnižšieho po najvyššie poradie pre číselné položky.

Obrázok ukazuje príklad použitia funkcie VLOOKUP na vrátenie 15% alebo 20% prepitného z tabuľky sprepitného v závislosti od súčtu šeku pred zdanením. Bunka F3 obsahuje funkciu VLOOKUP:

=VLOOKUP(Pretax_Total,Tip_Table,IF(Tip_Percentage=0,15;2,3))

Tento vzorec vráti sumu prepitného na základe percenta prepitného v bunke F1 a sumy pred zdanením šeku v bunke F2.

Vyhľadajte jednu hodnotu pomocou funkcie VLOOKUP a HLOOKUP v Exceli

Ak chcete použiť túto tabuľku sprepitného, ​​zadajte percento prepitného (15 % alebo 20 %) do bunky F1 (s názvom Tip_Percentage) a sumu šeku pred zdanením do bunky F2 (s názvom Pretax_Total). Excel potom vyhľadá hodnotu, ktorú zadáte do bunky Pretax_Total v prvom stĺpci vyhľadávacej tabuľky, ktorá obsahuje rozsah buniek A2:C101 a má názov Tip_Table.

Excel potom posunie hodnoty v prvom stĺpci tabuľky Tip_Table nadol, kým nenájde zhodu, načo program použije argument col_index_num vo funkcii VLOOKUP na určenie sumy prepitného z daného riadku tabuľky, ktorá sa má vrátiť do bunky F3.

Ak Excel zistí, že hodnota zadaná v bunke Pretax_Total (16,50 USD v tomto príklade) sa presne nezhoduje s jednou z hodnôt v prvom stĺpci tabuľky Tip_Table, program pokračuje v prehľadávaní porovnávacieho rozsahu, kým nenarazí na prvú hodnotu, ktorá presahuje celková suma pred zdanením (v tomto príklade 17,00 v bunke A19).

Excel sa potom presunie späť na predchádzajúci riadok v tabuľke a vráti hodnotu v stĺpci, ktorá sa zhoduje s argumentom col_index_num funkcie VLOOKUP. (Je to preto, že voliteľný argument range_lookup bol z funkcie vynechaný.)

Všimnite si, že príklad tabuľky tipov používa funkciu IF na určenie argumentu col_index_num pre funkciu VLOOKUP v bunke F3.

Funkcia IF určuje číslo stĺpca, ktorý sa má použiť v tabuľke sprepitného, ​​prirovnaním percenta zadaného v Tip_Percentage (bunka F1) s 0,15. Ak sa zhodujú, funkcia vráti 2 ako argument col_index_num a funkcia VLOOKUP vráti hodnotu z druhého stĺpca (15 % stĺpec B) v rozsahu Tabuľka tipov.

V opačnom prípade funkcia IF vráti hodnotu 3 ako argument col_index_num a funkcia VLOOKUP vráti hodnotu z tretieho stĺpca (20 % stĺpec C) v rozsahu Tabuľka tipov.

Nasledujúci obrázok zobrazuje príklad, ktorý používa funkciu HLOOKUP na vyhľadanie ceny každej položky pekárne uloženej v samostatnej tabuľke vyhľadávania cien a potom na vrátenie tejto ceny do stĺpca Cena/Doz v zozname Denný predaj. Bunka F3 obsahuje pôvodný vzorec s funkciou HLOOKUP, ktorý sa potom skopíruje do stĺpca F:

Vyhľadajte jednu hodnotu pomocou funkcie VLOOKUP a HLOOKUP v Exceli

=HLOOKUP(položka,Cenová_tabuľka,2,FALSE)

V tomto VVYHLEDAT funkcie, názov položky rozsahu, ktorý je daný stĺpci Položka v rozmedzí C3: C62 je definovaná ako lookup_value argumentu a rozsah bunka názov Cena stola, ktorý je daný rozsah buniek I1: M2 je table_array argumentom.

Argument row_index_num je 2, pretože chcete, aby Excel vrátil ceny v druhom riadku tabuľky na vyhľadávanie cien, a voliteľný argument range_lookup je FALSE, pretože názov položky v zozname denného predaja sa musí presne zhodovať s názvom položky v tabuľke vyhľadávania cien .

Tým, že funkcia HLOOKUP použije rozsah cenovej tabuľky na zadanie ceny za tucet pre každú položku pekárenského tovaru v zozname denných predajov, bude aktualizácia ktoréhokoľvek z predajov v zozname veľmi jednoduchá.

Jediné, čo musíte urobiť, je zmeniť jeho cenu/doz v tomto rozsahu a funkcia HLOOKUP okamžite aktualizuje novú cenu v zozname denných predajov všade, kde sa položka predáva.

Leave a Comment

Ako zmeniť pozadie v PowerPointe 2019

Ako zmeniť pozadie v PowerPointe 2019

Naučte sa, ako jednoducho zmeniť pozadie v PowerPointe 2019, aby vaše prezentácie pôsobili pútavo a profesionálne. Získajte tipy na plnú farbu, prechod, obrázky a vzory.

Ako používať štatistické funkcie v Exceli na počítanie položiek v množine údajov

Ako používať štatistické funkcie v Exceli na počítanie položiek v množine údajov

Excel poskytuje štyri užitočné štatistické funkcie na počítanie buniek v hárku alebo zozname: COUNT, COUNTA, COUNTBLANK a COUNTIF. Pre viac informácií o Excel funkciách, pokračujte.

Klávesové skratky pre bežné príkazy zobrazenia Excel 2013

Klávesové skratky pre bežné príkazy zobrazenia Excel 2013

Objavte efektívne klávesové skratky v Exceli 2013 pre zobrazenie, ktoré vám pomôžu zlepšiť produktivitu. Všetky skratky sú začiatkom s Alt+W.

Ako nastaviť okraje stránky v programe Word 2013

Ako nastaviť okraje stránky v programe Word 2013

Naučte sa, ako nastaviť okraje v programe Word 2013 s naším jednoduchým sprievodcom. Tento článok obsahuje užitočné tipy a predvoľby okrajov pre váš projekt.

Ako používať štatistické funkcie na výpočet priemerov, režimov a mediánov v Exceli

Ako používať štatistické funkcie na výpočet priemerov, režimov a mediánov v Exceli

Excel vám poskytuje niekoľko štatistických funkcií na výpočet priemerov, režimov a mediánov. Pozrite si podrobnosti a príklady ich použitia.

Ako zistiť a opraviť chyby vzorcov v Exceli 2016

Ako zistiť a opraviť chyby vzorcov v Exceli 2016

Excel 2016 ponúka niekoľko efektívnych spôsobov, ako opraviť chyby vo vzorcoch. Opravy môžete vykonávať po jednom, spustiť kontrolu chýb a sledovať odkazy na bunky.

Archivácia priečinkov programu Outlook

Archivácia priečinkov programu Outlook

V niektorých prípadoch Outlook ukladá e-mailové správy, úlohy a plánované činnosti staršie ako šesť mesiacov do priečinka Archív – špeciálneho priečinka pre zastarané položky. Učte sa, ako efektívne archivovať vaše položky v Outlooku.

Vytváranie a formátovanie tabuliek v programe Word 2019

Vytváranie a formátovanie tabuliek v programe Word 2019

Word vám umožňuje robiť rôzne zábavné veci s tabuľkami. Učte sa o vytváraní a formátovaní tabuliek vo Worde 2019. Tabuľky sú skvelé na organizáciu informácií.

Výber rozsahu Excel VBA

Výber rozsahu Excel VBA

V článku sa dozviete, ako umožniť používateľovi vybrať rozsah v Excel VBA pomocou dialógového okna. Získajte praktické tipy a príklady na zlepšenie práce s rozsahom Excel VBA.

Výpočet percentuálneho rozptylu so zápornými hodnotami v Exceli

Výpočet percentuálneho rozptylu so zápornými hodnotami v Exceli

Vzorec na výpočet percentuálnej odchýlky v Exceli s funkciou ABS pre správne výsledky aj so zápornými hodnotami.