Obsah
V druhé části našeho tutoriálu o funkci VPR (VLOOKUP) v Excelu rozebereme několik příkladů, které vám pomohou nasměrovat veškerou sílu VPR k řešení nejambicióznějších úloh Excelu. Příklady předpokládají, že již máte základní znalosti o tom, jak tato funkce funguje. Pokud ne, možná vás bude zajímat začít s první částí tohoto tutoriálu, která vysvětluje syntaxi a základní použití. VPR. No, pojďme začít.
Vyhledávejte v Excelu podle více kritérií
funkce VPR v Excelu je opravdu mocný nástroj pro vyhledávání konkrétní hodnoty v databázi. Je zde však značné omezení – jeho syntaxe umožňuje vyhledávat pouze jednu hodnotu. Co když chcete hledat podle více podmínek? Řešení najdete níže.
Příklad 1: Vyhledávání podle 2 různých kritérií
Předpokládejme, že máme seznam objednávek a chceme je najít Množství zboží (množství), na základě dvou kritérií – Jméno klienta (Zákazník) a Název produktu (Produkt). Věc je komplikována tím, že každý z kupujících objednal několik druhů zboží, jak je patrné z níže uvedené tabulky:
pravidelná funkce VPR nebude v tomto scénáři fungovat, protože vrátí první nalezenou hodnotu, která odpovídá dané vyhledávací hodnotě. Například pokud chcete znát množství položky Cukroví'objednané kupujícím Jeremy Hill, napište následující vzorec:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– tento vzorec vrátí výsledek 15odpovídající produktu jablka, protože je to první hodnota, která se shoduje.
Existuje jednoduché řešení – vytvořte další sloupec, ve kterém zkombinujete všechna požadovaná kritéria. V našem příkladu jsou to tyto sloupce Jméno klienta (Zákazník) a Název produktu (Produkt). Nezapomeňte, že sloučený sloupec musí být vždy sloupec zcela vlevo v rozsahu vyhledávání, protože je to levý sloupec, který funkce VPR vyhledá při hledání hodnoty.
Přidáte tedy do tabulky pomocný sloupec a přes všechny jeho buňky zkopírujete následující vzorec: =B2&C2. Pokud chcete, aby byl řetězec čitelnější, můžete kombinované hodnoty oddělit mezerou: =B2&» «&C2. Poté můžete použít následující vzorec:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
Kde je buňka B1 obsahuje zřetězenou hodnotu argumentu lookup_value (hledaná_hodnota) a 4 – Argument col_index_num (column_number), tj. číslo sloupce obsahujícího data, která mají být načtena.
Příklad 2: SVYHLEDAT podle dvou kritérií s tabulkou zobrazenou na jiném listu
Pokud potřebujete aktualizovat hlavní tabulku (Hlavní tabulka) přidáním dat z druhé tabulky (Vyhledávací tabulka), která se nachází na jiném listu nebo v jiném sešitu aplikace Excel, můžete požadovanou hodnotu shromáždit přímo ve vzorci, který vložíte do hlavního stolu.
Stejně jako v předchozím příkladu budete potřebovat pomocný sloupec ve vyhledávací tabulce s kombinovanými hodnotami. Tento sloupec musí být sloupec zcela vlevo v rozsahu vyhledávání.
Takže vzorec s VPR může být takto:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Zde sloupce B a C obsahují jména zákazníků a názvy produktů a odkaz Objednávky!$A&$2:$D$2 definuje tabulku, která se má vyhledat v jiném listu.
Aby byl vzorec čitelnější, můžete pojmenovat rozsah zobrazení a vzorec pak bude vypadat mnohem jednodušeji:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Aby vzorec fungoval, musí být hodnoty v levém sloupci tabulky, na kterou se díváte, zkombinovány přesně stejným způsobem jako v kritériích vyhledávání. Na obrázku výše jsme zkombinovali hodnoty u2bu2band a mezi ně vložili mezeru, stejným způsobem, jaký musíte udělat v prvním argumentu funkce (BXNUMX& „“ & CXNUMX).
Zapamatovat si! funkce VPR omezena na 255 znaků, nemůže vyhledat hodnotu delší než 255 znaků. Mějte to na paměti a ujistěte se, že délka požadované hodnoty nepřekračuje tuto hranici.
Souhlasím s tím, že přidání pomocného sloupku není nejelegantnější a ne vždy přijatelné řešení. Totéž můžete udělat bez pomocného sloupce, ale to by vyžadovalo mnohem složitější vzorec s kombinací funkcí INDEX (INDEX) a MATCH (VÍCE EXPOZOVANĚ).
Extrahujeme 2., 3. atd. hodnoty pomocí VLOOKUP
To už víš VPR může vrátit pouze jednu odpovídající hodnotu, přesněji první nalezenou. Ale co když se tato hodnota v zobrazeném poli několikrát opakuje a vy chcete extrahovat 2. nebo 3. z nich? Co když všechny hodnoty? Problém se zdá složitý, ale řešení existuje!
Předpokládejme, že jeden sloupec tabulky obsahuje jména zákazníků (Customer Name) a druhý sloupec obsahuje produkty (Produkt), které si zakoupili. Zkusme najít 2., 3. a 4. položku zakoupenou daným zákazníkem.
Nejjednodušší je přidat pomocný sloupec před sloupec Jméno zákazníka a vyplňte jej jmény zákazníků s číslem opakování každého jména, např. John Doe1, John Doe2 atd. Trik s číslováním uděláme pomocí funkce COUNTIF (COUNTIF), za předpokladu, že jména zákazníků jsou ve sloupci B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Poté můžete používat normální funkci VPRnajít požadovanou objednávku. Například:
- Najít 2 zboží objednané zákazníkem Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Najít 3 zboží objednané zákazníkem Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
Ve skutečnosti můžete jako vyhledávací hodnotu místo textu zadat odkaz na buňku, jak je znázorněno na následujícím obrázku:
Pokud pouze hledáte 2-e opakování, můžete to udělat bez pomocného sloupce vytvořením složitějšího vzorce:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
V tomto vzorci:
- $ F $ 2 – buňku obsahující jméno kupujícího (je nezměněno, prosím, odkaz je absolutní);
- $ B $ – sloupec Jméno zákazníka;
- Table4 – Váš stůl (toto místo může být také běžným sortimentem);
- $ C16 – koncová buňka vaší tabulky nebo rozsahu.
Tento vzorec najde pouze druhou odpovídající hodnotu. Pokud potřebujete extrahovat zbývající opakování, použijte předchozí řešení.
Pokud potřebujete seznam všech shod – funkce VPR toto není pomocník, protože vrací vždy pouze jednu hodnotu – tečku. Ale Excel má funkci INDEX (INDEX), který si s tímto úkolem snadno poradí. Jak bude takový vzorec vypadat, se dozvíte v následujícím příkladu.
Získejte všechna opakování požadované hodnoty
Jak je zmíněno výše VPR nemůže extrahovat všechny duplicitní hodnoty z naskenovaného rozsahu. K tomu potřebujete trochu složitější vzorec, složený z více funkcí Excelu, jako např INDEX (INDEX), MALÝ (MALÁ) a ŘÁDEK (ČÁRA)
Například níže uvedený vzorec najde všechna opakování hodnoty z buňky F2 v rozsahu B2:B16 a vrátí výsledek ze stejných řádků ve sloupci C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Zadejte tento maticový vzorec do více sousedních buněk, jako jsou buňky F4: F8jak je znázorněno na obrázku níže. Počet buněk musí být roven nebo větší než maximální možný počet opakování hledané hodnoty. Nezapomeňte kliknout Ctrl + Shift + Enterpro správné zadání maticového vzorce.
Pokud vás zajímá, jak to funguje, pojďme se trochu ponořit do detailů vzorce:
Část 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – porovnejte hodnotu v buňce F2 s každou z hodnot v rozsahu B2:B16. Pokud je nalezena shoda, pak výraz STRING(C2:C16)-1 vrátí číslo odpovídajícího řádku (hodnota -1 umožňuje nezahrnout řádek záhlaví). Pokud nejsou žádné shody, funkce IF (IF) vrátí prázdný řetězec.
Výsledek funkce IF (IF) bude takové horizontální pole: {1,"",3,"",5,"","","","","","",12,"","",""}
Část 2:
ROW()-3
СТРОКА()-3
Zde je funkce ŘÁDEK (LINE) funguje jako další čítač. Protože se vzorec zkopíruje do buněk F4:F9, odečteme číslo 3 od výsledku funkce k získání hodnoty 1 v buňce F4 (řádek 4, odečíst 3) získat 2 v buňce F5 (řádek 5, odečíst 3) a tak dále.
Část 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
funkce MALÝ (MALÁ) se vrátí n-oh nejmenší hodnota v datovém poli. V našem případě, kterou pozici (od nejmenší) vrátit, určuje funkce ŘÁDEK (LINE) (viz část 2). Takže pro buňku F4 funkce MALÝ({pole};1) Vrací 1 (nejmenší) prvek pole, tzn 1. Pro buňku F5 Vrací 2 nejmenší prvek v poli, tzn 3, Etc.
Část 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
funkce INDEX (INDEX) jednoduše vrátí hodnotu konkrétní buňky v poli C2:C16. Pro buňku F4 funkce INDEX($C$2:$C$16) vrátí se jablkaPro F5 funkce INDEX($C$2:$C$16) vrátí se Cukroví' a tak dále.
Část 5:
IFERROR()
ЕСЛИОШИБКА()
Nakonec vzorec vložíme dovnitř funkce IFERROR (IFERROR), protože pravděpodobně nebudete spokojeni s chybovou zprávou #AT (#N/A), pokud je počet buněk, do kterých je vzorec zkopírován, menší než počet duplicitních hodnot v zobrazeném rozsahu.
XNUMXD vyhledávání podle známého řádku a sloupce
Provádění hledání XNUMXD v Excelu zahrnuje hledání hodnoty podle známého čísla řádku a sloupce. Jinými slovy, extrahujete hodnotu buňky na průsečíku určitého řádku a sloupce.
Vraťme se tedy k naší tabulce a napišme vzorec s funkcí VPR, který najde informace o ceně citronů prodaných v březnu.
Existuje několik způsobů, jak provést vyhledávání XNUMXD. Podívejte se na možnosti a vyberte si tu, která vám nejlépe vyhovuje.
Funkce VLOOKUP a MATCH
Můžete použít spoustu funkcí VPR (VLOOKUP) a VÍCE EXPOZOVANĚ (MATCH) k nalezení hodnoty na průsečíku polí Název produktu (řetězec) a Měsíc (sloupec) příslušného pole:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
Výše uvedený vzorec je běžná funkce VPR, který hledá přesnou shodu hodnoty „Citrony“ v buňkách A2 až A9. Ale protože nevíte, ve kterém sloupci jsou březnové prodeje, nebudete moci nastavit číslo sloupce pro třetí argument funkce. VPR. Místo toho se použije funkce VÍCE EXPOZOVANĚk definování tohoto sloupce.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Přeloženo do lidské řeči tento vzorec znamená:
- Hledáme postavy „Mar“ – argument lookup_value (hledaná_hodnota);
- Pohled do buněk od A1 do I1 – argument vyhledávací_pole (lookup_array);
- Vrácení přesné shody – argument typ_shody (typ_shody).
Použití 0 ve třetím argumentu říkáte funkce VÍCE EXPOZOVANĚ vyhledejte první hodnotu, která přesně odpovídá hodnotě, kterou hledáte. To je ekvivalentní hodnotě NEPRAVDIVÉ (FALSE) pro čtvrtý argument VPR.
Takto můžete vytvořit vzorec pro obousměrné vyhledávání v aplikaci Excel, známý také jako dvourozměrné vyhledávání nebo obousměrné vyhledávání.
Funkce SUMPRODUCT
funkce SUMPRODUCT (SUMPRODUCT) vrátí součet součinů vybraných polí:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Funkce INDEX a MATCH
V příštím článku tyto funkce podrobně vysvětlím, takže prozatím stačí zkopírovat tento vzorec:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Pojmenované rozsahy a operátor křižovatky
Pokud nejste na všechny ty složité vzorce Excelu, mohl by se vám líbit tento vizuální a zapamatovatelný způsob:
- Vyberte tabulku, otevřete kartu Vzorce (Vzorce) a klikněte Vytvořit z výběru (Vytvořit z výběru).
- Zaškrtněte políčka Horní řádek (na řádku výše) a Levý sloupec (ve sloupci vlevo). Microsoft Excel přiřadí názvy rozsahům z hodnot v horním řádku a levém sloupci tabulky. Nyní můžete vyhledávat pomocí těchto názvů přímo bez vytváření vzorců.
- Do libovolné prázdné buňky napište =název_řádku název_sloupce, například takto:
= Citrony Mar
… nebo naopak:
= Mar Lemons
Pamatujte, že názvy řádků a sloupců musí být odděleny mezerou, která v tomto případě funguje jako operátor průniku.
Když zadáte název, Microsoft Excel zobrazí nápovědu se seznamem odpovídajících názvů, stejně jako když zadáváte vzorec.
- Stisknout vstoupit a zkontrolujte výsledek
Obecně platí, že ať zvolíte kteroukoli z výše uvedených metod, výsledek dvourozměrného vyhledávání bude stejný:
Použití více SVYHLEDAT v jednom vzorci
Stává se, že hlavní tabulka a vyhledávací tabulka nemají společný ani jeden sloupec, což vám brání v použití obvyklé funkce VPR. Existuje však další tabulka, která neobsahuje informace, které nás zajímají, ale má společný sloupec s hlavní tabulkou a vyhledávací tabulkou.
Podívejme se na následující příklad. Máme hlavní tabulku se sloupcem SKU (nové), kam chcete přidat sloupec s odpovídajícími cenami z jiné tabulky. Kromě toho máme 2 vyhledávací tabulky. První (Vyhledávací tabulka 1) obsahuje aktualizovaná čísla SKU (nové) a názvy produktů a druhý (Vyhledávací tabulka 2) – názvy produktů a stará čísla SKU (staré).
Chcete-li přidat ceny z druhé vyhledávací tabulky do hlavní tabulky, musíte provést akci známou jako double VPR nebo vnořené VPR.
- Napište funkci VPR, který v tabulce najde název produktu Vyhledávací tabulka 1použitím SKU, jako požadovaná hodnota:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Zde Nové_SKU – pojmenovaný rozsah $A:$B ve stole Vyhledávací tabulka 1, 2 – jedná se o sloupec B, který obsahuje názvy zboží (viz obrázek výše)
- Napište vzorec pro vložení cen z tabulky Vyhledávací tabulka 2 na základě známých názvů produktů. Chcete-li to provést, vložte vzorec, který jste vytvořili dříve, jako vyhledávací hodnotu pro novou funkci VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Zde Cena – pojmenovaný rozsah $ A: $ C ve stole Vyhledávací tabulka 2, 3 je sloupec C obsahující ceny.
Obrázek níže ukazuje výsledek vrácený vzorcem, který jsme vytvořili:
Dynamické nahrazování dat z různých tabulek pomocí funkce SVYHLEDAT a NEPŘÍMÝ
Nejprve si ujasněme, co rozumíme pod pojmem „Dynamická substituce dat z různých tabulek“, abychom se ujistili, že si rozumíme správně.
Existují situace, kdy existuje několik listů s daty stejného formátu a je nutné extrahovat potřebné informace z určitého listu v závislosti na hodnotě, která je zadána v dané buňce. Myslím, že je jednodušší to vysvětlit na příkladu.
Představte si, že máte zprávy o prodeji pro několik regionů se stejnými produkty a ve stejném formátu. Chcete najít údaje o prodeji pro konkrétní region:
Pokud máte jen dvě takové sestavy, pak můžete použít hanebně jednoduchý vzorec s funkcemi VPR и IF (IF) pro výběr požadovaného přehledu pro vyhledávání:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Kde:
- $D$2 je buňka obsahující název produktu. Všimněte si, že zde používáme absolutní odkazy, abychom se vyhnuli změně vyhledávací hodnoty při kopírování vzorce do jiných buněk.
- $ D3 je buňka s názvem regionu. Používáme absolutní odkaz na sloupec a relativní odkaz na řádek, protože plánujeme zkopírovat vzorec do jiných buněk ve stejném sloupci.
- FL_Sales и CA_Prodej – názvy tabulek (nebo pojmenovaných rozsahů), které obsahují odpovídající sestavy prodeje. Můžete samozřejmě použít například obvyklé názvy listů a odkazy na oblasti buněk 'FL Sheet'!$A$3:$B$10, ale pojmenované rozsahy jsou mnohem pohodlnější.
Nicméně, když existuje mnoho takových tabulek, funkce IF není nejlepší řešení. Místo toho můžete použít funkci NEPŘÍMÝ (NEPŘÍMÉ) pro návrat do požadovaného rozsahu vyhledávání.
Jak asi víte, funkce NEPŘÍMÝ se používá k vrácení odkazu zadaného textovým řetězcem, což je přesně to, co nyní potřebujeme. Takže směle nahraďte ve výše uvedeném vzorci výraz funkcí IF propojit s funkcí NEPŘÍMÝ. Zde je kombinace VPR и NEPŘÍMÝ funguje skvěle s:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Kde:
- $D$2 – jedná se o buňku s názvem produktu, je neměnný kvůli absolutní vazbě.
- $ D3 je buňka obsahující první část názvu oblasti. V našem příkladu toto FL.
- _Odbyt – společná část názvu všech pojmenovaných rozsahů nebo tabulek. Při kombinaci s hodnotou v buňce D3 tvoří plně kvalifikovaný název požadovaného rozsahu. Níže jsou uvedeny některé podrobnosti pro ty, kteří s touto funkcí začínají NEPŘÍMÝ.
Jak fungují NEPŘÍMÉ a VLOOKUP
Nejprve mi dovolte připomenout syntaxi funkce NEPŘÍMÝ (NEPŘÍMÝ):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
Prvním argumentem může být odkaz na buňku (styl A1 nebo R1C1), název rozsahu nebo textový řetězec. Druhý argument určuje, jaký styl odkazu je obsažen v prvním argumentu:
- A1pokud je argument PRAVÝ KÓD (PRAVDA) nebo není uvedeno;
- R1C1, Pokud FAS E (NEPRAVDIVÉ).
V našem případě má odkaz styl A1, takže můžete vynechat druhý argument a soustředit se na první.
Vraťme se tedy k našim prodejním reportům. Pokud si pamatujete, pak každá sestava je samostatná tabulka umístěná na samostatném listu. Aby vzorec fungoval správně, musíte pojmenovat tabulky (nebo rozsahy) a všechny názvy musí mít společnou část. Například takto: CA_Prodej, FL_Prodej, TX_Sales a tak dále. Jak vidíte, „_Sales“ je přítomno ve všech názvech.
funkce NEPŘÍMÝ spojuje hodnotu ve sloupci D a textový řetězec „_Sales“, čímž říká VPR ve které tabulce hledat. Pokud buňka D3 obsahuje hodnotu „FL“, vzorec prohledá tabulku FL_Prodej, pokud „CA“ – v tabulce CA_Prodej a tak dále.
Výsledek funkcí VPR и NEPŘÍMÝ bude následující:
Pokud jsou data umístěna v různých knihách aplikace Excel, musíte před pojmenovaný rozsah přidat název knihy, například:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Pokud je funkce NEPŘÍMÝ odkazuje na jiný sešit, tento sešit musí být otevřený. Pokud je zavřený, funkce ohlásí chybu. #REF! (#SSYL!).