Úvod do funkce VLOOKUP
Funkce VLOOKUP v Excelu je bezpochyby nejpoužívanější a nejvíce diskutovanou funkcí. Na každém pracovním pohovoru, který vyžaduje znalosti Excelu, je tato otázka nejčastější, pokud víte, jak používat funkci VLOOKUP.
Funkce VLOOKUP spadá do kategorie vyhledávání funkcí Excelu. V ve VLOOKUP znamená svisle. Funkce se používá k vyhledávání dat, která jsou strukturována svisle. Pro horizontální vyhledávání existuje funkce s názvem HLOOKUP.
Funkce VLOOKUP aplikace Excel vyhledá první shodu dané hodnoty s názvem vyhledávací hodnota v datové tabulce a vrátí hodnotu z indexu daného sloupce. Shoda může být přibližná nebo přesná.
Přestože je VLOOKUP snadno použitelný, má svá omezení a kulisy. Budeme diskutovat o všem, včetně silných a slabých stránek této funkce.
Syntax:
= VLOOKUP (lookup_value, table_array, col_index_number, [range_lookup])
Co je vyhledávací hodnota
Lookup_value: Je to hodnota, kterou chcete hledat v poli tabulky.
Pokud například hledáte roli č. 110 ve studentském stole, pak jeho 110 je vyhledávací hodnota.
Tabulka_pole: Tabulka, ze které chcete hledat hodnotu vyhledávání.
Pokud například hledáte 110 v poli tabulky B5: E17. Pak B5: E17 je vaše tabulkové pole.
col_index_number: Číslo sloupce v poli Tabulka, ze kterého chcete načíst výsledky.
Například pokud v poli tabulky B5: E17 chcete získat hodnotu ze sloupce D pak bude váš sloupcový index 3 (počítání od B do D (B, C, D)).
[range_lookup]: FALSE, pokud chcete hledat přesnou hodnotu, TRUE, pokud chcete přibližnou shodu.
Jak používat funkci VLOOKUP
Pokud máte strukturovaná data a chcete v nich data vyhledat, je řešením funkce VLOOKUP.
VLOOKUP odpovídající režimy
VLOOKUP má dva režimy shody, přibližnou shodu a přesnou shodu. Ve výchozím nastavení provádí VLOOKUP přibližnou shodu. Ale pokud chcete přinutit funkci VLOOKUP k přesné shodě, můžete to udělat také. Většinu času, já a já pevně věřím i ostatním, zkuste udělat přesnou shodu pomocí funkce VLOOKUP. Nechápu, proč si vývojáři Excelu myslí, že uživatelé by chtěli většinou používat přibližnou shodu.
1. VLOOKUP Přibližný zápas
= VLOOKUP (lookup_value, table_array, col_index_number, 1)
V VLOOKUP přibližná shoda, pokud hodnota není nalezena, pak poslední hodnota, která je menší než vyhledávací hodnota, se shoduje a vrátí se hodnota z daného sloupcového indexu.
VLOOKUP ve výchozím nastavení odpovídá přibližně, pokud pomineme proměnnou vyhledávání rozsahu. Přibližná shoda je užitečná, když chcete provést přibližnou shodu a když máte pole tabulky seřazené vzestupně.
Vlookup hledá hodnotu, a pokud ji nenajde v poli tabulky, pak odpovídá hodnotě, která je menší než hodnota v poli tabulky. Pojďme to pochopit na příkladu.
Příklad 1
Máme zde seznam studentů. Každý student získal v testu nějaké známky. Teď chci dát známku podle jejich známek.
Každý student, který dosáhl skóre méně než 40, by měl být označen F, student, který dosáhl skóre mezi 40 a 60, by měl být označen C atd., Jak je znázorněno na obrázku níže.
Tento vzorec VLOOKUP bychom napsali do E2 a přetáhli dolů.
= VLOOKUP (D2, $ H $ 2: $ I $ 6,2, PRAVDA)
Jak to funguje?
Ve výše uvedeném příkladu jsou naše vyhledávací hodnoty ve sloupci D, počínaje D2. Pole Array je H2: I6 (všimněte si, že je seřazeno vzestupně a absolutně). Sloupec, ze kterého získáváme data, je 2. A typ shody, který jsme definovali přibližně, předáním PRAVDA (nic a 1 bude znamenat totéž).
Podívejme se tedy na hodnotu F2 pro vyhledávání, která obsahuje 40.
- VLOOKUP hledá 40 v prvním sloupci (H) pole tabulky H2: I6.
- Najde 40 na druhé pozici v buňce H3.
- Nyní se přesune do druhého sloupce z H3 do I3 a vrátí D.
V tomto případě vlookup našel přesnou shodu. Podívejme se na další případ.
Druhé vyhledávací hodnoty D3, které obsahují 36.
- VLOOKUP hledá 36 v prvním sloupci pole tabulky H2: I6.
- VLOOKUP nenalezl 36 nikde v prvním sloupci.
- Protože VLOOKUP nemohl najít 36, odpovídá poslední nalezené hodnotě, která je menší než vyhledávací hodnota.
- První hodnota, která je menší než 36, je 0, proto vrací F, které souvisí s 0.
Zde se to děje pro každý případ. Pro 92 je poslední hodnota, která je menší než 92, 90. Je tedy vrácena hodnota A pro přibližnou shodu.
2. VLOOKUP Přesná shoda
Tato nejpoužívanější forma VLOOKUP a může být také velmi užitečná. Chcete -li vyhledat přesnou hodnotu v prvním sloupci pole tabulky, použijte přesnou shodu. Pokud například děláte vyhledávací formulář ID, než byste si nejvíce přáli, chcete přesnou shodu namísto jakékoli hodnoty, která je menší než tato hodnota.
Abychom VLOOKUP donutili k přesné shodě v Excelu, předáme 0 nebo FALSE jako parametr range_lookup.
= VLOOKUP (lookup_value, table_array, col_index_number, 0)
Pokud není hodnota nalezena v prvním sloupci pole tabulky, vzorec vrátí chybu #N/A.
Podívejme se na příklad.
Příklad 2
V tomto příkladu chci napsat pouze roli číslo studenta v A2 a v B2 chci načíst soubor studentovo jméno a v C2 jeho Školní známka. Jednoduchý. Pole tabulky je B5: D17. Proč?
Chcete -li to provést, napište tyto dva vzorce do buňky v buňce B2 a C2.
= VLOOKUP (A2, $ B $ 5: $ E $ 17,2,0)
= VLOOKUP (A2, $ B $ 5: $ E $ 17,3,0)
Ať už do buňky A2 napíšete jakékoli ID, funkce VLOOKUP vyhledá přesnou shodu ve sloupci B a vrátí hodnotu z daného col_index.
Jak funguje funkce VLOOKUP s přesnou shodou?
Exact Match VLOOKUP v Excelu je jednoduchý. Vyhledá vyhledávací hodnotu v prvním sloupci zadaného pole table_array a vrátí hodnotu z hodnoty z daného čísla column_index. Pokud není hodnota nalezena, vrátí funkce VLOOKUP chybu #N/A.
Nejlepší využití funkce VLOOKUP
Vlookup má mnoho využití. Budu diskutovat o několika nejužitečnějších případech použití.
3. Namísto komplexní vnořené funkce IF použijte pro hodnocení funkci Excel VLOOKUP
Chcete -li nahradit vnořenou funkci IF, použijte přibližnou shodu VLOOKUP. V příkladu 1 jsme viděli, jak můžeme pomocí vlookupu ohodnotit studenty podle jejich známek.
Přibližná shoda VLOOKUP je rychlejší než vnořená, pokud interně používá binární vyhledávání.
4. K načtení dat použijte VLOOKUP
Tento nejběžnější úkol, který lze snadno provést pomocí funkce VLOOKUP v aplikaci Excel. V příkladu 2 jsme to udělali pro načtení dat studentů pomocí jejich role no.
5. Zkontrolujte, zda je hodnota v záznamu pomocí aplikace Vlookup.
VLOOKUP lze použít ke kontrole, zda je hodnota v nějakém seznamu nebo ne.
V příkladu 2, když napíšeme 152, vrátí chybu #N/A. Pokud VLOOKUP vrátí #N/A, znamená to, že danou hodnotu nikde v daných datech nenašel.
Připravil jsem na to stručný článek. Můžete si jej přečíst zde.
Pomocí vzorce VLOOKUP zkontrolujte, zda hodnota existuje
Automatické získání indexu sloupce
Patřím k té skupině lidí, kteří nechtějí opakovat stejný úkol znovu a znovu. Takže mě měnit číslo col_index znovu a znovu mě štve a vždy se tomu vyhýbám. Ve skutečnosti se vyhýbám jakýmkoli úpravám ve svých vzorcích, jakmile jsem je napsal, pokud a dokud není jiná možnost.
Index sloupce lze v aplikaci Excel vypočítat automaticky. Existuje několik způsobů, jak to udělat. Podívejme se zde na některé z nich.
6. Použijte funkci VLOOKUP s funkcí MATCH
Takže jak víme, že funkce MATCH vrací nalezený index odpovídající hodnoty.
Pokud tedy máme přesně stejné záhlaví v místě vyhledávání jako zdrojová data, pak můžeme použít k získání col_index. Jak? Podívejme se…
V níže uvedených datech chceme pouze napsat číslo role v G2 a chceme načíst jméno studenta, známky a hodnocení v H2, podle toho, který nadpis tam napíšeme.
Tento vzorec napište do buňky H2
= VLOOKUP (G2, B2: E14, MATCH (H1, B1: E1,0), 0)
Nyní, kdykoli změníte nadpis v H1, se z tohoto sloupce zobrazí hodnota v H2.
Můžete použít rozevírací seznam pomocí ověření dat, které má všechny nadpisy z tabulky, aby bylo uživatelsky přívětivější.
Zde VLOOKUP dělá přesnou shodu. Nyní VLOOKUP dělá své obvyklé věci získávání dat. Kouzlo se provádí funkcí MATCH na pozici col_index.
Zde VLOOKUP vidí jakoukoli hodnotu v G2 v prvním sloupci pole tabulky B2: E14. Nyní v místě col_index máme ZÁPAS (H1, B1: E1,0).
Funkce MATCH hledá v rozsahu H1 libovolnou hodnotu B1: E1 a vrací index odpovídající hodnoty.
Například když píšeme student v H1 to hledá v dosahu B1: E1. Nachází se v C2. proto vrátí 2. Pokud napíšeme Školní známka vrátí známku studenta.
Všimněte si, že když napíšeme Region, vrátí #N/A. Protože je mimo rozsah tabulky. O tom budeme hovořit později v článku.
Toto je nejlepší způsob, jak nastavit VLOOKUP automaticky. Existují i jiné metody, ale nejsou tak flexibilní.
7. Použijte funkci VLOOKUP s funkcí COLUMN
Funkce sloupce vrací číslo sloupce zadané reference. A co potřebujeme pro načítání dat pomocí VLOOKUP? Col_index. Pomocí funkce COLUMN tedy můžeme samozřejmě získat data z libovolné datové sady.
Vezměme si výše uvedený příklad. Nyní však musíme načíst celá data. Nejen jeden sloupec.
Napište tento vzorec do H2 a zkopírujte do I2 a J2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, COLUMN (B1), 0)
Získáte tak dynamický výsledek. Jak? Uvidíme.
VLOOKUP funguje jako obvykle. Na sloupec_index jsme napsali SLOUPEC (B1), což se přeloží na 2, protože ve sloupci B ve sloupci B máme jméno studenta, vrátí jméno studenta.
Důležité: To funguje, protože máme tabulku od sloupce B. Pokud je vaše tabulka uprostřed listu, budete muset odečíst nebo přidat nějaká čísla, aby odpovídala vašemu požadavku.
Pokud například výše uvedená tabulka začínala od C1 ve stejné struktuře, všechny níže uvedené vzorce fungovaly dobře.
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (B1), 0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (C1) -1,0)
= VLOOKUP ($ G2, $ C $ 2: $ F $ 14, COLUMN (A1) +1,0)
8. VLOOKUP ke sloučení dat
Ve většině databází existuje jeden dotaz, který se sloučí do tabulek, většinou se nazývá spojovací dotaz, ale MS Excel v žádné databázi, proto nemá spojovací dotaz. To však neznamená, že se nemůžeme spojit se dvěma tabulkami, pokud máme ve dvou tabulkách společné sloupce.
Když tedy získáte nějaká data ze dvou oddělení stejné společnosti, budete je chtít sloučit, abyste je mohli analyzovat.
Když například získáte známky studentů od učitelů a poté docházku z administrativního oddělení, budete chtít na jednom místě zjistit, který student získal jakou známku a jaké je jeho procento docházky.
Musíme je sloučit do níže uvedené tabulky.
Známky, musíme získat z tabulky známek a docházka z údajů o docházce.
Napište níže vzorce do buňky D19, abyste získali značky, a přetáhněte dolů.
= VLOOKUP (B19, $ B $ 2: $ D $ 15,3,0)
Do buňky E19 napište níže vzorec
= VLOOKUP (B19, $ G $ 3: $ I $ 15,3,0)
A sloučili jsme dvě tabulky do jedné tabulky. Samozřejmě můžete přidat více než dvě tabulky. Potřebujete pouze získat data ze všech požadovaných tabulek na jednom místě pomocí VLOOKUP.
Osvědčené postupy VLOOKUP
Všechny výše uvedené příklady jsme tedy použili vlookup s nezpracovanými daty. Ve všech výše uvedených příkladech jsme byli opatrní ohledně dat, která jsme používali. Museli jsme být opatrní a dávat odkazy na tabulková pole. Existují však určité způsoby, které mohou toto úsilí značně omezit.
9. Používejte s VLOOKUP absolutní reference
Možná jste si všimli, že v některých článcích jsem použil absolutní rozsahy (rozsah $ znak). Absolutní běsy se používají, když nechceme, aby se rozsah měnil při kopírování buňky vzorce do jiných buněk.
Například pokud mám = VLOOKUP (G2,B2: E14, 2,0), 0) v buňce H2, a pokud ji zkopíruji nebo přetáhnu dolů do buňky H3, vzorec se změní na = VLOOKUP (G3,B3: E15, 2,0). Všechny odkazy jsou relativně změněny. Zde můžeme chtít změnit odkaz na vyhledávací hodnotu, ale ne na pole tabulky. Protože B2 se dostane ven nebo se dostane do dosahu a my to možná budeme chtít mít v našich tabulkových polích vždy.
Abychom tomu zabránili, používáme absolutní reference. Tyto rozsahy děláme absolutní, které nechceme měnit, jako například tabulka_pole v H2, = VLOOKUP (G2,$ B $ 2: $ E $ 14, 2,0), 0). Když zkopírujete H2 v H3, vzorec bude = VLOOKUP (G3,$ B $ 2: $ E $ 14, 2,0), 0). Všimněte si, že vyhledávací hodnota je změněna, ale ne table_array.
10. Používejte pojmenované rozsahy s VLOOKUP
Ve výše uvedeném příkladu jsme použili absolutní odkazování pro opravu table_array. Vypadá to docela rozmazaně a špatně se to čte. Co kdybyste mohli jen psát 'data' na místě $ B $ 2: $ E $ 14 a to by odkazovalo na $ B $ 2: $ E $ 14 vždy. Čtení a psaní vzorce bude ještě snazší.
Stačí tedy vybrat rozsah a pojmenovat jej „data“.
Chcete -li pojmenovat rozsah, vyberte rozsah. Klikněte pravým tlačítkem, klikněte na Definovat název a pojmenujte požadovaný rozsah. Nyní jen = VLOOKUP (G3,data,2,0), 0) vzorec bude fungovat úplně stejně jako dříve.
Další informace o pojmenovaných rozsazích si můžete přečíst v Excelu. Vysvětlil jsem zde všechny aspekty pojmenovaného rozsahu.
11. Zástupné znaky pro částečné párování pomocí VLOOKUP
VLOOKUP umožňuje částečné párování. Ano, při přesné shodě VLOOKUP můžete použít částečné shody pomocí operátorů zástupných znaků. Pokud například chcete vyhledat hodnotu začínající na „Gil“, můžete použít operátor zástupných znaků * s „Gil“ jako „Gil *“. Podívejme se na příklad.
Zde chci získat známky studenta, jehož jméno začíná Gil. Aby to bylo možné, napíšu níže vzorec.
= VLOOKUP ("*Gill", C2: D14,2,0)
Pro použití s funkcí VLOOKUP jsou v aplikaci Excel k dispozici dva zástupné znaky.
Zástupný znak hvězdička (*). To se používá, když uživatel neví, kolik znaků existuje a zná pouze některé znaky shody. Pokud například uživatel zná jméno začínající na „Sm“, napíše „Sm*“. Pokud uživatel ví, že vyhledávací hodnota končící na „123“, napíše „*123“. (divoká karta funguje pouze s texty). A když ví, že „se“ přijde do textu, napíše „*se*“.
Otazník ("?"). Používá se, když uživatel zná počet chybějících znaků. Pokud například chci vyhledat hodnotu, která má 4 znaky, ale nevím, jaké jsou, jednoduše napíšu „????“ v místě vyhledávané hodnoty.
Vlookup vrátí příslušnou hodnotu první nalezené hodnoty, která má přesně čtyři znaky.
Důležité: Divoká karta funguje pouze s textovými hodnotami. Pokud s nimi chcete použít zástupné znaky, převeďte čísla na text.
Funkce Slabiny VLOOKUP
VLOOKUP je skutečně výkonná a snadná funkce pro načítání hodnot, ale existuje mnoho oblastí, kde VLOOKUP není tak užitečný. Pokud pracujete na Excelu, musíte je také znát a vědět, jak dělat úkoly, které VLOOKUP neumí.
12. Nelze načíst hodnotu zleva z pole Table_Array
Největší chybou funkce VLOOKUP je to, že nemůže načíst hodnotu zleva z pole tabulky. VLOOKUP vyhledává pouze vpravo od vyhledávací hodnoty.
Důvodem je, že VLOOKUP vyhledává zadané vyhledávací hodnota v prvním sloupci daného pole tabulky. Nikdy nevrátí hodnotu zvenčí tabulky. A pokud se pokusíte ponechat levý sloupec v poli tabulky, stane se prvním sloupcem pole tabulky, a proto bude v tomto rozsahu vyhledávána vyhledávací hodnota. Což pravděpodobně vygeneruje chybu.
K vyhledávání hodnot zleva od vyhledávací hodnoty používáme místo toho INDEX-MATCH. Funkce INDEX-MATCH může vyhledávat hodnoty z libovolného sloupce na listu. Ve skutečnosti jsou rozsah vyhledávání a rozsah, od kterého chcete hodnoty vyhledávání, zcela nezávislé, což činí indexovou shodu vysoce přizpůsobitelnou.
VLOOKUP vrací #N/A, i když existuje vyhledávací hodnota.
#Není k dispozici chyba funkce VLOOKUP, pokud není nalezena hodnota vyhledávání. Může vám připadat nepříjemné, když hodnota existuje v rozsahu, ale funkce VLOOKUP vrací chybu #N/A.
14. Když jsou čísla formulována jako text
K tomu většinou dochází, když jsou čísla formulována jako text. Když najdete pomocí CTRL+F příkaz, Excel jej najde, ale když se pokusíte použít VLOOKUP, vrátí #N/A. Tento úkol je ve většině rozhovorů zadán jako trikové otázky.
Na výše uvedeném obrázku vidíte, že 101 je právě tam, ale vzorec vrací chybu. Jak to zvládáte? Existují dvě metody.
1. Převod textu na číslo v datech
Zelenou značku můžete vidět v levém horním rohu ve sloupci rollno. Udávají, že v hodnotě buňky je něco neobvyklého. Když vyberete buňku, zobrazí se, že číslo je zformátováno jako text.
Když kliknete na vykřičník (!), Zobrazí se vám možnost Převést na číslo. Vyberte všechny buňky a klikněte na tuto možnost. Všechny hodnoty budou převedeny na text.
2. Převeďte své vyhledávací číslo na text ve vzorci
Ve výše uvedeném příkladu jsme zmutovali původní data. V původních datech možná nebudete chtít nic měnit. Takže byste to místo toho chtěli udělat ve vzorci. Chcete -li ve vzorci VLOOKUP změnit číslo na text, napište toto.
= VLOOKUP (TEXT (G2, "0"), B2: D14,2,0)
Zde vzorec dynamicky mění číslo na text a poté jej shoduje s daným rozsahem.
15. Text s úvodními a koncovými mezerami
Některá data poskytovaná provozovateli zadávání dat a data z internetu nejsou čistá. Mohou mít koncové mezery nebo některé netisknutelné znaky. Při vyhledávání pomocí těchto hodnot se může zobrazit nějaká chyba #N/A. Abyste tomu zabránili, nejprve si vyčistěte data. Chcete -li odstranit úvodní mezery, použijte funkci TRIM. TRIM odstraní z textu libovolný počet úvodních nebo koncových mezer.
Doporučuji tedy přidat nový sloupec a získat tam vyčištěná data. Poté hodnotu vložte. Pokud chcete, můžete se původního sloupce zbavit.
= OŘEZAT (ČISTIT (text))
Zpracování chyb VLOOKUP
Jak víte, VLOOKUP nedokáže najít hodnotu, vrátí chybu #N/A. Je v pořádku dostat chybu #N/A, možná to někdy máte v úmyslu, například když chcete zkontrolovat, zda hodnota v seznamu již existuje nebo ne. Tam #N/A znamená, že hodnota tam není.
Ale #N/A vypadá ošklivě. Co takhle získat uživatelsky přívětivý výstup, například „Id not found“ nebo „Customer not Found“. Můžeme použít IFERROR s funkcí VLOOKUP, abychom se vyhnuli #N/A.
K zachycení chyby ve VLOOKUP použijte níže uvedený vzorec.
= IFERROR (VLOOKUP (150, B2: E14,2,0), "Roll no not found")
16. VLOOKUP s MULTIPLE kritérii
VLOOKUP nemůže fungovat s více kritérii.Ano, pro vyhledávání hodnot pomocí vzorce VLOOKUP můžete mít pouze jedno kritérium.
Pokud například máte jméno a příjmení ve dvou samostatných sloupcích.
A teď, když chcete vyhledat hodnotu, jejíž křestní jméno je John a příjmení Dave, pak to nemůžete udělat normálně.
Ale existuje na to řešení. Můžete vytvořit samostatný sloupec zřetězením křestního jména a příjmení a poté vyhledat tento sloupec příjmení a příjmení.
Tady je INDEX-MATCH alternativa, která dokáže vyhledat více kritérií bez jakéhokoli pomocného sloupku.
17. VLOOKUP načte pouze první nalezenou hodnotu
Představte si, že máte nějaká data v centrální oblasti. Nyní chcete získat prvních 5 datových záznamů centrální oblasti. VLOOKUP vrátí pouze první centrální hodnotu ve všech pěti záznamech. Toto je hlavní pozadí.
To ale neznamená, že toho nemůžeme dosáhnout. Můžeme použít komplexní vzorec polí, jak je uvedeno níže.
= INDEX ($ C $ 2: $ C $ 14, SMALL (IF (G2 = $ A $ 2: $ A $ 14, ROW ($ A $ 2: $ A $ 14) -ROW ($ A $ 2) +1), ROW (1: 1)))
Napište tento vzorec a stiskněte CTRL+SHIFT+ENTER.
A je hotovo.
Vysvětlil jsem to v článku, jak VYHLEDAT více hodnot v aplikaci Excel.
Takže jo, lidi, v tomto článku jsem pokryl všechny možné aspekty funkce VLOOKUP podle mých znalostí. Pokud si myslíte, že jsem něco zmeškal, dejte mi prosím vědět v sekci komentáře níže.
Vlookup Top 5 hodnot s duplicitními hodnotami pomocí INDEX-MATCH v Excelu
VLOOKUP Více hodnot
VLOOKUP s Dynamic Col Index
Částečná shoda s funkcí VLOOKUP
Použijte VLOOKUP ze dvou nebo více vyhledávacích tabulek
Vlookup podle data v Excelu
Pomocí vzorce VLOOKUP zkontrolujte, zda hodnota existuje
Jak VYHLEDAT z jiného listu aplikace Excel
VLOOKUP s čísly a textem
Funkce IF, ISNA a VLOOKUP
Funkce ISNA a VLOOKUP
Funkce IFERROR a VLOOKUP