Excel

Funkcia Excel VLOOKUP

Excel Vlookup Function

Funkcia Excel VLOOKUPZhrnutie

VLOOKUP je funkcia programu Excel na vyhľadávanie údajov v tabuľke usporiadanej zvisle. VLOOKUP podporuje približné a presné párovanie a zástupné znaky (*?) pre čiastkové zápasy. Vyhľadávacie hodnoty sa musia objaviť v súbore najprv stĺpec tabuľky prešiel do VLOOKUP.



Účel Vyhľadať hodnotu v tabuľke zhodou v prvom stĺpci Návratová hodnota Zodpovedajúca hodnota z tabuľky. Syntax = VLOOKUP (hodnota, tabuľka, stĺpcový index, [prehľad_rozsahu]) Argumenty
  • hodnotu - hodnota, ktorú treba hľadať v prvom stĺpci tabuľky.
  • stôl - Tabuľka, z ktorej sa má získať hodnota.
  • col_index - Stĺpec v tabuľke, z ktorého sa má načítať hodnota.
  • range_lookup - [voliteľné] PRAVDA = približná zhoda (predvolené). FALSE = presná zhoda.
Verzia Excel 2003 Poznámky k použitiu

VLOOKUP je funkcia programu Excel na získavanie údajov z tabuľky usporiadanej zvisle. Vyhľadávacie hodnoty sa musia objaviť v súbore najprv stĺpec tabuľky prešiel do VLOOKUP. VLOOKUP podporuje približné a presné párovanie a zástupné znaky (*?) pre čiastkové zápasy.

Vertikálne údaje | Čísla stĺpcov | Len vyzerá správne | Zhodné režimy | Presná zhoda | Približná zhoda | Prvý zápas | Zástupný znak | Obojsmerné vyhľadávanie | Viacnásobné kritériá | #N/A Chyby | Videá





V je pre zvislé

Účelom VLOOKUP je získať informácie z tabuľky usporiadanej takto:

VLOOKUP je pre vertikálne údaje



Použitím čísla objednávky v stĺpci B ako vyhľadávacej hodnoty môže VLOOKUP získať číslo zákazníka, sumu, meno a štát pre každú objednávku. Ak napríklad chcete získať meno zákazníka pre objednávku 1004, vzorec je:

 
= VLOOKUP (1004,B5:F9,4,FALSE) // returns 'Sue Martin'

Pre horizontálne údaje môžete použiť HLOOKUP , INDEX a ZHODA , alebo XLOOKUP .

najčastejšou chybou pri zadávaní vzorca je odkaz na nesprávnu bunku vo vzorci

VLOOKUP je založený na číslach stĺpcov

Keď používate VLOOKUP, predstavte si, že každý stĺpec v stôl je očíslované, začínajúc zľava. Ak chcete získať hodnotu z konkrétneho stĺpca, zadajte príslušné číslo ako „index stĺpca“. Index stĺpca na získanie nižšie uvedeného krstného mena je napríklad 2:

Príklad presnej zhody VLOOKUP

Priezvisko a e -mail je možné získať v stĺpcoch 3 a 4:

 
= VLOOKUP (H3,B4:E13,2,FALSE) // first name = VLOOKUP (H3,B4:E13,3,FALSE) // last name = VLOOKUP (H3,B4:E13,4,FALSE) // email address

VLOOKUP vyzerá iba správne

VLOOKUP sa môže pozerať iba doprava. Údaje, ktoré chcete získať (výsledné hodnoty), sa môžu zobraziť v ľubovoľnom stĺpci doprava z vyhľadávacích hodnôt:

VLOOKUP sa môže pozerať iba doprava

Ak potrebujete vyhľadať hodnoty vľavo, pozrite si časť INDEX a ZHODA , alebo XLOOKUP .

Presná a približná zhoda

VLOOKUP má dva režimy zhody, presný a približný. Názov argumentu, ktorý riadi zhodu, je „ range_lookup '. Je to mätúce meno, pretože sa zdá, že to má niečo do činenia rozsahy buniek ako A1: A10. Slovo „rozsah“ v tomto prípade skutočne znamená „rozsah hodnôt“ - kedy range_lookup je PRAVDA, VLOOKUP bude zodpovedať a rozsah hodnôt než presnú hodnotu. Dobrým príkladom je použitie VLOOKUP na výpočet známok .

