Excel Dvojcestné vyhľadávanie pomocou VLOOKUP Časť 2

01 z 06

Spustenie funkcie VLOŽENÝ MATCH

Zadanie funkcie MATCH ako Argument indexu stĺpca. © Ted French

Vráťte sa do časti 1

Zadanie funkcie MATCH ako Argument indexu stĺpca

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, v ktorých chceme nájsť údaje, takže potrebujeme spôsob, ako ľahko zmeniť indexové číslo stĺpca bez úpravy nášho vzoru vyhľadávania.

Práve tu vstupuje do hry funkcia MATCH. Umožní nám to priradiť číslo stĺpca k názvu poľa - či už v januári, februári alebo marci -, ktoré zadáme do bunky E2 pracovného hárka.

Funkcie hniezdenia

Funkcia MATCH teda slúži ako argument stĺpca indexu čísel VLOOKUP.

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

Ručné zadávanie funkcie MATCH

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

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

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

Tutorial Kroky

Zadanie Argumentu Lookup_value funkcie MATCH

Prvým krokom pri vstupe do vnorenej funkcie MATCH je zadanie argumentu Lookup_value .

Hodnota Lookup_value bude umiestnenie alebo referencia bunky pre hľadaný výraz, ktorý chceme v databáze porovnať.

  1. V dialógovom okne funkcie VLOOKUP kliknite na riadok Col_index_num .
  2. Zadajte zhodu názvu funkcie, ktorú nasleduje otvorená kruhová konzola " ( "
  3. Kliknutím na bunku E2 zadáte odkaz do bunky do dialógového okna.
  4. Po vyplnení odkazu na bunku E3 zadajte čiarku "," a dokončite zadanie argumentu Lookup_value funkcie MATCH.
  5. Ponechajte dialógové okno funkcie VLOOKUP otvorené pre ďalší krok v príručke.

V poslednom kroku tutoriálu budú hodnoty Lookup_values ​​zadané do buniek D2 a E2 pracovného hárka .

02 z 06

Pridanie Lookup_array pre funkciu MATCH

Pridanie Lookup_array pre funkciu MATCH. © Ted French

Pridanie Lookup_array pre funkciu MATCH

Tento krok zahŕňa pridanie argumentu Lookup_array pre vnorenú funkciu MATCH.

Vyhľadávací_obrázok je rozsah buniek, ktoré funkcia MATCH vyhľadá, aby našla argument vyhľadávania, ktorý bol pridaný v predchádzajúcom kroku tutoriálu.

V tomto príklade chceme, aby funkcia MATCH vyhľadala bunky D5 až G5 pre zhodu s názvom mesiaca, ktorý bude vložený do bunky E2.

Tutorial Kroky

Tieto kroky sa zadajú po čiarke zadanej v predchádzajúcom kroku na riadok Col_index_num v dialógovom okne VLOOKUP.

  1. V prípade potreby kliknite na čiaru Col_index_num za čiarou a umiestnite kurzor na konci aktuálneho záznamu.
  2. Zvýraznite bunky D5 až G5 v pracovnom hárku a zadajte tieto odkazy na bunky ako rozsah, na ktorý sa má vyhľadávať.
  3. Stlačením klávesu F4 na klávesnici zmeníte tento rozsah na absolútne odkazy na bunky . Týmto umožníte skopírovať vyplnený vzorec vyhľadávania na iné miesta v pracovnom hárku v poslednom kroku príručky
  4. Zadajte čiarku "," za odkazom na bunku E3, aby ste vyplnili zadanie argumentu Lookup_array funkcie MATCH.

03 z 06

Pridanie typu zhody a dokončenie funkcie MATCH

Excel dvojcestný vyhľadávanie pomocou VLOOKUP. © Ted French

Pridanie typu zhody a dokončenie funkcie MATCH

Tretí a posledný argument funkcie MATCH je argument Match_type.

Tento argument informuje Excel o tom, ako porovnať hodnotu Lookup_value s hodnotami vo vyhľadávacom poli. Možnosti sú: -1, 0 alebo 1.

Tento argument je nepovinný. Ak sa vynechá, funkcia použije predvolenú hodnotu 1.

Tutorial Kroky

Tieto kroky sa zadajú po čiarke zadanej v predchádzajúcom kroku na riadok Row_num v dialógovom okne funkcie VLOOKUP.

  1. Po druhej čiarke na riadku Col_index_num zadajte nulu " 0 ", pretože chceme, aby vnorená funkcia vrátila presnú zhodu s mesiacom zadaním v bunke E2.
  2. Zadajte uzatváraciu kruhovú konzolu " ) ", aby ste dokončili funkciu MATCH.
  3. Ponechajte dialógové okno funkcie VLOOKUP otvorené pre ďalší krok v príručke.

