Funkce VLOOKUP aplikace Excel

VLOOKUP je funkce aplikace Excel pro získávání dat z tabulky uspořádané svisle. Hodnoty vyhledávání se musí objevit v prvním sloupci tabulky předané do VLOOKUP. VLOOKUP podporuje přibližnou a přesnou shodu a zástupné znaky (*?) Pro částečné shody.

Vertikální data | Čísla sloupců | Pouze vypadá správně | Odpovídající režimy | Přesná shoda | Přibližná shoda | První zápas | Zástupný znak | Obousměrné vyhledávání | Několik kritérií | # N / A chyby | Videa

V je pro vertikální

Účelem VLOOKUPu je získat informace z takto uspořádané tabulky:

Pomocí čísla objednávky ve sloupci B jako vyhledávací hodnoty může VLOOKUP získat ID zákazníka, částku, jméno a stát pro jakoukoli objednávku. Například pro získání jména zákazníka pro objednávku 1004 je vzorec:

=VLOOKUP(1004,B5:F9,4,FALSE) // returns "Sue Martin"

 Pro horizontální data můžete použít HLOOKUP, INDEX a MATCH nebo XLOOKUP. 
 VLOOKUP je založen na číslech sloupců 
 Když použijete VLOOKUP, představte si, že každý sloupec v tabulce je očíslován, počínaje zleva. Chcete-li získat hodnotu z konkrétního sloupce, zadejte příslušné číslo jako „index sloupce“. Například index sloupce pro načtení křestního jména níže je 2: 
  
 Příjmení a e-mail lze načíst pomocí sloupců 3 a 4: 

=VLOOKUP(H3,B4:E13,2,FALSE) // first name=VLOOKUP(H3,B4:E13,3,FALSE) // last name=VLOOKUP(H3,B4:E13,4,FALSE) // email address

 VLOOKUP vypadá jen dobře 
 VLOOKUP může vypadat pouze vpravo. Data, která chcete načíst (výsledné hodnoty), se mohou zobrazit v libovolném sloupci napravo od vyhledávaných hodnot: 
  
 Pokud musíte vyhledat hodnoty nalevo, viz INDEX a MATCH nebo XLOOKUP. 
 Přesné a přibližné shody 
 VLOOKUP má dva režimy shody, přesný a přibližný. Název argumentu, který řídí shodu, je „range_lookup“. Toto je matoucí název, protože se zdá, že má něco společného s rozsahy buněk jako A1: A10. Ve skutečnosti slovo „range“ v tomto případě odkazuje na „range of values“ – když je range_lookup TRUE, VLOOKUP bude odpovídat spíše rozsahu hodnot než přesné hodnotě. Dobrým příkladem toho je použití nástroje VLOOKUP k výpočtu hodnocení. 
 Je důležité si uvědomit, že range_lookup má výchozí hodnotu TRUE, což znamená, že VLOOKUP ve výchozím nastavení použije přibližnou shodu, což může být nebezpečné. Nastavit range_lookup na FALSE k vynucení přesné shody: 
 Poznámka: Můžete také zadat nulu (0) místo FALSE pro přesnou shodu. 
 Přesná shoda 
 Ve většině případů pravděpodobně budete chtít použít VLOOKUP v režimu přesné shody. To dává smysl, když máte jedinečný klíč, který lze použít jako vyhledávací hodnotu, například název filmu v těchto datech: 

  
 Vzorec v H6 pro vyhledání roku na základě přesné shody názvu filmu je: 
=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact match

 Přibližná shoda 
 V případech, kdy chcete nejlepší shodu, ne nutně přesnou shodu, budete chtít použít přibližný režim. Například níže chceme vyhledat sazbu provize v tabulce G5: H10. Hodnoty vyhledávání pocházejí ze sloupce C. V tomto příkladu musíme použít VLOOKUP v režimu přibližné shody, protože ve většině případů nikdy nebude nalezena přesná shoda. Vzorec VLOOKUP v D5 je nakonfigurován tak, aby provedl přibližnou shodu nastavením posledního argumentu na TRUE: 
  

=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate match

 VLOOKUP prohledá hodnoty ve sloupci G a vyhledá hodnotu. Pokud je nalezena přesná shoda, použije ji VLOOKUP. Pokud ne, VLOOKUP „ustoupí“ a porovná s předchozím řádkem. 
 Poznámka: Při použití režimu přibližné shody s VLOOKUP je nutné data seřadit vzestupně podle hodnoty vyhledávání. 
 První shoda 
 V případě duplicitních hodnot vyhledá VLOOKUP první shodu, když je režim shody přesný. Na obrazovce níže je VLOOKUP nakonfigurován tak, aby zjistil cenu barvy „Zelená“. Existují tři položky s zelenou barvou a funkce VLOOKUP vrací cenu za první položku, 17 USD. Vzorec v buňce F5 je: 

