Raskite keletą duomenų laukų naudodami Excel VLOOKUP

Sujungdami "Excel" VLOOKUP funkciją su COLUMN funkcija , galime sukurti paieškos formules, kuriomis galima grąžinti keletą verčių iš vienos duomenų bazės ar duomenų lentelės eilutės.

Pavyzdyje, parodytame paveikslėlyje aukščiau, ieškos formulė leidžia lengvai grąžinti visas vertes, pvz., Kainą, dalies numerį ir tiekėją, susijusį su įvairiais aparatūros elementais.

01 iš 10

Grįžti keletą verčių su "Excel VLOOKUP"

Toliau išvardyti veiksmai sukuria paieškos formules, kurios matomos aukščiau esančiame paveikslėlyje, ir grąžins keletą reikšmių iš vienos duomenų įrašų.

Paieškos formoje reikalaujama, kad funkcija "COLUMN" būtų dedama į "VLOOKUP" viduje.

Funkcijos pritvirtinimas reiškia antrosios funkcijos įvedimą kaip vieną iš pirmosios funkcijos argumentų .

Šiame vadove funkcija COLUMN bus įrašyta kaip VLOOKUP stulpelio indekso skaičiaus argumentas.

Paskutinis žingsnio veiksmas apima kopijuoti paieškos formules į papildomus stulpelius, kad gautumėte papildomų verčių pasirinktai daliai.

Pamokų turinys

• Įvadas į mokymo duomenis
• Duomenų lentelėje įvardyto diapazono kūrimas
• VLOOKUP funkcijos pradžia
• Įvedimas "Lookup Value Argument" naudojant "Absolute Cell Reference"
• Įveskite lentelės masyvo argumentą
• Įeinama į "Nested" COLUMN funkciją
• VLOOKUP funkcijos užbaigimas
• Paieškos formos kopijavimas su užpildymo rankena
• Duomenų gavimas naudojant paieškos formą

02 iš 10

Įveskite mokymo duomenis

Pirmas žingsnis pamoka yra įvesti duomenis į "Excel" darbalapį .

Norėdami sekti instrukcijų veiksmus, įveskite duomenis, rodomus paveikslėlyje aukščiau, į šias langelius .

• Įrašykite didžiausią duomenų diapazoną į ląsteles nuo D1 iki G1
• Įveskite antrąjį diapazoną į ląsteles nuo D4 iki G10

Šio pamokymo metu sukurti paieškos kriterijai ir paieškos formulė bus įrašyti į darbalapio 2 eilutę .

Mokomoji medžiaga neapima formato, matomo paveikslėlyje, tačiau tai neturės įtakos, kaip veikia paieškos formulė.

Informacija apie formatavimo parinktis, panašias į anksčiau pateiktas, pateikiama šiame " Excel " formato vedlyje .

Tutorial žingsniai

1. Įveskite duomenis, kaip parodyta paveikslėlyje aukščiau, ląstelėse nuo D1 iki G10

03 iš 10

Duomenų lentelėje įvardyto diapazono kūrimas

Nurodytas diapazonas yra paprastas būdas paminėti daugybę duomenų formulėje. Užuot įrašę duomenų ląstelių nuorodas , galite tiesiog įvesti diapazono pavadinimą.

Antras vardo diapazono naudojimo privalumas yra tai, kad šio diapazono ląstelių nuorodos niekada nesikeičia net tada, kai formulė yra nukopijuota į kitas lakštas lakštuose.

Todėl diapazono pavadinimai yra alternatyva naudoti absoliutus ląstelių nuorodas, kad būtų išvengta klaidų, kai kopijuojamos formulės.

Pastaba: diapazono pavadinime nėra duomenų antraščių ar laukų pavadinimų (4 eilutė), bet tik pačių duomenų.

Tutorial žingsniai

1. Pažymėkite langelius nuo D5 iki G10 darbalapyje, kad juos pasirinktumėte
2. Spustelėkite " Name Box", esantį virš stulpelio A
3. Pavadinimo laukelyje įveskite "lentelė" (be kabučių)
4. Paspauskite ENTER klaviatūros klavišą
5. Dabar langeliuose nuo D5 iki G10 yra "lentelės" diapazonas. Vėliau naudosime VLOOKUP lentelių masyvo argumento pavadinimą

04 iš 10

VLOOKUP dialogo lango atidarymas

Nors galima tiesiog įvesti paieškos formules tiesiai į lakštą darbalapyje, daugeliui žmonių sunku išlaikyti sintaksę tiesiai, ypač sudėtingos formulės, tokios kaip tas, kurį mes naudojame šiame vadovėlyje.

Šiuo atveju alternatyva yra dialogo lango VLOOKUP naudojimas. Beveik visi "Excel" funkcijos turi dialogo langą, pagal kurį galite įvesti kiekvieną funkcijos argumentą atskiroje eilutėje.

Tutorial žingsniai

1. Spustelėkite lakšto langelį E2 - vietą, kurioje bus rodomos dvimatės paieškos formos rezultatai
2. Spustelėkite skirtuką " Formulės " juostos
3. Jei norite atidaryti išskleidžiamąjį sąrašą, spustelėkite parinktį " Ieškoti ir nuoroda"
4. Spustelėkite sąraše VLOOKUP , kad atidarytumėte funkcijos dialogo langą

05 iš 10

Įvedimas "Lookup Value Argument" naudojant "Absolute Cell Reference"

Paprastai paieškos vertė atitinka duomenų lauką pirmajame duomenų lentelės stulpelyje .

Mūsų pavyzdyje paieškos vertė nurodo įrangos dalį, apie kurią norime rasti informacijos, pavadinimą.

Paieškos duomenys yra leistini tipai:

• tekstiniai duomenys
• loginė vertė (tik TRUE arba FALSE)
• skaičius
• ląstelės nuoroda į reikšmę darbalapyje

Šiame pavyzdyje mes pateiksime langelio nuorodą, kurioje bus nurodytas pavadinimo pavadinimas - langelis D2.

Absoliutus ląstelių nuorodos

Vėliau žingsnyje pamoka bus nukopijuoti paieškos langelį E2 langelyje į F2 ir G2 langelius.

Paprastai, kai formulės yra nukopijuotos "Excel", ląstelių nuorodos keičia, kad atspindėtų jų naują vietą.

Jei taip atsitiks, D2 - langelio nuoroda į paieškos vertę - keisis, nes formulė yra nukopijuota, sukuriant klaidas ląstelėse F2 ir G2.

Kad būtų išvengta klaidų, ląstelės nuorodą D2 konvertuosime į absoliutų elemento nuorodą .

Absoliučios ląstelių nuorodos nesikeičia, kai formulės yra nukopijuotos.

Absoliučios ląstelių nuorodos sukurtos paspaudžiant klaviatūros klavišą F4 . Tokiu būdu pridedami dolerio žymenys po ląstelės nuoroda, pvz., $ D $ 2

Tutorial žingsniai

1. Dialogo lange spustelėkite eilutę " lookup_value"
2. Spustelėkite langelį D2, kad pridėtumėte šią langelio nuorodą į lookup_value eilutę. Tai ląstelė, kurioje įrašysime dalį pavadinimo, apie kurį mes ieškome informacijos
3. Nejudindami įterpimo taško, paspauskite klaviatūros klaviatūros klavišą F4, kad paverstų D2 į absoliučią ląstelių nuorodą $ D $ 2
4. Palikite dialogo langą VLOOKUP funkciją atidarykite kitą žingsnį

06 iš 10

Įveskite lentelės masyvo argumentą

Stalo masyvas - tai duomenų lentelė , kurią ieškos formulė ieško norėdama rasti norimą informaciją.

Lentelės masyvas turi turėti bent du duomenų stulpelius.

• pirmame stulpelyje yra paieškos verčių argumentas (ankstesnis žingsnis instrukcijoje)
• antroji ir bet kokie papildomi stulpeliai bus ieškomi pagal paieškos formulę, norėdami rasti norimą informaciją.

Stalo masyvo argumentas turi būti įrašytas kaip diapazonas, kuriame yra duomenų lentelės ląstelių nuorodos arba diapazono pavadinimas .

Šiame pavyzdyje mes naudosime diapazono pavadinimą, sukurtą mokymo pakopos 3 veiksme.

Tutorial žingsniai

1. Dialogo lange spustelėkite eilutę table_array
2. Įveskite šio argumento diapazono pavadinimą "Table" (be kabučių)
3. Palikite dialogo langą VLOOKUP funkciją atidarykite kitą žingsnį

07 iš 10

"COLUMN" funkcijos pritvirtinimas

Paprastai VLOOKUP tik grąžina duomenis iš vieno duomenų lentelės stulpelio , o šis stulpelis nustatomas pagal stulpelio indekso skaičiaus argumentą.

Tačiau šiame pavyzdyje mes turime tris stulpelius, į kuriuos norime grąžinti duomenis, todėl mums reikia lengvai pakeisti stulpelio indekso numerį be redagavimo mūsų paieškos formule.

Čia įvyksta funkcija "COLUMN". Įvedę ją kaip stulpelio indekso skaičiaus argumentą, ji pasikeis, nes paieškos formule nukopijuojama iš langelio D2 į lenteles E2 ir F2 vėliau pamoka.

Nesting Functions

Todėl COLUMN funkcija veikia kaip VLOOKUP stulpelio indekso skaičiaus argumentas .

Tai atliekama lizdant funkciją COLUMN VLOOKUP viduje dialogo lango eilutėje Col_index_num .

Funkcijos "COLUMN" įvedimas rankiniu būdu

Kai įdiegdami funkcijas, "Excel" neleidžia mums atidaryti antrosios funkcijos dialogo lango, kad įvestumėte jo argumentus.

Todėl COLUMN funkcija turi būti įvesta rankiniu būdu eilutėje Col_index_num .

Funkcija "COLUMN" turi tik vieną argumentą - referencinį argumentą, kuris yra langelio nuoroda.

Funkcijos "COLUMN" standartinio argumento pasirinkimas

Funkcijos "COLUMN" užduotis yra sugrąžinti stulpelio numerį, pateiktą kaip " Reference" argumentą.

Kitaip tariant, jis konvertuoja stulpelio raidę į skaičių, kurio stulpelis A yra pirmasis stulpelis, stulpelis B antrasis ir tt

Kadangi pirmasis duomenų laukas, kurį mes norime grąžinti, yra straipsnio kaina - tai yra duomenų lentelės antroji stulpelis - mes galime pasirinkti etaloninę nuorodą bet kuriai langelio B stulpelyje kaip etaloninį argumentą, kad gautumėte numerį 2 Col_index_num argumentas.

Tutorial žingsniai

1. Dialogo lange VLOOKUP funkcijos spustelėkite eilutę Col_index_num
2. Įveskite funkcijos pavadinimo stulpelį, o po jo - atvirą apvalią kronšteiną " ( "
3. Spustelėkite langelį B1 darbalapyje, kad įvedėte tą langelio nuorodą kaip " Reference" argumentą
4. Įveskite uždarymo skliaustą " ) ", kad užbaigtumėte funkciją "COLUMN"
5. Palikite dialogo langą VLOOKUP funkciją atidarykite kitą žingsnį

08 iš 10

Įveskite "VLOOKUP" diapazono paieškos argumentą

VLOOKUP "Range_lookup" argumentas yra loginė reikšmė (tik TRUE arba FALSE), kuri nurodo, ar norite, kad VLOOKUP surastų tikslų arba apytikstą atitikimą "Lookup_value".

• Jei TRUE arba jei šis argumentas yra praleistas, VLOOKUP grąžina tikslų atitikimą "Lookup_value" arba, jei tikslios atitikties nerasta, VLOOKUP grąžina kitą didžiausią vertę. Kad tai atliktų formulė, lentelės_ražinio pirmojo stulpelio duomenys turi būti rūšiuojami didėjančia tvarka .
• Jei FALSE, "VLOOKUP" naudos tik tikslią atitikmenį "Lookup_value". Jei pirmajame "Table_array" stulpelyje, kuris atitinka paieškos vertę, yra dvi ar daugiau verčių, naudojama pirmoji rasta reikšmė. Jei tikslios atitikties nerasta, grąžinama # N / A klaida.

Šiame vadove, kadangi mes ieškome konkrečios informacijos apie konkretų aparatūros elementą, mes nustatysime Range_lookup lygią False .

Tutorial žingsniai

1. Dialogo lange spustelėkite eilutę Range_lookup
2. Šioje eilutėje įrašykite žodį " klaidingas" , nurodydamas, kad mes norime, kad VLOOKUP grąžintų tikslią atitin kamą informaciją, kurią mes ieškome
3. Spustelėkite Gerai, kad atliktumėte paieškos formules ir uždarytumėte dialogo langą
4. Kadangi mes dar nepateikėme paieškos kriterijų į D2 langelį, ląstelėje E2 pasirodys # N / A klaida
5. Ši klaida bus ištaisyta, kai mes pridėsime paieškos kriterijus paskutiniame mokymo etape

09 iš 10

Paieškos formos kopijavimas su užpildymo rankena

Paieškos formos tikslas yra vienu metu gauti duomenis iš kelių stulpelių duomenų lentelėje.

Norėdami tai padaryti, paieškos formule turi būti visi laukai, iš kurių mes norime gauti informaciją.

Šiame vadove mes norime, kad ji gautų duomenis iš duomenų lentelės 2, 3 ir 4 stulpelių - tai yra kaina, dalies numeris ir tiekėjo vardas, kai mes nurodome dalies pavadinimą kaip "Lookup_value".

Kadangi duomenys yra išdėstyti į įprastą lapo lapą , mes galime nukopijuoti paieškos langelį E2 langelyje į langelius F2 ir G2.

Kai formulė yra nukopijuojama, "Excel" atnaujins santykinę ląstelių nuorodą funkcijoje COLUMN (B1), kad atspindėtų naujos formulės vietą.

Be to, Excel nepakeičia absoliučios ląstelių nuorodos $ D $ 2 ir lentelės pavadinimų diapazono , kai formulė yra nukopijuojama.

Yra daugiau nei vienas būdas kopijuoti duomenis "Excel", bet tikriausiai paprasčiausias būdas yra naudojant užpildymo rankenėlę .

Tutorial žingsniai

1. Spustelėkite langelį E2, kur yra paieškos formule, kad ji taptų aktyvia ląstele
2. Įdėkite pelės žymeklį ant juodo kvadrato apatiniame dešiniajame kampe. Rodyklė pasikeis prie pliuso ženklo " + " - tai yra užpildymo rankena
3. Spustelėkite kairįjį pelės mygtuką ir vilkite užpildymo rankenėlę į langelį G2
4. Paleiskite pelės mygtuką, o lange F3 turėtų būti dvimatė paieškos forma
5. Jei padaryta teisingai, ląstelės F2 ir G2 dabar taip pat turėtų būti # N / A klaida, esanti ląstelėje E2

10 iš 10

Įveskite paieškos kriterijus

Kai paieškos formule nukopijuota į reikiamas ląsteles, ji gali būti naudojama norint gauti informaciją iš duomenų lentelės.

Norėdami tai padaryti, įveskite norimo įrašyti elemento pavadinimą į langelį "Lookup_value" (D2) ir paspauskite ENTER klavišą.

Po to kiekviena ląstelė, kurioje yra paieškos formos, turėtų turėti kitą duomenų apie aparatūros elementą, kurį ieškote.

Tutorial žingsniai

1. Paspauskite ant langelio D2 darbalapyje
2. Įveskite piktogramą į langelį D2 ir paspauskite klaviatūros klavišą ENTER
3. E2-G2 ląstelėse turi būti pateikta tokia informacija:
   • E2 - 14,76 JAV doleriai - valdiklio kaina
   • F2 - PN-98769 - valdiklio dalies numeris
   • G2 - "Widgets Inc." - "widget" tiekėjo pavadinimas
4. Toliau išbandykite VLOOKUP masyvo formulę, įvesdami kitų dalių pavadinimą į ląstelę D2 ir stebėdami rezultatus ląstelėse E2-G2

Jei pateikiamas toks klaidos pranešimas kaip #REF! pasirodo ląstelėse E2, F2 arba G2, šis VLOOKUP klaidų pranešimų sąrašas gali padėti nustatyti, kur yra problema.