Excel VLOOKUPi abil saate mitme andmevälja leida

Kombineerides Excel VLOOKUP-funktsiooni funktsiooni COLUMN abil, võime luua otsinguvalemi, mis võimaldab teil ühest andmebaasist või andmete tabelist mitme väärtuse tagastada.

Eespool esitatud kujutises näidatud näites muudab otsingu valem kõigi väärtuste (nt hind, osa number ja tarnija) tagastamine erinevate riistvarakomponentidega.

01 of 10

Excel VLOOKUPi abil mitu väärtust

Järgnevalt loetletud toimingute põhjal luuakse ülaltoodud pildil kuvatud otsinguvorm, mis tagab mitme andmeühiku väärtuse.

Päringu valem nõuab, et VOLOOKUP-i sees oleks voldiku funktsioon COLUMN.

Funktsiooni paigutamine tähendab teise funktsiooni sisestamist üheks esimese funktsiooni ühe argumendina .

Selles juhendis sisestatakse VOLOOKUP-i veeru indeksi numbri argumendina funktsioon COLUMN.

Juhendaja viimane samm hõlmab tulemuste valemi kopeerimist täiendavatele veergudele, et saada valitud osa jaoks lisaväärtusi.

Juhendaja sisu

• Treeningu andmete sisestamine
• Andmebaasi nimega vahemiku loomine
• VLOOKUP-i funktsiooni käivitamine
• Otsinguväärtuse argumendi sisestamine Absoluutsete rakuliinide abil
• Tabeli massiivi argumendi sisestamine
• Sisestatud COLUMNi funktsiooni sisestamine
• VLOOKUP-i funktsiooni täitmine
• Otsinguvormi valimine täidiskäepideme abil
• Andmete taastamine otsinguvormiga

02 of 10

Sisestage juhendaja andmed

Juhendaja esimene samm on andmete sisestamine Exceli töölehele .

Tutoriali etappide järgimiseks sisestage ülaltoodud pildi andmed järgmistesse lahtritesse .

• Sisestage ülemise andmevahega raku D1 kuni G1
• Sisestage teine ​​vahemik rakkudesse D4 kuni G10

Selle õpetuse käigus loodud otsingukriteeriumid ja otsinguvalik sisestatakse töölehe reas 2.

Tutorial ei sisalda pildil nähtavat vormingut, kuid see ei mõjuta otsinguvaliku toimimist.

Selles Excelis põhitähtede vormindamise juhendis on saadaval vorminguvalikute teave, mis sarnaneb eespool kirjeldatutele.

Juhendaja sammud

1. Sisestage andmed, nagu näha ülaltoodud pildist, rakkudeks D1 kuni G10

03 of 10

Andmebaasi nimega vahemiku loomine

Nimetatud vahemik on lihtne viide valemi andmehulgale. Selle asemel, et sisestada andmete raku viited , võite lihtsalt sisestada vahemiku nime.

Nimetatud vahemiku kasutamise teine ​​eelis on see, et selle vahemiku raku viited ei muuda kunagi isegi siis, kui valem on kopeeritud töölehe teistele rakkudele.

Seetõttu on vahemike nimed alternatiiviks absoluutsete raku viidete kasutamisele, et vigu vältida valemite kopeerimisel.

Märkus: vahemiku nimi ei sisalda andmete päise või välju (rida 4), vaid ainult andmeid ise.

Juhendaja sammud

1. Tõstke esile töölaual D5 kuni G10 valikud
2. Klõpsake veeru A kohal veergu Name Box
3. Tüüp "Tabel" (ilma hinnapakkumisteta) nimeväljale
4. Vajutage klaviatuuril sisestusklahvi
5. Lahtritele D5 kuni G10 on nüüd tabeli vahemiku nimi. Me kasutame VLOOKUP tabeli massiivi argumenti hiljem juhendamisel

04 10-st

VLOOKUP-i dialoogiboksi avamine

Kuigi on võimalik lihtsalt sisestada otsingu valem otse töölehe lahtrisse, on paljudel inimestel keeruline säilitada süntaksi otseselt - eriti keeruka valemi puhul, nagu see, mida me selles õpetuses kasutame.

Antud juhul on alternatiiviks VLOOKUP-i dialoogiboksi kasutamine. Peaaegu kõik Exceli funktsioonid on dialoogiakna, mis võimaldab teil sisestada iga funktsiooni argumendid eraldi reale.

Juhendaja sammud

1. Klõpsake töölehe lahter E2 - asukoht, kus kuvatakse kahemõõtmelise otsingu valemi tulemused
2. Klikkige lindi vahekaardil Vormid
3. Funktsiooni rippmenüü avamiseks klõpsake lingil valikul Otsi ja võrdlus
4. Funktsiooni dialoogiboksi avamiseks klõpsake loendis VLOOKUP

05 of 10

Otsinguväärtuse argumendi sisestamine Absoluutsete rakuliinide abil

