VLOOKUP k vrácení více hodnot | Průvodce krok za krokem
Excel Vlookup pro vrácení více hodnot
Jednou z klíčových funkcí funkce VLOOKUP je, že bude pracovat pro jedinečné hodnoty a pokud existují nějaké duplicitní hodnoty, vrátí se jakákoli první nalezená hodnota i pro všechny ostatní vyhledávací hodnoty. Toto je jedna z klíčových věcí, které musíme mít na paměti při používání vzorce VLOOKUP. Když se vyhledávací hodnota objeví vícekrát a pokud má více hodnot, musíme zahrnout různé strategie, v tomto článku vám ukážeme, jak vrátit více hodnot pomocí funkce VLOOKUP.
Jak vrátit více hodnot pomocí funkce Vlookup?
Jak jsme řekli výše, VLOOKUP funguje pro jedinečné hodnoty a pro duplicitní hodnoty vrátí první nalezenou hodnotu.
Tuto šablonu Vlookup pro vrácení více hodnot Excel si můžete stáhnout zde - Vlookup pro vrácení více hodnot Excel šablonyPodívejte se například na níže uvedená data.
Máme „tabulku 1“ a „tabulku 2“, v „tabulce 1“ máme ovoce a jejich ceny v různých městech a pro „tabulku 2“ musíme zjistit nákladovou cenu z „tabulky 1“ pomocí funkce VLOOKUP. Nejprve použijte funkci VLOOKUP.
- Podívejte se, máme stejnou cenu pro všechna města. Například v „tabulce 1“ pro „Apple“ ve městě „Bangalore“ máme 108, protože se jedná o první hodnotu nalezenou v tabulce pro „Apple“, která pro všechny města vrátila stejnou hodnotu.
- Podobně pro „Grapes“ je první hodnota 79 a stejná byla vrácena pro všechna města a v případě „Orange“ také vrátila 56 pro všechna města.
V těchto případech tedy musíme vytvořit pomocný sloupec a vytvořit tak jedinečný seznam hodnot vyhledávání. Každé ovoce má pro každé město jiné ceny, takže kombinace názvu ovoce a města může vytvořit jedinečný seznam, vložit sloupec pomocníka a kombinovat název ovoce a název města.
Takže každý název ovoce v kombinaci s městem včetně zpětného lomítka (/) jako oddělovače mezi názvem ovoce a názvem města.
Nyní se vraťte k „tabulce 2“ a otevřete funkci VLOOKUP.
Nyní musíme zahrnout stejnou strategii pomocného sloupce, abychom mohli vybrat vyhledávací hodnotu, nejprve zvolit název ovoce.
Poté zkombinujte zpětné lomítko a poté jej zkombinujte s názvem města.
Nyní zkombinujte název města.
Nyní je vyhledávací hodnota podobná sloupci pomocníka, nyní vyberte pole tabulky počínaje sloupcem pomocníka.
Nyní uveďte číslo sloupce jako 4 a vyhledávání rozsahu jako FALSE nebo 0.
Tady máš, máme nový ceník nákladů s přesnými čísly, řekněme díky pomocnému sloupci nebo kombinaci Fruit Name & City.
Pro více hodnot použijte alternativní metody
Viděli jsme, jak může být pomocný sloupec užitečný k načtení více hodnot pomocí vzorce VLOOKUP. Ale představte si situaci níže.
V tomto nemáme žádný název města k vytvoření sloupce zřetězení, takže možná budeme muset použít různé strategie níže je složitý vzorec, který můžeme použít k získání více hodnot duplicitních jedinečných hodnot.
= INDEX ($ B $ 2: $ B $ 11, MALÉ (POKUD (E3 = $ A $ 2: $ A $ 11, ŘÁDEK ($ A $ 2: $ A $ 11)) - ŘÁDEK ($ A $ 2) +1), ŘÁDEK (1: 1)))
Tento vzorec vypadá zdlouhavě, že? Máme však jinou alternativní metodu, tj. Kombinovat název ovoce s jejich počtem v seznamu.
Použijte níže uvedenou funkci COUNTIF k vytvoření pomocného sloupce.
Výše uvedená funkce nám poskytne počet každého ovoce v kombinaci se samotným názvem ovoce. Pro příklad se podívejte na řádek číslo 4 v tomto, máme počet „Apple“ dvakrát, takže count říká 2 a v kombinaci s názvem ovoce nám dává „2Apple“. Tak se vytvoří jedinečný seznam ovoce.
Nyní vytvořte vyhledávací tabulku jako níže.
Nyní otevřete funkci VLOOKUP ve vyhledávací tabulce, tj. V buňce H3.
Ve sloupci pomocníka se počítá první kombinovaná hodnota, takže zde vyberte číselnou hodnotu a poté zkombinujte s názvem ovoce.
Nyní vyberte tabulku a zadejte indexové číslo sloupce, abyste získali výsledek.
Věci k zapamatování
- VLOOKUP vrátí stejnou hodnotu pro vyhledávací hodnoty, pokud má vyhledávací hodnota duplicitní názvy.
- Chcete-li načíst více hodnot stejné vyhledávací hodnoty, musíme vytvořit pomocné sloupce pomocí kterékoli z výše uvedených 3 metod.
