Консолидация в экселе как сделать: Консолидация данных в Excel

Импорт и консолидация таблиц Excel через Power Query

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

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

Внести статичное имя в поле Имя на этот раз не получится. Поэтому заходим в Формулы → Определенные имена → Задать имя (или нажимаем Создать в Диспетчере имен), указываем название будущего динамического диапазона ДинамОтчет и внизу вместо ссылки записываем формулу:

=$A$2:ИНДЕКС($B:$B;ПОИСКПОЗ(99^99;$B:$B))

Ко всем ссылкам этой формулы Excel еще автоматически добавит название листа.

Смысл формулы следующий. Верхняя левая ячейка диапазона фиксируется ($A$2), а правая нижняя определяется формулой, которая возвращает адрес последней заполненной строки в столбце B.

Но не все так просто. Excel видит это имя лишь как формулу, а не диапазон. Как же его увидит Power Query? Делаем ход конем.

Создаем пустой запрос Power Query Данные → Получить и преобразовать данные → Получить данные → Из других источников → Пустой запрос. Открывается пустой запрос, где в строке формул нужно ввести:

= Excel.CurrentWorkbook()

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

Название запроса не подхватывается, поэтому придется изменить самостоятельно.

Чтобы извлечь содержимое объекта, в этой же строке правой кнопкой мыши кликаем по Table, далее выбираем Детализация.

Power Query разворачивает таблицу и даже делает некоторые шаги обработки: повышает заголовки и задает нужный формат для столбцов.

Теперь в запрос будут попадать новые строки, несмотря на то, что исходные данные не являются таблицей Excel.

Вот такие приемы импорта данных в Power Query из книги Excel. Самый распространенный из них – это импорт из таблицы Excel. Тем не менее, в случае необходимости можно прибегнуть к альтернативам, создав именованный или динамический именованный диапазон.

Консолидация данных из разных таблиц Excel

Одна из насущных задач, с которыми сталкиваются пользователи, – консолидация данных. Под консолидацией понимается объединение нескольких таблиц в одну. До появления Power Query это была довольно трудоемкая операция, особенно, если процесс требовал автоматизации. Хотя в эксель есть специальная команда Данные → Работа с данными → Консолидация, пользоваться ей не удобно. Мне, по крайней мере. Появление Power Query в корне изменило представление о том, как нужно объединять таблицы.

Рассмотрим пример. В некоторый файл каждый месяц вносится отчет о продажах в формате таблицы Excel. Каждая таблица при этом имеет соответствующее название: Январь_2018, Февраль_2018 и т.д. Необходимо объединить все таблицы книги в одну. Как бы скопировать и вставить одну под другой, создав при этом дополнительный столбец, указывающий, к какой таблице принадлежит конкретная строка. Задача не одноразовая, а с заделом на будущее, поэтому нужно предусмотреть появление в этом файле новых таблиц.

Процесс начинается с запуска пустого запроса: Данные → Получить и преобразовать данные → Создать запрос → Из других источников → Пустой запрос

Затем в строке формул вводим знакомую команду

= Excel.CurrentWorkbook()

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.

↓ Скачать файл с примером ↓

Поделиться в социальных сетях:

Консолидация бюджетов

Консолидация бюджетов

Содержание
Введение
Изменения в версии 2.6
Установка и запуск
Состав файла
Структура организации
Статьи затрат/доходов
Анализ
Данные
Формат бюджета подразделения
Данные во внешних файлах
Загрузка и обновление данных*
Заключение

Загрузки Магазин FAQ

Программа Финансы в Excel. Консолидация бюджетов (до версии 2.6 — Финансы в Excel. Бюджетирование) представляет собой рабочую книгу Excel, содержащую программный код для автоматизации процесса консолидации типовых бюджетов на крупных и средних предприятиях. Анализ и консолидация бюджетов производится с использованием сводных таблиц Excel. Исходные данные бюджетов подразделений могут храниться в различных файлах.

Введение

