Как собрать данные из разных файлов excel в одну таблицу

Собрать и просуммировать данные из разных файлов при помощи PowerQuery

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

Если еще не работали с надстройкой PowerQuery и не знаете что это такое, то для начала лучше ознакомиться со статьей: Power Query — что такое и почему её необходимо использовать в работе?

Ниже можно скачать файлы, которые применялись в статье. В архиве два файла бюджета(в папке Бюджет) и готовая модель с запросом(файл «Сводный»).
В файле с запросом так же применен прием получения пути к файлам динамически из папки, которая расположена в папке с файлом запроса. Подробнее про это можно прочитать в статье: Относительный путь к данным PowerQuery
Скачать готовую модель:

Модель агрегации файлов.zip (53,5 KiB, 1 397 скачиваний)

Для ведения бюджета применяется таблица такого вида:

Сама таблица преобразована заранее в так называемую «умную» таблицу: выделяем таблицу -вкладка Вставка (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 Базовый №6. Объединить все файлы из папки по вертикали

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

В этом видео вы узнаете:

• Как объединить все таблицы в одной папке с Power Query
• Как сделать консолидацию всех файлов в папке в Excel
• Как объединить по вертикали все файлы в одной папке

Решение

Разберем 2 примера. Первым пример будет простым. Мы объединим файлы без предварительной обработки.

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

Объединить файлы из одной папки без предварительной обработки

Если предварительная обработка не требуется, то задача решается в 2 логических этапа:

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

Объединить файлы из одной папки с предварительной обработкой

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

Сначала нужно подключиться к папке с файлами и развернуть столбец Content, нажав на кнопку:

После нажатия на кнопку Power Query автоматически создаст запросы, функции и параметры:

Все, что вы проделаете с запросом «Пример файла» автоматически применится к каждому файлу в папке.

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

Примененные функции

• Folder.Files
• Table.SelectColumns
• Table.AddColumn
• Csv.Document
• Table.ExpandTableColumn
• Table.PromoteHeaders
• Table.RemoveRowsWithErrors
• Table.TransformColumnTypes
• Int64.Type
• Table.Skip
• Table.SelectRows
• Table.RenameColumns
• Table.ColumnNames
• Excel.CurrentWorkbook

Код

Без предварительной обработки

let // Подключаемся к папке и выбираем файлы для объединения source = Folder.Files(path & "Котировки csv"), cols_select_1 = Table.SelectColumns(source, ), col_add = Table.AddColumn(cols_select_1, "Таблица", each Csv.Document([Content])), cols_select_2 = Table.SelectColumns(col_add, ), // Развернуть табличный столбец и почистить данные col_expand = Table.ExpandTableColumn( cols_select_2, "Таблица", , < "Таблица.Column1", "Таблица.Column2", "Таблица.Column3", "Таблица.Column4", "Таблица.Column5", "Таблица.Column6", "Таблица.Column7" >), headers_promote = Table.PromoteHeaders(col_expand, [PromoteAllScalars = true]), rows_remove_errors = Table.RemoveRowsWithErrors(headers_promote, ), types_1 = Table.TransformColumnTypes( rows_remove_errors, , > ), types_2 = Table.TransformColumnTypes( types_1, < , , , , >, "en-US" ) in types_2

С предварительной обработкой

Код «Пример файла»:

let source = Folder.Files(path_folder), rows_select = Table.SelectRows(source, each ([Extension] = ".txt")), get_file = rows_select[Content] in get_file

Код «Параметр файла примера1»:

#"Пример файла" meta [ IsParameterQuery = true, BinaryIdentifier = #"Пример файла", Type = "Binary", IsParameterQueryRequired = true ]

Код «Преобразовать пример файла из Продажи»:

let source = Csv.Document( #"Параметр файла примера1", [Delimiter = " ", Columns = 26, Encoding = 65001, QuoteStyle = QuoteStyle.None] ), rows_skip = Table.Skip(source, 4), headers = Table.PromoteHeaders(rows_skip, [PromoteAllScalars = true]), rows_select = Table.SelectRows(headers, each ([Дата] <> "Итого")) in rows_select

Код «Преобразовать файл из Продажи»:

let fn_append = (#"Параметр файла примера1" as binary) => let source = Csv.Document( #"Параметр файла примера1", [ Delimiter = " ", Columns = 26, Encoding = 65001, QuoteStyle = QuoteStyle.None ] ), rows_skip = Table.Skip(source, 4), headers = Table.PromoteHeaders(rows_skip, [PromoteAllScalars = true]), rows_select = Table.SelectRows(headers, each ([Дата] <> "Итого")) in rows_select in fn_append

Код результирующей таблицы:

let source = Folder.Files(path_folder & "Продажи\"), fn_append = Table.AddColumn( source, "Преобразовать файл из Продажи", each #"Преобразовать файл из Продажи"([Content]) ), cols_rename = Table.RenameColumns( fn_append, ), cols_select = Table.SelectColumns( cols_rename, ), col_expand = Table.ExpandTableColumn( cols_select, "Преобразовать файл из Продажи", Table.ColumnNames(#"Преобразовать файл из Продажи"(#"Пример файла")) ) in col_expand

Этот урок входит в Базовый курс Power Query

Номер урока	Урок	Описание
1	Зачем нужен Power Query. Обзор возможностей	Этот урок сам по себе является мини-курсом. Здесь вы узнаете для каких видов операций с данными создан Power Query.
2	Подключение Excel	Подключаемся к файлам Excel. Импортируем данные из таблиц, именных диапазонов, динамических именных диапазонов.
3	Подключение CSV/TXT, таблиц, диапазонов	Подключаемся к к файлам CSV/TXT, Excel.
4	Объединить таблицы по вертикали	Учимся объединять две таблицы по вертикали — combine.
5	Объединить по вертикали все таблицы одной книги друг за другом	Как объединить по вертикали все таблицы одной книги, находящиеся на разных листах Excel.
6	Объединить по вертикали все файлы в папке	Объединяем по вертикали таблицы, которые находятся в разных файлах в одной папке.
7	Объединение таблиц по горизонтали	Учимся объединять таблицы по горизонтали — JOIN, merge.
8	Объединить таблицы с агрегированием	Объединить таблицы по горизонтали и сразу выполнить группировку с агрегированием — JOIN + GROUP BY.
9	Анпивот (Unpivot)	Изучаем операцию Анпивот — из сводной таблицы делаем таблицу с данными.
10	Многоуровневый анпивот (Анпивот с подкатегориями)	Более сложный вариант Анпивота — в строках находится несколько измерений.
11	Скученные данные	Данные собраны в одном столбце, нужно правильно его разбить на несколько.
12	Скученные данные 2	Разбираем еще один пример скученных данных.
13	Ссылка на другую строку	Как сослаться на другую строку.
14	Ссылка на другую строку 2	Как сослаться на другую строку, используя объединение по горизонтали.
15	Виды объединения таблиц по горизонтали	Изучаем виды объединения таблиц по горизонтали — LEFT JOIN, FULL JOIN, INNER JOIN, CROSS JOIN.
16	Виды объединения таблиц по горизонтали 2	Изучаем анти-соединение и соединение таблицы с ней же самой — ANTI JOIN, SELF JOIN.
17	Группировка	Изучаем операцию группировки с агрегированием — GROUP BY.
18	Консолидация множества таблиц пользовательской функцией	Объединяем по вертикали множество таблиц с предварительной обработкой при помощи пользовательской функции.
19	Деление на справочник и факт	Разделим один датасет на два датасета: справочник и факт.
20	Создание параметра	Мы можем ввести значение в какую-то ячейку Excel, а потом передать это значение в формулу Power Query.
21	Таблица параметров	Создадим целую таблицу параметров и будем их использовать в запросах Power Query.
22	Объединение таблиц по вертикали, когда не совпадают заголовки столбцов	Как объединить две таблицы по вертикали, если названия столбцов не совпадают.
23	Поиск ключевых слов	Научимся искать ключевые слова в текстовом поле.
24	Поиск ключевых слов 2	Будем искать ключевые поля в текстовом поле и присваивать этому значению какую-то категорию.

Power Query Базовый №6. Объединить все файлы из папки по вертикали was last modified: 13 мая, 2022 by Admin

XLS Слияние

Объедините/объедините XLS с Excel, PDF, изображениями и HTML онлайн бесплатно.

Поддерживается aspose.com & aspose.cloud
Копирование в один клик
Ваши файлы успешно обработаны СКАЧАТЬ СЕЙЧАС
Сохраняем в облачное хранилище:
Successfully saved to Dropbox

Нажмите Ctrl + D, чтобы сохранить его в закладках и не искать его снова.

Поделиться через фейсбук

Поделиться в Твиттере

Поделиться в LinkedIn

Посмотреть другие приложения

Попробуйте наш облачный API

Добавьте это приложение в закладки

Обработанные файлы
Загружено MB

Aspose.Cells Excel Merger

Это бесплатное веб-приложение для объединения нескольких файлов Excel: объединение в PDF, DOCX, PPTX, XLS, XLSX, XLSM, XLSB, ODS, CSV, TSV, HTML, JPG, BMP, PNG, SVG, TIFF, XPS, MHTML и Маркдаун. Объединяйте Excel онлайн из Mac OS, Linux, Android, iOS и где угодно.

• Объединить XLS
• Сохранить в желаемый формат: PDF, XLS, XLSX, DOCX, PPTX, XLSM, XLSB, ODS, CSV, TSV, HTML, JPG, BMP, PNG, SVG, TIFF, XPS, MHTML, MD
• Быстрый способ объединить несколько файлов таблиц Excel
• Объедините разные форматы файлов в один
• Легко сохраняйте документ в формате PDF, изображений или HTML.
• Объединение файлов таблиц OpenDocument
• Выберите порядок объединенных файлов
• Объединение файлов Excel в несколько листов или один лист

Как объединить файлы XLS с помощью приложения Aspose.Cells Merger

• Загрузите файлы XLS для объединения.
• При необходимости установите параметры слияния.
• Нажмите кнопку «ОБЪЕДИНИТЬ».
• Загрузите объединенные файлы мгновенно или отправьте ссылку для загрузки по электронной почте.

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

Быстрый и простой способ слияния

Загрузите свои документы и нажмите кнопку «ОБЪЕДИНИТЬ». Он объединит ваши файлы документов в один и предоставит вам ссылку для загрузки объединенного документа. Выходной формат будет выходным форматом вашего первого документа.

Объединение из любого места

Он работает на всех платформах, включая Windows, Mac, Android и iOS. Все файлы обрабатываются на наших серверах. Вам не требуется установка плагинов или программного обеспечения.

Поддерживается Aspose.Cells . Все файлы обрабатываются с использованием API-интерфейсов Aspose, которые используются многими компаниями из списка Fortune 100 в 114 странах.

Объединение большого числа таблиц в Power Query

В компании AdventureWorks ежегодно по мере выпуска новых товаров создается новый файл. Сейчас имеется три файла: C03E03 — 2015.xlsx, C03E03 — 2016.xlsx, C03E03 — 2017.xlsx за три года. Но в будущем их может стать больше. Отчет должен включать все таблицы из папки. Скачайте приложенные файлы Excel и поместите их в выделенную папку. Откройте новую книгу в Excel. Пройдите Данные –> Получить данные –> Из файла –> Из папки. В окне Обзор кликните Открыть, хотя никакие файлы не выбраны:

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

Скачать заметку в формате Word или pdf, примеры в формате архива (внутри несколько файлов Excel без поддержки макросов)

В окне предварительного просмотра PQ вы увидите файлы, находящиеся в паке. Выберите Объединить –> Объединить и преобразовать данные.

Рис. 2. Объединить и преобразовать данные

Если у вас нет уверенности, что все файлы из папки следует объединять, лучше добавить еще один шаг в сценарий. Вместо Объединить –> Объединить и преобразовать данные кликните Преобразовать данные. После этого в редакторе Power Query откроется папка загрузки. Здесь можно применять фильтры. Например, если в папке имеются другие типы документов, можно отфильтровать файлы по расширению xlsx. Затем можно объединить файлы, кликнув кнопку Объединить файлы, расположенную в заголовке столбца Content.

Рис. 3. Управление файлами при объединении

Не важно, каким путем вы пошли: кликнув Объединить –> Объединить и преобразовать данные или через фильтрацию и кнопку Объединить файлы, у вас откроется окно Объединить файлы. Выберите лист Sheet1, кликните Ok.

Рис. 4. Объединить файлы

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

В окне редактора Power Query столбец Source.Name содержит имена файлов из папки. Из этих имен можно извлечь важный контекст – год. Например, так: замените «C03E03 — » пустой строкой, затем замените «.xlsx» пустой строкой. Чтобы выполнить замену выделите столбец Source.Name и пройдите Преобразование –> Замена значений –> Замена значений. Или разделите столбец Source.Name разделителем «- «, а затем «.» Удалите лишние столбцы. Переименуйте столбец «Year». Загрузите три объединеных файлах на лист Excel.

Если теперь вы добавите файл C03E03 — 2018.xlsx в папку, то вам будет достаточно в книге Excel с запросом, использующим импорт из папки, кликнуть Обновить, и данные из нового файла будут добавлены. В какой-то момент, выполняя запрос выше в редакторе PQ был создан ряд вспомогательных запросов. Мы изучим их смысл позже.

Добавление листов из книги

Иногда сходные таблицы сохраняются на разных листах в одной книги Excel. Сведем все рабочие листы в одну таблицу, сохраняя контекст года выпуска для каждого продукта. Загрузите файл C03E04. Откройте новую книгу Excel. Пройдите Данные –> Получить данные –> Из файла –> Из книги. Выберите файл C03E04.xlsx, кликните Импорт.

В окне Навигатор выберите строку, где находится значок папки (в нашем примере, C03E04.xlsx). Не выбирайте определенные листы в окне Навигатор. Если это сделать, то появление новых рабочих листов в рабочей книге потребует изменить запрос. Вместо этого выбирается вся книга. Кликните Преобразовать данные. Откроется редактор Power Query

Рис. 5. Три листа в одном запросе

Переименуйте запрос в Products. Вы видите таблицу с рабочими листами в отдельных строках. Фактическое содержание каждого листа инкапсулировано в столбце Data. Перед тем как комбинировать рабочие листы, оставьте только столбцы Name и Date.

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

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

Рис. 6. Объединенная таблица

Пройдите Преобразование –> Использовать первую строку в качестве заголовков. Кликните фильтр в заголовке столбца Name. Снимите галочку с Name. Проверьте что в районе строки 73 исчезла строка заголовка второго листа. Измените заголовок столбца 2015 на Year. Загрузите объединенную таблицу в книгу Excel.

Протестируем решение, добавив в исходный файл C03E04.xlsx лист с данными за 2018 г. Для этого просто продублируйте лист 2017 и переименуйте его в 2018.

Вы уже заметили, что, пока активирован редактор Power Query, нельзя получить доступ к другим рабочим книгам. Если при работающем редакторе Power Query вам всё же нужно поработать в Excel, запустите новый экземпляр Excel с помощью панели задач или меню Пуск. Новый экземпляр Excel не будет блокироваться текущим окном редактора Power Query.

Обновите запрос Products в вашей рабочей книге Excel и удостоверьтесь, что товары, продублированные из рабочего листа 2018, теперь добавляются со значением 2018 в качестве даты выпуска. Это работает!

Усложним задачу. Продублируйте лист 2015, переименуйте его в 2014 и поместите первым в книге. Теперь обновление завершится ошибкой:

Рис. 7. Ошибка обновления

Чтобы быстро устранить эту ошибку, откройте редактор Power Query, выберите запрос Products и выберите в панели Примененные столбцы шаг Изменение типа. В строке формул отобразится: