Excel Vlookup na vrátenie viacerých hodnôt
Jednou z kľúčových funkcií funkcie VLOOKUP je, že bude pracovať pre jedinečné hodnoty, a ak existujú nejaké duplicitné hodnoty, potom sa vráti akákoľvek prvá nájdená hodnota aj pre všetky ostatné vyhľadávacie hodnoty. Toto je jedna z kľúčových vecí, ktoré musíme mať na pamäti pri použití vzorca VLOOKUP. Keď sa vyhľadávacia hodnota objaví viackrát a ak má viac hodnôt, musíme zahrnúť rôzne stratégie. V tomto článku si ukážeme, ako pomocou funkcie VLOOKUP vrátiť viac hodnôt.
Ako vrátiť viac hodnôt pomocou funkcie Vlookup?
Ako sme už povedali vyššie, VLOOKUP pracuje pre jedinečné hodnoty a pre duplicitné hodnoty vráti prvú nájdenú hodnotu.
Napríklad si pozrite nasledujúce údaje.
V tabuľkách „Tabuľka 1“ a „Tabuľka 2“ máme tabuľky „Plody“ a ich ceny v rôznych mestách. V prípade tabuľky 2 musíme pomocou funkcie VLOOKUP dospieť k nákladovej cene tabuľky „Tabuľka 1“. Najskôr použite funkciu VLOOKUP.
- Pozrite, pre všetky mestá máme rovnakú cenu. Napríklad v „Tabuľke 1“ pre „Apple“ v meste „Bangalore“ máme 108, pretože toto je prvá hodnota nájdená v tabuľke pre „Apple“, ktorá sa vrátila rovnako pre všetky mestá.
- Podobne pre „Grapes“ je prvá hodnota 79 a to isté bolo vrátené pre všetky mestá. V prípade „Orange“ tiež vrátilo 56 pre všetky mestá.
Takže v týchto prípadoch musíme vytvoriť pomocný stĺpec, aby sme vytvorili jedinečný zoznam vyhľadávacích hodnôt. Každé ovocie má pre každé mesto odlišné ceny, takže kombináciou názvu ovocia a mesta môžete vytvoriť jedinečný zoznam, vložiť stĺpec pomocníka a skombinovať názov ovocia a názov mesta.
Takže každý názov ovocia v kombinácii s mestom tak, že ako oddeľovač medzi názvom ovocia a názvom mesta uvediete spätnú lomku (/).
Teraz sa vráťte k „Tabuľke 2“ a otvorte funkciu VLOOKUP.
Teraz tu musíme zahrnúť rovnakú stratégiu pomocného stĺpca, aby sme mohli zvoliť vyhľadávaciu hodnotu, najskôr zvoliť názov ovocia.
Pred spojením s názvom mesta potom skombinujte spätnú lomku.
Teraz skombinujte názov mesta.
Teraz je vyhľadávacia hodnota podobná stĺpcu pomocníka. Teraz vyberte pole tabuľky začínajúce od pomocného stĺpca.
Teraz uveďte číslo stĺpca ako 4 a hľadanie rozsahu ako FALSE alebo 0.
A je to, máme nový cenník nákladov s presnými číslami, povedzme vďaka stĺpcu Pomocník alebo kombináciou Fruit Name & City.
Použite alternatívne metódy pre viac hodnôt
Videli sme, ako môže byť pomocný stĺpec užitočný pri načítaní viacerých hodnôt pomocou vzorca VLOOKUP. Ale predstavte si situáciu nižšie.
V tomto prípade nemáme žiadny názov mesta na vytvorenie stĺpca zreťazenia, takže bude možno potrebné použiť rôzne stratégie. Ďalej uvádzame zložitý vzorec, ktorý môžeme použiť na získanie viacerých hodnôt duplicitných jedinečných hodnôt.
= INDEX ($ B $ 2: $ B $ 11, MALÉ (AK (E3 = $ A $ 2: $ A $ 11, RIADOK ($ A $ 2: $ A $ 11)) - RIADOK ($ A $ 2) +1), RIADOK (1: 1)))
Tento vzorec vyzerá zdĺhavo, nie? Máme však inú alternatívnu metódu, tj kombinovať názov ovocia s ich počtom v zozname.
Pomocou nižšie uvedenej funkcie COUNTIF vytvorte pomocný stĺpec.
Vyššie uvedená funkcia nám poskytne počet jednotlivých druhov ovocia v kombinácii so samotným názvom ovocia. Napríklad sa pozrite na riadok číslo 4, kde máme počet „Apple“ dvakrát, takže count hovorí 2 a v kombinácii s názvom ovocia nám dáva „2Apple“. Tak vznikne jedinečný zoznam ovocia.
Teraz vytvorte vyhľadávaciu tabuľku, ako je uvedená nižšie.
Teraz otvorte funkciu VLOOKUP vo vyhľadávacej tabuľke, tj. V bunke H3.
V stĺpci Pomocník sa počíta prvá kombinovaná hodnota, takže tu vyberte číselnú hodnotu a potom ju skombinujte s názvom ovocia.
Teraz vyberte tabuľku a zadajte indexové číslo stĺpca, aby ste dosiahli výsledok.
Na čo treba pamätať
- Ak má vyhľadávaná hodnota duplicitné názvy, funkcia VLOOKUP vráti rovnakú hodnotu pre vyhľadávané hodnoty.
- Na načítanie viacerých hodnôt s rovnakou vyhľadávacou hodnotou musíme vytvoriť pomocné stĺpce pomocou ktorejkoľvek z vyššie uvedených 3 metód.
Odporúčané články
Toto bol sprievodca nástrojom VLOOKUP na vrátenie viacerých hodnôt. Tu diskutujeme o tom, ako vrátiť viac hodnôt pomocou funkcie VLOOKUP a ako použiť alternatívne metódy pre viac hodnôt a šablónu programu Excel na stiahnutie. Môžete sa tiež pozrieť na tieto užitočné funkcie v programe Excel -
- Používajte VLOOKUP s Power BI
- Použiť VYHĽADÁVANIE pre text
- Použite VLOOKUP s funkciou Match
- Výukový program VLOOKUP Excel
