Nájdite viac políčok údajov pomocou aplikácie Excel VLOOKUP

Kombináciou funkcie VLOOKUP programu Excel a funkcie COLUMN môžete vytvoriť vzorec vyhľadávania, ktorý vám umožňuje vrátiť viac hodnôt z jedného riadka databázy alebo tabuľky údajov.

V príklade uvedenom na obrázku vyššie vyhľadávací vzorec uľahčuje vrátenie všetkých hodnôt - napríklad ceny, čísla dielov a dodávateľa - súvisiacich s rôznymi kusmi hardvéru.

01 z 10

Vráťte viac hodnôt pomocou aplikácie Excel VLOOKUP

Nasledujúcimi krokmi sa vytvorí vzorec vyhľadávania, ktorý je videný na obrázku vyššie, čím sa z jedného záznamu údajov vrátia viac hodnôt.

Vzhľad vyhľadávania vyžaduje, aby bola funkcia COLUMN vnorená do vnútra VLOOKUP.

Nasadenie funkcie zahŕňa zadanie druhej funkcie ako jeden z argumentov pre prvú funkciu.

V tejto príručke bude funkcia COLUMN zadaná ako argument indexu stĺpcov pre VLOOKUP.

Posledný krok v príručke zahŕňa kopírovanie vzoru vyhľadávania do ďalších stĺpcov, aby sa získali ďalšie hodnoty pre vybranú časť.

Obsah výučby

• Zadanie údajov výučby
• Vytvorenie pomenovaného rozsahu pre tabuľku s údajmi
• Spustenie funkcie VLOOKUP
• Zadanie argumentu vyhľadávacieho hodnotenia pomocou referencií absolútnych buniek
• Zadanie argumentu tabuľkového poľa
• Zadanie funkcie VNITEK COLUMN
• Dokončenie funkcie VLOOKUP
• Kopírovanie vyhľadávacieho vzorca s plniacou rukoväťou
• Načítanie údajov pomocou vzorca vyhľadávania

02 z 10

Zadajte výučbové údaje

Prvým krokom v príručke je zadanie údajov do pracovného hárka programu Excel .

Ak chcete postupovať podľa krokov v príručke, zadajte údaje uvedené na obrázku vyššie do nasledujúcich buniek .

• Zadajte najvyšší rozsah údajov do buniek D1 až G1
• Zadajte druhý rozsah do buniek D4 až G10

Kritériá vyhľadávania a vzorec vyhľadávania vytvorený počas tohto tutoriálu budú zadané do riadku 2 pracovného hárka.

Tutoriál nezahŕňa formátovanie zobrazené na obrázku, ale to neovplyvní spôsob fungovania vyhľadávacieho vzorca.

Informácie o možnostiach formátovania, ktoré sú podobné tým, ktoré sú uvedené vyššie, sú k dispozícii v tomto základnom výučbe formátovania programu Excel .

Tutorial Kroky

1. Zadajte dáta, ako vidíte na obrázku vyššie, do buniek D1 až G10

03 z 10

Vytvorenie pomenovaného rozsahu pre tabuľku s údajmi

Pomenovaný rozsah je jednoduchý spôsob, ako odkázať na rozsah údajov vo vzorci. Namiesto toho, aby ste zadali údaje o bunkách , môžete zadať len názov rozsahu.

Druhou výhodou pri používaní pomenovaného rozsahu je to, že referencie buniek pre tento rozsah sa nikdy nezmení ani vtedy, keď je vzorec skopírovaný do iných buniek v pracovnom hárku.

Názvy rozsahu sú preto alternatívou k používaniu absolútnych odkazov na bunky, aby sa zabránilo chybám pri kopírovaní vzorcov.

Poznámka: Názov rozsahu neobsahuje nadpisy alebo názvy polí pre dáta (riadok 4), ale len údaje samotné.

Tutorial Kroky

1. Označte bunky D5 až G10 v pracovnom hárku a vyberte ich
2. Kliknite na pole Názov nad stĺpcom A
3. Do poľa Názov zadajte "Tabuľka" (bez úvodzoviek)
4. Stlačte kláves ENTER na klávesnici
5. Bunky D5 až G10 majú teraz názov rozsahu "Tabuľka". Názov príkazu VLOOKUP tabuľkového poľa použijeme neskôr v príručke

