Для чего применяется операция консолидация данных

Для чего применяется операция консолидация данных

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

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

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

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

· Выбрать место расположения итогового списка (это может быть тот же лист, где находится исходная ведомость или отдельный лист в книге, или новая книга Excel ).

· Вызвать функцию консолидации данных: Меню-Данные-Консолидация.

· Заполнить диалоговое окно, которое представлено на рис. 9.

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

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

Вопросы для самопроверки

1. Как объяснить термин – консолидация по расположению?

2. Могут ли отсутствовать данные в таблицах, которые объединяют?

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

4. Как правильно использовать строку в окне Консолидация под наименованием Ссылка?

О консолидации данных

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

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

Использование трехмерных формул

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

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

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

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

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

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

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

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

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

Другие способы объединения данных

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

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

Консолидация данных

1. Изучите данные и определите, требуется ли их консолидация с помощью трехмерных ссылок в формулах, по положению или по категории. Формулы. Трехмерные ссылки можно использовать в формулах для любого типа и расположения данных. Рекомендуется пользоваться этим способом. Положение. Если планируется объединение данных, находящихся в одинаковых ячейках разных диапазонов, можно выполнить их консолидацию по положению. Категория. Если при наличии нескольких диапазонов с разными макетами планируется объединять данные по строкам или столбцам с одинаковыми подписями, можно выполнить их консолидацию по категории.
2. Выполните одно из следующих действий. Консолидируйте данные с использованием трехмерных ссылок или формул

1. На листе консолидации скопируйте или задайте надписи для данных консолидации.
2. Укажите ячейку, в которую следует поместить данные консолидации.
3. Введите формулу, включающую ссылки на исходные ячейки каждого листа, содержащего данные, для которых будет выполняться консолидация. Например, чтобы объединить данные в ячейке B3 листов с Лист2 по Лист7, введите =СУММ(Лист2:Лист7!B3). Если данные, которые требуется объединить, находятся в разных ячейках на разных листах, введите формулу в формате=СУММ(Лист3!B4; Лист4!A7; Лист5!C5). Чтобы ввести ссылку (например Лист3!B4), не используя клавиши на клавиатуре, введите формулу до того места, где требуется вставить ссылку, а затем укажите на листе нужную ячейку.

Консолидируйте данные по положению или категории

1. Настройте данные для консолидации. Инструкции

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

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

Изменение итоговой таблицы консолидации данных

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

1. Щелкните поле Ссылка.
2. Для консолидации щелкните вкладку листа первого диапазона.
3. Введите имя диапазона или выберите диапазон из списка.
4. Нажмите кнопку Добавить.

Измените размер или формы диапазона данных

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

Удалите диапазон данных и консолидации

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

Автоматически обновите консолидацию

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

Установите флажок Создавать связи с исходными данными.

Итоговые функции для анализа данных

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

Функция	Результат
Сумма	Сумма чисел. Эта операция используется по умолчанию для подведения итогов по числовым полям.
Количество	Количество данных. Эта операция используется по умолчанию для подведения итогов по нечисловым полям. Операция «Кол-во значений» работает так же, как и функция СЧЁТЗ.
Среднее	Среднее чисел.
Максимум	Максимальное число.
Минимум	Минимальное число.
Произведение	Произведение чисел.
Кол-во чисел	Количество данных, являющихся числами. Операция «Кол-во чисел» работает так же, как и функция СЧЁТ.
Несмещенное отклонение	Несмещенная оценка стандартного отклонения для генеральной совокупности, где выборка является подмножеством генеральной совокупности.
Смещенное отклонение	Смещенная оценка стандартного отклонения генеральной совокупности по выборке данных.
Несмещенная дисперсия	Несмещенная оценка дисперсии для генеральной совокупности, где выборка является подмножеством генеральной совокупности.
Смещенная дисперсия	Смещенная оценка дисперсии генеральной совокупности по выборке данных.

Консолидация данных в Excel

Консолидация данных – опция в Excel, которая позволяет объединять данные из разных таблиц в одну, а также объединять листы, расположенные в одном или разных файлах, в один. Чем же так полезна консолидация для интернет-маркетолога?

Разберем два наглядных примера.

Пример №1

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

Специальный отчет в Google Analytics

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

«Дубли» ключевых слов в статистике

Это связано с тем, что статистика выгружена из двух рекламных систем (Яндекс.Директ и Google Ads), и в Google Рекламе для ключевых слов используется модификатор широкого соответствия (знак «+»).

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

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

Суммирование данных вручную

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

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

Удаляем + в ключевых словах перед консолидацией

— переходим на соседний лист (так удобнее);

— выделяем ячейку, в которую хотим вставить данные;

— переходим в Данные — Консолидация

В открывшемся окне нас интересуют следующие настройки:

• Функция – Сумма (поскольку хотим суммировать данные);
• Ссылка – выбираем весь диапазон данных на соседнем листе;
• Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Нажимаем ОК. Если все сделали верно, то на текущем листе получите результат в виде объединенных (консолидированных) данных:

Итоговая таблица после консолидации

Таблица наша также уменьшилась. До консолидации было 252 строки, после объединения данных их стало 212:

ДО и ПОСЛЕ консолидации

Видео консолидации примера №1:

Консолидация данных в Excel

Пример №2

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

Да, можно просто выгрузить данные из рекламных кабинетов Яндекс.Директ и Google Ads за общий период. Но что делать, когда данные по показам, кликам, расходам и конверсиям привязаны к бизнес-показателям, которых нет в интерфейсах, но которые завязаны на контекстной рекламе? А если в каждый из этих месяцев работали разные рекламные кампании (в один месяц – 10, в другой – 5 и т.д.)? Опять ручной процесс сведения данных.

И для таких случаев пригодится Консолидация в Excel. Давайте сведем данные с помощью данной настройки на примере выгрузки интернет-магазина за 3 месяца в один отчет. Статистика по месяцам расположена на разных вкладках (сентябрь — ноябрь — декабрь).

Статистика по 3 месяцам на разных вкладках файла

Мы хотим свести данные в одну таблицу. Воспользуемся консолидацией.

В открывшемся окне нас интересуют следующие настройки:

• Функция – Сумма (поскольку хотим суммировать данные);
• Ссылка – выбираем весь диапазон данных на листах поочередно (с помощью кнопки Добавить);
• Использовать в качестве имен – ставим галочки для подписи верхней строки и значения левого столбца.

Галочка Создавать связи с исходными данными позволит производить пересчет консолидированного отчета автоматически при изменении данных в исходных файлах (в нашем примере – это листы сентябрь, октябрь, ноябрь). Нажимаем ОК.

Если все сделали правильно, то на текущем листе получите результат в виде объединенных (консолидированных) данных с трех таблиц в одной:

Слева появятся новые значки:

Связи с исходными данными

• 1 – свернуть все связи с исходными данными;
• 2 – развернуть все связи с исходными данными.

При нажатии на + раскроется соответствующая строка, в которой будут отображены данные по каждому из исходных листов.

Видео консолидации примера №2:

Консолидация данных в Excel

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

Понравился эксперимент с видео? Поставь 5.0 статье ->

Консолидация финансовых данных из нескольких организаций

Некоторые организации используют Business Central в нескольких бизнес-подразделениях или юридических лицах. Другие используют Business Central в дочерних компаниях, которые должны отчитываться перед головными организациями. Business Central предоставляет бухгалтерам инструменты для облегчения переноса операций главной книги из двух или более организаций (дочерних компаний) в консолидированную организацию.

Каждая организация, участвующая в консолидации, называется филиалом. Организация, в которой объединяются данные, называется консолидированной организацией.

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

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

Консолидация возможна следующими способами:

• Между организациями с разными планами счетов.
• Организации, которые используют различные финансовые годы и различные валюты.
• По полному объему или проценту финансовых данных организации.
• С использованием различных обменных курсов валют для отдельных счетов ГК.
• организации, работающие в других программах бухгалтерского учета и управления бизнесом.
• организации в разных средах Business Central.

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

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

Консолидировать данные

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

Тестирование данных перед консолидацией

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

Выполнение консолидации

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

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

1. Выполните вход в консолидированную организацию.
2. На странице Филиалы выберите действие Консолидировать.
3. Заполните обязательные поля.

Использование отчета «Консолидированный пробный баланс»

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

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

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

Исключение повторных транзакций

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

Исключение повторных транзакций:

1. Найдите транзакции, которые, возможно, нуждаются в корректировке, и введите строки финансового журнала для их исключения.
2. Запустите отчет Исключения консолидации ГК, чтобы помочь оценить влияние строк финансового журнала перед учетом.
3. Выполните учет корректирующих транзакций.

В отчете Исключения консолидации ГК отображается предварительный пробный баланс, в котором можно моделировать последствия исключения операций. В этом отчете сравниваются операции в консолидированной компании с исключениями, введенными в финансовый журнал.

Чтобы филиал можно было включить в отчет, вы должны настроить его на странице Филиалы. Не забудьте перевести переключатель Консолидировать в положение «вкл.».

Для каждого счета создается строка в соответствии со структурой плана счетов. Если все суммы в строке равны нулю, счет не показывается. Для каждого счета в отчете показывается следующая информация:

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

Экспорт и импорт консолидированных данных между базами данных

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

В этом разделе описывается ручной процесс с использованием файла.

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

Используйте этот же процесс для экспорта консолидированных данных, которые необходимо отправить в Dynamics 365 Finance. (Например, если филиал является дочерней организацией, а материнская организация использует Dynamics 365 Finance.)

После выполнения пакетного задания обрабатываются все записи в счетах Главной книги. Для каждой комбинации выбранных измерений и даты содержимое записей в полях Сумма суммируется и экспортируется. Обрабатывается следующая комбинация выбранных измерений и даты с тем же номером счета. Затем обрабатываются комбинации со следующим номером счета и так далее.

Экспортированные записи содержат следующие поля: Номер счета, Дата учета и Сумма. Если данные об измерениях также были экспортированы, то включаются коды измерений и значения измерений.

1. Для каждой экспортированной, если итог полей Сумма является дебетом, то номер счета, настроенный в поле Консол. дебет. счет ГК филиала, экспортируется в строку. Если итог относится к кредиту, соответствующее число в поле Консол. кредит. счет ГК экспортируется в строку.
2. Дата, использованная для каждой экспортированной строки, является конечной датой периода или, если передача осуществляется ежедневно, точной датой расчета.
3. Значение измерения, экспортированное для операции, станет значением измерения консолидированной организации, которая указана в поле Код консолидации для данного значения измерения. Если в поле Код консолидации для данного значения измерения ничего не введено, в строку экспортируется само значение измерения.
4. Кроме того, файлы XML содержат курсы валют для данного периода консолидации. Эти курсы находятся в отдельном разделе в начале файла.

Использование нашего API для автоматической передачи данных между средами

В Business Central предусмотрен API, который позволяет автоматизировать процесс обмена финансовыми данными между филиалами и консолидированной организацией. Этот API бесплатный, и настроить его достаточно просто. Он даже позволяет обмениваться данными между средами Business Central. Обмен данными между средами может стать актуальным, например, если филиалы находятся в разных географических регионах Azure. Подробнее об использовании API для автоматизации процесса консолидации см. в статье Настройка консолидации организаций.