Je dôležité tomu porozumieť range_lookup predvolene je TRUE , čo znamená, že VLOOKUP bude predvolene používať približnú zhodu, čo môže byť nebezpečné . Nastaviť range_lookup na FALSE, aby sa vynútila presná zhoda:

 
= VLOOKUP (value, table, col_index) // approximate match (default) = VLOOKUP (value, table, col_index, TRUE) // approximate match = VLOOKUP (value, table, col_index, FALSE) // exact match

Poznámka: Môžete tiež zadať nulu (0) namiesto FALSE pre presnú zhodu.

Presná zhoda

Vo väčšine prípadov budete pravdepodobne chcieť použiť VLOOKUP v režime presnej zhody. To dáva zmysel, keď máte jedinečný kľúč na použitie ako hodnotu vyhľadávania, napríklad názov filmu v týchto údajoch:

VLOOKUP presná zhoda s filmami

Vzorec v H6 nájsť Rok , na základe presnej zhody názvu filmu, je:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Približná zhoda

V prípadoch, keď chcete Najlepší zápas , nie nevyhnutne presná zhoda , budete chcieť použiť približný režim. Napríklad nižšie chceme vyhľadať sadzbu provízie v tabuľke G5: H10. Hodnoty vyhľadávania pochádzajú zo stĺpca C. V tomto prípade musíme použiť príkaz VLOOKUP v približná zhoda režim, pretože vo väčšine prípadov sa nikdy nenájde presná zhoda. Vzorec VLOOKUP v D5 je nakonfigurovaný na vykonanie približnej zhody nastavením posledného argumentu na hodnotu TRUE:

VLOOKUP približná sadzba provízie za zápas

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

VLOOKUP naskenuje hodnoty v stĺpci G na vyhľadanú hodnotu. Ak sa nájde presná zhoda, VLOOKUP ho použije. Ak nie, VLOOKUP „ustúpi“ a bude zodpovedať predchádzajúcemu riadku.

Poznámka: Keď používate režim približnej zhody s VLOOKUP, údaje je potrebné zoradiť vzostupne podľa hodnoty vyhľadávania.

Prvý zápas

V prípade duplicitných hodnôt VLOOKUP nájde príponu prvý zápas keď je režim zhody presný. Na nasledujúcej obrazovke je VLOOKUP nakonfigurovaný tak, aby našiel cenu za farbu „zelenú“. Existujú tri položky so zelenou farbou a funkcia VLOOKUP vracia cenu za najprv vstup, 17 dolárov. Vzorec v bunke F5 je:

 
= VLOOKUP (E5,B5:C11,2,FALSE) // returns 17

VLOOKUP vráti prvý zápas

Zástupný znak

Funkcia VLOOKUP podporuje zástupné znaky , čo umožňuje vykonať čiastočnú zhodu s hodnotou vyhľadávania. VLOOKUP môžete napríklad použiť na získanie hodnôt z tabuľky iba po zadaní časť vyhľadávacej hodnoty. Ak chcete vo VLOOKUP používať zástupné znaky, musíte zadať režim presnej zhody zadaním FALSE alebo 0 pre posledný argument, range_lookup . Vzorec v H7 vyhľadá krstné meno „Michael“ po zadaní „Aya“ do bunky H4:

 
= VLOOKUP ($H&'*',$B:$E4,2,FALSE)

Zápas zástupných znakov VLOOKUP

Prečítajte si viac podrobné vysvetlenie tu .

Obojsmerné vyhľadávanie

Vo funkcii VLOOKUP je argument stĺpcového indexu zvyčajne napevno kódovaný ako statické číslo. Môžete však vytvoriť aj súbor dynamický stĺpcový index pomocou funkcie MATCH na vyhľadanie pravého stĺpca. Táto technika vám umožňuje vytvoriť dynamické obojsmerné vyhľadávanie, ktoré sa zhoduje v oboch riadkoch a stĺpce. Na nasledujúcej obrazovke je VLOOKUP nakonfigurovaný na vyhľadávanie podľa mena a mesiaca. Vzorec v H6 je:

 
= VLOOKUP (H4,B5:E13, MATCH (H5,B4:E4,0),0)

VLOOKUP obojsmerné vyhľadávanie

Pre viac detailov, pozri tento príklad .

Poznámka: Vo všeobecnosti INDEX a ZHODA je flexibilnejší spôsob vykonávať obojsmerné vyhľadávanie .

Viac kritérií

Funkcia VLOOKUP nespracováva viac kritérií natívne. Môžete však použiť a pomocný stĺp na spojenie viacerých polí dohromady a používanie týchto polí ako viacerých kritérií vo VLOOKUP. V nižšie uvedenom príklade je stĺpec B pomocným stĺpcom, ktorý zreťazuje meno a priezvisko spolu s týmto vzorcom:

 
=C5&D5 // helper column

VLOOKUP je nakonfigurovaný tak, aby robil to isté pri vytváraní vyhľadávanej hodnoty. Vzorec v H6 je:

 
= VLOOKUP (H4&H5,B5:E13,4,0)

VLOOKUP s viacerými kritériami

Podrobnosti pozri tento príklad .

Poznámka: INDEX a ZHODA a XLOOKUP sú robustnejšie spôsoby, ako spracovať vyhľadávanie na základe viacerých kritérií.

VLOOKUP a #N/A chyby

Ak použijete VLOOKUP, nevyhnutne narazíte na chybu #N/A. Chyba #N/A znamená iba „nenájdené“. Na nasledujúcej obrazovke napríklad neexistuje vyhľadávacia hodnota „Toy Story 2“ vo vyhľadávacej tabuľke a všetky tri vzorce VLOOKUP vracajú #N/A:

VLOOKUP #N/A príklad chyby

Jeden zo spôsobov, ako „zachytiť“ chybu NA, je použiť Funkcia IFNA Páči sa ti to:

VLOOKUP #N/A príklad chyby - opravené

Vzorec v H6 je:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'Not found')

Správu je možné prispôsobiť podľa želania. Ak chcete vrátiť nič (t.j. zobraziť prázdny výsledok), keď VLOOKUP vráti #N/A, môžete použiť prázdny reťazec takto:

 
= IFNA ( VLOOKUP (H4,B5:E9,2,FALSE),'') // no message

Chyba #N/A je užitočná, pretože vám hovorí, že niečo nie je v poriadku. V praxi existuje mnoho dôvodov, prečo sa vám môže zobraziť táto chyba, vrátane:

  • Hľadaná hodnota v tabuľke neexistuje
  • Hodnota vo vyhľadávaní je nesprávne napísaná alebo obsahuje viac miesta
  • Režim zápasu je presný, ale mal by byť približný
  • Rozsah tabuľky nie je zadaný správne
  • Kopírujete VLOOKUP a tabuľku referencia nie je uzamknutá

Čítaj viac: VLOOKUP bez #N/A chýb

Viac o VLOOKUP

Ďalšie poznámky

  • Range_lookup kontroluje, či hodnotu sa musí presne zhodovať alebo nie. Predvolená hodnota je PRAVDA = povoliť nepresnú zhodu.
  • Nastaviť range_lookup FALSE to vyžadovať presná zhoda a PRAVDA do povoliť nepresnú zhodu .
  • Ak range_lookup je TRUE (predvolené nastavenie), nepresná zhoda spôsobí, že funkcia VLOOKUP bude zodpovedať najbližšej hodnote v tabuľke, ktorá je stále menej ako hodnotu .
  • Kedy range_lookup sa vynechá, funkcia VLOOKUP umožní nepresnú zhodu, ale použije presnú zhodu, ak taká existuje.
  • Ak range_lookup je TRUE (predvolené nastavenie), uistite sa, že hodnoty vyhľadávania v prvom riadku tabuľky sú zoradené vzostupne. V opačnom prípade môže funkcia VLOOKUP vrátiť nesprávnu alebo neočakávanú hodnotu.
  • Ak range_lookup je FALSE (vyžadujú presnú zhodu), hodnoty v prvom stĺpci stôl nemusia byť triedené.


^