Первый интерфейс сводных таблиц, называемых также сводными отчеты, был включен в состав Excel еще в 1993м году (версии Excel 5.0). Несмотря на множество полезных функциональных возможностей, он практически не применяется в работе большинством пользователей Excel. Даже опытные пользователи зачастую подразумевают под термином «сводный отчет» нечто построенное с помощью сложных формул. Попробуем популяризировать использование сводных таблиц в повседневной работе экономистов. В статье обсуждаются теоретические основы создания сводных отчетов, даются практические рекомендации по их использованию, а также приводится пример доступа к данным на основе нескольких таблиц.
Термины многомерного анализа данных
Большинство экономистов слышали термины «многомерные данные», «виртуальный куб», «OLAP-технологии» и т.п. Но при детальном разговоре обычно выясняется, что почти все не очень представляют, о чем идет речь. То есть люди подразумевают нечто сложное и обычно не имеющее отношение к их повседневной деятельности. На самом деле это не так.
Многомерные данные, измерения
Можно с уверенностью утверждать, что экономисты практически постоянно сталкиваются с многомерными данными, но пытаются представить их в предопределенном виде с помощью электронных таблиц. Под многомерностью здесь подразумевается возможность ввода, просмотра или анализа одной и той же информации с изменением внешнего вида, применением различных группировок и сортировок данных. Например, план продаж можно проанализировать по следующим критериям:
- виды или группы товаров;
- бренды или категории товаров;
- периоды (месяц, квартал, год);
- покупатели или группы покупателей;
- регионы продаж
- и т.п.
Каждый из приведенных критериев в терминах многомерного анализа данных называется «измерением». Можно сказать, что измерение характеризует информацию по определенному набору значений. Специальным типом измерения многомерной информации являются «данные». В нашем примере данными плана продаж могут являться:
- объем продаж;
- цена продажи;
- индивидуальная скидка
- и т.п.
Теоретически данные могут также являться стандартным измерением многомерной информации (например, можно сгруппировать данные по цене продажи), но обычно все-таки данные являются специальным типом значений.
Таким образом, можно сказать, что в практической работе экономисты используются два типа информации: многомерные данные (фактические и плановые числа, имеющие множество признаков) и справочники (характеристики или измерения данных).
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) как для получения данных, так и для их предварительной обработки в сводных таблицах.
Работать со сводными таблицами Excel приходится в разных сферах. Можно быстро обрабатывать большие объемы информации, сравнивать, группировать данные. Это значительно облегчает труд менеджеров, продавцов, руководителей, маркетологов, социологов и т.д.
Сводные таблицы позволяют быстро сформировать различные отчеты по одним и тем же данным. Кроме того, эти отчеты можно гибко настраивать, изменять, обновлять и детализировать.
Создание отчета с помощью мастера сводных таблиц
У нас есть тренировочная таблица с данными:
Каждая строка дает нам исчерпывающую информацию об одной сделке:
- в каком магазине были продажи;
- какого товара и на какую сумму;
- кто из продавцов постарался;
- когда (число, месяц).
Если это огромная сеть магазинов и продажи идут, то в течение одного квартала размер таблицы станет ужасающим. Проанализировать данные в сотне строк будет очень сложно. А на составление отчета уйдет не один день. В такой ситуации сводная таблица просто необходима.
Создам отчет с помощью мастера сводных таблиц. В новых версиях Excel он почему-то спрятано глубоко в настройках:
- Выберите «Файл»-«Параметры»-«Панель быстрого доступа».
- В выпадающем списке левой колонки: «Выбрать команду из» укажите «Все команды».
- В левой колонке найдите по алфавитному порядку и выделите: «Мастер сводных таблиц и диаграмм». Нажмите на кнопку между колонками: «Добавить» чтобы инструмент переместился в правую колонку и нажмите ОК.
Теперь инструмент находится в панели быстрого доступа, а значит всегда под рукой.
Готовый отчет можно форматировать, изменять.
Как обновить данные в сводной таблице Excel?
Это можно сделать вручную и автоматически.
Настройка автоматического обновления при изменении данных:
- На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
- Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
- В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».
Теперь каждый раз при открытии файла с измененными данными будет происходить автоматическое обновление сводной таблицы.
Некоторые секреты форматирования
Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал.
Группировка по дате в сводной таблице Excel:
Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:
Если фамилия продавцов для анализа деятельности сети магазинов не важна, можем сформировать отчет с поквартальной прибылью.
Чтобы убрать результаты группировки, необходимо щелкнуть по ячейке с данными правой кнопкой мыши и нажать разгруппировать. Либо выбрать данный параметр в меню «Структура».
Работа с итогами
У нас есть сводный отчет такого вида:
Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.
Как в сводной таблице сделать итоги сверху:
Уже нет той перегруженности, которая затрудняла восприятие информации.
Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:
Получим отчет без дополнительных сумм:
Детализация информации
Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов.
Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».
По умолчанию в сводную таблицу помещается абсолютно вся информация из того столбца, который мы добавляем в отчет.
В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.
Жмем ОК – сводная таблица меняется.
Наиболее универсальный отчёт в Process Insight - это аналог сводной таблицы Excel, называемый “Сводный отчёт”:
Отчёт позволяет выбирать любые бюджетные и фактические проводки, фильтровать их по заданным критериям и группировать по тем или иным аналитическим разрезам. С его помощью можно сравнивать между собой сценарии бюджетов, формировать отчёты план-факт-отклонение в разрезах статей бюджетов, контрагентов, проектов, бизнес-направлений, периодов, расчётных счетов и других имеющихся в Системе аналитик.
Параметры отчёта:
Для отчёта должны быть указаны аналитические разрезы, которые будут размещены в строках (по вертикали) и в столбцах (по горизонтали). Для традиционного отчёта (на рисунке) это соответственно статьи бюджета и периоды.
В следующей строке формы отмечаются галочками данные, которые должны быть собраны в отчёте: только план, только факт, план-фак, план-факт-отклонение.
Если отметить галочкой “Добавить итоги”, то последним столбцом таблицы или последней строкой будет “Итого”, содержащее сумму по всем строкам или столбцам.
На следующей строке стоят по умолчанию отмеченным опции “Не выводить пустые столбцы” и “Не выводить пустые строки”. Если по тем или иным статьям бюджета за выбранный отчётный период не было движения, то при отмеченных галочками опциях Process Insight не будет выводить в отчёт такие строки. То же относится и к периодам. В отчёт попадут только строки и столбцы, содержащие данные.
В разделе “Ограничения выборки” можно указать период, за который нужно сформировать отчёт, а также выбрать ограничения по другим аналитикам.
В разделе “Свойства отчёта” указываются название отчёта, опция округления до рублей, тип отчёта и параметры конвертирования.
С помощью инструмента “Сводный отчёт” можно формировать как отчёты по движению денежных средств, так отчёты о прибылях и убытках.
Отчёт можно сопроводить комментариями и указать время его хранения на сервере (см. следующий раздел).
Пример отчёта:
Полученный отчёт может быть экспортирован в Excel (для этого нужно нажать на иконку Excel в верхнем левом углу).
Справа от иконки Excel находится кнопка обновления отчёта. С её помощью можно запросить с сервера сформированный отчёт заново.
Ещё правее находится кнопка перехода в полноэкранный режим с фиксированными заголовками строк и столбцов. Этот режим удобен для работы с отчётами, содержащими большое количество строк или столбцов.
Что такое сводная отчетность? Это определение должны знать все работники бухгалтерских организаций. В случае когда у организации существуют дочерние подразделения, кроме бухгалтерской отчетности собственной, существует необходимость в составлении сводной. Ей называется свод показателей всей группы объединенных между собой предприятий о финансовом состоянии на конкретную дату отчета и результатах ее общей финансовой деятельности за конкретный отчетный период. Такие отчеты выполняет головная фирма.
Главные понятия
Консолидацией называется объединение чего-либо по конкретным единым отличительным признакам. Соответственно, консолидированная бухгалтерская отчетность производится путем сложения одноименных отчетных статей материнской фирмы и всех существующих дочерних организаций.
Необходимость в этом появляется в процессе объединения сразу нескольких предприятий, являющихся изначально юридическими лицами, в одну группу, которая не будет иметь статус юридического лица. В ней должен осуществляться единый контроль над всеми объединенными элементами.
Отличия финансовых отчетностей
В ходе процесса развития крупного российского бизнеса появилась необходимость в выводе многих компаний на более широкий международный уровень и привлечении новых инвестиций. При этом возникла потребность в получении доступной и полноценной информации о финансовой деятельности объединившихся компаний. Ответственность за ее ведение несет головная фирма, в международной деятельности она была названа консолидированной финансовой отчетностью.
Сводная и консолидированная отчетности различаются тем, что целью консолидированных отчетов является предоставление информации, характеризующей данное объединение объектов, юридически самостоятельных, но работающих как одно целое. Документация создается для того, чтобы показать заинтересованным организациям - кредиторам, инвесторам, профсоюзам, местным органам власти, поставщикам итоги финансовой деятельности объединенных субъектов - ее масштабы, сделать ее доступной, повысить уровень доверия к ней.
Головная фирма ведет контроль над своими дочерними субъектами. Она наделена правом определять направление, всю финансовую политику данных подразделений для получения максимальной прибыли от их деятельности. Консолидированные финансовые отчеты производятся для создания полной картины о финансовой деятельности объединенной группы организаций в целом и не могут заменить отдельно взятые бухгалтерские отчеты этих субъектов.
В сводной бухгалтерской отчетности сливаются пункты бухгалтерских отчетов сразу нескольких разных предприятий, которыми владеет один собственник. В другом случае она выполняется для статистики, например, органов местной власти, министерств. Отличается сводная тем, что при ее выполнении происходит построчное сложение отчетных показателей дочерних фирм или подведомственных организаций.
Консолидированная отчетность предоставляет отчетную информацию о работе фирм, являющихся самостоятельными юридически, но ведущих совместный контроль над какой-либо деятельностью или имуществом. Этот отчет составляет головная компания.
Сводная бухгалтерская отчетность составляется министерствами и другими органами власти и строится на основании бухгалтерских отчетов организаций, находящихся у них в подчинении. Пользователи учетной информации испытывают необходимость в получении консолидированных финансовых отчетов, сущность которых состоит в том, чтобы объединить между собой отчеты экономически взаимодействующих предприятий.
Сводная консолидированная бухгалтерская отчетность призвана сформировать достоверную и качественную информацию, чтобы на ее основе пользователи смогли принимать взвешенные решения о дальнейшей деятельности.
Задача консолидированного финансового отчета - отразить общее представление о деятельности группы, а не просто суммировать отчетные показатели всех входящих в нее субъектов. Основное его назначение - котировка акций на фондовой бирже.
Таким образом, современный российский бухгалтерский учет выделяет следующие три разновидности объединенных отчетов:
- сводный отчет организации (из отчетов отдельных филиалов);
- сводный отчет всевозможных министерств и ведомств;
- консолидированный отчет группы связанных между собой организаций.
Эти виды отчетов различаются между собой по таким основным признакам, как методы и цели их подготовки, пользователи информации, которая в них содержится.
Подготовка и принципы сводной отчетности
В странах Запада выработана своя методика составления консолидированных отчетностей. В нашей стране обязательные требования по их составлению были определены не так давно и взаимосвязаны с таким понятием, как сводная финансовая отчетность. Существуют общие международные стандарты, сводная бухгалтерская отчетность должна соответствовать основным их методам и требованиям:
- Принцип полноты состоит в том, что все будущие доходы и расходы, активы и обязательства консолидированного объединения должны быть приняты в полном объеме.
- Принцип собственного капитала - он должен быть определен балансовой стоимостью акций всех консолидируемых объектов, так как они существуют в виде целой финансовой единицы во главе с материнской фирмой.
- Принцип достоверной оценки: отчет должен предоставляться в ясной форме, удобно сформированной для понимания, показывая при этом достоверную отчетную информацию финансового состояния входящих в группу объектов.
- Принцип постоянства - применяется методика консолидации в длительном промежутке времени при условии существующего функционирования предприятия. Могут быть допущены отклонения в некоторых случаях, они обязательно должны быть обоснованы и раскрыты в сопутствующих отчетам приложениях.
- Принцип существенности: раскрываются понятия, которые могут оказать влияние на решение о перемене финансовой деятельности предприятия.
- Методы единой оценки: учет всех активов и пассивов, прибылей, затрат и расходов в полном объеме, основываясь на методах законодательства, принятых в головной организации.
- Единая дата: отчет составляется на дату подведения баланса материнского предприятия.
Можно сделать вывод, что сводная отчетность и консолидированная - различные виды финансовых бухгалтерских отчетов, и нельзя смешивать эти понятия, так как это приводит к непониманию различий в них и путанице при их составлении.