=VLOOKUP(E5,B5:C11,2,FALSE) // returns 17

  
 Zástupný znak shoda 
 Funkce VLOOKUP podporuje zástupné znaky, což umožňuje provést částečnou shodu s vyhledávací hodnotou. Například můžete použít VLOOKUP k načtení hodnot z tabulky po zadání pouze části vyhledávací hodnoty. Chcete-li použít zástupné znaky s VLOOKUP, musíte zadat režim přesné shody poskytnutím FALSE nebo 0 pro poslední argument range_lookup. Vzorec v H7 načte křestní jméno „Michael“ po zadání „Aya“ do buňky H4: 

=VLOOKUP($H$4&"*",$B$5:$E$104,2,FALSE)

  
 Zde si přečtěte podrobnější vysvětlení. 
 Obousměrné vyhledávání 
 Uvnitř funkce VLOOKUP sloupec argument indexu je obvykle pevně zakódován jako statické číslo.Můžete však také vytvořit dynamický index sloupce pomocí funkce MATCH k vyhledání pravého sloupce. Tato technika umožňuje vytvořit dynamické obousměrné vyhledávání, které se shoduje s řádky i sloupci. Na obrazovce níže je VLOOKUP nakonfigurován tak, aby prováděl vyhledávání na základě jména a měsíce. Vzorec v H6 je: 

=VLOOKUP(H4,B5:E13,MATCH(H5,B4:E4,0),0)

  
 Další podrobnosti najdete v tomto příkladu. 
 Poznámka: INDEX a MATCH jsou obecně flexibilnější způsob provádění obousměrného vyhledávání. 
 Více kritérií 
 Funkce VLOOKUP nativně nezpracovává více kritérií. Můžete však použít sloupec pomocníka k propojení více polí dohromady a tato pole použít jako více kritérií uvnitř VLOOKUP. V níže uvedeném příkladu je sloupec B pomocným sloupcem, který spojuje křestní jméno a příjmení společně s tímto vzorcem: 

=C5&D5 // helper column

 VLOOKUP je nakonfigurován tak, aby udělal stejnou věc k vytvoření vyhledávací hodnoty. Vzorec v H6 je: 

=VLOOKUP(H4&H5,B5:E13,4,0)

  
 Podrobnosti najdete v tomto příkladu. 
 Poznámka: INDEX a MATCH a XLOOKUP jsou robustnější způsoby zpracování vyhledávání na základě více kritérií. 
 VLOOKUP a chyby # N / A 
 Pokud použijete VLOOKUP, nevyhnutelně narazíte na chybu # N / A. Chyba # N / A znamená pouze „nenalezena“. Například na obrazovce níže vyhledávací hodnota „Toy Story 2“ ve vyhledávací tabulce neexistuje a všechny tři vzorce VLOOKUP vrátí # N / A: 
  
 Jedním ze způsobů „zachycení“ chyby NA je použití funkce IFNA takto: 
  
 Vzorec v H6 je: 

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"Not found")

 Zprávu lze upravit podle potřeby. Chcete-li vrátit nic (tj. Zobrazit prázdný výsledek), když VLOOKUP vrátí # N / A, můžete použít prázdný řetězec, jako je tento: 

=IFNA(VLOOKUP(H4,B5:E9,2,FALSE),"") // no message

 Chyba # N / A je užitečná, protože vám říká, že něco není v pořádku. V praxi existuje řada důvodů, proč se tato chyba může zobrazit, včetně: 

 Vyhledávací hodnota v tabulce neexistuje 
 Vyhledávací hodnota je chybně napsána nebo obsahuje extra prostor 
 Režim shody je přesný, ale měl by být přibližný 
 Rozsah tabulky není zadán správně 
 Kopírujete VLOOKUP a odkaz na tabulku není uzamčeno 

 Další informace: VLOOKUP bez chyb # N / A 
 Další informace o VLOOKUP 

 Další příklady VLOOKUP 
 Videa VLOOKUP 
 23 tipů pro používání VLOOKUP 

 Další poznámky 

 Range_lookup určuje, zda se hodnota musí přesně shodovat nebo ne. Výchozí hodnota je TRUE = povolit nepřesnou shodu. 
 Nastavte range_lookup na FALSE, aby vyžadovala přesnou shodu, a TRUE, abyste povolili nepřesnou shodu. 
 Pokud je range_lookup TRUE ( výchozí nastavení), bude nepřesná shoda způsobovat, že funkce VLOOKUP bude odpovídat nejbližší hodnotě v tabulce, která je stále menší než hodnota. 
 Když je range_lookup vynechán, funkce VLOOKUP umožní nepřesnou hodnotu shoda, ale pokud existuje, použije přesnou shodu. 
 Pokud je range_lookup TRUE (výchozí nastavení), ujistěte se, že jsou vyhledávací hodnoty v prvním řádku tabulky seřazeny vzestupně. V opačném případě může funkce VLOOKUP vrátit nesprávnou nebo neočekávanou hodnotu. 
 Pokud je range_lookup FALSE (vyžaduje přesnou shodu), hodnoty v prvním sloupci tabulky není nutné třídit.