VLOOKUP Levá funkce v aplikaci Excel

Funkce VLOOKUP aplikace Excel slouží k vyhledání a vrácení informací z tabulky dat na základě vyhledávací hodnoty, kterou zvolíte.

Typicky VLOOKUP vyžaduje, aby hodnota vyhledávání byla v levém sloupci tabulky dat a funkce vrátí další pole dat umístěné ve stejném řádku napravo od této hodnoty.

Nicméně kombinací funkce VLOOKUP s funkcí CHOOSE lze vytvořit vzorec levého vyhledávání, který:

• Umožňuje vyhledávací hodnotu z libovolného sloupce v datové tabulce
• Vrací informace umístěné v libovolném sloupci nalevo od vyhledávací hodnoty

Poznámka: Tyto pokyny platí pro verze Excel 2019, 2016, 2013, 2010 a Excel pro Office 365.

01 z 05

Výuková příručka: Použití funkcí VLOOKUP a CHOOSE ve vzoru levého vyhledávání

Chcete-li vytvořit vzorec levého vyhledávání, který je uveden v ukázkovém obrázku, použijte vzorec:

= VLOOKUP ($ D $ 2, výběr ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)

V tomto příkladu vzorec umožňuje najít součást dodávanou různými společnostmi uvedenými ve sloupci 3 tabulky údajů.

Úkolem funkce CHOOSE ve vzorci je vytlačit VLOOKUP, aby věřil, že sloupec 3 je sloupec 1. Výsledkem je, že název společnosti může být použit jako vyhledávací hodnota pro nalezení názvu součásti dodané každou společností.

Zadejte výukové údaje

1. Zadejte nadpis Dodavatel do buňky D1.
2. Zadejte nadpis Část v buňce E1.
3. Zadejte tabulku dat zobrazenou na výše uvedeném obrázku do buněk D4 až F9
4. Řádky 2 a 3 zůstanou prázdné, aby vyhovovaly kritériím vyhledávání a vzorci levého vyhledávání, který byl vytvořen během tohoto kurzu

Otevřete dialogové okno VLOOKUP

Ačkoli je možné zadat vzorec přímo do buňky F1 v listu, mnoho lidí má potíže se syntaxem vzorce.

V tomto případě je bezpečnější použít dialogové okno VLOOKUP. Téměř všechny funkce aplikace Excel mají dialogové okno, které umožňuje zadat jednotlivé argumenty funkce na samostatném řádku.

1. Klikněte na buňku E2 pracovního listu. E2 je místo, kde budou zobrazeny výsledky levého vzoru vyhledávání.
2. Klikněte na tlačítko Vzorce záložku pásku.
3. Klikněte na tlačítko Vyhledávání a reference v pásu k otevření rozevíracího seznamu funkcí.
4. Klikněte na VLOOKUP v seznamu otevřete dialogové okno funkce.

02 z 05

Zadání argumentů do dialogového okna VLOOKUP

Argumenty funkce jsou hodnoty použité funkcí k výpočtu výsledku.

V dialogovém okně funkce je název každého argumentu na samostatném řádku, po němž následuje pole, do kterého se má zadat hodnota.

Zadejte následující hodnoty pro každý argument VLOOKUP na správný řádek dialogového okna, jak je znázorněno na přiloženém obrázku.

Hodnota vyhledávání

Hodnota vyhledávání je pole informace, které se používá k vyhledání pole tabulky. VLOOKUP vrátí další pole dat ze stejného řádku jako hodnota vyhledávání.

Tento příklad používá odkaz na buňku na místo, kde bude název společnosti zadán do listu. Výhodou je, že umožňuje snadné změny názvu společnosti bez úpravy vzorce.

1. Klikněte na tlačítko lookup_value v dialogovém okně.
2. Klikněte na buňku D2 přidat tento článek odkaz na lookup_value řádek.
3. zmáčkni F4 klávesu na klávesnici, aby absolutní odkaz na buňku - $ D $ 2.

Absolutní odkazy na buňky

Absolutní odkazy na buňky se používají pro argumenty vyhledávací hodnoty a argumenty tabulky, aby se předešlo chybám, pokud je vzorec vyhledávání zkopírován do jiných buněk v listu.

Zadání funkce CHOOSE

Argument tabulkového pole je blok souvislých dat, ze kterých jsou získány konkrétní informace.

Obvykle VLOOKUP hledá pouze vpravo od argumentu hodnoty vyhledávání, aby nalezl data v tabulkovém poli. Chcete-li, aby to vypadalo doleva, VLOOKUP musí být podveden přesouváním sloupců v tabulkovém poli pomocí funkce CHOOSE.

V tomto vzorci vykonává funkce CHOOSE dvě úlohy:

1. Vytvoří pole tabulky, které má pouze dva sloupce (sloupce D a F).
2. Změní pořadí vpravo doleva sloupců v poli tabulky tak, že sloupec F přijde první a sloupec D je druhý.

Zadávání funkcí

Při ručním zadávání funkcí musí být každá z argumentů funkce oddělena čárkou.

1. V dialogovém okně funkce VLOOKUP klepněte na tlačítko Table_array řádek.
2. Zadejte následující VYBRAT funkce:VYBERTE ({1,2}, $ F: $ F, $ D: $ D)

Číslo sloupcového indexu