04 z 10

Otvorenie dialógového okna VLOOKUP

Aj keď je možné jednoducho napísať vzorec vyhľadávania priamo do bunky v pracovnom hárku, mnohí ľudia považujú za ťažké zachovať syntax rovno - najmä pre komplexný vzorec, ako je ten, ktorý používame v tomto výučbe.

Alternatívou je v tomto prípade použiť dialógové okno VLOOKUP. Takmer všetky funkcie programu Excel majú dialógové okno, ktoré umožňuje zadávať jednotlivé argumenty funkcie na samostatnom riadku.

Tutorial Kroky

1. Kliknite na bunku E2 pracovného hárka - miesto, kde sa zobrazia výsledky dvojrozmerného vzoru vyhľadávania
2. Kliknite na kartu Vzorky pásky
3. Ak chcete otvoriť rozbaľovací zoznam funkcií, kliknite na voľbu Vyhľadávanie a referencia na karte
4. Kliknutím na VLOOKUP v zozname otvoríte dialógové okno funkcie

05 z 10

Zadanie argumentu vyhľadávacieho hodnotenia pomocou referencií absolútnych buniek

Normálne sa hodnota vyhľadávania zhoduje s údajom v prvom stĺpci tabuľky s údajmi.

V našom príklade sa vyhľadávacia hodnota vzťahuje na názov hardvérovej časti, o ktorej chceme nájsť informácie.

Prípustné typy údajov pre vyhľadávaciu hodnotu sú:

• textové údaje
• logická hodnota (TRUE alebo FALSE)
• číslo
• odkaz bunky na hodnotu v pracovnom hárku

V tomto príklade zadáme odkaz na bunku, kde sa nachádza názov časti - bunka D2.

Absolútne odkazy na bunky

V neskoršom kroku v príručke budeme skopírovať vzorec vyhľadávania v bunke E2 do buniek F2 a G2.

Normálne, keď sú vzorce skopírované v programe Excel, odkazy na bunky sa zmenia tak, aby odzrkadľovali ich nové umiestnenie.

Ak k tomu dôjde, D2 - referencia bunky pre vyhľadávaciu hodnotu - sa zmení vzhľadom na to, že vzorec je skopírovaný, čím sa vytvárajú chyby v bunkách F2 a G2.

Aby sme zabránili chybám, skonvertujeme bunkovú referenciu D2 na absolútnu bunkovú referenciu .

Absolútne odkazy na bunky sa pri kopírovaní vzorcov nemenia.

Absolútne odkazy na bunky sa vytvárajú stlačením klávesu F4 na klávesnici. Týmto spôsobom sa pridávajú znaky dolára okolo odkazu na bunku, ako napríklad $ D $ 2

Tutorial Kroky

1. Kliknite na riadok lookup_value v dialógovom okne
2. Kliknutím na bunku D2 pridajte tento článok do riadku lookup_value . Toto je bunka, kde zadáme názov časti, o ktorej hľadáme informácie
3. Bez presunutia bodu vkladania stlačte kláves F4 na klávesnici, aby sa D2 premenil na absolútnu bunkovú referenciu $ D $ 2
4. Ponechajte dialógové okno funkcie VLOOKUP otvorené pre ďalší krok v príručke

06 z 10

Zadanie argumentu tabuľkového poľa

Tabuľkový pohľad je tabuľka údajov, ktoré vyhľadávací vzorec vyhľadáva, aby našiel požadované informácie.

Súbor tabuľky musí obsahovať aspoň dva stĺpce údajov .

• prvý stĺpec obsahuje argument pre hodnotu vyhľadávania (predchádzajúci krok v príručke)
• druhý a ďalšie stĺpce sa budú vyhľadávať podľa vzoru vyhľadávania, aby našli informácie, ktoré špecifikujeme.

Argument tabuľkového poľa musí byť zadaný buď ako rozsah obsahujúci referencie buniek pre tabuľku s údajmi alebo ako názov rozsahu .

