ВПР с динамическим индексом Col
В link: / formulas-and-functions-Introduction-of-vlookup-function [функция VLOOKUP] мы часто определяем col_index_no static. Мы жестко кодируем его в формуле VLOOKUP, например VLOOKUP (id, data, 3,0). Проблема возникает, когда мы вставляем или удаляем столбец в данных. Если мы удалим или добавим столбец до или после 3-го столбца, 3-й столбец больше не будет ссылаться на предполагаемый столбец. Это одна проблема. Другой — когда у вас есть несколько столбцов для поиска. Вам нужно будет изменить индекс столбца в каждой формуле. Простое копирование не поможет.
Но как насчет того, чтобы вы могли указать VLOOKUP смотреть на заголовки и возвращать только совпадающие значения заголовков. Это называется двусторонним ВПР.
Например, если у меня есть формула ВПР для столбца меток, то функция ВПР должна искать столбец меток в данных и возвращать значение из этого столбца. Это решит нашу проблему.
Хм … Итак, как нам это сделать? Используя link: / lookup-formulas-excel-match-function [функция сопоставления] внутри`link: / formulas-and-functions-Introduction-of-vlookup-function [функция VLOOKUP] `.
Общая формула
=VLOOKUP(lookup_value,table_array,MATCH(lookup_heading,table_headings,0),0)
Lookup_value: значение поиска в первом столбце table_array.
Table_array: диапазон, в котором вы хотите выполнить поиск. Например, A2, D10.
Lookup_heading: заголовок, который вы хотите найти в заголовках table_array.
Table_headings: Ссылка на заголовки в массиве таблиц. Например. если таблица — это A2, D10 и заголовки вверху каждого столбца, то это A1: D1.
Итак, теперь мы знаем, что нам нужно для динамического col_index, давайте проясним все на примере.
Пример динамической ВПР В этом примере у нас есть таблица, которая содержит данные об учащихся в диапазоне A4: E16.
Используя номер свитка и заголовок, я хочу получить данные из этой таблицы. В этом случае в ячейке H4 я хочу получить данные о рулоне, записанном в ячейке G4, и о заголовке в H3. Если я изменю заголовок, данные из соответствующего диапазона должны быть получены в ячейке H4.
Запишите эту формулу в ячейку H4
=VLOOKUP(G4,B4:E16,MATCH(H3,B3:E3,0),0)
Поскольку наш массив таблиц равен B4: E16, наш массив заголовков становится B3: E3.
Примечание. Если ваши данные хорошо структурированы, то в заголовках столбцов будет одинаковое количество столбцов, и это будет первая строка в таблице.
Как это работает:
Итак, основная часть вычисляет номер индекса столбца автоматически.
Для этого мы использовали функцию ПОИСКПОЗ.
ПОИСКПОЗ (H3, B3: E3,0): поскольку H3 содержит «ученик», ПОИСКПОЗ вернет 2.
Если бы у H3 было «Grade», он бы вернул 4 и так далее. Наконец, формула ВПР будет иметь col_index_num.
=VLOOKUP(G4,B4:E16,2,0)
Как мы знаем, ссылка: / поиск-формулы-excel-match-function [MATCH]
функция возвращает порядковый номер данного значения в предоставленном одномерном диапазоне. Следовательно, link: / lookup-formulas-excel-match-function [MATCH] будет искать любое значение, записанное в H3 в диапазоне B3: E3, и вернет его номер индекса.
Теперь всякий раз, когда вы меняете заголовок в H3, если он есть в заголовках, эта формула будет возвращать значение из соответствующего столбца. В противном случае вы получите ошибку # Н / Д.
ВПР в несколько столбцов быстро
В приведенном выше примере нам нужен был ответ из одного значения столбца. Но что, если вы хотите получить сразу несколько столбцов. Если вы скопируете приведенную выше формулу, она вернет ошибки. Нам нужно внести в него небольшие изменения, чтобы сделать его переносимым.
Использование link: / excel-range-name-absolute-reference-in-excel [Absolute References] с функцией ВПР Запишите приведенную ниже формулу в ячейку H2.
=VLOOKUP($G2,$B$2:$E$14,MATCH(H$1,$B$1:$E$1,0),0)
Теперь скопируйте H2 во все ячейки в диапазоне H2: J6, чтобы заполнить их данными.
Как это работает:
Здесь я привел «link: / excel-range-name-absolute-reference-in-excel [absolute-reference]» для каждого диапазона, кроме строки в поисковом значении для 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(Heading, Headings,0),0)
Видите, каждый может это прочитать и понять.
Итак, используя эти методы, вы можете сделать col_index_num динамическим. Сообщите мне, было ли это полезно, в разделе комментариев ниже.
Статьи по теме:
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel] ссылка: Относительный% 20and% 20Absolute% 20Reference% 20in% 20Excel [Относительный и абсолютный справочник в Excel]
link: / excel-range-name-all-about-named-range-in-excel [Именованные диапазоны в Excel]
link: / lookup-formulas-how-to-vlookup-from-different-excel-sheet [Как выполнить ВПР из другого листа Excel]
link: / lookup-formulas-vlookup-multiple-values [VLOOKUP Multiple Values]
Популярные статьи
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-Повышение-продуктивность [50 ярлыков Excel для повышения производительности]: быстрее справляйтесь с задачей. Эти 50 ярлыков заставят вас работать в Excel еще быстрее.
link: / formulas-and-functions-Introduction-of-vlookup-function [Как использовать функцию ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листы.
link: / tips-countif-in-microsoft-excel [Как использовать функцию СЧЁТЕСЛИ в Excel]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений. Функция Countif важна для подготовки вашей приборной панели.
link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция информационной панели. Это поможет вам суммировать значения для конкретных условий.