01 z 03
Nájdite približné zhody údajov s aplikáciou Excel VLOOKUP
Ako funguje funkcia VLOOKUP
Funkcia VLOOKUP aplikácie Excel, ktorá slúži na vertikálne vyhľadávanie , môže byť použitá na vyhľadávanie špecifických informácií nachádzajúcich sa v tabuľke údajov alebo databázy.
VLOOKUP zvyčajne vracia jediné pole údajov ako výstup. Ako to robí, je:
- Zadáte názov alebo lookup_value, ktorý informuje VLOOKUP, v ktorom riadku alebo záznam v tabuľke s údajmi vyhľadajte požadované údaje
- Zadáte číslo stĺpca - známe ako col_index_num - údajov, ktoré hľadáte
- Funkcia vyhľadáva hodnotu lookup_value v prvom stĺpci tabuľky s údajmi
- VLOOKUP potom vyhľadá a vráti požadované informácie z iného poľa toho istého záznamu pomocou dodaného čísla stĺpca
Najprv triedenie dát
Aj keď to nie je vždy potrebné, zvyčajne je najlepšie najprv zoradiť rozsah údajov, ktoré vyhľadáva VLOOKUP vo vzostupnom poradí, pomocou prvého stĺpca rozsahu pre triedu kľúča.
Ak údaje nie sú zoradené, VLOOKUP môže vrátiť nesprávny výsledok.
Syntax a argumenty funkcie VLOOKUP
Syntax funkcie odkazuje na rozloženie funkcie a obsahuje názov funkcie, zátvorky a argumenty .
Syntax pre funkciu VLOOKUP je:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
lookup _value - (povinná) hodnota, ktorú chcete hľadať - napríklad množstvo predané na obrázku vyššie
table_array - (povinné) Toto je tabuľka údajov, ktoré VLOOKUP vyhľadáva, aby našla informácie, ktoré nasleduje.
- Table_array musí obsahovať aspoň dva stĺpce údajov
- Prvý stĺpec normálne obsahuje hodnotu lookup_value
col_index_num - (povinné) číslo stĺpca požadovanej hodnoty.
- Číslovanie začína reťazcom search_key ako stĺpec 1
- Ak je col_index_num nastavená na číslo väčšie ako počet stĺpcov vybraných v argumente table_array a #REF! chyba je vrátená funkciou
range_lookup - (voliteľné) označuje, či je rozsah rozdelený vo vzostupnom poradí.
- Údaje v prvom stĺpci sa používajú ako kľúč triedenia
- Booleovská hodnota - TRUE alebo FALSE sú jediné prijateľné hodnoty
- Ak je vynechaná, hodnota je v predvolenom nastavení nastavená na TRUE
- Ak je nastavená hodnota TRUE alebo vynechaná a prvý stĺpec rozsahu nie je zoradený vo vzostupnom poradí, môže dôjsť k nesprávnemu výsledku
- Ak je nastavená hodnota TRUE alebo vynechaná a presná zhoda pre výsledok vyhľadávania _value sa nenašla, najbližšia zhoda, ktorá je menšia vo veľkosti alebo hodnote, sa používa ako kľúč vyhľadávania
- Ak je nastavené na hodnotu FALSE, VLOOKUP akceptuje presnú zhodu pre vyhľadávaciu _value . Ak existuje viacero zodpovedajúcich hodnôt, vráti sa prvá zodpovedajúca hodnota
- Ak je nastavená funkcia FALSE a nenašla sa žiadna zodpovedajúca hodnota vyhľadávacieho kľúča , funkcia vráti chybu # N / A
Príklad: Nájdite si sadzbu zľavy pre zakúpené množstvo
Príklad na obrázku vyššie používa funkciu VLOOKUP na nájdenie diskontnej sadzby, ktorá sa líši v závislosti od množstva zakúpených položiek.
Príklad ukazuje, že zľava na nákup 19 položiek je 2%. Je to preto, že v stĺpci Množstvo sa nachádzajú rozsahy hodnôt. V dôsledku toho VLOOKUP nedokáže nájsť presnú zhodu. Namiesto toho sa musí nájsť približná zhoda s cieľom vrátiť správnu diskontnú sadzbu.
Nájsť približné zhody:
- zoradiť dáta v table_array vo vzostupnom poradí;
- nastavte parameter range_lookup na hodnotu TRUE
V nasledujúcom príklade sa nasledujúci vzorec, ktorý obsahuje funkciu VLOOKUP, používa na zistenie zľavy na množstvo zakúpeného tovaru.
= VLOOKUP (C 2, $ C $ 5: $ D $ 8,2, TRUE)
Aj keď tento vzorec môže byť iba zadaný do bunky pracovného hárka, ďalšou možnosťou, ako sa používa s postupmi uvedenými nižšie, je použiť dialógové okno funkcie pre zadanie jeho argumentov.
- Použitie dialógového okna často uľahčuje správne zadanie argumentov funkcie.
Otvorenie dialógového okna VLOOKUP
Kroky používané na zadanie funkcie VLOOKUP zobrazenej na obrázku B do bunky B2 sú:
- Kliknutím na bunku B2 ju vytvoríte ako aktívnu bunku - miesto, kde sa zobrazujú výsledky funkcie VLOOKUP
- Kliknite na kartu Vzorce .
- Z rozbaľovacej ponuky vyberte položku Vyhľadávanie a referencia a otvorte rozbaľovací zoznam funkcií
- Kliknutím na VLOOKUP v zozname vyvoláte dialógové okno funkcie
02 z 03
Zadanie Argumentov funkcie VLOOKUP v Exceli
Zobrazenie odkazov na bunky
Argumenty pre funkciu VLOOKUP sa zadávajú do samostatných riadkov dialógového okna, ako je znázornené na obrázku vyššie.
Odkazy na bunky, ktoré sa majú použiť ako argumenty, môžu byť napísané do správnej čiary, alebo ako je to urobené v nižšie uvedených krokoch, ukazujúce, ktoré zahŕňajú zvýraznenie požadovaného rozsahu buniek pomocou ukazovateľa myši, je možné použiť na ich zadanie do dialógového okna ,
Výhody použitia smerovania zahŕňajú:
- Je to rýchlejšie ako písanie;
- Pri zadávaní správnych odkazov na bunky sa urobí menej chýb.
Použitie relatívnych a absolútnych bunkových odkazov s argumentmi
Nie je nezvyčajné používať viac kópií VLOOKUP na vrátenie rôznych informácií z rovnakej tabuľky údajov. Aby to bolo jednoduchšie, môže sa VLOOKUP často kopírovať z jednej bunky do druhej. Keď sa funkcie skopírujú do iných buniek, je potrebné dbať na to, aby výsledné referencie buniek boli správne vzhľadom na nové umiestnenie funkcie.
Vo vyššie uvedenom obrázku označujú znaky dolára ( $ ) referencie buniek pre parameter table_array, ktoré označujú, že ide o absolútne odkazy na bunky , čo znamená, že sa nezmení, ak sa funkcia skopíruje do inej bunky. Toto je žiaduce, pretože viaceré kópie VLOOKUP by všetky odkazovali na rovnakú tabuľku údajov ako zdroj informácií.
Odkaz na bunku použitý pre lookup_value, na druhej strane , nie je obklopený znakmi dolára, čo z nej robí relatívnu bunkovú referenciu. Relatívne odkazy na bunky sa menia, keď sa skopírujú, aby odrážali svoju novú polohu vzhľadom na polohu údajov, na ktoré odkazujú.
Zadanie funkčných argumentov
- Kliknite na riadok Lookup _value v dialógovom okne VLOOKUP
- Kliknite na bunku C2 v pracovnom hárku a zadajte túto bunkovú referenciu ako argument search_key
- Kliknite na riadok Table_array dialógového okna
- Zvýraznite bunky C5 až D8 v pracovnom hárku a zadajte tento rozsah ako argument Table_array - hlavičky tabuľky nie sú zahrnuté
- Stlačením klávesu F4 na klávesnici zmeníte rozsah na absolútne odkazy na bunky
- Kliknite na riadok Col_index_num dialógového okna
- Zadajte hodnotu 2 na tento riadok ako argument Col_index_num , pretože diskontné sadzby sú umiestnené v stĺpci 2 argumentu Table_array
- Kliknite na riadok Range_lookup v dialógovom okne
- Zadajte slovo True ako argument Range_lookup
- Stlačením klávesu Enter na klávesnici zatvorte dialógové okno a vrátite sa na pracovný hárok
- Odpoveď 2% (diskontná sadzba pre zakúpené množstvo) by sa mala objaviť v bunke D2 pracovného hárka
- Po kliknutí na bunku D2 sa vo formulári nad pracovným hárkom zobrazí úplná funkcia = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
Prečo VLOOKUP vrátil 2% ako výsledok
- V tomto príklade stĺpec Množstvo neobsahuje presnú zhodu pre hodnotu vyhľadávacieho kľúča 19.
- Keďže parameter is_sorted je nastavený na TRUE, VLOOKUP nájde približnú zhodu s hodnotou search_key .
- Najbližšia hodnota vo veľkosti, ktorá je stále menšia ako hodnota vyhľadávacieho kľúča 19, je 11.
- Spoločnosť VLOOKUP preto hľadá percento zľavy v riadku obsahujúcom 11 a v dôsledku toho vráti diskontnú sadzbu vo výške 2%.
03 z 03
Excel VLOOKUP nefunguje: # N / A a #REF Chyby
VLOOKUP Chybové hlásenia
Nasledujúce chybové hlásenia sú priradené k VLOOKUP.
A # N / A ("hodnota nie je k dispozícii") Zobrazí sa chyba Ak:
- Hodnota _vyhľadávania sa nenachádza v prvom stĺpci argumentu rozsahu
- Argument Table_array je nepresný. Argument môže napríklad obsahovať prázdne stĺpce na ľavej strane rozsahu
- Argument Range_lookup je nastavený na hodnotu FALSE a presná zhoda pre argument search_key sa nedá nájsť v prvom stĺpci rozsahu
- Argument Range_lookup je nastavený na hodnotu TRUE a všetky hodnoty v prvom stĺpci rozsahu sú väčšie ako kľúč vyhľadávania
A #REF! ("odkaz mimo rozsah") Zobrazí sa chyba Ak:
- Argument Col_index_num je väčší ako počet stĺpcov v table_array