Задача консолидации и анализа бюджетов подразделений предприятия на разных уровнях, несмотря на кажущуюся простоту, на практике вызывает множество проблем, связанных как с организационной деятельностью, так и с отсутствием специальных программных средств. В подавляющем большинстве случаев для работы используется Excel – в электронных таблицах готовятся и хранятся исходные бюджеты подразделений, в других файлах эти данные собираются в общий консолидированный отчет. При этом используются формулы и ссылки на внешние файлы, которые могут быть обновлены по запросу пользователей.

Недостатки подобной схемы работы общеизвестны:

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

Подобные аргументы о проблемах использования Excel очень популярны при обсуждении специализированных программ для бюджетирования, обычно основанных на OLAP-технологиях (названия приводить не будем, чтобы не делать рекламу; желающие могут найти описания через поисковые системы). Однако эти программные продукты, несмотря на активное продвижение, не являются панацеей и к тому же требуют больших финансовых вложений для внедрения и поддержки.

Одной из задач программы «Финансы в Excel. Консолидация бюджетов» является устранение описанных недостатков без привлечения внешних программных средств, сохранив при этом все возможности вычислительного аппарата Excel.

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

Изменения в версии 2.6

• Изменено название программы. Старое название Финансы в Excel. Бюджетирование
• Начиная с версии 2.6, не поддерживается старый формат рабочей книги XLS. Файл программы сохранен в формате XLSX/XLSM и работает в Excel, начиная с версии 2007 (2007-2016). Программа в формате XLS доступны только в версии 2.0, и может быть приобретена только по специальному запросу.
• Обновлен внешний вид рабочих книг в соответствии с дизайном последних версий Microsoft Office.
• Элементы управления Checkbox в справочнике Струкутра заменены на стандартные поля. Изменения значения возможно либо через двойной клик на ячейке, либо через ввод значения 1 — да, 0 — нет.

Установка и запуск

В разделе Загрузки доступен для скачивания и установки файл демонстрационной версии:

• setup_demo. exe

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

Если на компьютере установелены программы Финансы в Excel более ранних версий, то желательно их деинсталлировать стандартными средствами Windows, либо просто отключить надстройку ExcelFin.AddIn версий 1.55 и ниже.

 

По умолчанию демонстрационный файл устанавливается в папку в каталог документов текущего пользователя Windows User\Мои документы\ExcelFin (User – имя пользователя). Кроме основного выполняемого файла, программа установки создает нескольо папок и файлов с примерами условных бюджетов подразделений. Рабочие файлы могут располагаться на любых дисковых ресурсах.

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

Windows \ Все программы \ Финансы в Excel \ Консолидация бюджетов.

После успешного запуска программы откроется Excel-файл программы. Для нормальной работы программы необходимо подключить макросы Excel.

 

Состав файла

Программа представляет собой файл формата Excel с набором макросов. Обращаем внимание, что без подключенных макросов анализ проводить невозможно – данные обновляться не будут.

 

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

Файл включает следующие обязательные страницы:

Структура – справочник иерархической организационной структуры.

Статьи – справочник статей бюджета. Статья идентифицируется и сортируется в отчете по коду.

Бюджет – основной лист консолидации данных и анализа.

Данные – служебный лист с данными сводной таблицы. Может быть скрыт.

Кроме перечисленных страниц, основной файл может включать одну или несколько страниц с исходными бюджетами подразделений. Такие страницы могут находиться также во внешних файлах – правила поиска и загрузки исходных данных описаны на листе Структура. В демонстрационном примере в основной файл включены 2 страницы с исходными данными (

бФиндир и бГендир), еще 3 бюджета подразделений включены в дистрибутивный архив в виде отдельных файлов.

Справочник предназначен для описания иерархической организационной структуры предприятия. Настраивается на странице Структура.

 

Уровни подчинения задаются через ввод данных в ячейки со второго по шестой столбец (подзаголовки столбцов: 1, 2, 3, 4, 5). В каждой строке этого диапазона может быть только одна ячейка с названием, остальные должны оставаться пустыми. На верхнем уровне консолидируется предприятие целиком. Название организации вводится в ячейку, помеченную желтым цветом фона.

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

