Vlevo vyhledávací vzorec aplikace Excel pomocí příkazu VLOOKUP

01 z 03

Najděte data nalevo

Přehled vyhledávání vlevo ve formátu 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.

Normálně VLOOKUP vyžaduje, aby vyhledávací hodnota 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.

Kombinací VLOOKUP s funkcí CHOOSE ; může však být vytvořena vzorec pro vyhledávání vlevo, který:

• umožňuje volbu vyhledávací hodnoty z libovolného sloupce v datové tabulce
• vrátí informace umístěné v libovolném sloupci nalevo od vyhledávací hodnoty

Příklad: Použití funkcí VLOOKUP a CHOOSE v levém vzoru vyhledávání

Níže uvedené kroky vytvoří vzorec levého vyhledávání, který je vidět na obrázku výše.

Vzorec

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

umožňuje najít část dodávanou různými společnostmi uvedenými ve sloupci 3 tabulky údajů.

Úkolem funkce CHOOSE ve vzorci je zkreslit VLOOKUP do vědomí, že sloupec 3 je vlastně 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í.

Tutorial Steps - Zadání výukových dat

1. Do uvedených buněk zadejte následující položky: D1 - dodavatel E1 - část
2. Zadejte tabulku dat zobrazenou na výše uvedeném obrázku do buněk D4 až F9
3. Řádky 2 a 3 jsou ponechány prázdné, aby vyhovovaly kritériím vyhledávání a vzoru levého vyhledávání vytvořenému během tohoto kurzu

Spuštění formuláře levého vyhledávání - otevření dialogového okna VLOOKUP

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

Alternativou je v tomto případě 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.

Tutorial Kroky

1. Klikněte na buňku E2 pracovního listu - místo, kde se zobrazí výsledky levého vzorce hledání
2. Klikněte na kartu Vzorky karet
3. Klepnutím na možnost Vyhledat a odkaz na pásu karet otevřete rozbalovací seznam funkcí
4. Klikněte na VLOOKUP v seznamu pro vyvolání dialogového okna funkce

02 z 03

Zadání argumentů do dialogového okna VLOOKUP - Kliknutím zobrazíte větší obrázek

Argumenty společnosti 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 umístěn na samostatném řádku, po němž následuje pole, do kterého chcete zadat hodnotu.

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

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.

Tutorial Kroky

1. Klikněte na řádek lookup_value v dialogovém okně
2. Klepnutím na buňku D2 přidejte tento článek na řádek lookup_value
3. Stiskněte klávesu F4 na klávesnici, aby absolutní hodnota absolutní - $ D $ 2

Poznámka: Hodnoty vyhledávací hodnoty a argumenty tabulky jsou použity pro absolutní odkazy na buňky, aby se zabránilo chybám v případě, že vzorec vyhledávání je zkopírován do jiných buněk v listu.

Array tabulky: Zadání funkce CHOOSE

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

Normálně VLOOKUP hledá pouze vpravo od argumentu hodnoty vyhledávání, aby našel 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í se v levém pořadí sloupců v tabulce pole tak, že sloupec F přijde první a sloupec D je druhý

Podrobné informace o tom, jak tato funkce splňuje tyto úkoly, naleznete na stránce 3 výuky .

Tutorial Kroky

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

1. V dialogovém okně funkce VLOOKUP klepněte na řádek Table_array
2. Zadejte následující funkci CHOOSE
3. 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; nicméně odkazuje na pořadí sloupců nastavených funkcí CHOOSE.

Funkce CHOOSE vytvoří pole tabulky se dvěma sloupci se sloupcem F s prvním sloupcem F následovaným sloupcem D. Jelikož požadovaná informace - název dílu - je ve sloupci D, musí být hodnota argumentu sloupcového indexu nastavena na hodnotu 2.

Tutorial Kroky

1. Klikněte na řádek Col_index_num v dialogovém okně
2. Do tohoto řádku zadejte hodnotu 2

Vyhledání rozsahu

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 vrací 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 bude používat pouze přesnou shodu na 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 výukovém programu, jelikož hledáme konkrétní název, bude Range_lookup nastavena na hodnotu FALSE tak, aby vzorec vrátil pouze přesné shody.

Tutorial Kroky

1. Klepněte na řádek Range_lookup v dialogovém okně
2. Zadejte v tomto řádku slovo False a označte, že chceme, aby VLOOKUP vrátil přesné shodu pro data, která hledáme
3. Klepnutím na tlačítko OK dokončete dialog pro levé vyhledávání a zavření
4. Jelikož jsme dosud nezadali název firmy do buňky D2, v buňce E2 by měla být přítomna chyba # N / A

03 ze dne 03

Testování vzorce levého vyhledávání

Vrací data s levým vyhledáváním

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

Název dílu se zobrazí v buňce E2.

Tutorial Kroky

1. Klikněte na buňku D2 ve svém listu
2. Zadejte Gadgets Plus do buňky D2 a stiskněte klávesu ENTER na klávesnici
3. Text Gadgets - součást dodávaná společností Gadgets Plus - by měla být zobrazena v buňce E2
4. Vyzkoušejte vyhledávací vzorec 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

VLOOKUP Chybové zprávy

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

Pokud není pravopisem problém, může tento seznam chybových hlášení VLOOKUP určit, kde je problém.

Zrušení úlohy funkce CHOOSE

Jak bylo uvedeno, v tomto vzorci má funkce CHOOSE dvě úlohy:

• vytvoří pole tabulky, které má pouze dva sloupce - sloupce D a F
• změní se v levém pořadí sloupců v tabulce pole tak, že sloupec F přijde první a sloupec D je druhý

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.

Zadáním více indexových čísel; Funkce však vrátí více hodnot v libovolném pořadí. Získání volby CHOOSE pro návrat více hodnot se provádí vytvořením pole .

Zadání pole se provádí obklopením čísel zadaných s kudrnatými příchytkami nebo závorami. Pro indexové číslo jsou zadány dvě čísla: {1,2} .

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

Další sloupce by vám umožnily vrátit různé informace pomocí vzorce levého vyhledávání jednoduše tím, že změní argument sloupce indexu sloupců VLOOKUP na číslo sloupce obsahující požadované informace.

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

Ve funkci CHOOSE použitý v tomto vzorci: CHOOSE ({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 se předává podél VLOOKUP.

Nyní, pokud jde o VLOOKUP, pole tabulky je pouze dva sloupce široký 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í, VLOOKUP bude moci hledat data nacházející se nalevo od vyhledávací hodnoty.

Výsledkem je, že společnost VLOOKUP je schopna použít název firmy k nalezení části, kterou dodávají.