Jak používat vzorec VLOOKUP k získání dat ze seznamu aplikace Excel. Odstraňujte problémy se SVYHLEDÁVÁNÍM, když se něco pokazí. Podrobná videa a sešity aplikace Excel s příklady VLOOKUP.
VLOOKUP Video
Postupujte podle pokynů pro vytvoření vzorce VLOOKUP ve videu pro vyhledávání ceny produktu, viz níže. Písemné pokyny jsou pod videem. Získejte ukázkový soubor Vyhledání ceny produktu, který bude následovat spolu s videem (soubor ke stažení č. 4).
Vyberte umístění vyhledávací tabulky
Je vhodné uložit každé vyhledávání tabulka na samostatném listu v sešitu. Poté, když přidáváte a mazáte řádky ve vyhledávacích tabulkách, nebudete omylem přidávat nebo mazat řádky v žádné jiné tabulce.
V tomto příkladu je vyhledávací tabulka je na listu s názvem Produkty.
Vytvořit vyhledávací tabulku
Vyhledávací vzorce mohou pracovat svisle, hledat hodnoty dolů ve sloupci, nebo mohou pracovat vodorovně, hledat hodnoty napříč řádkem . V tomto příkladu budou informace uloženy svisle s hodnotami dolů ve sloupci a později použijeme vzorec VLookup k provedení vertikálního vyhledávání.
- Do prvního řádku zadejte nadpisy
- První sloupec by měl obsahovat jedinečné hodnoty klíčů, na kterých bude vyhledávání založeno. V tomto příkladu najdete cena za konkrétní kód produktu.
- Pokud máte v listu další údaje, ponechte alespoň jeden prázdný řádek v dolní části tabulky a jeden prázdný sloupec v pravé části tabulky, abyste oddělili vyhledávací tabulka z ostatních dat.
Poznámka: Chcete-li na tabulku lépe odkazovat, můžete rozsah pojmenovat. Zde jsou pokyny: Pojmenování rozsahu ▲ NAHORU
Argumenty funkce VLOOKUP
Funkce VLOOKUP má čtyři argumenty:
- lookup_value : Jakou hodnotu chcete vyhledat? V tomto příkladu je kód produktu v buňce A7 a chcete najít jeho pr název oduct.
- table_array: Kde je vyhledávací tabulka? Pokud použijete absolutní odkaz ($ A $ 2: $ C $ 5), místo relativního odkazu (A2: C5) bude snazší zkopírovat do vzorce do jiných buněk. Nebo pojmenujte vyhledávací tabulku a odkazujte na ni podle názvu.
- col_index_num: Který sloupec má hodnotu, kterou chcete vrátit? V tomto příkladu jsou názvy produktů ve druhém sloupci vyhledávací tabulky.
- : Chcete přesnou shodu? Je přibližná shoda v pořádku?
Pokud použijete TRUE jako poslední argument nebo vynecháte poslední argument, lze vrátit přibližnou shodu. Tento příklad má jako poslední argument NEPRAVDA, takže pokud kód produktu nebude nalezen, bude výsledek # N / A. (Poznámka: Excel je docela shovívavý a přijme 0 místo FALSE a 1 místo TRUE.) ▲ TOP
Vytvořit vzorec VLookup
Jakmile vytvoříte vyhledávací tabulku, můžete v sešitu vytvořit další vzorce a stáhnout informace ze seznamu produktů. Chcete-li například připravit fakturu, můžete zadat kód produktu a vzorce získají název produktu nebo cenu z tabulky produktu.
V tomto příkladu je faktura vytvořena na listu s názvem Faktura. Vzorec VLOOKUP by měl najít přesnou shodu pro kód produktu a vrátit název produktu.
Chcete-li vytvořit vzorec VLOOKUP, který vypočítává cenu produktu, postupujte takto:
- Vyberte list Faktura
- Zadejte kód produktu A23 do buňky A7
- V buňce B7 spusťte vzorec VLOOKUP:
= VLOOKUP ( - klikněte na buňka A7 pro zadání jejího odkazu.
- Zadejte čárku a ukončete první argument
- Vyberte list produktů
- Vyberte buňky A2: C5 (seznam produktů )
- Stisknutím klávesy F4 změníte odkazy na buňky na absolutní. Vzorec by nyní měl vypadat takto:
= VLOOKUP (A7, Produkty! $ A $ 2: $ C $ 5 - Chcete-li ukončit druhý argument, zadejte čárku.
- Zadejte 2, což je sloupec ve vyhledávací tabulce, který obsahuje název produktu.
- Čárku ukončete třetí argument.
- Zadejte FALSE, chcete-li určit, že bude nalezena přesná shoda pro kód produktu, a přidejte závěrečnou závorku.
- Vzorec by nyní měl vypadat l podobně:
= VLOOKUP (A7, Produkty! $ A $ 2: $ C $ 5,2, FALSE) - Stisknutím klávesy Enter dokončíte vzorec. Zobrazí se název produktu.
Poznámka: Chcete-li vrátit cenu produktu, vytvořte vzorec VLOOKUP, který odkazuje na sloupec 3 vyhledávací tabulky. Například do buňky C7 zadejte následující vzorec:
= VLOOKUP (A7, Products! $ A $ 2: $ C $ 5,3, FALSE) ▲ TOP
Vzorec VLOOKUP pro rozsah hodnot
V některých situacích je preferována přibližná shoda, takže několik hodnot vrátí stejný výsledek. Například při klasifikaci studentských prací by všechny papíry se známkou 85 a více měly dostat známku A.
Chcete-li zobrazit postup vytvoření tohoto vzorce, podívejte se na níže uvedené video VLOOKUP. Písemné pokyny jsou pod videem.
V tomto příkladu je vyhledávací tabulka vytvořena na listu s názvem Grades. Chcete-li vytvořit vyhledávací tabulku, zadejte ve sloupci A minimální skóre pro každou známku. Ve sloupci B zadejte odpovídající známku. Seřaďte skóre ve vzestupném pořadí.
Buňky A2: B6 byly pojmenovány GradeList.
Skóre se zadává na list s názvem Report Card, kde vzorec VLOOKUP vypočítává známku.
V snímek obrazovky níže, vzorec byl zkopírován do řádku 6 a vzorec můžete vidět v buňce C6.
Zkombinujte VLOOKUP S MATCH
Místo zadávání čísla sloupce do vzorce VLOOKUP použijte funkci MATCH k nalezení správného sloupce ve vyhledávací tabulce. To má několik výhod:
- Zpřístupňuje vzorec, takže je jednodušší kopírovat vzorec do listu.
- Může zabránit problémům, pokud jsou přidány nové sloupce ve vyhledávací tabulce nebo pokud jsou uspořádány vyhledávací sloupce.
Toto video ukazuje kroky a pod videem jsou písemné pokyny pro další příklad.
Váš prohlížeč nemůže tento rámeček zobrazit. Zde je odkaz na stránku
VLOOKUP s MATCH pro podrobnosti objednávky
V tomto příkladu vrátí vzorec VLOOKUP podrobnosti objednávky z vyhledávací tabulky na základě ID čísla objednávky . Zde je vyhledávací tabulka s názvem tblOrders.
POZNÁMKA: Tento příklad je v ukázkovém sešitu č. 1 na listu s názvem OrdersMATCH.
Zde je list se vzorci VLOOKUP. Chceme region, datum objednávky a částku objednávky pro každou objednávku, takže jsou potřeba 3 vzorce VLOOKUP.
Pokud jsou do vzorce zadána čísla sloupců, je v každém sloupci potřeba jiný vzorec:
- Region: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
Funkce MATCH
Místo zadávání čísla sloupce do vzorce VLOOKUP můžeme použít funkci MATCH. Funkce MATCH najde pozici položky v seznamu a vrátí číslo pozice.
Na snímku obrazovky níže vrátí MATCH vzorec hodnotu 2 jako pozici „Region“ v buňkách nadpisu (A1: D1) pro vyhledávací tabulku.
= MATCH ( C5, Orders_ALL! $ A $ 1: $ D $ 1, 0)
POZNÁMKA: Aby tato technika fungovala správně, nadpisy na listu VLOOKUP musí přesně odpovídat nadpisům vyhledávací tabulky. Aby byla zajištěna přesná shoda, jsou buňky záhlaví VLOOKUP propojeny s buňkami záhlaví vyhledávací tabulky.
Přidat MATCH do VLOOKUP
Chcete-li přidat funkci MATCH do vzorce VLOOKUP, stačí nahradit zadané číslo sloupce
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
s MATCH vzorcem:
= VLOOKUP ($ B6, tblOrdersALL, MATCH) (C5, Orders_ALL! $ A $ 1: $ D $ 1, 0), 0)
Zkopírujte vzorec VLOOKUP napříč
Místo toho, abyste v každém sloupci potřebovali jiný vzorec, můžete vzorec zkopírovat napříč a použít ve všech sloupcích stejný vzorec. V každém sloupci bude odkazovat na buňku záhlaví v daném sloupci a najde svou pozici ve vyhledávací tabulce.
POZNÁMKA: Pokud vyplňujete vzorec ve sloupcích s jiným formátováním, postupujte takto:
- Vyberte buňku se vzorcem, který chcete zkopírovat
- Ukažte na úchyt výplně ve vybrané buňce (malý čtverec v pravém dolním rohu)
- Stiskněte PRAVÉ tlačítko myši a přetáhněte na poslední buňku, která potřebuje vzorec.
- Uvolněte tlačítko myši a klikněte na Vyplnit bez formátování
Kombinovat IF a VLOOKUP
Můžete použít IF vzorec se vzorcem VLookup, pokud jsou nalezeny přesné hodnoty, a prázdný řetězec pokud nebyl nalezen.
Chcete-li zobrazit kroky pro nastavení vzorce IF a VLOOKUP, můžete se podívat na toto krátké video. Písemné pokyny jsou pod videem.
Chcete-li skrýt chyby kombinací IF s VLOOKUP, postupujte takto:
- Na listu Faktura v buňce A8 zadejte produkt kód A28. Pokud má vzorec VLookup v buňce B8 jako čtvrtý argument FALSE, je výsledek # N / A, protože ve vyhledávací tabulce neexistuje přesná shoda pro kód produktu.
- Vzorec VLookup zabalte do IF vzorec (v tomto příkladu byl pojmenován seznam produktů) pomocí funkce ISNA ke kontrole chyby # N / A:
- Stiskněte klávesu Enter a buňka se zobrazí prázdná. Protože nebyla nalezena žádná přesná shoda, vzorec VLookup vrátil # N / A, takže výsledek funkce ISNA je PRAVDA. Vzorec IF to převedl na prázdný řetězec.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), „“, VLOOKUP (A8, ProductList, 2, FALSE))
Kombinujte IFERROR a VLOOKUP
Díky Chipovi Pearsonovi za navržení tohoto vzorce.
V aplikaci Excel 2007 byla představena nová funkce IFERROR. . Můžete použít vzorec IFERROR s VLookup ke kontrole hodnoty několika tabulek a vrácení souvisejících informací, když budou nalezeny. V tomto příkladu mají objednávkové listy tři regiony, západní, východní a střední. Na každém listu je pojmenovaný rozsah – OrdersW, OrdersE a OrdersC.
Na listu s názvem Objednávky můžete zadat ID objednávky a poté použít VLOOKUP s IFERROR ke kontrole každého pojmenovaného rozsahu a zobrazit informace o vybrané objednávce.
- Na listu objednávky v buňce B6 zadejte 4 jako ID objednávky. Tato objednávka byla umístěna do centrální oblasti.
- Chcete-li jednoduše zkontrolovat tabulku východní oblasti, vzorec VLOOKUP v buňce C6 by byl:
= VLOOKUP (B6, OrdersE, 2, FALSE) - Stiskněte klávesu Enter a vzorec VLOOKUP vrátí # N / A, protože ID objednávky 4 není v tabulce objednávek regionů Východu.
- Protože objednávka mohla mít byly umístěny v některé ze tří oblastí, potřebujete vzorec, který zkontroluje každou tabulku.
Pokud ID objednávky není nalezeno v první tabulce, měl by vzorec zkontrolovat druhou tabulku. Pokud ID objednávky není ve druhé tabulce tabulka, měla by zkontrolovat třetí tabulku. Pokud ID objednávky ve třetí tabulce není, měla by se v buňce objevit zpráva „Nenalezeno“. - Vzorec IFERROR umožňuje zkontrolovat hodnotu, poté určete, co dělat, pokud dojde k chybě. Pokud používáte IFERROR s existujícím vzorcem, můžete místo chyby # N / A zobrazit „Nenalezeno“:
= IFERROR (VLOOKUP (B6, OrdersE, 2, FALSE), „Nebylo nalezeno“) - Chcete-li zkontrolovat všechny tři tabulky, můžete použít následující vzorce IFERROR a VLOOKUP:
Tím se zkontroluje tabulka OrdersE a pokud se zjistí chyba, zkontroluje se tabulka OrdersW a poté OrdersC. Pokud ID objednávky není nalezeno v žádné ze tří tabulek, zobrazí se v buňce zpráva Nenalezeno.
Můžete také zkontrolovat více tabulek ve starších verzích aplikace Excel, kde IFERROR není k dispozici, pomocí delší vzorec:
VLOOKUP pro kombinované hodnoty
V některých tabulkách nemusí být jedinečné hodnoty libovolný sloupec ve vyhledávací tabulce. Například v níže uvedené tabulce je bunda uvedena dvakrát ve sloupci A. Pro každou kombinaci bundy a velikosti však existuje pouze jeden záznam – bunda střední v řádku 4 a bunda velká v řádku 5.
Pokud potřebujete zjistit cenu za velkou bundu, VLOOKUP založený pouze na sloupci A vrátí cenu za první uvedenou bundu (střední ). Podhodnoťte bundu – prodali byste ji za 60,00 místo za 65,00.
Chcete-li vytvořit jedinečné vyhledávací hodnoty, můžete vložit nový sloupec na levé straně tabulky a pomocí vzorce kombinovat produkt a velikost. V buňce A2 vzorec kombinuje hodnotu v B2 a znak kanálu a hodnotu v C2.
= B2 & „|“ & C2
Zkopírujte tento vzorec do posledního řádku dat, takže každý řádek má ve sloupci A jedinečnou hodnotu.
Poznámka : Místo znaku kanálu můžete použít jiný znak, který není zahrnut ve vašich datech.
Potom v vzorec VLOOKUP, zkombinujte produkt a velikost jako Lookup_value. V buňce H1 vzorec kombinuje hodnotu v F1 a znak potrubí a hodnotu v G1.
= VLOOKUP (F1 &“ | „& G1, $ A $ 2: $ D $ 5,4, FALSE)
Poznámka: Cena je nyní v sloupec 4, místo sloupce 3. ▲ NAHORU
Odstraňování problémů se vzorcem VLOOKUP
Váš vzorec VLOOKUP může vrátit # N / A, i když hodnota, kterou hledáte, se zdá být ve vyhledávací tabulce. Běžné příčiny jsou:
Text vs. číslo
Mezerové znaky
Další znaky
Text vs. číslo
Běžnou příčinou této chyby je, že jednou z hodnot je číslo a druhou text. Například vyhledávací tabulka může obsahovat „123 (text) a hodnota k vyhledání je 123 (číslo).
- Pokud je to možné, převeďte text na čísla pomocí jedné z zde zobrazené metody: Převod textu na čísla
- Pokud nemůžete převést data, můžete převést vyhledávací hodnotu ve vzorci VLOOKUP pomocí některého z níže uvedených řešení:
- Vyhledávací hodnoty jsou Text, tabulka má čísla
- Vyhledávací hodnoty jsou čísla, tabulka má text
- Chcete-li zjistit, které hodnoty jsou text a která jsou čísla, přečtěte si podrobný postup řešení problémů s číslem nebo textem
1. Hodnoty vyhledávání jsou Text a tabulka obsahuje Čísla
Pokud vyhledávací tabulka obsahuje čísla a vyhledávanou hodnotou je text, použijte vzorec podobný tomuto:
= VLOOKUP (–A7, Produkty!$ A $ 2: $ C $ 5,3, FALSE)
Dvojité unární (-) převádí text na číslo a bude fungovat správně, i když jsou vyhledávacími čísly čísla.
2. Vyhledávacími hodnotami jsou čísla a tabulka obsahuje text
Pokud vyhledávací tabulka obsahuje text a hodnota, která má být vyhledána, je číselná, použijte vzorec podobný tomuto:
= VLOOKUP (A7 & „“), Produkty! $ A $ 2: $ C $ 5,3, FALSE)
NEBO
= VLOOKUP (TEXT (A7, „00000“) „Produkty! $ A $ 2: $ C $ 5,3, FALSE)
Funkce TEXT převede číslo na text a bude fungovat správně, i když jsou vyhledávacími hodnotami text. V prvním příkladu vytvoří operátor & textový řetězec z neformátovaného čísla. Ve druhém příkladu by číslo formátované s úvodními nulami (např. 00123) odpovídalo textovému „číslu“ s úvodními nulami.
Chcete-li zobrazit kroky pro vyřešení problému VLOOKUP, když má vyhledávací tabulka textové hodnoty, podívejte se na tento krátký videonávod.
Mezery v jedné hodnotě, nikoli ve druhé
Další možnou příčinou, že není nalezena odpovídající hodnota, je rozdíl v mezerách. Jedna z hodnot může obsahovat úvodní mezery (nebo koncové mezery nebo vložené mezery) a druhá není t. Chcete-li hodnoty otestovat, můžete pomocí funkce LEN zkontrolovat délku každé hodnoty.
Například: = LEN (A7) vrátí počet znaků v buňce A7. Mělo by se to rovnat počtu znaků v odpovídající buňce ve vyhledávací tabulce.
Pokud je to možné, odstraňte zbytečné mezery a vzorec VLOOKUP by měl fungovat správně. Pokud mezery nemůžete odstranit, použijte funkci TRIM ve VLOOKUP k odstranění úvodních, koncových nebo duplikovaných mezer. Například:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Další znaky
Pokud samotná funkce TRIM problém nevyřeší , můžete vyzkoušet jeden z následujících návrhů:
- Funkce SUBSTITUTE
- Funkce CLEAN
- Makro pro odebrání webových znaků
Funkce SUBSTITUTE
Pomocí funkce SUBSTITUTE můžete odstranit nežádoucí znaky. Na blogu Contextures je příklad: Vyčištění dat aplikace Excel pomocí funkce TRIM a SUBSTITUTE
Funkce CLEAN
Dalším způsobem, jak vyřešit problémy s VLOOKUP, je funkce CLEAN, která dokáže odstranit některé nežádoucí znaky z textu. Více informací o funkci CLEAN najdete v tomto příspěvku na blogu Contextures: 30 funkcí aplikace Excel za 30 dní: 29 – CLEAN
Webové znaky v jedné hodnotě, nikoli ve druhé
Pokud jste zkopírovali data z webové stránky, může obsahovat neporušený prostor HTML (& nbsp) znaky. David McRitchie napsal makro, které je odstraní, spolu s o další mezery znaky
Problémy při řazení vzorce VLOOKUP
Vzorec VLOOKUP může nejprve vrátit správné výsledky, ale pokud je seznam položek seřazen, zobrazí nesprávné výsledky. K tomu může dojít, pokud odkaz na vyhledávací hodnotu obsahuje název listu. Například:
= VLOOKUP („Objednávkový formulář“! B5, Produkty! $ B $ 2: $ C $ 6,2, FALSE)
POZNÁMKA: K tomuto problému může dojít u jiných funkcí také, například vzorec pro vyhledávání INDEX / MATCH.
Podívejte se na toto video, kde najdete kroky pro vyřešení problému, a stáhněte si ukázkový soubor VLOOKUP Sorting problem to follow along. Písemné pokyny jsou pod videem.
Názvy listů v odkazu
Tento typ odkazu se vytvoří, když při vytváření vzorce kliknete na jiný list. Jakmile to uděláte, Excel přidá název listu ke všem následným odkazům ve vzorci.
Na snímku obrazovky výše je Dress v buňce B9 a buňka C9 ukazuje správnou cenu 30 $.
Po seřazení produktů AZ se však Dress přesune nahoru do buňky B5, ale vzorec v buňce C5 nadále odkazuje do buňky B9. Z důvodu názvů listů v odkazech Excel zachová původní odkazy namísto zachování odkazu na aktuální řádek. Buňka C5 zobrazuje místo svetrů cenu za svetr. ▲ TOP
Vyřešit problém
Chcete-li problém vyřešit, odstraňte ze seznamu nepotřebné názvy listů VLOOKUP odkazy na buňky. Zde je upravený vzorec pro buňku C5:
= VLOOKUP (B5, Produkty! $ B $ 2: $ C $ 6,2, FALSE)
Po odstranění nepotřebných názvů listů, seznam lze bezpečně seřadit a u každé položky se zobrazí správné výsledky.
Stáhnout ukázkové soubory
1. Získejte ukázkový sešit VLOOKUP (Excel 2007 a novější). Soubor ZIP je ve formátu xlsx a neobsahuje žádná makra.
2. Pro Excel 2003 získáte tuto verzi ukázkového souboru VLOOKUP. Soubor ZIP je ve formátu xls a neobsahuje žádná makra.
3. Chcete-li zobrazit problém, který může nastat při řazení pomocí VLOOKUP, stáhněte si tento ukázkový soubor VLOOKUP Třídění problému. Soubor ZIP je ve formátu xlsx a neobsahuje žádná makra.
4. Získejte sešit Vyhledání ceny produktu, který se používá ve výukovém videu.Soubor ZIP je ve formátu xlsx a neobsahuje žádná makra. ▲ TOP
Další výukové programy
Odstraňování problémů s číslem / textem VLOOKUP
VLOOKUP z jiného sešitu
HLOOKUP
ZVOLTE Funkce
Vyhledávání – 2 kritéria
VYHLEDÁVÁNÍ
INDEX / MATCH
Porovnání vyhledávacích funkcí
Seznam funkcí