Кроме описания отделов и уровней подчинения, в справочнике указываются ссылки на файлы и листы – источники данных бюджетов. Путь к файлу задается в столбце 11 (Файл). Возможны следующие варианты.

• Пустое значение. Данные хранятся в основном программном файле.
• Относительный путь к файлу от программного файла.
• Абсолютный путь к файлу, расположенному на общем ресурсе (сервере) или на локальном диске компьютера.

 

Поле Лист (столбец 12) хранит название страницы в файле с данными бюджета подразделения.

Если не задан Лист, то строка игнорируется при обновлении данных.

В представленной версии основного файла программы заготовлено 50 строк для описания структуры организации. Добавлять бюджеты можно через копирование и вставку строк целиком. Если требуется больше 5ти уровней иерархии подчинения, то потребуется исправление кода программы.

Статьи затрат/доходов

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

 

Обязательными полями справочника являются:

Код – идентификатор статьи. Может быть числовыми или символьным, но важно иметь в виду, что в отчете статьи будут отсортированы по возрастанию данного кода.

Наименование – произвольное наименование статьи бюджета. Настоятельно рекомендуется использовать уникальные наименования статей, хотя это и не является обязательным условиям (идентификация производится по коду). Кроме хранения названия, ячейка влияет на формат отображения статьи в отчете. Этот формат определяется следующими параметрами:

• шрифт (размер, жирность, курсив, подчеркивание)
• цвет текста
• цвет фона
• уровень отступа

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

Анализ

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

Для выбора бюджета используется выпадающий список в верхней части окна:

 

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

Страница Бюджет не предполагает редактирования или проведения вычислений. Сводная таблица является преднастроенной, изменение ее формата или вида может привести к ошибкам при обновлении данных.

Подробный анализ (drill-down) можно провести через двойной клик на ячейке в области данных. Это стандартное средство интерфейса Excel.

Наряду с названиями статей и суммой по периодам отображается дополнительное поле «комментарий», если он был задан через примечание к ячейке в исходных данных.

Данные

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

Формат бюджета подразделения

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

 

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

Для поиска начала таблицы на листе анализируются первые 10 строк и 10 столбцов от начала страницы. Признаком верхнего левого угла бюджетной таблицы является ячейка со словом «код» (регистр не важен).

Справа в той же строке располагаются заголовки периодов. Важно, что периоды должны определяться датами, а не строками. Можно использовать формат ячейки для отображения даты в виде месяца и года (формат числа «МММ ГГ»).

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

Конец бюджетной таблицы определяется через поиск пустых ячеек в столбце кода. При наличии 10ти пустых ячеек подряд считается, что достигнут конец таблицы. Этот параметр можно изменить в коде программы.

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

Данные во внешних файлах

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

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

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

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

Загрузка и обновление данных*

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

Загрузку данных можно производить, находясь на листе Структура или на листе Бюджет. В первом случае доступна выборочная загрузка данных только по указанным бюджетам подразделений (через установку или снятие флажка в соответствующей строке). Операция производится по нажатию кнопки √ в заголовке окна. Для больших файлов загрузка данных может занять длительное время.

 

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

При загрузке данных из внешних файлов нет ограничения на их состояние (открыт или закрыт), так как программа использует доступ только для чтения.

Заключение

Программа распространяется с открытым исходным кодом без паролей. В коде можно изменить параметры анализа исходных бюджетов или, если требуется, подкорректировать номера столбцов в справочниках. Ссылки представлены в коде VBA в виде публичных свойств объектов рабочих листов, либо в виде глобальных констант.

 

* В демонстрационной версии программы загрузка данных не поддерживается.

---

Загрузки Магазин FAQ

Смотри также

» Бюджетные таблицы и совместный доступ к данным

Тема бюджетирования в последние годы является едва ли не самой популярной в области автоматизации финансового менеджмента. Мы не…

Читать далее…

» Суммирование по кодам

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

Читать далее…

Консолидация данных в Google Sheets help

Эта страница поможет вам выполнить 3 простых шага надстройки Consolidate Sheets. К концу все ваши таблицы будут не только объединены, но и рассчитаны в один сводный отчет Google Sheets. И у вас будет этот отчет либо в виде значений, либо в виде формулы, которая автоматически будет отражать все будущие изменения, внесенные в исходные листы.

