Vo funkcii VLOOKUP často definujeme col_index_no static. Pevne ho zakódujeme do vzorca VLOOKUP, napríklad VLOOKUP (id, údaje,3, 0). Problém nastáva, keď do údajov vložíme alebo odstránime stĺpec. Ak odstránime alebo pridáme stĺpec pred alebo za 3. stĺpec, tretí stĺpec už nebude odkazovať na zamýšľaný stĺpec. Toto je jeden problém. Iné je, keď máte na vyhľadanie viac stĺpcov. V každom vzorci budete musieť upraviť index stĺpcov. Jednoduché vkladanie kópií nepomôže.
Ale čo keby ste povolili VLOOKUP, aby sa pozrel na nadpisy a vrátil iba zodpovedajúcu hodnotu nadpisov. Toto sa nazýva obojsmerné VLOOKUP.
Ak mám napríklad vzorec VLOOKUP preznačky stĺpec, potom by mal hľadať VLOOKUP značky údaje a návratovú hodnotu z tohto stĺpca. To vyrieši náš problém.
Hmm … Dobre, ako to teda urobíme? Použitím funkcie Match v rámci funkcie VLOOKUP.
Generický vzorec
=VLOOKUP(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Lookup_value: hodnota vyhľadávania v prvom stĺpci table_array.
Pole_rozpisu: rozsah, v ktorom chcete vykonať vyhľadávanie. Napr. A2, D10.
Lookup_heading: nadpis, ktorý chcete vyhľadať v nadpisoch table_array.
Tabuľkové nadpisy: Odkaz na nadpisy v poli tabuľky. Napr. ak je tabuľka A2, D10 a nadpisy v hornej časti každého stĺpca, potom jeho A1: D1.
Takže teraz vieme, čo potrebujeme pre dynamický col_index, poďme si všetko vyjasniť na príklade.
Príklad dynamického VLOOKUPU
V tomto prípade máme túto tabuľku, ktorá obsahuje údaje o študentoch v rozsahu A4: E16.
Pomocou rolky č a záhlavia chcem načítať údaje z tejto tabuľky. V tomto prípade v bunke H4 chcem získať údaje o roli, ktorá nie je zapísaná v bunke G4, a o záhlaví v H3. Ak zmením nadpis, údaje z príslušného rozsahu by sa mali načítať do bunky H4.
Napíšte tento vzorec do bunky H4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Pretože naše pole tabuľky je B4: E16, naše pole nadpisov sa zmení na B3: E3.
Poznámka: Ak sú vaše údaje dobre štruktúrované, hlavičky stĺpcov budú mať rovnaký počet stĺpcov a bude to prvý riadok v tabuľke.
Ako to funguje:
Hlavnou časťou je automatické vyhodnotenie indexového čísla stĺpca. Na to sme použili funkciu MATCH.
ZÁPAS (H3, B3: E3,0): Pretože H3 obsahuje „študent“, MATCH vráti 2. Ak by H3 mal „známku“, vrátil by 4 a podobne. Vzorec VLOOKUP bude mať konečne col_index_num.
= VLOOKUP (G4, B4: E16,2,0)
Ako vieme, funkcia MATCH vracia indexové číslo danej hodnoty v dodanom jednorozmernom rozsahu. Preto MATCH vyhľadá akúkoľvek hodnotu zapísanú v H3 v rozsahu B3: E3 a vráti jej indexové číslo.
Teraz, kedykoľvek zmeníte nadpis v H3, ak je v nadpisoch, tento vzorec vráti hodnotu z príslušného stĺpca. V opačnom prípade budete mať #N/A chybu.
VLOOKUP rýchlo vo viacerých stĺpcoch
Vo vyššie uvedenom príklade sme potrebovali odpoveď z jednej hodnoty stĺpca. Ale čo keď chcete získať viac stĺpcov naraz. Ak skopírujete vyššie uvedený vzorec, vráti chyby. Musíme v ňom urobiť niekoľko drobných zmien, aby bol prenosný.
Použitie absolútnych referencií s VLOOKUP
Do bunky H2 napíšte nasledujúci vzorec.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Teraz skopírujte H2 do všetkých buniek v rozsahu H2: J6, aby ste ho naplnili údajmi.
Ako to funguje:
Tu som dal absolútna referencia pre každý rozsah okrem riadka vo vyhľadávacej hodnote pre VLOOKUP (G2 dolárov) a stĺpcom v parametri lookup_value pre MATCH (1 H $).
$ G2: To umožní, aby sa riadok zmenil na hodnotu vyhľadávania pre funkciu VLOOKUP pri kopírovaní smerom nadol, ale obmedzí sa zmena stĺpca pri kopírovaní doprava. Vďaka tomu bude VLOOKUP hľadať Id zo stĺpca G iba s relatívnym riadkom.
Podobne, 1 H $ umožní zmenu stĺpca pri horizontálnom kopírovaní a obmedzenie riadku pri kopírovaní smerom nadol.
Použitie pomenovaných rozsahov
Vyššie uvedený príklad funguje dobre, ale čítanie a písanie tohto vzorca je náročné. A to nie je vôbec prenosné. To je možné zjednodušiť pomocou pomenované rozsahy.
Najprv si tu urobíme pomenovanie. Pre tento príklad som pomenoval
$ B $ 2: $ E $ 14: ako údaje
$ B $ 1: $ E $ 1: ako nadpisy
1 H $: Pomenujte ho ako Nadpis. Urobte stĺpce relatívne. Ak to chcete urobiť, vyberte H1. Stlačte kombináciu klávesov CTRL+F3, kliknite na nové, v časti Odkazy odstráňte „$“ z prednej časti H.
G2 dolárov: Podobne to pomenujte ako RollNo. Tento čas robí riadok relatívnym odstránením '$' z prednej časti 2.
Teraz, keď máte na hárku všetky mená, napíšte tento vzorec kdekoľvek v súbore Excel. Vždy dostane správnu odpoveď.
= VLOOKUP (RollNo, Data, MATCH (nadpisy, nadpisy, 0), 0)
Viete, každý si to môže prečítať a porozumieť tomu.
Použitím týchto metód teda môžete urobiť col_index_num dynamickým. Dajte mi vedieť, či to pomohlo v nižšie uvedenej sekcii komentárov.
Ako používať tFunkcia VLOOKUP v programe Excel
Relatívna a absolútna referencia v programe Excel
Pomenované rozsahy v programe Excel
Ako VYHLÁSIŤ z iného listu programu Excel
VLOOKUP Viacnásobné hodnoty
Populárne články
50 Skratka pre Excel, ktorá zvýši vašu produktivitu : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať tFunkcia VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.