Funkcija VLOOKUP programmā Excel meklē vērtību šūnu diapazonā, pēc tam atgriež vērtību, kas atrodas tajā pašā rindā, kurā ir meklētā vērtība.
VLOOKUP, kas apzīmē “Vertical Lookup”, ir meklēšanas funkcija, kas meklē vērtību diapazona vistālāk kreisajā kolonnā (pirmajā kolonnā) un atgriež paralēlo vērtību no kolonnas labajā pusē. Funkcija VLOOKUP meklē vērtību tikai uz augšu (no augšas uz leju) tabulā, kas sakārtota vertikāli.
Piemēram, pieņemsim, ka darblapā ir krājumu saraksts ar tabulu, kurā norādīti preču nosaukumi, iegādes datums, daudzums un cena. Pēc tam mēs varētu izmantot VLOOKUP citā darblapā, lai no krājumu darblapas iegūtu daudzumu un cenu noteiktam preces nosaukumam.
Funkcija VLOOKUP sākotnēji var šķist biedējoša, taču patiesībā to ir diezgan viegli izmantot, kad saprotat, kā tā darbojas. Šajā apmācībā mēs parādīsim, kā programmā Excel izmantot funkciju VLOOKUP.
VLOOKUP Sintakse un argumenti
Ja plānojat izmantot funkciju VLOOKUP, jums jāzina tās sintakse un argumenti.
Funkcijas VLOOKUP sintakse:
=UZKLĀŠANA(uzmeklēšanas_vērtība,tabulas_masīvs,kolas_indeksa_numurs,[diapazona_uzmeklēšana])Šī funkcija sastāv no 4 parametriem vai argumentiem:
- lookup_value: Tas norāda vērtību, kuru meklējat norādītās tabulas masīva pirmajā kolonnā. Uzmeklēšanas vērtībai vienmēr ir jāatrodas vistālāk kreisajā pusē (meklēšanas tabulas slejā.
- table_array: Šī ir tabula (šūnu diapazons), kurā vēlaties meklēt vērtību. Šī tabula (meklēšanas tabula) var būt tajā pašā darblapā vai citā darblapā, vai pat citā darbgrāmatā.
- col_index_num: Tas norāda tabulas masīva kolonnas numuru, kuram ir vērtība, kuru vēlaties iegūt.
- [range_lookup]: Šis parametrs norāda, vai vēlaties iegūt precīzu vai aptuvenu atbilstību. Tā ir vai nu TRUE, vai FALSE, ievadiet “FALSE”, ja vēlaties precīzu vērtību, vai ievadiet “TRUE”, ja aptuvenā vērtība atbilst.
Izmantojot funkciju VLOOKUP programmā Excel
Izpētīsim, kā programmā Microsoft Excel izmantot VLOOKUP.
Pamatpiemērs
Lai izmantotu VLOOKUP, vispirms ir jāizveido sava datu bāze vai tabula (skatiet tālāk).
Pēc tam izveidojiet tabulu vai diapazonu, kurā vēlaties meklēt, un izņemiet vērtības no meklēšanas tabulas.
Pēc tam atlasiet šūnu, kurā vēlaties iegūt izvilkto vērtību, un ievadiet šādu VLOOKUP formulu. Piemēram, mēs vēlamies uzmeklēt tālruņa numuru 'Ena', pēc tam mums ir jāievada uzmeklēšanas vērtība kā B13, A2:E10 kā tabulas masīvs, 5 tālruņa numura kolonnas numuram un FALSE, lai atgrieztu precīzu vērtību. Pēc tam nospiediet taustiņu Enter, lai pabeigtu formulu.
=VLOOKUP(B13,A2:E10,5,FALSE)Tabulas diapazons nav jāievada manuāli, jūs varat vienkārši atlasīt diapazonu vai tabulu, izmantojot peli argumentam table_array. Un tas tiks automātiski pievienots argumentam.
Atcerieties, lai tas darbotos, meklēšanas vērtībai ir jāatrodas mūsu meklēšanas tabulas kreisajā malā (A2:E10). Turklāt Lookup_value nav obligāti jāatrodas darblapas A slejā, tai vienkārši ir jābūt meklējamā diapazona vistālāk kreisājai kolonnai.
Vlookup izskatās pareizi
Funkcija VLOOKUP var skatīties tikai tabulas labajā pusē. Tā meklē vērtību tabulas vai diapazona pirmajā kolonnā un izvelk atbilstošo vērtību no kolonnas labajā pusē.
Precīza atbilstība
Funkcijai Excel VLOOKUP ir divas saskaņošanas metodes, tās ir: precīzas un aptuvenas. Parametrs "range_lookup" funkcijā VLOOKUP norāda, kāda veida meklējat, precīzu vai aptuvenu.
Ja ievadāt range_lookup kā “FALSE” vai “0”, formula meklē vērtību, kas ir tieši vienāda ar lookup_value (tas var būt cipars, teksts vai datums).
=VLOOKUP(A9,A2:D5,3,FALSE)
Ja tabulā netiks atrasta precīza atbilstība, tiks parādīta kļūda #N/A. Mēģinot meklēt “Japāna” un atgriezt tai atbilstošo vērtību 4. slejā, tiek parādīta kļūda #N/A, jo tabulas pirmajā slejā nav vārda “Japāna”.
Pēdējā argumentā varat ievadīt skaitli “0” vai “FALSE”. Viņi abi programmā Excel nozīmē vienu un to pašu.
Aptuvenā atbilstība
Dažreiz jums nav obligāti nepieciešama precīza atbilstība, pietiek ar labāko atbilstību. Šādos gadījumos varat izmantot aptuvenās atbilstības režīmu. Lai atrastu aptuvenu atbilstību, iestatiet funkcijas pēdējo argumentu uz “TRUE”. Noklusējuma vērtība ir TRUE, kas nozīmē, ka, ja nepievienosiet pēdējo argumentu, funkcija pēc noklusējuma izmantos aptuveno atbilstību.
=VLOOKUP(B10,A2:B7,2,TRUE)Šajā piemērā mums nav nepieciešams precīzs rezultāts, lai atrastu atbilstošu atzīmi. Viss, kas mums nepieciešams, ir atzīmes, lai būtu šajā punktu diapazonā.
Ja VLOOKUP atrod precīzu atbilstību, tas atgriezīs šo vērtību. Ja iepriekš minētajā piemērā formula nevar atrast look_up vērtību 89 pirmajā kolonnā, tā atgriezīs nākamo lielāko vērtību (80).
Pirmais mačs
Ja tabulas kreisajā kolonnā ir dublikāti, VLOOKUP atradīs un atgriezīs pirmo atbilstību.
Piemēram, VLOOKUP ir konfigurēts, lai atrastu uzvārdu vārdam “Mia”. Tā kā ir 2 ieraksti ar vārdu “Mia”, funkcija atgriež uzvārdu pirmajam ierakstam “Bena”.
Aizstājējzīmju atbilstība
Funkcija VLOOKUP ļauj atrast daļēju atbilstību noteiktai vērtībai, izmantojot aizstājējzīmes. Ja vēlaties atrast vērtību, kas satur uzmeklēšanas vērtību jebkurā pozīcijā, pievienojiet zīmi & (&), lai apvienotu mūsu uzmeklēšanas vērtību ar aizstājējzīmi (*). Izmantojiet zīmes “$”, lai izveidotu absolūtas šūnu atsauces, un pirms vai pēc uzmeklēšanas vērtības pievienojiet aizstājējzīmi “*”.
Piemērā šūnā B13 ir tikai daļa no uzmeklēšanas vērtības (Vin). Tātad, lai veiktu daļēju atbilstību norādītajām rakstzīmēm, aiz šūnas atsauces savienojiet aizstājējzīmi “*”.
=VLOOKUP($B$13&"*",$A$2:$E$10,3,FALSE)
Vairāki meklējumi
Funkcija VLOOKUP ļauj izveidot dinamisku divvirzienu meklēšanu, kas atbilst gan rindām, gan kolonnām. Nākamajā piemērā VLOOKUP ir iestatīts, lai veiktu uzmeklēšanu, pamatojoties uz vārdu (Mayra) un pilsētu. B14 sintakse ir:
=UZmeklēšana(B13,A2:E10,MATCH(A14,A1:E1,0),0)
Kā veikt VLOOKUP no citas lapas programmā Excel
Parasti funkcija VLOOKUP tiek izmantota, lai atgrieztu atbilstošas vērtības no atsevišķas darblapas, un to reti izmanto ar datiem vienā darblapā.
Lai veiktu Vlookup no citas Excel lapas, bet tajā pašā darbgrāmatā, ievadiet lapas nosaukumu pirms table_array ar izsaukuma zīmi (!).
Piemēram, lai meklētu darblapas “Produkti” šūnas A2 vērtību diapazonā A2:B8 darblapā “ItemPrices” un atgrieztu atbilstošu vērtību no kolonnas B:
=VLOOKUP(A2,preces Cenas!$A$2:$C$8,2,FALSE)Zemāk esošajā attēlā ir redzama tabula darblapā “ItemPreces”.
Ievadot formulu VLOOKUP darblapas “Produkti” C slejā, tā iegūst atbilstošus datus no darblapas “ItemPrices”.
Kā veikt VLOOKUP no citas darbgrāmatas programmā Excel
Varat arī meklēt vērtību pilnīgi citā darbgrāmatā. Ja vēlaties veikt VLOOKUP no citas darbgrāmatas, darbgrāmatas nosaukums ir jāiekļauj kvadrātiekavās, kam seko lapas nosaukums pirms table_array ar izsaukuma zīmi (!) (kā parādīts tālāk).
Piemēram, izmantojiet šo formulu, lai meklētu citas darblapas šūnas A2 vērtību no darbgrāmatas “Item.xlsx” darblapas “ItemPrices”:
=VLOOKUP(A2,[Item.xls]Vienumu cenas!$A$2:$B$8,2,FALSE)Vispirms atveriet abas darbgrāmatas, pēc tam sāciet ievadīt formulu darblapas (Produkta darblapas) šūnā C2 un, nonākot pie argumenta table_array, dodieties uz galveno datu darbgrāmatu (Item.xlsx) un atlasiet tabulas diapazonu. Tādā veidā jums nav manuāli jāievada darbgrāmatas un darblapas nosaukums. Ievadiet pārējos argumentus un nospiediet taustiņu Enter, lai pabeigtu funkciju.
Pat ja aizverat darbgrāmatu, kurā ir uzmeklēšanas tabula, VLOOKUP formula turpinās darboties, taču tagad varat redzēt pilnu aizvērtās darbgrāmatas ceļu, kā parādīts nākamajā ekrānuzņēmumā.
Izmantojiet VLOOKUP funkciju no Excel lentes
Ja nevarat atcerēties formulas, vienmēr varat piekļūt funkcijai VLOOKUP no Excel lentes. Lai piekļūtu VLOOKUP, Excel lentē atveriet cilni “Formulas” un noklikšķiniet uz ikonas “Meklēt un atsauce”. Pēc tam nolaižamās izvēlnes apakšā atlasiet opciju VLOOKUP.
Pēc tam dialoglodziņā “Funkciju argumenti” ievadiet argumentus. Pēc tam noklikšķiniet uz pogas "Labi".
Piemērā mēs tabulā meklējām vārdu “Sherill”, lai atgrieztu tā atbilstošo stāvokli kolonnā D.
Mēs ceram, ka no šī raksta uzzinājāt, kā programmā Excel izmantot funkciju VLOOKUP. Ja vēlaties uzzināt vairāk par to, kā lietot programmu Excel, skatiet citus ar Excel saistītos rakstus.