Если вы знакомы с функцией VLOOKUP (ВПР) или ее горизонтальным аналогом HLOOKUP (ГПР) , то должны помнить, что эти замечательные функции ищут информацию только по одному параметру, то есть в одномерном массиве - по строке или по столбцу. А если нам необходимо выбирать данные из двумерной таблицы по стечению сразу двух параметров - и по строке и по столбцу одновременно? Давайте рассмотрим несколько вариантов объединения таблиц.
Предположим, что нам нужно соединить две таблицы, которые отражают кредитный портфель и портфель обеспечения:
Мы можем начать использовать функцию VLOOKUP , чтобы соединить каждый отдельный столбец, однако если у нас таблицы имеют чрезвычайно много строк и столбцов, то это занятие может превратиться в настоящую муку. Однако из этой ситуации есть достаточно простой выход, поскольку Excel имеет следующие две замечательные функции, как INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) из категории Ссылки и массивы (Lookup and Reference) , что в паре работают как 2D VLOOKUP.
Хорошо, что нам необходимо сделать, чтобы быстро соединить две таблицы? Давайте для начала скопируем шапку второй таблицы (ту, что мы будем присоединять) и вставим ее рядом с шапкой первой таблице. По названию шапки функция MATCH (ПОИСКПОЗ) будет выдавать нам порядковый номер колонки, а по номеру сделки - необходим нам порядковый номер строки.
Фактически, мы хотим найти значение ячейки с пересечения определенной строки и столбца в таблице. Для наглядности, разобьем задачу на три этапа.
Итак, сочетая все вышеперечисленное в одну формулу, получаем для ячейки F14 следующую формулу:
=INDEX(J2:M18;MATCH(A14;K2:K18;0);MATCH(F1;J1:M1;0))
або =ИНДЕКС(J2:M18;ПОИСКПОЗ(A14;K2:K18;0);ПОИСКПОЗ(F1;J1:M1;0))
Таким образом, растягивая нашу формулу на весь диапазон, мы правильно присоединим дополнительные поля. Также нужно обратить внимание, что при растягивании формулы, нам нужно зафиксировать диапазоны знаком доллара ($) (для фиксирования можно также использовать клавишу F4 ), для поиска по шапке фиксируем только строку ( F$1 ), для поиска по номерам сделок - только колонку ($A14 ).
Слегка модифицируем предыдущий пример, и допустим, что у нас есть аналогичные таблицы, однако их заголовки являются объединенными, поэтому функция MATCH нам не поможет определить правильно порядковый номер колонки.
В данном случае, мы можем создать техническое поле над таблицей, и проставить номера колонок вручную. Тогда, мы сможем воспользоваться уже привычной нам функцией VLOOKUP.
Итак, формула для нашего второго варианта будет следующей:
=VLOOKUP($A3;$J$3:$M$19;E$1;0)
=ВПР($A3;$J$3:$M$19;E$1;0)
Опять же, не забываем фиксировать диапазон и соответствующие поисковые поля знаком $, для того чтобы формула корректно работала и не выдавала ошибку при смещении диапазонов.