Sujungus "Excel"VLOOKUP funkcija suCOLUMN funkcija mes galime sukurti paieškos formules, kuriomis galima grąžinti keletą verčių iš vienos duomenų bazės ar duomenų lentelės eilutės.
01 iš 10Grįž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 forma reikalauja, kad COLUMN funkcija yra įdėta viduje VLOOKUP. Funkcijos pritvirtinimas reiškia antrosios funkcijos įvedimą kaip vieną iš pirmosios funkcijos argumentų.
Šiame vadove, COLUMN funkcija bus įrašyta kaip stulpelio indeksas numeris argumentas VLOOKUP. Paskutinis žingsnio veiksmas apima kopijuoti paieškos formules į papildomus stulpelius, kad gautumėte papildomų verčių pasirinktai daliai.
Įveskite mokymo duomenis
Pirmas žingsnis pamoka yra įvesti duomenis į "Excel" darbalapį. Norėdami sekti instrukcijų veiksmus, įveskite pirmiau pateiktuose paveikslėliuose nurodytus duomenis į šias langelius:
- Įveskite aukščiausią duomenų diapazoną ląstelės D1 į G1.
- Įveskite antrąjį diapazoną į ląstelės D4 į G10.
Šio pamokymo metu sukurti paieškos kriterijai ir paieškos formulė bus įvestos 2 eilutė darbalapio.
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.
03 iš 10Duomenų 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.
Diapazono pavadinime nėra duomenų pavadinimų ar laukų pavadinimų (4 eilutė), bet tik pačius duomenis.
- Pažymėti ląstelės D5 į G10 darbalelei juos pasirinkti.
- Spustelėkite ant Vardo langelis esantis aukščiau A skiltis.
- Įveskite Lentelė į Vardo langelis.
- Paspauskite Įveskite raktas ant klaviatūros.
- Ląstelės D5 į G10 dabar turi diapazono pavadinimą Lentelė. Mes naudosime pavadinimą VLOOKUP stalo masyvas argumentas vėliau pamoka.
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 naudoti VLOOKUP dialogo langas. Beveik visi "Excel" funkcijos turi dialogo langą, pagal kurį galite įvesti kiekvieną funkcijos argumentą atskiroje eilutėje.
Tutorial žingsniai
- Spustelėkite ląstelė E2 darbalapio vieta - vieta, kurioje bus rodomos dvimatės paieškos formos rezultatai.
- Spustelėkite ant Skirtuko formules iš juosta.
- Spustelėkite ant Ieškoti ir nuoroda parinktis juosta atidaryti išskleidžiamąjį meniu funkciją.
- Spustelėkite VLOOKUP sąraše, norėdami atidaryti funkcijos dialogo langą.
Įvedimas "Lookup Value Argument"
Paprastai Paiešką vertę Duomenų lentelės pirmame stulpelyje atitinka duomenų lauką. Mūsų pavyzdyje yra Paiešką vertę nurodo aparatinės įrangos dalies pavadinimą, apie kurį mes norime rasti informaciją. Leidžiami duomenų tipai Paiešką vertę yra tekstiniai duomenys, loginės vertės, skaičiai ir ląstelių nuorodos.
Absoliutus ląstelių nuorodos
Paprastai, kai formulės yra nukopijuotos "Excel", ląstelių nuorodos keičia, kad atspindėtų jų naują vietą. Jei taip atsitiks D2, ląstelių nuoroda į Paiešką vertę, keisis, nes formulė yra nukopijuota, sukuriant klaidas ląstelės F2 ir G2.
Absoliučios ląstelių nuorodos nesikeičia, kai formulės yra nukopijuotos.
Norėdami išvengti klaidų, mes konvertuosime langelio nuorodą D2 į absoliučią langelį nuoroda. Absoliučios ląstelių nuorodos sukurtos paspaudus F4 raktas ant klaviatūros. Tai taip pridedant dolerio ženklus aplink ląstelių nuoroda, pavyzdžiui $ D $ 2.
- Spustelėkite ant lookup_value dialogo lango eilutė.
- Spustelėkite ląstelė D2 pridėti šią langelį nuoroda į lookup_value linija - tai ląstelė, kurioje įrašysime dalį pavadinimo, apie kurį mes ieškome informacijos
- Nejudėdami įterpimo taško, paspauskite F4 klaviatūra konvertuoti D2 į absoliučią ląstelių nuorodą $ D $ 2.
- Palikite VLOOKUP funkcijos dialogo langas atidaromas kitam žingsnio žingsniui.
Į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.
Pirmasis stulpelis pateikia paieškos verčių argumentą (ankstesnį žingsnį mokymo programoje), o antrasis stulpelis bus ieškomas pagal paieškos formulę, norint rasti informaciją, kurią mes nurodome.
Stalo masyvo argumentas turi būti įrašytas kaip diapazonas, kuriame yra duomenų lentelės ląstelių nuorodos arba diapazono pavadinimas.
- Spustelėkite ant table_array dialogo lango eilutė.
- Įveskite Lentelė įvesti šio argumento diapazono pavadinimą.
- Palikite VLOOKUP funkcijos dialogo langas atidaromas kitam žingsnio žingsniui.
"COLUMN" funkcijos pritvirtinimas
Paprastai VLOOKUP tik grąžina duomenis iš vieno duomenų lentelės stulpelio, o šį stulpelį nustato stulpelio indekso numeris argumentas.
Tačiau šiame pavyzdyje mes turime tris stulpelius, kuriuos norime grąžinti iš duomenų, todėl mums reikia lengvai pakeisti stulpelio indekso numeris be redagavimo mūsų paieškos formules - tai čia COLUMN funkcija ateina
Įvesdami jį kaip stulpelio indekso numeris argumentas, jis pasikeis, nes paieškos formule nukopijuojama iš ląstelė D2 į ląstelės E2 ir F2 vėliau pamoka.
Nesting Functions
The COLUMN funkcija, todėl veikia kaip VLOOKUP's stulpelio indekso numeris argumentas; tai daroma lizdus COLUMN funkcija viduje VLOOKUP viduje konors Col_index_num dialogo lango eilutė.
Funkcijos "COLUMN" įvedimas rankiniu būdu
Kai įdiegdami funkcijas, "Excel" neleidžia mums atidaryti antrosios funkcijos dialogo lango, kad įvestumėte jo argumentus. The COLUMN Todėl funkcija turi būti įvesta rankiniu būdu Col_index_num linija The COLUMN funkcija turi tik vieną argumentą - Nuoroda argumentas, kuris yra ląstelių nuoroda.
Funkcijos "COLUMN" standartinio argumento pasirinkimas
The COLUMN funkcijos darbas yra grąžinti stulpelio numerį, nurodytą kaip Nuoroda argumentas. Kitaip tariant, jis konvertuoja stulpelio raidę į skaičių su A skiltis pirmoji stulpelis stulpelis B antroji ir pan.
Nuo pirmojo duomenų lauko tai, ko mes norime grąžinti, yra straipsnio, kuris pateikiamas duomenų lentelės antroje skiltyje, kaina. Mes galime pasirinkti ląstelių nuorodą bet kuriai ląstelėje stulpelis B kaip Nuoroda Argumentas, norint gauti numerį 2 už Col_index_num argumentas.
- Viduje konors VLOOKUP dialogo langas, spustelėkite mygtuką Col_index_num linija
- Įveskite funkcijos pavadinimą stulpelis po to a atidaryti apvalią kronšteina.
- Spustelėkite ląstelė B1 į darbalapį įrašyti į tą langelį kaip " Nuoroda argumentas.
- Įveskite a uždaroma kronšteina užpildyti COLUMN funkcija
- Palikite VLOOKUP funkcijos dialogo langas atidaromas kitam žingsnio žingsniui.
Įveskite "VLOOKUP" diapazono paieškos argumentą
VLOOKUP's "Range_lookup" argumentas yra logiška reikšmė (TIESA arba Klaidinga), kuris nurodo, ar norite VLOOKUP rasti tikslią arba apytikrį atitikimą Lookup_value.
- Jei TIESA arba jei šis argumentas yra praleistas: VLOOKUP grąžina arba tikslų atitikimą Lookup_valueArba, jei tikslios atitikties nerasta, VLOOKUP grąžina kitą didžiausią vertę. Norėdami tai padaryti, naudokite pirmojo stulpelio duomenis Table_array turi būti rūšiuojami didėjančia tvarka.
- Jei Klaidinga: VLOOKUP naudos tik tikslią atitikmenį Lookup_value. Jei pirmajame stulpelyje yra dvi ar daugiau verčių Table_array kurie atitinka paieškos vertę, naudojama pirmoji rasta vertė. Jei tikslios atitikties nerasta, a # N / A klaida grąžinama.
Šiame vadove, nes mes ieškome konkrečios informacijos apie konkretų aparatūros elementą, mes nustatysime "Range_lookup" lygus Klaidinga.
- Spustelėkite ant "Range_lookup" dialogo lango eilutė.
- Įveskite žodį Klaidinga šioje eilutėje nurodykite, kad norime VLOOKUP grąžinti tikslią atitin kamą informaciją, kurią mes ieškome.
- Spustelėkite Gerai užpildyti paieškos formules ir uždaryti dialogo langą.
- Kadangi mes dar nepateikėme paieškos kriterijų ląstelė D2 a # N / A klaida bus čia ląstelė E2.
- Ši klaida bus ištaisyta, kai mes pridėsime paieškos kriterijus paskutiniame mokymo etape.
Kopijuoti paieškos formules
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 jis gautų duomenis iš 2 stulpeliai, 3, ir 4 duomenų lentelės - tai yra kaina, dalies numeris ir tiekėjo vardas, kai mes įveskite dalinį pavadinimą kaip Lookup_value.
Kadangi duomenys yra išdėstyti į įprastą lapo lapą, mes galime nukopijuoti paieškos formules ląstelė E2 į ląstelės F2 ir G2.
Kai formulė yra nukopijuota, "Excel" atnaujins santykinę ląstelių nuorodą COLUMN funkcija (ląstelė B1), kad atspindėtų naujos formulės vietą.
Be to, "Excel" nekeičia absoliutaus langelio nuorodos $ D $ 2 ir pavadintą diapazoną Lentelė kaip formulė yra nukopijuota.
Yra daugiau nei vienas būdas kopijuoti duomenis "Excel", bet tikriausiai paprasčiausias būdas yra naudojant Užpildykite rankeną.
- Spustelėkite ląstelė E2, kur yra paieškos formule, kad ji taptų aktyvia ląstele.
- Vieta pelės žymeklis per juodas kvadratas apatiniame dešiniajame kampe - žymeklis pasikeis į pliuso ženklą, kad būtų nurodyta užpildymo rankena.
- Spustelėkite kairįjį pelės mygtuką ir vilkite užpildymo rankenėlę per visą ląstelė G2.
- Išleisti pelės mygtukas ir ląstelė F3 turėtų būti dvimatė paieškos forma.
- Jei padaryta teisingai, ląstelės F2 ir G2 dabar taip pat turėtų būti # N / A klaida, kuri yra ląstelė E2.
Į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 prekės pavadinimas Jūs norite gauti į Lookup_value ląstelė (D2) ir paspauskite Įveskite raktas ant klaviatūros.
Po to kiekviena ląstelė, kurioje yra paieškos formos, turėtų turėti kitą duomenų apie aparatūros elementą, kurį ieškote.
- Spustelėkite ląstelė D2 darbalapyje.
- Įveskite Valdiklis į ląstelė D2 ir paspauskite Įveskite raktas ant klaviatūros.
- Turi būti pateikta tokia informacija ląstelės E2 į G2.
- E2: 14,76 USD - valdikliui kaina
- F2: PN-98769 - valdiklio dalies numeris
- G2: "Widgets Inc." - "widget" tiekėjo pavadinimas
- Patikrinkite VLOOKUP masyvo formulę, įvesdami kitų dalių pavadinimą į ląstelė D2 ir stebėti rezultatus ląstelės E2 į G2
