Stiahnite si ukážkový zošit
Tento návod ukazuje, ako používať Funkcia Excel VLOOKUP v programe Excel vyhľadať hodnotu.
Prehľad funkcií VLOOKUP
Funkcia VLOOKUP Funkcia Vlookup znamená vertikálne vyhľadávanie. Hľadá hodnotu v ľavom stĺpci tabuľky. Potom vráti hodnotu o zadaný počet stĺpcov napravo od nájdenej hodnoty. Je to to isté ako hľadanie, ibaže hodnoty vyhľadáva vertikálne namiesto horizontálne.
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
Syntax a argumenty funkcie VLOOKUP:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - Hodnota, ktorú chcete hľadať.
table_array - Rozsah údajov, ktorý obsahuje hodnotu, ktorú chcete hľadať, aj hodnotu, ktorú má vlookup vrátiť. Stĺpec obsahujúci hodnoty vyhľadávania musí byť stĺpcom úplne vľavo.
col_index_num - Číslo stĺpca rozsahu údajov, z ktorého chcete vrátiť hodnotu.
range_lookup - Pravda alebo lož. FALSE hľadá presnú zhodu. PRAVDA vyhľadá najbližšiu zhodu, ktorá je menšia alebo rovná vyhľadávacej hodnote. Ak je vybratá možnosť TRUE, stĺpec úplne vľavo (vyhľadávací stĺpec) musí byť zoradený vzostupne (od najnižšieho k najvyššiemu).
Čo je to funkcia VLOOKUP?
Ako jedna zo starších funkcií vo svete tabuliek sa na to používa funkcia VLOOKUP V.ertikálne Vyhľadávanie. Má niekoľko obmedzení, ktoré sú často prekonávané inými funkciami, ako napríklad INDEX/MATCH. Výhodou však je, že je to jediná funkcia, ktorá dokáže sama vyhľadať presnú zhodu. Býva to tiež jedna z prvých funkcií, o ktorých sa ľudia dozvedia.
Základný príklad
Pozrime sa na ukážku údajov z hodnotiacej knihy. Budeme sa zaoberať niekoľkými príkladmi na získavanie informácií pre konkrétnych študentov.
Ak chceme zistiť, do ktorej triedy Bob patrí, napísali by sme vzorec:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, NEPRAVDA) |
Je dôležité si uvedomiť, že položka, ktorú hľadáme (Bob), musí byť v prvom stĺpci nášho vyhľadávacieho rozsahu (A2: C5). Funkcii sme povedali, že chceme vrátiť hodnotu z 2nd stĺpcom, ktorý je v tomto prípade stĺpcom B. Nakoniec sme naznačili, že chceme urobiť an presná zhoda umiestnením Nepravdivé ako posledný argument. Tu bude odpoveď „Čítanie“.
Bočný tip: Ako konečný argument môžete použiť aj číslo 0 namiesto False, pretože majú rovnakú hodnotu. Niektorí ľudia to uprednostňujú, pretože je rýchlejšie písať. Len vedzte, že obidve sú prijateľné.
Posunuté údaje
Aby sme v našom prvom príklade dodali určité objasnenie, položka vyhľadávania nemusí byť v stĺpci A vašej tabuľky, ale iba v prvom stĺpci vášho rozsahu vyhľadávania. Použime rovnakú množinu údajov:
Teraz nájdeme známku pre triedu vedy. Náš vzorec by bol
| 1 | = VLOOKUP („Veda“, B2: C5, 2, NEPRAVDA) |
Toto je stále platný vzorec, pretože prvým stĺpcom nášho rozsahu vyhľadávania je stĺpec B, kde sa nachádza náš hľadaný výraz „veda“. Vraciame hodnotu z 2nd stĺpec rozsahu vyhľadávania, ktorým je v tomto prípade stĺpec C. Odpoveď potom je „A-“.
Použitie zástupných znakov
Funkcia VLOOKUP podporuje používanie zástupných znakov „*“ a „?“ pri vyhľadávaní. Povedzme napríklad, že sme zabudli, ako sa píše Frankovo meno, a chceli sme len nájsť meno, ktoré začína na „F“. Mohli by sme napísať vzorec
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
To by dokázalo nájsť meno Frank v riadku 5 a potom vrátiť hodnotu z 2nd relatívny stĺpec. V tomto prípade bude odpoveď „veda“.
Nepresná zhoda
Väčšinou sa budete chcieť uistiť, že posledný argument vo VLOOKUP je nepravdivý (alebo 0), aby ste získali presnú zhodu. Existuje však niekoľko prípadov, keď hľadáte nepresnú zhodu. Ak máte zoznam zoradených údajov, môžete tiež použiť VLOOKUP na vrátenie výsledku pre položku, ktorá je buď rovnaká alebo najbližšia najmenšia. Toto sa často používa pri riešení narastajúcich rozsahov čísel, napríklad v daňovej tabuľke alebo províziách.
Povedzme, že chcete nájsť sadzbu dane pre príjem zadaný do bunky D2. Vzorec v D4 môže byť:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Rozdiel v tomto vzorci je v tom, že náš posledný argument je „Pravda“. Na našom konkrétnom príklade vidíme, že keď naši jednotlivci vložia príjem 45 000 dolárov, budú mať sadzbu dane 15%.
Poznámka: Aj keď zvyčajne chceme presnú zhodu s argumentom False, zabudli ste zadať 4th Ak použijete argument VLOOKUP, predvolená hodnota je True. To môže viesť k neočakávaným výsledkom, najmä pri práci s textovými hodnotami.
Dynamický stĺpec
VLOOKUP vyžaduje, aby ste zadali argument, z ktorého stĺpca chcete vrátiť hodnotu, ale môže nastať situácia, keď neviete, kde bude stĺpec, alebo chcete svojmu používateľovi umožniť zmeniť, z ktorého stĺpca sa má vrátiť. V týchto prípadoch môže byť užitočné použiť funkciu MATCH na určenie čísla stĺpca.
Zoberme si znova príklad našej hodnotiacej knihy s niektorými vstupmi v E2 a E4. Aby sme získali číslo stĺpca, mohli by sme napísať vzorec pre
| 1 | = ZÁPAS (E2, A1: C1, 0) |
Pokúsi sa nájsť presnú polohu „Grade“ v rozsahu A1: C1. Odpoveď bude 3. Keď to vieme, môžeme to zapojiť do funkcie VLOOKUP a napísať vzorec v E6 takto:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Funkcia MATCH sa teda vyhodnotí na 3 a to povie VLOOKUP, aby vrátil výsledok z 3rd stĺpci v rozsahu A2: C5. Celkovo potom dostaneme požadovaný výsledok „C“. Náš vzorec je teraz dynamický v tom, že môžeme zmeniť buď vzhľad stĺpca, alebo názov, ktorý sa má hľadať.
Obmedzenia VLOOKUP
Ako už bolo spomenuté na začiatku článku, najväčším prepadom programu VLOOKUP je, že vyžaduje, aby sa hľadaný výraz nachádzal v ľavom stĺpci rozsahu vyhľadávania. Aj keď existuje niekoľko fantastických trikov, ktoré môžete urobiť, aby ste to prekonali, bežnou alternatívou je použitie INDEX a MATCH. Táto kombinácia vám poskytne väčšiu flexibilitu a niekedy môže ísť aj o rýchlejší výpočet.
VLOOKUP v Tabuľkách Google
Funkcia VLOOKUP funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
Doplňujúce Poznámky
Pomocou funkcie VLOOKUP vykonajte VERTIKÁLNE vyhľadávanie. VLOOKUP hľadá presnú zhodu (range_lookup = FALSE) alebo najbližšia zhoda, ktorá sa rovná alebo je nižšia ako lookup_value (range_lookup = TRUE, iba číselné hodnoty) v prvom riadku poľa table_array. Potom vráti zodpovedajúcu hodnotu, n počet riadkov pod zhodou.
Pri použití VLOOKUP na nájdenie presnej zhody najskôr definujete identifikačnú hodnotu, ktorú chcete hľadať ako lookup_value. Táto identifikačná hodnota môže byť SSN, ID zamestnanca, meno alebo iný jedinečný identifikátor.
Ďalej definujete rozsah (nazývaný table_array), ktorý obsahuje identifikátory v hornom riadku a akékoľvek hodnoty, ktoré chcete v konečnom dôsledku hľadať v riadkoch pod ním. DÔLEŽITÉ: Jedinečné identifikátory musia byť v hornom riadku. Ak nie sú, musíte buď presunúť riadok na začiatok, alebo namiesto VLOOKUP použiť MATCH / INDEX.
Po tretie, definujte číslo riadku (riadkový_index) z table_array že sa chceš vrátiť. Majte na pamäti, že prvý riadok obsahujúci jedinečné identifikátory je riadok 1. Druhý riadok je riadok 2 atď.
Nakoniec musíte označiť, či sa má v range_lookup. Ak je vybratá možnosť presnej zhody a presná zhoda sa nenájde, vráti sa chyba (#N/A). Ak chcete, aby bol vzorec vrátený prázdny alebo „nenájdený“ alebo akákoľvek iná hodnota namiesto chybovej hodnoty (#N/A), použite funkciu VLOŽIŤ.
Ak chcete použiť funkciu VLOOKUP na vrátenie približnej sady zhody: range_lookup = PRAVDA. Táto možnosť je k dispozícii iba pre číselné hodnoty. Hodnoty je potrebné zoradiť vzostupne.
Ak chcete vykonať horizontálne vyhľadávanie, použite namiesto toho funkciu HLOOKUP.
Príklady VLOOKUP vo VBA
Vo VBA môžete tiež použiť funkciu VLOOKUP. Typ:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Argumenty funkcie (lookup_value, atď.) Môžete buď zadať priamo do funkcie, alebo definovať premenné, ktoré sa majú použiť.
Vráťte sa do zoznamu všetkých funkcií v programe Excel
