Във функцията VLOOKUP често дефинираме col_index_no static. Ние го кодираме твърдо във формулата VLOOKUP, като VLOOKUP (идентификатор, данни,3, 0). Проблемът възниква, когато вмъкнем или изтрием колона в данните. Ако премахнем или добавим колона преди или след третата колона, третата колона вече няма да се отнася за предвидената колона. Това е един проблем. Друго е, когато имате няколко колони за търсене. Ще трябва да редактирате индекса на колоната във всяка формула. Простото поставяне на копие няма да помогне.
Но какво ще кажете, ако можете да кажете на VLOOKUP да преглежда заглавията и да връща само съвпадаща стойност на заглавията. Това се нарича двупосочен VLOOKUP.
Например, ако имам формула VLOOKUP замарки колона, тогава VLOOKUP трябва да търси марки колона в данни и възвращаема стойност от тази колона. Това ще реши проблема ни.
Хм… Добре, как да го направим? Като използвате функцията Match в рамките на функцията VLOOKUP.
Обща формула
=VLOOKUP(lookup_value, table_array, MATCH (lookup_heading, table_headings, 0), 0)
Lookup_value: стойността за търсене в първата колона на table_array.
Таблица_масив: диапазона, в който искате да направите справка. Например, A2, D10.
Lookup_heading: заглавието, което искате да потърсите в заглавията на table_array.
Таблица_заглавия: Позоваване на заглавията в масива от таблици. Напр. ако таблицата е A2, D10 и заглавия в горната част на всяка колона, тогава нейната A1: D1.
Така че, сега знаем от какво се нуждаем за динамичен col_index, нека изясним всичко с пример.
Пример за динамично VLOOKUP
За този пример имаме тази таблица, която съдържа данни за ученици в диапазон A4: E16.
Използвайки roll no и heading, искам да извлека данни от тази таблица. За този пример, в клетка H4, искам да получа данни за ролка, която не е записана в клетка G4, и за заглавие в H3. Ако променя заглавието, данните от съответния диапазон трябва да бъдат извлечени в клетка H4.
Напишете тази формула в клетка H4
= VLOOKUP (G4, B4: E16, MATCH (H3, B3: E3,0), 0)
Тъй като нашият табличен масив е B4: E16, нашият масив от заглавия става B3: E3.
Забележка: Ако вашите данни са добре структурирани, тогава заглавията на колони ще имат същия брой колони и това е първият ред в таблицата.
Как работи:
И така, основната част е автоматичното оценяване на номера на индекса на колоната. За целта използвахме функцията MATCH.
МАТЧ (H3, B3: E3,0): Тъй като H3 съдържа „студент“, MATCH ще върне 2. Ако H3 имаше „Оценка“, щеше да върне 4 и т.н. Формулата VLOOKUP най -накрая ще има col_index_num.
= VLOOKUP (G4, B4: E16,2,0)
Както знаем, функцията MATCH връща индексния номер на дадена стойност в предоставения едноизмерен диапазон. Следователно MATCH ще търси всяка стойност, записана в H3 в диапазон B3: E3 и ще върне индексния си номер.
Сега, когато променяте заглавие в H3, ако е в заглавия, тази формула ще връща стойност от съответната колона. В противен случай ще имате грешка #N/A.
VLOOKUP в няколко колони бързо
В горния пример се нуждаехме от отговора от стойността на една колона. Но какво ще стане, ако искате да получите няколко колони наведнъж. Ако копирате горната формула, тя ще върне грешки. Трябва да направим някои малки промени в него, за да го направим преносим.
Използване на абсолютни препратки с VLOOKUP
Напишете формулата по -долу в клетка Н2.
= VLOOKUP ($ G2, $ B $ 2: $ E $ 14, MATCH (H $ 1, $ B $ 1: $ E $ 1,0), 0)
Сега копирайте H2 във всички клетки в диапазон H2: J6, за да го попълните с данни.
Как работи:
Тук съм дал абсолютна справка на всеки диапазон, с изключение на ред в стойност за търсене за VLOOKUP ($ G2) и колона в lookup_value за MATCH (H $ 1).
$ G2: Това ще позволи на реда да се промени за стойност за търсене за функцията VLOOKUP, докато копирате надолу, но ще ограничите промяната на колоната при копиране вдясно. Което ще накара VLOOKUP да търси Id от колона G само със съответния ред.
По същия начин, H $ 1 ще позволи на колоната да се промени при хоризонтално копиране и ще ограничи реда при копиране надолу.
Използване на именовани диапазони
Горният пример работи добре, но става предизвикателство да се чете и пише тази формула. И това изобщо не е преносимо. Това може да бъде опростено с помощта именувани диапазони.
Тук първо ще направим някои имена. За този пример дадох име
$ B $ 2: $ E $ 14: като Данни
$ B $ 1: $ E $ 1: като заглавия
H $ 1: Наречете го като заглавие. Направете колоните относителни. За да направите това, изберете H1. Натиснете CTRL+F3, щракнете върху ново, в Препратки към раздел премахнете „$“ от предната страна на H.
$ G2: По същия начин го наречете като RollNo. Този път прави реда относителен, като премахва '$' от предната част на 2.
Сега, когато имате всички имена на листа, напишете тази формула навсякъде в Excel файл. Той винаги ще получава верния отговор.
= VLOOKUP (RollNo, Data, MATCH (Заглавие, Заглавия, 0), 0)
Вижте, всеки може да прочете това и да го разбере.
Така че, използвайки тези методи, можете да направите col_index_num динамичен. Кажете ми дали това е било полезно в раздела за коментари по -долу.
Как да използвате tФункцията VLOOKUP в Excel
Относителна и абсолютна справка в Excel
Назовани диапазони в Excel
Как да VLOOKUP от различни Excel лист
VLOOKUP Множество стойности
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате tФункцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.