Funktsioonis VLOOKUP määratleme sageli col_index_no staatiline. Me kodeerime selle VLOOKUP valemis, näiteks VLOOKUP (id, andmed,3, 0). Probleem tekib siis, kui sisestame või kustutame andmete veeru. Kui eemaldame või lisame veeru enne või pärast kolmandat veergu, ei viita kolmas veerg enam kavandatud veerule. See on üks probleem. Muu on see, kui teil on otsida mitu veergu. Peate iga valemi veergude indeksit muutma. Lihtne kopeerimine-kleepimine ei aita.
Aga kuidas oleks, kui saaksite käsul VLOOKUP vaadata pealkirju ja tagastada ainult vastavad pealkirjade väärtused. Seda nimetatakse kahesuunaliseks VLOOKUPiks.
Näiteks kui mul on VLOOKUP valemmärke veerg, siis peaks VLOOKUP otsima märke veergu ja tagastage selle veeru väärtus. See lahendab meie probleemi.
Hmm… Okei, kuidas me siis seda teeme? Kasutades funktsiooni VLOOKUP funktsiooni Match Function.
Üldine valem
=VLOOKUP(otsinguväärtus, tabelimassiiv, MATCH (otsingu_pealkiri, tabeli_pealkirjad, 0), 0)
Otsinguväärtus: otsingu väärtus tabeli_massiivi esimeses veerus.
Tabelimassiiv: vahemik, millest soovite otsida. Näiteks A2, D10.
Otsingu_pealkiri: pealkirja, mida soovite tabeli_massiivi pealkirjadest otsida.
Tabeli_pealkirjad: Tabeli massiivi pealkirjade viide. Nt. kui tabel on A2, D10 ja pealkirjad iga veeru ülaosas, siis selle A1: D1.
Niisiis, nüüd teame, mida vajame dünaamilise col_indexi jaoks, teeme kõik näite abil selgeks.
Dünaamilise VLOOKUP -i näide
Selle näite jaoks on meil tabel, mis sisaldab õpilaste andmeid vahemikus A4: E16.
Kasutades rullnumbrit ja pealkirja, tahan tabelist andmeid hankida. Sel juhul soovin lahtris H4 saada lahtrisse G4 kirjutatud rulli ja pealkirja H3 andmeid. Kui muudan pealkirja, tuleks lahtrist H4 alla laadida vastava vahemiku andmed.
Kirjutage see valem lahtrisse H4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Kuna meie tabelimassiiv on B4: E16, muutub meie pealkirjade massiiviks B3: E3.
Märge: Kui teie andmed on hästi struktureeritud, on veerupäistel sama arv veerge ja see on tabeli esimene rida.
Kuidas see töötab:
Niisiis, peamine osa on veeruindeksi numbri automaatne hindamine. Selleks kasutasime funktsiooni MATCH.
MATCH (H3, B3: E3,0): Kuna H3 sisaldab “õpilane”, tagastab MATCH 2. Kui H3 -l oleks see “hinne”, oleks see tagastanud 4 jne. VLOOKUP valemil on lõpuks see col_index_num.
= VLOOKUP (G4, B4: E16,2,0)
Nagu me teame, tagastab funktsioon MATCH antud väärtuse indeksi numbri esitatud ühemõõtmelises vahemikus. Seega otsib MATCH kõiki väärtusi, mis on kirjutatud H3 -sse vahemikus B3: E3 ja tagastab oma indeksinumbri.
Nüüd, kui muudate H3 pealkirja, kui see on pealkirjades, tagastab see valem vastava veeru väärtuse. Vastasel juhul ilmub teile viga #N/A.
VLOOKUP kiiresti mitmes veerus
Ülaltoodud näites vajasime vastust ühe veeru väärtusest. Aga mis siis, kui soovite saada mitu veergu korraga. Kui kopeerite ülaltoodud valemi, tagastab see vead. Kaasaskantavaks muutmiseks peame selles tegema mõningaid väiksemaid muudatusi.
Absoluutsete viidete kasutamine koos VLOOKUP -iga
Kirjutage lahtrisse H2 järgmine valem.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Nüüd kopeerige H2 kõikidesse lahtritesse vahemikus H2: J6, et see andmetega täita.
Kuidas see töötab:
Siin olen andnud absoluutne viide igast vahemikust, välja arvatud VLOOKUP ($ G2) ja veerg otsinguväärtuses MATCH (H $ 1).
$ G2: See võimaldab reas muuta otsingu väärtust funktsiooni VLOOKUP jaoks, kopeerides allapoole, kuid piirab veeru muutmist paremale kopeerimisel. Mis paneb VLOOKUP otsima id veerust G ainult suhtelise reaga.
Sarnaselt H $ 1 võimaldab veergu horisontaalselt kopeerides muuta ja piirab rida allapoole kopeerimisel.
Nimetatud vahemike kasutamine
Ülaltoodud näide töötab hästi, kuid selle valemi lugemine ja kirjutamine on keeruline. Ja see pole üldse kaasaskantav. Seda saab lihtsustada kasutades nimega vahemikud.
Esmalt teeme siin mõned nimed. Selle näite jaoks nimetasin
$ B $ 2: $ E $ 14: kui andmed
$ B $ 1: $ E $ 1: rubriikidena
1 H $: Nimetage see pealkirjaks. Muutke veerud suhtelisteks. Selleks valige H1. Vajutage klahvikombinatsiooni CTRL+F3, klõpsake nuppu Uus, jaotises Viitab jaotise H eemaldamisele '$'.
$ G2: Samamoodi nimetage see RollNo -ks. See aeg muudab rea suhteliseks, eemaldades 2 esiosast '$'.
Nüüd, kui teil on kõik nimed lehel, kirjutage see valem kõikjal Exceli failis. See saab alati õige vastuse.
= VLOOKUP (RollNo, Data, MATCH (Rubriik, Rubriigid, 0), 0)
Vaata, igaüks saab seda lugeda ja sellest aru saada.
Niisiis, kasutades neid meetodeid, saate muuta col_index_num dünaamiliseks. Andke mulle teada, kas sellest oli allpool kommentaaride osas abi.
Kuidas kasutada tta funktsioon VLOOKUP Excelis
Suhteline ja absoluutne viide Excelis
Nimetatud vahemikud Excelis
Kuidas otsida erinevatelt Exceli lehtedelt
VLOOKUP Mitu väärtust
Populaarsed artiklid
50 Exceli otsetee tootlikkuse suurendamiseks : Saate oma ülesandega kiiremini hakkama. Need 50 otseteed aitavad teil Excelis veelgi kiiremini töötada.
Kuidas kasutada tta funktsioon VLOOKUP Excelis : See on üks enim kasutatud ja populaarsemaid Exceli funktsioone, mida kasutatakse erinevate vahemike ja lehtede väärtuse otsimiseks.
Funktsiooni COUNTIF kasutamine Excelis : Loendage väärtused tingimustega, kasutades seda hämmastavat funktsiooni. Te ei pea oma andmeid teatud väärtuste loendamiseks filtreerima. Countif funktsioon on armatuurlaua ettevalmistamisel hädavajalik.
Funktsiooni SUMIF kasutamine Excelis : See on veel üks armatuurlaua oluline funktsioon. See aitab teil teatud tingimustel väärtusi kokku võtta.