Kombinací funkce VLOOKUP aplikace Excel s funkcí COLUMN můžete vytvořit vyhledávací vzorec, který vrátí více hodnot z jednoho řádku databáze nebo tabulky dat. Naučte se, jak vytvořit vyhledávací vzorec, který vrátí více hodnot z jednoho datového záznamu.
Pokyny v tomto článku platí pro Excel 2019, 2016, 2013, 2010; a Excel pro Microsoft 365.
Sečteno podtrženo
Vyhledávací vzorec vyžaduje, aby funkce COLUMN byla vnořena do funkce VLOOKUP. Vnoření funkce zahrnuje zadání druhé funkce jako jednoho z argumentů pro první funkci.
Zadejte data výukového programu
V tomto tutoriálu je funkce COLUMN zadána jako argument čísla indexu sloupce pro SVYHLEDAT. Poslední krok v tutoriálu zahrnuje zkopírování vyhledávacího vzorce do dalších sloupců, abyste získali další hodnoty pro vybranou součást.
Prvním krokem v tomto tutoriálu je zadání dat do excelového listu. Abyste mohli postupovat podle kroků v tomto tutoriálu, zadejte data zobrazená na obrázku níže do následujících buněk:
- Zadejte nejvyšší rozsah dat do buněk D1 až G1.
- Zadejte druhý rozsah do buněk D4 až G10.
Kritéria vyhledávání a vyhledávací vzorec vytvořený v tomto tutoriálu jsou zadány na řádku 2 listu.
Tento výukový program nezahrnuje základní formátování aplikace Excel zobrazené na obrázku, ale to nemá vliv na fungování vyhledávacího vzorce.
Vytvořte pojmenovaný rozsah pro tabulku dat
Pojmenovaný rozsah je snadný způsob, jak odkazovat na rozsah dat ve vzorci. Místo zadávání odkazů na buňky pro data zadejte název rozsahu.
Druhou výhodou použití pojmenovaného rozsahu je, že odkazy na buňky pro tento rozsah se nikdy nezmění, ani když je vzorec zkopírován do jiných buněk v listu. Názvy rozsahů jsou alternativou k použití absolutních odkazů na buňky, aby se zabránilo chybám při kopírování vzorců.
Název rozsahu nezahrnuje záhlaví nebo názvy polí pro data (jak je uvedeno na řádku 4), pouze data.
-
Zvýrazněte buňky D5 až G10 v pracovním listu.
Image -
Umístěte kurzor do pole Název nad sloupcem A, napište Tabulka a stiskněte Enter. Buňky D5 až G10 mají název rozsahu Tabulka.
Image - Název rozsahu pro argument pole tabulky SVYHLEDAT je použit později v tomto tutoriálu.
Otevřít dialogové okno VLOOKUP
Přestože je možné zadat vyhledávací vzorec přímo do buňky v listu, pro mnoho lidí je obtížné udržet syntaxi přímou – zvláště u složitého vzorce, jako je ten, který je použit v tomto tutoriálu.
Jako alternativu použijte dialogové okno Argumenty funkce VLOOKUP. Téměř všechny funkce Excelu mají dialogové okno, kde se každý z argumentů funkce zadává na samostatný řádek.
-
Vyberte buňku E2 z listu. Toto je místo, kde se zobrazí výsledky dvojrozměrného vyhledávacího vzorce.
Image -
Na pásu karet přejděte na kartu Formulas a vyberte Vyhledat a reference.
Image -
Výběrem VLOOKUP otevřete dialogové okno Argumenty funkce.
Image - V dialogovém okně Argumenty funkce se zadávají parametry funkce VLOOKUP.
Zadejte argument vyhledávací hodnoty
Vyhledávaná hodnota obvykle odpovídá poli dat v prvním sloupci datové tabulky. V tomto příkladu vyhledávací hodnota odkazuje na název součásti, o které chcete najít informace. Povolené typy dat pro vyhledávací hodnotu jsou textová data, logické hodnoty, čísla a odkazy na buňky.
Absolute Cell Reference
Při kopírování vzorců v Excelu se odkazy na buňky změní tak, aby odrážely nové umístění. Pokud k tomu dojde, D2, odkaz na buňku pro vyhledávanou hodnotu, se změní a vytvoří chyby v buňkách F2 a G2.
Absolutní odkazy na buňky se při kopírování vzorců nemění.
Abyste předešli chybám, převeďte odkaz na buňku D2 na absolutní odkaz na buňku. Chcete-li vytvořit absolutní odkaz na buňku, stiskněte klávesu F4. To přidá znaky dolaru kolem odkazu na buňku, například $D$2.
-
V dialogovém okně Argumenty funkcí umístěte kurzor do textového pole lookup_value. Poté v listu vyberte cell D2 a přidejte tento odkaz na buňku do lookup_value. Do buňky D2 bude zadán název dílu.
Image -
Aniž byste přesunuli kurzor, stiskněte klávesu F4 pro převod D2 na absolutní odkaz na buňku $D$2.
Image - Ponechejte dialogové okno funkce SVYHLEDAT otevřené pro další krok výukového programu.
Zadejte argument pole tabulky
Pole tabulky je tabulka dat, kterou vyhledávací vzorec prohledává, aby našel požadované informace. Pole tabulky musí obsahovat alespoň dva sloupce dat.
První sloupec obsahuje argument vyhledávací hodnoty (který byl nastaven v předchozí části), zatímco druhý sloupec se prohledává pomocí vyhledávacího vzorce, aby se našly vámi zadané informace.
Argument pole tabulky musí být zadán buď jako rozsah obsahující odkazy na buňky pro datovou tabulku, nebo jako název rozsahu.
Chcete-li přidat tabulku dat do funkce VLOOKUP, umístěte kurzor do textového pole table_array v dialogovém okně a zadejte Tablepro zadání názvu rozsahu pro tento argument.
Vnořte funkci COLUMN
Normálně funkce VLOOKUP vrací data pouze z jednoho sloupce datové tabulky. Tento sloupec je nastaven argumentem číslo indexu sloupce. V tomto příkladu jsou však tři sloupce a indexové číslo sloupce je třeba změnit bez úpravy vyhledávacího vzorce. Chcete-li toho dosáhnout, vnořte funkci COLUMN do funkce SVYHLEDAT jako argument Col_index_num.
Při vnořování funkcí Excel neotevře dialogové okno druhé funkce pro zadání jejích argumentů. Funkci COLUMN je nutné zadat ručně. Funkce COLUMN má pouze jeden argument, argument Reference, což je odkaz na buňku.
Funkce COLUMN vrací číslo sloupce poskytnutého jako argument Reference. Převede písmeno sloupce na číslo.
Chcete-li zjistit cenu položky, použijte údaje ve sloupci 2 datové tabulky. Tento příklad používá sloupec B jako odkaz pro vložení 2 do argumentu Col_index_num.
-
V dialogovém okně Argumenty funkce umístěte kurzor do textového pole Col_index_num a zadejte COLUMN(. (Nezapomeňte uvést otevřenou kulatou závorku.)
Image -
V listu vyberte buňku B1 a zadejte odkaz na buňku jako argument Reference.
Image - Napište uzavírací kulatou závorku a dokončete funkci COLUMN.
Zadejte argument vyhledávání rozsahu VLOOKUP
Argument Range_lookup nástroje VLOOKUP je logická hodnota (PRAVDA nebo NEPRAVDA), která udává, zda má funkce SVYHLEDAT najít přesnou nebo přibližnou shodu s hodnotou Lookup_value.
- TRUE nebo Omitted: VLOOKUP vrací blízkou shodu s Lookup_value. Pokud není přesná shoda nalezena, funkce SVYHLEDAT vrátí další největší hodnotu. Data v prvním sloupci tabulky Table_array musí být řazena vzestupně.
- FALSE: VLOOKUP používá přesnou shodu s hodnotou Lookup_value. Pokud jsou v prvním sloupci pole Table_array dvě nebo více hodnot, které odpovídají vyhledávací hodnotě, použije se první nalezená hodnota. Pokud nebude přesná shoda nalezena, vrátí se chyba N/A.
V tomto tutoriálu budou vyhledány konkrétní informace o konkrétní položce hardwaru, takže Range_lookup je nastaven na FALSE.
V dialogovém okně Argumenty funkce umístěte kurzor do textového pole Range_lookup a napište False, abyste funkci VLOOKUP řekli, aby vrátila přesnou shodu dat.
Výběrem OK dokončíte vyhledávací vzorec a zavřete dialogové okno. Buňka E2 bude obsahovat chybu N/A, protože do buňky D2 nebyla zadána vyhledávací kritéria. Tato chyba je dočasná. Bude opraveno, když v posledním kroku tohoto tutoriálu přidáte vyhledávací kritéria.
Zkopírujte vyhledávací vzorec a zadejte kritéria
Vyhledávací vzorec načítá data z více sloupců datové tabulky najednou. K tomu musí být vyhledávací vzorec umístěn ve všech polích, ze kterých chcete informace.
Chcete-li načíst data ze sloupců 2, 3 a 4 datové tabulky (cena, číslo dílu a jméno dodavatele), zadejte částečný název jako Lookup_value.
Vzhledem k tomu, že data jsou v listu uspořádána v pravidelném vzoru, zkopírujte vyhledávací vzorec do buňky E2 do buňky F2 a G2 Při kopírování vzorce aktualizuje Excel relativní odkaz na buňku ve funkci COLUMN (buňka B1), aby odrážel nové umístění vzorce. Excel při kopírování vzorce nemění absolutní odkaz na buňku (například $D$2) a pojmenovaný rozsah (tabulku).
Existuje více než jeden způsob, jak kopírovat data v Excelu, ale nejsnazším způsobem je použít nástroj Fill Handle.
-
Vyberte buňku E2, kde se nachází vyhledávací vzorec, aby se stala aktivní buňkou.
Image -
Přetáhněte úchyt výplně na cell G2. Buňky F2 a G2 zobrazují chybu N/A, která se vyskytuje v buňce E2.
Image -
Chcete-li použít vyhledávací vzorce k načtení informací z datové tabulky, v listu vyberte buňku D2, napište Widget a stiskněte Zadejte.
Image V buňkách E2 až G2 se zobrazí následující informace.
- E2: 14,76 $ – cena widgetu
- F2: PN-98769 – číslo dílu pro widget
- G2: Widgets Inc. – název dodavatele widgetů
-
Chcete-li otestovat maticový vzorec VLOOKUP, zadejte název dalších částí do buňky D2 a sledujte výsledky v buňkách E2 až G2.
Image - Každá buňka obsahující vyhledávací vzorec obsahuje jinou část dat o hardwarové položce, kterou jste hledali.
Funkce SVYHLEDAT s vnořenými funkcemi, jako je COLUMN, poskytuje výkonnou metodu pro vyhledávání dat v tabulce pomocí jiných dat jako vyhledávací reference.
