Funkcijā VLOOKUP mēs bieži definējam col_index_no static. Mēs to kodējam VLOOKUP formulā, piemēram, VLOOKUP (id, dati,3, 0). Problēma rodas, ievietojot vai dzēšot sleju datu iekšienē. Ja mēs noņemsim vai pievienosim kolonnu pirms vai pēc trešās kolonnas, trešā sleja vairs neatsauksies uz paredzēto kolonnu. Šī ir viena problēma. Cits ir gadījums, kad jums ir jāatrod vairākas kolonnas. Jums būs jārediģē kolonnu indekss katrā formulā. Vienkārša kopēšana un ielīmēšana nepalīdzēs.
Bet kā būtu, ja jūs varētu pateikt VLOOKUP apskatīt virsrakstus un atgriezt tikai atbilstošo virsrakstu vērtību. To sauc par divvirzienu VLOOKUP.
Piemēram, ja man ir VLOOKUP formulazīmes slejā, tad VLOOKUP jāmeklē zīmes datu slejā un atgriezt vērtību no šīs slejas. Tas atrisinās mūsu problēmu.
Hmm … Labi, tad kā mēs to darām? Izmantojot funkciju Match Funkcija VLOOKUP.
Vispārīgā formula
=MEKLĒŠANA(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Uzmeklēšanas_vērtība: uzmeklēšanas vērtība tabulas_masīva pirmajā kolonnā.
Tabulas_masīvs: diapazons, kurā vēlaties veikt uzmeklēšanu. Piemēram, A2, D10.
Lookup_heading: virsrakstu, kuru vēlaties meklēt tabulas_masīva virsrakstos.
Table_headings: Atsauce uz tabulas masīva virsrakstiem. Piem. ja tabula ir A2, D10 un virsraksti katras kolonnas augšpusē, tad tā A1: D1.
Tātad, tagad mēs zinām, kas mums vajadzīgs dinamiskajai kol_index, noskaidrosim visu ar piemēru.
Dinamiskās VLOOKUP piemērs
Šajā piemērā mums ir šī tabula, kurā ir dati par skolēniem diapazonā A4: E16.
Izmantojot sarakstu Nr un virsrakstu, es vēlos izgūt datus no šīs tabulas. Šajā gadījumā šūnā H4 es vēlos iegūt datus par ruļļu Nr, kas rakstīts šūnā G4 un virsrakstu H3. Ja es mainu virsrakstu, dati no attiecīgā diapazona jāiegūst šūnā H4.
Ierakstiet šo formulu šūnā H4
= MEKLĒŠANA (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Tā kā mūsu tabulu masīvs ir B4: E16, mūsu virsrakstu masīvs kļūst par B3: E3.
Piezīme: Ja jūsu dati ir labi strukturēti, kolonnu virsrakstos būs vienāds kolonnu skaits, un tā ir tabulas pirmā rinda.
Kā tas strādā:
Tātad galvenā daļa ir kolonnu indeksa numura automātiska novērtēšana. Lai to izdarītu, mēs izmantojām funkciju MATCH.
SASTĀVS (H3, B3: E3,0): Tā kā H3 satur “students”, MATCH atgriezīs 2. Ja H3 būtu “Novērtējums”, tas būtu atgriezis 4 utt. VLOOKUP formula beidzot iegūs tā col_index_num.
= MEKLĒŠANA (G4, B4: E16,2,0)
Kā mēs zinām, funkcija MATCH atgriež dotās vērtības indeksa numuru piegādātajā viendimensiju diapazonā. Tādējādi MATCH meklēs jebkuru vērtību, kas ierakstīta H3 diapazonā B3: E3, un atgriezīs indeksa numuru.
Tagad, kad mainīsit virsrakstu H3, ja tas ir virsrakstos, šī formula atgriež vērtību no attiecīgās slejas. Pretējā gadījumā jums radīsies kļūda #N/A.
ĀTRA MEKLĒŠANA vairākās kolonnās
Iepriekš minētajā piemērā atbilde bija nepieciešama no vienas kolonnas vērtības. Bet ko darīt, ja vēlaties iegūt vairākas kolonnas vienlaikus. Ja kopējat iepriekš minēto formulu, tas atgriezīs kļūdas. Mums ir jāveic dažas nelielas izmaiņas, lai padarītu to pārnēsājamu.
Absolūtu atsauču izmantošana ar VLOOKUP
Ierakstiet zemāk esošo formulu šūnā H2.
= APSKATS ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Tagad kopējiet H2 visās šūnās diapazonā H2: J6, lai to aizpildītu ar datiem.
Kā tas strādā:
Šeit es esmu devis absolūta atsauce no katra diapazona, izņemot rindu VLOOKUP uzmeklēšanas vērtībā ($ G2) un kolonna lookup_value par MATCH (H $ 1).
$ G2: Tas ļaus rindai mainīt uzmeklēšanas vērtību funkcijai VLOOKUP, kopējot uz leju, bet ierobežos kolonnas maiņu, kad tā tiks kopēta pa labi. Tas liks VLOOKUP meklēt ID no kolonnas G tikai ar relatīvo rindu.
Līdzīgi, H $ 1 ļaus kolonnai mainīties, ja to kopēs horizontāli, un ierobežos rindu, kopējot uz leju.
Izmantojot nosauktos diapazonus
Iepriekš minētais piemērs darbojas labi, taču ir grūti lasīt un rakstīt šo formulu. Un tas vispār nav pārnēsājams. To var vienkāršot, izmantojot nosaukti diapazoni.
Vispirms mēs šeit nosauksim dažus vārdus. Šajā piemērā es nosaucu
$ B $ 2: $ E $ 14: kā Dati
$ B $ 1: $ E $ 1: kā virsrakstus
1 USD: Nosauciet to kā virsrakstu. Padariet kolonnas relatīvas. Lai to izdarītu, atlasiet H1. Nospiediet taustiņu kombināciju CTRL+F3, noklikšķiniet uz jauna, sadaļā Atsaucieties uz '$' noņemšanu no H priekšpuses.
$ G2: Līdzīgi nosauciet to kā RollNo. Šis laiks padara rindu relatīvu, noņemot '$' no 2 priekšpuses.
Tagad, kad lapā ir visi vārdi, uzrakstiet šo formulu jebkurā vietā uz Excel faila. Tas vienmēr saņems pareizo atbildi.
= VLOOKUP (RollNo, Data, MATCH (Heading, Headings, 0), 0)
Redzi, ikviens to var izlasīt un saprast.
Tātad, izmantojot šīs metodes, jūs varat padarīt col_index_num dinamisku. Ļaujiet man zināt, vai tas bija noderīgi komentāru sadaļā zemāk.
Kā lietot tVLOOKUP funkcija programmā Excel
Relatīvā un absolūtā atsauce programmā Excel
Nosauktie diapazoni programmā Excel
Kā VLOOKUP no dažādām Excel lapām
VLOOKUP vairākas vērtības
Populāri raksti
50 Excel saīsne produktivitātes paaugstināšanai : Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā lietot tVLOOKUP funkcija programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā programmā Excel izmantot funkciju COUNTIF : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.