Kā lietot programmu VLOOKUP programmā Excel

VLOOKUP ir viena no Excel visnoderīgākajām funkcijām, un tā ir arī viena no vismazāk saprotamajām.Šajā rakstā mēs izskaidrojam VLOOKUP kā reālu piemēru. Mēs izveidosim izmantojamu rēķinu veidni fiktīvam uzņēmumam.

VLOOKUP ir Excel funkcija .Šajā rakstā tiks pieņemts, ka lasītājam jau ir izpratne par Excel funkcijām un tā var izmantot tādas pamata funkcijas kā SUM, VERAGE un TODAY.Visbiežāk lietotā VLOOKUP ir datu bāzes funkcija, kas nozīmē, ka tā darbojas ar datu bāzes tabulām - vai vienkārši - sarakstā ir iekļautas lietas Excel darblapā.Kādas lietas? Nu, jebkura veida lieta. Jums var būt darblapa, kurā ir savos kompaktdiska kolekcijā esošs darbinieku vai produktu saraksts vai klienti vai kompaktdiski vai nakts debesis zvaigznes. Tas nav īsti svarīgi.

Lūk, saraksta vai datubāzes piemērs.Šajā gadījumā tas ir to produktu saraksts, kurus mūsu fiktīvais uzņēmums pārdod:

Parasti šādiem sarakstiem ir sava veida unikāls identifikators katram saraksta vienumam.Šajā gadījumā unikālais identifikators ir kolonnā "Vienības kods".Piezīme. Lai VLOOKUP funkcija darbotos ar datubāzi / sarakstu, šajā sarakstā

jābūt kolonnā, kurā ir unikālais identifikators( vai "atslēga" vai "ID"), un kolonnai jābūt pirmajai kolonnai tabulā..Mūsu paraugu datu bāze atbilst šim kritērijam.

Vissvarīgākā daļa no VLOOKUP lietošanas ir izpratne tieši par to. Tātad, ļaujiet mums noskaidrot, vai mēs varam to vispirms saprast:

VLOOKUP izgūst informāciju no datubāzes / saraksta, pamatojoties uz piegādātās unikālā identifikatora gadījumu.

Iepriekš minētajā piemērā, jūs ievietotu VLOOKUP funkciju citā izklājlapā ar objekta kodu, un tas atgriezīs jums vai nu atbilstoša objekta aprakstu, tā cenu vai tā pieejamību( tās "Noliktavā"), kā aprakstīts jūsusākotnējais saraksts. Kurš no šīm informācijas daļām jūs nodos atpakaļ?Nu, jums tas jāizlemj, kad veidojat formulu.

Ja viss, kas jums nepieciešams, ir viena datu daļa no datu bāzes, būtu grūti iedziļināties, lai izveidotu formulu ar VLOOKUP funkciju tajā.Parasti jūs izmantojat šāda veida funkcionalitāti atkārtoti izmantojamajā izklājlapā, piemēram, veidnē.Katru reizi, kad kāds ievada derīgu objekta kodu, sistēma izgūst visu nepieciešamo informāciju par attiecīgo vienumu.

Izveidojiet šādu piemēru: rēķina veidne , kuru mēs atkal varam atkal izmantot mūsu fiktīvā uzņēmumā.

Vispirms mēs palaižam Excel un izveidojam tukšu rēķinu:

Tas ir, kā tā darbosies: persona, kas izmanto rēķina veidni, ailē "A" aizpildīs kodu elementu sēriju, un sistēma izgūs katru vienumuapraksts un cena no mūsu produktu datu bāzes.Šī informācija tiks izmantota, lai aprēķinātu katras vienības kopsummu( pieņemot, ka mēs ievadījām derīgu daudzumu).

Lai saglabātu šo piemēru vienkāršāk, mēs atrodam produktu datubāzi atsevišķā lapā tajā pašā darbgrāmatā:

Patiesībā visticamāk produkta datu bāze tiks izvietota atsevišķā darbgrāmatā.VLOOKUP funkcijai nav lielas nozīmes, jo nav svarīgi, vai datubāze atrodas tajā pašā lapā, citā lapā vai pilnīgi citā darbgrāmatā.

Tātad, mēs esam izveidojuši mūsu produktu datu bāzi, kas izskatās šādi:

Lai pārbaudītu VLOOKUP formulējumu, ko mēs gatavojamies rakstīt, vispirms ievadiet derīgu vienumu kodu mūsu tukšu rēķinu šūnā A11:

Tālāk mēspārvieto aktīvo šūnu uz šūnu, kurā mēs gribam, lai informācija, kas iegūta no datubāzes, tiktu saglabāta, izmantojot VLOOKUP.Interesanti, ka šis ir solis, ka lielākā daļa cilvēku kļūst nepareizi. Lai izskaidrotu tālāk: mēs gatavojamies izveidot VLOOKUP formulu, kas atveidos aprakstu, kas atbilst elementa kodam šūnā A11.Kur mēs vēlamies šo aprakstu nodot, kad to iegūstam? Protams, šūnā B11.Tātad, kur mēs rakstām VLOOKUP formula: šūnā B11.Izvēlieties šūnu B11 tūlīt.

Mums ir nepieciešams atrast visu pieejamo funkciju sarakstu, kuras Excel var piedāvāt, lai mēs varētu izvēlēties VLOOKUP un saņemt palīdzību formulas aizpildīšanai. To konstatē, vispirms noklikšķinot uz formulu cilnes un pēc tam uz Ievietot funkciju :

Atveras lodziņš, kas ļauj atlasīt kādu no programmā Excel pieejamajām funkcijām.

Lai atrastu meklēto, mēs varētu ievadīt meklēšanas vienumu, piemēram, "lookup"( jo mums interesējošā funkcija ir meklēšanas funkcija).Sistēma atgriezīs mums sarakstu ar visām ar meklēšanas funkcijām saistītajām funkcijām programmā Excel. VLOOKUP ir otrais sarakstā.Atlasiet to klikšķi OK .

Parādās funkciju argumenti lodziņš, kas pamudināja mūs visus argumentus ( vai parametrus ), kas nepieciešami, lai pabeigtu VLOOKUP funkciju. Jūs varat iedomāties šo lodziņu kā funkciju, kas lūdz mums šādus jautājumus:

1. Kādu unikālo identifikatoru jūs meklējat datubāzē?
2. Kur ir datu bāze?
3. Kādu informāciju no datu bāzes, kas saistīta ar unikālo identifikatoru, vēlaties iegūt jums?

Pirmie trīs argumenti ir parādīti treknrakstā , kas norāda, ka tie ir obligātie argumenti( VLOOKUP funkcija ir nepilnīga bez tām un neatgriezīs derīgu vērtību).Ceturtais arguments nav trekns, tas nozīmē, ka tas nav obligāts:

Mēs papildināsim argumentus, no augšas uz leju.

Pirmais arguments, kas mums jāaizpilda, ir LookUp_value arguments. Funkcijai mums ir jāpasaka, kur atrastu unikālo identifikatoru( pozīcijas kodu šajā gadījumā), ka tam būtu jāatgriež apraksts. Mums jāizvēlas agrāk ierakstītais objekta kods( A11).

Noklikšķiniet uz atlases ikonas pa labi no pirmā argumenta:

Tad vienu reizi noklikšķiniet uz šūnas, kurā ir vienuma kods( A11), un nospiediet . Ievadiet :

. "A11" vērtība tiek ievietota pirmajā argumentā.

Tagad mums jāievada Table_array argumenta vērtība. Citiem vārdiem sakot, mums ir jāpasaka VLOOKUP kur atrast datubāzi / sarakstu. Noklikšķiniet uz selektora ikonas blakus otrajam argumentam:

Tagad atrodiet datubāzi / sarakstu un atlasiet visu sarakstu - , neiekļaujot galvenes līniju .Mūsu piemērā datu bāze atrodas atsevišķā darblapā, tāpēc vispirms noklikšķinām uz šīs darblapas cilnes:

Tālāk mēs izvēlamies visu datubāzi, neiekļaujot galvenes rindu:

. .. un nospiediet . Ievadiet .Šūnu diapazons, kas pārstāv datubāzi( šajā gadījumā "Produktu datu bāze" A2: D7 ") tiek automātiski ievadīta otrajam argumentam.

Tagad mums jāievada trešais arguments, Col_index_num .Mēs izmantojam šo argumentu, lai norādītu uz VLOOKUP, kura informācijas daļa no datubāzes, saistīta ar mūsu preces kodu A11, mēs vēlamies, lai mēs to atgrieztu.Šajā konkrētajā piemērā mēs vēlamies, lai mēs atgrieztu preces aprakstu .Ja paskatās uz datubāzes darblapu, jūs pamanīsit, ka kolonna "Apraksts" ir otrā kolonna datu bāzē.Tas nozīmē, ka mums ir jāievada "2" vērtība lodziņā Col_index_num :

Ir svarīgi atzīmēt, ka šeit mēs neievadām "2", jo sleja "Apraksts" ir B slejāšo darba lapu. Ja datubāze notiks ar darblapas kolonnas K , mēs šajā laukā joprojām ierakstu "2", jo sleja "Apraksts" ir otra sleja atlasīto šūnu kopu, norādot "Table_array".

Visbeidzot, mums ir jāizlemj, vai ievadīt vērtību galīgajā VLOOKUP argumentā Range_lookup .Šis arguments pieprasa vai nu patieso vai false vērtību, vai arī tas jāpaliek tukšam. Izmantojot VLOOKUP ar datubāzēm( tāpat kā 90% laika), veids, kā izlemt, ko iekļaut šajā argumentā, var domāt šādi:

Ja datu bāzes pirmā kolonna( kolonna, kurā ir unikālie identifikatori)tiek sakārtoti alfabētiski / cipari augošā secībā, tad šajā argumentā ir iespējams ievadīt patieso vērtību vai atstāt tukšu.

Ja pirmā datu bāzes aile ir nevis šķirota vai sakārtota dilstošā secībā, tad jums jābūt ievadīt false vērtību šajā argumentā

Tā kā mūsu datu bāzes pirmā kolonna ir , nevis šķirota,šajā argumentā mēs ieejam false :

Tas ir viss! Mēs esam ievadījuši visu VLOOKUP pieprasīto informāciju, lai atgrieztu nepieciešamo vērtību. Noklikšķiniet uz pogas OK un ievērojiet, ka apzīmējums, kas atbilst pozīcijas kodam "R99245", ir pareizi ievadīts šūnā B11:

. Par mums izveidotā formula izskatās šādi:

Ja mēs ievadām dažādu vienuma kodu šūnāA11 mēs sāksim redzēt VLOOKUP funkcijas jaudu: apraksta šūna mainās, lai tas atbilstu jaunā objekta kodam:

Mēs varam veikt līdzīgu pasākumu kopumu, lai iegūtu objekta cenu atpakaļ uz šūnu E11.Ņemiet vērā, ka jaunā formula ir jāveido šūnā E11.Rezultāts izskatīsies šādi:

. .. un formula izskatīsies šādi:

Ņemiet vērā, ka vienīgā atšķirība starp divām formulām ir trešais arguments( Col_index_num ) ir mainīts no "2" uz "3"( jomēs gribam iegūt datus no datubāzes 3. kolonnas).

Ja mēs nolēmām nopirkt 2 no šiem priekšmetiem, mēs ievadītu "2" šūnā D11.Tad mēs ievadīsim vienkāršu formulu šūnā F11, lai iegūtu līnijas kopsummu:

= D11 * E11

. .. kas izskatās šādi. ..

Rēķina veidnes aizpildīšana

Līdz šim mēs uzzinājām daudz par VLOOKUP.Faktiski mēs esam iemācījušies visu, ko mēs gatavojamies mācīties šajā rakstā.Ir svarīgi atzīmēt, ka VLOOKUP var izmantot citiem apstākļiem, izņemot datubāzes. Tas ir retāk sastopams, un to var aplūkot turpmākajos "How-To Geek" rakstos.

Mūsu rēķina veidne vēl nav pabeigta. Lai to pabeigtu, mēs veiktu sekojošo:

1. Mēs izņemtu parauga vienības kodu no šūnas A11 un no "2" no šūnas D11.Tas radīs mūsu nesen izveidotās VLOOKUP formulas, lai parādītu kļūdas ziņojumus:
   
   Mēs varam to novērst, racionāli izmantojot Excel IF() un ISBLANK() funkcijas. Mēs mainām savu formulu no šīs. .. = VLOOKUP( A11, "Product Database"! A2: D7,2, FALSE) . .. uz šo. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, 'Product Database'! A2: D7,2, FALSE))
   
2. Mums vajadzētu kopēt formulas šūnās B11, E11 un F11 uz atlikušo rēķina pozīcijas rindu.Ņemiet vērā: ja mēs to izdarīsim, iegūtie formulas vairs netiks pareizi norādītas datu bāzes tabulā.Mēs to varētu salabot, mainot šūnu atsauces datubāzei uz absolūto šūnu atsaucēm. Alternatīvi - un vēl labāk - mēs varētu izveidot diapazona nosaukumu visai produktu datubāzei( piemēram, "Produkti") un izmantot šos diapazona nosaukumus, nevis šūnu atsauces. Formula mainās no šī. .. = IF( ISBLANK( A11), VLOOKUP( A11, "Product Database"! A2: D7,2, FALSE)) . .. uz šo. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Produkti, 2, FALSE)) . .. un , pēc tam nokopējiet formulas uz pārējām rēķina vienumu rindām.
3. Mēs droši vien "bloķētu" šūnas, kas satur mūsu formulas( vai drīzāk , atbloķētu citas šūnas) un pēc tam aizsargātu darblapu, lai nodrošinātu, ka mūsu rūpīgi izveidotās formulas netiek nejauši pārrakstītas, kad kāds nāk pie aizpildīšanasrēķinā.
4. Mēs varētu saglabāt failu kā veidni , lai to varētu atkārtoti izmantot visi mūsu kompānijas

. Ja mēs uzskatām, ka patiešām būtu gudrs, mēs izveidotu visu mūsu klientu datu bāzi citā darblapā un pēc tam izmantotuklienta ID, kas ierakstīts šūnā F5, lai automātiski aizpildītu klienta vārdu un adresi šūnās B6, B7 un B8.

Ja jūs vēlētos praktizēt ar VLOOKUP vai vienkārši aplūkojat mūsu iegūto rēķinu veidni, to var lejupielādēt no šejienes.