Funkcia VLOOKUP programu Excel sa používa na vyhľadanie a vrátenie informácií z tabuľky údajov na základe vyhľadávanej hodnoty, ktorú si vyberiete.
VLOOKUP typicky vyžaduje, aby hodnota vyhľadávania bola v ľavom stĺpci tabuľky údajov a funkcia vráti ďalšie pole údajov umiestnené v tom istom riadku napravo od tejto hodnoty.
Avšak kombináciou funkcie VLOOKUP s funkciou CHOOSE sa môže vytvoriť vzorec ľavého vyhľadávania, ktorý:
- Umožňuje vyhľadávaciu hodnotu z ľubovoľného stĺpca v tabuľke s údajmi
- Vracia informácie umiestnené v ľubovoľnom stĺpci naľavo od hodnoty vyhľadávania
Poznámka: Tieto pokyny sa vzťahujú na verzie programu Excel 2019, 2016, 2013, 2010 a Excel pre balík Office 365.
01 z 05Výučba: Použitie funkcií VLOOKUP a CHOOSE vo formulári Left Lookup Formula
Ak chcete vytvoriť vzorec ľavého vyhľadávania, ktorý je videný na obrázku príkladu, použite vzorec:
= VLOOKUP ($ D $ 2 zvoliť ({1,2}, $ F: $ F $ D: $ D), 2, FALSE)
V tomto príklade vzorec umožňuje nájsť časť dodávanú rôznymi spoločnosťami uvedenými v stĺpci 3 tabuľky údajov.
Úlohou funkcie CHOOSE vo vzorci je trik VLOOKUP, aby veril, že stĺpec 3 je stĺpec 1. V dôsledku toho môže byť názov spoločnosti použitý ako vyhľadávacia hodnota na nájdenie názvu časti dodanej každou spoločnosťou.
Zadajte výučbové údaje
- Zadajte nadpis dodávateľ do bunky D1.
- Zadajte nadpis časť v bunke E1.
- Zadajte do buniek tabuľku údajov zobrazených na obrázku vyššie D4 až F9
- riadky 2 a 3 sú prázdne, aby spĺňali kritériá vyhľadávania a vzorec ľavého vyhľadávania vytvorený počas tohto kurzu
Otvorte dialógové okno VLOOKUP
Aj keď je možné formulár napísať priamo do bunky F1 v pracovnom hárku, veľa ľudí má problémy so syntaxou vzorca.
V tomto prípade je bezpečnejšie 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.
- Kliknite na bunku E2 pracovného hárka. E2 je miesto, kde sa zobrazia výsledky vyhľadávania vľavo.
- Klikni na vzorca kartu pásky.
- Klikni na Vyhľadávanie a referencia v páse k otvoreniu rolovacieho zoznamu funkcií.
- Kliknite na VLOOKUP v dialógovom okne funkcie otvorte dialógové okno funkcie.
Zadávanie argumentov do dialógového okna VLOOKUP
Argumenty funkcie sú hodnoty používané funkciou na výpočet výsledku.
V dialógovom okne funkcie je názov každého argumentu na samostatnom riadku, po ktorom nasleduje pole, do ktorého sa má zadávať hodnota.
Zadajte nasledujúce hodnoty pre každý z argumentov VLOOKUP na správny riadok dialógového okna, ako je uvedené na priloženom obrázku.
Hodnota vyhľadávania
Hodnota vyhľadávania je oblasť informácií, ktorá sa používa na vyhľadávanie tabuľkového poľa. VLOOKUP vráti ďalšie pole údajov z rovnakého riadku ako vyhľadávacia hodnota.
Tento príklad používa referenciu bunky na miesto, kde sa do pracovného hárka zadá názov spoločnosti. Výhodou je, že umožňuje ľahko zmeniť názov spoločnosti bez úpravy vzorca.
- Klikni na lookup_value line v dialógovom okne.
- Kliknite na bunku D2 pridať tento odkaz na bunku lookup_value linka.
- Stlačte tlačidlo F4 klávesu na klávesnici, aby bola bunková referencia absolútna - $ D $ 2.
Absolútne odkazy na bunky
Absolútne odkazy na bunky sa používajú pre hodnoty vyhľadávania a argumenty tabuľkového poľa, aby sa zabránilo chybám, ak je vzorec vyhľadávania skopírovaný do iných buniek v pracovnom hárku.
Zadanie funkcie CHOOSE
Argument tabuľkového poľa je blok súvisiacich údajov, z ktorých sa získavajú konkrétne informácie.
VLOOKUP zvyčajne vyzerá napravo od argumentu hodnoty vyhľadávania, aby našiel údaje v tabuľkovom poli. Aby ste to mohli vidieť doľava, VLOOKUP musí byť napísaný preskupením stĺpcov v tabuľkovom poli pomocou funkcie CHOOSE.
V tomto vzore uskutočňuje funkcia CHOOSE dve úlohy:
- Vytvorí tabuľkový pohľad, ktorý je len dva stĺpce široký (stĺpce D a F).
- Zmeňuje poradie stĺpcov v tabuľkovom poli vpravo doľava, takže stĺpec F prichádza prvý a stĺpec D je druhý.
Zadávanie funkcií
Pri zadávaní funkcií ručne musí byť každá argumentácia funkcie oddelená čiarkou.
- V dialógovom okne funkcie VLOOKUP kliknite na table_array linka.
- Zadaj nasledujúce VYBERTE funkcie:Zvoliť ({1,2}, $ F: $ F $ D: $ D)
Číslo indexu stĺpca
Zvyčajne indexové číslo stĺpca označuje, ktorý stĺpec tabuľkového poľa obsahuje údaje, ktoré nasleduje. V tomto vzore však odkazuje na poradie stĺpcov nastavených funkciou CHOOSE.
Funkcia CHOOSE vytvára tabuľkový pohľad, ktorý je dva stĺpce široký so stĺpcom F, po ktorom nasleduje prvý stĺpec D. Keďže požadovaná informácia - názov časti - je v stĺpci D, hodnota argumentu indexu stĺpca musí byť nastavená na hodnotu 2.
- Klikni na Col_index_num line v dialógovom okne.
- Zadajte a 2 v tomto riadku.
Hľadanie rozsahu
Argument Range_lookup VLOOKUP je logická hodnota (TRUE alebo FALSE), ktorá označuje, či chcete VLOOKUP nájsť presnú alebo približnú zhodu s hodnotou vyhľadávania.
- Ak je TRUE alebo ak je tento argument vynechaný, VLOOKUP vráti buď presnú zhodu s hodnotou Lookup_value, alebo ak sa nenašla presná zhoda, 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 používa iba presnú zhodu s hodnotou 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, pretože hľadáme konkrétny názov, Range_lookup je nastavený na nepravdivý takže iba vzorky sú vrátené vzorcom.
- Klikni na argument typ line v dialógovom okne.
- Zadajte slovo nepravdivý v tomto riadku, aby sme naznačili, že chceme, aby VLOOKUP vrátil presnú zhodu údajov, ktoré hľadáme.
- kliknite OK aby ste vyplnili vzorec ľavého vyhľadávania a zatvorte dialógové okno.
- Pretože sme ešte nezadali názov spoločnosti do bunky D2, v bunke E2 sa zobrazí chyba # N / A.
Vracanie údajov pomocou vzorca vyhľadávania vľavo
Ak chcete zistiť, ktoré spoločnosti dodávajú časti, zadajte názov spoločnosti do bunky D2 a stlačte tlačidlo ENTER na klávesnici.
Názov časti sa zobrazí v bunke E2.
- Kliknite na bunku D2 na pracovnom hárku.
- typ Gadgets Plus do bunky D2 a stlačte tlačidlo ENTER na klávesnici.
- Text "Zábavné doplnky" - časť dodávaná spoločnosťou Gadgets Plus - by sa mala zobraziť v bunke E2.
Vyskúšajte vzorec vyhľadávania ďalším zadaním ďalších názvov spoločností do bunky D2 a príslušný názov položky by sa mal objaviť v bunke E2.
Ak sa v bunke E2 zobrazí chybové hlásenie ako # N / A, skontrolujte pravopisné chyby v bunke D2.
04 z 05Vytvorenie tabuľkového poľa s dvoma stĺpcami
Syntax pre funkciu CHOOSE je:
= CHOOSE (Index_number, Value1, Value2, … Value254)
Funkcia CHOOSE zvyčajne vracia jednu hodnotu zo zoznamu hodnôt (Value1 na hodnotu254) na základe zadaného indexového čísla.
Ak je indexové číslo 1, funkcia vracia hodnotu 1 zo zoznamu; ak je indexové číslo 2, funkcia vracia hodnotu 2 zo zoznamu atď.
Pri zadávaní viacerých indexových čísel funkcia vracia viaceré hodnoty v ľubovoľnom požadovanom poradí. Získanie CHOOSE na vrátenie viacerých hodnôt sa uskutoční vytvorením poľa.
Ak chcete zadať pole, obklopte zadané čísla pomocou kučeravých zátvoriek alebo zátvoriek. Pre indexové číslo sú zadané dve čísla:{ 1,2 }.
Treba poznamenať, že CHOOSE sa neobmedzuje na vytvorenie tabuľky s dvoma stĺpcami. Zahrnutím dodatočného čísla do poľa - napríklad {1,2,3} - a ďalším rozsahom v argumentu hodnoty, CHOOSE vytvorí tabuľku s tromi stĺpcami.
Ďalšie stĺpce vám umožňujú vrátiť rôzne informácie pomocou vzorca ľavého vyhľadávania zmenou argumentu čísla stĺpca VLOOKUP na číslo stĺpca obsahujúce požadované informácie.
05 z 05Zmena poradia stĺpcov pomocou funkcie CHOOSE
Vo funkcii CHOOSE použité v tomto vzorci:
Zvoliť ({1,2}, $ F: $ F $ D: $ D)
rozsah pre stĺpec F je uvedený pred stĺpcom D.
Pretože funkcia CHOOSE nastavuje pole tabuľky VLOOKUP (zdroj údajov pre túto funkciu), prepína poradie stĺpcov vo funkcii CHOOSE a prechádza pozdĺž VLOOKUP.
Pokiaľ ide o VLOOKUP, pole tabuľky má iba dva stĺpce so stĺpcom F vľavo a stĺpcom D vpravo. Keďže stĺpec F obsahuje názov spoločnosti, ktorú chceme vyhľadávať, a pretože stĺpec D obsahuje názvy častí, VLOOKUP môže pri hľadaní údajov, ktoré sa nachádzajú naľavo od vyhľadávacej hodnoty, vykonávať svoje pravidelné vyhľadávacie funkcie.
Výsledkom je, že spoločnosť VLOOKUP môže používať názov spoločnosti na nájdenie časti, ktorú dodáva.
