U funkciji VLOOKUP često definiramo col_index_no static. Tvrdo ga kodiramo unutar formule VLOOKUP, poput VLOOKUP -a (id, podaci,3, 0). Problem nastaje kada umetnemo ili izbrišemo stupac unutar podataka. Ako uklonimo ili dodamo stupac prije ili poslije 3. stupca, 3. stupac se više neće odnositi na predviđeni stupac. Ovo je jedan problem. Drugo je kada imate više stupaca za pretraživanje. Morat ćete urediti indeks stupca u svakoj formuli. Jednostavno lijepljenje kopija neće pomoći.
Ali što ako kažete VLOOKUP -u da pogleda naslove i vrati samo odgovarajuću vrijednost naslova. To se naziva dvosmjerni VLOOKUP.
Na primjer, ako imam formulu VLOOKUP zaoznake stupac, tada bi VLOOKUP trebao tražiti oznake stupac u podacima i povratna vrijednost iz tog stupca. To će riješiti naš problem.
Hmm … U redu, pa kako ćemo to učiniti? Korištenjem funkcije podudaranja unutar funkcije VLOOKUP.
Opća formula
=VLOOKUP(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Lookup_value: vrijednost traženja u prvom stupcu matrice_tablice.
Polje_tablice: raspon u kojem želite izvršiti pretraživanje. Npr., A2, D10.
Lookup_heading: naslov koji želite pronaći u naslovima table_array.
Naslovi tablice: Referenca naslova u nizu tablica. Npr. ako je tablica A2, D10 i naslovi na vrhu svakog stupca, tada je ona A1: D1.
Dakle, sada znamo što nam je potrebno za dinamički col_index, razjasnimo sve primjerom.
Primjer dinamičkog VLOOKUP -a
Za ovaj primjer imamo ovu tablicu koja sadrži podatke učenika u rasponu A4: E16.
Koristeći roll no i heading, želim dohvatiti podatke iz ove tablice. Za ovaj primjer, u ćeliji H4 želim dobiti podatke o roli koja nije zapisana u ćeliji G4 i o naslovu u H3. Ako promijenim naslov, podaci iz odgovarajućeg raspona trebali bi se dohvatiti u ćeliju H4.
Napišite ovu formulu u ćeliju H4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Budući da je naš tablični niz B4: E16, naš niz naslova postaje B3: E3.
Bilješka: Ako su vaši podaci dobro strukturirani, tada će naslovi stupaca imati isti broj stupaca i to je prvi redak u tablici.
Kako radi:
Dakle, glavni dio je automatska procjena broja indeksa stupca. Da bismo to učinili, koristili smo funkciju MATCH.
UTAKMICA (H3, B3: E3,0): Budući da H3 sadrži “učenik”, MATCH će se vratiti 2. Da je H3 imao “Ocjenu”, vratio bi 4, itd. Formula VLOOKUP konačno će imati svoj col_index_num.
= VLOOKUP (G4, B4: E16,2,0)
Kao što znamo, funkcija MATCH vraća indeksni broj zadane vrijednosti u isporučenom jednodimenzionalnom rasponu. Stoga će MATCH tražiti bilo koju vrijednost napisanu u H3 u rasponu B3: E3 i vratiti svoj indeksni broj.
Sada kad god promijenite naslov u H3, ako je u naslovima, ova će formula vratiti vrijednost iz odgovarajućeg stupca. U suprotnom ćete imati grešku #N/A.
VLOOKUP u više stupaca brzo
U gornjem primjeru trebao nam je odgovor iz vrijednosti jednog stupca. Ali što ako želite dobiti više stupaca odjednom. Ako kopirate gornju formulu, vratit će se pogreške. Moramo napraviti neke manje izmjene kako bismo ga učinili prenosivim.
Korištenje apsolutnih referenci s VLOOKUP -om
Formulu ispod zapišite u ćeliju H2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Sada kopirajte H2 u sve ćelije u rasponu H2: J6 kako biste ga ispunili podacima.
Kako radi:
Evo dao sam apsolutna referenca svakog raspona osim retka u vrijednosti traženja za VLOOKUP (G2 USD) i stupac u lookup_value za MATCH (H $ 1).
$ G2: To će omogućiti promjenu retka za traženje vrijednosti za funkciju VLOOKUP tijekom kopiranja prema dolje, ali ograničiti promjenu stupca pri kopiranju s desne strane. Zbog čega će VLOOKUP tražiti ID iz G stupca samo s relativnim retkom.
Slično, H $ 1 dopustit će promjenu stupca pri vodoravnom kopiranju i ograničiti redak pri kopiranju prema dolje.
Korištenje imenovanih raspona
Gornji primjer dobro funkcionira, ali postaje izazovan za čitanje i pisanje ove formule. A ovo uopće nije prijenosno. To se može pojednostaviti korištenjem imenovani rasponi.
Ovdje ćemo prvo dati neka imena. Za ovaj primjer dao sam ime
$ B $ 2: $ E $ 14: kao Podaci
$ B $ 1: $ E $ 1: kao Naslovi
H $ 1: Imenujte ga kao Naslov. Neka stupci budu relativni. Da biste to učinili, odaberite H1. Pritisnite CTRL+F3, kliknite na novo, u odjeljku Odnosi se na uklanjanje "$" s prednje strane H.
G2 USD: Slično, nazovite ga RollNo. Ovo vrijeme čini red relativnim uklanjanjem '$' s prednje strane 2.
Sada, kad imate sva imena na listu, napišite ovu formulu bilo gdje u excel datoteci. Uvijek će dobiti točan odgovor.
= VLOOKUP (RollNo, Data, MATCH (Naslov, Naslovi, 0), 0)
Vidite, svatko ovo može pročitati i razumjeti.
Dakle, koristeći ove metode, col_index_num možete učiniti dinamičnim. Javite mi je li vam ovo pomoglo u donjem odjeljku komentara.
Kako koristiti tFunkcija VLOOKUP u Excelu
Relativna i apsolutna referenca u Excelu
Imenovani rasponi u Excelu
Kako VLOOKUP iz različitih Excel tablica
VLOOKUP Više vrijednosti
Popularni članci
50 Excel prečac za povećanje vaše produktivnosti : Brže izvršite svoj zadatak. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti tFunkcija VLOOKUP u Excelu : Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti funkciju COUNTIF u Excelu : Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste brojali određene vrijednosti. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu : Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.