Как объединить таблицы в excel: Как объединить две таблицы и более в одну?

Содержание

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

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше

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

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

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

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

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

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

Номер заказа	Дата продажи	Код продукта
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	Восток

Нам необходимо обеспечить правильное выравнивание значений «ИД продаж» и «Регион» для каждого заказа с каждым уникальным элементом строки заказа. Для этого впустим заголовки таблицы «ИД продажи» и «Регион» в ячейки справа от таблицы «Синяя», а затем с помощью формулЫ ВЗ ПРОСМОТР выберем правильные значения из столбцов «ИД продажи» и «Регион» таблицы «Оранжевая».

Вот как это сделать.

Скопируйте заголовки «ИД продажи» и «Регион» в таблице «Оранжевая» (только эти две ячейки).
В ячейку справа от заголовка «ИД товара» таблицы «Синяя».

Теперь таблица «Синяя» содержит пять столбцов, включая новые — «Код продажи» и «Регион».
В таблице «Синяя», в первой ячейке столбца «Код продажи» начните вводить такую формулу:

=ВПР(
org/ListItem»>
В таблице «Синяя» выберите первую ячейку столбца «Номер заказа» — 20050.

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

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

Введите точку с запятой и выделите всю таблицу «Оранжевая» с помощью мыши. В формулу будет добавлен аргумент Оранжевая[#Все].
Введите точку с запятой, число 2, еще раз точку с запятой, а потом 0, вот так: ;2;0
Нажмите клавишу ВВОД, и законченная формула примет такой вид:

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

Обратите внимание: Excel заполняет ячейки вниз по этому столбцу, используя формулу ВПР.
Вернитесь к шагу 3, но в этот раз начните вводить такую же формулу в первой ячейке столбца «Регион».
На шаге 6 вместо 2 введите число 3, и законченная формула примет такой вид:

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

Теперь все ячейки новых столбцов в таблице «Синяя» заполнены значениями. В них содержатся формулы ВПР, но отображаются значения. Возможно, вы захотите заменить формулы ВПР в этих ячейках фактическими значениями.
Выделите все ячейки значений в столбце «Код продажи» и нажмите клавиши CTRL+C, чтобы скопировать их.
На вкладке Главная щелкните стрелку под кнопкой Вставить.
В коллекции параметров вставки нажмите кнопку Значения.
org/ListItem»>
Выделите все ячейки значений в столбце «Регион», скопируйте их и повторите шаги 10 и 11.

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Как объединить две таблицы Excel по частичному совпадению ячеек

Автор Антон Андронов На чтение 8 мин Опубликовано 25.01.2020

Из этой статьи Вы узнаете, как быстро объединить данные из двух таблиц Excel, когда в ключевых столбцах нет точных совпадений. Например, когда уникальный идентификатор из первой таблицы представляет собой первые пять символов идентификатора из второй таблицы. Все предлагаемые в этой статье решения протестированы мной в Excel 2013, 2010 и 2007.

Итак, есть два листа Excel, которые нужно объединить для дальнейшего анализа данных. Предположим, в одной таблице содержатся цены (столбец Price) и описания товаров (столбец Beer), которые Вы продаёте, а во второй отражены данные о наличии товаров на складе (столбец In stock). Если Вы или Ваши коллеги составляли обе таблицы по каталогу, то в обеих должен присутствовать как минимум один ключевой столбец с уникальными идентификаторами товаров. Описание товара или цена могут изменяться, но уникальный идентификатор всегда остаётся неизменным.

Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, «12345» и «12345-новый_суффикс«. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.

И что совсем плохо – соответствия могут быть вовсе нечёткими, и «Некоторая компания» в одной таблице может превратиться в «ЗАО «Некоторая Компания»» в другой таблице, а «Новая Компания (бывшая Некоторая Компания)» и «Старая Компания» тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?

Выход есть всегда, читайте далее и Вы узнаете решение!

Замечание: Решения, описанные в этой статье, универсальны. Вы можете адаптировать их для дальнейшего использования с любыми стандартными формулами, такими как ВПР (VLOOKUP), ПОИСКПОЗ (MATCH), ГПР (HLOOKUP) и так далее.

Выберите подходящий пример, чтобы сразу перейти к нужному решению:

Ключевой столбец в одной из таблиц содержит дополнительные символы
Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
Данные в ключевых столбцах не совпадают (123-SDX и HFGT-23) или есть частичное совпадение, меняющееся от ячейки к ячейке (Coca Cola и Coca-Cola Inc.)

Содержание

Ключевой столбец в одной из таблиц содержит дополнительные символы
Другие формулы
Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице
Данные в ключевых столбцах не совпадают
1. Создаём вспомогательную таблицу для поиска.
2. Обновляем главную таблицу при помощи данных из таблицы для поиска.
3. Переносим данные из таблицы поиска в главную таблицу

Ключевой столбец в одной из таблиц содержит дополнительные символы

Рассмотрим две таблицы. Столбцы первой таблицы содержат номенклатурный номер (SKU), наименование пива (Beer) и его цену (Price). Во второй таблице записан SKU и количество бутылок на складе (In stock). Вместо пива может быть любой товар, а количество столбцов в реальной жизни может быть гораздо больше.

В таблице с дополнительными символами создаём вспомогательный столбец. Можно добавить его в конец таблицы, но лучше всего вставить его следующим справа после ключевого столбца, чтобы он был на виду.

Ключевым в таблице в нашем примере является столбец A с данными SKU, и нужно извлечь из него первые 5 символов. Добавим вспомогательный столбец и назовём его SKU helper:

Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:
Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):
Даём столбцу имя SKU helper.
Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:
=ЛЕВСИМВ(A2;5)
=LEFT(A2,5)
Здесь A2 – это адрес ячейки, из которой мы будем извлекать символы, а 5 – количество символов, которое будет извлечено.
Копируем эту формулу во все ячейки нового столбца.

Готово! Теперь у нас есть ключевые столбцы с точным совпадением значений – столбец SKU helper в основной таблице и столбец SKU в таблице, где будет выполняться поиск.

Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

Другие формулы

Извлечь первые Х символов справа: например, 6 символов справа из записи «DSFH-164900». Формула будет выглядеть так:
=ПРАВСИМВ(A2;6)
=RIGHT(A2,6)
Пропустить первые Х символов, извлечь следующие Y символов: например, нужно извлечь «0123» из записи «PREFIX_0123_SUFF». Здесь нам нужно пропустить первые 8 символов и извлечь следующие 4 символа. Формула будет выглядеть так:
=ПСТР(A2;8;4)
=MID(A2,8,4)
Извлечь все символы до разделителя, длина получившейся последовательности может быть разной. Например, нужно извлечь «123456» и «0123» из записей «123456-суффикс» и «0123-суффикс» соответственно. Формула будет выглядеть так:
=ЛЕВСИМВ(A2;НАЙТИ("-";A2)-1)
=LEFT(A2,FIND("-",A2)-1)

Одним словом, Вы можете использовать такие функции Excel, как ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID), НАЙТИ (FIND), чтобы извлекать любые части составного индекса. Если с этим возникли трудности – свяжитесь с нами, мы сделаем всё возможное, чтобы помочь Вам.

Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

Предположим, таблица, в которой производится поиск, содержит столбец с идентификаторами. В ячейках этого столбца содержатся записи вида XXXX-YYYY, где XXXX – это кодовое обозначение группы товаров (мобильные телефоны, телевизоры, видеокамеры, фотокамеры), а YYYY – это код товара внутри группы. Главная таблица состоит из двух столбцов: в одном содержатся коды товарных групп (Group), во втором записаны коды товаров (ID). Мы не можем просто отбросить коды групп товаров, так как один и тот же код товара может повторяться в разных группах.

Добавляем в главной таблице вспомогательный столбец и называем его Full ID (столбец C), подробнее о том, как это делается рассказано ранее в этой статье.

В ячейке C2 запишем такую формулу:

=СЦЕПИТЬ(A2;"-";B2)
=CONCATENATE(A2,"-",B2)

Здесь A2 – это адрес ячейки, содержащей код группы; символ «—» – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.

Теперь объединить данные из наших двух таблиц не составит труда. Мы будем сопоставлять столбец Full ID первой таблицы со столбцом ID второй таблицы. При обнаружении совпадения, записи из столбцов Description и Price второй таблицы будут добавлены в первую таблицу.

Данные в ключевых столбцах не совпадают

Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись «Case-Ip4S-01» соответствует записи «SPK-A1403» в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать «SPK-A1403» в «Case-Ip4S-01».

Плохая новость: Данные, содержащиеся в этих двух таблицах Excel, придётся обрабатывать вручную, чтобы в дальнейшем было возможно объединить их.

Хорошая новость: Это придётся сделать только один раз, и получившуюся вспомогательную таблицу можно будет сохранить для дальнейшего использования. Далее Вы сможете объединять эти таблицы автоматически и сэкономить таким образом массу времени 🙂

1. Создаём вспомогательную таблицу для поиска.

Создаём новый лист Excel и называем его SKU converter. Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз :-).

В результате мы имеем вот такую таблицу:

2. Обновляем главную таблицу при помощи данных из таблицы для поиска.

В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных – столбец Supp.SKU.

Столбец Supp.SKU заполняется оригинальными кодами производителя.

Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU, соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика. После этого повторяем шаг 2.

3. Переносим данные из таблицы поиска в главную таблицу

В нашей главной таблице есть ключевой столбец с точным совпадением с элементами таблицы поиска, так что теперь эта задача не вызовет сложностей 🙂

При помощи функции ВПР (VLOOKUP) объединяем данные листа Store с данными листа Wholesale Supplier 1, используя для поиска соответствий столбец Supp. SKU.

Вот пример обновлённых данных в столбце Wholesale Price:

Всё просто, не так ли? Задавайте свои вопросы в комментариях к статье, я постараюсь ответить, как можно скорее.

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: https://www.ablebits.com/office-addins-blog/2013/09/20/merge-worksheets-excel-partial-match/
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Объединение таблиц в Power Query

Перейти к содержанию

Вы здесь:

Вы работали со сводными таблицами Excel? Если нет – нужно срочно начинать. Это супер-инструмент, который позволяет быстро и просто обрабатывать большие объемы данных. А если вы с ними все-таки работали, то я как экстрасенс-капитан-очевидность могу точно сказать, что вы сталкивались с ситуацией, когда нужно построить сводную таблицу на основе нескольких источников данных. Например, с помощью нескольких одинаковых таблиц Excel, копируя их одну под другой. Или дополняя свою таблицу новыми столбцами и аналитиками.

Чаще всего, чтобы объединить данные в Excel, пользователи копируют таблицы одну под другую. Или пишут формулы по типу ВПР, если в таблицу нужно добавить новые столбцы или аналитики. Но вы наверняка знаете, что самый удобный инструмент для объединения данных в Excel – это Power Query. Там есть два принципиальных способа:

По вертикали – добавление таблицы под таблицу. Полезно, когда таблицы с одинаковой шапкой находятся в разных файлах или на разных листах.
По горизонтали – слияние таблиц, похожее на ВПР. А здесь есть еще варианты — не только аналогичные ВПР, но и ВПР-наоборот, и ВПР-неВПР… Целых шесть видов объединения таблиц.

Разберем, чем эти способы отличаются друг от друга.

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

Как на основе таблиц с одинаковой шапкой построить единую базу? Добавив таблицы одну под другую

Раньше процедуру добавления можно было выполнить только с помощью копирования. Отсюда – много ручной работы при внесении новых данных (ну, или писали макросы). Однако с момента, как в Excel появился Power Query, у нас появилась возможность добавлять таблицы одну под другую несколькими щелчками мыши:

Подробности про объединение данных с помощью Добавления смотрите в видео.

Объединение таблиц применяется, когда у вас есть две таблицы (шапки у них скорее всего разные), и одна таблица дополняет другую. Например, в отчете продажи показаны по городам, а у вас просят вывести информацию по областям. Тогда потребуется уже объединение по горизонтали:

Одна таблица дополняет другую с помощью объединения по общему столбцу

В «обычном» Excel это делается с помощью формулы ВПР или других формул с похожим функционалом. Power Query предлагает нам больше способов объединения, чем ВПР. Используя этот инструмент, мы можем не «просто» связать таблицы, но и сделать это по определенным условиям.

Подробности про Объединение данных смотрите в следующем видео.

Далее в статье подробно разбираются типы объединения данных. Чтобы вам было удобнее читать, приложены файлы с примерами.

Power Query предлагает на выбор шесть различных способов объединения таблиц:

Внешнее соединение слева (все из первой таблицы, совпадающие из второй)
Внешнее соединение справа (все из второй таблицы, совпадающие из первой)
Полное внешнее (все строки из обеих таблиц)
Внутреннее (только совпадающие строки)
Анти-соединение слева (только строки в первой таблице)
Анти-соединение справа (только строки во второй таблице)

Если посмотреть в интернете статьи про объединение данных, вы наверняка встретите поясняющие рисунки в виде кругов, где круги – это схематичное изображение множеств или таблиц, которые мы хотим объединить. Для Power Query иллюстрации по объединению данных выглядят точно так же:

Внешнее соединение слева

Внешнее соединение справа

Полное внешнее

Внутреннее соединение

Анти-соединение слева

Анти-соединение справа

Разберемся, что все это значит и как работают разные способы объединения.

Внешнее соединение слева – самый часто встречающийся способ объединения таблиц. Это то, что мы делаем с помощью формулы ВПР в Excel – из одной таблицы «перетаскиваем» данные в другую.

Когда вы выбираете «Внешнее соединение слева», к данным из первой таблицы добавляются все значения из второй таблицы, соответствующие столбцу поиска. Если во второй таблице нет искомых значений, вы получите null (null – это значит пусто).

Пример: объединим план продаж в штуках с плановыми ценами.

Скачать пример

Добавим таблицы в Power Query: вкладка Данные → Получить данные → Из других источников → Из таблицы / диапазона, или для новых версий Excel: вкладка Данные → Из таблицы / диапазона. Объединяем запросы: вкладка Главная → Объединить запросы (Объединить запросы в новый), выбираем столбец слияния «снаряд» и тип объединения «Внешнее соединение слева».

На некоторые товары плановые цены еще не установлены, поэтому после объединения в столбце «цена» для этих товаров будет проставлен null.

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

Обратите внимание, в нижней части окна появилось информационное сообщение: «Выбор согласовал 6 из первых строк (9)». Соответственно, в таблице с результатами цены заполнены только в шести строках, а в 3-х стоит null.

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

Что происходит, когда вы выбираете «Внешнее соединение справа»: из первой таблицы исчезнут все не найденные во второй таблице данные. Из второй таблицы будут добавлены те значения, которых нет в первой. Если посмотреть на рисунки, то видно, что Внешнее соединение справа работает так же, как и Внешнее соединение слева – отличается только порядок расположения таблиц.

Пример: в первой таблице — ожидаемые среднемесячные продажи на одного покупателя, во второй – количество потенциальных покупателей.

Скачать пример

Объединим таблицы, чтобы запланировать будущие продажи. Общий столбец для поиска — «город».

Смотрим, что получилось в результате объединения: из таблицы «исчезли» данные по городам, в которых нет потенциальных покупателей – Алматы и Санкт-Петербург. Зато появился город, в котором есть возможные покупатели – Астана. По этому городу потребуется проставить сумму ожидаемых средних продаж, чтобы выполнить дальнейшие расчеты.

Этот тип соединения создает таблицу, в которой есть все строки и первой, и второй таблицы.

Что происходит, когда вы выбираете «Полное внешнее соединение»: с помощью заданного общего столбца будут объединены все строки из первой и второй таблиц.

Пример: в первой таблице приведен объем производства по дням, во второй – объем брака.

Скачать пример

Для создания общей таблицы используется Полное внешнее соединение. Для объединения используем общий столбец «дата». В результате получится отчет, который с помощью некоторых доработок приводим к нужному виду:

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

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

Что происходит, когда вы выбираете «Внутреннее соединение»: таблицы будут объединены с помощью заданного общего столбца. При этом из первой таблицы исчезнут все не найденные в «общем» столбце второй таблицы строки. Из второй таблицы – исчезнут не найденные в первой. В математике эта операция называется «пересечением множеств».

Пример: есть два списка покупателей, принявших участие в акциях А и Б – по одному для каждой из акций. С помощью Внутреннего соединения получим список покупателей, участвовавших и в той, и в другой акции.

Скачать пример

Поиск выполняем по общему столбцу «Покупатель». Результат — список покупателей, участвовавших в обеих акциях:

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

Пример: в первой таблице – записи о сделанных покупателям коммерческих предложениях, во второй – данные о продажах. С помощью Анти-соединения слева найдем покупателей, которые получили предложение, но не сделали покупку.

Скачать пример

С помощью запроса исключим из первой таблицы найденных во второй покупателей:

В результате получили список покупателей, которые не сделали покупки.

Анти-соединение справа работает так же, как и Анти-соединение слева. Разница здесь только в порядке расположения таблиц.

Пример: в первой таблице – отчет о выполненных задачах, во второй – список поставленных задач. Объединив таблицы с помощью Анти-соединения справа, найдем невыполненные задачи.

Скачать пример

В результате объединения получим список невыполненных задач:

Кроме объединения таблиц с помощью запросов Power Query, такие операции можно выполнять и с помощью DAX-формул, применяя их в различных сочетаниях: GENERATEALL, NATURALLEFTOUTERJOIN, NATURALINNERJOIN, CROSSJOIN, FILTER+CROSSJOIN, GENERATE, EXCEPT и др.

Теги: ExcelPower BI

Автор: Станислав Салостей

Вверх

Объединение таблиц в Excel с помощью Power Query (простое пошаговое руководство)

Содержание

Слияние таблиц с помощью Power Query

С Power Query стало проще работать с данными, распределенными по листам или даже книгам.

Одна из вещей, где Power Query может сэкономить вам много времени, — это когда вам нужно объединить таблицы с разными размерами и столбцами на основе соответствующего столбца.

Ниже приведено видео, в котором я показываю, как именно объединить таблицы в Excel с помощью Power Query.

Если вы предпочитаете читать текст, а не смотреть видео, ниже приведены письменные инструкции.

Предположим, у вас есть таблица, как показано ниже:

В этой таблице есть данные, которые я хочу использовать, но в ней все еще отсутствуют два важных столбца — «Идентификатор продукта» и «Регион», в котором работает торговый представитель.

Эта информация представлена в виде отдельных таблиц, как показано ниже:

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

И под слиянием я не имею в виду простую копипасту.

Вам нужно будет сопоставить соответствующие записи из таблицы 1 с данными из таблиц 2 и 3.

Теперь вы можете положиться на ВПР или ИНДЕКС / ПОИСКПОЗ.

Или, если вы специалист по VBA, вы можете написать для этого код.

Но эти варианты трудоемки и сложны по сравнению с Power Query.

В этом уроке я покажу вам, как объединить эти три таблицы Excel в одну.

Чтобы эта техника работала, вам понадобятся соединительные столбики. Например, в таблицах 1 и 2 общим столбцом является «Товар», а в таблицах 1 и 3 общим столбцом является «Торговый представитель». Также обратите внимание, что в этих соединительных столбцах не должно быть повторений.

Примечание. Power Query можно использовать в качестве надстройки в Excel 2010 и 2013, и это встроенная функция, начиная с Excel 2016. В зависимости от вашей версии некоторые изображения могут выглядеть по-другому (в этом руководстве используются снимки из Excel 2016).

Я назвал эти таблицы, как показано ниже:

Таблица 1 — Sales_Data
Таблица 2 — Pdt_Id
Таблица 3 — Область

Переименовывать эти таблицы не обязательно, но лучше давать имена, описывающие, о чем таблица.

За один раз вы можете объединить только две таблицы в Power Query.

Итак, нам сначала нужно объединить Таблицу 1 и Таблицу 2, а затем на следующем шаге объединить с ней Таблицу 3.

Объединение таблицы 1 и таблицы 2

Чтобы объединить таблицы, вам сначала нужно преобразовать эти таблицы в соединения в Power Query. Как только у вас будут связи, вы можете легко их объединить.

Вот шаги, чтобы сохранить таблицу Excel как соединение в Power Query:

Выберите любую ячейку в таблице Sales_Data.
Щелкните вкладку Данные.
В группе «Получить и преобразовать» нажмите «Из таблицы / диапазона». Откроется редактор запросов.
В редакторе запросов перейдите на вкладку «Файл».
Нажмите на опцию «Закрыть и загрузить в».
В диалоговом окне «Импорт данных» выберите «Только создать соединение».
Щелкните ОК.

Вышеупомянутые шаги создадут соединение с именем Sales_Data (или любым именем, которое вы дали таблице Excel).

Повторите вышеуказанные шаги для таблиц 2 и 3.

Итак, когда вы закончите, у вас будет три подключения (с именами Sales_Data, Pdt_Id и Region).

Теперь давайте посмотрим, как объединить таблицы Sales_Data и Pdt_Id.

Щелкните вкладку «Данные».
В группе «Получить и преобразовать данные» нажмите «Получить данные».
В раскрывающемся списке нажмите «Объединить запросы».
Щелкните «Объединить». Это откроет диалоговое окно слияния.
В диалоговом окне «Объединение» выберите «Sales_Data» в первом раскрывающемся списке.
Во втором раскрывающемся списке выберите «Pdt_Id».
В предварительном просмотре «Sales_Data» щелкните столбец «Товар». При этом будет выбран весь столбец.
В предварительном просмотре «Pdt_Id» щелкните столбец «Item». При этом будет выбран весь столбец.
В раскрывающемся списке «Присоединиться к типу» выберите «Левый внешний (все с первого, соответствие со второго)».
Щелкните ОК.

Вышеупомянутые шаги откроют редактор запросов и покажут вам данные из Sales_Data с одним дополнительным столбцом (Pdt_Id).

Объединение таблиц Excel (таблицы 1 и 2)

Теперь процесс объединения таблиц будет происходить в редакторе запросов со следующими шагами:

В дополнительном столбце (Pdt_Id) щелкните двунаправленную стрелку в заголовке.
В открывшемся окне параметров снимите флажки со всех имен столбцов и выберите только элемент. Это потому, что у нас уже есть столбец с названием продукта в существующей таблице, и нам нужен только идентификатор продукта для каждого продукта.
Снимите флажок «Использовать исходное имя столбца в качестве префикса».
Щелкните ОК.

Это даст вам результирующую таблицу, в которой есть все записи из таблицы Sales_Data и дополнительный столбец, который также имеет идентификаторы продуктов (из таблицы Pdt_Id).

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

Но у нас есть три таблицы, которые нужно объединить, так что работы еще предстоит.

Вам необходимо сохранить эту результирующую таблицу как соединение (чтобы мы могли использовать ее для объединения с таблицей 3).

Вот шаги, чтобы сохранить эту объединенную таблицу (с данными из таблиц Sales_Data и Pdt_Id) в качестве соединения:

Перейдите на вкладку Файл.
Нажмите на опцию «Закрыть и загрузить в».
В диалоговом окне «Импорт данных» выберите «Только создать соединение».
Щелкните ОК.

Это сохранит недавно объединенные данные как соединение. Вы можете переименовать это соединение, если хотите.

