VLOOKUP ir viens no Excel visnoderīgākajām funkcijām, un tas ir arī viens no vismazāk saprotamiem. Šajā rakstā mēs izskaidrojam VLOOKUP kā reālu piemēru. Mēs izveidosim izmantojamu Rēķina veidne 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 lietojot, VLOOKUP ir a datu bāze funkcija, kas nozīmē, ka tā darbojas ar datu bāzes tabulām - vai vienkārši vienkārši saraksti no lietām Excel darblapā. Kādas lietas? Labi jebkura sava 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.
Šeit ir piemērs sarakstam vai datu bāzei. Šajā gadījumā tas ir produktu saraksts, kurus pārdod mūsu fiktīvie uzņēmumi:
Parasti šiem 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, šim sarakstam ir jābūt slejai, kurā ir unikālais identifikators (vai "atslēga" vai "ID"), un šai slejai jābūt tabulas pirmajai kolonnai. Mūsu paraugu datu bāze atbilst šim kritērijam.
Sarežģītākā VLOOKUP lietošanas daļa ir precīzi saprotams, kas tas ir. Tātad, redzēsim, vai mēs vispirms varam to saprast:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
Iepriekš minētajā piemērā jūs ievietotu VLOOKUP funkciju citā izklājlapā ar objekta kodu, un tas atgriezīsies vai nu uz atbilstošā objekta aprakstu, tā cenu vai tā pieejamību (tā "Krājuma" daudzumu), kā aprakstīts oriģinālā sarakstu. 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.
Izveidosim piemēru: An Rēķina veidne ka mēs atkal varam atkal izmantot mūsu fiktīvā uzņēmumā.
Vispirms sākam Excel, un mēs izveidojam tukšu rēķinu:
Lai vienkāršotu šo piemēru, mēs atrodam produktu datubāzi atsevišķā lapā vienā un tajā pašā darbgrāmatā:
Tāpēc esam izveidojuši mūsu produktu datu bāzi, kas izskatās šādi:
Mums ir nepieciešams atrast sarakstu ar visām iespējamām funkcijām, kuras Excel var piedāvāt, lai mēs varētu izvēlēties VLOOKUP un saņemt palīdzību formulas aizpildīšanai. Tas ir atrodams, vispirms noklikšķinot uz Formulas cilni un pēc tam noklikšķinot uz Ievietot funkciju:
Lai atrastu to, ko meklējam, mēs varētu ievadīt meklēšanas vienumu, piemēram, "lookup" (tāpēc, ka mums interesējošā funkcija ir uzmeklēšana 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ā. Izvēlieties to klikšķi labi.
The Funkcijas Argumenti parādās lodziņš, liekot mums visu argumenti (vai parametri), kas nepieciešams, lai pabeigtu VLOOKUP funkciju. Jūs varat iedomāties šo lodziņu kā funkciju, kas liek mums uzdot šādus jautājumus:
- Kādu unikālo identifikatoru jūs meklējat datubāzē?
- Kur ir datu bāze?
- Kuru informāciju no datubāzes, kas saistīta ar unikālo identifikatoru, jūs vēlaties saņemt no jums?
Tiek parādīti pirmie trīs argumenti treknrakstā, norādot, ka viņi ir obligāts 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.
Pirmais arguments, kas mums jāpabeidz, ir Lookup_value arguments. Funkcijai mums ir nepieciešams pateikt, kur atrast unikālo identifikatoru ( lietas kods šajā gadījumā), ka tas 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:
Tagad mums jāievada vērtība Table_array arguments. Citiem vārdiem sakot, mums ir jāpasaka VLOOKUP kur atrast datubāzi / sarakstu. Noklikšķiniet uz selektora ikonas blakus otrajam argumentam:
… 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 preces būtu apraksts atgriezās pie mums. Ja paskatās uz datubāzes darblapu, jūs pamanīsiet, ka slejā "Apraksts" ir otrais kolonna datu bāzē. Tas nozīmē, ka mums jāievada vērtība "2" Col_index_num kaste:
Svarīgi atzīmēt, ka šeit mēs neievērojam "2", jo slejā "Apraksts" ir norāde B šajā darblapā. Ja datu bāzei ir bijis sākt no kolonnas K no darba lapas, mēs joprojām ievadītu lauku "2" šajā laukā, jo sleja "Apraksts" ir otra sleja atlasīto šūnu kopai, norādot "Table_array".
Visbeidzot, mums ir jāizlemj, vai ievadīt vērtību galīgajā VLOOKUP argumentā, Range_lookup. Šis arguments prasa vai nu a taisnība vai viltus vērtība, vai arī tas būtu jāatstāj tukšs. Izmantojot VLOOKUP ar datubāzēm (kā tas ir 90% laika), veids, kā izlemt, ko iekļaut šajā argumentā, var domāt šādi:
If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
Tā kā mūsu datu bāzes pirmā kolonna ir nē sakārtoti, mēs ievadām viltus vērā šo argumentu:
Tieši tā! Mēs esam ievadījuši visu VLOOKUP pieprasīto informāciju, lai atgrieztu nepieciešamo vērtību. Noklikšķiniet uz labi pogu un pamanīsit, ka aprakstu, kas atbilst pozīcijas kodam "R99245", ir pareizi ievadīts šūnā B11:
Ja mēs ievadīsim a atšķirīgs elementa 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 vienumu cena atgriezās šūnā E11. Ņemiet vērā, ka jaunā formula ir jāveido šūnā E11. Rezultāts izskatīsies šādi:
Ņemiet vērā, ka vienīgā atšķirība starp abām formulām ir trešais arguments (Col_index_num) ir mainījies no "2" uz "3" (jo mēs vēlamies, lai dati tiktu iegūti no 3. datu kolonnas).
Ja mēs nolēmām nopirkt 2 no šiem priekšmetiem, mēs ievadīsim "2" šūnā D11. Tad mēs ievadīsim vienkāršu formulu šūnā F11, lai iegūtu kopējo līniju:
=D11*E11
… kas izskatās kā šis …
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 paveiktu, mēs veiktu sekojošo:
-
Mēs izņemtu parauga vienības kodu no šūnas A11 un no "2" no šūnas D11. Tādējādi mūsu jaunizveidotās VLOOKUP formulas parādīs kļūdas ziņojumus:
Image Mēs to varam izlabot, racionāli izmantojot Excel IF () un ISBLANK () funkcijas. Mēs mainām savu formulu no šī … = VLOOKUP (A11, "Product Database"! A2: D7,2, FALSE) … šim … = IF (ISBLANK (A11), "", VLOOKUP (A11, "Product Database"! A2: D7,2, FALSE))
- Mēs nokopētu 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 datubāzes tabulā. Mēs varētu to novērst, mainot šūnu atsauces datu bāzei uz absolūti šūnu atsauces. Alternatīvi - un vēl labāk - mēs varētu izveidot diapazona nosaukums par visu produktu datubāzi (piemēram, "Produkti") un izmantojiet šo diapazona nosaukumu, nevis šūnu atsauces. Formula mainītos no šī … = IF (ISBLANK (A11), "", VLOOKUP (A11, "Product Database"! A2: D7,2, FALSE)) … šim … = IF (ISBLANK (A11), "", VLOOKUP (A11, Produkti, 2, FALSE)) …un tad nokopējiet formulas uz pārējām rēķina vienuma rindām.
- Mēs droši vien "bloķētu" šūnas, kas satur mūsu formulas (vai drīzāk atslēgt the cits šūnas) un pēc tam aizsargāt darblapu, lai nodrošinātu, ka mūsu rūpīgi izveidotās formulas nejauši netiek pārrakstītas, kad kāds nāk, lai aizpildītu rēķinu.
- Mēs saglabātu failu kā veidne, lai to varētu atkārtoti izmantot ikviens mūsu uzņēmums
Ja mēs jutos tiešām gudrs, mēs izveidotu visu mūsu klientu datubāzi citā darblapā un pēc tam izmantotu klienta ID, kas ierakstīts šūnā F5, lai automātiski aizpildītu klienta vārdu un adresi šūnās B6, B7 un B8.
