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. Sukursime tinkamą naudoti Sąskaitos faktūros šablonas fiktyvi kompanija.
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". Dažniausiai naudojamas VLOOKUP yra a duomenų bazė funkcija, tai reiškia, kad ji veikia su duomenų bazės lenteles - arba paprasčiau sąrašai dalykų "Excel" darbalapyje. Kokie dalykai? Gerai bet koks rūšiuoti 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.
Štai 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: kad funkcija VLOOKUP dirbtų su duomenų baze / sąrašu, šiame sąraše turi būti stulpelis, kuriame yra unikalus identifikatorius (arba "raktas" arba "ID"), ir šis stulpelis turi būti pirmas stulpelis lentelėje. Mūsų pavyzdinė duomenų bazė atitinka šį kriterijų.
Sunkiausia VLOOKUP naudojimo dalis yra tai, ko tai suprantama. Taigi pažiūrėkime, ar pirmiausia galime tai suprasti:
VLOOKUP retrieves information from a database/list based on a supplied instance of the unique identifier.
Anksčiau pateiktame pavyzdyje jūs įtraukėte funkciją VLOOKUP į kitą skaičiuoklę su elemento kodu ir grąžina jums atitinkamo produkto aprašymą, jo kainą arba jo prieinamumą (jo kiekį atsargoje), kaip aprašyta jūsų originalo sąrašas. Kurios iš šių informacijos dalių jums praeis? Na, jūs nuspręsite tai kurdami formulę.
Jei viskas, ko jums reikia - tai vienkartinė informacija iš duomenų bazės, tai būtų daug problemų, kad galėtume sukurti formulę su VLOOKUP funkcija. Paprastai tokia funkcija naudojama pakartotinai naudoti skaičiuoklę, pvz., Šabloną. Kiekvieną kartą, kai kas nors įveda galiojantį prekės kodą, sistema surinks visą reikiamą informaciją apie atitinkamą elementą.
Paremkime pavyzdį: An Sąskaitos faktūros šablonas kad galėtume vėl ir vėl panaudoti savo fiktyviai kompanijai.
Pirmiausia pradėsime "Excel", ir mes sukursime tuščią sąskaitą:
Tai, kaip jis ketina dirbti: asmuo, naudojantis sąskaitos faktūros šabloną, užpildys eilės elementų kodus stulpelyje "A", o sistema gaus kiekvieno produkto aprašymą ir kainą iš mūsų gaminių 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 nustatysime produkto duomenų bazę toje pačioje knygoje atskirame lape:
Taigi, sukūrėme savo produktų duomenų bazę, kuri atrodo taip:
Mums reikia rasti visų galimų funkcijų, kurias "Excel" gali pasiūlyti, sąrašą, kad galėtume pasirinkti VLOOKUP ir gauti tam tikrą pagalbą pildydami formulę. Tai randama pirmą kartą paspaudus mygtuką Formulės skirtuką ir tada spustelėdami Įterpti funkciją:
Norėdami rasti tą, ko ieškome, galėtume įvesti paieškos terminą, pvz., "Paiešką" (nes mūsų funkcija yra Paiešką funkcija). Sistema grąžins mums sąrašą visų su užklausa susijusių funkcijų Excel. VLOOKUP yra antrasis sąraše. Pasirinkite jį paspaudę Gerai.
The Funkcija Argumentai pasirodo langas, paskatinęs mus visus argumentai (arba parametrai), reikalingos VLOOKUP funkcijai užbaigti. Galite pagalvoti apie šį langelį kaip apie funkciją, užduodančią mums šiuos klausimus:
- Kokį unikalų identifikatorių ieškote duomenų bazėje?
- Kur yra duomenų bazė?
- Kokią informaciją iš duomenų bazės, susietą su unikaliu identifikatoriumi, norite gauti jums?
Pateikti pirmieji trys argumentai paryškinti, nurodant, kad jie yra privalomas Argumentai (VLOOKUP funkcija be jų yra neišsami ir negrąžina teisingos vertės). Ketvirtasis argumentas nėra paryškintas, o tai reiškia, kad jis neprivalomas:
Pirmasis argumentas, kurį turime užbaigti, yra Lookup_value argumentas. Funkcijai mums reikia pasakyti, kur rasti unikalų identifikatorių ( prekės kodas šiuo atveju), kad jis turėtų grąžinti aprašymą. Mes turime pasirinkti anksčiau įvestą prekės kodą (A11).
Pirmojo argumento dešinėje spustelėkite parinkiklio piktogramą:
Tada spustelėkite vieną kartą langelyje, kuriame yra elemento kodas (A11), ir paspauskite Įveskite:
Dabar turime įvesti vertę Table_array argumentas. Kitaip tariant, mes turime pasakyti "VLOOKUP", kur rasti duomenų bazę / sąrašą. Paspauskite pasirinkimo piktogramą šalia antro argumento:
… ir paspauskite Įveskite. Ląstelių, atstovaujančių duomenų bazei (šiuo atveju "Produktų duomenų bazė" A2: D7 ") diapazonas automatiškai įvedamas į antrą argumentą.
Dabar turime patekti į 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. Šiame konkretajame pavyzdyje mes norime turėti daiktą apibūdinimas grįžo pas mus. Jei pažvelgsite į duomenų bazės darbo lapą, pastebėsite, kad stulpelis "Aprašymas" yra antra stulpelis duomenų bazėje. Tai reiškia, kad mes turime įvesti reikšmę "2" į Col_index_num dėžė:
Svarbu pažymėti, kad mes čia neįvedame "2", nes stulpelis "Aprašymas" yra B toje darbalapyje esantis stulpelis. Jei duomenų bazė atsitiko stulpelyje K toje darbo vietoje mes vis tiek turėtume įvesti "2", nes stulpelis "Aprašymas" yra antras langelių rinkinys, kurį pasirinkome nurodydami "Table_array".
Galiausiai turime nuspręsti, ar įvesti vertę į galutinį VLOOKUP argumentą, " Range_lookup". Šis argumentas reikalauja a tiesa arba klaidinga vertė, arba ji turėtų būti palikta tuščia. Naudodamiesi VLOOKUP su duomenų bazėmis (kaip yra tiesa 90% laiko), būdas nuspręsti, ką įtraukti į šį argumentą, gali būti laikoma tokia:
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
Kaip pirmoji mūsų duomenų bazės stulpelis ne surūšiuoti, mes įvesti klaidinga į šį argumentą:
Viskas! Įvedome visą VLOOKUP reikalingą informaciją, kad galėtume grąžinti reikiamą vertę. Spustelėkite Gerai mygtuką ir pastebėsite, kad aprašymas, atitinkantis elemento kodą "R99245", buvo teisingai įvestas į langelį B11:
Jei įvesime a skiriasi 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šias priemones, kad gautume elementą kaina grįžo į ląstelę E11. Atkreipkite dėmesį, kad naujoji formulė turi būti sukurta lange E11. Rezultatas atrodys taip:
Atkreipkite dėmesį, kad vienintelis skirtumas tarp dviejų formulių yra trečiasis argumentas (Col_index_num) pasikeitė nuo "2" į "3" (nes mes norime, kad duomenys būtų gauti iš trečio 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
… atrodo taip …
Užpildykite sąskaitos faktūros šabloną
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 tai padaryti, mes atliksime šiuos veiksmus:
-
Mes pašalinsime pavyzdžio elemento kodą iš ląstelės A11 ir "2" iš ląstelės D11. Dėl to mūsų neseniai sukurtos VLOOKUP formulės parodys klaidos pranešimus:
Image Mes galime ištaisyti tai racionaliai naudojant "Excel" IF () ir ISBLANK () funkcijos. Mes pakeisime savo formulę iš šio … = VLOOKUP (A11, "Produktų duomenų bazė"! A2: D7,2, FALSE) …šiam… = IF (ISBLANK (A11), "", VLOOKUP (A11, "Produkto duomenų bazė"! A2: D7,2, FALSE))
- Mes nukopijuosime formules ląstelėse B11, E11 ir F11 iki likusios sąskaitos faktūros eilutės elementų. 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 langelių nuorodas į absoliutus ląstelių nuorodos. Arba - ir dar geriau - mes galėtume sukurti diapazono pavadinimas visai produktų duomenų bazei (pvz., "Produktai") ir naudoti šį diapazono pavadinimą vietoj ląstelių nuorodų. Formuluotė pasikeistų iš šios … = IF (ISBLANK (A11), "", VLOOKUP (A11, "Produkto duomenų bazė"! A2: D7,2, FALSE)) …šiam… = IF (ISBLANK (A11), "", VLOOKUP (A11, Produktai, 2, FALSE)) … ir tada nukopijuokite formules į likusius sąskaitos faktūros eilutės elementus.
- Mes tikriausiai "užrakinsime" ląsteles, kuriose yra mūsų formulės (ar greičiau atrakinti as kita ląstelės), tada apsaugokite darbalapį, kad būtų užtikrinta, kad mūsų kruopščiai sukonstruotos formulės nebūtų netyčia perrašytos, kai kas nors ateina užpildyti sąskaitą faktūrą.
- Mes išsaugotume failą kaip šablonas, kad ją galėtų pakartotinai naudoti visi mūsų įmonėje
Jei mes jaustume tikrai protingai, mes sukursime visų mūsų klientų duomenų bazę kitame darbalapyje, o tada naudosime kliento ID, įvestą į langelį F5, kad automatiškai užpildyti kliento vardą ir adresą ląstelėse B6, B7 ir B8.
