Загрузите образец книги
В этом руководстве вы узнаете, как извлекать данные из нескольких столбцов с помощью функций ПОИСКПОЗ и ВПР в Excel и Google Таблицах.
Почему нужно совмещать ВПР и СОПРЯЖЕНИЕ?
Традиционно при использовании функции ВПР вы вводите порядковый номер столбца чтобы определить, из какого столбца извлекать данные.
Это создает две проблемы:
- Если вы хотите получить значения из нескольких столбцов, вы должны вручную ввести порядковый номер столбца для каждого столбца
- Если вы вставляете или удаляете столбцы, ваш порядковый номер столбца больше не будет действительным.
Чтобы сделать вашу функцию ВПР динамической, вы можете найти порядковый номер столбца с функцией ПОИСКПОЗ.
| 1 | = ВПР (G3; B3: E5; ПОИСКПОЗ (H2; B2: E2,0); ЛОЖЬ) |
Посмотрим, как работает эта формула.
Функция ПОИСКПОЗ
Функция ПОИСКПОЗ вернет порядковый номер столбца желаемого заголовка столбца.
В приведенном ниже примере номер индекса столбца для «Возраст» вычисляется функцией ПОИСКПОЗ:
| 1 | = MATCH ("Возраст"; B2: E2,0) |
«Возраст» - это заголовок 2-го столбца, поэтому возвращается 2.
Примечание: последний аргумент функции MATCH должен иметь значение 0 для точного совпадения.
Функция ВПР
Теперь вы можете просто вставить результат функции MATCH в свою функцию VLOOKUP:
| 1 | = ВПР (G3; B3: E5; H3; ЛОЖЬ) |
Замена аргумента индекса столбца функцией ПОИСКПОЗ дает нам исходную формулу:
| 1 | = ВПР (G3; B3: E5; ПОИСКПОЗ (H2; B2: E2,0); ЛОЖЬ) |
Вставка и удаление столбцов
Теперь, когда вы вставляете или удаляете столбцы в диапазоне данных, результат вашей формулы не изменится.
В приведенном выше примере мы добавили Учитель столбец к диапазону, но по-прежнему хотите, чтобы учащийся Возраст. Выходные данные функции MATCH определяют, что «Age» теперь является третьим элементом в диапазоне заголовков, а функция VLOOKUP использует 3 в качестве индекса столбца.
Блокировка ссылок на ячейки
Чтобы наши формулы было легче читать, мы показали формулы без заблокированных ссылок на ячейки:
| 1 | = ВПР (G3; B3: E5; ПОИСКПОЗ (H2; B2: E2,0); ЛОЖЬ) |
Но эти формулы не будут работать должным образом при копировании и вставке в другое место в файле. Вместо этого вы должны использовать ссылки на заблокированные ячейки следующим образом:
| 1 | = ВПР ($ G3; $ B $ 3: $ E $ 5; ПОИСКПОЗ (H $ 2; $ B $ 2: $ E $ 2,0); ЛОЖЬ) |
Прочтите нашу статью о блокировке ссылок на ячейки, чтобы узнать больше.
ВПР и СООТВЕТСТВИЕ объединены в Google Таблицах
Эти формулы работают в Google Таблицах точно так же, как и в Excel.