Объединение таблицы 3 с итоговой таблицей

Процесс слияния третьей таблицы с результирующей таблицей (которую мы получили путем слияния таблиц 1 и 2) точно такой же.

Вот шаги, чтобы объединить эти таблицы:

Щелкните вкладку «Данные».
В группе «Получить и преобразовать данные» нажмите «Получить данные».
В раскрывающемся списке нажмите «Объединить запросы».
Щелкните «Объединить». Это откроет диалоговое окно слияния.
В диалоговом окне «Объединение» выберите «Объединить1» в первом раскрывающемся списке.
Во втором раскрывающемся списке выберите «Регион».
В предварительном просмотре «Merge1» щелкните столбец «Торговый представитель». При этом будет выбран весь столбец.
В предварительном просмотре региона щелкните столбец «Торговый представитель». При этом будет выбран весь столбец.
В раскрывающемся списке «Присоединиться к типу» выберите «Левый внешний» (все начиная с первого, соответствующие — со второго).
Щелкните ОК.

Вышеупомянутые шаги откроют редактор запросов и покажут вам данные из Merge1 с одним дополнительным столбцом (регион).

Теперь процесс объединения таблиц будет происходить в редакторе запросов со следующими шагами:

В дополнительном столбце («Регион») нажмите на двойную стрелку в заголовке.
В открывшемся окне параметров снимите флажки со всех имен столбцов и выберите только «Регион».
Снимите флажок «Использовать исходное имя столбца в качестве префикса».
Щелкните ОК.

Вышеупомянутые шаги предоставят вам таблицу, в которой объединены все три таблицы (таблица Sales_Data с одним столбцом для Pdt_Id и одним для региона).

Вот шаги, чтобы загрузить эту таблицу в Excel:

Щелкните вкладку Файл.
Нажмите «Закрыть и загрузить в».
В диалоговом окне «Импорт данных» выберите параметры «Таблица» и «Новые листы».
Щелкните ОК.

Это даст вам объединенную таблицу на новом листе.

Одна из лучших особенностей Power Query заключается в том, что вы можете легко учесть любые изменения в базовых данных (таблицы 1, 2 и 3), просто обновив их.

Например, предположим, что Лауру переводят в Азию, и вы получаете новые данные за следующий месяц. Теперь вам не нужно повторять описанные выше шаги снова. Все, что вам нужно сделать, это обновить таблицу, и она сделает все заново за вас.

Через несколько секунд у вас будет новая объединенная таблица.

Вам также могут понравиться следующие руководства по Power Query:

Объедините данные из нескольких книг в Excel (с помощью Power Query).
Объедините данные из нескольких листов в один лист в Excel.
Как отключить данные в Excel с помощью Power Query (также известного как Get & Transform)
Получение списка имен файлов из папок и подпапок (с помощью Power Query)

Как объединить таблицы в excel

Консолидация (объединение) данных из нескольких таблиц в одну

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

Необходимо объединить их все в одну общую таблицу, просуммировав совпадающие значения по кварталам и наименованиям.

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

которая просуммирует содержимое ячеек B2 с каждого из указанных листов, и затем скопировать ее на остальные ячейки вниз и вправо.

Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ(‘2001 год:2003 год’!B3)

Фактически — это суммирование всех ячеек B3 на листах с 2001 по 2003, т.е. количество листов, по сути, может быть любым. Также в будущем возможно поместить между стартовым и финальным листами дополнительные листы с данными, которые также станут автоматически учитываться при суммировании.

Способ 2. Если таблицы неодинаковые или в разных файлах

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

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Хорошо заметно, что таблицы не одинаковы — у них различные размеры и смысловая начинка. Тем не менее их можно собрать в единый отчет меньше, чем за минуту. Единственным условием успешного объединения (консолидации) таблиц в подобном случае является совпадение заголовков столбцов и строк. Именно по первой строке и левому столбцу каждой таблицы Excel будет искать совпадения и суммировать наши данные.

Для того, чтобы выполнить такую консолидацию:

Заранее откройте исходные файлы
Создайте новую пустую книгу (Ctrl + N)
Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация(Data — Consolidate) . Откроется соответствующее окно:

Обратите внимание, что в данном случае Excel запоминает, фактически, положение файла на диске, прописывая для каждого из них полный путь (диск-папка-файл-лист-адреса ячеек). Чтобы суммирование происходило с учетом заголовков столбцов и строк необходимо включить оба флажка Использовать в качестве имен (Use labels) . Флаг Создавать связи с исходными данными (Create links to source data) позволит в будущем (при изменении данных в исходных файлах) производить пересчет консолидированного отчета автоматически.

После нажатия на ОК видим результат нашей работы:

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

Как объединить две таблицы Excel по частичному совпадению ячеек

Трудности начинаются, когда Вы получаете некоторые таблицы от производителя или из других отделов компании. Дело может ещё усложниться, если вдруг вводится новый формат уникальных идентификаторов или самую малость изменятся складские номенклатурные обозначения (SKU). И перед Вами стоит задача объединить в Excel новую и старую таблицы с данными. Так или иначе, возникает ситуация, когда в ключевых столбцах имеет место только частичное совпадение записей, например, “12345” и “12345-новый_суффикс“. Вам-то понятно, что это тот же SKU, но компьютер не так догадлив! Это не точное совпадение делает невозможным использование обычных формул Excel для объединения данных из двух таблиц.

И что совсем плохо – соответствия могут быть вовсе нечёткими, и “Некоторая компания” в одной таблице может превратиться в “ЗАО «Некоторая Компания»” в другой таблице, а “Новая Компания (бывшая Некоторая Компания)” и “Старая Компания” тоже окажутся записью об одной и той же фирме. Это известно Вам, но как это объяснить Excel?

Выход есть всегда, читайте далее и Вы узнаете решение!

Выберите подходящий пример, чтобы сразу перейти к нужному решению:

Ключевой столбец в одной из таблиц содержит дополнительные символы

Наводим указатель мыши на заголовок столбца B, при этом он должен принять вид стрелки, направленной вниз:
Кликаем по заголовку правой кнопкой мыши и в контекстном меню выбираем Вставить (Insert):
Даём столбцу имя SKU helper.
Чтобы извлечь первые 5 символов из столбца SKU, в ячейку B2 вводим такую формулу:

Здесь A2 – это адрес ячейки, из которой мы будем извлекать символы, а 5 – количество символов, которое будет извлечено.

Теперь при помощи функции ВПР (VLOOKUP) мы получим нужный результат:

Другие формулы

Извлечь первые Х символов справа: например, 6 символов справа из записи “DSFH-164900”. Формула будет выглядеть так:

Данные из ключевого столбца в первой таблице разбиты на два или более столбца во второй таблице

В ячейке C2 запишем такую формулу:

Здесь A2 – это адрес ячейки, содержащей код группы; символ “–” – это разделитель; B2 – это адрес ячейки, содержащей код товара. Скопируем формулу в остальные строки.

Данные в ключевых столбцах не совпадают

Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

1. Создаём вспомогательную таблицу для поиска.

В результате мы имеем вот такую таблицу:

2. Обновляем главную таблицу при помощи данных из таблицы для поиска.

В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

Столбец Supp.SKU заполняется оригинальными кодами производителя.

3. Переносим данные из таблицы поиска в главную таблицу

В нашей главной таблице есть ключевой столбец с точным совпадением с элементами таблицы поиска, так что теперь эта задача не вызовет сложностей

Вот пример обновлённых данных в столбце Wholesale Price:

Как объединить таблицы в excel

Как объединить две таблицы, сопоставив столбец в Excel?

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

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

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

Чтобы объединить две таблицы по сопоставлению столбцов, вы можете применить функцию ВПР.

1. Выберите пустую ячейку рядом с основной таблицей, например C2. Смотрите скриншот:

2. Введите эту формулу =IFERROR(VLOOKUP($A2,Sheet2!$A$1:$C$6,COLUMN(A1),FALSE),»») в нее, затем перетащите маркер автозаполнения вправо, пока не появится пустая ячейка, а затем перетащите маркер заполнения вниз к ячейкам, в которых требуется эта формула. Смотрите скриншот:

3. Вы можете добавить новые заголовки столбцов в объединенную таблицу. Смотрите скриншот:

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

Функция ПРОСМОТР не доставит вам хлопот, но, к счастью, есть полезная функция. Слияние таблиц in Kutools for Excel который может быстро добавлять и обновлять новые элементы в основной таблице на основе другой таблицы.

1. Выберите основную таблицу, которую вы хотите использовать для обновления и объединения с другой, щелкните Kutools Plus > Слияние таблиц.

2. Затем в Слияние таблиц в диалоговом окне мастера выберите таблицу поиска в Выберите таблицу поиска текстовое поле, проверьте параметры, если у таблиц есть заголовки.

3. Нажмите Nextна шаге 2 мастера отметьте ключевой столбец, на основе которого вы хотите объединить.

4. Нажмите Nextна шаге 3 мастера отметьте столбцы, в которых вы хотите обновить данные.

5. Нажмите Next к следующему шагу отметьте новые столбцы, которые вы хотите добавить к основным данным.

6. Нажмите Next, на последнем шаге вам нужно установить некоторые параметры для новых элементов, например, выделить обновленные с помощью цвета фона.

7. Нажмите Завершить. Основная таблица обновлена и дополнена новыми элементами.

Демо

Объедините несколько таблиц по заголовкам столбцов в один лист с помощью Kutools for Excel

Если вы хотите объединить несколько таблиц на листах или книг по заголовкам столбцов в один лист, как показано на скриншоте ниже, вы можете применить мощный Сочетать Функция Kutools for Excel чтобы быстро ее решить.

После установки Kutools for Excel, пожалуйста, сделайте, как показано ниже 🙁 Скачать бесплатно Kutools for Excel сейчас!)

1. Включите Excel и щелкните Kutools Plus > Сочетать, и в появившемся диалоговом окне отметьте Объедините несколько листов из книг в один лист. Смотрите скриншот:

2. Затем нажмите Next идти на Шаг 2 из 3 диалоговое окно и добавьте книги, нажав Добавить кнопку, затем выберите имена листов, из которых вы хотите объединить Список рабочих листов раздел. Смотрите скриншот:

3. Нажмите Next идти на Шаг 3 из 3 диалог. На последнем шаге диалога укажите в нем номер строки заголовка, вот он, также вы можете указать другие параметры настройки по своему усмотрению. Смотрите скриншот:

4. Нажмите Завершить, появится диалоговое окно для выбора папки для размещения объединенной новой книги. И нажмите скидка перейти к первому Kutools for Excel диалоговое окно, которое напоминает вам, открывать ли объединенную книгу, нажмите Да открывать или нет, а потом второй Kutools for Excel Появится диалоговое окно с напоминанием о необходимости сохранения этого сценария объединения, щелкните любые параметры по своему усмотрению.

Теперь листы были объединены в один лист на основе заголовков столбцов.

Объединить таблицы в excel Excelka.ru

13.12.2019

Объединить таблицы в excel

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

Инструкция

Устанавливаем себе надстройку ЁXCEL . Читаем справку.

Приступаем к решению. Переходим во вкладку ЁXCEL главного меню, нажимаем кнопку «Таблицы», в выпавшем списке выбираем команду «Объединить таблицы»:

В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем «ОК»:

Программа сформирует запрос — объединит таблицы и выведет информационное сообщение:

Переходим на итоговый лист (тот где хотим вывести общую таблицу), устанавливаем курсор в ячейку «A1«. Переходим в главном меню во вкладку «Данные» в разделе «Получение внешних данных» нажимаем кнопку «Существующие подключения»:

В открывшемся диалоговом окне выбираем «Подключения в этой книге» — «Запрос из Excel Files» и нажимаем «Открыть»:

