Kombináciou funkcie VLOOKUP programu Excel s funkciou COLUMN môžete vytvoriť vzorec vyhľadávania, ktorý vráti viacero hodnôt z jedného riadku databázy alebo tabuľky údajov. Zistite, ako vytvoriť vyhľadávací vzorec, ktorý vráti viacero hodnôt z jedného záznamu údajov.
Pokyny v tomto článku platia pre Excel 2019, 2016, 2013, 2010; a Excel pre Microsoft 365.
Zrátané podčiarknutie
Vyhľadávací vzorec vyžaduje, aby bola funkcia COLUMN vnorená do funkcie VLOOKUP. Vnorenie funkcie zahŕňa zadanie druhej funkcie ako jedného z argumentov pre prvú funkciu.
Zadajte údaje príručky
V tomto návode je funkcia COLUMN zadaná ako argument čísla indexu stĺpca pre funkciu VLOOKUP. Posledný krok v návode zahŕňa skopírovanie vyhľadávacieho vzorca do ďalších stĺpcov, aby ste získali ďalšie hodnoty pre vybratú časť.
Prvým krokom v tomto návode je zadanie údajov do pracovného hárka programu Excel. Ak chcete postupovať podľa krokov v tomto návode, zadajte údaje zobrazené na obrázku nižšie do nasledujúcich buniek:
- Zadajte horný rozsah údajov do buniek D1 až G1.
- Zadajte druhý rozsah do buniek D4 až G10.
Kritériá vyhľadávania a vyhľadávací vzorec vytvorený v tomto návode sú zadané v riadku 2 pracovného hárka.
Tento tutoriál neobsahuje základné formátovanie Excelu zobrazené na obrázku, ale to nemá vplyv na fungovanie vyhľadávacieho vzorca.
Vytvorte pre tabuľku údajov pomenovaný rozsah
Pomenovaný rozsah predstavuje jednoduchý spôsob odkazovania na rozsah údajov vo vzorci. Namiesto zadávania odkazov na bunky pre údaje zadajte názov rozsahu.
Druhou výhodou použitia pomenovaného rozsahu je, že odkazy na bunky pre tento rozsah sa nikdy nemenia, aj keď sa vzorec skopíruje do iných buniek v pracovnom hárku. Názvy rozsahov sú alternatívou k používaniu absolútnych odkazov na bunky, aby sa predišlo chybám pri kopírovaní vzorcov.
Názov rozsahu nezahŕňa nadpisy ani názvy polí pre údaje (ako je uvedené v riadku 4), iba údaje.
-
Zvýraznite bunky D5 až G10 v pracovnom hárku.
Image -
Umiestnite kurzor do poľa Názov nad stĺpcom A, napíšte Tabuľka a stlačte Enter. Bunky D5 až G10 majú názov rozsahu Tabuľka.
Image - Názov rozsahu pre argument poľa tabuľky VLOOKUP sa použije neskôr v tomto návode.
Otvoriť dialógové okno VLOOKUP
Hoci je možné napísať vyhľadávací vzorec priamo do bunky v pracovnom hárku, pre mnohých ľudí je ťažké zachovať správnu syntax – najmä v prípade zložitého vzorca, ako je ten, ktorý je použitý v tomto návode.
Ako alternatívu použite dialógové okno Argumenty funkcie VLOOKUP. Takmer všetky funkcie Excelu majú dialógové okno, kde je každý z argumentov funkcie zadaný na samostatnom riadku.
-
Vyberte bunku E2 z pracovného hárka. Toto je miesto, kde sa zobrazia výsledky dvojrozmerného vyhľadávacieho vzorca.
Image -
Na páse s nástrojmi prejdite na kartu Formulas a vyberte Lookup & Reference.
Image -
Výberom VLOOKUP otvoríte dialógové okno Argumenty funkcie.
Image - V dialógovom okne Argumenty funkcie sa zadávajú parametre funkcie VLOOKUP.
Zadajte argument hodnoty vyhľadávania
Za normálnych okolností sa vyhľadávaná hodnota zhoduje s poľom údajov v prvom stĺpci tabuľky údajov. V tomto príklade sa vyhľadávacia hodnota vzťahuje na názov časti, o ktorej chcete nájsť informácie. Povolené typy údajov pre vyhľadávaciu hodnotu sú textové údaje, logické hodnoty, čísla a odkazy na bunky.
Absolútne referencie buniek
Pri kopírovaní vzorcov v Exceli sa odkazy na bunky zmenia tak, aby odrážali nové umiestnenie. Ak k tomu dôjde, D2, odkaz na bunku pre vyhľadávaciu hodnotu, sa zmení a vytvorí chyby v bunkách F2 a G2.
Absolútne odkazy na bunky sa pri kopírovaní vzorcov nemenia.
Ak chcete predísť chybám, konvertujte odkaz na bunku D2 na absolútny odkaz na bunku. Ak chcete vytvoriť absolútny odkaz na bunku, stlačte kláves F4. Toto pridá znaky dolára okolo odkazu na bunku, napríklad $D$2.
-
V dialógovom okne Argumenty funkcie umiestnite kurzor do textového poľa lookup_value. Potom v pracovnom hárku vyberte cell D2 a pridajte tento odkaz na bunku do lookup_value. Bunka D2 je miesto, kde bude zadaný názov dielu.
Image -
Bez presúvania kurzora stlačte kláves F4 na konverziu D2 na absolútnu referenciu bunky $D$2.
Image - Ponechajte dialógové okno funkcie VLOOKUP otvorené pre ďalší krok v návode.
Zadajte argument poľa tabuľky
Pole tabuľky je tabuľka údajov, ktorú vyhľadávací vzorec hľadá, aby našiel požadované informácie. Pole tabuľky musí obsahovať aspoň dva stĺpce údajov.
Prvý stĺpec obsahuje argument vyhľadávacej hodnoty (ktorý bol nastavený v predchádzajúcej časti), zatiaľ čo druhý stĺpec sa vyhľadáva pomocou vyhľadávacieho vzorca, aby sa našli informácie, ktoré zadáte.
Argument poľa tabuľky musí byť zadaný buď ako rozsah obsahujúci odkazy na bunky pre tabuľku údajov, alebo ako názov rozsahu.
Ak chcete pridať tabuľku údajov do funkcie VLOOKUP, umiestnite kurzor do textového poľa table_array v dialógovom okne a zadajte Tablena zadanie názvu rozsahu pre tento argument.
Vnorenie funkcie COLUMN
VLOOKUP normálne vracia údaje iba z jedného stĺpca tabuľky údajov. Tento stĺpec je nastavený argumentom čísla indexu stĺpca. V tomto príklade sú však tri stĺpce a indexové číslo stĺpca je potrebné zmeniť bez úpravy vyhľadávacieho vzorca. Aby ste to dosiahli, vnorte funkciu COLUMN do funkcie VLOOKUP ako argument Col_index_num.
Pri vnorení funkcií Excel neotvorí dialógové okno druhej funkcie na zadanie jej argumentov. Funkciu STĹPCA je potrebné zadať manuálne. Funkcia COLUMN má iba jeden argument, argument Reference, ktorý je odkazom na bunku.
Funkcia COLUMN vracia číslo stĺpca poskytnutého ako argument Reference. Skonvertuje písmeno stĺpca na číslo.
Ak chcete zistiť cenu položky, použite údaje v stĺpci 2 tabuľky s údajmi. Tento príklad používa stĺpec B ako referenciu na vloženie 2 do argumentu Col_index_num.
-
V dialógovom okne Argumenty funkcie umiestnite kurzor do textového poľa Col_index_num a zadajte COLUMN(. (Nezabudnite zahrnúť otvorenú okrúhlu zátvorku.)
Image -
V pracovnom hárku vyberte bunku B1 a zadajte odkaz na túto bunku ako argument referencie.
Image - Ak chcete dokončiť funkciu STĹPCA, zadajte uzavieraciu okrúhlu zátvorku.
Zadajte argument vyhľadávania rozsahu VLOOKUP
Argument Range_lookup nástroja VLOOKUP je logická hodnota (TRUE alebo FALSE), ktorá označuje, či má funkcia VLOOKUP nájsť presnú alebo približnú zhodu s hodnotou Lookup_value.
- TRUE alebo Vynechané: Funkcia VLOOKUP vráti blízku zhodu s hodnotou Lookup_value. Ak sa nenájde presná zhoda, funkcia VLOOKUP vráti ďalšiu najväčšiu hodnotu. Údaje v prvom stĺpci tabuľky Table_array musia byť zoradené vzostupne.
- FALSE: VLOOKUP používa presnú zhodu s hodnotou Lookup_value. Ak sa v prvom stĺpci tabuľky Table_array nachádzajú dve alebo viac hodnôt, ktoré zodpovedajú hodnote 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 budú vyhľadané špecifické informácie o konkrétnej hardvérovej položke, takže Range_lookup je nastavený na FALSE.
V dialógovom okne Argumenty funkcie umiestnite kurzor do textového poľa Range_lookup a napíšte False, čím povedzte funkcii VLOOKUP, aby vrátila presnú zhodu pre údaje.
Vyberte OK na dokončenie vyhľadávacieho vzorca a zatvorenie dialógového okna. Bunka E2 bude obsahovať chybu N/A, pretože kritériá vyhľadávania neboli zadané do bunky D2. Táto chyba je dočasná. Opraví sa, keď sa v poslednom kroku tohto návodu pridajú kritériá vyhľadávania.
Skopírujte vyhľadávací vzorec a zadajte kritériá
Vyhľadávací vzorec načítava údaje z viacerých stĺpcov tabuľky údajov naraz. Ak to chcete urobiť, vyhľadávací vzorec sa musí nachádzať vo všetkých poliach, z ktorých chcete informácie.
Ak chcete získať údaje zo stĺpcov 2, 3 a 4 tabuľky údajov (cena, číslo dielu a názov dodávateľa), zadajte čiastočný názov ako Lookup_value.
Keďže sú údaje v pracovnom hárku usporiadané v pravidelnom vzore, skopírujte vyhľadávací vzorec do bunky E2 do buniek F2 a G2 Pri kopírovaní vzorca Excel aktualizuje relatívny odkaz na bunku vo funkcii COLUMN (bunka B1), aby odrážal nové umiestnenie vzorca. Excel pri kopírovaní vzorca nemení absolútny odkaz na bunku (napríklad $D$2) a pomenovaný rozsah (tabuľka).
Existuje viac ako jeden spôsob kopírovania údajov v Exceli, ale najjednoduchším spôsobom je použiť rukoväť Fill Handle.
-
Vyberte bunku E2, kde sa nachádza vyhľadávací vzorec, aby sa stala aktívnou bunkou.
Image -
Presuňte rukoväť výplne na cell G2. Bunky F2 a G2 zobrazujú chybu N/A, ktorá sa nachádza v bunke E2.
Image -
Ak chcete použiť vyhľadávacie vzorce na získanie informácií z tabuľky údajov, v pracovnom hárku vyberte bunka D2, napíšte Widget a stlačte Enter.
Image Nasledujúca informácia sa zobrazuje v bunkách E2 až G2.
- E2: 14,76 $ – cena miniaplikácie
- F2: PN-98769 – číslo dielu pre miniaplikáciu
- G2: Widgets Inc. – názov dodávateľa miniaplikácií
-
Ak chcete otestovať vzorec poľa VLOOKUP, zadajte názov ostatných častí do bunky D2 a sledujte výsledky v bunkách E2 až G2.
Image - Každá bunka obsahujúca vyhľadávací vzorec obsahuje inú časť údajov o hardvérovej položke, ktorú ste hľadali.
Funkcia VLOOKUP s vnorenými funkciami, ako je COLUMN, poskytuje výkonnú metódu na vyhľadávanie údajov v tabuľke pomocou iných údajov ako referenčných údajov.
