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

Обновлено: 05.07.2024

Вывод: узнайте, как объединять таблицы в Excel с помощью Power Query.

Уровень квалификации: средний

Видеоруководство

Загрузить файл Excel

Объединение таблиц с помощью Power Query.xlsx (75,5 КБ)

Объединение таблиц с помощью Power Query — FINAL.xlsx (100,8 КБ)

Вот файл, содержащий макрос VBA для создания подключений Power Query ко всем таблицам в книге.

Power Query Create Table Connections Macro.xlsm (92,7 КБ)

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

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

Необходимо помнить только два обязательных условия.

Все листы или наборы данных, которые вы хотите объединить, должны быть отформатированы как таблицы Excel, а не только данные, настроенные в формате таблицы.

Чтобы превратить набор данных в таблицу Excel, просто выберите любую ячейку в наборе, а затем выберите «Форматировать как таблицу» на вкладке «Главная». Обычно хорошей идеей является назвать таблицу после того, как вы ее создали/вставили.

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

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

Есть способы обойти это, о которых я расскажу в следующем посте.

Настройка в Power Query

Теперь вы можете создавать запросы в Power Query. Сначала мы создадим запросы на подключение для каждой таблицы. Затем мы объединим эти запросы с запросом на добавление, чтобы объединить или сложить данные.

1. Создание запросов на подключение к таблицам

Чтобы объединить или добавить таблицы вместе, необходимо создать подключение к каждой из них в Power Query.

Перейдите в редактор Power Query, щелкнув From Table/Range на вкладке Data или Power Query (в зависимости от используемой версии Excel).

Открывается предварительный просмотр ваших данных. Чтобы создать соединение:

Вы можете увидеть только что созданное подключение на панели "Запросы и подключения". Если вы не видите панель «Запросы и подключения», вы можете открыть ее, нажав эту кнопку на вкладке «Данные» на ленте.

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

Использование макроса соединения таблиц

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

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

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

Закрыть и загрузить настройки

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

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

2. Объединение связанных таблиц с добавлением

После того, как все ваши таблицы подключены, объединить их очень просто:

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

Вы можете выбрать все доступные таблицы, выбрав первую таблицу, удерживая клавишу Shift, а затем выбрав последнюю таблицу в списке. Затем нажмите кнопку Добавить >>, чтобы переместить их вправо.

После того, как вы нажмете OK, вы вернетесь в редактор Power Query, где сможете предварительно просмотреть объединенные таблицы. Вы можете внести коррективы и преобразовать данные перед закрытием редактора и загрузкой данных на новый лист.

Обновление и обновление данных

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

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

Добавление новых таблиц

Если вы когда-нибудь захотите добавить в запрос новые таблицы (или исключить существующие), вы можете снова открыть окно "Добавить" следующим образом:

Щелкнув выходной запрос на панели "Запросы и подключения".
Открытие панели "Параметры запроса", если она еще не видна (вкладка "Вид", затем "Параметры запроса").
Нажав на значок шестеренки рядом с источником.

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

Добавление новых столбцов

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

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

Другие сообщения Power Query

Затем настройте и запустите Power Query с помощью этого руководства: Полное руководство по установке Power Query.

Бесплатный обучающий веб-семинар по электроинструментам

Сейчас я провожу бесплатный обучающий веб-семинар по всем электроинструментам в Excel. Сюда входят Power Query, Power Pivot, Power BI, сводные таблицы, макросы, VBA и многое другое.

Он называется The Modern Excel Blueprint. Во время вебинара я объясню, что это за инструменты и как они могут вписаться в ваш рабочий процесс.

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

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

Заключение

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

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

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

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

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

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

Лист 1: Таблица 1: Информация о клиенте

Лист 2: Таблица 2: Сведения о продукте

В обеих таблицах номер заказа является общей информацией, на основе которой мы создадим связь между ними.

Ниже приведены шаги по объединению этих двух таблиц:

Окончательный результат ниже:

Соответственно, в соответствии с вашими требованиями, вы можете перетаскивать поля.

Что нужно помнить об объединении двух таблиц в Excel

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

Сводная таблица из данных на 2 листах

Чтобы создать сводную таблицу из табличных данных на двух или более листах, используйте Power Query Excel (также называемую "Получить и преобразовать данные"). Выполнив несколько простых шагов, вы сможете объединить все данные, если в этих таблицах есть один или несколько заголовков столбцов с одинаковыми именами.

Посмотрите это видео, чтобы увидеть шаги, а письменные инструкции находятся под видео.

Данные в двух таблицах

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

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

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

В данных "Восток" (синий) есть столбец "Менеджер", которого нет в другой таблице.
В данных по Западу (черный) есть столбец "Налоги", которого нет в другой таблице.

Добавить таблицы

Первый шаг – создать запрос для каждой таблицы, Восток и Запад, чтобы получить ее данные.

Команды Power Query

Если вы используете команды на вкладке Excel Power Query, выполните следующие действия:

Выберите ячейку в таблице Восток.
На ленте откройте вкладку Power Query.
Далее нажмите команду "Из таблицы".

Команды получения и преобразования

Если вы НЕ используете команды вкладки Power Query, выполните следующие действия для команд Get & Transform:

На листе "Восток" выберите любую ячейку в таблице "Восток".
На ленте откройте вкладку "Данные".
Далее нажмите кнопку "Из таблицы/диапазона".

Окно Power Query

После нажатия кнопки на вкладке Power Query или на вкладке "Данные" открывается окно редактора Power Query.

В этом окне, показанном на снимке экрана ниже, вы можете увидеть данные из Восточного региона.

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

Добавлен новый рабочий лист

Новый рабочий лист вставлен с данными из таблицы Восток.

В правой части окна Excel вы можете увидеть список запросов книги.
Справа от каждого запроса в списке есть кнопка "Обновить".

Добавить стол West

Далее выполните указанные ниже действия, чтобы получить данные таблицы West.

На рабочем листе "Запад" выберите любую ячейку в таблице "Запад".
На ленте нажмите вкладку «Данные», затем нажмите кнопку «Из таблицы/диапазона»
- ИЛИ откройте вкладку Power Query и выберите команду "Из таблицы".
В открывшемся окне редактора Power Query вы увидите данные из западного региона.
- Никаких изменений в этой таблице или ее данных не требуется.
- Нажмите кнопку "Закрыть и загрузить".
Объединить данные таблицы

Далее вы создадите третий запрос. Этот запрос объединит данные из двух новых таблиц с отдельными данными Востока и Запада в одну объединенную таблицу.

Команды Power Query

Если вы используете Power Query:
- Перейдите на вкладку Power Query на ленте.
- Нажмите команду "Добавить" в группе "Объединить".
Команды получения и преобразования

Если вы НЕ используете Power Query:
- Выберите любую ячейку в книге.
- Перейдите на вкладку "Данные" на ленте.
- Нажмите стрелку "Получить данные", затем нажмите "Объединить запросы" и нажмите "Добавить".
Добавить окно

Откроется окно "Добавить", где вы можете выбрать, какие таблицы объединять.
- В окне добавления выберите таблицу из каждого раскрывающегося списка имен таблиц
  - В этом примере не имеет значения, какая таблица выбрана в качестве основной.
  Объединенные данные
  
  Затем открывается окно редактора Power Query, в котором отображаются объединенные данные.
  
  Для столбцов, которые являются уникальными для одной из таблиц, строки из другой таблицы будут отображаться пустыми. Вы можете увидеть пример этого в столбцах «Налог» и «Менеджер» на снимке экрана ниже.
  - Никаких изменений в данные вносить не нужно.
  - Нажмите кнопку "Закрыть и загрузить".
  Новый рабочий лист
  
  Появится новый рабочий лист с объединенными данными из объединенных таблиц.
  
  Примечание. Данные, для которых в редакторе Power Query указано значение null, являются пустыми ячейками в таблице рабочего листа.
  
  Создать сводную таблицу
  
  Теперь, когда две таблицы добавлены, вы можете использовать объединенные данные для создания сводной таблицы, в которой показаны записи о продажах на Востоке и на Западе.
  - Выберите ячейку в объединенной таблице данных.
  - Затем на вкладке "Вставка" ленты Excel нажмите "Сводная таблица".
  Откроется диалоговое окно "Сводная таблица из таблицы или диапазона".
  - Выберите место для сводной таблицы.
  - Нажмите "ОК", чтобы создать сводную таблицу.
  Добавить сводные поля в макет
  
  В выбранном месте появится пустой макет сводной таблицы
  - Выберите любую ячейку в макете пустой сводной таблицы.
  - Добавьте флажки к полям в списке полей сводной таблицы, чтобы добавить эти поля в макет сводной таблицы.
  На снимке экрана ниже:
  - Поля «Регион» и «Элемент» добавлены в область меток строк.
  - Поле "Единицы" добавлено в область "Значения" как "Сумма единиц".
  Эта сводная таблица содержит сводку наборов данных в двух исходных таблицах.
  
  Вы можете просмотреть данные по востоку и западу отдельно или удалить поле "Регион", чтобы увидеть объединенные итоги по каждому товару.
  
  Объединить 3 таблицы Excel
  
  Чтобы узнать, как объединить 3 таблицы с помощью Power Query, посмотрите этот короткий видеоурок от Майка «ExcelIsFun» Гирвина.
  
  Если у вас более 3 таблиц, используйте тот же метод, чтобы добавить дополнительные таблицы.
  
  Получить образец файла
  
  Получите образец файла объединения таблиц с помощью Power Query. Заархивированный файл Excel имеет формат xlsx и не содержит макросов. Есть две таблицы с данными Востока и Запада. В рабочей книге нет подключений. Следуйте инструкциям на этой странице, чтобы создать запросы, объединяющие данные таблицы.
  
  Объединение таблиц в Excel невозможно, если в обеих таблицах хотя бы один общий столбец. Если первое условие выполнено, мы можем объединить таблицы с помощью функций VlookUP и Index Match. С помощью функции Vlookup мы можем получить значения из второй таблицы в первую таблицу, если значение поиска совпадает с диапазоном поиска. И если мы хотим использовать функцию Index вместе с Match для объединения таблиц, у нас также должно быть хотя бы одно общее значение для сопоставления.
  
  Функции Excel, формулы, диаграммы, форматирование, создание информационной панели Excel и др.
  
  Примеры объединения двух таблиц в Excel
  
  Ниже приведены различные примеры объединения таблиц в Excel:
  
  Это самый простой способ. Это не связано с какой-либо формулой или ракетостроением. Мы просто используем метод копирования и вставки, чтобы объединить их. У меня есть таблицы продаж за два месяца. Один — данные о продажах за январь, второй — данные о продажах за февраль.
  
  У меня есть две одинаковые таблицы, но не вместе. Здесь мы можем сделать два простых слияния. Один из них — создание новой таблицы или добавление данных в существующую таблицу. Если вы заметили, заголовки обеих таблиц одинаковы.
  
  Вариант 1
  
  Поскольку у меня есть регион с тем же заголовком, я могу объединить таблицу, подобную приведенной ниже.
  
  На изображении выше я исключил заголовок «Регион», поскольку он является общим для обеих таблиц.
  
  Вариант 2
  
  Чтобы сделать данные более гибкими для анализа, я могу просто изменить структуру данных, как показано в таблице ниже.
  
  Теперь посмотрите на приведенную выше таблицу, я объединил одну под другой, но добавил еще один столбец в качестве месяца. Это для анализа данных путем применения сводной таблицы; приведенная выше структура данных важна. Мы можем легко применить сводную таблицу к приведенной выше таблице.
  
  Кто не знает ВПР? Почти все пользователи Excel знают об этом; Я надеюсь, что вы также знаете об этом. Если вы полностью используете VLOOKUP, объединение таблиц будет проще для вас. У меня есть три таблицы информации о сотрудниках. В первой таблице показан идентификатор сотрудника и его имя, во второй таблице — идентификатор сотрудника и его отдел, а в третьей таблице — идентификатор сотрудника и информация об их зарплате.
  
  Если вы посмотрите на все три таблицы, EMP ID является общим заголовком во всех трех таблицах. Основываясь на этом общем заголовке, мы можем объединить все три таблицы вместе. Слияние должно быть в следующем формате.
  
  Мне нужно объединить столбцы "Отдел" и "Зарплата" из таблиц 2 и 3. Если вы знакомы с формулой ВПР, то это будет прогулка в парке.
  - Примените формулу ВПР для отдела из таблицы 2:
  - Примените формулу ВПР к ячейке N2 столбца отдела:
  - Теперь перетащите формулу вниз во все пустые ячейки:
  - Примените формулу ВПР к зарплате из таблицы 3:
  - Примените формулу ВПР к ячейке O2 столбца "Зарплата":
  - Теперь перетащите формулу вниз во все пустые ячейки:
  Теперь мы объединили все данные из другой таблицы, применив метод ВПР.
  
  ИНДЕКС И ПОИСКПОЗ станут для вас новой формулой. Но не о чем беспокоиться, это просто альтернативная формула для формулы ВПР. Она работает точно так же, как и формула ВПР.
  - Используйте приведенную ниже формулу для объединения столбца отдела из таблицы 2:
  - Примените формулу ВПР к ячейке L3 столбца отдела:
  - Теперь перетащите формулу вниз во все пустые ячейки:
  - Используйте приведенную ниже формулу для объединения столбца "Зарплата" из таблицы 3:
  - Примените формулу ВПР к ячейке N2 столбца отдела:
  - Теперь перетащите формулу вниз во все пустые ячейки:
  Что нужно помнить об объединении двух таблиц в Excel
  - POWER QUERY может объединять множество таблиц. В Excel 2010 и 2013 это надстройка, а в Excel 2016 – встроенная функция.
  - Если вы выполняете слияние с помощью функции ВПР, вам нужно быть осторожным с повторяющимися записями в значении поиска. Прежде чем продолжить, проверьте наличие повторяющихся значений.
  - После применения формулы удалите формулу с помощью специального метода вставки, поскольку это помешает вашей объединенной таблице данных, если что-то случится с другими зависимыми таблицами.
  - Вы можете использовать функцию ЕСЛИОШИБКА, чтобы избавиться от ошибок, генерируемых формулой ВПР в случае ошибок.
  - Если данные находятся на нескольких листах с одинаковыми заголовками, вы можете выполнить поиск по коду VBA, чтобы объединить листы вместе.
  Рекомендуемые статьи
  
  Это руководство по объединению двух таблиц в Excel. Здесь мы обсуждаем объединение двух таблиц в Excel и как объединить две таблицы в Excel вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете ознакомиться с другими нашими рекомендуемыми статьями –
  
  Читайте также: