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

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

Как суммировать данные с разных листов книг Excel

Формулы могут работать с ячейками из других листов. Вам просто нужно указать перед ссылкой на ячейку имя листа и восклицательный знак. Например, следующая формула добавляет 12 к значению в ячейке С1 , которая находится на листе Лист2: =Лист2!С1+12 .

Что делать, если вам нужно вычислить сумму всех значений в С1, начиная с листа Лист2 и заканчивая листом Лист6? Следующая формула решает эту задачу: =СУММ(Лист2:Лист6!C1) . В данном случае двоеточие разделяет название первого и последнего листов.

Для создания такой формулы выполните приведенные ниже действия.

  1. Активизируйте ячейку, которая будет содержать формулу, и наберите =СУММ( .
  2. Щелкните на вкладке первого листа (в нашем случае это Лист2) и выберите ячейку С1 .
  3. Нажмите Shift и щелкните на вкладке последнего листа (в нашем случае это Лист6).
  4. Нажмите Enter, и формула будет введена в ячейку.

В шаге 2 вы можете выбрать диапазон, а не одну-единствениую ячейку. Например, эта формула возвращает сумму C1:F12 для всех листов от Лист2 до Лист6: =СУММ(Лист2:Лист6!С1:Р12) .

Теперь я покажу вам интересный прием, который узнал при прочтении новостных конференций Excel и на обучение в Минске. Если вы хотите просуммировать одну и ту же ячейку со всех листов, кроме текущего, просто введите формулу наподобие этой: =СУММ(‘*’!C1) . Звездочка служит подстановочным символом, который интерпретируется как «все листы, кроме этого одного». Когда вы нажмете клавишу Enter после ввода этой формулы, Excel преобразует формулу, чтобы она использовала фактические имена листов. Это работает, даже если активный лист находится где-нибудь в середине, между другими листами. Например, если книга состоит из шести листов и вы введете приведенную выше формулу в ячейку листа Лист3, Excel создаст следующую формулу: =СУММ(Лист1:Лист2!С1;Лист4:Лист6!C1) .

Но это еще не все. Введите следующую формулу для нахождения суммы ячеек С1 во всех листах, которые начинаются со слова Регион: =СУММ(‘Регион*»!C1) . Excel может преобразовать данную формулу во что-то наподобие этого: =СУММ(Регион1:Регион4!C1) .

Вы также можете использовать подстановочный знак ? — он указывает на любой отдельный символ. Например, при вводе следующей формулы Excel создаст формулу, которая просуммирует значения, начиная с листа Лист1 и заканчивая листом Лист9 (названия, которые содержат одну цифру): =СУММ(‘Лист?’!C1). Этот прием не ограничивается функцией СУММ. Он работает и с другими функциями, такими как СРЗНАЧ, МИН, МАКС.

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

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

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

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

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

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

Уважаемы гуру VBA помогите новичку не провалить задание.
Дело в том, что с разных отделов ( около 40) мне шлют заполненную таблицу Excel, универсальную для всех.
Разница лишь в цифрах, которые туда заносит каждый отдел исходя из своих показателей. Мне нужно создать сводную, одну таблицу, которая бы суммировала значения всех отделов. Неизменными остаются -шапка таблицы и столбец с названием показателей. Какой макрос применить в этом случае? Спасибо!
( таблица в приложении)

Лучшие ответы ( 2 )
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:

Собрать данные из разных книг в одну
Добрый день. Прошу помощи в написании макроса, который собирал бы данные из нескольких книг в.

Как перенести или скопировать определенные данные со всех книг в одну макросом
Здравствуйте. Не знаю как выполнить задачу. Есть несколько открытых книг с листами( книга 2, книга.

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

190 / 59 / 20
Регистрация: 16.07.2013
Сообщений: 234

ЦитатаСообщение от selenti Посмотреть сообщение

Какой макрос применить в этом случае?

Макрос обхода всех файлов в одной папке, копирование информации в новый эксель с последующим составлением свода.

Регистрация: 25.07.2016
Сообщений: 8

ЦитатаСообщение от Michael99 Посмотреть сообщение

Макрос обхода всех файлов в одной папке, копирование информации в новый эксель с последующим составлением свода.

ЦитатаСообщение от Michael99 Посмотреть сообщение

Макрос обхода всех файлов в одной папке, копирование информации в новый эксель с последующим составлением свода.

Michael99, а как это технически сделать? Я в этом совсем темнота еще
655 / 247 / 89
Регистрация: 28.10.2015
Сообщений: 524

Лучший ответ

Сообщение было отмечено selenti как решение

Решение

Регистрация: 25.07.2016
Сообщений: 8

Да, так это и выглядит.
В папке более 50 Книг под названием «филиал по________», с одним листом в каждой,под названием «Свод_раздел1″и расширением (.xls).
Пустая сводная таблица может быть как вне папки, так и внутри нее.
Вчера всю ночь просидела над формированием книги, копируя туда все листы с более чем 50 книг, чтобы потом применить функцию суммы. Это было очень долго ( т.к офис стоял 2010), потом пришлось переустановить на 2016-й и заработало. Но эта процедура меня ждет каждый месяц и я уже с содраганием смотрю в август месяц.

Добавлено через 4 минуты
Кнопка «Собрать» не сработала((

Добавлено через 8 минут
Прошу прощения, это я с утра(после бессонной ночи ) трою. Работает! Спасибо Вам огромное.

655 / 247 / 89
Регистрация: 28.10.2015
Сообщений: 524

Лучший ответ

Сообщение было отмечено selenti как решение

Решение

ЦитатаСообщение от selenti Посмотреть сообщение

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

Предоставленный мною макрос собирает данные из файлов в указанной папки. Если сама сводная лежит там же, где и искомые данные, то код надо допилить немного, иначе книга попытается открыть сама себя и затем закрыть сама себя.
Вот, держите вариант для случая «итоговый отчет лежит в той же папке, где и искомые данные»
В данном случае код обходит книгу, в которой он написан (итоговый отчет), но открывает все остальные, которые лежат в той же папке.
+ всё ускорено временным отключением системных сообщений (application.displayalerts = false)
+ во время работы макрос пишет в окно отладки (immediate window) информацию об открываемых файлах и файле, который был пропущен.

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

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

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

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

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

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

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

Кнопка

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

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

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

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

    Кнопка

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

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

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

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

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

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

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

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

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

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

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

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

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