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

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

  • автор:

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

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

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

Консолидация по расположению

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

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

Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.

Кнопка

Перейдите в раздел >Консолидация данных.

Консолидация по категории

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

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

Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.

Кнопка

Перейдите в раздел >Консолидация данных.

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

Импорт данных из папки с несколькими файлами (Power Query)

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

Общие сведения об объединении файлов папок

Примечание. В этом разделе показано, как объединить файлы из папки. Вы также можете объединять файлы, хранящиеся в SharePoint, Хранилище BLOB-объектов Azure и Azure Data Lake Storage. Процесс аналогичен.

Подготовка

  • Убедитесь, что все файлы, которые требуется объединить, содержатся в выделенной папке без лишних файлов. В противном случае все файлы в папке и все выбираемые вложенные папки включаются в объединенные данные.
  • Каждый файл должен иметь одинаковую схему с согласованными заголовками столбцов, типами данных и количеством столбцов. Столбцы не должны находиться в том же порядке, в котором сопоставление выполняется по именам столбцов.
  • По возможности избегайте несвязанных объектов данных для источников данных, которые могут содержать несколько объектов данных, таких как JSON-файл, книга Excel или база данных Access.

Импорт из текстовых, CSV-файлов или XML-файлов

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

  1. Выберите Данные >получить > данныхиз файла >из папки. Откроется диалоговое окно Обзор .
  2. Найдите папку, содержащую файлы, которые требуется объединить.
  3. Список файлов в папке появится в диалоговом окне Путь к папке <>. Убедитесь, что перечислены все нужные файлы.

Пример диалогового окна импорта текста

Result (Результат)

Power Query автоматически создает запросы для консолидации данных из каждого файла на листе. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.

Импорт из JSON

  1. Выберите Данные >получить > данныхиз файла >из папки. Откроется диалоговое окно Обзор.
  2. Найдите папку, содержащую файлы, которые требуется объединить.
  3. Список файлов в папке появится в диалоговом окне Путь к папке <>. Убедитесь, что перечислены все нужные файлы.
  4. Выберите одну из команд в нижней части диалогового окна, например Объединить >Объединить & Преобразование. В разделе Обо всех этих командах рассматриваются дополнительные команды.

Значок развертывания столбца

Появится Редактор Power Query.
Столбец Значение — это структурированный столбец List . Щелкните значок Развернуть

, а затем выберите Развернуть до новых строк.

Развертывание списка JSON

Значок развертывания столбца

Столбец Значение теперь является структурированным столбцом Запись . Щелкните значок Развернуть

. Откроется раскрывающееся диалоговое окно.

Развертывание записи JSON

Result (Результат)

Power Query автоматически создает запросы для консолидации данных из каждого файла на листе. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.

Импорт из Excel или Access

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

  1. Выберите Данные >получить > данныхиз файла >из папки. Откроется диалоговое окно Обзор.
  2. Найдите папку, содержащую файлы, которые требуется объединить.
  3. Список файлов в папке появится в диалоговом окне Путь к папке <>. Убедитесь, что перечислены все нужные файлы.
  4. Выберите одну из команд в нижней части диалогового окна, например Объединить >Объединить & Загрузить. В разделе Обо всех этих командах рассматриваются дополнительные команды.
  5. В диалоговом окне Объединение файлов выполните следующие действия:
    • В поле Пример файла выберите файл, который будет использоваться в качестве примера данных, используемых для создания запросов. Вы не можете выбрать объект или выбрать только один объект. Но вы не можете выбрать более одного.
    • Если у вас много объектов, используйте поле Поиск , чтобы найти объект, или параметры отображения вместе с кнопкой Обновить , чтобы отфильтровать список.
    • Установите или снимите флажок Пропускать файлы с ошибками в нижней части диалогового окна.
  6. Нажмите кнопку ОК.

Result (Результат)

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

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

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

  1. Выберите Данные >Получить данные >из файла >из папки. Откроется диалоговое окно Обзор .
  2. Найдите папку, содержащую файлы, которые требуется объединить, и нажмите кнопку Открыть.
  3. Список всех файлов в папке и вложенных папках появится в диалоговом окне . Убедитесь, что перечислены все нужные файлы.
  4. Выберите Преобразовать данные в нижней части экрана. Откроется Редактор Power Query, в котором отображаются все файлы в папке и все вложенные папки.
  5. Чтобы выбрать нужные файлы, отфильтруйте столбцы, например Расширение или Путь к папке.
  6. Чтобы объединить файлы в одну таблицу, выберите столбец Содержимое , содержащий каждый двоичный файл (обычно первый столбец), а затем выберите Главная >Объединить файлы. Откроется диалоговое окно Объединение файлов .
  7. Power Query анализирует пример файла, по умолчанию первый файл в списке, чтобы использовать правильный соединитель и определить соответствующие столбцы.

Result (Результат)

Power Query автоматически создает запросы для консолидации данных из каждого файла в лист. Созданные шаги запроса и столбцы зависят от выбранной команды. Дополнительные сведения см. в разделе Обо всех этих запросах.

Обо всех этих командах

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

  • Объединение и преобразование данных Чтобы объединить все файлы с запросом, а затем запустить Редактор Power Query, выберите Объединить >Объединить и преобразовать данные.
  • Объединение и загрузка Чтобы открыть диалоговое окно Пример файла, создайте запрос, а затем загрузите на лист, выберите Объединить >Объединить и Загрузить.
  • Объединение и загрузка в Чтобы открыть диалоговое окно Пример файла, создайте запрос, а затем откройте диалоговое окно Импорт , выберите Объединить >Объединить и Загрузить в.
  • Нагрузки Чтобы создать запрос с одним шагом, а затем загрузить на лист, выберите Загрузить >Загрузить.
  • Загрузка в Чтобы создать запрос с одним шагом, а затем открыть диалоговое окно Импорт , выберите Загрузить >Загрузить в.
  • Преобразование данныхЧтобы создать запрос с одним шагом, а затем запустить Редактор Power Query, выберите Преобразовать данные.

Обо всех этих запросах

Однако вы объединяете файлы, несколько вспомогательных запросов создаются в области Запросы в группе «Вспомогательные запросы».

Список запросов, созданных на панели

  • Power Query создает запрос «Образец файла» на основе примера запроса.
  • Запрос функции Transform File использует запрос Parameter1, чтобы указать каждый файл (или двоичный файл) в качестве входных данных для запроса Sample File. Этот запрос также создает столбец Содержимое , содержащий содержимое файла, и автоматически расширяет структурированный столбец Запись , чтобы добавить данные столбца в результаты. Запросы «Файл преобразования» и «Пример файла» связаны, поэтому изменения в запросе «Пример файла» отражаются в запросе «Файл преобразования».
  • Запрос, содержащий окончательные результаты, находится в группе «Другие запросы». По умолчанию он называется в честь папки, из которую вы импортировали файлы.

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

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

Примечание: Поддерживаются файлы Excel и Access, текстовые файлы, а также файлы в форматах CSV, JSON и XML.

  1. Перейдите на вкладку Power Query, а затем выберите Пункт Из файла >Из папки.

Диалоговое окно объединения двоичных файлов, в котором показаны файлы, доступные для объединения

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

Нажмите кнопку

Убедившись в том, что все нужные файлы присутствуют в списке, нажмите в столбце Содержимое кнопку Объединить двоичные данные.

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

Будет выполнен анализ каждого файла и определен правильный формат, например текстовый, Excel или JSON. В этом примере отображается список листов из первой книги Excel. Выберите нужный лист и нажмите кнопку ОК.

Диалоговое окно для предварительного просмотра результатов объединения. Нажмите

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

Известные проблемы

При загрузке процедуры объединения двоичных файлов в Excel может появиться такое сообщение об ошибке:

Сообщение об ошибке при объединении двоичных файлов. Это известная ошибка, которая будет устранена.

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

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

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

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

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

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

Для ведения бюджета применяется таблица такого вида:
Исходная таблица
Сама таблица преобразована заранее в так называемую «умную» таблицу: выделяем таблицу -вкладка Вставка (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 таблице в любой ячейке щелкнуть правой кнопкой мыши и выбрать Обновить:
Обновить
Все файлы в папке будут просмотрены, преобразованы и просуммированы.

Статья помогла? Поделись ссылкой с друзьями!

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

Добрых суток.
Господа и возможно дамы, прошу вашего совета и помощи.
Имеется несколько книг Excel, а в них данные (формы/таблицы) которые нужно просуммировать.
Суммировать нужно по критериям основной (сводной таблицы). Для это есть столбцы с кодировкой и наименованием.
Подскажите пожалуйста формулу которая сможет суммировать данные из разных книг по критерию/критериям/признакам.
Заранее благодарен всем за все .

З.Ы.:В примере одна книга с примерами таблиц для суммирования из разных книг.

Прикрепленные файлы

  • Vopros.xlsx (19.72 КБ)

Пользователь
Сообщений: 14900 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
02.08.2013 15:28:27

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

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.

Пользователь
Сообщений: 8 Регистрация: 01.01.1970
02.08.2013 15:43:06

Цитата
Adonis пишет:
Суммировать нужно по критериям основной (сводной таблицы). Для это есть столбцы с кодировкой и наименованием.

Критерием по которому нужно суммировать можно выбрать код строки (код3 и/или код2 и/или код1
) и/или Название строки/столбца.

Пользователь
Сообщений: 14900 Регистрация: 15.09.2012
Профессиональная разработка приложений для MS Office
02.08.2013 16:15:12

Круто. Читайте в статье. Там про критерии все расписано и действует. Мой же вопрос был достаточно конкретным: как понять из каких книг суммировать, раз это надо делать с несколькими книгами? Но раз не хотите отвечать — пытать не буду.
Могу лишь посоветовать воспользоваться последним кодом из статьи, указав для критерия wsAnotherWB имя книги из ячейки. К примеру суммирование будете производить в столбце С. Тогда в С1 пишите имя первой книги: Книга1.xls, в С2 второй — Книга2.xls, в С3 — третьей — Книга3.xls.
И в функции просто ссылаетесь на эту ячейку и протягиваете по столбцам. А далее просто суммируете результаты функции.

И объясните — зачем Вы процитировали себя же? Я справшивал совершенно иное. Если хотите сами на свои вопросы отвечать — то можно это делать и не на форуме.

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *