Sujungę Excel funkciją VLOOKUP su funkcija COLUMN galite sukurti paieškos formulę, kuri pateikia kelias reikšmes iš vienos duomenų bazės arba duomenų lentelės eilutės. Sužinokite, kaip sukurti paieškos formulę, kuri pateikia kelias reikšmes iš vieno duomenų įrašo.
Šiame straipsnyje pateiktos instrukcijos taikomos Excel 2019, 2016, 2013, 2010; ir „Excel“, skirta „Microsoft 365“.
Bottom line
Paieškos formulė reikalauja, kad funkcija COLUMN būtų įdėta į VLOOKUP. Funkcijos įdėjimas apima antrosios funkcijos įvedimą kaip vieną iš pirmosios funkcijos argumentų.
Įveskite mokymo programos duomenis
Šioje mokymo programoje funkcija COLUMN įvedama kaip VLOOKUP stulpelio indekso numerio argumentas. Paskutinis mokymo programos veiksmas apima paieškos formulės nukopijavimą į papildomus stulpelius, kad būtų gautos papildomos pasirinktos dalies reikšmės.
Pirmasis šios mokymo programos veiksmas – įvesti duomenis į „Excel“darbalapį. Norėdami atlikti šioje mokymo programoje nurodytus veiksmus, toliau esančiame paveikslėlyje pateiktus duomenis įveskite į šiuos langelius:
- Įveskite didžiausią duomenų diapazoną į langelius D1–G1.
- Įveskite antrąjį diapazoną į langelius D4 iki G10.
Šioje pamokoje sukurti paieškos kriterijai ir paieškos formulė įvedami į 2 darbalapio eilutę.
Ši mokymo programa neapima pagrindinio „Excel“formatavimo, parodyto paveikslėlyje, tačiau tai neturi įtakos paieškos formulės veikimui.
Sukurkite pavadintą duomenų lentelės diapazoną
Įvardytas diapazonas yra paprastas būdas nurodyti duomenų diapazoną formulėje. Užuot įvedę duomenų langelių nuorodas, įveskite diapazono pavadinimą.
Antras pavadinto diapazono naudojimo pranašumas yra tas, kad šio diapazono langelių nuorodos niekada nesikeičia net tada, kai formulė nukopijuojama į kitus darbalapio langelius. Diapazono pavadinimai yra alternatyva absoliučių langelių nuorodų naudojimui, kad būtų išvengta klaidų kopijuojant formules.
Į diapazono pavadinimą neįeina duomenų antraštės ar laukų pavadinimai (kaip parodyta 4 eilutėje), tik duomenys.
-
Darbalapyje paryškinkite ląstelės D5 iki G10.
Image -
Įdėkite žymeklį į pavadinimo laukelį, esantį virš A stulpelio, įveskite Table, tada paspauskite Enter. Nuo D5 iki G10 langelių diapazono pavadinimas yra lentelė.
Image - VLOOKUP lentelės masyvo argumento diapazono pavadinimas bus naudojamas vėliau šioje mokymo programoje.
Atidarykite VLOOKUP dialogo langą
Nors galima įvesti paieškos formulę tiesiai į darbalapio langelį, daugeliui žmonių sunku išlaikyti teisingą sintaksę – ypač sudėtingos formulės, tokios kaip šioje mokymo programoje, atveju.
Kaip alternatyva, naudokite dialogo langą VLOOKUP Funkcijos argumentai. Beveik visose „Excel“funkcijose yra dialogo langas, kuriame kiekvienas funkcijos argumentas įvedamas atskiroje eilutėje.
-
Pasirinkite darbalapio langelį E2. Tai vieta, kurioje bus rodomi dvimatės paieškos formulės rezultatai.
Image -
Juostoje eikite į Formulės skirtuką ir pasirinkite Lookup & Reference.
Image -
Pasirinkite VLOOKUP, kad atidarytumėte dialogo langą Funkcijos argumentai.
Image - Funkcijų argumentų dialogo lange įvedami funkcijos VLOOKUP parametrai.
Įveskite paieškos vertės argumentą
Paprastai peržvalgos reikšmė atitinka duomenų lauką pirmajame duomenų lentelės stulpelyje. Šiame pavyzdyje paieškos reikšmė nurodo dalies, kurioje norite rasti informacijos, pavadinimą. Leistini duomenų tipai peržvalgos vertei yra tekstiniai duomenys, loginės reikšmės, skaičiai ir langelių nuorodos.
Absoliučios langelių nuorodos
Kai formulės nukopijuojamos programoje „Excel“, langelių nuorodos keičiasi, kad atspindėtų naują vietą. Jei taip atsitiks, D2, peržvalgos vertės langelio nuoroda, pasikeičia ir sukuria klaidas langeliuose F2 ir G2.
Kopijuojant formules, absoliučios langelių nuorodos nesikeičia.
Kad išvengtumėte klaidų, konvertuokite langelio nuorodą D2 į absoliučią langelio nuorodą. Norėdami sukurti absoliučią langelio nuorodą, paspauskite klavišą F4. Taip aplink langelio nuorodą pridedami dolerio ženklai, pvz., $D$2.
-
Dialogo lange Funkcijos argumentai įveskite žymeklį į lookup_value teksto laukelį. Tada darbalapyje pasirinkite cell D2, kad pridėtumėte šią langelio nuorodą į lookup_value. D2 langelyje bus įvestas dalies pavadinimas.
Image -
Neperkeldami įterpimo taško, paspauskite klavišą F4, kad konvertuotumėte D2 į absoliučią langelio nuorodą $D$2.
Image - Palikite funkcijos VLOOKUP dialogo langą atidarytą kitam mokymo programos veiksmui.
Įveskite lentelės masyvo argumentą
Lentelių masyvas yra duomenų lentelė, kurios ieškoma pagal paieškos formulę, kad surastų norimą informaciją. Lentelės masyve turi būti bent du duomenų stulpeliai.
Pirmajame stulpelyje yra paieškos vertės argumentas (kuris buvo nustatytas ankstesniame skyriuje), o antrame stulpelyje ieškoma pagal paieškos formulę, kad būtų galima rasti jūsų nurodytą informaciją.
Lentelės masyvo argumentas turi būti įvestas kaip diapazonas, kuriame yra duomenų lentelės langelių nuorodos, arba kaip diapazono pavadinimas.
Jei norite pridėti duomenų lentelę prie funkcijos VLOOKUP, įveskite žymeklį į table_array dialogo lange esantį teksto laukelį ir įveskite Table, kad įvestumėte šio argumento diapazono pavadinimą.
Įdėkite stulpelio funkciją
Paprastai VLOOKUP pateikia duomenis tik iš vieno duomenų lentelės stulpelio. Šis stulpelis nustatomas pagal stulpelio indekso numerio argumentą. Tačiau šiame pavyzdyje yra trys stulpeliai, o stulpelio indekso numerį reikia pakeisti neredaguojant paieškos formulės. Norėdami tai padaryti, įdėkite funkciją COLUMN į funkciją VLOOKUP kaip argumentą Col_index_num.
Įdėjus funkcijas, „Excel“neatidaro antrosios funkcijos dialogo lango, kad galėtų įvesti jos argumentus. Funkciją COLUMN reikia įvesti rankiniu būdu. Funkcija COLUMN turi tik vieną argumentą, nuorodos argumentą, kuris yra langelio nuoroda.
Funkcija COLUMN grąžina stulpelio numerį, pateiktą kaip nuorodos argumentas. Jis paverčia stulpelio raidę į skaičių.
Norėdami sužinoti prekės kainą, naudokite duomenų lentelės 2 stulpelio duomenis. Šiame pavyzdyje B stulpelis naudojamas kaip nuoroda, norint įterpti 2 į Col_index_num argumentą.
-
Dialogo lange Funkcijų argumentai įveskite žymeklį į Col_index_num teksto laukelį ir įveskite COLUMN(. (Būtinai įtraukite atvirą apvalų skliaustą.)
Image -
Darbalapyje pasirinkite cell B1, kad įvestumėte tą langelio nuorodą kaip nuorodos argumentą.
Image - Įveskite uždarymo apvalų skliaustą, kad užbaigtumėte funkciją COLUMN.
Įveskite VLOOKUP diapazono paieškos argumentą
VLOOKUP Range_lookup argumentas yra loginė reikšmė (TRUE arba FALSE), nurodanti, ar VLOOKUP turėtų rasti tikslią ar apytikslę atitiktį Lookup_value.
- TRUE arba Omitted: VLOOKUP grąžina artimą Lookup_value atitiktį. Jei tiksli atitiktis nerasta, VLOOKUP grąžina kitą didžiausią reikšmę. Duomenys pirmajame lentelės_masyvo stulpelyje turi būti rūšiuojami didėjančia tvarka.
- FALSE: VLOOKUP naudoja tikslią Lookup_value atitiktį. Jei pirmajame Table_masyvo stulpelyje yra dvi ar daugiau reikšmių, atitinkančių paieškos reikšmę, naudojama pirmoji rasta reikšmė. Jei tiksli atitiktis nerasta, pateikiama klaida N/A.
Šioje mokymo programoje bus ieškoma konkrečios informacijos apie tam tikrą aparatūros elementą, todėl diapazono_žvalga yra FALSE.
Dialogo lange Funkcijų argumentai užveskite žymeklį į Range_lookup teksto laukelį ir įveskite False, kad nurodytumėte VLOOKUP grąžinti tikslią duomenų atitiktį.
Pasirinkite OK, kad užbaigtumėte paieškos formulę ir uždarytumėte dialogo langą. E2 langelyje bus klaida N/A, nes į D2 langelį neįvesti paieškos kriterijai. Ši klaida laikina. Jis bus ištaisytas, kai paskutiniame šios mokymo programos etape bus pridėti paieškos kriterijai.
Nukopijuokite paieškos formulę ir įveskite kriterijus
Paieškos formulė vienu metu nuskaito duomenis iš kelių duomenų lentelės stulpelių. Kad tai padarytumėte, paieškos formulė turi būti visuose laukuose, iš kurių norite gauti informacijos.
Jei norite gauti duomenis iš 2, 3 ir 4 duomenų lentelės stulpelių (kaina, dalies numeris ir tiekėjo pavadinimas), įveskite dalinį pavadinimą kaip Lookup_value.
Kadangi duomenys darbalapyje išdėstyti įprastu šablonu, nukopijuokite paieškos formulę langelyje E2 į cells F2 ir G2 Kopijuojant formulę, „Excel“atnaujina santykinę langelio nuorodą funkcijoje COLUMN (ląstelė B1), kad atspindėtų naują formulės vietą. Kopijuojant formulę „Excel“nekeičia absoliučios langelio nuorodos (pvz., $D$2) ir pavadinto diapazono (lentelės).
Yra daugiau nei vienas būdas kopijuoti duomenis programoje „Excel“, tačiau lengviausias būdas yra naudoti užpildymo rankenėlę.
-
Pasirinkite ląstelę E2, kurioje yra paieškos formulė, kad ji taptų aktyvia ląstele.
Image -
Vilkite užpildymo rankenėlę į ląstelę G2. Langeliuose F2 ir G2 rodoma N/A klaida, kuri yra langelyje E2.
Image -
Jei norite naudoti paieškos formules informacijai iš duomenų lentelės gauti, darbalapyje pasirinkite ląstelė D2, įveskite Widget ir paspauskite Įveskite.
Image Ši informacija rodoma langeliuose E2–G2.
- E2: 14,76 USD – valdiklio kaina
- F2: PN-98769 – valdiklio dalies numeris
- G2: Widgets Inc. – valdiklių tiekėjo pavadinimas
-
Norėdami išbandyti VLOOKUP masyvo formulę, įveskite kitų dalių pavadinimus langelyje D2 ir stebėkite rezultatus langeliuose E2 iki G2.
Image - Kiekviename langelyje, kuriame yra paieškos formulė, yra skirtingi duomenys apie aparatinės įrangos elementą, kurio ieškojote.
Funkcija VLOOKUP su įdėtomis funkcijomis, pvz., COLUMN, yra veiksmingas būdas ieškoti duomenų lentelėje, naudojant kitus duomenis kaip paieškos nuorodą.