04 z 06

Zadanie argumentu vyhľadávania rozsahu VLOOKUP

Zadanie argumentu vyhľadávania rozsahu. © Ted French

Argument vyhľadávania 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 Lookup_value.

V tomto návode, keďže hľadáme údaje o predaji za konkrétny mesiac, 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čite dvojrozmerný vzorec vyhľadávania a zatvorte dialógové okno
  4. Pretože sme doposiaľ nezadali vyhľadávacie kritériá do buniek D2 a E2, bude v bunke F2 prítomná chyba # N / A
  5. Táto chyba bude opravená v ďalšom kroku v príručke, keď pridáme vyhľadávacie kritériá v ďalšom kroku tutoriálu.

05 z 06

Testovanie vzoru obojsmerného vyhľadávania

Excel dvojcestný vyhľadávanie pomocou VLOOKUP. © Ted French

Testovanie vzoru obojsmerného vyhľadávania

Ak chcete použiť vzorec obojsmerného vyhľadávania a zistiť mesačné údaje o predaji pre rôzne súbory cookie uvedené v poli tabuľky, zadajte názov súboru cookie do bunky D2, mesiac do bunky E2 a stlačte kláves ENTER na klávesnici.

Údaje o predaji sa zobrazia v bunke F2.

Tutorial Kroky

  1. Kliknite na bunku D2 v pracovnom hárku
  2. Napíšte ovsenú kašu do bunky D2 a stlačte kláves ENTER na klávesnici
  3. Kliknite na bunku E2
  4. Zadajte február do bunky E2 a stlačte kláves ENTER na klávesnici
  5. Hodnota 1.345 dolárov - objem predaja cookies Oatmeal v mesiaci február - by mala byť zobrazená v bunke F2
  6. V tomto okamihu by váš pracovný hárok mal zodpovedať príkladu na strane 1 tohto tutoriálu
  7. Vyskúšajte vzorec vyhľadávania ďalej zadaním akejkoľvek kombinácie typov cookie a mesiacov prítomných v table_array a údaje o predaji by sa mali zobraziť v bunke F2
  8. Posledný krok v príručke zahŕňa kopírovanie vyhľadávacieho vzorca pomocou funkcie Vyplniť rukoväť .

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

06 z 06

Kopírovanie vzoru dvojrozmerného vyhľadávania s rukoväťou na plnenie

Excel dvojcestný vyhľadávanie pomocou VLOOKUP. © Ted French

Kopírovanie vzoru dvojrozmerného vyhľadávania s rukoväťou na plnenie

Ak chcete zjednodušiť porovnávanie údajov za rôzne mesiace alebo iné súbory cookie, vzorec vyhľadávania sa môže skopírovať do iných buniek, aby sa mohli súčasne zobraziť viaceré sumy.

Keďže údaje sú v pracovnom hárku usporiadané pravidelne, môžeme skopírovať vzorec vyhľadávania v bunke F2 do bunky F3.

Vzhľadom na to, že vzorec je skopírovaný, program Excel aktualizuje relatívne odkazy na bunky tak, aby odrážali nové umiestnenie formulára. V tomto prípade D2 sa stáva D3 a E2 sa stáva E3,

Rovnako Excel zachováva absolútnu bunkovú referenciu rovnakú, takže absolútny rozsah $ D $ 5: $ G $ 5 zostáva rovnaký pri skopírovaní vzorca.

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 D3 v pracovnom hárku
  2. Napíšte ovsené vločke do bunky D3 a stlačte kláves ENTER na klávesnici
  3. Kliknite na bunku E3
  4. Do poľa E3 napíšte March a stlačte kláves ENTER na klávesnici
  5. Kliknutím na bunku F2 ju vytvoríte ako aktívnu bunku
  6. Umiestnite ukazovateľ myši na čierny štvorec v pravom dolnom rohu. Ukazovateľ sa zmení na znamienko plus "+" - toto je rukoväť na vyplnenie
  7. Kliknite na ľavé tlačidlo myši a presuňte rukoväť výplne smerom dole na bunku F3
  8. Uvoľnite tlačidlo myši a bunka F3 by mala obsahovať vzorec dvojrozmerného vyhľadávania
  9. Hodnota 1,287 dolárov - objem predaja cookies Oatmeal v mesiaci marec - by sa mal zobraziť v bunke F3