Как соединить 2 таблицы в excel в одну впр

Объединение двух или нескольких таблиц

Вы можете объединить строки из одной таблицы в другую, просто вклеив данные в первые пустые ячейки под целевой таблицей. Таблица увеличится в размере, чтобы включить в нее новые строки. Если строки в обеих таблицах совпадают, вы можете объединить столбцы одной таблицы с другой, вклеив их в первые пустые ячейки справа от таблицы. В этом случае таблица также увеличится вместить новые столбцы.

Слияние строк на самом деле очень просто, но слияние столбцов может быть непросто, если строки одной таблицы не соответствуют строкам в другой таблице. Некоторых проблем с выравниванием можно избежать, если воспользоваться функцией ВПР.

Объединение двух таблиц с помощью функции ВЛОП

В приведенного ниже примере вы увидите две таблицы с другими именами: "Синяя" и "Оранжевая". В таблице "Синяя" каждая строка представляет собой позицию заказа. Например, заказ № 20050 содержит две позиции, № 20051 — одну, № 20052 — три и т. д. Мы хотим объединить столбцы "Код продажи" и "Регион" с таблицей "Синяя" с учетом соответствия значений в столбце "Номер заказа" таблицы "Оранжевая".

Объединение двух столбцов с другой таблицей

Значения "ИД заказа" повторяются в таблице "Синяя", но значения "ИД заказа" в таблице "Оранжевая" уникальны. Если просто скопировать и ввести данные из таблицы "Оранжевая", значения "ИД продаж" и "Регион" для второй строки заказа 20050 будут отключены на одну строку, что изменит значения в новых столбцах таблицы "Синяя".

Вот данные для таблицы "Синяя", которую можно скопировать на пустой лист. После в таблицы нажмите CTRL+T, чтобы преобразовать ее в таблицу, а затем переименуйте таблицу Excel синюю.

Быстрое объединение больших таблиц (VLOOKUP2D, ВПР2D)

Если вы знакомы с функцией VLOOKUP (ВПР) или ее горизонтальным аналогом HLOOKUP (ГПР) , то должны помнить, что эти замечательные функции ищут информацию только по одному параметру, то есть в одномерном массиве — по строке или по столбцу. А если нам необходимо выбирать данные из двумерной таблицы по стечению сразу двух параметров — и по строке и по столбцу одновременно? Давайте рассмотрим несколько вариантов объединения таблиц.

Вариант 1. Соединение двух таблиц (используем функции INDEX и MATCH)

Предположим, что нам нужно соединить две таблицы, которые отражают кредитный портфель и портфель обеспечения:

Мы можем начать использовать функцию VLOOKUP , чтобы соединить каждый отдельный столбец, однако если у нас таблицы имеют чрезвычайно много строк и столбцов, то это занятие может превратиться в настоящую муку. Однако из этой ситуации есть достаточно простой выход, поскольку Excel имеет следующие две замечательные функции, как INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ) из категории Ссылки и массивы (Lookup and Reference) , что в паре работают как 2D VLOOKUP.

Хорошо, что нам необходимо сделать, чтобы быстро соединить две таблицы? Давайте для начала скопируем шапку второй таблицы (ту, что мы будем присоединять) и вставим ее рядом с шапкой первой таблице. По названию шапки функция MATCH (ПОИСКПОЗ) будет выдавать нам порядковый номер колонки, а по номеру сделки — необходим нам порядковый номер строки.

Фактически, мы хотим найти значение ячейки с пересечения определенной строки и столбца в таблице. Для наглядности, разобьем задачу на три этапа.

  • Во-первых, нам нужно определить номер строки, соответствующей выбранной кредитной сделке. Это поможет сделать функция MATCH (ПОИСКПОЗ) из категории Ссылки и массивы (Lookup and Reference ) . В частности, формула MATCH (A14;K2:K18;0) даст нам нужен результат (для соглашения "№ 5398" это будет порядкоий номер 13). Первый аргумент этой функции — искомое значение (соглашение "№ 5398" из ячейки А14 ), второй — диапазон ячеек, где мы ищем соглашение (столбец с номерами сделок в таблице — К2:К18 ), третий аргумент задает тип поиска ( 0 — точное совпадение наименования, приблизительный поиск запрещен).
  • Во-вторых, совершенно аналогичным образом мы должны определить порядковый номер столбца в таблице с нужным нам содержанием. Функция MATCH (F1;J1:M1;0) сделает это и выдаст, например, для Collateralname, выбранного пользователем в ячейке F1 значение 3.
  • И, наконец, в-третьих, нам нужна функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца — функция INDEX (ИНДЕКС) из той же категории Ссылки и массивы (Lookup and Reference) . Первый аргумент этой функции — диапазон ячеек (в нашем случае это вся таблица, т.е. J2:M18), второй — номер строки, третий — номер столбца (а их мы определим с помощью функций 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 ).

Вариант 2. Соединение двух таблиц (используем функцию VLOOKUP)

Слегка модифицируем предыдущий пример, и допустим, что у нас есть аналогичные таблицы, однако их заголовки являются объединенными, поэтому функция MATCH нам не поможет определить правильно порядковый номер колонки.

В данном случае, мы можем создать техническое поле над таблицей, и проставить номера колонок вручную. Тогда, мы сможем воспользоваться уже привычной нам функцией VLOOKUP.

Итак, формула для нашего второго варианта будет следующей:

=VLOOKUP( $A3 ; $J$3:$M$19 ; E$1 ;0)

Опять же, не забываем фиксировать диапазон и соответствующие поисковые поля знаком $, для того чтобы формула корректно работала и не выдавала ошибку при смещении диапазонов.

Ссылка на основную публикацию