Excel VLOOKUP funkcija

VLOOKUP ir Excel funkcija, lai meklētu datus vertikāli sakārtotā tabulā. VLOOKUP atbalsta aptuvenu un precīzu atbilstību, un aizstājējzīmes (*?) daļējām spēlēm. Uzmeklēšanas vērtībām ir jābūt redzamām pirmais tabulas sleja pārgāja uz VLOOKUP.

• vērtību - Vērtība, kas jāmeklē tabulas pirmajā slejā.
• tabula - tabula, no kuras var iegūt vērtību.
• kol_indekss - tabulas sleja, no kuras izgūt vērtību.
• range_lookup - [pēc izvēles] TRUE = aptuvena atbilstība (noklusējums). FALSE = precīza atbilstība.

VLOOKUP ir Excel funkcija, lai iegūtu datus no tabulas, kas sakārtota vertikāli. Uzmeklēšanas vērtībām ir jābūt redzamām pirmais tabulas sleja pārgāja uz VLOOKUP. VLOOKUP atbalsta aptuvenu un precīzu atbilstību, un aizstājējzīmes (*?) daļējām spēlēm.

Vertikālie dati | Kolonnu numuri | Izskatās tikai pareizi | Atbilstoši režīmi | Precīza atbilstība | Aptuvenā atbilstība | Pirmā spēle | Aizstājējzīme | Divvirzienu uzmeklēšana | Vairāki kritēriji | #N/A Kļūdas | Video

V ir vertikāls

VLOOKUP mērķis ir iegūt informāciju no šādas tabulas:

Izmantojot B slejā esošo pasūtījuma numuru kā uzmeklēšanas vērtību, VLOOKUP var iegūt klienta ID, summu, nosaukumu un valsti jebkuram pasūtījumam. Piemēram, lai iegūtu klienta vārdu pasūtījumam 1004, formula ir šāda:

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

Horizontāliem datiem varat izmantot MEKLĒŠANA , INDEKSS un MATCH , vai XLOOKUP .

mainīt vertikālo asi uz grāmatvedības formātu

VLOOKUP pamatā ir kolonnu numuri

Izmantojot VLOOKUP, iedomājieties, ka katra kolonna tabula ir numurēts, sākot no kreisās puses. Lai iegūtu vērtību no konkrētas kolonnas, norādiet atbilstošo skaitli kā “kolonnu indeksu”. Piemēram, kolonnu indekss, lai iegūtu zemāk esošo vārdu, ir 2:

Uzvārdu un e -pasta adresi var iegūt, izmantojot 3. un 4. sleju:

 
= 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 izskatās tikai pareizi

VLOOKUP var skatīties tikai pa labi. Dati, kurus vēlaties izgūt (rezultātu vērtības), var parādīties jebkurā slejā pa labi no meklēšanas vērtībām:

Ja jums ir nepieciešams meklēt vērtības pa kreisi, skatiet INDEKSS un MATCH , vai XLOOKUP .

Precīza un aptuvena atbilstība

VLOOKUP ir divi saskaņošanas režīmi - precīzs un aptuvens. Argumenta nosaukums, kas kontrolē atbilstību, ir ' range_lookup '. Šis ir mulsinošs nosaukums, jo šķiet, ka tam ir kāds sakars šūnu diapazoni piemēram, A1: A10. Patiesībā vārds “diapazons” šajā gadījumā attiecas uz “vērtību diapazonu” - kad range_lookup ir TRUE, VLOOKUP atbilst a vērtību diapazons nevis precīzu vērtību. Labs piemērs tam ir izmantošana VLOOKUP, lai aprēķinātu atzīmes .

Ir svarīgi to saprast range_lookup pēc noklusējuma ir TRUE , kas nozīmē, ka VLOOKUP pēc noklusējuma izmantos aptuveno atbilstību, kas var būt bīstami . Uzstādīt range_lookup uz FALSE, lai piespiestu precīzu atbilstību:

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

Piezīme. Precīzai atbilstībai FALSE vietā varat norādīt arī nulli (0).

Precīza atbilstība

Vairumā gadījumu jūs, iespējams, vēlēsities izmantot VLOOKUP precīzās atbilstības režīmā. Tam ir jēga, ja jums ir unikāla atslēga, ko izmantot kā uzmeklēšanas vērtību, piemēram, filmas nosaukums šajos datos:

Formula H6, lai atrastu Gads , pamatojoties uz precīzu filmas nosaukuma atbilstību, ir:

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

Aptuvenā atbilstība

Gadījumos, kad vēlaties vislabāk atbilst , ne vienmēr precīza atbilstība , jūs vēlaties izmantot aptuveno režīmu. Piemēram, zemāk mēs vēlamies atrast komisijas maksu tabulā G5: H10. Uzmeklēšanas vērtības nāk no kolonnas C. Šajā piemērā mums jāizmanto VLOOKUP in aptuvenā atbilstība režīmā, jo vairumā gadījumu precīza atbilstība nekad netiks atrasta. VLOOKUP formula D5 ir konfigurēta, lai veiktu aptuvenu atbilstību, iestatot pēdējo argumentu uz TRUE:

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

VLOOKUP meklēs G kolonnas vērtības, lai atrastu uzmeklēšanas vērtību. Ja tiek atrasta precīza atbilstība, VLOOKUP to izmantos. Ja nē, VLOOKUP “atkāpsies” un sakritīs ar iepriekšējo rindu.

Piezīme. Ja izmantojat aptuvenās atbilstības režīmu ar VLOOKUP, dati ir jāsakārto augošā secībā pēc uzmeklēšanas vērtības.

Pirmais mačs

Dublētu vērtību gadījumā VLOOKUP atradīs pirmais mačs kad atbilstības režīms ir precīzs. Tālāk redzamajā ekrānā VLOOKUP ir konfigurēts, lai atrastu cenu par zaļo krāsu. Ir trīs ieraksti ar zaļu krāsu, un VLOOKUP atgriež cenu pirmais ieeja, 17 ASV dolāri. Formula šūnā F5 ir šāda:

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

Aizstājējzīme

Funkcija VLOOKUP atbalsta aizstājējzīmes , kas ļauj veikt daļēju atbilstību uzmeklēšanas vērtībai. Piemēram, varat izmantot VLOOKUP, lai izgūtu vērtības no tabulas tikai pēc ievadīšanas daļa uzmeklēšanas vērtību. Lai ar VLOOKUP izmantotu aizstājējzīmes, jānorāda precīzās atbilstības režīms, pēdējam argumentam norādot FALSE vai 0, range_lookup . H7 formula iegūst vārdu “Michael” pēc tam, kad šūnā H4 ir ierakstīta “Aya”:

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

Lasiet vairāk detalizēts skaidrojums šeit .

Divvirzienu uzmeklēšana

Funkcijas VLOOKUP ietvaros kolonnu indeksa arguments parasti tiek kodēts kā statisks skaitlis. Tomēr jūs varat arī izveidot a dinamisko kolonnu indekss izmantojot funkciju MATCH, lai atrastu labo kolonnu. Šis paņēmiens ļauj izveidot dinamisku divvirzienu uzmeklēšanu, kas atbilst abām rindām un kolonnas. Tālāk redzamajā ekrānā VLOOKUP ir konfigurēts, lai veiktu uzmeklēšanu, pamatojoties uz vārdu un mēnesi. H6 formula ir šāda:

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

Lai iegūtu sīkāku informāciju, skatiet šo piemēru .

Piezīme: Kopumā INDEKSS un MATCH ir elastīgāks veids, kā veikt divvirzienu meklēšanu .

Vairāki kritēriji

Funkcija VLOOKUP sākotnēji neapstrādā vairākus kritērijus. Tomēr jūs varat izmantot a palīgu kolonna lai apvienotu vairākus laukus kopā un izmantotu šos laukus kā vairākus kritērijus VLOOKUP. Tālāk redzamajā piemērā B sleja ir palīga kolonna, kas saliktie vārds un uzvārds kopā ar šo formulu:

 
=C5&D5 // helper column

VLOOKUP ir konfigurēts darīt to pašu, lai izveidotu uzmeklēšanas vērtību. H6 formula ir šāda:

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

Lai iegūtu sīkāku informāciju, skatiet šo piemēru .

Piezīme: INDEKSS un MATCH un XLOOKUP ir daudz efektīvāki veidi, kā apstrādāt meklēšanu, pamatojoties uz vairākiem kritērijiem.

IEPAKOŠANĀS un #N/A kļūdas

Ja izmantojat VLOOKUP, neizbēgami rodas kļūda #N/A. Kļūda #N/A nozīmē tikai “nav atrasta”. Piemēram, tālāk redzamajā ekrānā uzmeklēšanas tabulā nav uzmeklēšanas vērtības “Rotaļlietu stāsts 2”, un visas trīs VLOOKUP formulas atgriež #N/A:

Viens veids, kā “iesprostot” NA kļūdu, ir izmantot IFNA funkcija kā šis:

H6 formula ir šāda:

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

Ziņojumu var pielāgot pēc vēlēšanās. Lai neko neatgrieztu (t.i., lai parādītu tukšu rezultātu), kad VLOOKUP atgriež #N/A, varat izmantot šādu tukšu virkni:

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

Kļūda #N/A ir noderīga, jo tā norāda, ka kaut kas nav kārtībā. Praksē ir daudz iemeslu, kāpēc jūs varat redzēt šo kļūdu, tostarp:

• Uzmeklēšanas vērtība tabulā nepastāv
• Uzmeklēšanas vērtība ir uzrakstīta nepareizi vai satur papildu vietu
• Atbilstības režīms ir precīzs, taču tam jābūt aptuvenam
• Tabulas diapazons nav ievadīts pareizi
• Jūs kopējat VLOOKUP un tabulu atsauce nav bloķēta

Lasīt vairāk: VLOOKUP bez #N/A kļūdām

Vairāk par VLOOKUP

• Vairāk VLOOKUP piemēru
• VLOOKUP videoklipi
• 23 padomi, kā izmantot VLOOKUP

Citas piezīmes

• Range_lookup kontrolē, vai vērtību precīzi jāatbilst vai nē. Noklusējums ir TRUE = atļaut neprecīzu atbilstību.
• Uzstādīt range_lookup uz FALSE to pieprasīt precīza atbilstība un TRUE to atļaut neprecīzu atbilstību .
• Ja range_lookup ir TRUE (noklusējuma iestatījums), ja nav precīza atbilstība, funkcija VLOOKUP sasniegs tuvāko tabulas vērtību vēl mazāk nekā vērtību .
• Kad range_lookup tiek izlaists, funkcija VLOOKUP atļaus neprecīzu atbilstību, taču tiks izmantota precīza atbilstība, ja tāda ir.
• Ja range_lookup ir TRUE (noklusējuma iestatījums), pārliecinieties, ka uzmeklēšanas vērtības tabulas pirmajā rindā ir sakārtotas augošā secībā. Pretējā gadījumā VLOOKUP var atgriezt nepareizu vai neparedzētu vērtību.
• Ja range_lookup ir FALSE (nepieciešama precīza atbilstība), vērtības pirmajā slejā tabula nav jāšķiro.