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. Faktinę kompaniją sukursime naudingą Sąskaitų faktūrų šabloną .

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 duomenų bazės funkcija, o tai reiškia, kad ji veikia su duomenų bazės lenteles, arba paprasčiausiai sąrašų 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 nėra svarbu.

Č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: jei naudojate VLOOKUP funkciją dirbti su duomenų baze / sąrašu, šiame sąraše turi turėti stulpelį su unikaliu identifikatoriumi (arba "raktas" arba "ID") ir , tas stulpelis turi būti pirmas stulpelis lentelėje . Aukščiau pateikta mūsų pavyzdinė duomenų bazė atitinka šį kriterijų.

Sunkiausia VLOOKUP naudojimo dalis yra tai, ko tai suprantama. Tada 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ę su elemento kodas, ir jis grąžins jums arba atitinkamo elemento aprašymą, jo kainą ar jos prieinamumą (jo "Kiekis sandėlyje"), kaip aprašyta pradiniame sąraše. Kurios iš šių informacijos dalių jums praeis? Gerai, jūs nuspręsite tai kurdami formulę.

Jei jums tereikia tik vienos informacijos iš duomenų bazės, tai būtų daug sunkumų, kad galėtumėte kurti formulę su funkcija VLOOKUP tai Paprastai tokia funkcija naudojama pakartotinai naudoti skaičiuoklę, pvz., Šabloną. Kiekvieną kartą, kai kas nors įveda galiojantį kodo elementą, sistema surinks visą reikiamą informaciją apie atitinkamą elementą.

Sukursime tokį pavyzdį: Sąskaitų faktūrų šablonas , kurį galime pakartotinai panaudoti mūsų fiktyvi kompanija.

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

Štai kaip bus dirbti: asmuo, kuris naudoja sąskaitos faktūros šabloną, užpildys eilutę elementų kodų stulpelyje "A" ir sistema gaus kiekvieno produkto aprašymą ir kainą iš mūsų produktų duomenų bazės. Ši informacija bus naudojama kiekvieno elemento eilutės sumai apskaičiuoti (darant prielaidą, kad mes įvesti galiojantį kiekį).

Norint išlaikyti šį pavyzdį paprasta, mes nustatysime produkto duomenų bazę atskirame toje pačioje knygoje esančiame lape:

Iš tikrųjų labiau tikėtina, kad produktų 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 kitokioje darbo knygoje.

Taigi sukūrėme savo produktų duomenų bazę, kuri atrodo kaip šis:

Norėdami patikrinti VLOOKUP formulę, kurią ketiname parašyti, pirmiausia įveskite galiojantį elemento kodą į mūsų tuščią sąskaitą-ląstelę A11:

Tada mes perkelėsime aktyvią langelį į langelį, kuriame mes norime, kad informacija, kurią iš duomenų bazės gautų VLOOKUP, būtų saugoma. Įdomu, kad tai yra žingsnis, kurį dauguma žmonių sugeba. Toliau paaiškinti: mes ketiname sukurti VLOOKUP formulę, kuri aptiks 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.

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, pirmiausia spustelėjus skirtuką Formulės , tada spustelėję Įterpti funkciją :

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

Norint rasti tą, ko ieškome, galėtume įvesti paieškos terminą, pvz., "Paiešką" (nes mūsų funkcija yra lookup funkcija). Sistema grąžins mums sąrašą visų su peržvalga susijusių "Excel" funkcijų. VLOOKUP yra antrasis sąraše. Pasirinkite jį spragtelėję Gerai .

Parodomas laukelis Function Arguments , ragindamas mums visus reikalingus argumentus (arba parametrus ) kad atliktumėte VLOOKUP funkciją. Galite pagalvoti apie šį langelį kaip apie funkciją, užduodančią mums šiuos klausimus:

1. Koks unikalus identifikatorius ieškote duomenų bazėje?
2. Kur yra duomenų bazė?
3. Kokia informacija iš duomenų bazės, susieta su Unikalus identifikatorius, ar norėtumėte jį gauti?

Pirmieji trys argumentai yra parodyta paryškintuoju , nurodant, kad jie yra privalomi argumentai (VLOOKUP funkcija yra neišsami be jie negrąžins teisingos vertės). Ketvirtas argumentas nėra paryškintas, o tai reiškia, kad jis yra neprivalomas:

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

Pirmasis argumentas, kurį turime užbaigti, yra argumentas Lookup_value . 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ą elemento kodą (A11 formatu).

Paspauskite pirmojo argumento dešinėje pusėje esančią selektoriaus piktogramą:

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

"A11" vertė įtraukiama į pirmąjį argumentą.

Dabar turime įvesti reikšmę Table_array argumentui. Kitaip tariant, mes turime pasakyti "VLOOKUP", kur rasti duomenų bazę / sąrašą. Paspauskite ant selektoriaus piktogramos š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, taigi mes pirmiausia spragtelėjame tą lapą:

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

... ir paspauskite Įvesti .

Dabar mes 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. Šiame konkretajame pavyzdyje norime, kad elementas būtų aprašymas grąžintas mums. Jei pažvelgsite į duomenų bazės užduotį, pastebėsite, kad stulpelis "Aprašymas" yra stulpelis antroji duomenų bazėje. Tai reiškia, kad turime įvesti reikšmę "2" į langelį Col_index_num :

