Kombineerides Exceli funktsiooni VLOOKUP funktsiooniga COLUMN, saate luua otsinguvalemi, mis tagastab andmebaasi või andmetabeli ühest reast mitu väärtust. Siit saate teada, kuidas luua otsinguvalemit, mis tagastab ühest andmekirjest mitu väärtust.
Selles artiklis olevad juhised kehtivad Excel 2019, 2016, 2013, 2010; ja Excel Microsoft 365 jaoks.
Alumine rida
Otsinguvalem nõuab, et funktsioon COLUMN oleks pesastatud VLOOKUP-i. Funktsiooni pesastamine hõlmab teise funktsiooni sisestamist esimese funktsiooni ühe argumendina.
Sisestage õpetuse andmed
Selles õpetuses sisestatakse VLOOKUP veeru indeksi numbri argumendiks funktsioon COLUMN. Õpetuse viimane samm hõlmab otsinguvalemi kopeerimist täiendavatesse veergudesse, et saada valitud osa jaoks lisaväärtusi.
Selle õpetuse esimene samm on andmete sisestamine Exceli töölehel. Selle õpetuse juhiste järgimiseks sisestage alloleval pildil näidatud andmed järgmistesse lahtritesse:
- Sisestage ülemine andmevahemik lahtritesse D1 kuni G1.
- Sisestage teine vahemik lahtritesse D4 kuni G10.
Selles õpetuses loodud otsingukriteeriumid ja otsinguvalem sisestatakse töölehe 2. reale.
See õpetus ei sisalda pildil näidatud Exceli põhivormingut, kuid see ei mõjuta otsinguvalemi toimimist.
Looge andmetabelile nimeline vahemik
Nimega vahemik on lihtne viis valemis andmevahemikule viitamiseks. Andmete lahtriviite tippimise asemel tippige vahemiku nimi.
Teine eelis nimega vahemiku kasutamisel on see, et selle vahemiku lahtriviited ei muutu kunagi isegi siis, kui valem kopeeritakse töölehe teistesse lahtritesse. Vahemikunimed on alternatiiv absoluutsete lahtriviidete kasutamisele, et vältida valemite kopeerimisel tekkivaid vigu.
Vahemiku nimi ei sisalda andmete pealkirju ega väljade nimesid (nagu näidatud real 4), vaid ainult andmeid.
-
Tõstke töölehel esile lahtrid D5 kuni G10.
Image -
Viige kursor nimekasti, mis asub veeru A kohal, tippige Table, seejärel vajutage Sisestus. Lahtrite D5 kuni G10 vahemiku nimi on tabel.
Image - Tabelimassiivi VLOOKUP argumendi vahemiku nime kasutatakse selles õpetuses hiljem.
Avage dialoogiboks VLOOKUP
Kuigi otsinguvalemit on võimalik sisestada otse töölehe lahtrisse, on paljudel inimestel raske süntaksit sirge hoida – eriti keeruka valemi puhul, nagu selles õpetuses kasutatud.
Alternatiivina kasutage dialoogiboksi VLOOKUP funktsiooni argumendid. Peaaegu kõigil Exceli funktsioonidel on dialoogiboks, kuhu funktsiooni iga argument sisestatakse eraldi reale.
-
Valige töölehe lahter E2. See on koht, kus kuvatakse kahemõõtmelise otsingu valemi tulemused.
Image -
Minge lindil vahekaardile Valemid ja valige Lookup & Reference.
Image -
Valige VLOOKUP, et avada dialoogiboks Funktsiooni argumendid.
Image - Funktsiooniargumentide dialoogiaken on koht, kuhu sisestatakse funktsiooni VLOOKUP parameetrid.
Sisestage otsinguväärtuse argument
Tavaliselt ühtib otsinguväärtus andmetabeli esimeses veerus oleva andmeväljaga. Selles näites viitab otsinguväärtus selle osa nimele, mille kohta soovite teavet leida. Otsinguväärtuse jaoks lubatud andmetüübid on tekstiandmed, loogilised väärtused, numbrid ja lahtriviited.
Absoluutsed lahtriviited
Valemite kopeerimisel Excelis muutuvad lahtriviited uue asukoha järgi. Kui see juhtub, muutub otsinguväärtuse lahtriviide D2 ning tekitab lahtrites F2 ja G2 vigu.
Absoluutsed lahtriviited valemite kopeerimisel ei muutu.
Vigade vältimiseks teisendage lahtriviide D2 absoluutseks lahtriviiteks. Lahtri absoluutse viite loomiseks vajutage klahvi F4. See lisab lahtri viite ümber dollarimärgid, näiteks $D$2.
-
Viige dialoogiboksis Funktsiooni argumendid kursor tekstikasti lookup_value. Seejärel valige töölehel lahter D2, et lisada see lahtriviide lahtrisse lookup_value. Lahtrisse D2 sisestatakse osa nimi.
Image -
Ilma sisestuspunkti liigutamata vajutage klahvi F4, et teisendada D2 absoluutseks lahtriviiteks $D$2.
Image - Jäta õpetuse järgmise sammu jaoks avatuks funktsiooni VLOOKUP dialoogiboks.
Sisestage tabelimassiivi argument
Tabelimassiiv on andmete tabel, mida otsinguvalem otsib soovitud teabe leidmiseks. Tabelimassiivis peab olema vähem alt kaks veergu andmeid.
Esimene veerg sisaldab otsinguväärtuse argumenti (mis seadistati eelmises jaotises), samas kui teises veerus otsitakse teie määratud teabe leidmiseks otsinguvalemit.
Tabelimassiivi argument tuleb sisestada kas andmetabeli lahtriviiteid sisaldava vahemikuna või vahemiku nimena.
Andmete tabeli lisamiseks funktsioonile VLOOKUP asetage kursor dialoogiboksi tekstikasti table_array ja tippige Tabelselle argumendi vahemiku nime sisestamiseks.
Pesastage VEERUN funktsioon
Tavaliselt tagastab VLOOKUP andmed ainult andmetabeli ühest veerust. See veerg määratakse veeru indeksi numbri argumendiga. Selles näites on aga kolm veergu ja veeru indeksi numbrit tuleb muuta ilma otsinguvalemit muutmata. Selle saavutamiseks pesastage funktsioon COLUMN funktsiooni VLOOKUP sees argumendina Col_index_num.
Funktsioonide pesastamisel ei ava Excel teise funktsiooni dialoogiboksi selle argumentide sisestamiseks. Funktsioon COLUMN tuleb sisestada käsitsi. Funktsioonil COLUMN on ainult üks argument, Reference argument, mis on lahtriviide.
Funktsioon COLUMN tagastab viiteargumendina esitatud veeru numbri. See teisendab veeru tähe numbriks.
Kauba hinna leidmiseks kasutage andmetabeli 2. veerus olevaid andmeid. See näide kasutab veergu B viitena, et lisada 2 argumendisse Col_index_num.
-
Viige dialoogiboksis Funktsiooni argumendid kursor tekstiväljale Col_index_num ja tippige COLUMN(. (Kaasake kindlasti avatud ümmargune sulg.)
Image -
Valige töölehel lahter B1, et sisestada selle lahtri viide argumendiks Reference.
Image - Sisestage sulgev ümarsulg funktsiooni COLUMN lõpetamiseks.
Sisestage VLOOKUP vahemiku otsingu argument
VLOOKUPi Range_lookup argument on loogiline väärtus (TRUE või FALSE), mis näitab, kas VLOOKUP peaks leidma otsinguväärtusele täpse või ligikaudse vaste.
- TRUE või Välja jäetud: VLOOKUP tagastab Otsingu_väärtusele lähedase vaste. Kui täpset vastet ei leitud, tagastab VLOOKUP suuruselt järgmise väärtuse. Tabeli_massiivi esimeses veerus olevad andmed tuleb sortida kasvavas järjekorras.
- FALSE: VLOOKUP kasutab täpset vastet väärtusele Lookup_value. Kui Table_array esimeses veerus on kaks või enam väärtust, mis vastavad otsinguväärtusele, kasutatakse esimest leitud väärtust. Kui täpset vastet ei leita, tagastatakse viga N/A.
Selles õpetuses otsitakse konkreetse riistvaraüksuse kohta spetsiifilist teavet, nii et valiku Range_lookup väärtus on FALSE.
Viige dialoogiboksis Funktsiooni argumendid kursor tekstikasti Range_lookup ja tippige False, et käskida VLOOKUPil andmetele täpse vaste tagastada.
Otsinguvalemi lõpuleviimiseks ja dialoogiboksi sulgemiseks valige OK. Lahter E2 sisaldab viga N/A, kuna otsingukriteeriumid pole lahtrisse D2 sisestatud. See viga on ajutine. See parandatakse, kui otsingukriteeriumid lisatakse selle õpetuse viimases etapis.
Kopeerige otsinguvalem ja sisestage kriteeriumid
Otsinguvalem hangib andmed korraga mitmest andmetabeli veerust. Selleks peab otsinguvalem asuma kõigil väljadel, millest soovite teavet saada.
Andmete toomiseks andmetabeli veergudest 2, 3 ja 4 (hind, osa number ja tarnija nimi), sisestage otsingu_väärtuseks osaline nimi.
Kuna andmed on töölehel paigutatud tavalise mustrina, kopeerige otsinguvalem lahtrisse lahtrisse E2 lahtritesse F2 ja G2 Valemi kopeerimisel värskendab Excel suhtelise lahtri viidet funktsioonis COLUMN (lahter B1), et kajastada valemi uut asukohta. Excel ei muuda valemi kopeerimisel absoluutset lahtriviidet (nt $D$2) ja nimega vahemikku (tabel).
Andmete kopeerimiseks Excelis on rohkem kui üks viis, kuid kõige lihtsam on kasutada täitmiskäepidet.
-
Valige lahter E2, kus asub otsinguvalem, et muuta see aktiivseks lahtriks.
Image -
Lohistage täitekäepide lahtrisse G2. Lahtrites F2 ja G2 kuvatakse N/A viga, mis esineb lahtris E2.
Image -
Andmetabelist teabe hankimiseks otsinguvalemite kasutamiseks valige töölehel lahter D2, tippige Widget ja vajutage Sisestage.
Image Järgmine teave kuvatakse lahtrites E2 kuni G2.
- E2: 14,76 $ – vidina hind
- F2: PN-98769 – vidina osanumber
- G2: Widgets Inc. – vidinate tarnija nimi
-
VLOOKUP massiivi valemi testimiseks tippige teiste osade nimed lahtrisse D2 ja jälgige tulemusi lahtrites E2 kuni G2.
Image - Iga otsinguvalemit sisaldav lahter sisaldab erinevaid andmeid teie otsitud riistvaraüksuse kohta.
Funktsioon VLOOKUP koos pesastatud funktsioonidega, nagu COLUMN, pakub võimsat meetodit andmete otsimiseks tabelist, kasutades otsingu viitena muid andmeid.