• Видео: как объединить данные из нескольких листов Google
• Прежде чем начать
• Как использовать надстройку Consolidate Sheets
  • Начать объединение листов
  • Шаг 1. Выберите листы для объединения
  • Шаг 2. Выберите параметры консолидации
  • Шаг 3: Выберите дополнительные параметры
  • Получить результат
    • Объединение нескольких файлов с формулой
      • Ограничения стандартных функций, используемых в формуле
• Связанные страницы

Видео: как объединить данные из нескольких листов Google

Перед тем, как начать

При выборе листов для объединения помните, что электронная таблица с результатом не должна превышать ограничение Google Sheets на 10 миллионов ячеек. Если это произойдет, надстройка сообщит вам об этом и не будет объединять ваши данные.

Как использовать надстройку Consolidate Sheets

Запустить Consolidate Sheets

Запустите инструмент из меню Google Sheets: Extensions > Consolidate Sheets > Start :

Совет. Дополнение также можно найти в Объединить и объединить Группа электроинструментов:

Шаг 1: Выберите листы для объединения

На этом шаге вам нужно определить листы, которые вы хотите объединить и рассчитать:

1. Отметьте флажок Sheets выбрать сразу все листы из древовидного списка и сложить их все.
2. Чтобы объединить еще больше листов Google, нажмите Добавить файлы с Диска .

   Совет. Для быстрого поиска файлов доступен быстрый поиск. Просто введите часть имени файла в поле Найдите поле в окне Импорт файлов с диска и нажмите Найти . Consolidate Sheets проверит весь ваш Диск и покажет вам список всех частичных и полных совпадений имен.

   Если вы не уверены, где находятся найденные электронные таблицы, просто наведите на них курсор мыши, и вы увидите подсказку с полным путем:
   

   Совет. Надстройка достаточно умна, чтобы позволить вам открывать несколько файлов с Диска одновременно. Держите нажатой клавишу Ctrl и щелкните каждый файл, который вам нужен. Как только они будут выбраны (вы увидите, что они станут голубыми), нажмите Добавить :
   
   Чтобы отменить выбор при выборе файлов, щелкните электронную таблицу еще раз.

3. Если вы передумаете в отношении какой-либо таблицы, выберите ее и нажмите кнопку Исключить . Это удалит файл из списка консолидации.
4. Для каждого выбранного листа можно указать диапазон для суммирования. Нажмите Все данные справа от имени листа (в столбце Диапазон ) и выполните одно из следующих действий:
   • Введите диапазон с клавиатуры.
   • Щелкните значок Выбрать диапазон и вручную выделите нужные ячейки на листе.
   • Щелкните значок Выберите диапазон , выберите любую ячейку в нужной таблице и нажмите Автоматический выбор . Инструмент определит используемый диапазон — ячейки с данными до первого пустого столбца и строки — автоматически:
     

Когда нужные листы выбраны и диапазоны определены, нажмите Следующий .

Шаг 2: Выберите параметры консолидации

Здесь вы должны решить, какие параметры консолидации подходят вам лучше всего:

1. Выберите одну из 11 общеизвестных функций из раскрывающегося меню для объединения и расчета ваших таблиц: СУММ, СЧЁТ, СРЕДНИЙ, МАКС, МИН, ПРОДУКТ, ЧИСЛА СЧЁТА, СТАНДОТКЛОН, СТАНДОТКЛОН, VAR, VARP .
2. Выберите способ суммирования данных:
   • Объединить по метке — это вариант, когда в ваших таблицах одинаковые заголовки расположены в другом порядке:
     • Чтобы учитывать заголовки в верхней строке, установите флажок Использовать метку заголовка :
       
     • Чтобы добавить записи на основе заголовков в первом столбце, отметьте галочкой Использовать метку левого столбца :
       
     • Если верхняя строка так же важна, как и левая колонка, вы можете выбрать оба варианта для консолидации как по столбцам, так и по строкам:
       

     Примечание. Имейте в виду, что модуль игнорирует пустые ячейки. Если вы выберете консолидацию по меткам, столбцы и/или строки с пустыми метками будут игнорироваться. Если все заголовки отсутствуют, модуль в результате вернет одну пустую ячейку.

   • Используйте параметр Консолидировать по позиции , если ваши таблицы имеют идентичную структуру и метки не имеют большого значения:
     

Щелкните Далее , чтобы выбрать место для консолидированных данных.

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

Надстройка позволяет персонализировать способ консолидации данных:

Выберите способ переноса данных с листов

Используйте формулу для объединения листов . Этот будет извлекать данные, используя специально созданную формулу. Формула будет автоматически обновлять сводную таблицу при изменении записей в исходных таблицах, а также при появлении или удалении данных из исходных таблиц.

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

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

Совет. На данный момент это бета-функция: мы с нетерпением ждем ваших отзывов и очень ценим их!

Решите, куда поместить результат

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

Нажмите Объединить , чтобы объединить и сложить ячейки в Google Таблицах.

Получить результат

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

Совет. Если вы решили поместить результат в новую электронную таблицу на шаге 3, надстройка предоставит вам ссылку для немедленного открытия нового файла:

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

Когда вы объединяете данные из разных файлов и выбираете получение результата в виде формулы, надстройка использует стандартный IMPORTRANGE Google Таблиц для объединения записей.

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

Наш аддон предлагает как это сделать правильно:

1. Всякий раз, когда в формуле результата используется IMPORTRANGE, вы увидите результирующее сообщение, сообщающее вам, как связать функцию с другими файлами:
   
2. Для вашего удобства модуль создает два листа:
   • Консолидированные данные с вашей формулой результата.
   • Подключите файлы к IMPORTRANGE , где вы должны предоставить все разрешения.

   Наконечник. Если вы поместите результат в новую электронную таблицу, нажмите кнопку Открыть новую электронную таблицу 9.0047 для перехода прямо на вкладку Подключить файлы . Сначала запустите Consolidate Sheets, чтобы включить наши пользовательские функции, а затем выполните следующие действия.

   Если вы поместите результат в текущий файл, лист Connect files станет активным автоматически.

3. Вкладка Подключить файлы всегда будет содержать следующее:
   • описание соединения листов
   • напоминание о запуске Consolidate Sheets (в этом нет необходимости, если только вы не поместите результат в новую электронную таблицу, где также должны быть включены наши собственные пользовательские функции)
   • список всех электронных таблиц, к которым необходимо получить доступ по формуле
   • статус доступа

   Просто наведите указатель мыши на ошибки в столбце Статус доступа и нажмите каждую появившуюся кнопку Разрешить доступ :
   

4. Как только вы это сделаете, Статус доступа будет говорить Подключено и ячейки станут зелеными:
   

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

Ограничения функций, используемых в формуле

Наряду с нашими собственными пользовательскими функциями, полученная формула использует пару стандартных: ЗАПРОС и ИМПОРТРАНГ. Вот некоторые вещи, которые вы должны знать о результате, возвращаемом формулой:

1. В настоящее время формулу можно создать, только если на шаге 2 вы выбрали суммирование (СУММ) или подсчет (СЧЁТ) данных. Другие агрегатные функции не поддерживаются.
2. Формула всегда проверяет, содержат ли консолидируемые столбцы всех листов числовые значения в первых двух строках (исключая строку заголовка). Если в первых двух строках какой-либо таблицы вообще нет числовых записей, формула будет построена, но ничего не вернет в результат.
3. Если вы поместите формулу в новую электронную таблицу, наши пользовательские функции (CONSIDER_HEADERS_CONS, AGGREGATE_CONS, COLUMN_LIST_CONS) не начнут работать, пока вы не запустите Consolidate Sheets один раз . Поскольку этот файл создан заново, таким образом вы активируете в нем наши пользовательские функции.

   Сделайте это перед предоставлением доступа IMPORTRANGE, и вам также не придется перезагружать электронную таблицу.

