Funkcijoje VLOOKUP dažnai apibrėžiame col_index_no static. Mes jį koduojame pagal VLOOKUP formulę, pvz., VLOOKUP (id, duomenys,3, 0). Problema kyla, kai įterpiame arba ištriname duomenų stulpelį. Jei pašalinsime arba pridėsime stulpelį prieš arba po trečiojo stulpelio, trečiasis stulpelis nebebus susijęs su numatytu stulpeliu. Tai viena problema. Kitas yra tada, kai turite ieškoti kelių stulpelių. Turėsite redaguoti kiekvienos formulės stulpelių indeksą. Paprastas kopijavimo įklijavimas nepadės.
Bet kaip, jei galite liepti VLOOKUP pažiūrėti antraštes ir grąžinti tik atitinkančių antraščių vertę. Tai vadinama dvipusiu VLOOKUP.
Pavyzdžiui, jei turiu VLOOKUP formulęženklų stulpelį, tada VLOOKUP turėtų ieškoti ženklų stulpelį duomenyse ir grąžinkite vertę iš to stulpelio. Tai išspręs mūsų problemą.
Hmm … Gerai, tai kaip mums tai padaryti? Naudojant funkciją „Match Function“ VLOOKUP funkcijoje.
Bendra formulė
=VLOOKUP(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Lookup_value: paieškos reikšmė pirmajame lentelės_masyvo stulpelyje.
Lentelės_masyvas: diapazonas, kuriame norite atlikti paiešką. Pvz., A2, D10.
Lookup_heading: antraštę, kurią norite ieškoti table_array antraštėse.
Table_headings: Lentelės masyvo antraščių nuoroda. Pvz. jei lentelė yra A2, D10 ir antraštės kiekvieno stulpelio viršuje, tada jos A1: D1.
Taigi, dabar mes žinome, ko mums reikia dinaminiam col_index, išsiaiškinkime viską pavyzdžiu.
Dinaminio VLOOKUP pavyzdys
Šiame pavyzdyje turime šią lentelę, kurioje yra A4: E16 diapazono mokinių duomenys.
Naudodamas ritinio Nr. Ir antraštę, noriu gauti duomenis iš šios lentelės. Šiuo atveju H4 langelyje noriu gauti duomenis apie ritinį Nr, parašytą G4 langelyje ir antraštę H3. Jei pakeisiu antraštę, duomenys iš atitinkamo diapazono turėtų būti gauti ląstelėje H4.
Parašykite šią formulę langelyje H4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Kadangi mūsų lentelių masyvas yra B4: E16, mūsų antraščių masyvas tampa B3: E3.
Pastaba: Jei jūsų duomenys yra gerai struktūrizuoti, stulpelių antraštėse bus vienodas stulpelių skaičius ir tai yra pirmoji lentelės eilutė.
Kaip tai veikia:
Taigi, pagrindinė dalis yra stulpelio indekso numerio įvertinimas automatiškai. Norėdami tai padaryti, naudojome MATCH funkciją.
Rungtynės (H3, B3: E3,0): Kadangi H3 yra „studentas“, „MATCH“ grąžins 2. Jei H3 turėtų „pažymį“, jis grąžintų 4 ir pan. Pagaliau VLOOKUP formulė turės col_index_num.
= VLOOKUP (G4, B4: E16,2,0)
Kaip žinome, funkcija MATCH grąžina pateiktos vienos dimensijos diapazono vertės indeksą. Taigi, MATCH ieškos bet kokios vertės, parašytos H3 diapazone B3: E3, ir grąžins savo indekso numerį.
Dabar, kai pakeisite H3 antraštę, jei ji yra antraštėse, ši formulė grąžins reikšmę iš atitinkamo stulpelio. Priešingu atveju turėsite #N/A klaidą.
VLOOKUP greitai keliuose stulpeliuose
Pirmiau pateiktame pavyzdyje mums reikėjo atsakymo iš vienos stulpelio vertės. Bet ką daryti, jei norite gauti kelis stulpelius vienu metu. Jei nukopijuosite aukščiau pateiktą formulę, ji grąžins klaidas. Turime atlikti keletą nedidelių pakeitimų, kad jis būtų nešiojamas.
Absoliutų nuorodų naudojimas su VLOOKUP
Parašykite žemiau esančią formulę langelyje H2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Dabar nukopijuokite H2 į visas H2: J6 diapazono langelius, kad užpildytumėte duomenis.
Kaip tai veikia:
Čia aš daviau absoliuti nuoroda kiekvieno diapazono, išskyrus eilutę pagal VLOOKUP ($ G2) ir stulpelį lookup_value, skirtą MATCH (1 USD).
$ G2: Tai leis eilutei pakeisti VLOOKUP funkcijos paieškos vertę kopijuojant žemyn, bet apriboti stulpelio keitimą nukopijuojant į dešinę. Dėl to VLOOKUP ieškos ID iš G stulpelio tik su atitinkama eilute.
Panašiai, 1 USD leis keisti stulpelį, kai nukopijuojamas horizontaliai, ir apribos eilutę, kai nukopijuos žemyn.
Naudojant pavadintus diapazonus
Aukščiau pateiktas pavyzdys veikia gerai, tačiau tampa sunku skaityti ir rašyti šią formulę. Ir tai visai nėra nešiojama. Tai galima supaprastinti naudojant pavadinti diapazonai.
Pirmiausia čia įvardinsime. Šiame pavyzdyje aš pavadinau
$ B $ 2: $ E $ 14: kaip Duomenys
$ B $ 1: $ E $ 1: kaip antraštės
1 USD: Pavadinkite tai kaip antraštę. Padarykite stulpelius santykinius. Norėdami tai padaryti, pasirinkite H1. Paspauskite CTRL+F3, spustelėkite naują, skyriuje Nuoroda į skyrių „$“ pašalinti iš H.
$ G2: Panašiai pavadinkite jį kaip RollNo. Šį kartą eilutė tampa santykinė, pašalinus „$“ iš 2 priekio.
Dabar, kai lape turite visus pavadinimus, parašykite šią formulę bet kur „Excel“ faile. Jis visada gaus teisingą atsakymą.
= VLOOKUP (RollNo, duomenys, MATCH (antraštė, antraštės, 0), 0)
Žiūrėk, kiekvienas gali tai perskaityti ir suprasti.
Taigi, naudodami šiuos metodus, galite padaryti col_index_num dinamišką. Leiskite man žinoti, ar tai buvo naudinga komentarų skiltyje žemiau.
Kaip naudotis t„VLOOKUP“ funkcija „Excel“
Santykinė ir absoliuti nuoroda „Excel“
Pavadinti diapazonai „Excel“
Kaip VLOOKUP iš skirtingų „Excel“ lapų
VLOOKUP Kelios vertės
Populiarūs straipsniai
50 „Excel“ spartusis klavišas produktyvumui padidinti : Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudotis t„VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.
Kaip naudoti funkciją „COUNTIF“ programoje „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudotis „SUMIF“ funkcija „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.