VLOOKUP funkcija programmā Excel — rokasgrāmata iesācējiem: sintakse un piemēri

Saturs

VLOOKUP funkcija programmā Excel – vispārīgs apraksts un sintakse
- Funkcijas VLOOKUP sintakse
VLOOKUP piemēri
Precīza vai aptuvenā atbilstība funkcijā VLOOKUP
- 1. piemērs: precīzas atbilstības atrašana ar VLOOKUP
- 2. piemērs: VLOOKUP izmantošana, lai atrastu aptuvenu atbilstību
VLOOKUP programmā Excel — jums tas ir jāatceras!

Šodien mēs sākam rakstu sēriju, kurā aprakstīta viena no visnoderīgākajām Excel funkcijām VPR (VLOOKUP). Šī funkcija tajā pašā laikā ir viena no vissarežģītākajām un vismazāk saprotamajām.

Šajā apmācībā par VPR Es centīšos pēc iespējas vienkāršāk izklāstīt pamatus, lai nepieredzējušiem lietotājiem mācību process būtu pēc iespējas skaidrāks. Turklāt mēs izpētīsim vairākus piemērus ar Excel formulām, kas demonstrēs visbiežāk sastopamos funkcijas lietošanas gadījumus VPR.

VLOOKUP funkcija programmā Excel – vispārīgs apraksts un sintakse

Tātad, kas tas ir VPR? Pirmkārt, tā ir Excel funkcija. Ko viņa dara? Tas meklē jūsu norādīto vērtību un atgriež atbilstošo vērtību no citas kolonnas. Tehniski runājot, VPR meklē vērtību dotā diapazona pirmajā kolonnā un atgriež rezultātu no citas kolonnas tajā pašā rindā.

Visizplatītākajā lietojumprogrammā funkcija VPR meklē datu bāzē doto unikālo identifikatoru un no datu bāzes izvelk ar to saistīto informāciju.

Pirmais burts funkcijas nosaukumā VPR (VLOOKUP) nozīmē Вvertikāla (Vvertikāli). Pēc tā jūs varat atšķirt VPR no GPR (HLOOKUP), kas meklē vērtību diapazona augšējā rindā − Гhorizontāli (Hhorizontāli).

funkcija VPR pieejams programmās Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP un Excel 2000.

Funkcijas VLOOKUP sintakse

funkcija VPR (VLOOKUP) ir šāda sintakse:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Kā redzat, funkcija VPR programmā Microsoft Excel ir 4 opcijas (vai argumenti). Pirmie trīs ir obligāti, pēdējie nav obligāti.

lookup_value (lookup_value) — vērtība, kas jāmeklē. Tā var būt vērtība (skaitlis, datums, teksts) vai šūnas atsauce (kas satur uzmeklēšanas vērtību) vai vērtība, ko atgriež kāda cita Excel funkcija. Piemēram, šī formula meklēs vērtību 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Ja uzmeklēšanas vērtība ir mazāka par mazāko vērtību meklējamā diapazona pirmajā kolonnā, funkcija VPR ziņos par kļūdu # AT (#N/A).

tabulas_masīvs (tabula) – divas vai vairākas datu kolonnas. Atcerieties, funkcija VPR vienmēr meklē vērtību argumentā norādītā diapazona pirmajā kolonnā tabulas_masīvs (tabula). Skatāmajā diapazonā var būt dažādi dati, piemēram, teksts, datumi, skaitļi, Būla vērtības. Funkcija nav reģistrjutīga, tas nozīmē, ka lielie un mazie burti tiek uzskatīti par vienādiem. Tātad mūsu formula meklēs vērtību 40 šūnās no A2 uz A15, jo A ir argumentā norādītā diapazona A2:B15 pirmā kolonna tabulas_masīvs (tabula):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (kolonnas_numurs) ir kolonnas numurs dotajā diapazonā, no kuras tiks atgriezta vērtība atrastajā rindā. Kreisākā kolonna dotajā diapazonā ir 1, otrā kolonna ir 2, trešā kolonna ir 3 un tā tālāk. Tagad jūs varat izlasīt visu formulu:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Formula, kas meklē vērtību 40 diapazonā A2: A15 un atgriež atbilstošo vērtību no kolonnas B (jo B ir otrā kolonna diapazonā A2:B15).

Ja argumenta vērtība col_index_num (kolonnas_numurs) mazāks par 1tAD VPR ziņos par kļūdu #VĒRTĪBA! (#VĒRTĪBA!). Un ja tas ir lielāks par kolonnu skaitu diapazonā tabulas_masīvs (tabula), funkcija atgriezīs kļūdu #REF! (#LINK!).

diapazona_uzmeklēšana (range_lookup) — nosaka, ko meklēt:
- precīza atbilstība, argumentam jābūt vienādam FALSE (FALSE);
- aptuvenā atbilstība, arguments ir vienāds PATIESAIS KODS (TRUE) vai vispār nav norādīts.
Šis parametrs nav obligāts, bet ļoti svarīgs. Vēlāk šajā apmācībā VPR Es parādīšu dažus piemērus, kas izskaidro, kā uzrakstīt formulas precīzas un aptuvenas atbilstības atrašanai.

VLOOKUP piemēri

Es ceru, ka funkcija VPR kļūst jums mazliet skaidrāks. Tagad apskatīsim dažus lietošanas gadījumus VPR formulās ar reāliem datiem.

Kā izmantot VLOOKUP, lai meklētu citā Excel lapā

Praksē formulas ar funkciju VPR tiek reti izmantoti, lai meklētu datus vienā darblapā. Biežāk jūs meklēsit un izgūsit atbilstošās vērtības no citas lapas.

Lai izmantotu VPR, meklējiet citā Microsoft Excel lapā, jums ir jābūt argumentā tabulas_masīvs (tabula) norādiet lapas nosaukumu ar izsaukuma zīmi, kam seko šūnu diapazons. Piemēram, šī formula parāda, ka diapazons A2: B15 atrodas uz lapas ar nosaukumu Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Protams, lapas nosaukums nav jāievada manuāli. Vienkārši sāciet rakstīt formulu un, kad runa ir par argumentu tabulas_masīvs (tabula), pārslēdzieties uz vajadzīgo lapu un ar peli atlasiet vajadzīgo šūnu diapazonu.

Formula, kas parādīta zemāk esošajā ekrānuzņēmumā, darblapā A slejā (tā ir diapazona A1:B1 2. kolonna) meklē tekstu “Produkts 9”. Cenas.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Lūdzu, atcerieties, ka, meklējot teksta vērtību, tā jāievieto pēdiņās (“”), kā tas parasti tiek darīts Excel formulās.

Par argumentu tabulas_masīvs (tabula) vēlams vienmēr izmantot absolūtās atsauces (ar $ zīmi). Šajā gadījumā, kopējot formulu uz citām šūnām, meklēšanas diapazons paliks nemainīgs.

Meklēt citā darbgrāmatā, izmantojot VLOOKUP

Lai funkcionētu VPR strādājis starp divām Excel darbgrāmatām, jums ir jānorāda darbgrāmatas nosaukums kvadrātiekavās pirms lapas nosaukuma.

Piemēram, zemāk ir formula, kas meklē vērtību 40 uz lapas Sheet2 grāmatā Skaitļi.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Šis ir vienkāršākais veids, kā izveidot formulu programmā Excel ar VPRkurā ir saites uz citu darbgrāmatu:

Atveriet abas grāmatas. Tas nav nepieciešams, taču šādā veidā ir vieglāk izveidot formulu. Jūs nevēlaties ievadīt darbgrāmatas nosaukumu manuāli, vai ne? Turklāt tas pasargās jūs no nejaušām drukas kļūdām.
Sāciet rakstīt funkciju VPRun kad runa ir par strīdu tabulas_masīvs (tabula), pārslēdzieties uz citu darbgrāmatu un atlasiet tajā vajadzīgo meklēšanas diapazonu.

Tālāk esošajā ekrānuzņēmumā ir parādīta formula ar meklēšanas iestatījumu diapazonu darbgrāmatā Cenu saraksts.xlsx uz lapas Cenas.

funkcija VPR darbosies pat tad, ja aizverat meklēto darbgrāmatu un formulas joslā tiek parādīts pilns ceļš uz darbgrāmatas failu, kā parādīts tālāk:

Ja darbgrāmatas vai lapas nosaukumā ir atstarpes, tas jāiekļauj apostrofos:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Kā izmantot nosauktu diapazonu vai tabulu formulās, izmantojot VLOOKUP

Ja plānojat izmantot vienu un to pašu meklēšanas diapazonu vairākās funkcijās VPR, varat izveidot nosauktu diapazonu un ievadīt tā nosaukumu formulā kā argumentu tabulas_masīvs (tabula).

Lai izveidotu nosauktu diapazonu, vienkārši atlasiet šūnas un laukā ievadiet atbilstošu nosaukumu Vārds, pa kreisi no formulas joslas.

Tagad produkta cenas noteikšanai varat pierakstīt šādu formulu Produkts 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Lielākā daļa diapazonu nosaukumu darbojas visai Excel darbgrāmatai, tāpēc argumentam nav jānorāda lapas nosaukums tabulas_masīvs (tabula), pat ja formula un meklēšanas diapazons atrodas dažādās darblapās. Ja tie atrodas dažādās darbgrāmatās, tad pirms diapazona nosaukuma ir jānorāda darbgrāmatas nosaukums, piemēram, šādi:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Tātad formula izskatās daudz skaidrāka, piekrītat? Nosaukto diapazonu izmantošana ir arī laba alternatīva absolūtām atsaucēm, jo nosauktais diapazons nemainās, kopējot formulu citās šūnās. Tas nozīmē, ka varat būt pārliecināti, ka formulas meklēšanas diapazons vienmēr būs pareizs.

Ja šūnu diapazonu pārveidojat par pilnvērtīgu Excel izklājlapu, izmantojot komandu Tabula (Tabula) cilne Ievietošana (Ievietot), tad, atlasot diapazonu ar peli, programma Microsoft Excel automātiski pievienos formulai kolonnu nosaukumus (vai tabulas nosaukumu, ja atlasāt visu tabulu).

Gatavā formula izskatīsies apmēram šādi:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Vai varbūt pat šādi:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Izmantojot nosauktos diapazonus, saites norādīs uz tām pašām šūnām neatkarīgi no funkcijas kopēšanas vietas VPR darbgrāmatas ietvaros.

Aizstājējzīmju izmantošana VLOOKUP formulās

Tāpat kā ar daudzām citām funkcijām, VPR Varat izmantot šādas aizstājējzīmes:

Jautājuma zīme (?) – aizstāj jebkuru atsevišķu rakstzīmi.
Zvaigznīte (*) – aizstāj jebkuru rakstzīmju secību.

Aizstājējzīmju izmantošana funkcijās VPR var būt noderīga daudzos gadījumos, piemēram:

Ja jūs neatceraties precīzi tekstu, kas jums jāatrod.
Ja vēlaties atrast vārdu, kas ir daļa no šūnas satura. Ziniet to VPR meklē pēc šūnas satura kopumā, it kā opcija būtu iespējota Saskaņot visu šūnas saturu (Visa šūna) standarta Excel meklēšanā.
Ja šūnā ir papildu atstarpes satura sākumā vai beigās. Šādā situācijā jūs varat ilgstoši sagrozīt smadzenes, mēģinot saprast, kāpēc formula nedarbojas.

1. piemērs: tiek meklēts teksts, kas sākas vai beidzas ar noteiktām rakstzīmēm

Pieņemsim, ka vēlaties meklēt konkrētu klientu tālāk parādītajā datu bāzē. Jūs neatceraties viņa uzvārdu, bet zināt, ka tas sākas ar “ack”. Šeit ir formula, kas lieliski veiks darbu:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Tagad, kad esat pārliecināts, ka esat atradis pareizo vārdu, varat izmantot to pašu formulu, lai atrastu šī klienta samaksāto summu. Lai to izdarītu, vienkārši mainiet funkcijas trešo argumentu VPR uz vajadzīgo kolonnas numuru. Mūsu gadījumā šī ir C kolonna (3. diapazonā):

=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Šeit ir vēl daži piemēri ar aizstājējzīmēm:

~ Atrodiet vārdu, kas beidzas ar “cilvēks”:

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Atrodiet vārdu, kas sākas ar “ad” un beidzas ar “son”:

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Mēs atrodam pirmo vārdu sarakstā, kas sastāv no 5 rakstzīmēm:

=VLOOKUP("?????",$A$2:$C$11,1,FALSE)

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Lai funkcionētu VPR ar aizstājējzīmēm darbojās pareizi, kā ceturtais arguments, kas jums vienmēr jāizmanto FALSE (FALSE). Ja meklēšanas diapazonā ir vairāk nekā viena vērtība, kas atbilst meklēšanas vienumiem ar aizstājējzīmēm, tiks atgriezta pirmā atrastā vērtība.

2. piemērs. Apvienojiet aizstājējzīmes un šūnu atsauces VLOOKUP formulās

Tagad apskatīsim nedaudz sarežģītāku piemēru, kā meklēt, izmantojot funkciju VPR pēc vērtības šūnā. Iedomājieties, ka A kolonna ir licences atslēgu saraksts, bet kolonna B ir to nosaukumu saraksts, kuriem pieder licence. Turklāt šūnā C1 ir daļa (vairākas rakstzīmes) no kāda veida licences atslēgas, un jūs vēlaties atrast īpašnieka vārdu.

To var izdarīt, izmantojot šādu formulu:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Šī formula meklē vērtību no šūnas C1 dotajā diapazonā un atgriež atbilstošo vērtību no kolonnas B. Ņemiet vērā, ka pirmajā argumentā mēs izmantojam rakstzīmi & (&) pirms un pēc šūnas atsauces, lai saistītu teksta virkni.

Kā redzat attēlā zemāk, funkcija VPR atgriež “Jeremy Hill”, jo viņa licences atslēga satur rakstzīmju secību no šūnas C1.

Ņemiet vērā, ka arguments tabulas_masīvs (tabula) iepriekš redzamajā ekrānuzņēmumā satur tabulas nosaukumu (7. tabula), nevis norāda šūnu diapazonu. Tas ir tas, ko mēs darījām iepriekšējā piemērā.

Precīza vai aptuvenā atbilstība funkcijā VLOOKUP

Un visbeidzot, aplūkosim tuvāk pēdējo argumentu, kas ir norādīts funkcijai VPR - diapazona_uzmeklēšana (interval_view). Kā minēts nodarbības sākumā, šis arguments ir ļoti svarīgs. Jūs varat iegūt pilnīgi atšķirīgus rezultātus vienā formulā ar tās vērtību PATIESAIS KODS (TRUE) vai FALSE (FALSE).

Vispirms noskaidrosim, ko programmā Microsoft Excel nozīmē precīzas un aptuvenas atbilstības.

Ja arguments diapazona_uzmeklēšana (range_lookup) ir vienāds ar FALSE (FALSE), formula meklē precīzu atbilstību, ti, tieši tādu pašu vērtību, kāda norādīta argumentā lookup_value (meklēšanas_vērtība). Ja diapazona pirmajā ailē tspējīgs_masīvs (tabula) saskaras ar divām vai vairākām vērtībām, kas atbilst argumentam lookup_value (meklēšanas_vērtība), tad tiks atlasīts pirmais. Ja neviena atbilstība netiek atrasta, funkcija ziņos par kļūdu # AT (#N/A). Piemēram, šī formula ziņos par kļūdu # AT (#N/A), ja diapazonā A2:A15 nav vērtības 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Ja arguments diapazona_uzmeklēšana (range_lookup) ir vienāds ar PATIESAIS KODS (TRUE), formula meklē aptuvenu atbilstību. Precīzāk, vispirms funkcija VPR meklē precīzu atbilstību un, ja tāda netiek atrasta, atlasa aptuvenu. Aptuvenā atbilstība ir lielākā vērtība, kas nepārsniedz argumentā norādīto vērtību. lookup_value (meklēšanas_vērtība).

Ja arguments diapazona_uzmeklēšana (range_lookup) ir vienāds ar PATIESAIS KODS (TRUE) vai nav norādīts, tad vērtības diapazona pirmajā kolonnā ir jākārto augošā secībā, tas ir, no mazākās uz lielāko. Pretējā gadījumā funkcija VPR var atgriezt kļūdainu rezultātu.

Lai labāk izprastu izvēles nozīmi PATIESAIS KODS (PATIESĪBA) vai FALSE (FALSE), apskatīsim vēl dažas formulas ar funkciju VPR un paskaties uz rezultātiem.

1. piemērs: precīzas atbilstības atrašana ar VLOOKUP

Kā atceraties, lai meklētu precīzu atbilstību, funkcijas ceturtais arguments VPR vajadzētu būt nozīmei FALSE (FALSE).

Atgriezīsimies pie tabulas no paša pirmā piemēra un uzzināsim, kurš dzīvnieks var pārvietoties ar ātrumu 50 jūdzes stundā. Es uzskatu, ka šī formula jums nesagādās nekādas grūtības:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Ņemiet vērā, ka mūsu meklēšanas diapazons (A sleja) satur divas vērtības 50 – šūnās A5 и A6. Formula atgriež vērtību no šūnas B5. Kāpēc? Jo, meklējot precīzu atbilstību, funkcija VPR izmanto pirmo atrasto vērtību, kas atbilst meklētajai vērtībai.

2. piemērs: VLOOKUP izmantošana, lai atrastu aptuvenu atbilstību

Kad izmantojat funkciju VPR lai meklētu aptuvenu atbilstību, ti, kad arguments diapazona_uzmeklēšana (range_lookup) ir vienāds ar PATIESAIS KODS (TRUE) vai izlaists, pirmā lieta, kas jums jādara, ir kārtot diapazonu pēc pirmās kolonnas augošā secībā.

Tas ir ļoti svarīgi, jo funkcija VPR atgriež nākamo lielāko vērtību pēc dotās vērtības, un pēc tam meklēšana tiek pārtraukta. Ja neievērosit pareizo šķirošanu, jūs saņemsiet ļoti dīvainus rezultātus vai kļūdas ziņojumu. # AT (#N/A).

Tagad varat izmantot vienu no šīm formulām:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Kā redzat, vēlos noskaidrot, kuram no dzīvniekiem ir vistuvākais ātrums 69 jūdzes stundā. Un šeit ir rezultāts, ko funkcija man atgrieza VPR:

Kā redzat, formula atgrieza rezultātu Antilope (Antilope), kuras ātrums 61 jūdzes stundā, lai gan sarakstā ir arī gepards (gepards), kurš skrien ar ātrumu 70 jūdzes stundā, un 70 ir tuvāk 69 nekā 61, vai ne? Kāpēc tas notiek? Tā kā funkcija VPR meklējot aptuvenu atbilstību, atgriež lielāko vērtību, kas nav lielāka par meklēto.

Es ceru, ka šie piemēri nedaudz izskaidro darbu ar šo funkciju VPR programmā Excel, un jūs vairs neskatāties uz viņu kā uz cilvēku no malas. Tagad nav par ļaunu īsi atkārtot mūsu pētītā materiāla galvenos punktus, lai tos labāk saglabātu atmiņā.

VLOOKUP programmā Excel — jums tas ir jāatceras!

funkcija VPR Excel nevar skatīties pa kreisi. Tas vienmēr meklē vērtību argumenta norādītā diapazona vistālāk kreisajā kolonnā tabulas_masīvs (tabula).
Funkcijā VPR visas vērtības nav reģistrjutīgas, ti, mazie un lielie burti ir līdzvērtīgi.
Ja meklētā vērtība ir mazāka par minimālo vērtību meklējamā diapazona pirmajā kolonnā, funkcija VPR ziņos par kļūdu # AT (#N/A).
Ja 3. arguments col_index_num (kolonnas_numurs) mazāks par 1funkcija VPR ziņos par kļūdu #VĒRTĪBA! (#VĒRTĪBA!). Ja tas ir lielāks par kolonnu skaitu diapazonā tabulas_masīvs (tabula), funkcija ziņos par kļūdu #REF! (#LINK!).
Argumentā izmantojiet absolūtās šūnu atsauces tabulas_masīvs (tabula), lai, kopējot formulu, tiktu saglabāts pareizais meklēšanas diapazons. Kā alternatīvu mēģiniet izmantot nosauktos diapazonus vai tabulas programmā Excel.
Veicot aptuvenās atbilstības meklēšanu, atcerieties, ka meklējamā diapazona pirmā kolonna ir jākārto augošā secībā.
Visbeidzot, atcerieties ceturtā argumenta nozīmi. Izmantojiet vērtības PATIESAIS KODS (PATIESĪBA) vai FALSE (FALSE) apzināti, un jūs atbrīvosities no daudzām galvassāpēm.

Nākamajos mūsu funkciju apmācības rakstos VPR programmā Excel mēs uzzināsim sarežģītākus piemērus, piemēram, dažādu aprēķinu veikšanu, izmantojot VPR, vērtību iegūšana no vairākām kolonnām un daudz ko citu. Paldies, ka izlasījāt šo pamācību, un ceru, ka tiksimies atkal nākamnedēļ!