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

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

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

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

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

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

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

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

Номер заказа

Дата продажи

Код продукта

20050

02.02.2014

C6077B

20050

02.02.2014

C9250LB

20051

02.02.2014

M115A

20052

03.02.2014

A760G

20052

03.02.2014

E3331

20052

03.02.2014

SP1447

20053

03.02.2014

L88M

20054

04.02.2014

S1018MM

20055

05.02.2014

C6077B

20056

06.02.2014

E3331

20056

06.02.2014

D534X

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

Номер заказа

Код продажи

Регион

20050

447

Запад

20051

398

Юг

20052

1006

Север

20053

447

Запад

20054

885

Восток

20055

398

Юг

20056

644

Восток

20057

1270

Восток

20058

885

Восток

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

  1. Скопируйте заголовки "Код продажи" и "Регион" в таблице "Оранжевая" (только эти две ячейки).

  2. Вставьте их в ячейку справа от заголовка "Код продукта" в таблице "Синяя".

Теперь таблица "Синяя" содержит пять столбцов, включая новые — "Код продажи" и "Регион".

  1. В таблице "Синяя", в первой ячейке столбца "Код продажи" начните вводить такую формулу:

    =ВПР(

  2. В таблице "Синяя" выберите первую ячейку столбца "Номер заказа" — 20050.

Частично введенная формула выглядит так:

Частично введенная формула ВПР

Выражение [@[Номер заказа]] означает, что нужно взять значение в этой же строке из столбца "Номер заказа".

  1. Введите точку с запятой и выделите всю таблицу "Оранжевая" с помощью мыши. В формулу будет добавлен аргумент Оранжевая[#Все].

  2. Введите точку с запятой, число 2, еще раз точку с запятой, а потом 0, вот так: ;2;0

  3. Нажмите клавишу ВВОД, и законченная формула примет такой вид:

Законченная формула ВПР

Выражение Оранжевая[#Все] означает, что нужно просматривать все ячейки в таблице "Оранжевая". Число 2 означает, что нужно взять значение из второго столбца, а 0 — что возвращать значение следует только в случае точного совпадения.

Обратите внимание: Excel заполняет ячейки вниз по этому столбцу, используя формулу ВПР.

  1. Вернитесь к шагу 3, но в этот раз начните вводить такую же формулу в первой ячейке столбца "Регион".

  2. На шаге 6 вместо 2 введите число 3, и законченная формула примет такой вид:

Законченная формула ВПР

Между этими двумя формулами есть только одно различие: первая получает значения из столбца 2 таблицы "Оранжевая", а вторая — из столбца 3.

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

  1. Выделите все ячейки значений в столбце "Код продажи" и нажмите клавиши CTRL+C, чтобы скопировать их.

  2. На вкладке Главная щелкните стрелку под кнопкой Вставить.

Стрелка под кнопкой "Вставить", открывающая коллекцию параметров вставки

  1. В коллекции параметров вставки нажмите кнопку Значения.

Кнопка "Значения" в коллекции параметров вставки

  1. Выделите все ячейки значений в столбце "Регион", скопируйте их и повторите шаги 10 и 11.

Теперь формулы ВПР в двух столбцах заменены значениями.

Дополнительные сведения о таблицах и функции ВПР

Как добавить или удалить строку или столбец в таблице

Использование структурированных ссылок в формулах таблиц Excel

Использование функции ВПР (учебный курс)

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×