VLOOKUP (funkcija VLOOKUP)

Padoms.: Mēģiniet izmantot jauno funkciju XLOOKUP , uzlabotu VLOOKUP versiju, kas darbojas jebkurā virzienā un pēc noklusējuma atgriež precīzas atbilstības, atvieglojot un ērtāku lietošanu nekā tās priekšteči.

Izmantojiet funkciju VLOOKUP, ja vēlaties atrast tabulā datus vai diapazonu pēc rindas. Piemēram, uzmeklējiet kādu automašīnas detaļu pēc detaļas numura vai atrodiet darbinieka vārdu, pamatojoties uz darbinieka ID.

Funkcija VLOOKUP vienkāršākajā formā izsaka:

=VLOOKUP(ko vēlaties uzmeklēta, kur to vēlaties meklēt, kolonnas numuru diapazonā, kurā ietverta atgriežamā vērtība, atgriezt aptuvenu vai precīzu atbilstību, kas norādīta kā 1/TRUE, vai 0/FALSE).

Jūsu pārlūkprogramma neatbalsta video. Instalējiet Microsoft Silverlight, Adobe Flash Player vai Internet Explorer 9.

Padoms.: VLOOKUP var sakārtot datus tā, lai jūsu meklētā vērtība (Augļi) būtu pa kreisi no atgriežamās vērtības (summa), ko vēlaties atrast.

Izmantojiet funkciju VLOOKUP, lai tabulā uzmeklētu vērtību.

Sintakse

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

Piemērs.

=VLOOKUP(A2,A10:C20,2,TRUE)
=VLOOKUP("Vanags",B2:E7,2,FALSE)
=VLOOKUP(A2,'Klienta informācija'! A:F,3,FALSE)

Argumenta nosaukums	Apraksts
lookup_value (obligāta vērtība)	Uzmeklējamā vērtība. Uzmeklamā vērtībai ir jābūt šūnu diapazona pirmajā kolonnā, ko norādāt *argumentā table_array* . Piemēram, ja *table-array aptver* šūnas B2:D7, lookup_value ir jābūt kolonnā B. *Lookup_value* var būt vērtība vai atsauce uz šūnu.
tabulas_masīvs (obligāta vērtība)	Šūnu diapazons, kurā funkcija VLOOKUP meklēs argumenta *uzmeklējamā_vērtība* vērtību un atgriežamo vērtību. Varat izmantot nosauktu diapazonu vai tabulu, un argumentā var izmantot nosaukumus, nevis šūnu atsauces. Šūnu diapazona pirmajā kolonnā ir jābūt ietvertai *vērtībai lookup_value*. Tāpat šūnu diapazonā ir jāiekļauj arī atgriežamā vērtība, ko vēlaties atrast. Papildinformācija par to, kā atlasīt diapazonus darblapā.
col_index_num (obligāta vērtība)	Kolonnas numurs (sākot ar 1 kolonnas kreisajā pusē lielākajai daļai *table_array*), kurā ir ietverta atgriežamā vērtība.
diapazona_uzmeklēšana (neobligāta vērtība)	Loģiska vērtība, kas norāda, vai funkcijai VLOOKUP jāatrod aptuvena vai precīza atbilstība: Aptuvenā atbilstība — 1/TRUE pieņem, ka pirmā kolonna tabulā tiek kārtota vai nu skaitliskā, vai alfabētiskā secībā, un pēc tam meklēs tuvāko vērtību. Šī ir noklusējuma metode, kas tiks izmantota, ja nenorādīsit citu metodi. Piemēram, =VLOOKUP(90,A1:B100,2,TRUE). Precīza atbilstība — 0/FALSE meklē precīzu vērtību pirmajā kolonnā. Piemēram, =VLOOKUP("Mājs",A1:B100,2,FALSE).

Kā sākt darbu

Lai izveidotu VLOOKUP sintaksi, ir nepieciešama četru veidu informācija:

Vērtība, kuru vēlaties uzmeklēt (tiek dēvēta arī par uzmeklējamo vērtību).
Diapazons, kurā atrodas uzmeklējamā vērtība. Ņemiet vērā: lai VLOOKUP darbotos pareizi, uzmeklēšanas vērtībai vienmēr ir jābūt diapazona pirmajā kolonnā. Piemēram, ja jūsu uzmeklēšanas vērtība ir šūnā C2, tad diapazonam ir jāsākas ar C.
Kolonnas numurs diapazonā, kurā ir ietverta atgriežamā vērtība. Piemēram, ja kā diapazonu norādāt B2:D11, B būs pirmā kolonna, C — otrā kolonna utt.
Ja vēlaties iegūt atgriežamās vērtības aptuvenu atbilstību, norādiet vērtību TRUE, bet, ja vēlaties iegūt precīzu atbilstību, norādiet vērtību FALSE. Ja nenorādīsit neko, noklusējuma vērtība vienmēr būs TRUE vai aptuvenā atbilstība.

Tagad saliksim visu kopā, kā tas ir norādīts tālāk.

=VLOOKUP(uzmeklējamā vērtība, diapazons, kurā ir ietverta uzmeklējamā vērtība, kolonnas numurs diapazonā, kurā ir ietverta atgriežamā vērtība, Aptuvenā atbilstība (TRUE) vai Precīza atbilstība (FALSE)).

Piemēri

Tālāk ir sniegti daži funkcijas VLOOKUP piemēri.

1. piemērs

=VLOOKUP (B3,B2:E7,2,FALSE)

VLOOKUP meklē vērtības Vērtības vārds šūnā table_array B2:E7 pirmajā kolonnā (kolonnā B table_array. False atgriež precīzu atbilstību.

2. piemērs

=VLOOKUP (102,A2:C7,2,FALSE)

VLOOKUP meklē precīzu uzvārda 102 (lookup_value) atbilstību (FALSE) diapazona A2:C7 otrajā kolonnā (kolonnā B) un atgriež Sengāns.

3. piemērs

=IF(VLOOKUP(103,A1:E7,2,FALSE)="Souse","Located","Not found")

IF pārbauda, vai funkcija VLOOKUP atgriež Sousa kā darbinieka uzvārdu korelā ar 103 (lookup_value) argumentā A1:E7 (table_array). Tā kā uzvārds, kas atbilst vērtībai 103, ir Leal, nosacījums IF ir aplams un tiek rādīts Nav atrasts.

4. piemērs

=INT(YEARFRAC(DATE(2014;6;30);VLOOKUP(105;A2:E7;5;FLASE);1))

VLOOKUP meklē darbinieka dzimšanas datumu, kas atbilst diapazonam A2:E7 (table_array) 109 (lookup_value) un atgriež vērtību 03/04/1955. Pēc tam YEARFRAC atņem šo dzimšanas datumu no 2014/6/30 un atgriež vērtību, kas pēc tam tiek pārvērsta par INY par veselo skaitli 59.

5. piemērs

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,"Darbinieks nav atrasts",VLOOKUP(105,A2:E7,2,FALSE))

IF pārbauda, vai VLOOKUP atgriež vērtību uzvārdam no kolonnas B 105 (lookup_value). Ja funkcija VLOOKUP atrod uzvārdu, funkcija IF parādīs uzvārdu, pretējā gadījumā funkcija IF atgriež vērtību Darbinieks nav atrasts. ISNA nodrošina, ka gadījumā, ja VLOOKUP #N/A, kļūda tiek aizstāta ar Darbinieks nav atrasts, nevis #N/A.

Šajā piemērā atgrieztā vērtība ir Gīks, kas ir uzvārds, kas atbilst 105.

Funkciju VLOOKUP var izmantot, lai apvienotu vairākas tabulas vienā tabulā, ja vien vienā tabulā ir lauki kopā ar citām tabulām. Tas var būt īpaši noderīgi, ja nepieciešams koplietot darbgrāmatu ar lietotājiem, kuriem ir vecākas Excel versijas un kas neatbalsta datu līdzekļus ar vairākām tabulām kā datu avotus — apvienojot avotus vienā tabulā un mainot datu līdzekļa datu avotu uz jauno tabulu, datu līdzekli var izmantot vecākās Excel versijās (ja datu līdzekli atbalsta vecākā versija).

Darblapa ar kolonnām, kurās izmantota funkcija VLOOKUP, lai iegūtu datus no citām tabulām

Šeit kolonnām A–F un H ir vērtības vai formulas, kurās tiek lietotas tikai darblapā esošās vērtības, bet pārējās kolonnās tiek izmantota funkcija VLOOKUP un kolonnas A (Klienta kods) un kolonnas B (Attorney) vērtības, lai iegūtu datus no citām tabulām.

Kopējiet tabulu, kurā ir kopējie lauki, jaunā darblapā un piešķiriet tai nosaukumu.
Noklikšķiniet uz > rīki vai > relācijas , lai atvērtu dialoglodziņu Relāciju pārvaldība.
Katrai uzskaitītai relācijai ņemiet vērā:
- Lauks, kas saista tabulas (norādīts dialoglodziņa iekavās). Šī ir lookup_value VLOOKUP formulai.
- Saistītās uzmeklēšanas tabulas nosaukums. Šī ir table_array VLOOKUP formulā.
- Saistītas uzmeklēšanas tabulas lauks (kolonna), kurā ir jūsu jaunajā kolonnā tiešie dati. Šī informācija netiek rādīta relāciju pārvaldības dialoglodziņā — lai skatītu izgūstamo lauku, ir jāskatās saistītajā uzmeklēšanas tabulā. Ir jāņem vērā kolonnas numurs (A=1) — šī ir col_index_num formulas vērtība.
Lai pievienotu lauku jaunajai tabulai, ievadiet savu VLOOKUP formulu pirmajā tukšajā kolonnā, izmantojot 3. darbībā apkopoto informāciju.

Mūsu piemērā kolonnā G tiek izmantots Jurists ( lookup_value), lai iegūtu rēķina likmes datus no ceturtās kolonnas (col_index_num = 4) no darblapas tabulas Attorney, tblAttorneys ( table_array) ar formulu =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).

Formula var izmantot arī šūnas atsauci un diapazona atsauci. Mūsu piemērā tas būtu =VLOOKUP(A2,'Attorneys'! A:D,4,FALSE).
Turpiniet pievienot laukus, līdz ir visi jums nepieciešami lauki. Ja mēģināt sagatavot darbgrāmatu, kurā ir datu līdzekļi, kas izmanto vairākas tabulas, mainiet datu līdzekļa datu avotu uz jauno tabulu.

Problēma	Kļūmes iemesls
Tika atgriezta nepareiza vērtība	Ja *range_lookup* ir TRUE vai tiek izlaists, pirmā kolonna ir jāsakārto alfabētiskā vai skaitliskā secībā. Ja pirmā kolonna nav sakārtota, atgrieztā vērtība var būt tāda, kādu neesat paredzējis. Vai nu sakārtojiet pirmo kolonnu, vai precīzajai atbilstībai izmantojiet FALSE.
#N/A šūnā	Ja *diapazona_uzmeklēšana* ir TRUE, saņemsit kļūdas vērtību #N/A, ja *uzmeklējamā_vērtība* esošā vērtība būs mazāka par mazāko vērību masīva *tabulas_masīvs* pirmajā kolonnā. Ja *diapazona_uzmeklēšana* ir FALSE, kļūdas vērtība #N/A norāda, ka precīzs skaitlis nav atrasts. Papildinformāciju par kļūdu #N/A atrisināšanu funkcijā VLOOKUP skatiet rakstā Kļūdas #N/A labošana funkcijā VLOOKUP.
#REF! šūnā	Ja *col_index_num* ir lielāks par kolonnu skaitu *tabulas masīvā*, tiks parādīts #REF! kļūdas vērtību. Papildinformāciju par kļūdu #REF! atrisināšanu funkcijā VLOOKUP skatiet rakstā Kļūdas #REF! labošana.
#VALUE! šūnā	Ja *table_array* ir mazāks par 1, jūs saņemsit #VALUE! kļūdas vērtību. Papildinformāciju par kļūdas #VALUE! atrisināšanu funkcijā VLOOKUP skatiet rakstā Kļūdas #VALUE! labošana funkcijā VLOOKUP.
#NAME? šūnā	Kļūdas vērtība #NAME? parasti nozīmē, ka formulā trūkst pēdiņu. Lai atrastu personas vārdu, pārliecinieties, vai formulā vārdu esat ielicis pēdiņās. Piemēram, formulā =VLOOKUP("Vanags",B2:E7,2,FALSE) ievadiet vārdu kā "Vanags". Papildinformāciju skatiet rakstā Kļūdas #NAME! labošana.
#IZPLEŠANĀS! šūnā	Šī #SPILL kļūda parasti nozīmē, ka jūsu formula paļaujas uz uzmeklējamo vērtību netieši no krustošanās un izmanto visu kolonnu kā atsauci. Piemēram, =VLOOKUP(A:A,A:C,2,FALSE). Šo problēmu var novērst, enkurojot uzmeklēšanas atsauci ar operatoru @, piemēram, =VLOOKUP(@A:A,A:C,2,FALSE). Vai arī varat izmantot tradicionālo metodi VLOOKUP un atsaukties uz vienu šūnu, nevis visu kolonnu: =VLOOKUP(A2,A:C,2,FALSE).

Rīkojieties šādi	Kāpēc
Izmantojiet range_lookup absolūtās atsauces	Ja izmantosit absolūtās atsauces, varēsit aizpildīt formulu tā, lai tā vienmēr veiktu uzmeklēšanu tieši tajā precīzajā uzmeklēšanas diapazonā. Papildinformācija par to, kā izmantot absolūtās šūnu atsauces.
Nesaglabājiet skaitļu vai datumu vērtības kā tekstu.	Meklējot skaitļu vai datu vērtības, pārliecinieties, vai *table_array* pirmās kolonnas dati netiek glabāti kā teksta vērtības. Pretējā gadījumā funkcija VLOOKUP var atgriezt nepareizu vai negaidītu vērtību.
Pirmās kolonnas kārtošana	Pirms funkcijas VLOOKUP izmantošanas sakārtojiet masīva *tabulas_masīvs* pirmo kolonnu, ja argumenta diapazona_uzmeklēšana vērtība ir TRUE.
Aizstājējzīmju izmantošana	Ja *range_lookup* ir FALSE *un lookup_value* ir teksts, varat izmantot aizstājējzīmes — jautājuma zīmi (?) un zvaigznīti () lookup_value.* Jautājuma zīme aizvieto jebkuru atsevišķu rakstzīmi. Zvaigznīte aizvieto jebkuru rakstzīmju secību. Ja vēlaties atrast tieši jautājuma zīmi vai zvaigznīti, rakstzīmes priekšā ierakstiet tildi (~). Piemēram, =VLOOKUP("Fontan?",B2:E7,2,FALSE) meklēs visus vērtības Vērtības ar dažādām pēdējā burta instancēm.
Pārbaudiet, vai datos nav kļūdainu rakstzīmju.	Ja pirmajā kolonnā meklējat teksta vērtības, pārbaudiet, vai dati pirmajā kolonnā nesākas un nebeidzas ar atstarpi, tajos netiek nepareizi izmantotas taisnās (' vai ") un ieapaļās (‘ vai “) pēdiņas vai nedrukājamas rakstzīmes. Šādos gadījumos VLOOKUP var atgriezt negaidītu vērtību. Lai iegūtu precīzus rezultātus, mēģiniet noņemt šūnā aiz tabulas vērtībām esošās beigu atstarpes, izmantojot funkciju CLEAN vai funkciju TRIM.