Svarbu pažymėti, kad mes čia neįvedame "2", nes stulpelis "Aprašymas" yra stulpelyje B šiame darbalapyje. Jei duomenų bazė atsidūrė darbalaukio stulpelyje K , šiame lauke mes vis tiek turėtume įvesti "2", nes stulpelis "Aprašymas" yra antrasis langelių rinkinys, kurį pasirinkome nurodydami " Table_array ".

Galiausiai turime nuspręsti, ar įvesti reikšmę į galutinį VLOOKUP argumentą, Range_lookup . Šis argumentas reikalauja arba true , arba klaidingos reikšmė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 unikalus identifikatoriai) yra rūšiuojami pagal abėcėlę / skaitmenimis didėjimo tvarka, tada įmanoma įvesti reikšmę tiesa į šį argumentą arba palikite tuščią.

Jei pirmoji duomenų bazės stulpelis yra rūšiuojama ne arba surūšiuota mažėjančia tvarka, tuomet privalo įvesti reikšmė klaidinga į šį argumentą

Kadangi mūsų duomenų bazės stulpelyje yra išdėstyti ne , į šį argumentą įvedame klaidingą :

Tai yra tai! Įvedome visą VLOOKUP reikalingą informaciją, kad galėtume grąžinti reikiamą vertę. Spustelėkite mygtuką Gerai ir pastebėsite, kad aprašymas, atitinkantis elemento kodą "R99245", teisingai įvestas į langelį B11:

Mums sukurta formulė yra tokia:

Jei įvesime skirtingas elemento kodas į ląstelę A11, mes pradėsime matyti VLOOKUP funkcijos galią: apibūdinimo ląstelė pasikeičia, kad atitiktų naujo elemento kodą:

Mes galime atlikti panašias priemones, kad gautume elemento kaina sugrįžo į langelį E11. Atkreipkite dėmesį, kad naujoji formulė turi būti sukurta lange E11. Rezultatas atrodys taip:

... ir formulė atrodys taip:

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

Jei mes nusprendėme įsigyti 2 iš šių elementų, mes įvedame" 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ą

daug sužinojome apie VLOOKUP iki šiol. 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, atlikome šiuos veiksmus:

1. Mes pašalinsime pavyzdžio elemento kodą iš ląstelės A11 ir "2" iš ląstelės D11. Tai sukels mūsų naujai sukurtas VLOOKUP formules rodyti klaidų pranešimus:
   

   

   
   Mes galime tai išspręsti tinkamai naudojant Excel IF () ir ISBLANK () funkcijas. Mes pakeičiame savo formulę iš šios ... = VLOOKUP (A11, "Produktų 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 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 ląstelių nuorodas į duomenų bazę į absoliutų ląstelių nuorodas. Arba - ir dar geriau - galėtume sukurti visai produktų duomenų bazei (pvz., "Produktai") diapazono pavadinimą ir naudoti šio diapazono pavadinimą vietoj ląstelių nuorodų. Formulė pasikeistų iš šios ... = IF (ISBLANK (A11), ", VLOOKUP (A11, "Produktų duomenų bazė"! A2: D7,2, FALSE)) ... į šį ... = IF (ISBLANK (A11), ", VLOOKUP (A11, Produktai, 2, FALSE)) ... ir tada nukopijuokite formules į likusius sąskaitos faktūros eilutės elementus.
3. Mes greičiausiai "užrakins" ląsteles, kuriose yra mūsų formulės (ar greičiau atrakinti kitas langelius), o tada apsaugoti darbo lapą, siekiant užtikrinti, kad mūsų kruopščiai sukonstruotos formulės nebūtų atsitiktinai perrašyti, kai kas nors ateina užpildyti sąskaitą faktūrą.
4. Mes išsaugosime failą kaip šabloną , kad jį galėtų pakartotinai naudoti visi mūsų įmonės nariai

Jei mes jausme tikrai protingas, mes sukūrėme visų mūsų klientų duomenų bazę kitame darbalapyje, tada naudosime kliento ID, įvestą į langelį F5, kad automatiškai užpildyti kliento vardą ir adresą ląstelėse B6, B7 ir B8.

Jei jūs norėčiau praktikuoti su VLOOKUP, arba tiesiog pamatyti mūsų gautą sąskaitą T emlate, jį galima atsisiųsti iš čia.

Kaip naudotis Spike, jei norite kopijuoti ir įklijuoti tekstą "Microsoft Word"

"Word" yra mažai žinoma funkcija, vadinama "spike", kuri leidžia rinkti teksto ir / arba vaizdų blokus iš skirtingų "Word" dokumento vietose, tada įklijuokite visą tą turinį į kitą dokumento ar kito "Word" failo ar kitos programos vietą. "Spike in Word" pavadintas po senamadiško popieriaus laikiklio, ant kurio žmonės suklupo popierių, nes jie buvo padaryta su jais.

(how-to)

HTG Apžvalgos D-Link "AC3200" "Ultra" "Wi-Fi" maršrutizatorius: greitas erdvėlaivis jūsų "Wi-Fi" poreikiams

" Premium "maršrutizatorių rinka vis labiau įgauna įperkamus ir galingus maršrutizatorius. Net tokioje rinkoje D-Link AC3200 Ultra Wi-Fi maršrutizatorius pasižymi tiek dydžiu, tiek stilingu, tiek ir našumu. Kas yra D-Link AC3200 Ultra-Wi-Fi maršrutizatorius? D-Link AC3200 Ultra-Wi-Fi maršrutizatorius (arba DIR- 890L) (299 USD) yra dabartinis "D-Link" pagrindinis maršrutizatorius ir žvėris tiek fizinio dydžio, tiek galios požiūriu.

(how-to)