Jak vytvořit vzorec aplikace Excel pro vyhledávání vlevo pomocí VLOOKUP

Funkce VLOOKUP aplikace Microsoft Excel vyhledá a vrátí informace z tabulky dat na základě vyhledávací hodnoty, kterou zvolíte. Typicky VLOOKUP vyžaduje, aby vyhledávací hodnota byla ve sloupci tabulky dat nalevo a funkce vrací další pole dat umístěné ve stejném řádku napravo od této hodnoty. Kombinace VLOOKUP s funkcí CHOOSE však vytvoří vzorec levého vyhledávání, kde vyhledávací hodnota může být z libovolného sloupce v datové tabulce. Vzorec vrací informace umístěné v libovolném sloupci nalevo od vyhledávací hodnoty. Tyto pokyny platí pro Excel verze 2019, 2016, 2013, 2010 a Excel pro Microsoft 365.

Vytvořte vzorec pro vyhledávání vlevo

V tomto příkladu vytvoříme vzorec pro vyhledání vlevo, abychom našli část dodanou různými společnostmi uvedenými ve sloupci 3 datové tabulky. V tomto vzorci funkce CHOOSE přiměje VLOOKUP, aby věřil, že sloupec 3 je sloupec 1. Ve výsledku můžeme použít název společnosti jako vyhledávací hodnotu k nalezení názvu součásti dodávané každou společností.

Zadávání údajů

1. Zadejte nadpis Dodavatel do buňky D1.

2. Zadejte nadpis Část v buňce E1.

3. Zadejte tabulku dat na obrázku výše do buněk D4 až F9.

4. Řádky 2 a 3 jsou ponechány prázdné, aby vyhovovaly kritériím vyhledávání a vzorci vyhledávání vlevo vytvořenému během tohoto kurzu.

Otevřete dialogové okno VLOOKUP

I když je možné vzorec zadat přímo do buňky F1 v listu, mnoho lidí má potíže se syntaxí 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 každý z argumentů funkce na samostatném řádku.

1. Vyberte buňku E2 listu. E2 je umístění, kde se zobrazí výsledky vzorce pro vyhledávání vlevo.

2. Vybrat Vzorce záložka pásu karet.

3. Vybrat Vyhledávání a reference možnost na pásu karet otevřete rozevírací seznam funkcí.

4. Vybrat HLEDAT vyvolat dialogové okno funkce.

Do pole SVYHLEDAT zadejte argumenty

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 následovaném polem, do kterého se má zadat hodnota. Zde je to, co zadáte do každé části dialogového okna VLOOKUP.

Hodnota vyhledávání

Vyhledávací hodnota je pole informací použité k prohledání pole tabulky. VLOOKUP vrací další pole dat ze stejného řádku jako vyhledávací hodnota. Tento příklad používá odkaz na buňku do umístění, kde bude název společnosti zadán do listu. To usnadňuje změnu názvu společnosti bez úpravy vzorce.

1. Vybrat Vyhledávací_hodnota řádek v dialogovém okně SVYHLEDAT.

2. Vyberte buňku D2 přidat tento odkaz na buňku do Vyhledávací_hodnota čára.

3. zmáčkni F4 klávesu na klávesnici, aby byl odkaz na buňku absolutní: $ D $ 2.

   Pro vyhledávací hodnotu a argumenty pole tabulky se používají absolutní odkazy na buňky, aby se zabránilo chybám, pokud je vzorec vyhledávání zkopírován do jiných buněk v listu.

Zadání funkce CHOOSE do pole tabulky

Argument pole tabulky je blok souvislých dat, ze kterých se získávají konkrétní informace. VLOOKUP obvykle vyhledává data v poli tabulky pouze napravo od argumentu hodnoty vyhledávání. Aby to vypadalo nalevo, musí být VLOOKUP podveden přeskupením sloupců v poli tabulky. Děláme to pomocí funkce CHOOSE. V tomto vzorci vytvoří funkce CHOOSE pole tabulky, které je široké pouze dva sloupce (sloupce D a F), a změní pořadí zprava doleva sloupců v poli tabulky, takže sloupec F bude první a sloupec D je druhý. Vzhledem k tomu, že funkce CHOOSE nastavuje pole tabulky VLOOKUP (zdroj dat pro tuto funkci), přepíná se pořadí sloupců ve funkci CHOOSE spolu s VLOOKUP. Pokud jde o VLOOKUP, pole tabulky je široké pouze dva sloupce se sloupcem F vlevo a sloupcem D vpravo. Vzhledem k tomu, že sloupec F obsahuje název společnosti, kterou chceme vyhledat, a protože sloupec D obsahuje názvy dílů, může VLOOKUP plnit své běžné vyhledávací povinnosti při hledání dat, která se nacházejí nalevo od vyhledávací hodnoty. Díky tomu může VLOOKUP použít název společnosti k vyhledání součásti, kterou dodává. CHOOSE se neomezuje pouze na vytvoření dvousloupcové tabulky. Zahrnutím dalšího čísla do pole, například {1,2,3} a dalšího rozsahu do argumentu hodnota, CHOOSE vytvoří třísloupcovou tabulku.

1. V dialogovém okně funkce SVYHLEDAT vyberte Table_array čára.

2. Zadejte následující VYBRAT funkce:

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

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

Indexové číslo sloupce

Číslo indexu sloupce obvykle označuje, který sloupec pole tabulky obsahuje data, která sledujete. V tomto vzorci však odkazuje na pořadí sloupců nastavené funkcí CHOOSE. Funkce CHOOSE vytvoří pole tabulky, které je široké dva sloupce, nejprve sloupec F a poté sloupec D. Protože hledaná informace (název součásti) je ve sloupci D, musí být hodnota argumentu indexu sloupce nastavena na 2.

1. Vybrat Col_index_num řádek v dialogovém okně.

2. Zadejte a 2 v tomto řádku.

Rozsah vyhledávání

Argument Range_lookup VLOOKUP je logická hodnota (pouze TRUE nebo FALSE), která označuje, zda chcete, aby VLOOKUP našel přesnou nebo přibližnou shodu s vyhledanou hodnotou. Pokud má hodnotu PRAVDA nebo je tento argument vynechán, vrátí funkce VLOOKUP buď přesnou shodu s Lookup_value, nebo pokud není nalezena přesná shoda, vrátí funkce VLOOKUP další největší hodnotu. Aby to mohl vzorec udělat, musí být data v prvním sloupci Table_array seřazeny vzestupně. Pokud je FALSE, VLOOKUP používá pouze přesnou shodu s Lookup_value. Pokud jsou v prvním sloupci Table_array dvě nebo více hodnot, které odpovídají vyhledávací hodnotě, použije se první nalezená hodnota. Pokud není nalezena přesná shoda, vrátí se chyba # N / A. Protože hledáme název konkrétní součásti, nastavíme Range_lookup na Nepravdivé takže vzorec vrátí pouze přesné shody.

1. Vybrat Range_lookup řádek v dialogovém okně.

2. Napište slovo Nepravdivé v tomto řádku označujeme, že chceme, aby VLOOKUP vrátil přesnou shodu pro data, která hledáme.

   

3. Vybrat OK dokončete vzorec pro vyhledávání vlevo a zavřete dialogové okno. Protože jsme dosud nezadali název společnosti do buňky D2, objeví se v buňce E2 chyba # N / A.

Testování vzorce pro vyhledávání vlevo

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

1. Vyberte buňku D2 v listu.

2. Typ Gadgets Plus do buňky D2 a stiskněte Enter nebo Vrátit se.

3. Text Gadgety, což je součást dodávaná společností Gadgets Plus, by měla být zobrazena v buňce E2.

   

4. Vyhledávací vzorec dále otestujte zadáním dalších názvů společností do buňky D2 a odpovídající název součásti by se měl objevit v buňce E2. Pokud se v buňce E2 zobrazí chybová zpráva, například # N / A, zkontrolujte, zda v buňce D2 nejsou pravopisné chyby.