4. Формулы не будут работать без нашего дополнения. Независимо от того, используете ли вы инструмент для создания формул или копий общих файлов с формулами, у вас должно быть установлено дополнение. В противном случае нам просто нечем будет поддерживать наши пользовательские функции.

Связанные страницы

• Объединить данные из нескольких листов в один
• Объединить данные из двух электронных таблиц Google
• Vlookup несколько совпадений на основе нескольких критериев

Как автоматизировать консолидацию в Excel (с помощью простых шагов)

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

---

Скачать практическую рабочую тетрадь

Шаги по автоматизации консолидации данных в Excel

📌 Шаг 1: Подготовьте исходные данные

📌 Шаг 2. Используйте функцию консолидации

📌 Шаг 3: выберите функцию и добавьте ссылки

📌 Шаг 4: Автоматизируйте консолидацию

📌 Шаг 5: Наблюдайте за результатами

То, что нужно запомнить

Заключение

Статьи по Теме

Загрузить рабочую тетрадь

Вы можете скачать учебную тетрадь, нажав кнопку загрузки ниже.

---

Действия по автоматизации консолидации данных в Excel

Выполните следующие действия, чтобы автоматизировать консолидацию данных в Excel .

📌 Шаг 1: Подготовьте исходные данные

• Предположим, у вас есть следующий набор данных. Здесь рабочие листы Y2021 и Y2022 содержат продажи за первый квартал соответствующих лет.
• Если верхняя строка и левый столбец одинаковы на всех листах, значения будут добавлены в одни и те же ячейки. В противном случае данные будут добавлены в соседние строки или столбцы.
• Здесь листы находятся в одной книге (автоматическая консолидация). Перед объединением данных лучше открыть все электронные таблицы, если вы хотите сделать это из разных файлов.

Подробнее: Как объединить два листа в один в Excel (3 полезных метода)

---

📌 Шаг 2. Используйте функцию консолидации

• Теперь выберите ячейку, в которую вы хотите получить консолидированные данные. Затем выберите Консолидировать в группе Data Tools на вкладке Data , как показано на рисунке ниже.

Подробнее: Как консолидировать данные в Excel из нескольких книг (2 метода)

---

Аналогичные показания

• Функция консолидации текстовых данных в Excel (с 3 примерами)
• Как удалить консолидацию в Excel (2 удобных метода)
• [Исправлено]: ссылка на консолидацию недействительна в Excel (с быстрым исправлением)

---

📌 Шаг 3: Выберите функцию и добавьте ссылки

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

• Затем перейдите к первым исходным данным (Y2021) и выберите диапазон данных, которые вы хотите консолидировать. Далее нажмите на стрелку вниз.

• Теперь вы должны нажать кнопку Добавить , чтобы добавить ссылку. Затем снова нажмите стрелку вверх, чтобы добавить больше ссылок.

• После этого переходим ко второму источнику данных (Y2022). Затем выберите диапазон данных, которые вы хотите консолидировать, как в предыдущем методе. Далее нажмите на стрелку вниз.

Подробнее: Как консолидировать данные из нескольких строк в Excel (4 быстрых метода)

---

📌 Шаг 4: Автоматизация консолидации

• После этого нажмите кнопку Добавить , как и раньше, чтобы добавить эту ссылку. Вы увидите список добавленных ссылок в диалоговом окне Consolidate .
• Затем необходимо установить флажки для меток Верхний ряд и Левый столбец . Вы можете оставить их неотмеченными, если в вашем наборе данных нет меток.
• Теперь самое главное для автоматизации консолидации — проверить «Создать ссылки на исходные данные». В противном случае консолидированные данные не будут обновляться при изменении исходных данных.
• После этого нажмите кнопку ОК.

---

📌 Шаг 5: Наблюдайте за результатами

• Наконец, вы увидите следующие результаты. Обратите внимание, что некоторые группы были созданы автоматически, так как слева есть несколько знаков + .

• Разверните группы, чтобы увидеть исходные данные и их расположение. Теперь любые изменения, внесенные в исходные данные, будут автоматически обновлять консолидированные данные. Потому что вы видите, что консолидированные данные напрямую связаны с исходными данными.