V tomto príklade použijeme názov rozsahu vytvorený v kroku 3 tutoriálu.

Tutorial Kroky

1. Kliknite na riadok table_array v dialógovom okne
2. Zadajte "Tabuľka" (bez úvodzoviek), ak chcete zadať názov rozsahu pre tento argument
3. Ponechajte dialógové okno funkcie VLOOKUP otvorené pre ďalší krok v príručke

07 z 10

Vloženie funkcie COLUMN

Normálne VLOOKUP vracia iba údaje z jedného stĺpca údajovej tabuľky a tento stĺpec je nastavený argumentom indexu stĺpca .

V tomto príklade máme však tri stĺpce, z ktorých chceme vrátiť údaje, takže potrebujeme spôsob, ako ľahko zmeniť indexové číslo stĺpca bez úpravy nášho vzoru vyhľadávania.

Toto je miesto, kde COLUMN funkcia prichádza dovnútra. Zadaním ako argument indexu stĺpca indexu , to sa zmení ako vzorec vyhľadávania je skopírovaný z bunky D2 do buniek E2 a F2 neskôr v tutoriáli.

Funkcie hniezdenia

Funkcia COLUMN preto slúži ako argument indexu počtu stĺpcov VLOOKUP.

To sa dosiahne vložením funkcie COLUMN vo vnútri VLOOKUP v riadku Col_index_num dialógového okna.

Ručné zadávanie funkcie COLUMN

Pri vkladaní funkcií program Excel neumožňuje otvoriť dialógové okno druhej funkcie a zadat jeho argumenty.

Funkcia COLUMN preto musí byť zadaná ručne v riadku Col_index_num .

Funkcia COLUMN má iba jeden argument - referenčný parameter, ktorý je odkazom na bunku.

Výber referenčného argumentu funkcie COLUMN

Úloha funkcie COLUMN je vrátiť číslo stĺpca zadaného ako referenčný argument.

Inými slovami, konvertuje písmeno stĺpca na číslo, pričom stĺpec A je prvý stĺpec, stĺpec B druhý a tak ďalej.

Keďže prvé pole údajov, ktoré chceme vrátiť, je cena položky - čo je v stĺpci dva údajovej tabuľky - môžeme vybrať referenciu bunky pre ľubovoľnú bunku v stĺpci B ako referenčný argument, aby sme dostali číslo 2 pre argumentu Col_index_num .

Tutorial Kroky

1. V dialógovom okne funkcie VLOOKUP kliknite na riadok Col_index_num
2. Zadajte stĺpec s názvom funkcií, po ktorom nasleduje otvorená kruhová konzola " ( "
3. Kliknutím na bunku B1 v pracovnom hárku zadáte odkaz na bunku ako referenčný argument
4. Zadajte zatvorenie okrúhleho držiaka " ) " na dokončenie funkcie COLUMN
5. Ponechajte dialógové okno funkcie VLOOKUP otvorené pre ďalší krok v príručke

08 z 10

Zadanie argumentu vyhľadávania rozsahu VLOOKUP

Argument Range_lookup VLOOKUP je logická hodnota (TRUE alebo FALSE), ktorá označuje, či chcete VLOOKUP nájsť presnú alebo približnú zhodu s Lookup_value.

• Ak je TRUE alebo ak je tento argument vynechaný, VLOOKUP vráti buď presnú zhodu s hodnotou Lookup_value, alebo ak presná zhoda nie je nájdená, VLOOKUP vráti ďalšiu najväčšiu hodnotu. Aby mohol tento vzorec urobiť, údaje v prvom stĺpci table_array sa musia zoradiť vo vzostupnom poradí .
• Ak FALSE, VLOOKUP bude používať presnú zhodu len s Lookup_value. Ak v prvom stĺpci tabuľky Table_array existujú dve alebo viac hodnôt, ktoré sa zhodujú s hodnotou vyhľadávania, použije sa prvá nájdená hodnota. Ak sa nenájde presná zhoda, vráti sa chyba # N / A.

V tomto návode, keďže hľadáme konkrétne informácie o konkrétnej hardvérovej položke, nastavíme parameter Range_lookup na hodnotu False .

