Объединить данные из нескольких листов в один excel power query
Обновлено: 17.07.2024
На примере файлов бюджетов покажу как можно собирать данные со всех этих файлов в одну итоговую таблицу и просуммировать все присланные данные по статьям из каждой таблицы.
Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query - что такое и почему её необходимо использовать в работе?
Модель агрегации файлов.zip (53,5 KiB, 1 118 скачиваний)
Видеоинструкция:
Для ведения бюджета применяется таблица такого вида:
Сама таблица преобразована заранее в так называемую "умную" таблицу: выделяем таблицу -вкладка Вставка (Insert) и выбрать Таблица (Table) :
Для каждого филиала отдельный файл только с одним этим листом. После заполнения филиалы присылают эти файлы в головной офис, где их необходимо объединить в одну такую же таблицу, но суммировать данные по каждой статье и каждому месяцу, чтобы получить единый файл бюджета с суммированием по каждой статье от всех филиалов.
Все действия будут производиться при помощи Power Query и лишь в самом конце на лист будет выгружена итоговая таблица, которую потом надо будет только обновлять(пара кликов мыши), если данные изменятся или будут присланы файла от за другие месяцы или от других филиалов. Никаких макросов использовать не надо.
Перейдем к реализации.
Создаем новую пустую книгу, переходим на вкладку Данные(или Power Query) -Получить данные -Из файла -Из папки:
В появившемся окне указываем путь к папке, в которую были помещены файлы бюджетов, присланные филиалами
Нажимаем Ок.
Появится окно, в котором будет список всех файлов в выбранной папке. Нажимаем Изменить и попадем в редактор запросов Power Query. Здесь пошагово мы и будем делать все преобразования отчетов для их объединения и приведения к нужному виду.
Для начала удалим лишние столбцы, оставив только два столбца: Content и Name :
Для этого выделяем лишние столбцы с зажатой клавишей Shift и нажимаем Delete(или правая кнопка мыши -Удалить столбцы).
Теперь надо получить таблицы из файлов. Для этого переходим на вкладку Добавить столбец -Пользовательский столбец. В появившемся окне даем имя новому столбцу(у меня это Данные), а в поле формулы вписываем такую функцию:
=Excel.Workbook([Content])
Нажимаем Ок.
В отчет будет добавлен новый столбец. Необходимо его «развернуть» - получить все данные из каждого файла. Для этого нажимаем на этом столбце значок в виде двух разнонаправленных стрелок, снимаем галочку «Использовать исходное имя столбца как префикс» и нажимаем Ок:
Будет добавлено еще два столбца, из которых аналогичным образом разворачиваем столбец Data(нажатием на значок в виде двух разнонаправленных стрелок). Там будут наименования вроде Column1, Column2 и т.д. – это нормально, выгружаем все как есть. Получится такая картина:
Теперь столбцы Content , Name и Name.1 можно удалить (в столбце Name записано имя файла, поэтому если оно нужно – можно оставить на время отладки запроса. Но впоследствии данные будут объединены и просуммированы и оно все равно будет лишним).
Т.к. у нас реальные данные в таблицах начинаются не с первой строки и имеется шапка – необходимо убрать все лишние строки, чтобы исключить ошибки при дальнейшем суммировании данных. Для этого сначала в Column2 раскрываем меню фильтра и убираем галочки со значений NULL :
А в Column1 в фильтре убираем галочку с пункта «Статьи». Теперь первой строкой данных у нас идут названия месяцев. Делаем их заголовками: вкладка Преобразование -Таблица -Использовать первую строку в качестве заголовков:
Т.к. первый столбец теперь будет иметь не совсем понятное имя вроде Column1 - имеет смысл переименовать его в «Статьи».
Далее выделяем все столбцы месяцев и столбец Итого -вкладка Преобразование -группа Любой столбец -раскрываем список Тип данных и выбираем Десятичное число:
Теперь надо объединить все одинаковые строки статей и просуммировать данные по ним за каждый месяц. Выделяем столбец Статьи вкладка Преобразование -Таблица -Группировать по:
В появившемся окне сразу выбираем режим Дополнительно и указываем параметры группировки:
Группировка – оставляем поле Статьи . Ниже создаем 13 столбцов группировки – по одному на каждый месяц и один для Итого. Для каждого столбца указываем имя(лучше такое же как и имя исходного столбца – название месяца, т.к. именно они будут использоваться в итоговой таблице), Операция – Сумма .
Останется перейти на вкладку Главная -Закрыть и загрузить. Готовая таблица будет выгружена на новый лист текущей книги.
Теперь, если в папку будут помещены другие файлы или имеющиеся будут заменены другими и результирующую таблицу бюджета потребуется обновить – все, что необходимо будет сделать, это на созданной PowerQuery таблице в любой ячейке щелкнуть правой кнопкой мыши и выбрать Обновить:
Все файлы в папке будут просмотрены, преобразованы и просуммированы.
Power Query умеет подключаться к разным источникам. Далее рассмотрим, как получить данные из книги Excel.
Таблицы Excel
Лучше всего данные хранить в таблице Excel, это самый удобный и распространенный источник для Power Query. На ленте даже есть специальная кнопка.
Чтобы загрузить таблицу в редактор Power Query, достаточно выделить любую ее ячейку и нажать Данные → Получить и преобразовать данные → Из таблицы/диапазона.
Примечание. В вашей версии Excel расположение кнопок и их названия могут отличаться.
Если то же самое проделать с обычным диапазоном, то Excel вначале преобразует диапазон в таблицу Excel, а потом запустит Power Query.
Запросу присваивается имя таблицы Excel, которая является источником данных. Поэтому желательно сразу дать таблице говорящее название. Не обязательно, конечно, но желательно. В противном случае рекомендуется переименовать сам запрос, чтобы затем его можно было легко найти среди других запросов книги.
Данные находятся в Power Query. Новые значения, внесенные в исходную таблицу, автоматически попадут в запрос после его обновления. Далее в редакторе Power Query делают обработку данных и выгружают либо в виде таблицы Excel, либо оставляют в памяти Excel в виде подключения.
Именованный диапазон Excel
Источником для Power Query может быть не только таблица Excel. Например, вы получили красивый отформатированный отчет и не хотите вносить в него изменения. Тогда нужно использовать именованный диапазон. Самый простой способ создать именованный диапазон – это выделить область на листе и ввести название в поле Имя.
Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.
Здесь перечислены все именованные диапазоны, формулы и таблицы. Среди них есть и только что созданный Отчет.
Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.
Такой способ позволяет «не портить» исходные данные. Но у него есть и очевидный недостаток: новые строки, которые выйдут за пределы именованного диапазона, не попадут в запрос.
Динамический именованный диапазон Excel
Решить данную проблему можно, создав динамический именованный диапазон. Это такой диапазон, который задается формулой и автоматически расширяется до последней заполненной ячейки.
Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:
Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.
Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.
Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.
Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:
После ввода формулы (нажатием Enter) Power Query обратится к текущей книге и выведет все объекты, среди которых есть и наш динамический диапазон ДинамОтчет.
Название запроса не подхватывается, поэтому придется изменить самостоятельно.
Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.
Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.
Теперь в запрос будут попадать новые строки, несмотря на то, что исходные данные не являются таблицей Excel.
Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.
Консолидация данных из разных таблиц Excel
Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.
Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.
Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос
Затем в строке формул вводим знакомую команду
Power Query показывает все таблицы в текущей книге.
Их нужно развернуть кнопкой с двумя стрелками в названии поля Content (на скриншоте ниже выделено красным кружком).
Если есть лишние столбцы, то их можно не выводить, сняв соответствующую галку. Также лучше убрать галку напротив опции Использовать исходное имя столбца как префикс. Нажимаем Ok.
Все таблицы находятся на одном листе, а рядом колонка с названием источника, откуда взята каждая строка.
Данные загружены. Можно приступать к их обработке. Ограничимся преобразованием названий таблиц в настоящую дату, чтобы затем использовать для сведения данных по месяцам.
Визуально мы наблюдаем и месяц, и год. Но Power Query такое название воспринимает, как текст. Поэтому делаем следующее.
Удалим нижнее подчеркивание. Правой кнопкой мыши по названию столбца Name → Замена значений.
В следующем окне настроек указываем, что меняем _ на пусто, то есть в нижнем поле ничего не указываем.
Подчеркивание удаляется из названия.
Поиск и замена здесь работает так же, как и в обычном Excel.
Далее запускаем команду Преобразование → Столбец «Дата и время» → Дата → Выполнить анализ.
Power Query распознает дату и меняет формат колонки. Мы также переименовываем столбец на Период.
Полученную таблицу можно использовать для анализа данных. Выгрузим ее на лист Excel.
Главная → Закрыть и загрузить.
Но что-то пошло не так. Во-первых, внизу таблицы пустая строка; во-вторых, при выгрузке произошла одна ошибка. Обновим запрос (справа от названия запроса значок обновления).
Что-то еще больше пошло не так. Даты исчезли, снизу таблицы добавились новые строки, а количество ошибок уже 19. Спокойствие, только спокойствие! Дело вот в чем.
Помните, на первом шаге мы получили все таблицы из файла? Так ведь и выгруженная таблица – это тоже таблица! Получается, Power Query взял 3 исходных таблицы, обработал, выгрузил на лист Excel и на следующем круге видит уже 4 таблицы!
При повторном обновлении запрос захватывает их все, а т.к. таблица выхода имеет другую структуру, то возникают ошибки.
Короче, из запроса нужно исключить таблицу, которая получается на выходе (Запрос1). Есть разные подходы, самый простой – это добавить шаг фильтрации. Выделяем в правой панели первый шаг Источник, открываем фильтр в колонке с названиями, снимаем галку с таблицы Запрос1 → Ok.
Снова выгружаем таблицу в Excel и на этот раз все в порядке.
Сделаем с помощью сводной таблицы маленький отчет по месяцам.
Прошло время, и в файл добавили новую таблицу с продажами за апрель.
Требуется обновить сводный отчет. Представьте на минуту, как это происходит в обычном Эксель: таблица копируется в самый низ общего источника, продлевается колонка с датой, изменяется диапазон для сводной таблицы, обновляется весь отчет.
А вот, как это выглядит при использовании Power Query.
Достаточно два раза нажать кнопку Обновить все (первый раз – для обновления запроса, второй – для сводной таблицы).
На добавление в отчет новых данных вместе с их обработкой потребовалось несколько секунд.
Вот за это мы так любим Power Query.
Для объединения подходят многие файлы с данными. Обычно это результаты выгрузки из автоматизированных систем.
Файлы могут быть в формате txt, csv, xml, xlsx. C текстовыми файлами txt, csv никаких проблем нет, данные просто будут в одной общей таблице.
C xml-файлами немного сложнее, так как их структура может быть непредсказуема (многоуровневая табличная архитектура), но, в основном, надстройка POWERQUERY легко справляется и с ними.
Кроме того, мы можем ещё до объединения файлов, сделать обработку.
Например, удалить или отфильтровать столбцы, разбить столбец на несколько или наоборот объединить несколько столбцов в один, удалить лишние пробелы в столбце. Можно даже изменить тип данных столбца, если это необходимо. Следует отметить, что данная настройка есть по умолчанию в MS Excel с версии 2016 года.
Как все это работает?
Допустим у нас есть папка с файлами, которые необходимо объединить в один. Первым делом, мы открываем пустой файл xlsx.
В верхнем меню выбираем «Данные», затем «Создать запрос» и указываем нашу папку с файлами.
Нажимаем объединить и изменить
Нажимаем кнопку ОК и работаем с предварительным просмотром, где можно производить редактирование таблицы.
После проведенных манипуляций (фильтрование, удаление каких-либо полей и т.д.), нажимаем «Закрыть и загрузить». Объединенная таблица(запрос) выгрузится на лист. Кроме того, если у нас поменяются исходные файлы, не требуется заново создавать запрос, достаточно будет просто обновить уже имеющийся.
Например, мы первым запросом объединили несколько файлов, вторым объединили другие файлы. Тогда можно их соединить, при наличии у них одинакового поля. Следует отметить, что поддерживается правое, левое и полное соединение.
Предположим, у вас есть три таблицы в книге, теперь вы хотите объединить эти таблицы в одну таблицу на основе соответствующих ключевых столбцов, чтобы получить результат, как показано на скриншоте ниже. Это может быть сложной задачей для большинства из нас, но, пожалуйста, не волнуйтесь, в этой статье я расскажу о некоторых методах решения этой проблемы.
 |  |  | ||
 | ||||
