Tyto příklady vás naučí, jak Vlookup použít více kritérií, vrátit konkrétní instanci nebo všechny shody, provést dynamický Vlookup v více listů a další.
Je to druhá část série, která vám pomůže využít sílu aplikace Excel VLOOKUP. Příklady naznačují, že víte, jak tato funkce funguje. Pokud ne, má důvod začít se základním použitím VLOOKUP v aplikaci Excel.
Než se přesuneme dále, dovolte mi krátce vám připomenout syntaxi:
Nyní, když jsou všichni na stejné stránce, pojďme se blíže podívat na pokročilé příklady vzorců VLOOKUP:
- Jak zobrazit Vlookup více kritérií v aplikaci Excel
- Vlookup a návrat n-té shody
- Vlookup více hodnot
- Vlookup na základě hodnot řádků a sloupců
- Vnořený vzorec Vlookup
- VLOOKUP a INDIRECT k načítání dat z více listů
Jak vyhledávat více kritérií
Funkce Excel VLOOKUP je opravdu užitečná, pokud jde o vyhledávání v databázi pro určitou hodnotu. Chybí mu však důležitá vlastnost – jeho syntaxe umožňuje pouze jednu vyhledávací hodnotu. Ale co když chcete vyhledat několik podmínek? Můžete si vybrat z několika různých řešení.
Vzorec 1. VLOOKUP na základě tw o hodnoty
Předpokládejme, že máte seznam objednávek a chcete najít množství na základě 2 kritérií, jména zákazníka a produktu. Komplikujícím faktorem je, že každý zákazník si objednal více produktů, jak ukazuje následující tabulka:
Obvyklý vzorec VLOOKUP v tomto nebude fungovat situace, protože vrátí první nalezenou shodu na základě jediné vyhledávací hodnoty, kterou zadáte.
Chcete-li to překonat, můžete přidat sloupec pomocníka a zřetězit tam hodnoty ze dvou vyhledávacích sloupců (Zákazník a Produkt). Je důležité, aby sloupec pomocníka měl být v poli tabulky sloupcem úplně vlevo, protože v něm Excel VLOOKUP vždy hledá vyhledávací hodnotu.
Takže přidejte sloupec nalevo od tabulky a zkopírujte níže uvedený vzorec v tomto sloupci. Tím se sloupec pomocníka naplní hodnotami ze sloupců B a C (znak mezery je mezi nimi zřetězen pro lepší čitelnost):
=B2&" "&C2
A potom použijte standardní vzorec VLOOKUP a vložte obě kritéria do argumentu lookup_value oddělená mezerou:
=VLOOKUP("Jeremy Sweets", A2:D11, 4, FALSE)
Nebo zadejte kritéria do samostatných buněk (v našem případě G1 a G2) a zřetězte tyto buňky:
=VLOOKUP(G1&" "&G2, A2:D11, 4, FALSE)
Jak jsme chcete vrátit hodnotu ze sloupce D, který je čtvrtý v poli tabulky, použijeme 4 pro col_index_num. Argument range_lookup je nastaven na FALSE, aby Vlookup odpovídal přesné shodě. Výsledek ukazuje následující snímek obrazovky:
Pokud je vyhledávací tabulka v jiném listu, zahrňte název listu do vzorce VLOOKUP. Například:
=VLOOKUP(G1&" "&G2, Orders!A2:D11, 4, FALSE)
Alternativně vytvořte pojmenovaný rozsah pro vyhledávací tabulku (řekněme Objednávky), aby byl vzorec jednodušší – ke čtení:
=VLOOKUP(G1&" "&G2, Orders, 4, FALSE)
Další informace najdete v části Jak Vlookup z jiného listu v aplikaci Excel.
Vzorec 2. Excel VLOOKUP s více podmínkami
Teoreticky můžete výše uvedený přístup k Vlookupu použít více než dvěma kritérii. H existuje však několik upozornění. Za prvé, vyhledávací hodnota je omezena na 255 znaků a za druhé, návrh listu nemusí umožňovat přidání pomocného sloupce.
Naštěstí Microsoft Excel často poskytuje více než jeden způsob, jak dělat totéž. Chcete-li Vlookup použít více kritérií, můžete použít kombinaci INDEX MATCH nebo funkci XLOOKUP, která byla nedávno zavedena v Office 365.
Chcete-li například vyhledat na základě 3 různých hodnot (datum, jméno zákazníka a produkt), použijte jeden z následujících vzorců:
=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))
=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)
Kde:
Podrobné vysvětlení vzorců naleznete na stránce:
- XLOOKUP s více kritérii
- Vzorec INDEX MATCH s více kritérii
Jak používat VLOOKUP k získání 2., 3. nebo n-té shody
Jak již víte, Excel VLOOKUP může načíst pouze jednu odpovídající hodnotu, přesněji vrátí první nalezenou shodu. Ale co když je ve vašem vyhledávacím poli několik shod a chcete získat 2. nebo 3. instanci? Úkol zní docela složitě, ale řešení skutečně existuje!
Vzorec 1. Vlookup N-ta instance
Předpokládejme, že máte v jednom sloupci jména zákazníků, produkty, které zakoupili v jiném, a vy hledají 2. nebo 3. produkt zakoupený daným zákazníkem.
Nejjednodušší způsob je přidat sloupec pomocníka nalevo od tabulky, jako jsme to udělali v prvním příkladu. Tentokrát jej však naplníme jmény zákazníků a čísly výskytů, například „John Doe1“, „John Doe2“ atd.
Chcete-li výskyt získat, použijte funkci COUNTIF s odkazem na smíšený rozsah ( první reference je absolutní a druhá je relativní jako $ B $ 2: B2). Vzhledem k tomu, že se relativní reference mění na základě pozice buňky, kde je vzorec zkopírován, v řádku 3 se změní na $ B $ 2: B3, v řádku 4 – $ B $ 2: B4 atd.
Zřetězeno se jménem zákazníka (B2) má vzorec tuto podobu:
=B2&COUNTIF($B$2:B2, B2)
Výše uvedený vzorec jde do A2 a potom jej zkopírujte do tolika buněk, kolik je potřeba.
Poté zadejte název cíle a číslo výskytu do samostatných buněk (F1 a F2) a použijte následující vzorec k vyhledání konkrétního výskytu:
=VLOOKUP(F1&F2, A2:C11, 3, FALSE)
Vzorec 2. Vlookup 2. výskyt
Pokud hledáte 2. instanci vyhledávací hodnoty, můžete to udělat bez pomocného sloupce. Místo toho vytvořte pole tabulky dynamicky pomocí funkce INDIRECT společně s MATCH:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11"), 2, FALSE)
Kde:
- E1 je vyhledávací hodnota
- A2: A11 je vyhledávací rozsah
- B11 je poslední (vpravo dole) buňka vyhledávací tabulky
Upozorňujeme, že výše uvedený vzorec je napsán pro konkrétní případ, kdy datové buňky ve vyhledávací tabulce začínají v řádku 2. Pokud je vaše tabulka někde v uprostřed listu použijte tento univerzální vzorec, kde A1 je levá horní buňka vyhledávací tabulky obsahující záhlaví sloupce:
=VLOOKUP(E1, INDIRECT("A"&(MATCH(E1, A2:A11, 0)+1+ROW(A1))&":B11"), 2, FALSE)
Jak tento vzorec funguje
Zde je klíčová část vzorce, která vytváří dynamický rozsah vlookup:
INDIRECT("A"&(MATCH(E1, A2:A11, 0)+2)&":B11")
Funkce MATCH nakonfigurovaná pro přesnou shodu (0 v posledním argumentu) porovná název cíle (E1) se seznamem jmen (A2: A11) a vrátí pozici první nalezené shody, což je 3 v o tvůj případ. Toto číslo bude použito jako počáteční souřadnice řádku pro rozsah vlookup, takže k němu přidáme 2 (+1 k vyloučení první instance a +1 k vyloučení řádku 1 s hlavičkami sloupců). Alternativně můžete použít 1 + ROW (A1) k automatickému výpočtu potřebné úpravy na základě polohy řádku záhlaví (v našem případě A1).
Ve výsledku získáme následující textový řetězec, který INDIRECT převádí na odkaz na rozsah:
INDIRECT("A"&5&":B11") -> A5:B11
Tento rozsah jde na argument table_array VLOOKUP, který jej nutí začít hledat v řádku 5, přičemž vynecháme první instanci vyhledávací hodnoty:
VLOOKUP(E1, A5:B11, 2, FALSE)
Jak Vlookup a vrátit více hodnot v aplikaci Excel
Funkce Excel VLOOKUP je navržena tak, aby vrátila pouze jednu shodu. Existuje způsob, jak Vlookup více instancí? Ano, existuje, i když není snadné. To vyžaduje kombinované použití několika funkcí, jako je INDEX, SMALL a ROW je maticový vzorec.
Například níže lze najít všechny výskyty vyhledávací hodnoty F2 v rozsahu vyhledávání B2: B16 a návrat více shod ze sloupce C:
{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,""), ROW()-3)),"")}
Existují 2 způsoby, jak zadat vzorec v listu:
- Zadejte vzorec do první buňky, stiskněte Ctrl + Shift + Enter a poté jej přetáhněte dolů k několika dalším buňkám.
- Vyberte několik sousedních buněk v jednom sloupci (F1: F11 v snímek obrazovky níže), zadejte vzorec a dokončete jej stisknutím Ctrl + Shift + Enter.
Ať tak či onak, počet buněk, do kterých zadáte vzorec, by měl být stejný nebo větší než maximální počet možných shod.
Podrobné vysvětlení logiky vzorců a další příklady najdete v části VLOOKUP více hodnot v aplikaci Excel .
Jak Vlookup v řádcích a sloupcích (obousměrné vyhledávání)
Obousměrné vyhledávání (aka maticové vyhledávání nebo 2-dimenzionální vyhledávání) je fantastické slovo pro vyhledání hodnoty v průsečíku určitého řádku a sloupce.Existuje několik různých způsobů, jak provést dvourozměrné vyhledávání v aplikaci Excel, ale protože se tento výukový program zaměřuje na funkci VLOOKUP, přirozeně ji použijeme.
V tomto příkladu si vezmeme níže uvedená tabulka s měsíčním prodejem a vypracováním vzorce VLOOKUP k načtení prodejního čísla konkrétní položky v daném měsíci.
S názvy položek v A2: A9, názvy měsíců v B1: F1, cílová položka v I1 a cílovém měsíci v I2 má vzorec následující podobu:
=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)
Jak tento vzorec funguje
Jádrem vzorce je standardní funkce VLOOKUP, která hledá přesnou shodu s vyhledávací hodnotou v I1. Ale protože nevíme ve kterém přesně sloupci jsou tržby za konkrétní měsíc, nemůžeme zadat číslo sloupce přímo do argumentu col_index_num. K vyhledání tohoto sloupce použijeme následující funkci MATCH:
MATCH(I2, A1:F1, 0)
Přeloženo do angličtiny, vzorec říká: vyhledat hodnotu I2 v A1: F1 a vrátit její relativní polohu v poli. Zadáním 0 k 3. argumentu dáte příkazu MATCH, aby našel hodnotu přesně rovnou vyhledávací hodnotě (je to jako použití FALSE pro argument range_lookup VLOOKUP).
Protože Mar je ve 4. sloupci ve vyhledávacím poli vrátí funkce MATCH hodnotu 4, která přejde přímo na argument col_index_num VLOOKUP:
VLOOKUP(I1, A2:F9, 4, FALSE)
Věnujte pozornost že ačkoli názvy měsíců začínají ve sloupci B, používáme pro vyhledávací pole A1: I1. To se provádí, aby číslo vrácené MATCH odpovídalo pozici sloupce v table_array VLOOKUP.
Chcete-li se dozvědět více způsobů provedení maticového vyhledávání v aplikaci Excel, přečtěte si INDEX MATCH MATCH a další vzorce pro 2-dimenzionální vyhledávání.
Jak provést více Vlookup v aplikaci Excel (vnořený Vlookup)
Někdy se může stát, že vaše hlavní tabulka a vyhledávací tabulka nemají společný jediný sloupec, což vám znemožňuje provést Vlookup mezi dvěma tabulkami. Existuje však jiná tabulka, která neobsahuje informace, které hledáte, ale má jeden společný sloupec s hlavní tabulkou a další společný sloupec s vyhledávací tabulkou.
Níže uvedený obrázek ilustruje situaci:
Cílem je zkopírovat ceny do hlavní tabulky na základě ID položek. Problém je v tom, že tabulka obsahující ceny nemá ID položek, což znamená, že budeme muset udělat dva Vlookupy v jednom vzorci.
Kvůli pohodlí nejprve vytvořme několik pojmenovaných rozsahů :
- Vyhledávací tabulka 1 má název Products (D3: E3).
- Vyhledávací tabulka 2 má název Ceny (G3: H3).
Tabulky mohou být ve stejných nebo různých pracovních listech.
A nyní provedeme takzvaný dvojitý Vlookup, vnořený Vlookup.
Nejprve vytvořte VLOOKUP vzorec pro vyhledání názvu produktu ve vyhledávací tabulce 1 (s názvem Products) na základě ID položky (A3):
=VLOOKUP(A3, Products, 2, FALSE)
Dále vložte výše uvedený vzorec do argumentu lookup_value jiné funkce VLOOKUP a vytáhněte ceny z vyhledávací tabulky 2 (pojmenované Ceny) na základě názvu produktu vráceného vnořeným VLOOKUP:
=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)
Snímek obrazovky níže ukazuje náš vnořený vzorec Vlookup v akci:
Jak Vlookup více listy dynamicky
Někdy můžete mít data ve stejném formátu rozdělená do několika listů. A vaším cílem je získat data z konkrétního listu v závislosti na hodnotě klíče v dané buňce.
To může být snadněji pochopitelné z příkladu. Řekněme, že máte několik regionálních přehledů prodeje ve stejném formátu a hledáte získání údajů o prodeji konkrétního produktu v určitých oblastech:
Stejně jako v předchozím příkladu začneme definováním několika jmen:
- Rozsah A2: B5 v listu CA má název CA_Sales.
- Rozsah A2 : B5 v listu FL se jmenuje FL_Sales.
- Rozsah A2: B5 v listu KS se jmenuje KS_Sales.
Jak vidíte, všechny pojmenované rozsahy mají společné část (prodej) a jedinečné součásti (CA, FL, KS). Nezapomeňte pojmenovat své rozsahy podobným způsobem, jaký je nezbytný pro vzorec, který budeme vytvářet.
Vzorec 1. INDIRECT VLOOKUP pro dynamické načítání dat z různých listů
Pokud je vaším úkolem načíst data z více listů, je nejlepším řešením vzorec VLOOKUP INDIRECT – kompaktní a snadno srozumitelný.
V tomto příkladu uspořádáme souhrnnou tabulku tímto způsobem:
- Zadejte požadované produkty v A2 a A3. To jsou naše vyhledávací hodnoty.
- Zadejte jedinečné části pojmenovaných rozsahů v B1, C1 a D1.
A teď zřetězíme buňku obsahující jedinečnou část (B1) se společnou částí („_Sales“) a výsledný řetězec přivedeme na INDIRECT:
INDIRECT(B$1&"_Sales")
Funkce INDIRECT transformuje řetězec na název, kterému Excel rozumí, a vložíte jej do argumentu table_array VLOOKUP:
=VLOOKUP($A2, INDIRECT(B$1&"_Sales"), 2, FALSE)
Výše uvedený vzorec jde do B2 a poté jej zkopírujete dolů a doprava.
Věnujte prosím pozornost tomu, ve vyhledávací hodnotě ($ A2) jsme uzamkli souřadnici sloupce s absolutním odkazem na buňku, aby sloupec zůstal pevný, když se vzorec zkopíruje doprava. V odkazu B $ 1 jsme uzamkli řádek, protože chceme sloupec souřadnice pro změnu a dodání příslušné části názvu NEPŘÍMÉ v závislosti na sloupci, do kterého je vzorec zkopírován:
Pokud je vaše hlavní tabulka uspořádána jinak, vyhledávací hodnoty v řádku a jedinečné části názvů rozsahu v colu mn, pak byste měli zamknout souřadnici řádku ve vyhledávací hodnotě (B $ 1) a souřadnici sloupce v částech názvu ($ A2):
=VLOOKUP(B$1, INDIRECT($A2&"_Sales"), 2, FALSE)
Vzorec 2. VLOOKUP a vnořené IF pro vyhledání více listů
V situaci, kdy máte právě dva nebo tři vyhledávací listy, můžete použít poměrně jednoduchý vzorec VLOOKUP s vnořenými funkcemi IF a vybrat správný list na základě hodnoty klíče v konkrétní buňce:
=VLOOKUP($A2, IF(B$1="CA", CA_Sales, IF(B$1="FL", FL_Sales, IF(B$1="KS", KS_Sales,""))), 2, FALSE)
Kde $ A2 je vyhledávací hodnota (název položky) a B $ 1 je klíčová hodnota (stát):
V tomto případě nemusíte nutně definovat názvy a můžete použít externí odkazy k odkazování na jiný list nebo sešit.
Další příklady vzorců najdete v části Jak SVYHLEDAT na více listů v aplikaci Excel .
Takto se používá VLOOKUP v aplikaci Excel. Děkuji vám za přečtení a doufám, že vás příští týden uvidíme na našem blogu!
Praktický sešit ke stažení
Pokročilé příklady vzorců VLOOKUP (soubor .xlsx)
- Výukový program Excel VLOOKUP pro začátečníky
- Jak zobrazit více shody v aplikaci Excel
- Jak používat VLOOKUP & Funkce SUM nebo SUMIF v Excel
- INDEX & MATCH funkce v aplikaci Excel – lepší alternativa k VLOOKUP
- 4 způsoby, jak rozlišovat velká a malá písmena Vlookup v aplikaci Excel
- VLOOKUP aplikace Excel nefunguje – chyby N / A, NÁZEV a HODNOTA