Lejupielādējiet darbgrāmatas piemēru
Šī apmācība parāda, kā lietot Excel VLOOKUP funkcija programmā Excel, lai atrastu vērtību.
VLOOKUP funkciju pārskats
Funkcija VLOOKUP Vlookup apzīmē vertikālu uzmeklēšanu. Tā meklē vērtību tabulas kreisajā kolonnā. Pēc tam atgriež vērtību ar noteiktu kolonnu skaitu pa labi no atrastās vērtības. Tas ir tāds pats kā hlookup, izņemot to, ka vērtības tiek meklētas vertikāli, nevis horizontāli.
(Ievērojiet, kā parādās formulas ievades)
Funkcijas VLOOKUP sintakse un argumenti:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - Vērtība, kuru vēlaties meklēt.
tabulas_masīvs - Datu diapazons, kurā ir gan vērtība, kuru vēlaties meklēt, gan vērtība, kuru vēlaties, lai vlookup atgrieztos. Slejai, kurā ir meklēšanas vērtības, jābūt kreisākajai kolonnai.
kol_indeksa_numurs - Datu diapazona kolonnas numurs, no kura vēlaties atgriezt vērtību.
range_lookup - Patiesība vai meli. FALSE meklē precīzu atbilstību. TRUE meklē tuvāko atbilstību, kas ir mazāka vai vienāda ar lookup_value. Ja ir izvēlēta TRUE, tad kreisākā sleja (uzmeklēšanas kolonna) ir jāsakārto augošā secībā (no zemākās līdz augstākajai).
Kas ir VLOOKUP funkcija?
Kā viena no vecākajām funkcijām izklājlapu pasaulē tiek izmantota funkcija VLOOKUP Vertisks Uzmeklējumi. Tam ir daži ierobežojumi, kurus bieži pārvar citas funkcijas, piemēram, INDEX/MATCH. Tomēr tā priekšrocība ir tā, ka tā ir viena funkcija, kas pati par sevi var veikt precīzas atbilstības meklēšanu. Tā arī mēdz būt viena no pirmajām funkcijām, ko cilvēki uzzina.
Pamata piemērs
Apskatīsim datu paraugu no atzīmju grāmatas. Mēs apskatīsim vairākus piemērus informācijas iegūšanai konkrētiem studentiem.
Ja mēs vēlamies uzzināt, kurā klasē ir Bobs, mēs uzrakstītu formulu:
| 1 | = MEKLĒŠANA ("Bobs", A2: C5, 2, FALSE) |
Svarīgi atcerēties, ka vienumam, kuru meklējam (Bob), ir jāatrodas mūsu meklēšanas diapazona pirmajā kolonnā (A2: C5). Funkcijai mēs esam teikuši, ka vēlamies atgriezt vērtību no 2nd kolonna, kas šajā gadījumā ir sleja B. Visbeidzot, mēs norādījām, ka vēlamies veikt an precīza atbilstība ievietojot False kā pēdējo argumentu. Šeit atbilde būs “Lasīšana”.
Sānu gals: Kā galīgo argumentu False vietā varat izmantot arī skaitli 0, jo tiem ir vienāda vērtība. Daži cilvēki dod priekšroku tam, jo rakstīšana ir ātrāka. Vienkārši ziniet, ka abi ir pieņemami.
Pārvietoti dati
Lai mūsu pirmajam piemēram pievienotu skaidrojumu, uzmeklēšanas vienumam nav jābūt izklājlapas A slejā, bet tikai meklēšanas diapazona pirmajā kolonnā. Izmantosim to pašu datu kopu:
Tagad atradīsim dabaszinātņu klases atzīmi. Mūsu formula būtu
| 1 | = VLOOKUP ("Zinātne", B2: C5, 2, FALSE) |
Šī joprojām ir derīga formula, jo mūsu meklēšanas diapazona pirmā sleja ir B sleja, kur tiks atrasts mūsu meklēšanas vienums “Zinātne”. Mēs atgriežam vērtību no 2nd meklēšanas diapazona slejā, kas šajā gadījumā ir sleja C. Tad atbilde ir “A-”.
Aizstājējzīmju lietošana
Funkcija VLOOKUP atbalsta aizstājējzīmju “*” un “?” Izmantošanu. veicot meklēšanu. Piemēram, pieņemsim, ka mēs bijām aizmirsuši rakstīt Frenka vārdu un vienkārši vēlējāmies meklēt vārdu, kas sākas ar “F”. Mēs varētu uzrakstīt formulu
| 1 | = MEKLĒŠANA ("F*", A2: C5, 2, FALSE) |
Tas varētu atrast vārdu Frank 5. rindā un pēc tam atgriezt vērtību no 2nd relatīvā kolonna. Šajā gadījumā atbilde būs “Zinātne”.
Nav precīza atbilstība
Lai iegūtu precīzu atbilstību, lielāko daļu laika vēlaties pārliecināties, vai pēdējais arguments VLOOKUP ir nepareizs (vai 0). Tomēr ir gadījumi, kad jūs meklējat neprecīzu atbilstību. Ja jums ir sakārtotu datu saraksts, varat izmantot arī VLOOKUP, lai atgrieztu vienuma rezultātu, kas ir vienāds vai nākamais mazākais. To bieži izmanto, strādājot ar pieaugošu skaitļu diapazonu, piemēram, nodokļu tabulā vai komisijas prēmijās.
Pieņemsim, ka vēlaties atrast nodokļu likmi ienākumam, kas ievadīts šūnā D2. D4 formula var būt šāda:
| 1 | = MEKLĒŠANA (D2, A2: B6, 2, TRUE) |
Atšķirība šajā formulā ir tāda, ka mūsu pēdējais arguments ir “Patiess”. Mūsu konkrētajā piemērā mēs redzam, ka tad, kad mūsu indivīds iegūs ienākumus 45 000 ASV dolāru apmērā, viņiem būs 15%nodokļa likme.
Piezīme: Lai gan mēs parasti vēlamies precīzu atbilstību ar argumentu False, jūs aizmirstat norādīt 4tūkst VLOOKUP argumentā noklusējuma vērtība ir True. Tā rezultātā jūs varat iegūt negaidītus rezultātus, it īpaši, strādājot ar teksta vērtībām.
Dinamiskā kolonna
VLOOKUP pieprasa norādīt argumentu, kurā slejā vēlaties atgriezt vērtību, taču var gadīties gadījumi, kad nezināt, kur šī sleja atradīsies, vai arī vēlaties ļaut lietotājam mainīt, no kuras kolonnas atgriezties. Šādos gadījumos var būt noderīgi izmantot funkciju MATCH, lai noteiktu kolonnas numuru.
Aplūkosim vēlreiz mūsu atzīmju grāmatas piemēru ar dažiem ievadiem E2 un E4. Lai iegūtu kolonnas numuru, mēs varētu uzrakstīt formulu
| 1 | = MATCH (E2, A1: C1, 0) |
Tas mēģinās atrast precīzu “Novērtējuma” pozīciju diapazonā A1: C1. Atbilde būs 3. Zinot to, mēs varam to pievienot VLOOKUP funkcijai un rakstīt formulu E6 šādi:
| 1 | = MEKLĒŠANA (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Tātad funkcija MATCH tiks novērtēta līdz 3, un tas liek VLOOKUP atgriezt rezultātu no 3rd kolonnā A2: C5 diapazonā. Kopumā mēs iegūstam vēlamo “C” rezultātu. Mūsu formula tagad ir dinamiska, jo mēs varam mainīt apskatāmo kolonnu vai meklējamo nosaukumu.
VLOOKUP ierobežojumi
Kā minēts raksta sākumā, lielākais VLOOKUP kritums ir tas, ka meklēšanas vienumam ir jāatrod meklēšanas diapazona kreisajā augšējā kolonnā. Lai gan ir daži izsmalcināti triki, ko varat darīt, lai to pārvarētu, izplatītā alternatīva ir izmantot indeksu un atbilstību. Šī kombinācija sniedz jums lielāku elastību, un dažreiz tas var būt pat ātrāks aprēķins.
MEKLĒŠANA Google izklājlapās
Funkcija VLOOKUP Google izklājlapās darbojas tieši tāpat kā programmā Excel:
papildu piezīmes
Izmantojiet funkciju VLOOKUP, lai veiktu vertikālu meklēšanu. VLOOKUP meklē precīzu atbilstību (range_lookup = FALSE) vai tuvākā atbilstība, kas ir vienāda vai mazāka par lookup_value (range_lookup = TRUE, tikai skaitliskas vērtības) tabulas_masīva pirmajā rindā. Pēc tam tas atgriež atbilstošu vērtību, n rindu skaitu zem atbilstības.
Ja izmantojat VLOOKUP, lai atrastu precīzu atbilstību, vispirms definējiet identifikācijas vērtību, kuru vēlaties meklēt kā lookup_value. Šī identifikācijas vērtība var būt SSN, darbinieka ID, vārds vai kāds cits unikāls identifikators.
Tālāk jūs definējat diapazonu (ko sauc par tabulas_masīvs), kas satur identifikatorus augšējā rindā un visas vērtības, kuras jūs galu galā vēlaties meklēt rindās zem tās. SVARĪGI: unikālajiem identifikatoriem jābūt augšējā rindā. Ja tie nav, jums ir jāpārvieto rinda uz augšu vai jāizmanto MATCH / INDEX, nevis VLOOKUP.
Treškārt, definējiet rindas numuru (rindas_indekss) no tabulas_masīvs ka vēlaties atgriezties. Paturiet prātā, ka pirmā rinda, kurā ir unikālie identifikatori, ir 1. rinda. Otrā rinda ir 2. rinda utt.
Visbeidzot, sadaļā jānorāda, vai meklēt precīzu atbilstību (FALSE) vai tuvāko atbilstību (TRUE) range_lookup. Ja ir atlasīta precīzās atbilstības opcija un precīza atbilstība netiek atrasta, tiek atgriezta kļūda (#N/A). Lai formula atgrieztos tukša vai “nav atrasta” vai kāda cita vērtība kļūdas vērtības (#N/A) vietā, izmantojiet funkciju IFERROR kopā ar VLOOKUP.
Lai izmantotu funkciju VLOOKUP, lai atgrieztu aptuvenu atbilstības kopu: range_lookup = PATIESA. Šī opcija ir pieejama tikai skaitliskām vērtībām. Vērtības jāsakārto augošā secībā.
Lai veiktu horizontālu uzmeklēšanu, izmantojiet funkciju HLOOKUP.
VLOOKUP piemēri VBA
VBA varat izmantot arī funkciju VLOOKUP. Tips:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Funkcijas argumentiem (lookup_value utt.) Varat tos ievadīt tieši funkcijā vai definēt mainīgos, ko izmantot.
Atgriezieties visu Excel funkciju sarakstā
