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.

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, kā arī varat izmantot nosaukumus argumentā, 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 ar kreiso lielāko 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ā ir 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:

  1. Vērtība, kuru vēlaties uzmeklēt (tiek dēvēta arī par uzmeklējamo vērtību).

  2. 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.

  3. 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.

  4. 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 pirmajā kolonnā (kolonnā B) kolonnā kolonnā table_array B2:E7 un atgriež Olilookup no kolonnas (kolonnas C), kas atrodas kolonnā 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) otrajā kolonnā (kolonnā B) diapazonā A2:C7 un atgriež Vērtību Vērtība.

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, kas 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ē tā darbinieka dzimšanas datumu, kurš atbilst diapazonā A2:E table_array 7 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 funkcija 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āka 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.

  1. Kopējiet tabulu, kurā ir kopējie lauki, jaunā darblapā un piešķiriet tai nosaukumu.

  2. Noklikšķiniet uz > datu rīki > Relācijas, lai atvērtu dialoglodziņu Relāciju pārvaldība.

    Dialoglodziņš Relāciju pārvaldība
  3. 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 formulas VLOOKUP formula.

    • Saistītas uzmeklēšanas tabulas lauks (kolonna), kurā ir jūsu jaunajā kolonnā laukā ievadītie 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.

  4. 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 Attorney (the 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).

  5. 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 #VALUE!.

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, saņemsit #VALUE! kļūdas vērtību #VALUE!.

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.

Kļūdas #SPILL! šū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.

Vai nepieciešama papildu palīdzība?

Vienmēr varat vērsties pie speciālista Excel Tech kopienā vai saņemt atbalstu Answers kopienā.

Skatiet arī

Ātrās uzziņas karte: VLOOKUP atsvaidzinātājs
Ātrās uzziņas karte: VLOOKUP ieteikumi problēmu novēršanai
Kļūdas labošana #VALUE! funkcijā VLOOKUP
Kļūdas #N/A labošana funkcijā VLOOKUP
Formulas pārskats programmā Excel
Kā nepieļaut bojātas formulas
Kļūdu noteikšana formulās
Excel funkcijas (alfabētiskā secībā)
Excel funkcijas (pēc kategorijas)
VLOOKUP (bezmaksas priekšskatījums)

Vai nepieciešama papildu palīdzība?

Paplašiniet savas prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Microsoft Office Insider programmas dalībniekiem

Vai šī informācija bija noderīga?

Cik lielā mērā esat apmierināts ar valodas kvalitāti?
Kas ietekmēja jūsu pieredzi?

Paldies par jūsu atsauksmēm!

×