V funkciji VLOOKUP pogosto definiramo col_index_no static. Težko ga kodiramo v formuli VLOOKUP, na primer VLOOKUP (id, podatki,3, 0). Težava nastane, ko vnesemo ali izbrišemo stolpec znotraj podatkov. Če odstranimo ali dodamo stolpec pred ali po tretjem stolpcu, se tretji stolpec ne bo več skliceval na predvideni stolpec. To je en problem. Drugo je, če imate za iskanje več stolpcev. V vsaki formuli boste morali urediti indeks stolpca. Enostavno lepljenje kopij ne bo pomagalo.
Kaj pa, če lahko VLOOKUP -u poveste, naj pogleda naslove in vrne le ujemajočo se vrednost naslova. To se imenuje dvosmerni VLOOKUP.
Na primer, če imam formulo VLOOKUP zaoznake stolpec, potem naj poišče VLOOKUP oznake stolpec v podatkih in vrnitev vrednosti iz tega stolpca. To bo rešilo naš problem.
Hmm … V redu, kako to naredimo? Z uporabo funkcije Match v funkciji VLOOKUP.
Splošna formula
=VLOOKUP(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Lookup_value: iskalno vrednost v prvem stolpcu matrike_tabel.
Matrika_maza: obseg, v katerem želite poiskati. Npr. A2, D10.
Lookup_heading: naslov, ki ga želite poiskati v naslovih table_array.
Naslovi tabel: Sklic naslovov v matriki tabel. Npr. če je tabela A2, D10 in naslovi na vrhu vsakega stolpca, potem njen A1: D1.
Torej, zdaj vemo, kaj potrebujemo za dinamični col_index, razčistimo vse s primerom.
Primer dinamičnega VLOOKUP -a
Za ta primer imamo to tabelo, ki vsebuje podatke o študentih v območju A4: E16.
Z uporabo roll no in heading želim pridobiti podatke iz te tabele. V tem primeru želim v celici H4 dobiti podatke o zvitku brez zapisa v celici G4 in o naslovu v H3. Če spremenim naslov, je treba podatke iz ustreznega obsega pridobiti v celici H4.
To formulo zapišite v celico H4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Ker je naša matrika tabel B4: E16, postane matrika naslovov B3: E3.
Opomba: Če so vaši podatki dobro strukturirani, imajo naslovi stolpcev enako število stolpcev in so prva vrstica v tabeli.
Kako deluje:
Torej, glavni del je samodejno ocenjevanje številke indeksa stolpca. Za to smo uporabili funkcijo MATCH.
UJEMA (H3, B3: E3,0): Ker H3 vsebuje "študent", se MATCH vrne 2. Če bi imel H3 "oceno", bi vrnil 4 itd. Formula VLOOKUP bo končno dobila svoj col_index_num.
= VLOOKUP (G4, B4: E16,2,0)
Kot vemo, funkcija MATCH vrne številko indeksa dane vrednosti v podanem enodimenzionalnem območju. Tako bo MATCH poiskal katero koli vrednost, zapisano v H3 v območju B3: E3, in vrnil svojo indeksno številko.
Zdaj, ko spremenite naslov v H3, če je v naslovih, bo ta formula vrnila vrednost iz ustreznega stolpca. V nasprotnem primeru boste imeli napako #N/A.
VLOOKUP v več stolpcih hitro
V zgornjem primeru smo potrebovali odgovor iz vrednosti enega stolpca. Kaj pa, če želite dobiti več stolpcev hkrati. Če kopirate zgornjo formulo, bo vrnila napake. V njem moramo narediti nekaj manjših sprememb, da bo prenosljiv.
Uporaba absolutnih referenc z VLOOKUP
Spodnjo formulo zapišite v celico H2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Zdaj kopirajte H2 v vse celice v območju H2: J6, da ga napolnite s podatki.
Kako deluje:
Tukaj sem dal absolutna referenca vsakega obsega, razen vrstice v iskalni vrednosti za VLOOKUP ($ G2) in stolpec v lookup_value za MATCH (H $ 1).
$ G2: To bo omogočilo spremembo vrstice za iskalno vrednost za funkcijo VLOOKUP med kopiranjem navzdol, vendar omejitev spreminjanja stolpca pri kopiranju na desno. Zaradi česar bo VLOOKUP iskal ID iz stolpca G samo z relativno vrstico.
Podobno, H $ 1 bo omogočilo spreminjanje stolpca pri vodoravnem kopiranju in omejil vrstico pri kopiranju navzdol.
Uporaba imenovanih obsegov
Zgornji primer deluje dobro, vendar je branje in pisanje te formule zahtevno. In to sploh ni prenosno. To je mogoče poenostaviti z uporabo imenovanih obsegov.
Tu bomo najprej nekaj poimenovali. Za ta primer sem poimenoval
$ B $ 2: $ E $ 14: kot Podatki
$ B $ 1: $ E $ 1: kot naslovi
H $ 1: Poimenujte ga kot naslov. Naj bodo stolpci relativni. To storite tako, da izberete H1. Pritisnite CTRL+F3, kliknite novo, v razdelku Se nanaša na odstranjevanje '$' s sprednje strani H.
$ G2: Podobno ga poimenujte kot RollNo. Ta čas naredi vrstico relativno, tako da odstranite '$' s sprednje strani 2.
Zdaj, ko imate vsa imena na listu, zapišite to formulo kjer koli v datoteki Excel. Vedno bo dobil pravilen odgovor.
= VLOOKUP (RollNo, Data, MATCH (Naslov, Naslovi, 0), 0)
Glej, vsakdo lahko to prebere in razume.
Tako lahko s temi metodami naredite col_index_num dinamičnim. Sporočite mi, če vam je to pomagalo v spodnjem razdelku s komentarji.
Kako uporabljati tFunkcija VLOOKUP v Excelu
Relativna in absolutna referenca v Excelu
Imenovani obsegi v Excelu
Kako VLOOKUP iz različnih Excelovih listov
VLOOKUP Več vrednosti
Priljubljeni članki
50 Bližnjica v Excelu za večjo produktivnost : Hitreje opravite svojo nalogo. S temi 50 bližnjicami boste še hitreje delali v Excelu.
Kako uporabljati tFunkcija VLOOKUP v Excelu : To je ena najpogosteje uporabljenih in priljubljenih funkcij programa Excel, ki se uporablja za iskanje vrednosti iz različnih obsegov in listov.
Kako uporabljati funkcijo COUNTIF v Excelu : Štejte vrednosti s pogoji s to neverjetno funkcijo. Za štetje določenih vrednosti vam ni treba filtrirati podatkov. Funkcija Countif je bistvena za pripravo vaše armaturne plošče.
Kako uporabljati funkcijo SUMIF v Excelu : To je še ena bistvena funkcija nadzorne plošče. To vam pomaga povzeti vrednosti za posebne pogoje.