В открывшемся диалоговом окне устанавливаем переключатели в положения «Таблица» и «Имеющийся лист», нажимаем «ОК»:

В активном листе будет создана таблица, которая будет объединять таблицы, расположенные на указанных нами листах:

Теперь все таблицы связаны. Добавьте новые строчки в любую из объединенных таблиц. Перейдите во вкладку «Данные» и нажмите кнопку «Обновить все»:

В итоговой таблице появятся строчки, добавленные в выбранный вами лист.

Чтобы обновление итоговой таблицы происходило автоматически вставьте в модуль каждого листа (кроме Итогового) следующий код (Как вставлять макросы?):

Видео-пример

Важно:

Количество столбцов во всех таблицах должно быть одинаково;
Кроме таблиц на листах не должно быть никакой информации;
Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте — необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).

Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль «ЭтаКнига» следующий код:

Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.

Чтобы оценить всю прелесть — выньте файл из архива и при загрузке файла включите макросы.

Возможные ошибки при использовании этого метода:

В таблицах одинаковое количество столбцов, но при попытке использовать запрос получаем сообщение: «В таблицах или запросах, выбранных в запросе на объединение, не совпадает число столбцов.» Причина: в одном или нескольких листах с таблицами есть пустые столбцы (в них раньше были данные) и MS Excel считает их столбцами таблиц. Решение: Выделить и удалить все пустые столбцы на листах с таблицами.
Все столбцы удалили, но ошибка осталась. Причина: файл сохранен на сетевом диске или открыт из почтовой программы. Решение: сохраните файл у себя на локальном компьютере, выполните запрос, вставьте в него макрос меняющий путь в запросе и выложите обратно в сеть (если нужно).
В полученном запросе в некоторых ячейках пропадают данные. Причина: в ваших таблицах встречаются столбцы, которые одновременно содержатся и числовые и текстовые значения. MS Excel считает, что эти столбцы должны содержать только числа и не выводит текст. Решение:преобразуйте все числовые значения в этих столбцах во всех таблицах в текстовые. Обновите запрос — текстовые данные появятся.

Еще, вопрос. Хочу сливать сразу много однотипных таблиц. Но не все данные а только первые 15 столбцов.
Начиная с 16-й колонки у меня расчетные данные, которые не нужно сливать.

Консолидация (объединение) данных из нескольких таблиц в одну

Способ 1. С помощью формул

Имеем несколько однотипных таблиц на разных листах одной книги. Например, вот такие:

Самый простой способ решения задачи «в лоб» — ввести в ячейку чистого листа формулу вида

=’2001 год’!B3+’2002 год’!B3+’2003 год’!B3

Если листов очень много, то проще будет разложить их все подряд и использовать немного другую формулу:

=СУММ(‘2001 год:2003 год’!B3)

Способ 2. Если таблицы неодинаковые или в разных файлах

Рассмотрим следующий пример. Имеем три разных файла (Иван.xlsx, Рита.xlsx и Федор.xlsx) с тремя таблицами:

Для того, чтобы выполнить такую консолидацию:

Заранее откройте исходные файлы
Создайте новую пустую книгу (Ctrl + N)
Установите в нее активную ячейку и выберите на вкладке (в меню) Данные — Консолидация(Data — Consolidate) . Откроется соответствующее окно:

Установите курсор в строку Ссылка(Reference) и, переключившись в файл Иван.xlsx, выделите таблицу с данными (вместе с шапкой). Затем нажмите кнопку Добавить(Add) в окне консолидации, чтобы добавить выделенный диапазон в список объединяемых диапазонов.

Повторите эти же действия для файлов Риты и Федора. В итоге в списке должны оказаться все три диапазона:

После нажатия на ОК видим результат нашей работы:

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

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

В приведенном ниже примере вы увидите две таблицы с именами, которые ранее использовались для новых имен: «синий» и «оранжевый». В таблице синей каждая строка является элементом строки для заказа. Таким образом, номер заказа 20050 состоит из двух элементов, код заказа 20051 — один товар, код заказа 20052 состоит из трех элементов и т. д. Мы хотим объединить столбцы «код продажи» и «регион» с таблицей «Синяя», исходя из совпадающих значений в столбцах «код заказа» в таблице «оранжевый».

Значения идентификатора заказа повторяются в таблице Blue, но значения ИДЕНТИФИКАТОРов Order в таблице оранжевый являются уникальными. Если бы пришлось просто копировать и вставлять данные из оранжевой таблицы, то значения ИДЕНТИФИКАТОРов продаж и областей для второй позиции строки 20050 будут отключены на одну строку, что приведет к изменению значений в новых столбцах в таблице Blue.

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

Как объединить таблицы в excel

Работа в Excel – это, вне сомнения, бесценный опыт, который пригодится каждому пользователю. Разумеется, я не утверждаю, что владеть этим инструментом в совершенстве должен каждый юзер, но все же чем большими знаниями вы обладаете, тем лучше. В предыдущих своих статьях я уже неоднократно рассказывал о возможностях Экселя, о тонкостях построения диаграмм, графиков и таблиц. Кроме того, не единожды речь заходила и о применении тех или иных функций на практике.

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

Если таблицы одинаковые

Как вы понимаете, консолидация таблиц здорово упрощает работу. Она позволяет создать сводную таблицу, объединив данные всех остальных в одну. Рассмотрим для начала первый вариант, когда таблицы, в общем-то, однотипные, различие лишь в цифрах. Свести их в таком случае воедино очень просто, нужно лишь суммировать значения в ячейках. Как это делается, я уже рассказывал: используется формула СУММ, которая просуммируют все значения, которые совпадают по кварталам и наименованиям (в конкретном случае). Вот и всё, принцип действий очень простой.

Если таблицы разные

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

К счастью, такой вариант имеется! Представим, что у нас есть три разноплановые таблицы, которые нужно объединить.

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

Как выполнить консолидацию? Все очень просто:

Откройте предварительно все исходники, после чего создайте пустую книгу, воспользовавшись комбинацией [Ctrl]+[N].
Выберите ячейку, затем откройте вкладку «Данные» и отыщите кнопочку «Консолидация».
В открывшемся окне необходимо установить соответствующие параметры: в поле «Ссылка» перейдите на одну из таблиц и выделите ее полностью, включая шапку. Теперь кликните по кнопке «Добавить», которая расположена в окошке консолидации, после чего в поле «Список диапазонов» будет добавлен диапазон, выделенный ранее.
Проделайте те же действия для оставшихся таблиц.
Поставьте галочки в обоих пунктах «Использовать в качестве имен», а также напротив «Создавать связи с исходными данными». Нажмите «ОК».
Теперь можно наслаждаться результатом: все файлы просуммировались по названиям левого столбика и верхней строчки выделенных областей каждой из таблиц.

Надеюсь, этот небольшой ликбез поможет вам быстро и качественно объединять несколько таблиц в Excel в одну.

Видео в помощь

Как объединить две или несколько таблиц Excel

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

Приведенные ниже инструменты достаточно мощны, чтобы удовлетворить потребности большинства пользователей, хотя, если вы пытаетесь справиться с сотнями электронных таблиц, вам может понадобиться использовать макросы или Visual Basic.

Копирование и вставка

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

Варианты копирования и вставки в Excel не имеют единственного вкуса! Когда вы копируете свои данные и переходите к их вставке, щелкните правой кнопкой мыши в том месте, куда вы хотите вставить данные, и выберите опцию Специальная вставка. Вот несколько наиболее полезных:

Значения / Формулы: если у вас есть числовая ячейка, которая рассчитывается по формуле, вы можете либо скопировать только значение, либо сохранить формулу. Параметр копирования и вставки по умолчанию в Excel сохраняет формулу.

Транспонирование: изменяет строки на столбцы, а столбцы — на строки.Это может быть очень полезно, если, например, вы получили электронные таблицы от людей с разными идеями о том, куда должны идти метки.

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

Используйте функцию «Переместить или Копировать» для объединения рабочих книг

Если вы работаете с несколькими книгами / рабочими листами, копирование быстро устареет. К счастью, в Excel есть встроенная функция, которая позволяет перемещать листы между книгами.

1. Откройте книгу, в которую вы хотите переместить листы, и книги, из которых вы хотите переместить листы. В Excel требуется, чтобы исходная и целевая книги были открыты для перемещения таблиц. В приведенном ниже примере они показаны на одном экране с использованием параметра «Просмотр рядом».

2. Щелкните правой кнопкой мыши на одном из листов, который вы хотите переместить, и когда откроется меню, нажмите кнопку «Переместить или скопировать».

3. Меню должно показать вам выпадающий список всех открытых электронных таблиц. Выберите тот, который будет вашим пунктом назначения. Выберите, хотите ли вы, чтобы ваш лист находился в начале, конце или где-то посередине существующих рабочих листов целевой книги. Не игнорируйте флажок Создать копию! Если вы не проверите его, ваш лист будет удален из вашей исходной книги.

4. Проверьте книгу назначения. Перемещенный или скопированный лист должен отображаться с тем же именем, что и в исходной книге, но может иметь (2) или другой номер после него, если в месте назначения есть повторяющееся имя.

Используйте функцию консолидации для объединения рабочих листов

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

1. Создайте новую рабочую книгу или рабочий лист для ваших консолидированных данных, затем откройте исходные рабочие листы.

2. Откройте новую электронную таблицу и перейдите к кнопке «Консолидация» на вкладке «Данные».

3. Обратите внимание, что здесь есть несколько функций. Каждая функция будет комбинировать ячейки с одинаковыми метками по-разному: сумма, среднее, сохранять минимальное / максимальное значение и т. Д.

4. В полях под функциями спрашивается, где найти ваши метки. Если у вас есть метки в вашем верхнем ряду или крайнем левом столбце, установите соответствующие флажки. В противном случае ваши электронные таблицы будут просто смешиваться друг с другом как копия-паста.

Как объединить две или несколько таблиц Excel

5.В поле «Создать ссылки на исходные данные» ячейки в вашей целевой таблице будут ссылаться на ячейки в исходных таблицах, чтобы данные автоматически обновлялись.

6. Нажатие на кнопку «Обзор» открывает ваш файловый менеджер. Выберите электронную таблицу, которую вы хотите объединить.

7. Нажмите кнопку «Ссылка» и откройте электронную таблицу, которую вы только что выбрали. Здесь вы можете выделить данные, которые вы хотите объединить.

8. Нажмите клавишу Enter и затем кнопку «Добавить». Это должно поместить выбранные данные в список слияния.

9. Повторите вышеуказанные шаги для любого количества рабочих листов / книг, которые вы хотите объединить.

10. Нажмите «ОК», чтобы объединить выбранные данные в новую электронную таблицу и убедиться, что они работают правильно.

Заключение

Эти методы объединяют удобный интерфейс с приличное количество энергии. Существует не так много заданий, которые эти три инструмента, в некоторой комбинации, не смогут выполнить, и они не требуют каких-либо экспериментов с кодом VB или макросами. Однако, как и во всех вещах в Excel, это очень помогает, если ваши данные хорошо организованы, прежде чем вы начнете логически называть свои книги и рабочие таблицы, убедитесь, что строки и столбцы выстроены так, как вы хотите, и убедитесь, что ваши ссылки line up!

Объединение таблиц в Excel с помощью Power Query (Простое пошаговое руководство)

Благодаря Power Query работа с данными, разбросанными по листам или даже книгам, стала проще.

Одна из вещей, где Power Query может сэкономить вам много времени, — это когда вам нужно объединить таблицы с разными размерами и столбцами на основе совпадающего столбца.

Ниже видео, где я показываю, как именно объединять таблицы в Excel с помощью Power Query.

Если вы предпочитаете читать текст, а не смотреть видео, ниже приведены письменные инструкции.

Предположим, у вас есть таблица, как показано ниже:

В этой таблице есть данные, которые я хочу использовать, но в ней по-прежнему отсутствуют два важных столбца — «Идентификатор продукта» и «Регион», где работает торговый представитель.

Эта информация предоставляется в виде отдельных таблиц, как показано ниже:

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

И под слиянием я не подразумеваю простую копипаст.

Вам нужно будет сопоставить соответствующие записи из таблицы 1 с данными из таблиц 2 и 3.

Теперь вы можете полагаться на ВПР или ИНДЕКС/ПОИСКПОЗ, чтобы сделать это.

Или, если вы гений VBA, вы можете написать код для этого.

Но эти варианты трудоемки и сложны по сравнению с Power Query.

В этом уроке я покажу вам, как объединить эти три таблицы Excel в одну.

Чтобы эта техника работала, вам нужны соединительные столбики. Например, в Таблице 1 и Таблице 2 общим столбцом является «Элемент», а в Таблице 1 и Таблице 3 общим столбцом является «Торговый представитель». Также обратите внимание, что в этих соединительных столбцах не должно быть повторений.

Примечание. Power Query можно использовать в качестве надстройки в Excel 2010 и 2013, а начиная с Excel 2016 — это встроенная функция. В зависимости от вашей версии некоторые изображения могут выглядеть по-разному (в этом руководстве используются захваты изображений из Excel 2016).

Я назвал эти таблицы, как показано ниже:

Tabel 1 — Sales_Data
Таблица 2 — PDT_ID
Таблица 3 — Регион

. лучше давать имена, которые описывают, о чем таблица.

За один раз в Power Query можно объединить только две таблицы.

Итак, сначала нам нужно объединить Таблицу 1 и Таблицу 2, а затем объединить с ней Таблицу 3 на следующем шаге.

Объединение таблиц 1 и 2

Чтобы объединить таблицы, сначала необходимо преобразовать эти таблицы в соединения в Power Query. Когда у вас есть связи, вы можете легко объединить их.

Вот шаги, чтобы сохранить таблицу Excel как соединение в Power Query:

Выберите любую ячейку в таблице Sales_Data.
Перейдите на вкладку Данные.
В группе «Получить и преобразовать» нажмите «Из таблицы/диапазона». Это откроет редактор запросов.
В редакторе запросов перейдите на вкладку «Файл».
Нажмите «Закрыть и загрузить в».
В диалоговом окне «Импорт данных» выберите «Только создать соединение».
Нажмите OK.

Вышеуказанные шаги создадут соединение с именем Sales_Data (или любым другим именем, которое вы дали таблице Excel).

Повторите вышеуказанные шаги для Таблицы 2 и Таблицы 3.

Таким образом, когда вы закончите, у вас будет три соединения (с именами Sales_Data, Pdt_Id и Region).

Теперь давайте посмотрим, как объединить таблицы Sales_Data и Pdt_Id.

Щелкните вкладку Данные.
В группе «Получить и преобразовать данные» нажмите «Получить данные».
В раскрывающемся списке нажмите «Объединить запросы».
Нажмите «Объединить». Это откроет диалоговое окно слияния.
В диалоговом окне «Объединение» выберите «Sales_Data» в первом раскрывающемся списке.
Выберите «Pdt_Id» во втором раскрывающемся списке.
В предварительном просмотре «Данные о продажах» щелкните столбец «Элемент». При этом будет выбран весь столбец.
В предварительном просмотре «Pdt_Id» щелкните столбец «Элемент». При этом будет выбран весь столбец.
В раскрывающемся списке «Присоединить тип» выберите «Левый внешний (все из первого, совпадение из второго)».
Нажмите OK.

Вышеуказанные шаги откроют редактор запросов и отобразят данные из Sales_Data с одним дополнительным столбцом (Pdt_Id).

Объединение таблиц Excel (таблица 1 и 2)

Теперь процесс объединения таблиц будет происходить в редакторе запросов со следующими шагами:

В дополнительном столбце (Pdt_Id) щелкните двойной указатель стрелка в шапке.
В открывшемся окне параметров снимите все имена столбцов и выберите только элемент. Это связано с тем, что у нас уже есть столбец с названием продукта в существующей таблице, и нам нужен только идентификатор продукта для каждого продукта.
Снимите флажок «Использовать исходное имя столбца в качестве префикса».
Нажмите «ОК».

Это даст вам результирующую таблицу, в которой есть все записи из таблицы Sales_Data, а также дополнительный столбец с идентификаторами продуктов (из таблицы Pdt_Id).

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

Но нам нужно объединить три таблицы, так что предстоит еще много работы.

Вам необходимо сохранить полученную таблицу как соединение (чтобы мы могли использовать ее для объединения с таблицей 3).

Вот шаги, чтобы сохранить эту объединенную таблицу (с данными из таблицы Sales_Data и Pdt_Id) в качестве соединения:

Перейдите на вкладку «Файл»
Нажмите «Закрыть и загрузить в».
В диалоговом окне «Импорт данных» выберите «Только создать соединение».
Нажмите OK.

Новые объединенные данные будут сохранены как соединение. Вы можете переименовать это соединение, если хотите.

Объединение таблицы 3 с результирующей таблицей

Процесс слияния третьей таблицы с результирующей таблицей (которую мы получили слиянием Таблицы 1 и Таблицы 2) точно такой же.

Вот шаги для объединения этих таблиц:

Нажмите на вкладку Данные.
В группе «Получить и преобразовать данные» нажмите «Получить данные».
В раскрывающемся списке нажмите «Объединить запросы».
Нажмите «Объединить». Это откроет диалоговое окно слияния.
В диалоговом окне «Объединение» выберите «Объединить1» в первом раскрывающемся списке.
Выберите «Регион» во втором раскрывающемся списке.
В предварительном просмотре «Объединение 1» щелкните столбец «Торговый представитель». При этом будет выбран весь столбец.
В предварительном просмотре региона щелкните столбец «Торговый представитель». При этом будет выбран весь столбец.
В раскрывающемся списке «Присоединить тип» выберите «Левый внешний» (все из первого, совпадение из второго).
Нажмите OK.

Вышеуказанные шаги откроют редактор запросов и отобразят данные из Merge1 с одним дополнительным столбцом (Регион).

Теперь процесс объединения таблиц будет происходить в редакторе запросов со следующими шагами:

В дополнительном столбце (Регион) нажмите на двойную стрелку в заголовке.
В открывшемся окне параметров снимите все имена столбцов и выберите только регион.
Снимите флажок «Использовать исходное имя столбца в качестве префикса».
Нажмите «ОК».

Приведенные выше шаги дадут вам таблицу, в которой объединены все три таблицы (таблица Sales_Data с одним столбцом для Pdt_Id и одним для региона).

Вот шаги для загрузки этой таблицы в Excel:

Перейдите на вкладку «Файл».
Нажмите «Закрыть и загрузить в».
В диалоговом окне «Импорт данных» выберите параметры «Таблица» и «Новые листы».
Нажмите OK.

Это даст вам результирующую объединенную таблицу на новом рабочем листе.

Одна из лучших особенностей Power Query заключается в том, что вы можете легко вносить любые изменения в базовые данные (таблицы 1, 2 и 3), просто обновляя их.

Например, предположим, что Лору переводят в Азию, и вы получаете новые данные за следующий месяц. Теперь вам не нужно повторять вышеперечисленные шаги снова. Все, что вам нужно сделать, это обновить таблицу, и она сделает все заново за вас.

Через несколько секунд вы получите новую объединенную таблицу.

Вам также могут понравиться следующие учебные пособия по Power Query:

Объединение данных из нескольких книг в Excel (с помощью Power Query).
Объединение данных из нескольких рабочих листов в один рабочий лист в Excel.
Как развернуть данные в Excel с помощью Power Query (также известного как «Получить и преобразовать»)
Получить список имен файлов из папок и подпапок (с помощью Power Query)

Как объединить две или более таблиц?

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Дополнительно…Меньше

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

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

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

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

Значения идентификаторов заказов повторяются в синей таблице, но значения идентификаторов заказов в оранжевой таблице уникальны. Если бы мы просто скопировали и вставили данные из таблицы Orange, значения идентификатора продаж и региона для второй позиции заказа 20050 сместились бы на одну строку, что изменило бы значения в новых столбцах таблицы Blue. стол.

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

Идентификатор заказа	Дата продажи	Код продукта
20050	02.02.14	К6077Б
20050	02. 02.14	К9250ЛБ
20051	02.02.14	М115А
20052	03.02.14	А760Г
20052	03.02.14	Э3331
20052	03. 02.14	СП1447
20053	03.02.14	Л88М
20054	04.02.14	С1018ММ
20055	05.02.14	К6077Б
20056	06. 02.14	Э3331
20056	06.02.14	Д534С

Вот данные для таблицы Orange. Скопируйте его на тот же лист. Вставив его на лист, нажмите Ctrl+T, чтобы преобразовать его в таблицу, а затем переименуйте таблицу в Orange.

Идентификатор заказа	Идентификатор продаж	Регион
20050	447	Запад
20051	398	Юг
20052	1006	Север
20053	447	Запад
20054	885	Восток
20055	398	Юг
20056	644	Восток
20057	1270	Восток
20058	885	Восток

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

Вот как:

Скопируйте заголовки Идентификатор продаж и Регион в таблицу Orange (только эти две ячейки).
Вставьте заголовки в ячейку справа от заголовка Product ID синей таблицы.
Теперь синяя таблица состоит из пяти столбцов, включая новые столбцы «Идентификатор продаж» и «Регион».
В синей таблице в первой ячейке под Идентификатором продаж начните писать эту формулу:
=ВПР(
org/ListItem»>
В синей таблице выберите первую ячейку в столбце идентификатора заказа, 20050.
Частично заполненная формула выглядит так:
Часть [@[Идентификатор заказа]] означает «получить значение в этой же строке из столбца идентификатора заказа».
Введите запятую и выберите мышью всю таблицу Orange, чтобы в формулу было добавлено «Orange[#All]».
Введите еще одну запятую, 2, еще одну запятую и 0, например: ,2,0
Нажмите Enter, и готовая формула будет выглядеть так:
Часть Orange[#All] означает «просмотреть все ячейки в таблице Orange». 2 означает «получить значение из второго столбца», а 0 означает «вернуть значение, только если есть точное совпадение».
Обратите внимание, что Excel заполнил ячейки в этом столбце, используя формулу ВПР.
Вернитесь к шагу 3, но на этот раз начните писать ту же формулу в первой ячейке под областью.
На шаге 6 замените 2 на 3, чтобы итоговая формула выглядела так:
Между этой формулой и первой формулой есть только одно отличие — первая получает значения из столбца 2 таблицы Orange, а вторая — из столбца 3.
Теперь вы увидите значения в каждой ячейке новых столбцов в синей таблице. Они содержат формулы ВПР, но отображают значения. Вы захотите преобразовать формулы ВПР в этих ячейках в их фактические значения.
org/ListItem»>
Выберите все ячейки значений в столбце Идентификатор продаж и нажмите Ctrl+C, чтобы скопировать их.
Нажмите Главная > стрелка ниже Вставьте .
В галерее Вставить щелкните Вставить значения .
Выберите все ячейки значений в столбце «Регион», скопируйте их и повторите шаги 10 и 11.
Теперь формулы ВПР в двух столбцах заменены значениями.

Подробнее о таблицах и ВПР

org/ListItem»>
Изменение размера таблицы путем добавления строк и столбцов
Используйте структурированные ссылки в формулах таблиц Excel
ВПР: когда и как использовать (обучающий курс)

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Запросы на слияние и объединение таблиц

Знакомство с Power Query

Справка по Excel и обучение

Знакомство с Power Query

Объединение запросов и объединение таблиц

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

Excel для Microsoft 365 Microsoft 365 для бизнеса Дополнительно. .. Меньше

В настоящее время данные суммируются только на уровне продукта. В таблице Категория продукты могут быть объединены на уровень выше. Таким образом, вы можете загрузить таблицу Category и создать объединение полей Product Name .

Выберите рабочий лист «Категории «, а затем выберите Данные > Получение и преобразование данных > Из таблицы или диапазона .
Выберите Закрыть и загрузить таблицу , чтобы вернуться к рабочему листу, а затем переименуйте вкладку Лист в «Категории PQ».
org/ListItem»>
Выберите рабочий лист Данные о продажах , откройте Power Query, а затем выберите Главная > Объединить > Запросы на слияние > Объединить как новый .
В диалоговом окне Merge в таблице Sales выберите столбец Product Name из раскрывающегося списка.
В столбце Название продукта выберите таблицу Категория из раскрывающегося списка.
Чтобы завершить операцию объединения, выберите OK .
Power Query возвращает данные о продажах. Но вам нужно указать Power Query, какой столбец таблицы вы хотите отобразить.
Чтобы указать основную категорию, выберите Фильтр списка полей > Категория , а затем выберите OK .
Power Query отображает объединенные таблицы.
Переместите столбец Категория с правой стороны.
Удалить имя таблицы и заголовок столбца.
Выберите Главная > Закрыть и загрузить .
Power Query создает для вас новый рабочий лист.
org/ListItem»>
Переименуйте лист в «Объединение PQ».
Примечание В диалоговой панели Query and Connection появился новый запрос, но предыдущие преобразования остались. Это полезно, когда вы хотите создать несколько представлений одних и тех же данных для создания разных отчетов.
Чтобы сделать заголовок запроса более информативным, наведите указатель мыши на имя запроса, выберите многоточие в диалоговом окне Запрос , выберите параметр Свойства , а затем измените его на Объединение таблиц .

Объединение таблиц путем сопоставления данных или заголовков столбцов

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

Как часто при анализе данных в Excel вся необходимая информация собирается на одном листе? Почти никогда! Это очень распространенная ситуация, когда разные фрагменты данных рассредоточены по многим листам и книгам. К счастью, существует несколько различных способов объединения данных из нескольких таблиц в одну, и этот учебник научит вас делать это быстро и эффективно.

Объединить 2 таблицы Excel с формулами
Объединить две таблицы по одному столбцу с функцией ВПР
Объединить таблицы с помощью INDEX MATCH
Объединить две таблицы в Excel по нескольким столбцам
Объединение таблиц в одну с помощью Power Query
Мастер объединения таблиц — объединение двух таблиц по общим столбцам
Объединение листов — объединение нескольких таблиц по заголовкам столбцов
Дополнительные инструменты для объединения таблиц в Excel

Как объединить две таблицы в Excel с помощью формул

Какое бы задание вам ни нужно было выполнить в рабочих листах, где вы в первую очередь ищете решение? Как и многие пользователи, я обычно захожу на вкладку Формулы и открываю список функций. Объединение таблиц не является исключением 🙂

Как соединить таблицы с помощью ВПР

Если вы хотите объединить две таблицы на основе одного столбца , функция ВПР — это то, что вам нужно.

Предположим, у вас есть две таблицы на двух разных листах: основная таблица содержит имена продавцов и товары, а таблица поиска содержит имена и суммы. Вы хотите объединить эти две таблицы, сопоставив данные в Продавец столбец:

Как видите, порядок имен в основной таблице не соответствует порядку в таблице поиска, поэтому простой метод копирования/вставки не сработает.

Чтобы объединить две таблицы с помощью столбца соответствия ( Продавец ), введите следующую формулу в ячейку C2 основной таблицы:

=ВПР($A2,'Таблица поиска'!$A$2:$B$10 ,2,FALSE)

Где:

$A2 — это значение, которое вы ищете.
‘Таблица поиска’!$A$2:$B$10 — это таблица для поиска (обратите внимание, что мы блокируем диапазон с абсолютными ссылками на ячейки).
2 — номер столбца, из которого нужно получить значение.

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

Имейте в виду, что ВПР Excel имеет несколько ограничений, наиболее важным а именно: 1) невозможность извлечь данные из столбца слева от столбца поиска и 2) жестко заданный номер столбца нарушает формулу при добавлении или удалении столбцов в таблице поиска. С другой стороны, вы можете легко изменить порядок возвращаемых столбцов, просто изменив число в col_index_num аргумент.

Совет. Если у вас есть подписка на Excel 365, вы можете использовать более мощный преемник ВПР — функцию XLOOKUP в Excel.

Как объединить таблицы в Excel с ПОИСКПОЗ ИНДЕКС

Если вы ищете более мощную и универсальную альтернативу функции ВПР, воспользуйтесь этой комбинацией ПОИСКПОЗ ИНДЕКС:

ИНДЕКС ( return_range , ПОИСКПОЗ (

6 искомое_значение , lookup_range , 0))
Синтаксис подробно объясняется в этом руководстве: ИНДЕКС / ПОИСКПОЗ в Excel. И здесь я покажу вам, как использовать эту формулу для поиска справа налево , что не может сделать функция ВПР.
Допустим, у вас есть другая таблица поиска с идентификаторами заказов в первом столбце, и вы хотите скопировать эти идентификаторы в основную таблицу, сопоставив имена продавцов. Для лучшей наглядности обе таблицы размещены на одном листе:
Для выполнения этой задачи вы вводите следующие аргументы в формулу Index Match:
Диапазон_возврата — $E$2:$E$10
Искомое_значение — $A2
Диапазон поиска — $F$2:$F$10
Обратите внимание на знак $, который блокирует диапазоны, чтобы предотвратить их изменение при копировании формулы вниз по таблице:
Завершенная формула выглядит следующим образом:
=ИНДЕКС($E$2:$E$10, MATCH( $A2, $F$2:$F$10, 0))
…и прекрасно сочетает данные из двух таблиц:
В Excel 365 для той же цели можно использовать новую функцию XLOOKUP:
=XLOOKUP(A2, $F$2:$F$10, $E$2: $E$10, «Не найдено»)
Как объединить таблицы путем сопоставления нескольких столбцов
Если две таблицы, которые вы хотите объединить, не имеют уникального идентификатора, такого как идентификатор заказа или SKU, вы можете сопоставить значения в двух или более столбцов с помощью этой формулы:
ИНДЕКС( lookup_table , MATCH(1, ( lookup_value1 = lookup_range1 ) * ( lookup_value2 = lookup_range2 ), 0), return_column_number )
Note . Это формула массива, поэтому не забудьте нажать Ctrl + Shift + Enter, чтобы ввести ее правильно.
Разбивку формулы можно найти здесь: Поиск по нескольким критериям. А пока сосредоточимся на практическом использовании.
Предположим, что у вас есть следующие две таблицы, которые нужно объединить в одну. Поскольку столбец Order ID отсутствует в таблице поиска, единственный способ сопоставить заказы — это продавец и продукт :
На основе приведенного выше снимка экрана давайте определим аргументы для нашей формулы:
Lookup_table — $F$2:$H$9
Искомое_значение1 — $B2
Lookup_range1 — $F$2:$F$9
Lookup_value2 — $C2
Lookup_range2 — $G$2:$G$9
Номер_столбца_возврата — 3
Опять же, не забудьте исправить все диапазоны с абсолютными ссылками на ячейки , чтобы они не изменились при копировании формулы вниз:
=ИНДЕКС($F$2:$H$9, MATCH(1 , ($B2=$F$2:$F$9) * ($C2=$G$2:$G$9), 0), 3)
Введите формулу в D3, нажмите Ctrl + Shift + Enter, скопируйте ее в следующие строки и проверьте результат:
Чтобы более подробно изучить приведенные выше примеры и, возможно, перепроектировать формулы, вы можете загрузить наш образец книги для объединения двух таблиц в Excel.
Объединение нескольких таблиц в одну с помощью Excel Power Query
В ситуациях, когда вам нужно объединить две или более таблиц с разным количеством строк и столбцов, может пригодиться Excel Power Query. Однако имейте в виду, что объединение таблиц с помощью Power Query невозможно выполнить всего за пару кликов. Объяснение всех нюансов заняло бы гораздо больше места, чем у нас есть здесь, поэтому я лишь кратко обозначу основные особенности:
Power Query может объединить две таблицы, сопоставив один или несколько столбцов.
Исходные таблицы могут находиться на одном листе или на разных рабочих листах.
Исходные таблицы не изменены. Данные объединяются в новую таблицу, которую можно импортировать в существующий или новый рабочий лист.
В Excel 2016 — Excel 365 Power Query является встроенной функцией. В Excel 2010 и Excel 2013 его можно загрузить как надстройку.
Подробное руководство можно найти в этом руководстве: Как соединить таблицы с помощью Excel Power Query.
Мастер слияния таблиц — быстрый способ объединения таблиц путем сопоставления столбцов
Если вы еще не очень хорошо разбираетесь в формулах Excel, и у вас нет времени разбираться в загадочных тонкостях Power Query, наш мастер слияния таблиц может быть вашим временем. -заставка. Ниже я покажу три самых популярных варианта использования.
Пример 1. Объединение двух таблиц по нескольким столбцам
Если вам трудно запомнить формулу массива для сопоставления столбцов, положитесь на нашу надстройку, которая сделает эту работу быстро и идеально.
В этом примере мы будем использовать уже знакомые таблицы и соединить их на основе двух столбцов: Продавец и Товар . Обратите внимание, что в таблице подстановки на 2 столбца больше, чем в основной таблице:
После добавления мастера слияния таблиц на ленту Excel вам необходимо сделать следующее:
Выберите любую ячейку в основной таблице и нажмите Кнопка «Объединить две таблицы» на вкладке Ablebits Data :
Убедитесь, что надстройка правильно указала диапазон, и нажмите Далее :
Выберите таблицу поиска и нажмите Далее :
Укажите пары столбцов для соответствия, Продавец и Товар в нашем случае, и нажмите Далее :
Подсказка. Если регистр текста в ключевых столбцах имеет значение, установите флажок Сопоставление с учетом регистра , чтобы прописные и строчные буквы воспринимались как разные символы.
При необходимости выберите столбцы для обновления значениями из таблицы поиска. Так как в 9-ке обновлять нечего0862 Order IDs , оставляем его невыбранным и просто нажимаем Next .
Выберите столбцы для добавления в основную таблицу и нажмите Далее .
На этом шаге вы сообщаете мастеру, как именно вы хотите объединить таблицы. Все опции имеют описательные метки, поэтому я не буду вдаваться в длинные объяснения. Если вы не уверены в каком-либо параметре, щелкните знак вопроса рядом с ним, и небольшая диаграмма покажет вам, как будут объединены таблицы.
Параметры по умолчанию прекрасно работают в нашем случае, поэтому мы нажимаем Готово ничего не меняя:
Даем мастеру несколько секунд для обработки и просмотра результата:
Как вы можете видеть на снимке экрана выше, мастер сделал следующее:
Добавлен столбец Сумма путем сопоставления имени продавца и продукта в обеих таблицах.
Добавлен столбец Статус , который позволяет легко фильтровать совпадающие и новые строки. Если вы этого не хотите, снимите соответствующий флажок на последнем шаге.
Новые строки, которые присутствовали только в таблице поиска, были скопированы в конец и выделены синим цветом.
Если вы не хотите выделять новые строки, снимите флажок Установить цвет фона для всех добавленных строк на последнем шаге.
Если вы не хотите добавлять новые строки, снимите флажок Добавить несоответствующие строки в конец основной таблицы на последнем шаге.
Пример 2. Объединение таблиц и обновление выбранных столбцов
Если ваша основная таблица содержит устаревшие данные, вы можете обновить ее соответствующими значениями из таблицы поиска.
В качестве примера давайте объединим 2 таблицы по Идентификатор заказа и обновим значения в столбце Цена :
Чтобы получить результат, показанный на изображении выше, вам нужно сделать следующее:
Шаг 1 , Выберите главную таблицу.
Шаг 2. Выберите таблицу поиска.
Шаг 3. Выберите ID заказа в качестве соответствующего столбца.
Шаг 4. Выберите Цена в качестве столбца для обновления.
Шаг 5. Пропустите его, так как нет столбцов для добавления.
Шаг 6. Поскольку в столбце Новая цена есть несколько пробелов, мы выбираем обновление только в том случае, если ячейки таблицы поиска содержат данные a. При желании вы можете выделить обновленные ячейки любым цветом по вашему выбору. На скриншоте ниже показаны настройки:
Совет. Чтобы предотвратить перезапись существующих данных, вы можете обновить только пустые ячейки в основной таблице.
Пример 3. Объединение нескольких совпадений из двух таблиц
В ситуациях, когда таблица поиска содержит несколько вхождений значения поиска, вы можете захотеть перенести их все в свою основную таблицу. Задачу можно выполнить с помощью одной из нетривиальных формул массива, описанных в Vlookup, для возврата нескольких совпадений в Excel. Или вы можете сделать это простым способом с помощью мастера слияния таблиц.
Предположим, что ваша основная таблица содержит только один заказ каждого продавца, а таблица поиска содержит дополнительные заказы. Теперь вы хотите объединить все заказы в одну таблицу, сгруппированные по имени продавца, например:
Похоже, работы много? Нет, если у вас есть мастер слияния таблиц 🙂
Шаг 1. Выберите главную таблицу.
Шаг 2. Выберите таблицу поиска.
Шаг 3. Выберите Seller в качестве столбца для сопоставления.
Шаг 4. Обновление ID заказа и Product .
Шаг 5. Нет столбцов для добавления.
Шаг 6. Вставьте дополнительные совпадающие строки после строки с тем же значением ключа . При желании установите цвет фона для добавленных строк для быстрого просмотра изменений:
В приведенных выше примерах показаны только 3 из множества возможных способов соединения таблиц в Excel. Если вам интересно узнать о других сценариях, с которыми могут справиться мастера слияния таблиц, ознакомьтесь с иллюстрациями на этой странице. Или вы можете загрузить 30-дневную пробную версию и попробовать ее.
Объединение таблиц в Excel по заголовкам столбцов
В приведенных выше примерах мы объединяли две таблицы с одинаковыми столбцами и переносили данные из одной таблицы в другую. Если вы хотите объединить несколько таблиц с разных листов в одну на основе заголовков столбцов, наша надстройка «Объединить листы» — подходящий инструмент для этой работы.
На изображении ниже показаны исходные таблицы и желаемый результат:
Вот как вы можете выполнить эту задачу:
На ленте Excel перейдите на вкладку Ablebits > группу Объединить и щелкните Объединить Листы кнопка:
Выберите все рабочие листы, которые вы хотите объединить в один.
Если вы хотите объединить только одну таблицу, а не все данные, наведите указатель мыши на имя листа, а затем щелкните значок диалогового окна «Свернуть» справа, чтобы выбрать диапазон:
Выберите столбцы, которые вы хотите объединить, Идентификатор заказа и Продавец в этом примере:
При необходимости выберите дополнительные параметры. Мы используем стандартные, которые отлично работают в большинстве случаев:
.
Наконец, укажите, куда вы хотите поместить результирующую таблицу, и нажмите Объединить :
Готово! Три таблицы объединяются в одну точно так же, как показано в начале этого примера.
Дополнительные инструменты для объединения таблиц в Excel
Мастер объединения таблиц и объединение листов — самые популярные инструменты для объединения таблиц в Excel. Если у вас есть какая-то другая задача, скорее всего, вы также найдете быстрое решение на вкладке Ablebits Data :
Позвольте мне кратко описать, что делает каждая из этих надстроек:
Объединить две таблицы — объединить две таблицы, имеющие один или несколько одинаковых столбцов, как показано в этих примерах.
Объединить листы — объединяет несколько рабочих листов в один на основе заголовков столбцов, как мы сделали минуту назад в этом примере.
Объединить дубликаты — объединяет повторяющиеся строки по ключевым столбцам.
Объединить листы — объединяет таблицы и суммирует их данные.
Копировать листы — обеспечивает 4 различных способа объединения листов в Excel.
Объединить ячейки — объединить ячейки, столбцы и строки без потери данных, даже если выделенный фрагмент содержит несколько значений.
Мастер Vlookup — быстрый способ создать формулу Vlookup или Index/Match, наиболее подходящую для вашего набора данных.
Сравнить листы — найти, выделить и объединить различия между двумя листами.
Сравнить несколько листов — выделить различия на двух или более листах.
Все вышеперечисленные функции, а также более 60 других инструментов для экономии времени включены в наш Ultimate Suite for Excel. Ознакомительная версия доступна для скачивания прямо под этим постом. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Доступные загрузки
Ultimate Suite 14-дневная полнофункциональная версия (файл .zip)
Excel Power Query Объединить таблицы Объединить табличные данные
Вы можете использовать Power Query (или Get & Transform Data) для объединения данных из 2 или более таблиц, если в этих таблицах есть совпадающие заголовки столбцов. с одинаковыми именами. Затем создайте сводную таблицу из объединенных data
ПРИМЕЧАНИЕ. В более ранних версиях Excel используйте множественную консолидацию. функция объединения двух или более таблиц
Создание сводной таблицы из данных на 2 листах
Данные в двух таблицах
Добавление таблиц
Объединить данные таблицы
Создать сводную таблицу
Видео: Объединить 3 таблицы Excel
Получить образец файла
Учебник по сводной таблице Excel
Сводная таблица из данных на 2 листах
Создать сводную таблицу из таблицы данных на двух или более листах, используйте Power Query Excel (также называемую Get & Transform Data). С помощью нескольких простых шагов вы можете объединить все данные, если в этих таблицах есть один или несколько заголовков столбцов. с одинаковыми именами.
Посмотрите это видео, чтобы увидеть шаги, а письменные инструкции находятся под видео.
Данные в двух таблицах
Чтобы объединить данные из двух связанных таблиц с помощью этого метода, в хотя бы один заголовок столбца должен точно соответствовать в двух таблицах.
В этом примере рабочая книга Excel содержит данные о продажах из двух регионов — Востока и Запада. Данные находятся в двух отдельных таблицах и на разных листах.
Две таблицы не идентичны, но большинство заголовков столбцов имеют точное совпадение в другой таблице.
В данных Востока (синий) есть столбец Менеджер, которого нет в другой таблице
В данных West (черный) есть столбец Tax, которого нет в другой таблице.
Добавление таблиц
Первый шаг — создать запрос для каждой таблицы, Восток и Запад, чтобы получить ее данные.
Команды Power Query
Если вы используете команды на вкладке Excel Power Query, выполните следующие действия:
Выберите ячейку в таблице East.
На ленте щелкните вкладку Power Query
Затем нажмите команду «Из таблицы».
Команды Get & Transform
Если вы НЕ используете команды вкладки Power Query, выполните следующие действия для команд Get & Transform:
На листе East выберите любую ячейку в таблице East.
На ленте щелкните вкладку Данные
Затем нажмите кнопку «Из таблицы/диапазона»
Окно Power Query
После нажатия кнопки на вкладке Power Query или на вкладке Данные открывается окно редактора Power Query.
В этом окне, показанном на снимке экрана ниже, вы можете увидеть данные из Восточного региона.
Никаких изменений в этой таблице или ее данных не требуется
Щелкните Закрыть и загрузить кнопка, чтобы вернуться в книгу Excel
Добавлен новый рабочий лист
Вставлен новый рабочий лист с данными из таблицы Восток.
В в правой части окна Excel вы можете увидеть список запросов рабочей книги
Кнопка «Обновить» находится на справа от каждого запроса в списке.
Добавьте таблицу West
Затем выполните следующие действия, чтобы получить данные таблицы West.
На рабочем листе West выберите любую ячейку в таблице West.
На ленте щелкните вкладку «Данные», затем нажмите кнопку «Из таблицы/диапазона».
ИЛИ, щелкните вкладку Power Query, затем щелкните команду From Table
В открывшемся окне редактора Power Query вы можете увидеть данные из западного региона.
Никаких изменений в этой таблице или ее данных не требуется
Щелкните Закрыть и загрузить кнопка
Объединение данных таблицы
Далее вы создадите третий запрос. Этот запрос объединит данные из двух новых таблиц с отдельными данными Востока и Запада в одну объединенную таблицу.
Команды Power Query
Если вы используете Power Query:
Перейдите на вкладку Power Query на ленте
Щелкните команду Добавить, в группе «Объединить».
Команды получения и преобразования
Если вы НЕ используете Power Query:
Выберите любую ячейку в книге
Перейдите на вкладку «Данные» на ленте
.
Нажмите стрелку «Получить данные», затем нажмите «Объединить запросы» и нажмите «Добавить».
Окно добавления
Откроется окно добавления, где вы можете выбрать, какие таблицы объединять.
В окне «Добавить» выберите таблицу из каждого раскрывающегося списка имен таблиц.
В этом примере не имеет значения, какая таблица выбрана в качестве основной
После выбора обоих имен таблиц нажмите кнопку ОК.
Комбинированные данные
Затем открывается окно редактора Power Query, в котором отображаются объединенные данные.
Для столбцов, уникальные для одной из таблиц, строки из другой таблицы будут отображаться пустыми. Вы можете увидеть пример этого в столбцах «Налог» и «Менеджер» на снимке экрана ниже.
Нет необходимости вносить изменения в данные
Нажмите кнопку «Закрыть и загрузить».
Новый рабочий лист
Появится новый рабочий лист с объединенными данными из объединенных таблиц.
Примечание . Данные, которые в редакторе Power Query были показаны как «пустые», являются пустыми ячейками в таблице рабочего листа.

Создание сводной таблицы
Теперь, когда две таблицы добавлены, вы можете использовать объединенные данные для создания сводной таблицы, в которой отображаются записи о продажах с Востока и Запада.
Выберите ячейку в сводной таблице данных
Затем на вкладке «Вставка» ленты Excel нажмите Сводная таблица
Откроется диалоговое окно «Сводная таблица из таблицы или диапазона».
Выберите место для сводной таблицы
Нажмите OK, чтобы создать сводную таблицу
Добавить поля сводной таблицы в макет
Пустой макет сводной таблицы появляется в выбранном месте
Выберите любую ячейку в макете пустой сводной таблицы
Добавьте флажки к полям в списке полей сводной таблицы, чтобы добавить эти поля в макет сводной таблицы
На снимке экрана ниже:
Поля «Регион» и «Элемент» были добавлены в область меток строк
Поле единиц измерения было добавлено в область значений как Сумма единиц
Эта сводная таблица содержит сводку наборов данных в двух исходных таблицах.
Вы можете просмотреть данные Востока и Запада отдельно, или вы можете удалить поле Регион, чтобы увидеть объединенные итоги для каждого предмета продажи.
Объединить 3 таблицы Excel
Чтобы узнать, как объединить 3 таблицы с помощью Power Query, посмотрите это короткий видеоурок от Майка «ExcelIsFun» Гирвина.
Если у вас более 3 таблицы, используйте ту же технику для добавления дополнительных таблиц.
Получить образец файла
Получить образец Объединить Таблицы с файлом Power Query. Заархивированный файл Excel имеет формат xlsx и не содержит макросов. Есть две таблицы с данными Востока и Запада. Рабочая книга не содержит подключений — следуйте инструкциям на этой странице, чтобы создать запросы, объединяющие данные таблицы.
Дополнительные ресурсы по сводным таблицам
Блог о сводных таблицах
Статья о сводных таблицах Указатель
Часто задаваемые вопросы — сводные таблицы
Введение в сводную таблицу
Группировка данных
Несколько диапазонов консолидации
Промежуточные итоги
Как объединить две таблицы в Excel (5 методов)
В рабочей области мы часто сталкиваемся объединение двух отдельных таблиц друг с другом. Объединение двух отдельных таблиц повысит ясность информации и облегчит интерпретацию. Если вам интересно узнать, как объединить две таблицы в Excel, то эта статья может вам пригодиться. В этой статье мы обсудим, как вы можете объединить две отдельные таблицы в Excel с подробным объяснением.
Скачать практическую рабочую тетрадь
5 простых способов объединить две таблицы в Excel
1. Использование функции ВПР
2. Использование функции XLOOKUP
3. Применение Power Query
4. Сочетание функций ИНДЕКС и ПОИСКПОЗ
5. Использование функции копирования и вставки Excel
То, что нужно запомнить
Вывод
Статьи по Теме
Загрузить рабочую тетрадь
Загрузите эту практическую рабочую тетрадь ниже.
5 простых способов объединить две таблицы в Excel
Мы будем использовать следующий набор данных, чтобы создать связь между двумя таблицами в Excel с повторяющимися значениями. Оба набора данных имеют общий столбец. Общим столбцом является идентификатор продукта . Объединение двух таблиц будет основано на этом столбце.
1. Использование функции ВПР
Функция ВПР поможет нам искать значение из одного столбца в другой столбец. Затем скопируйте это значение и вставьте его в ячейку назначения. Что в конечном итоге поможет нам объединить две таблицы в Excel.
Шаги
На изображении ниже у нас есть две таблицы, которые мы хотим объединить вместе
Чтобы объединить таблицу, между ними должен быть общий столбец. Для данных таблиц общим столбцом является столбец Product Id .
Для этого сначала выберите ячейку I4 и введите следующую формулу:
=ВПР(F4,$B$4:$D$10,2,ЛОЖЬ)
Затем перетащите маркер заполнения в ячейку I10 .
Это заполнит диапазон ячейки I4:I10 первым столбцом первой таблицы, соответствующим столбцу Product Id .
Чтобы добавить второй столбец, выберите ячейку J4 и введите следующую формулу:
=ВПР(F4,$B$4:$D$10,3,ЛОЖЬ)
Затем перетащите маркер заполнения в ячейку J10 .
Это заполнит диапазон ячеек J5:J10 первым столбцом первой таблицы, соответствующим столбцу Product Id .
Теперь нам нужно отформатировать новые столбцы, чтобы они соответствовали остальным ячейкам.
Выберите диапазон ячеек D4:D10 , а затем щелкните значок форматирования в буфере обмена на вкладке Home .
На месте курсора появляется маленькая кисть.
С помощью этого курсора выберите диапазон ячеек I4:J10 .
Наконец, вы можете видеть, что две таблицы теперь объединены и отформатированы.
Подробнее: Как объединить две таблицы в Excel с помощью функции ВПР
2. Использование функции XLOOKUP
Функция XLOOKUP будет работать почти так же, как предыдущий метод. В XLOOKUP пользователю необходимо ввести аргумент выходного диапазона, который будет возвращен вместо серийного номера столбца в таблице.
Шаги
На изображении ниже у нас есть две таблицы, которые мы хотим объединить вместе
Чтобы объединить таблицу , у нас должен быть общий столбец между ними. Для данных таблиц общим столбцом является идентификатор продукта 9.столбец 0045.
Для этого сначала выберите ячейку I4 и введите следующую формулу:
=XLOOKUP(F4,$B$4:$B$10,$C$4:$C$10)
Затем перетащите маркер заполнения в ячейку I10 .
Это заполнит диапазон ячеек I4:I10 первым столбцом первой таблицы, соответствующим столбцу Product Id .
Чтобы добавить второй столбец, выберите ячейку J4 и введите следующую формулу:
=XLOOKUP(F4,$B$4:$B$10,$D$4:$D$10)
Затем перетащите маркер заполнения в ячейку J10 .
Это заполнит диапазон ячеек J4:J10 первым столбцом первой таблицы, соответствующим столбцу Product Id .
Теперь нам нужно отформатировать новые столбцы, чтобы они соответствовали остальным ячейкам.
Выберите диапазон ячеек D4:D10 , а затем щелкните значок форматирования в группе Буфер обмена на вкладке Главная .
На месте курсора появляется маленькая кисть.
С помощью этого курсора выберите диапазон ячеек I4:J10 .
Наконец, вы можете видеть, что две таблицы теперь объединены и отформатированы.
Подробнее: Как объединить две сводные таблицы в Excel (быстрыми шагами)
3. Применение Power Query
Power Query — один из самых мощных инструментов в Excel. Power query имеет много мощных функций по умолчанию, которые могут мгновенно решить многие проблемы
Шаги
На изображении ниже у нас есть две таблицы, которые мы хотим объединить вместе
Чтобы объединить таблицу, между ними должен быть общий столбец. Для данных таблиц общим столбцом является Столбец идентификатор продукта .
Чтобы добавить эти две таблицы данных, перейдите к Данные > Получить данные.
Далее перейдите к Из других источников > Из таблицы/диапазона .
Появится небольшое диалоговое окно.
В этом диалоговом окне необходимо ввести диапазон таблицы и поставить галочку в поле Моя таблица имеет заголовки .
Нажмите OK после этого.
Для второй таблицы сделайте то же самое и добавьте таблицу в Power Query .
В Power Query создается диалоговое окно таблицы, укажите диапазон таблицы и установите флажок Моя таблица имеет заголовки .
Нажмите OK после этого.
После загрузки обеих таблиц в запрос мощности мы можем объединить их вместе, используя функцию объединения.
Для этого откройте редактор запросов питания (нажатие OK на предыдущем шаге автоматически запустит редактор).
В редакторе запросов Power перейдите на вкладку Home .
И из вкладки Дом перейти в группу Комбинат . Затем нажмите Merge Queries .
В раскрывающемся меню выберите Merge Queries .
В новом окне под названием Объединить , выбрать Таблицу 1 в качестве первой таблицы
И во втором раскрывающемся меню выберите Таблица 2 в качестве второй таблицы.
В раскрывающемся меню ниже выберите Левый внешний (все из первого, совпадение из второго) .
Этот параметр раскрывающегося списка означает, что сопоставление начнется с первой таблицы слева, затем, в конце концов, появится правая часть сопоставления.
Нажмите OK после этого.
Сразу после нажатия OK вы увидите, что к первой таблице добавлен один столбец с именем Table2 .
Но на самом деле этот столбец является уменьшенной версией Table2 .
В этом столбце Table2 все столбцы скрыты.
Чтобы отобразить все столбцы Table2 , щелкните в правом верхнем углу заголовка столбца Table2 .
Затем в расширенном меню отметьте только Количество и Стоимость , так как у нас уже есть Идентификатор продукта в первой таблице.
Снимите флажок Использовать исходное имя столбца в качестве префикса .
Нажмите OK после этого.
После нажатия OK вы заметите, что два столбца теперь добавлены в первую таблицу.
Затем нажмите Закрыть и загрузить на вкладке Главная .
Затем в раскрывающемся меню нажмите «Закрыть и загрузить в ».
И выберите Таблица в Выберите способ просмотра этих данных в рабочей книге
Затем выберите Существующий рабочий лист , а затем выберите ячейку B13 .
Нажмите OK после этого.
После этого таблица будет загружена в ячейку B13:F19 .
И мы видим, что обе таблицы теперь объединены.
Подробнее: Как объединить две таблицы с помощью Power Query в Excel
4. Объединение функций ИНДЕКС и ПОИСКПОЗ
Функции ИНДЕКС и ПОИСКПОЗ помогут нам объединить две таблицы в Excel, сопоставив значение и затем проиндексировав значение из второй таблицы в первую таблицу.
Шаги
На изображении ниже у нас есть две таблицы, которые мы хотим объединить вместе
Чтобы объединить таблицу, между ними должен быть общий столбец. Для данных таблиц общим столбцом является столбец Product Id .
Для этого сначала выберите ячейку I4 и введите следующую формулу:
=ИНДЕКС($C$4:$C$10,ПОИСКПОЗ($F4,$B$4:$B$10,0))
Затем перетащите маркер заполнения в ячейку I10 .
Это заполнит диапазон ячеек I5:I10 первым столбцом первой таблицы, соответствующим столбцу Product Id .
Чтобы добавить второй столбец, выберите ячейку J4 и введите следующую формулу:
=ИНДЕКС($D$4:$D$10,ПОИСКПОЗ($F4,$B$4:$B$10,0))
Затем перетащите маркер заполнения в ячейку J10 .
Это заполнит диапазон ячеек I4:I10 первым столбцом первой таблицы, соответствующим столбцу Product Id .
Это завершит операцию слияния.
Теперь нам нужно отформатировать новые столбцы, чтобы они соответствовали остальным ячейкам.
Выберите диапазон ячеек D4:D10 , а затем щелкните значок форматирования в группе Буфер обмена на вкладке Главная .
На месте курсора появляется маленькая кисть.
С помощью этого курсора выберите диапазон ячеек I4:J10 .
Наконец, вы можете видеть, что две таблицы теперь объединены и отформатированы.

🔎 Разбивка формулы
ПОИСКПОЗ($F4,$B$4:$B$10,0)
Эта функция будет искать точное значение, указанное в первом аргументе, в массиве/диапазоне ячеек, указанном во втором аргументе. В этом случае он будет искать значение в ячейке F4 в массиве поиска в B4:B10, и возвращать серийный номер этого значения в этом диапазоне.
ИНДЕКС($C$4:$C$10,MATCH($F4,$B$4:$B$10,0))
После того, как мы получили серийный номер совпадающего значения в массиве поиска, затем, используя этот серийный номер, он будет искать значение того же серийного номера в другом столбце (первый аргумент) в таблице.
5. Использование функции копирования и вставки Excel
По сравнению с предыдущими методами этот довольно прост. Мы будем напрямую копировать и вставлять столбцы второй таблицы в первую таблицу.
Шаги
Как и в предыдущих методах, нам нужно иметь две таблицы с общим столбцом.
В то же время общие значения столбцов в обеих таблицах должны быть в одном и том же порядковом номере.
Сначала выберите второй и третий столбцы первого столбца, а затем щелкните правой кнопкой мыши.
В контекстном меню нажмите Копировать .
Затем выберите ячейку I4 и снова щелкните правой кнопкой мыши.
В контекстном меню нажмите Вставить .
При этом столбцы таблицы будут вставлены во вторую таблицу.
Вставка столбцов первой таблицы во второй столбец окончательно объединит две таблицы.
Подробнее: Как объединить две таблицы на основе одного столбца в Excel (3 способа)
Что нужно помнить
Необходимо поддерживать один и тот же серийный номер для записей столбцов в общих столбцах обеих таблиц.
В методе Power Query не выбирайте вторую таблицу в первую очередь. Всегда выбирайте первую таблицу, с которой будет начинаться сопоставление.
Заключение
Подводя итог, на вопрос о том, как мы можем объединить две отдельные таблицы в Excel в Excel с повторяющимися значениями, здесь отвечают 5 различными способами. Для решения этой задачи доступна для скачивания рабочая тетрадь, в которой вы можете попрактиковаться в этих методах.