Работать со сводными таблицами Excel приходится в разных сферах. Можно быстро обрабатывать большие объемы информации, сравнивать, группировать данные. Это значительно облегчает труд менеджеров, продавцов, руководителей, маркетологов, социологов и т.д.
Сводные таблицы позволяют быстро сформировать различные отчеты по одним и тем же данным. Кроме того, эти отчеты можно гибко настраивать, изменять, обновлять и детализировать.
У нас есть тренировочная таблица с данными:
Каждая строка дает нам исчерпывающую информацию об одной сделке:
Если это огромная сеть магазинов и продажи идут, то в течение одного квартала размер таблицы станет ужасающим. Проанализировать данные в сотне строк будет очень сложно. А на составление отчета уйдет не один день. В такой ситуации сводная таблица просто необходима.
Создам отчет с помощью мастера сводных таблиц. В новых версиях Excel он почему-то спрятано глубоко в настройках:
Теперь инструмент находится в панели быстрого доступа, а значит всегда под рукой.
Готовый отчет можно форматировать, изменять.
Это можно сделать вручную и автоматически.
Настройка автоматического обновления при изменении данных:
Теперь каждый раз при открытии файла с измененными данными будет происходить автоматическое обновление сводной таблицы.
Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал.
Группировка по дате в сводной таблице Excel:
Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:
Если фамилия продавцов для анализа деятельности сети магазинов не важна, можем сформировать отчет с поквартальной прибылью.
Чтобы убрать результаты группировки, необходимо щелкнуть по ячейке с данными правой кнопкой мыши и нажать разгруппировать. Либо выбрать данный параметр в меню «Структура».
У нас есть сводный отчет такого вида:
Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.
Как в сводной таблице сделать итоги сверху:
Уже нет той перегруженности, которая затрудняла восприятие информации.
Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:
Получим отчет без дополнительных сумм:
Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов.
Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».
По умолчанию в сводную таблицу помещается абсолютно вся информация из того столбца, который мы добавляем в отчет.
В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.
Жмем ОК – сводная таблица меняется.
Наиболее универсальный отчёт в Process Insight - это аналог сводной таблицы Excel, называемый “Сводный отчёт”:
Отчёт позволяет выбирать любые бюджетные и фактические проводки, фильтровать их по заданным критериям и группировать по тем или иным аналитическим разрезам. С его помощью можно сравнивать между собой сценарии бюджетов, формировать отчёты план-факт-отклонение в разрезах статей бюджетов, контрагентов, проектов, бизнес-направлений, периодов, расчётных счетов и других имеющихся в Системе аналитик.
Параметры отчёта:
Для отчёта должны быть указаны аналитические разрезы, которые будут размещены в строках (по вертикали) и в столбцах (по горизонтали). Для традиционного отчёта (на рисунке) это соответственно статьи бюджета и периоды.
В следующей строке формы отмечаются галочками данные, которые должны быть собраны в отчёте: только план, только факт, план-фак, план-факт-отклонение.
Если отметить галочкой “Добавить итоги”, то последним столбцом таблицы или последней строкой будет “Итого”, содержащее сумму по всем строкам или столбцам.
На следующей строке стоят по умолчанию отмеченным опции “Не выводить пустые столбцы” и “Не выводить пустые строки”. Если по тем или иным статьям бюджета за выбранный отчётный период не было движения, то при отмеченных галочками опциях Process Insight не будет выводить в отчёт такие строки. То же относится и к периодам. В отчёт попадут только строки и столбцы, содержащие данные.
В разделе “Ограничения выборки” можно указать период, за который нужно сформировать отчёт, а также выбрать ограничения по другим аналитикам.
В разделе “Свойства отчёта” указываются название отчёта, опция округления до рублей, тип отчёта и параметры конвертирования.
С помощью инструмента “Сводный отчёт” можно формировать как отчёты по движению денежных средств, так отчёты о прибылях и убытках.
Отчёт можно сопроводить комментариями и указать время его хранения на сервере (см. следующий раздел).
Пример отчёта:
Полученный отчёт может быть экспортирован в Excel (для этого нужно нажать на иконку Excel в верхнем левом углу).
Справа от иконки Excel находится кнопка обновления отчёта. С её помощью можно запросить с сервера сформированный отчёт заново.
Ещё правее находится кнопка перехода в полноэкранный режим с фиксированными заголовками строк и столбцов. Этот режим удобен для работы с отчётами, содержащими большое количество строк или столбцов.
Первый интерфейс сводных таблиц, называемых также сводными отчеты, был включен в состав Excel еще в 1993м году (версии Excel 5.0). Несмотря на множество полезных функциональных возможностей, он практически не применяется в работе большинством пользователей Excel. Даже опытные пользователи зачастую подразумевают под термином «сводный отчет» нечто построенное с помощью сложных формул. Попробуем популяризировать использование сводных таблиц в повседневной работе экономистов. В статье обсуждаются теоретические основы создания сводных отчетов, даются практические рекомендации по их использованию, а также приводится пример доступа к данным на основе нескольких таблиц.
Большинство экономистов слышали термины «многомерные данные», «виртуальный куб», «OLAP-технологии» и т.п. Но при детальном разговоре обычно выясняется, что почти все не очень представляют, о чем идет речь. То есть люди подразумевают нечто сложное и обычно не имеющее отношение к их повседневной деятельности. На самом деле это не так.
Можно с уверенностью утверждать, что экономисты практически постоянно сталкиваются с многомерными данными, но пытаются представить их в предопределенном виде с помощью электронных таблиц. Под многомерностью здесь подразумевается возможность ввода, просмотра или анализа одной и той же информации с изменением внешнего вида, применением различных группировок и сортировок данных. Например, план продаж можно проанализировать по следующим критериям:
Каждый из приведенных критериев в терминах многомерного анализа данных называется «измерением». Можно сказать, что измерение характеризует информацию по определенному набору значений. Специальным типом измерения многомерной информации являются «данные». В нашем примере данными плана продаж могут являться:
Теоретически данные могут также являться стандартным измерением многомерной информации (например, можно сгруппировать данные по цене продажи), но обычно все-таки данные являются специальным типом значений.
Таким образом, можно сказать, что в практической работе экономисты используются два типа информации: многомерные данные (фактические и плановые числа, имеющие множество признаков) и справочники (характеристики или измерения данных).
Аббревиатура OLAP (online analytical processing) в дословном переводе звучит как «аналитическая обработка в реальном времени». Определение не очень конкретное, под него можно подвести практически любой отчет любого программного продукта. По смыслу OLAP подразумевает технологию работы со специальными отчетами, включая программное обеспечение, для получения и анализа как раз многомерных структурированных данных. Одним из популярных программных продуктов, реализующих OLAP-технологии, является SQL Server Analysis Server. Некоторые даже ошибочно считают его единственным представителем программной реализации данной концепции.
«Виртуальный куб» (многомерный куб, OLAP-куб) - это специальный термин, предложенный некоторыми поставщиками специализированного программного обеспечения. OLAP-системы обычно готовят и хранят данные в собственных структурах, а специальные интерфейсы анализа (например, сводные отчеты Excel) обращаются к данным этих виртуальных кубов. При этом использование подобного выделенного хранилища совсем не обязательно для обработки многомерной информации. В общем случае, виртуальный куб – это и есть массив специально оптимизированных многомерных данных, который используется для создания сводных отчетов. Он может быть получен как через специализированные программные средства, так и через простой доступ к таблицам базы данных или любой другой источник, например к таблице Excel.
«Сводный отчет» (сводная таблица, Pivot Table) - это пользовательский интерфейс для отображения многомерных данных. С помощью данного интерфейса можно группировать, сортировать, фильтровать и менять расположение данных с целью получения различных аналитических выборок. Обновление отчета производится простыми средствами пользовательского интерфейса, данные автоматически агрегируются по заданным правилам, при этом не требуется дополнительный или повторный ввод какой-либо информации. Интерфейс сводных таблиц Excel является, пожалуй, самым популярным программным продуктом для работы с многомерными данными. Он поддерживает в качестве источника данных как внешние источники данных (OLAP-кубам и реляционным базам данных), так и внутренние диапазоны электронных таблиц. Начиная с версии 2000 (9.0), Excel поддерживает также графическую форму отображения многомерных данных – сводная диаграмма (Pivot Chart).
Реализованный в Excel интерфейс сводных таблиц позволяет расположить измерения многомерных данных в области рабочего листа. Для простоты можно представлять себе сводную таблицу, как отчет, лежащий сверху диапазона ячеек (на самом деле есть определенная привязка форматов ячеек к полям сводной таблицы). Сводная таблица Excel имеет четыре области отображения информации: фильтр, столбцы, строки и данные. Измерения данных именуются полями сводной таблицы . Эти поля имеют собственные свойства и формат отображения.
Еще раз хочется обратить внимание, что сводная таблица Excel предназначена исключительно для анализа данных без возможности редактирования информации. Ближе по смыслу было бы повсеместное употребление термина «сводный отчет» (Pivot Report), и именно так этот интерфейс и назывался до 2000го года. Но почему-то в последующих версиях разработчики от него отказались.
По своему определению OLAP-технология, в принципе, не подразумевает возможность изменения исходных данных при работе с отчетами. Тем не менее, на рынке сформировался целый класс программных систем, реализующих возможности как анализа, так и непосредственного редактирования данных в многомерных таблицах. В основном такие системы ориентированы на решение задач бюджетирования.
Используя встроенные средства автоматизации Excel, можно решить множество нестандартных задач. Пример реализации редактирования для сводных таблиц Excel на основе данных рабочего листа можно найти на нашем сайте.
Подойдем к практическому применению сводных таблиц. Попробуем проанализировать данные о продажах в различных направлениях. Файл pivottableexample.xls состоит из нескольких листов. Лист Пример содержит основную информацию о продажах за определенный период. Для простоты примера будем анализировать единственный числовой показатель – объем продажи в кг. Имеются следующие ключевые измерения данных: продукция, покупатель и перевозчик (транспортная компания). Кроме того, имеются несколько дополнительных измерений данных, являющихся признаками продукта: тип, бренд, категория, поставщик, а также покупателя: тип. Эти данные собраны на листе Справочники. На практике подобных измерений может быть гораздо больше.
Лист Пример содержит стандартное средство анализа данных – автофильтр. Глядя на пример заполнения таблицы, очевидно, что нормальному анализу поддаются данные о продажах по датам (они расположены по столбцам). Кроме того, используя автофильтр можно попробовать просуммировать данные по сочетаниям одного или нескольких ключевых критериев. Совершенно отсутствует информация о брендах, категориях и типах. Нет возможности сгруппировать данные с автоматическим суммированием по определенному ключу (например, по покупателям). Кроме того, набор дат зафиксирован, и просмотреть итоговую информацию за определенный период, например, 3 дня, автоматическими средствами не удастся.
Вообще, наличие предопределенного расположения даты в данном примере – главный недостаток таблицы. Расположив даты по столбцам, мы как бы предопределили измерение этой таблицы, таким образом, лишив себя возможности использовать анализ с помощью сводных таблиц.
Во-первых, надо избавиться от этого недостатка – т.е. убрать предопределенное расположение одного из измерений исходных данных. Пример корректной таблицы – лист Продажи .
Таблица имеет форму журнала ввода информации. Здесь дата является равноправным измерением данных. Также следует заметить, что для последующего анализа в сводных таблицах совершенно безразлично относительное положение строк друг относительно друга (иначе говоря, сортировка). Этими свойствами обладают записи в реляционных базах данных. Именно на анализ больших объемов баз данных ориентирован в первую очередь интерфейс сводных таблиц. Поэтому необходимо придерживаться этих правил и при работе с источником данных в виде диапазонов ячеек. При этом никто не запрещает использовать в работе интерфейсные средства Excel – сводные таблицы анализируют только данные, а форматирование, фильтры, группировки и сортировки исходных ячеек могут быть произвольными.
Теоретически на данных листа Продажи уже можно проводить анализ в трех измерениях: товары, покупатели и перевозчики. Данные о свойствах продукции и покупателей на данном листе отсутствуют, что, соответственно, не позволит показать их и в сводной таблице. В нормальном режиме создания сводной таблицы для исходных данных Excel не позволяет связывать данные нескольких таблиц по определенным полям. Обойти это ограничение можно программными средствами – см. пример-дополнение к данной статье на нашем сайте. Чтобы не прибегать к программным методам обработки информации (тем более, что они и не универсальны), следует добавить дополнительные характеристики непосредственно в форму ввода журнала – см. лист ПродажиАнализ.
Применение функций VLOOKUP позволяет легко дополнить исходные данные недостающими характеристиками. Теперь, применяя автофильтр, можно анализировать данные в различных измерениях. Но остается нерешенной проблема группировок. Например, отследить сумму только по брендам на определенные даты достаточно проблематично. Если ограничиваться формулами Excel, то нужно строить дополнительные выборки, используя функцию SUMIF.
Теперь посмотрим какие возможности дает интерфейс сводных таблиц. На листе СводАнализ построено несколько отчетов на основе диапазона ячеек с данными листа ПродажиАнализ .
Первая таблица анализа построена через интерфейс Excel 2007 Лента \ Вставка \ Сводная таблица (в Excel 2000-2003 меню Данные \ Сводная таблица ).
Вторая и третья таблицы созданы через копирование и последующую настройку. Источник данных для всех таблиц один и тот же. Можете это проверить, изменив исходные данные, затем надо обновить данные сводных отчетов.
С нашей точки зрения, преимущества в наглядности информации очевидны. Вы можете менять местами фильтры, столбцы и строки и, скрывать определенные группы значений любых измерений, применять ручное перетаскивание и автоматическую сортировку.
Кроме непосредственного отображения данных, имеется большой набор возможностей по отображению внешнего вида сводных таблиц. Лишние данные можно скрывать, используя фильтры. Для единичного элемента или поля проще пользоваться пунктом контекстного меню Удалить (в версии 2000-2003 Скрыть ).
Задавать отображение других элементов сводной таблицы также желательно не через форматирование ячейки, а через настройку поля или элемента сводной таблицы. Для этого необходимо подвести указатель мыши к нужному элементу, дождаться появления специальной формы курсора (в виде стрелки), затем через одинарный клик выделить выбранный элемент. После выделения можно изменять вид через ленту, контекстное меню или вызывать стандартный диалог формата ячейки:
Кроме того, в Excel 2007 появилось множество предопределенных стилей отображения сводной таблицы:
Обратите внимание, что в диаграмме активны управляющие фильтры и области перетаскивания.
Как уже отмечалось, пожалуй, наибольший эффект от применения сводных таблиц можно получить при доступе к данным внешних источников – OLAP-кубам и запросам к базам данных. Такие источники обычно хранят большие объемы информации, а также имеют предопределенную реляционную структуру, что позволяет легко определить измерения многомерных данных (поля сводной таблицы).
Excel поддерживает множество типов источников внешних данных:
Наибольшего эффекта от использования внешних источников информации можно добиться, применяя средства автоматизации (программы VBA) как для получения данных, так и для их предварительной обработки в сводных таблицах.
Взаимосвязанные предприятия представляют сводную финансовую отчетность (СФО), отражая показатели деятельности группы организаций и структурных отделений. Пользователями информации выступают контролирующие и статистические органы, учредители и другие заинтересованные лица. В процессе формирования отчетности суммируются однотипные показатели группы структурных образований. Данные используются для получения информации о финансовом и имущественном состоянии группы компаний. В статье расскажем, как составляется сводная финансовая отчетность, в какие сроки и кем.
СФО формируется по данным структурных отделений или группы компаний, объединенных одним собственником. Другим основанием составления сводных форм является потребность получения данных для контроля подведомственных организаций вышестоящим ведомством, распорядителем бюджета. СФО применяется для группы связанных компаний, находящихся под прямым или косвенным управлением головной компании. Отчетность составляют лица, имеющие в структуре:
Компании, составляющие СФО согласно отечественного законодательства, могут также сформировать данные по МСФО по желанию или в соответствии с нормативными требованиями. Особенности использования положений МСФО:
При составлении сводной отчетности в форме КФО в рамках МСФО не представляются данные о компаниях, находящихся под временным контролем, при отсутствии признака существенности или при наличии иных условий, установленных стандартами. Данные представляются как финансовые вложения.
Консолидированная финансовая отчетность (КФО) иногда отождествляется со сводными формами. Виды отчетности имеют особенности при формировании. Основным отличием КФО является использование МСФО при составлении отчетности, для СФО применяются требования международного и(или) российского законодательства.
| Условия составления | КФО | СФО |
| Лица, по результатам деятельности которых составляется отчетность | Самостоятельно зарегистрированные юридические лица | Группа компаний или структурных отделений с единым органом управления (собственником) |
| Составитель | Каждая организация в составе группы | Головное предприятие |
| Принцип группировки данных | Формирование данных по каждому предприятию с исключением ряда показателей согласно МСФО с учетом определенных алгоритмов | Суммирование показателей построчно за исключением ряда дублирующих данных, отражающих взаимную деятельность предприятий |
| Назначение отчетности | Формирование показателей, характеризующих взаимную деятельность между группой компаний | Простой свод данных статей нескольких зависимых структурных единиц |
В состав КФО входят балансы формы 1 и 2, в СФО – полный перечень бухгалтерской отчетности. Предприятиям предоставляется возможность использовать самостоятельно разработанные бланки на основании стандартных форм. При сохранении структуры бланков в формы дополняются строками, позволяющими раскрыть специфику деятельности и показатели контролируемых структур.
Подробные условия формирования данных раскрывают в пояснительной записке, важном документе для предоставления дополнительной информации об изменениях, тенденциях ведения деятельности и причинах отраженных сдвигов показателей.
Составление СФО производится головной организацией на основании собственных данных и величин, представленных подчиненными подразделениями. Процедура подготовки статей проходит ряд этапов:
Отчетность, сформированная по результатам конкретного периода, представляется руководителю основного предприятия для подписания и участникам для утверждения.
В составе форм СФО формируются бухгалтерская отчетность. При получении выходных сведений в СФО не учитываются:
При составлении форм сводной отчетности необходимо наличие вертикальной структуры управления. Формирование данных осуществляется, начиная от нижних звеньев с последующей группировкой высшим органом. Показатели СФО обязаны соответствовать ряду требований.
| Условия отражения данных | Пояснения к требованиям |
| Полнота внесения сведений | В значениях статей указываются все активы, пассивы, статьи доходов и расходов предприятий структуры |
| Единство условий учетной политики или приведение данных по принципам основной организации | Используется однотипность методов оценки статей имущества, обязательств, доходов, расходов и прочих величин |
| Единство периода и даты отчетности | Отчетность составляющих компаний или отделений группы формируется за один период и на одну дату |
| Единство валюты отчетности и единиц измерения | Данные подразделений, полученные в иностранной валюте, подлежат пересчету в российский рубль |
При составлении СФО, составленной согласно российского бухгалтерского законодательства, используют стандартный порядок отражения информации. Методы объединения данных зависят от степени влияния головной компании.
| Формы зависимости лиц | Уровень влияния головной организации | Метод объединения данных | Особенности метода |
| Дочернее общество | Полный контроль основной организации над деятельностью дочерней компании | Метод приобретения (полной консолидации) – суммирование значений по одноименным статьям с исключением взаимных расчетов | Потребность в четком определении структуры, достижение единства исходных оценок |
| Зависимое общество | Частично участвующее общество | Метод консолидирования доли, определяемой соглашением | Учитываются данные о капитале, фактическое участие в деятельности может отсутствовать |
| Совместное общество | Совместное влияние | Метод пропорционального консолидирования, размер которого зависит от части вложения | Потребность в наличии договора с четким распределением контролируемых операций |
Метод, выбранный предприятием, остается неизменным в течение последующих периодов для обеспечения сопоставимости показателей.
Ранее нормативным актом по созданию СФО служили Методические рекомендации, утвержденные приказом МФ РФ от 30.12.1996 № 112. В настоящее время законодательная норма утратила силу. Порядок представления СФО совпадает с условиями подачи обычных форм отчетности предприятий, руководствуясь в составлении данных ПБУ 4/99. При изменениях, внесенных в стандартные бланки, обоснованность корректировок раскрываются в пояснениях к отчетности. После изменения бланков формы используются в последующих периодах.
СФО, сформированная согласно условий российского законодательства, представляется в сроки, определенные для подачи бухгалтерской отчетности. СФО, формируемая в виде КФО, представляется в течение 120 дней по окончании года до проведения общего собрания участников. Пропуск срока подачи СФО влечет наказание, налагаемое на руководителя основного предприятия согласно 15.6 КоАП в виде штрафа в размере от 300 до 500 рублей.
Назначение анализа отчетности является определение финансового состояния компании и отдельных его показателей, выбор которых обусловлен потребителем информации. Для получения данных используются финансовые коэффициенты, например, ликвидности, оборачиваемости капитала и прочие. В процессе проверки проводится анализ основного предприятия и каждого участника группы.
При анализе определяется верность значений статей, расчета удельного веса средств материнской и дочерних компаний, долей в имуществе и прочих соотношений участников группы. Важной составляющей анализа является выявление динамики изменения отдельных показателей по отчетным периодам методом сравнительного анализа.
Вопрос №1. Как формируются данные СФО при отличии положений учетной политики головного и дочернего органа предприятия?
При подготовке СФО применяются условия финансовой политики, установленные головной организацией. Различия в положениях обязывают произвести корректировку внутренних актов дочерней структуры.
Вопрос №2. В каком порядке группируются данные СФО при наличии в организации дочерних и зависимых структур предприятия?
При формировании данных СФО в первую очередь объединяются данные головной и дочерней структуры, затем присоединяются значения по операциям зависимого общества.
Вопрос №3. Как определить, что отчетность является сводными формами?
В название бланка отчетности вносится отметка «сводный».
Вопрос №4. Имеются ли отличия методов анализа СФО от обычной отчетности?
Методы анализа показателей сводной финансовой отчетности не имеют отличий от изучения данных стандартных балансов.