Tutorial Kroky

1. Kliknite na riadok Range_lookup v dialógovom okne
2. Napíšte slovo False v tomto riadku, aby ste naznačili, že chceme, aby VLOOKUP vrátil presnú zhodu pre údaje, ktoré hľadáme
3. Kliknutím na tlačidlo OK dokončíte vzorec vyhľadávania a zatvorte dialógové okno
4. Pretože sme ešte nezadali kritériá vyhľadávania do bunky D2, bude v bunke E2 prítomná chyba # N / A
5. Táto chyba bude opravená, keď pridáme vyhľadávacie kritériá v poslednom kroku tutoriálu

09 z 10

Kopírovanie vyhľadávacieho vzorca s plniacou rukoväťou

Vzor vyhľadávania je určený na získanie údajov z viacerých stĺpcov údajovej tabuľky naraz.

Aby sme tak urobili, vzorec vyhľadávania musí byť vo všetkých poliach, z ktorých chceme získať informácie.

V tejto príručke chceme získať údaje zo stĺpcov 2, 3 a 4 údajovej tabuľky - to je cena, číslo dielca a meno dodávateľa pri zadávaní názvu časti ako vyhľadávacia hodnota.

Keďže údaje sú v pracovnom hárku usporiadané v pravidelnom vzore, môžeme skopírovať vzorec vyhľadávania v bunke E2 do buniek F2 a G2.

Keď je vzorec skopírovaný, aplikácia Excel aktualizuje relatívnu bunkovú referenciu vo funkcii COLUMN (B1) tak, aby odrážala nové umiestnenie vzorca.

Rovnako Excel nezmení absolútnu bunkovú referenciu $ D $ 2 a pomenovaný rozsah tabuľky ako vzorec je skopírovaný.

Existuje viac ako jeden spôsob, ako skopírovať údaje v programe Excel, ale pravdepodobne najjednoduchším spôsobom je použitie plniacej rukoväte .

Tutorial Kroky

1. Kliknite na bunku E2 - kde sa nachádza vyhľadávací vzorec - aby sa stala aktívnou bunkou
2. Umiestnite ukazovateľ myši na čierny štvorec v pravom dolnom rohu. Ukazovateľ sa zmení na znamienko plus " + " - toto je popisovač plnenia
3. Kliknite na ľavé tlačidlo myši a potiahnite držiak naplnenia do bunky G2
4. Uvoľnite tlačidlo myši a bunka F3 by mala obsahovať vzorec dvojrozmerného vyhľadávania
5. Ak sa to urobí správne, bunky F2 a G2 by teraz mali obsahovať aj chybu # N / A, ktorá je prítomná v bunke E2

10 z 10

Zadanie vyhľadávacích kritérií

Akonáhle bol vyhľadávací vzorec skopírovaný do požadovaných buniek , môže byť použitý na získanie informácií z tabuľky údajov.

Za týmto účelom zadajte názov položky, ktorú chcete prevziať do bunky Lookup_value (D2) a stlačte kláves ENTER na klávesnici.

Po dokončení každej bunky obsahujúcej vzorec vyhľadávania by mala obsahovať iný údaj o hardvérovej položke, ktorú hľadáte.

Tutorial Kroky

1. Kliknite na bunku D2 v pracovnom hárku
2. Napíšte Widget do bunky D2 a stlačte kláves ENTER na klávesnici
3. Nasledujúce informácie by sa mali zobraziť v bunkách E2 až G2:
   • E2 - 14,76 dolárov - cena miniaplikácie
   • F2 - PN-98769 - číslo dielu pre widget
   • G2 - Widgets Inc. - meno dodávateľa pre widgety
4. Otestujte vzorec poľa VLOOKUP ďalej zadaním názvu ostatných častí do bunky D2 a pozorovaním výsledkov v bunkách E2 až G2

Ak sa zobrazí chybové hlásenie ako #REF! sa zobrazí v bunkách E2, F2 alebo G2, tento zoznam chybových hlásení VLOOKUP vám môže pomôcť určiť, kde je problém.