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