Vľavo vyhľadávacia formulár programu Excel pomocou funkcie VLOOKUP

01 z 03

Vyhľadajte údaje vľavo

Prehľad formuly vyhľadávania v ľavom okne aplikácie Excel

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.

Normálne VLOOKUP 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 rovnakom riadku napravo od tejto hodnoty.

Kombináciou VLOOKUP s funkciou CHOOSE ; Môže sa však vytvoriť vzorec pre vyhľadávanie vľavo, ktorý:

• umožňuje vyhľadanie hodnoty vyhľadávania z ľubovoľného stĺpca v tabuľke s údajmi
• vrátiť informácie umiestnené v ľubovoľnom stĺpci naľavo od hodnoty vyhľadávania

Príklad: Používanie funkcií VLOOKUP a CHOOSE v ľavom vyhľadávacom vzorci

Nižšie uvedené kroky vytvárajú vzorec ľavého vyhľadávania videný na obrázku vyššie.

Vzorec

= VLOOKUP ($ D $ 2 zvoliť ({1,2}, $ F: $ F $ D: $ D), 2, FALSE)

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 vzore je trik VLOOKUP, aby veril, že stĺpec 3 je vlastne 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.

Tutorial Steps - Zadanie údajov výučby

1. Do uvedených buniek zadajte nasledujúce položky: D1 - Dodávateľ E1 - Časť
2. Zadajte tabuľku údajov zobrazených na obrázku vyššie do buniek D4 až F9
3. Riadky 2 a 3 zostávajú prázdne, aby vyhovovali kritériám vyhľadávania a vzoru ľavého vyhľadávania, ktorý bol vytvorený počas tohto kurzu

Spustenie formulára ľavého vyhľadávania - Otvorenie dialógového okna VLOOKUP

Hoci je možné jednoducho napísať vyššie uvedený vzorec priamo do bunky F1 v pracovnom hárku, veľa ľudí má problémy so syntaxou vzorca.

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 ľavé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 vyvoláte dialógové okno funkcie

02 z 03

Zadávanie argumentov do dialógového okna VLOOKUP - Kliknutím zobrazíte väčší obrázok

Argumenty spoločnosti 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 umiestnený na samostatnom riadku, za ktorým 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 znázornené na obrázku vyššie.

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.

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
3. Stlačením klávesu F4 na klávesnici vykonáte referenciu bunky absolútne - $ D $ 2

Poznámka: Absolútne bunkové odkazy 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.

Tabuľkový pohľad: Zadanie funkcie CHOOSE

Argument tabuľkového poľa je blok súvisiacich údajov, z ktorých sa získavajú konkrétne informácie.

Za normálnych okolností VLOOKUP hľadá iba vpravo 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:

1. vytvára tabuľkový pohľad, ktorý má iba dva stĺpce - stĺpce D a F
2. zmení sa v ľavom poradí stĺpcov v tabuľkovom poli tak, že stĺpec F prichádza prvý a stĺpec D je druhý

Podrobnosti o tom, ako funkcia CHOOSE dosiahne tieto úlohy, nájdete na strane 3 v príručke .

Tutorial Kroky

Poznámka: Pri zadávaní funkcií ručne musí byť každá argumentácia funkcie oddelená čiarkou "," .

1. V dialógovom okne funkcie VLOOKUP kliknite na riadok Table_array
2. Zadajte nasledujúcu funkciu CHOOSE
3. 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 vzorci; odkazuje však 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 najprv nasledovaný stĺpcom 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.

Tutorial Kroky

1. Kliknite na riadok Col_index_num v dialógovom okne
2. Do tohto riadku zadajte znak 2

Rozsah vyhľadávania

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 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, pretože hľadáme konkrétny názov, Range_lookup bude nastavený na hodnotu False, takže vzorci vrátia iba presné výsledky.

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čite dialógové okno pre vzorec ľavého vyhľadávania a zatvorte ho
4. Keďže sme ešte nezadali názov firmy do bunky D2, v bunke E2 by mala byť prítomná chyba # N / A

03 z 03

Testovanie vzorca na vyhľadávanie vľavo

Vrátenie údajov pomocou vzorca na vyhľadávanie vľavo

Ak chcete zistiť, ktoré spoločnosti dodávajú časti, zadajte názov spoločnosti do bunky D2 a stlačte kláves ENTER na klávesnici.

Názov časti sa zobrazí v bunke E2.

Tutorial Kroky

1. Kliknite na bunku D2 v pracovnom hárku
2. Napíšte Gadgets Plus do bunky D2 a stlačte kláves ENTER na klávesnici
3. Text Gadgets - časť dodávaná spoločnosťou Gadgets Plus - by mala byť zobrazená v bunke E2
4. 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 časti by sa mal objaviť v bunke E2

VLOOKUP Chybové hlásenia

Ak sa v bunke E2 objaví chybové hlásenie, ako napríklad # N / A , najprv skontrolujte chyby pravopisu v bunke D2.

Ak pravopis nie je problém, tento zoznam chybových hlásení VLOOKUP vám môže pomôcť určiť, kde je problém.

Zlomenie úlohy funkcie CHOOSE

Ako bolo uvedené, v tomto vzore má funkcia CHOOSE dve úlohy:

• vytvára tabuľkový pohľad, ktorý má iba dva stĺpce - stĺpce D a F
• zmení sa v ľavom poradí stĺpcov v tabuľkovom poli tak, že stĺpec F prichádza prvý a stĺpec D je druhý

Vytvorenie tabuľkového poľa s dvoma stĺpcami

Syntax pre funkciu CHOOSE je:

= CHOOSE (Index_number, Value1, Value2, ... Value254)

Funkcia CHOOSE normálne 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ď.

Zadaním viacerých indexových čísel; funkcia však vráti 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 .

Zadanie poľa sa uskutoční obklopením čísel zadaných s kučeravými zátvorkami alebo závorami. Pre číslo indexu sú zadané dve čísla: {1,2} .

Treba poznamenať, že CHOOSE sa neobmedzuje na vytvorenie tabuľky s dvomi stĺpcami. Zahrnutím dodatočného čísla do poľa - napríklad {1,2,3} - a ďalším rozsahom v argumentu hodnoty možno vytvoriť tabuľku s tromi stĺpcami.

Ďalšie stĺpce vám umožnia vrátiť rôzne informácie pomocou vzorca ľavého vyhľadávania jednoducho zmenou argumentu čísla stĺpca VLOOKUP na číslo stĺpca obsahujúceho požadované informácie.

Zmena poradia stĺpcov s funkciou CHOOSE

Vo funkcii CHOOSE použitom v tomto vzorci: CHOOSE ({1,2}, $ F: $ F, $ D: $ D) , rozsah pre stĺpec F je uvedený pred stĺpcom D.

Keďže funkcia CHOOSE nastavuje tabuľku VLOOKUP - zdroj dát pre túto funkciu - prepínanie poradia stĺpcov vo funkcii CHOOSE sa prenáša na VLOOKUP.

Pokiaľ ide o VLOOKUP, pole tabuľky má len dva stĺpce so stĺpcom F vľavo a stĺpec D vpravo. Keďže stĺpec F obsahuje názov spoločnosti, ktorú chceme vyhľadať, a pretože stĺpec D obsahuje názvy častí, VLOOKUP bude môcť vykonávať svoje bežné vyhľadávacie povinnosti pri vyhľadávaní údajov, ktoré sa nachádzajú naľavo od hodnoty vyhľadávania.

Výsledkom je, že spoločnosť VLOOKUP môže používať názov spoločnosti a nájsť časť, ktorú dodáva.