Kaip naudotis "VLOOKUP" "Excel"

VLOOKUP yra viena labiausiai naudingų funkcijų Excel, ir tai taip pat yra viena iš mažiausiai suprantamų.Šiame straipsnyje mes parodome VLOOKUP kaip realaus pavyzdžio. Mes sukursime naudingą sąskaitos faktūros šabloną , skirtą fiktyviam kompanijai.

VLOOKUP yra Excel funkcija .Šiame straipsnyje bus daroma prielaida, kad skaitytojas jau turi supratimą apie "Excel" funkcijas ir gali naudoti tokias pagrindines funkcijas kaip "SUM", "VIDUTINIS" ir "TODAY".Paprasčiausiam naudojimui VLOOKUP yra duomenų bazė funkcija, tai reiškia, kad ji veikia su duomenų bazės lenteles, arba paprasčiau, sąraše yra dalykų Excel darbalapyje. Kokie dalykai? Na, bet koks dalykas. Galite turėti darbo lapą, kuriame yra jūsų kolekcijoje esančių darbuotojų ar produktų ar klientų ar kompaktinių diskų sąrašas arba žvaigždės naktiniame danguje. Tai tikrai nesvarbu.

Čia yra sąrašo arba duomenų bazės pavyzdys.Šiuo atveju tai yra produktų, kuriuos parduoda mūsų fiktyvi kompanija, sąrašas:

Paprastai tokie sąrašai turi tam tikrą unikalų identifikatorių kiekvienam sąrašo elementui. Tokiu atveju unikalus identifikatorius yra stulpelyje "Prekės kodas".Pastaba: norint dirbti VLOOKUP su duomenų baze / sąrašu, šiame sąraše

turi būti stulpelis, kuriame yra unikalus identifikatorius( arba "raktas" arba "ID"), o šis stulpelis turi būti pirmas stulpelis lentelėje.Mūsų pavyzdinė duomenų bazė atitinka šį kriterijų.

Sunkiausia VLOOKUP naudojimo dalis yra tai, ko tiksliai suprasti. Taigi pažiūrėkime, ar galime pirmiausia tai suprasti:

VLOOKUP renka informaciją iš duomenų bazės / sąrašo, remdamasis pateiktu unikaliu identifikatoriumi.

Pirmiau pateiktame pavyzdyje jūs įtraukėte VLOOKUP funkciją į kitą skaičiuoklę naudodami elemento kodą, ir jis grąžins jums atitinkamo elemento aprašymą, jo kainą arba jo prieinamumą( jo "kiekį atsargoje"), kaip aprašyta jūsųoriginalus sąrašas. Kurios iš šių informacijos dalių jums praeis? Na, jūs nuspręsite tai kurdami formulę.

Jei viskas, ko jums reikia - tai viena duomenų dalis iš duomenų bazės, tai būtų daug sunkumų, norint sukurti formulę su VLOOKUP funkcija. Paprastai tokia funkcija naudojama pakartotinai naudoti skaičiuoklę, pvz., Šabloną.Kiekvieną kartą, kai kas nors įveda galiojantį kodą, sistema gaus visą reikiamą informaciją apie atitinkamą elementą.

Sukursime tokio pavyzdžio pavyzdį: sąskaitos faktūros šablonas , kurį galime pakartotinai panaudoti mūsų fiktyviai kompanijai.

Pirmiausia pradedame "Excel", o mes sukuriame tuščią sąskaitą:

Tai, kaip jis ketina dirbti: asmuo, kuris naudoja sąskaitos faktūros šabloną, užpildys eilutę elementų kodų stulpelyje "A", o sistema gaus kiekvieno elementoaprašymas ir kaina iš mūsų produktų duomenų bazės.Ši informacija bus naudojama skaičiuojant kiekvieno elemento eilutę( darant prielaidą, kad mes įvesti galiojantį kiekį).

Kad būtų lengviau išlaikyti šį pavyzdį, mes rasime produkto duomenų bazę atskirame to paties darbo žurnalo lape:

Iš tikrųjų labiau tikėtina, kad produkto duomenų bazė bus atskiroje knygoje. Tai nedaug skiriasi nuo VLOOKUP funkcijos, kuriai tikrai nereikia rūpintis, ar duomenų bazė yra tame pačiame lape, kitame lape ar visiškai kitoje darbo knygoje.

Taigi mes sukūrėme savo produktų duomenų bazę, kuri atrodo taip:

Norėdami patikrinti VLOOKUP formulę, kurią ketiname parašyti, pirmiausia įveskite galiojančią elemento kodą į tuščią sąskaitą faktūrą laukelyje A11:

Toliau mesperkelkite aktyviosios ląstelės į ląstelę, kurioje mes norime išsaugoti informaciją, gautą iš duomenų bazės pagal VLOOKUP.Įdomu, kad tai yra žingsnis, kurį dauguma žmonių sugeba. Toliau paaiškinti: mes ketiname sukurti VLOOKUP formulę, kuri gaus aprašymą, atitinkantį elemento kodą lange A11.Kur mes norime, kad šis aprašymas būtų pateikiamas, kai mes jį gauname?Žinoma, ląstelėje B11.Taigi, kur mes rašome VLOOKUP formulę: ląstelėje B11.Pasirinkite ląstelę B11 dabar.

Mes turime rasti visų galimų funkcijų, kurias "Excel" turi pasiūlyti, sąrašą, kad galėtume pasirinkti VLOOKUP ir gauti tam tikrą pagalbą užpildydami formulę.Tai nustatoma, pirmiausia spustelėjus skirtuką Formulės , tada spustelėdami Įterpti funkciją :

Pasirodo langelis, leidžiantis mums pasirinkti bet kurią iš "Excel" funkcijų.

Norėdami rasti tai, ko ieškome, galėtume įvesti paieškos terminą, pvz., "Paiešką"( nes mūsų funkcija yra paieškos funkcija).Sistema grąžins mums sąrašą visų su užklausa susijusių funkcijų Excel. VLOOKUP yra antrasis sąraše. Pasirinkite jį paspaudę OK .

Parodytas funkcijų argumentų langas , paragino mus visus argumentus ( arba parametrus ), reikalingus norint užbaigti VLOOKUP funkciją.Galite galvoti apie šį langelį kaip apie funkciją, užduodančią mums šiuos klausimus:

1. Kokį unikalų identifikatorių ieškote duomenų bazėje?
2. Kur yra duomenų bazė?
3. Kokią informaciją iš duomenų bazės, susietą su unikaliu identifikatoriumi, norite gauti jums?

Pirmieji trys argumentai rodo pusjuodžiu , nurodant, kad jie yra privalomi argumentai( be jų VLOOKUP funkcija yra neišsami ir negrąžina teisingos vertės).Ketvirtasis argumentas nėra paryškintas, o tai reiškia, kad jis yra neprivalomas:

Mes užpildysime argumentus, iš viršaus į apačią.

Pirmasis argumentas, kurį turime užbaigti, yra Lookup_value argumentas. Funkcijai mums reikia pasakyti, kur rasti unikalų identifikatorių( elemento kodas šiuo atveju), kad jis turėtų grąžinti aprašymą.Mes turime pasirinkti anksčiau įvestą prekės kodą( A11).

Spustelėkite pirmojo argumento dešinėje pusėje esančią parinkiklio piktogramą:

Tada spustelėkite vieną kartą langelyje, kurioje yra elemento kodas( A11), ir paspauskite Įveskite :

A11 vertė įterpiama į pirmąjį argumentą.

Dabar turime įvesti reikšmę Table_array argumentui. Kitaip tariant, mes turime pasakyti "VLOOKUP", kur rasti duomenų bazę / sąrašą.Paspauskite pasirinkimo piktogramą šalia antrojo argumento:

Dabar suraskite duomenų bazę / sąrašą ir pasirinkite visą sąrašą - be antraštės eilutės .Mūsų pavyzdyje duomenų bazė yra atskiroje darbalapyje, todėl mes pirmiausia spragtelėjame tą lapą:

Toliau mes pasirenkame visą duomenų bazę, be antraštės eilutės:

. .. ir paspauskite Įveskite .Ląstelių, atstovaujančių duomenų bazei( šiuo atveju "Produktų duomenų bazė" A2: D7 ") diapazonas automatiškai įvedamas į antrą argumentą.

Dabar turime įvesti trečiąjį argumentą, Col_index_num .Mes naudojame šį argumentą, norėdami nurodyti VLOOKUP, kuri informacija iš duomenų bazės, susijusi su mūsų elemento kodu A11, kurią norime grąžinti mums.Šiuo konkrečiu pavyzdžiu mes norime, kad aprašymas būtų grąžintas mums. Jei pažvelgsite į duomenų bazės užduotį, pastebėsite, kad stulpelis "Aprašymas" yra antroji stulpelis duomenų bazėje. Tai reiškia, kad mes turime įvesti reikšmę "2" į "Col_index_num" langelį:

Svarbu pažymėti, kad čia mes neimame "2", nes stulpelis "Aprašymas" yra B stulpelyjetai darbalapyje. Jei duomenų bazė atsidūrė darbalaukio stulpelyje K , mes vis tiek turėtume įvesti "2" šiame lauke, nes stulpelis "Aprašymas" yra antras stulpelis ląstelių rinkinyje, kurį pasirinkome nurodydami "Table_array".

Galiausiai turime nuspręsti, ar įvesti reikšmę į galutinį VLOOKUP argumentą, Range_lookup .Šis argumentas reikalauja arba tikrosios ar klaidingos vertės, arba jis turėtų būti paliktas tuščias. Naudojant VLOOKUP su duomenų bazėmis( kaip ir tiesa 90% laiko), būdas nuspręsti, ką įtraukti į šį argumentą, gali būti laikoma tokia:

Jei pirmoji duomenų bazės stulpelis( stulpelis, kuriame yra unikalūs identifikatoriai)yra suskirstytas pagal abėcėlę / skaitmenimis didėjančia tvarka, tada į šį argumentą galima įvesti tikrosios vertę arba palikti tuščią.

Jei pirmajame duomenų bazės stulpelyje yra rūšiuojami , o ne , arba jis yra surūšiuoti mažėjančia tvarka, tuomet turi į šį argumentą įvesti klaidingą vertę

Kadangi mūsų duomenų bazės stulpelyje yra , o ne , surūšiuoti,į šį argumentą įvedame klaidingą :

Tai viskas!Įvedome visą VLOOKUP reikalingą informaciją, kad galėtume grąžinti reikiamą vertę.Paspauskite mygtuką OK ir pastebėsite, kad aprašymas, atitinkantis elemento kodą "R99245", teisingai įvestas į ląstelę B11:

. Mums sukurta formulė yra tokia:

Jei įvesime į skirtingą elemento kodą į ląstelęA11, mes pradėsime matyti VLOOKUP funkcijos galią: apibūdinimo elementas pakeičiamas, kad atitiktų naujo elemento kodą:

Mes galime atlikti panašią veiksmų paketą, kad elemento kaina grįžtų į langelį E11.Atkreipkite dėmesį, kad naujoji formulė turi būti sukurta lange E11.Rezultatas bus toks:

. .. ir formulė atrodys taip:

Atkreipkite dėmesį, kad vienintelis skirtumas tarp dviejų formulių yra trečiasis argumentas( Col_index_num ) pasikeitė nuo "2" į "3"( nesmes norime, kad duomenys būtų perkelti iš 3 stulpelio duomenų bazėje).

Jei nuspręstume pirkti 2 iš šių daiktų, mes įvesime "2" į ląstelę D11.Tada mes pateiksime paprastą formulę į ląstelę F11, kad gautume visą eilutę:

= D11 * E11

. .. kuri atrodo taip. ..

Užpildžius sąskaitos faktūros šabloną

Mes iki šiol daug sužinojome apie VLOOKUP.Tiesą sakant, mes išmokome viską, ką ketiname išmokti šiame straipsnyje. Svarbu pažymėti, kad VLOOKUP gali būti naudojamas kitomis aplinkybėmis, be duomenų bazių.Tai yra mažiau įprasta, ir gali būti aprašyta būsimose "How-To Geek" straipsniuose.

Mūsų sąskaitos faktūros šablonas dar nėra baigtas. Norėdami jį užbaigti, mes atliksime šiuos veiksmus:

1. . Mes pašalinsime pavyzdžio elemento kodą iš ląstelių A11 ir "2" iš ląstelės D11.Dėl to mūsų naujai sukurtos VLOOKUP formulės bus rodomos klaidos pranešimų:
   
   Mes galime tai išspręsti tinkamai naudojant Excel IF() ir ISBLANK() funkcijas. Mes pakeičia formulę iš šio. .. = VLOOKUP( A11, "Produkto duomenų bazė"! A2: D7,2, FALSE) . .. į šį. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, "Produktų duomenų bazė"'! A2: D7,2, FALSE))
   
2. Mes nukopijuosime formules ląstelėse B11, E11 ir F11 žemyn iki likusios sąskaitos faktūros eilučių eilutės. Atkreipkite dėmesį, kad jei mes tai padarysime, susidariusios formulės neteisingai pateiks duomenų bazės lentelę.Galėtume tai išspręsti, pakeisdami duomenų bazės ląstelių nuorodas į absoliučias ląstelių nuorodas. Arba - ir dar geriau - mes galėtume sukurti diapazono pavadinimą visai produktų duomenų bazei( pvz., "Products") ir naudoti šį diapazono pavadinimą vietoj ląstelių nuorodų.Formulė pasikeistų iš šios. .. = IF( ISBLANK( A11), VLOOKUP( A11, "Product Database"! A2: D7,2, FALSE)) . .. į šį. .. = IF( ISBLANK( A11), "", VLOOKUP( A11, Produktai, 2, FALSE)) . .. ir , tada nukopijuokite formules į likusius sąskaitos faktūros eilučių daiktus.
3. Mes tikriausiai "užrakinsime" ląsteles, kuriose yra mūsų formulės( tiksliau , atrakinkite kitas ląsteles) ir apsaugokite darbo lapą, kad užtikrintume, kad mūsų kruopščiai sukonstruotos formulės nebūtų netyčia perrašytos, kai kas nors ateina užpildytisąskaitoje faktūroje.
4. Mes išsaugosime failą kaip šabloną , kad jį galėčiau pakartotinai naudoti visi mūsų kompanijoje

. Jei jaučiamės tikrai protingas, mes sukursime visų mūsų klientų duomenų bazę kitame darbalapyje ir naudokimekliento ID, įvestas į langelį F5, kad automatiškai užpildyti kliento vardą ir adresą ląstelėse B6, B7 ir B8.

Jei norite praktikuoti su VLOOKUP arba tiesiog pamatyti mūsų gautą sąskaitų faktūros šabloną, ją galite atsisiųsti iš čia.