Tavaliselt vastab otsinguväärtus andmevälja esimese veeru andmeväljale.

Meie näites viitab otsingu väärtus selle riistvara osa nimele, mille kohta me soovime teavet leida.

Otsitava väärtuse jaoks on lubatud järgmised andmed :

• tekstiandmed
• loogiline väärtus (ainult TRUE või VÄÄRNE)
• number
• raku viide töölehe väärtusele

Selles näites sisestame lahtri viite sellele, kus osade nimi asub - lahtri D2.

Absoluutsed raku viited

Hooldusprogrammi hilisemas etapis kopeerime lookup valemi lahtris E2 rakkudele F2 ja G2.

Tavaliselt, kui valemid Excelis kopeeritakse, muutuvad raku viited oma uue asukoha kajastamiseks.

Kui see nii juhtub, muutub D2 - otsingu väärtuse raku viide - kui valem on kopeeritud, luues vigu lahtrites F2 ja G2.

Vigade vältimiseks teisendame raku viite D2 absoluutse raku viitena .

Absoluutsed rakkude viited ei muutu, kui valemeid kopeeritakse.

Absoluutsed raku viited luuakse klaviatuuri F4 klahvi vajutades. See suurendab dollarit, mis viitab raku viitele, näiteks $ D $ 2

Juhendaja sammud

1. Klõpsake dialoogiboksis lookup_value rida
2. Klõpsake lahtri D2, et lisada selle lahtri viide lookup_value rida. See on raport, kus me kirjutame osa nime, mille kohta me teavet otsime
3. Sisestuspunkti liigutamata vajutage klaviatuuril klahvi F4, et teisendada D2 absoluutse lahtri viidega $ D $ 2
4. Jäta VLOOKUP-i funktsioonide dialoogiboks lahti juhendaja järgmisesse sammu

06 10-st

Tabeli massiivi argumendi sisestamine

Tabeli massiiv on andmete tabel , mille otsinguvalem otsib soovitud teabe leidmiseks.

Tabeli massiiv peab sisaldama vähemalt kaht veeru andmeid .

• esimene veerg sisaldab otsinguväärtuse argumenti (juhendaja eelmine samm)
• Teine ja kõik täiendavad veerud otsitakse meie poolt määratud teabe leidmiseks otsinguvormi abil.

Tabeli massiivide argument tuleb sisestada kas vahemikku, mis sisaldab andmeväljale vastavaid raku viiteid või vahemiku nime .

Selle näite puhul kasutame juhendaja sammul 3 loodud vahemiku nime.

Juhendaja sammud

1. Klõpsake dialoogiboksis laua_rakeerimisliini
2. Selle argumendi vahemiku nime sisestamiseks sisestage tabel (ilma jutumärkideta)
3. Jäta VLOOKUP-i funktsioonide dialoogiboks lahti juhendaja järgmisesse sammu

07 of 10

COLUMNi funktsiooni pesa

Tavaliselt tagastab VLOOKUP andmeid ainult andmete tabeli ühelt veerult ja see veerg määratakse veeru indeksi numbri argumendiga.

Selles näites aga on meil kolm kolonni, mida me soovime andmeid tagasi saata, nii et meil on vaja lihtsalt muuta veeru indeksi numbrit ilma meie otsingu valemi muutmiseta.

See on koht, kuhu siseneb funktsioon COLUMN. Selle sisestades veeru indeksi numbri argumendina, muutub see otsinguvigade valem kopeeritakse lahtrist D2 rakkudesse E2 ja F2 hiljem juhendamisel.

Pesemisfunktsioonid

Seega toimib COLUMN funktsioon VLOOKUP veeru indeksi numbri argumendina .

See saavutatakse funktsiooni COLUMN sisestamisega VLOOKUP-i sees dialoogiboksi Col_index_num reas.

Funktsiooni VÄRVIMINE sisestamine käsitsi

Funktsioonide paigutamise korral ei luba Excel Excel'i argumentide sisestamiseks avada teise funktsiooni dialoogi .

Seega tuleb funktsioon COLUMN käsitsi sisestada reale Col_index_num .

Funktsioonil COLUMN on ainult üks argument - viide argument, mis on raku viide.

COLUMNi funktsiooni viidete argumendi valimine

Funktsiooni COLUMN ülesandeks on viidete argumendina antud veeru number tagastada.

Teisisõnu teisendab see veeru kirja numbriks, mille veerg A on esimene veerg, veerg B teine ​​ja nii edasi.

Kuna esimene andmeväli, mida me tahame tagastada, on objekti hind - mis on andmeside tabeli veerus 2 - võime valida viitenumbri veeru B jaoks mis tahes lahtri viite, et saada number 2 Col_index_num argument.

Juhendaja sammud

1. VLOOKUP-i dialoogiboksis klikkige rida Col_index_num
2. Tippige funktsiooninime veerg, millele järgneb avatud ümarlaud " ( "
3. Klõpsake töölehel raami B1- le, et sisestada selle raku viide referentsvõrgu argumendina
4. Sisestage voldikuklahv " ) ", et täita COLUMN-funktsiooni
5. Jäta VLOOKUP-i funktsioonide dialoogiboks lahti juhendaja järgmisesse sammu

08 10-st

VLOOKUP Range Lookup argumentatsiooni sisestamine

VLOOKUP'i Range_lookup argument on loogiline väärtus (ainult TRUE või VÄÄR), mis näitab, kas soovite, et VLOOKUP otsiks lookup_value jaoks täpset või ligikaudset vastet.

• Kui TRUE või kui see argument on välja jäetud, tagastab VLOOKUP kas täpse vaste otsingu_väärtusele või kui täpse vaste ei leita, siis tagastab VLOOKUP järgmise suurima väärtuse. Selle valemi jaoks tuleb Tabeli_raami esimeses veerus olevad andmed sortida kasvavas järjekorras .
• Kui VÄÄRNE, VLOOKUP kasutab täpse vaste ainult Lookup_value'ile. Kui tabeli_raami esimeses veerus on kaks või enam väärtust, mis sobivad otsingu väärtusega, kasutatakse esimest väärtust. Kui täpne vaste ei leita, tagastatakse # N / A viga.

Selles õpetuses, kuna me otsime konkreetset riistvaraüksuse kohta teavet, määrame Range_lookup võrdseks valega.

Juhendaja sammud

1. Klõpsake dialoogiboksi rida Range_lookup
2. Sisestage selles reas sõna False , et näidata, et me tahame, et VLOOKUP tagastaks otsitavate andmete täpse vaste
3. Otsingu valemi täitmiseks klõpsa OK ja sulgege dialoogiboks
4. Kuna me ei ole veel otsingukriteeriumite sisestanud raku D2, ilmub lahtrisse E2 # N / A viga
5. See viga parandatakse, kui lisame otsingukriteeriumid õpetuse viimases etapis

09 of 10

Otsinguvormi valimine täidiskäepideme abil

Otsinguvormi eesmärk on andmete hankimine andmete tabeli mitmest tulbast korraga.

Selleks peab otsingu valem jääma kõikides valdkondades, millest me soovime teavet.

Selles õppetükis soovime, et andmete laadimine andmete tabeli veergudest 2, 3 ja 4 - see on hind, osa number ja tarnija nimi, kui sisestame osa nime otsingu_väärtusena.

Kuna andmed on töölehe korrapärases mustris, saame kopeerida lookup-valemi lahtris E2 rakkudele F2 ja G2.

Valemi kopeerimisel värskendab Excel suvalise raku viite funktsiooni COLUMN (B1), et kajastada valemi uut asukohta.

Samuti ei muuda Exceli väärtust absoluutse rakuliini $ D $ 2 ja nimega valiku Tabel kui valem kopeeritakse.

Exceli andmete kopeerimiseks on rohkem kui üks viis, kuid ilmselt lihtsaim viis on täita täitmise käepide .

Juhendaja sammud

1. Klõpsake lahtris E2 - kus asub otsinguparameeter -, et see oleks aktiivne lahter
2. Asetage hiirekursor musta ruudu all paremas nurgas. Pointer muutub plussmärgiks " + " - see on täitke käepide
3. Klõpsake hiire vasakut nuppu ja lohistage täitehoob üle lahtrisse G2
4. Vabasta hiirenupp ja raku F3 peaks sisaldama kahemõõtmelise otsingu valemit
5. Kui seda õigesti tehakse, peaksid ka F2 ja G2 sisaldama ka numbrit #, mis on lahtris E2 olemas

10-st 10-st

Otsingukriteeriumide sisestamine

Kui otsinguvalem on kopeeritud nõutavatele elementidele , saab seda andmeid andmete tabelist otsida.

Selleks sisestage objekti nimi, mida soovite lahtrisse Lookup_value (D2) laadida, ja vajutage klaviatuuril ENTER klahvi.

Kui see on tehtud, peaks iga lookup valem sisaldav laht sisaldama teistsugust teavet riistvara kohta, mida otsite.

Juhendaja sammud

1. Klõpsake töölehel D2 loendis
2. Tippige vidin raku D2 ja vajutage ENTER klahvi klaviatuuril
3. Lahtrites E2-G2 tuleb kuvada järgmine teave:
   • E2 - 14,76 eurot - vidina hind
   • F2 - PN-98769 - vidina osa number
   • G2 - Widgets Inc. - vidinate tarnija nimi
4. Katsetage VLOOKUP-i massiivi valemit veelgi, trükkides teiste osade nime raku D2 ja jälgides tulemusi rakkudes E2 kuni G2

Kui kuvatakse veateade näiteks #REF! ilmub lahtritesse E2, F2 või G2, võib see VLOOKUP-i veateadete loend aidata teil kindlaks teha, kus probleem asub.