Объединение двух или более таблиц в одну на основе ключевых столбцов с помощью функции Power Query (Excel 2016 и более поздние версии)
Для использования Power Query функция для объединения нескольких таблиц в одну на основе соответствующих ключевых столбцов, выполните следующие действия шаг за шагом:
1. Если диапазоны данных не относятся к табличному формату, сначала вы должны преобразовать их в таблицы, выберите диапазон, а затем нажмите Вставить > Настольные, В Создать таблицу диалоговое окно, нажмите OK кнопку см. скриншоты:
 |  |  |
2. После создания таблиц для каждого из диапазонов данных выберите первую таблицу и щелкните Данные > Из таблицы / диапазона, см. снимок экрана:
3, Затем в Table1-Power Query Editor окна, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить в, см. снимок экрана:
4. В выскочившем Импортировать данные, наведите на Только создать соединение , а затем нажмите OK кнопку, см. снимок экрана:
5. Затем создается первая таблица соединений в Запросы и связи Теперь повторите шаги 2–4, описанные выше, для создания таблиц соединений для двух других таблиц, которые вы хотите объединить. Когда закончите, вы получите скриншот, показанный ниже:
6. После создания соединений для таблиц вы должны объединить первые две таблицы в одну, нажмите Данные > Получить данные > Объединить запросы > идти, см. снимок экрана:
7. В идти диалоговом окне выполните следующие действия:
8, Затем нажмите OK кнопку в Редактор запросов Merge1-Power окна, нажмите кнопку, см. снимок экрана:
9. А затем в развернутом поле:
- (1.) Оставить значение по умолчанию Расширять выбран вариант;
- (2.) В Выбрать все столбцы поле со списком, отметьте имя столбца, который вы хотите объединить с первой таблицей;
- (3.) Снимите флажок Использовать исходное имя столбца в качестве префикса опцию.
10, Затем нажмите OK Кнопка, теперь вы можете видеть, что данные столбца во второй таблице были добавлены в первую таблицу, см. снимок экрана:
11. На этом этапе первая таблица и вторая таблица были успешно объединены ключевым столбцом, теперь вам нужно импортировать эту объединенную таблицу в новую таблицу подключения слияния, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить в, см. снимок экрана:
12. В выскочившем Импортировать данные диалоговое окно, выберите Только создать соединение и нажмите OK кнопку, см. снимок экрана:
13. Здесь вы можете увидеть, что создается соединение с именем Слияние1 в Запросы и связи панель, см. снимок экрана:
14. После объединения первых двух таблиц теперь вам нужно объединить новые Слияние1 таблица с третьей таблицей, нажмите Данные > Получить данные > Объединить запросы > идтиИ в идти диалоговом окне выполните следующие операции:
15. А затем нажмите OK, В Редактор запросов Merge2-Power окна, нажмите и в развернутом поле отметьте имя столбца, который вы хотите объединить из третьей таблицы, и снимите флажок Использовать исходное имя столбца в качестве префикса вариант, см. снимок экрана:
16, Затем нажмите OK Кнопка, вы получите объединенную таблицу с тремя таблицами вместе, и теперь вы должны импортировать эту объединенную таблицу на новый лист таблицы, нажмите Главная > Закрыть и загрузить > Закрыть и загрузить в, см. снимок экрана:
17. В Импортировать данные диалоговое окно, выберите Настольные и Новый рабочий лист варианты, см. снимок экрана:
18. Наконец, новая таблица с данными из трех таблиц на основе соответствующих ключевых столбцов была создана на новом листе, как показано ниже:
Советы:
1. Если ваши исходные данные изменяются, вам нужно, чтобы объединенная таблица была также изменена, щелкните одну ячейку в объединенной таблице, а затем нажмите запрос > обновление чтобы получить обновленные данные. Смотрите скриншот:
2. С помощью этой функции вы также можете объединить гораздо больше таблиц, повторив вышеуказанные шаги.
Объединяйте две или более таблиц в одну на основе ключевых столбцов с удивительной функцией
В этом разделе я покажу полезную функцию - Слияние таблиц of Kutools for Excel, с помощью этой функции вы можете быстро объединить две или более таблиц в одну на основе ключевых столбцов.
Например, у меня есть две таблицы, которые нужно объединить, как показано на скриншоте ниже:
 |  |  |
Советы: Чтобы применить это Слияние таблиц функция, во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.
После установки Kutools for Excel, пожалуйста, сделайте так:
1. Нажмите Kutools Plus > Слияние таблиц, см. снимок экрана:
2. На первом этапе Слияние таблиц мастера, выберите основную таблицу и таблицу поиска отдельно, (Внимание: данные столбца в поисковой таблице будут добавлены в основную таблицу), см. снимок экрана:
3. На шаге 2 из Слияние таблиц мастера, проверьте имя ключевого столбца, на основе которого вы хотите объединить таблицы, см. снимок экрана:
4. Нажмите Следующая кнопку на шаге 3 Слияние таблиц мастер, пожалуйста, нажмите Следующая кнопку напрямую, см. снимок экрана:
5. Затем, на шаге 4 мастера, проверьте имя столбца из таблицы поиска, которую вы хотите добавить в основную таблицу, см. Снимок экрана:
6. Продолжайте нажимать Следующая на последнем шаге мастера в Добавить поле со списком опций, отметьте Добавить несовпадающие строки в конец основной таблицы вариант, в то же время вы также можете выбрать операции для повторяющихся строк по мере необходимости. Смотрите скриншот:
7, Затем нажмите Завершить Кнопка, соответствующий столбец данных в таблице поиска будет добавлен в основную таблицу, как показано ниже:
| | |
Советы:
1. С помощью этой функции вы можете обновлять данные в основной таблице другой таблицей по мере необходимости.
2. Чтобы объединить больше таблиц, вам просто нужно выбрать результат новых объединенных данных в качестве основной таблицы, а затем повторить вышеуказанные шаги.
Читайте также: