Если вам нужно объединить наборы данных like-for-like в нескольких листах Excel в одну таблицу, не тратьте время и не рискуйте совершить ошибки, делая это вручную. Вместо этого используйте мощный редактор Power Query Editor в Excel. Если вы никогда раньше не использовали этот инструмент, вы будете поражены тем, насколько он прост в использовании!
Что следует отметить перед началом работы
Прежде чем начать, обратите внимание на следующие моменты:
- В этом руководстве обсуждается объединение таблиц только из одной и той же рабочей книги, а не из внешних источников или нескольких рабочих книг.
- Убедитесь, что все наборы данных, которые вы хотите добавить, отформатированы как таблицы Excel с заголовками столбцов. Кроме того, каждая таблица должна содержать одинаковые заголовки столбцов, хотя столбцы не обязательно должны быть в одинаковом порядке.
- Чтобы процесс прошел гладко, проверьте, что каждая таблица правильно названа в соответствии с содержащимися в ней данными.
Чтобы следовать по ходу чтения этого руководства, загрузите бесплатную копию книги Excel, использованной в примере. После того, как вы нажмете на ссылку, вы найдете кнопку загрузки в правом верхнем углу экрана.
Шаг 1: Создание подключений к данным
Давайте представим, что вам дали книгу Excel с двумя листами. Лист Vegetable_Prices_2022 содержит таблицу с названием Vegetables, а лист Fruit_Prices_2022 содержит таблицу с названием Fruit.
Обе таблицы содержат одинаковые заголовки столбцов, хотя столбцы находятся в разном порядке в каждом наборе данных. Ваша задача — сложить таблицы, одну поверх другой, образовав один набор данных со всей объединенной информацией.
Первый шаг включает создание связей между каждым из наборов данных.
Для этого выберите любую ячейку в первой таблице и на вкладке «Данные» на ленте нажмите «Из таблицы/диапазона».
Затем в редакторе Power Query нажмите нижнюю половину кнопки «Закрыть и загрузить» в правом верхнем углу и выберите «Закрыть и загрузить в».
Далее в диалоговом окне «Импорт данных» выберите переключатель «Только создать подключение» и нажмите «ОК».
На этом этапе в правой части окна Excel откроется панель «Запросы и подключения», где будет указано только что созданное вами подключение.
Теперь повторите этот процесс для оставшихся таблиц, которые вы хотите подключить, и просматривайте их список на панели «Запросы и подключения» по мере их подключения.
Шаг 2: Объедините запросы
Теперь, когда вы создали связи между наборами данных, пришло время объединить их физически.
На любом листе книги перейдите на вкладку «Данные», нажмите «Получить данные», затем выберите «Объединить запросы» > «Добавить».
Теперь выберите подходящий вариант в соответствии с количеством таблиц, которые вы собираетесь сложить. В этом случае вы складываете два набора данных, поэтому выберите «Две таблицы». Затем используйте раскрывающиеся меню, чтобы выбрать таблицы, которые вы хотите добавить.
При нажатии кнопки «ОК» редактор Power Query снова откроется с добавленными выбранными таблицами.
Обратите внимание, что, несмотря на то, что столбцы формы находились в разных местах в каждом из исходных наборов данных, редактор Power Query распознал их как соответствующие поля и объединил их в результирующем дополнении.
Шаг 3: Преобразование данных в редакторе Power Query
Прежде чем загрузить добавленные данные обратно в книгу Excel, внесите необходимые изменения в структуру данных.
Например, поскольку две таблицы по-прежнему расположены друг над другом, вы можете захотеть отсортировать данные по столбцу «Продукт», нажав кнопку фильтра в заголовке столбца и выбрав «Сортировать по убыванию».
Во-вторых, было бы разумнее расположить столбец «Форма» рядом со столбцом «Продукт», поэтому щелкните, перетащите и отпустите его за заголовок столбца.
Наконец, чтобы выделить названия продуктов, щелкните правой кнопкой мыши заголовок столбца, наведите курсор на «Преобразовать» и нажмите «В верхнем регистре».
Шаг 4: загрузка новых добавленных данных
Теперь, когда ваши данные добавлены и отредактированы, вы готовы загрузить их в свою рабочую книгу.
Нажмите верхнюю половину кнопки «Закрыть и загрузить» в верхнем левом углу редактора Power Query, чтобы загрузить таблицу на новый лист в рабочей книге.
Для других опций, таких как загрузка данных на существующий рабочий лист или в виде сводной таблицы, щелкните нижнюю половину кнопки и выберите «Закрыть и загрузить в».
Теперь то, что раньше было двумя наборами данных, аккуратно организовано в одну таблицу.
Если исходные данные изменятся, выберите любую ячейку в добавленной таблице и на вкладке «Конструктор таблиц» нажмите «Обновить». Это гарантирует, что добавленная таблица всегда будет отражать последние версии исходных данных.
Дополнительные действия, которые вы можете предпринять
Вам не обязательно останавливаться на достигнутом! Вот еще несколько шагов, которые вы можете предпринять, чтобы максимально эффективно использовать ваши новые данные.
Настройте свойства таблицы
По умолчанию таблицы, созданные с помощью Power Query, используют зеленый средний стиль таблицы 7. Чтобы выбрать другой стиль, выберите любую ячейку в таблице и выберите стиль в группе «Стиль таблицы» на вкладке «Конструктор таблиц» на ленте.
При открытой вкладке «Конструктор таблиц» просмотрите параметры в группе «Параметры стиля таблицы».
Добавить еще одну таблицу
Если вы хотите добавить еще одну таблицу из другого рабочего листа к существующему добавлению, сначала создайте новое подключение, выполнив шаг 1 выше. Затем дважды щелкните новый запрос на панели «Запросы и подключения».
Далее в редакторе Power Query выберите исходный файл append.
Теперь нажмите на значок шестеренки рядом с шагом Source. Там вы можете добавить новый запрос, прежде чем нажать «OK», а затем «Close And Load».
Создание сводных таблиц и диаграмм
Сводные таблицы и сводные диаграммы очень удобны для глубокого анализа данных, обобщения и представления. Итак, чтобы углубиться в ваши сложенные данные, выберите любую ячейку в таблице и на вкладке «Вставка» на ленте нажмите «Сводная таблица» или «Сводная диаграмма».
Редактор Power Query в Excel также может импортировать и организовывать данные из внешних источников, таких как PDF, веб-сайт или другая электронная таблица. Привычка использовать этот мощный инструмент, который ранее был надстройкой, пока не был интегрирован в Excel в качестве собственной функции в 2016 году, несомненно, сэкономит вам время и сделает обработку данных проще, чем когда-либо.