Obvykle indexové číslo sloupce označuje, který sloupec tabulkového pole obsahuje data, která jsou po. V tomto vzorci však odkazuje na pořadí sloupců nastavených funkcí CHOOSE.

Funkce CHOOSE vytvoří pole tabulky, které je dva sloupce široké se sloupcem F nejprve následovaným sloupcem D. Vzhledem k tomu, že požadovaná informace - název dílu - je ve sloupci D, hodnota argumentu sloupcového indexu musí být nastavena na hodnotu 2.

1. Klikněte na tlačítko Col_index_num v dialogovém okně.
2. Zadejte a 2 v tomto řádku.

Rozsah vyhledávání

Argument Range_lookup VLOOKUP je logická hodnota (TRUE nebo FALSE only), která označuje, zda chcete VLOOKUP najít přesnou nebo přibližnou shodu s hodnotou vyhledávání.

• Pokud je TRUE nebo pokud je tento argument vynechán, VLOOKUP vrátí buď přesnou shodu s hodnotou Lookup_value, nebo pokud není nalezena přesná shoda, VLOOKUP vrátí další největší hodnotu. Pro tento vzorec musí být data v prvním sloupci table_array seřazeny ve vzestupném pořadí.
• Pokud FALSE, VLOOKUP používá přesně shodu s hodnotou Lookup_value. Pokud existují dvě nebo více hodnot v prvním sloupci table_array, které odpovídají hodnotě vyhledávání, použije se první nalezená hodnota. Pokud není nalezena přesná shoda, vrátí se chyba # N / A.

V tomto tutoriálu, protože hledáme konkrétní název, Range_lookup je nastaven na Nepravdivé takže pouze vzorky jsou vráceny vzorcem.

1. Klikněte na tlačítko Range_lookup v dialogovém okně.
2. Zadejte slovo Nepravdivé v tomto řádku označujeme, že chceme, aby VLOOKUP vrátil přesnou shodu pro data, která hledáme.
3. Klikněte na tlačítko OK dokončete vzorec levého vyhledávacího vzorce a zavřete dialogové okno.
4. Protože jsme ještě nezadali název firmy do buňky D2, objeví se v buňce E2 chyba # N / A.

03 ze dne 05

Vrací data pomocí vzorce levého vyhledávání

Chcete-li zjistit, které společnosti dodávají, které součásti zadejte název společnosti do buňky D2 a stiskněte tlačítko ENTER na klávesnici.

Název součásti se zobrazí v buňce E2.

1. Klikněte na buňku D2 ve vašem pracovním listu.
2. Typ Gadgets Plus do buňky D2 a stiskněte tlačítko ENTER na klávesnici.
3. Text "Gadgets" - část dodávaná společností Gadgets Plus - by měla být zobrazena v buňce E2.

Vyzkoušejte vzorec vyhledávání dále zadáním dalších názvů firem do buňky D2 a odpovídající část názvu by se měla zobrazit v buňce E2.

Pokud se v buňce E2 zobrazí chybová zpráva, jako je # N / A, zkontrolujte chyby pravopisu v buňce D2.

04 z 05

Vytvoření pole dvou sloupců tabulky

Syntaxe funkce CHOOSE je:

= CHOOSE (Index_number, Value1, Value2, … Value254)

Funkce CHOOSE obvykle vrátí jednu hodnotu ze seznamu hodnot (Value1 to Value254) na základě zadaného indexového čísla.

Pokud je indexové číslo 1, funkce vrátí hodnotu 1 ze seznamu; pokud je indexové číslo 2, funkce vrátí hodnotu 2 ze seznamu a tak dále.

Při zadání více indexových čísel vrátí funkce více hodnot v libovolném požadovaném pořadí. Získání volby CHOOSE pro návrat více hodnot se provádí vytvořením pole.

Chcete-li zadat pole, obklopte čísla zadaná s kudrnatými příchytkami nebo závorami. Pro indexové číslo jsou zadána dvě čísla:{ 1,2 }.

Je třeba poznamenat, že volba CHOOSE není omezena na vytvoření tabulky s dvěma sloupci. Zahrnutím dalšího čísla do pole - například {1,2,3} - a dalšího rozsahu argumentu hodnot, CHOOSE vytvoří tabulku se třemi sloupci.

Další sloupce umožňují vrátit různé informace pomocí vzorce levého vyhledávání změnou argumentu sloupce indexu sloupce VLOOKUP na číslo sloupce obsahující požadované informace.

05 z 05

Změna pořadí sloupců pomocí funkce CHOOSE

Ve funkci CHOOSE použitý v tomto vzorci:

VYBERTE ({1,2}, $ F: $ F, $ D: $ D)

rozsah pro sloupec F je uveden před sloupcem D.

Vzhledem k tomu, že funkce CHOOSE nastavuje tabulku tabulky VLOOKUP (zdroj dat pro tuto funkci) přepínání pořadí sloupců ve funkci CHOOSE je předáno podél VLOOKUP.

Co se týče VLOOKUP, pole tabulky má pouze dva sloupce se sloupcem F vlevo a sloupec D vpravo. Vzhledem k tomu, že sloupec F obsahuje název společnosti, kterou chceme vyhledat, a protože sloupec D obsahuje názvy součástí, může VLOOKUP provádět své pravidelné vyhledávací povinnosti při hledání dat, která se nachází nalevo od vyhledávací hodnoty.

Výsledkem je, že společnost VLOOKUP může používat název společnosti k nalezení části, kterou dodávají.