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ý vrací 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.
Vraťte více hodnot pomocí VLOOKUP aplikace Excel
Vzorec vyhledávání vyžaduje, aby byla funkce COLUMN vnořena do VLOOKUP. Vnoření funkce zahrnuje zadání druhé funkce jako jednoho z argumentů pro první funkci.
Zadejte data výukového programu
V tomto výukovém programu je funkce COLUMN zadána jako index sloupce argument číslo pro SVYHLEDAT. Poslední krok v tomto kurzu zahrnuje zkopírování vyhledávacího vzorce do dalších sloupců, aby se načetly další hodnoty pro vybranou část. Prvním krokem v tomto kurzu je zadání dat do listu aplikace Excel. Chcete-li postupovat podle pokynů v tomto kurzu, zadejte data uvedená 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 vzorec pro vyhledávání vytvořený v tomto výukovém programu jsou zadány na řádku 2 listu. Tento kurz neobsahuje základní formátování aplikace Excel zobrazené na obrázku, ale to nemá vliv na to, jak vzorec vyhledávání funguje.
Vytvořte pojmenovaný rozsah pro datovou tabulku
Pojmenovaný rozsah je snadný způsob, jak odkazovat na rozsah dat ve vzorci. Místo psaní 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í, i 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í ani názvy polí pro data (jak je zobrazeno v řádku 4), pouze data.
-
Zvýraznit buňky D5 na G10 v listu.
-
Umístěte kurzor do pole Název nad sloupcem A, zadejte Stůl, poté stiskněte Enter. Buňky D5 až G10 mají název rozsahu Table.
-
Název rozsahu pro argument pole tabulky VLOOKUP se používá později v tomto kurzu.
Otevřete dialogové okno VLOOKUP
Ačkoli je možné vyhledávací vzorec zadat přímo do buňky v listu, mnoho lidí považuje za obtížné udržovat syntaxi rovnou – zejména pro složitý vzorec, jako je ten, který se používá v tomto kurzu. Jako alternativu použijte dialogové okno VLOOKUP Funkce Argumenty. Téměř všechny funkce aplikace Excel mají dialogové okno, kde je každý z argumentů funkce zadán na samostatném řádku.
-
Vybrat buňka E2 listu. Toto je místo, kde se zobrazí výsledky vzorce pro dvourozměrné vyhledávání.
-
Na pásu karet přejděte na ikonu Vzorce karta a vybrat Vyhledávání a reference.
-
Vybrat HLEDAT otevřít Argumenty funkcí dialogové okno.
-
V dialogovém okně Funkční argumenty se zadávají parametry funkce SVYHLEDAT.
Zadejte argument hodnoty vyhledávání
Normálně vyhledávání hodnota odpovídá poli dat v prvním sloupci tabulky dat. V tomto příkladu vyhledávání value odkazuje na název dílu, o kterém chcete najít informace. Povolené typy dat pro vyhledávání hodnotou jsou textová data, logické hodnoty, čísla a odkazy na buňky.
Absolutní odkazy na buňky
Při kopírování vzorců v aplikaci Excel 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ávání hodnota, mění se a vytváří chyby v buňkách F2 a G2. Při kopírování vzorců se absolutní odkazy na buňky nezmění. Chcete-li zabránit 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ává dolary kolem odkazu na buňku, například $ D $ 2.
-
V dialogovém okně Argumenty funkcí umístěte kurzor do vyhledávací_hodnota Textové pole. Poté v listu vyberte buňka D2 přidat tento odkaz na buňku do vyhledávací_hodnota. V buňce D2 bude zadán název součásti.
-
Bez posunutí kurzoru stiskněte F4 klíč pro převod D2 na absolutní referenci buňky $ D $ 2.
-
Pro další krok v tutoriálu nechte otevřené dialogové okno funkce VLOOKUP.
Zadejte argument Table Array
Pole tabulky je tabulka dat, která vyhledávací vzorec prohledá a najde požadované informace. Pole tabulky musí obsahovat alespoň dva sloupce dat. První sloupec obsahuje argument hodnoty vyhledávání (který byl nastaven v předchozí části), zatímco druhý sloupec je prohledáván vyhledávacím vzorcem, aby byly nalezeny informace, které určíte. Argument pole tabulky je třeba zadat 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 tabulka_pole textové pole v dialogovém okně a zadejte Stůl zadejte název rozsahu pro tento argument.
Vnořte funkci COLUMN
Normálně funkce VLOOKUP vrací pouze data z jednoho sloupce datové tabulky. Tento sloupec je nastaven argumentem číslo indexu sloupce. V tomto příkladu však existují tři sloupce a je nutné změnit indexové číslo sloupce bez úpravy vzorce vyhledávání. Chcete-li toho dosáhnout, vnořte funkci COLUMN do funkce VLOOKUP jako argument Col_index_num. Při vnořování funkcí Excel neotevře dialogové okno druhé funkce pro zadání argumentů. Funkce COLUMN musí být zadána ručně. Funkce COLUMN má pouze jeden argument, argument Reference, kterým je odkaz na buňku. Funkce COLUMN vrací číslo sloupce zadaného jako argument Reference. Převádí písmeno sloupce na číslo. Chcete-li zjistit cenu položky, použijte údaje ve sloupci 2 tabulky údajů. Tento příklad používá sloupec B jako odkaz k vložení 2 do argumentu Col_index_num.
-
V Argumenty funkcí v dialogovém okně umístěte kurzor do Col_index_num textové pole a zadejte SLOUPEC(. (Nezapomeňte zahrnout otevřený kulatý držák.)
-
V listu vyberte buňka B1 zadat tento odkaz na buňku jako argument Reference.
-
Zadejte a uzavírací kulatý držák k dokončení funkce COLUMN.
Zadejte argument pro vyhledávání rozsahu VLOOKUP
Argument Range_lookup VLOOKUP je logická hodnota (TRUE nebo FALSE), která označuje, zda by měl VLOOKUP najít přesnou nebo přibližnou shodu s Lookup_value.
- TRUE nebo vynecháno: VLOOKUP vrátí blízkou shodu s Lookup_value. Pokud není nalezena přesná shoda, vrátí funkce VLOOKUP další největší hodnotu. Data v prvním sloupci Table_array musí být řazena vzestupně.
- NEPRAVDIVÉ: VLOOKUP používá 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, je vrácena chyba # N / A.
V tomto kurzu budou vyhledány konkrétní informace o konkrétní hardwarové položce, takže Range_lookup je nastaven na FALSE. V dialogovém okně Funkční argumenty umístěte kurzor do textového pole Range_lookup a zadejte Nepravdivé říct VLOOKUP, aby vrátil přesnou shodu dat.
Vybrat OK dokončete vyhledávací vzorec a zavřete dialogové okno. Buňka E2 bude obsahovat chybu # N / A, protože vyhledávací kritéria nebyla zadána do buňky D2. Tato chyba je dočasná. Bude opraveno, když budou v posledním kroku tohoto kurzu přidána vyhledávací kritéria.
Zkopírujte vzorec pro vyhledávání a zadejte kritéria
Vyhledávací vzorec načte data z více sloupců tabulky dat najednou. K tomu musí být vyhledávací vzorec umístěn ve všech polích, ze kterých chcete získat informace. Chcete-li načíst data ze sloupců 2, 3 a 4 datové tabulky (cena, číslo dílu a název dodavatele), zadejte částečný název jako Lookup_value. Protože jsou data v listu rozložena v pravidelném vzoru, zkopírujte vyhledávací vzorec dovnitř buňka E2 na buňky F2 a G2. Při kopírování vzorce Excel aktualizuje relativní odkaz na buňku ve funkci COLUMN (buňka B1), aby odráželo nové umístění vzorce. Excel nemění absolutní odkaz na buňku (například $ D $ 2) a pojmenovaný rozsah (tabulka) při kopírování vzorce. Existuje více než jeden způsob kopírování dat v aplikaci Excel, ale nejjednodušší způsob je použít popisovač výplně.
-
Vybrat buňka E2, kde se nachází vzorec pro vyhledávání, aby se stala aktivní buňkou.
-
Přetáhněte úchyt výplně do buňka G2. Buňky F2 a G2 zobrazují chybu # N / A, která je přítomna v buňce E2.
-
Chcete-li použít vyhledávací vzorce k načtení informací z datové tabulky, vyberte v listu buňka D2, zadejte Widgeta stiskněte Enter.
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 vzorec pole SVYHLEDAT, zadejte název dalších částí do buňky D2 a sledujte výsledky v buňkách E2 až G2.
-
Každá buňka obsahující vzorec pro vyhledávání obsahuje jinou část dat o hardwarové položce, kterou jste hledali.
Funkce VLOOKUP s vnořenými funkcemi, jako je COLUMN, poskytuje výkonnou metodu pro vyhledání dat v tabulce pomocí jiných dat jako vyhledávací reference.