Анализ данных в Excel с примерами отчетов скачать. Три шага к управленческой отчетности в Excel

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

Рассмотрим поэтапно, как создается . Забегая вперед, скажем, что для работы нам понадобятся функции ВПР, СУММЕСЛИ, а также с инструменты «Автофильтр», «Проверка данных» и «Сводные таблицы».

Единые справочники для управленческой отчетности в Excel

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

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

График, который похоронит вашу управленческую отчетность

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

общие, с одинаковым набором значений (элементов) для всех бизнес-единиц (например, справочники «Затраты» будут содержать такие значения, как материалы, оплата труда, отчисления, прочие расходы);
специфичные, содержащие разные наборы значений признака, для каждой бизнес-единицы – свой набор (пример - справочник «Виды продукции»).

Структура общего справочника предельно проста - всего две графы. В одной - порядковый номер строки, в другой – значение аналитики (см. рис. 1).

Рисунок 1 Общий справочник для управленческой отчетности в Excel

В специфичном справочнике для перечисляются значения аналитик, а также наименования бизнес-единиц, в отчетности которых они будут задействованы. Чтобы в дальнейшем настроить любую отчетную форму под нужды конкретного подразделения, в этом справочнике также указываются порядковые номера бизнес-единиц и номера значений аналитического признака (как сквозные, так и в рамках бизнес-единицы, к которой относятся). На рисунке 3 приведен пример специфичного справочника видов продуктов для условной группы компаний «Век».

Рисунок 3 Специфичный справочник для управленческой отчетности в Excel

В ее составе две бизнес-единицы – Уральский и Центральный металлургические заводы (УМЗ и ЦМЗ соответственно). Первый завод производит чугун и сляб и передает его на переработку на второй завод (выпускает металлические балки и швеллеры). В справочнике видов продукции перечислены все производимые продукты (чугун, сляб, балка, швеллер), для каждого из них указано ответственное за его производство предприятие (УМЗ или ЦМЗ), присвоен порядковый номер в целом по ГК «Век» и в рамках бизнес-единицы, назначен специальный код (объединением полей «Наименование бизнес-единицы» и «Порядковый номер продукта в бизнес-единице»). Например, швеллеры выпускает Центральный металлургический завод – соответственно, в строке, где указана эта продукция, приводятся название бизнес-единицы «ЦМЗ», ее номер (2), далее порядковый номер вида «Швеллер» во всем множестве продуктов, производимых на ЦМЗ (их всего два, этому присвоен «2»), и, наконец, код продукта – «ЦМЗ2», где 2 – номер продукта в рамках ЦМЗ.

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

Формы для ввода данных управленческой отчетности в Excel

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

Аналитические формы – это шаблоны операционных отчетов по определенным показателям (выручка, затраты, объем производства и т. д.) в одном или нескольких аналитических разрезах. В них бизнес-единицы вносят свои данные. Мастер-формы – традиционные финансовые отчеты, например, как отчет о прибылях и убытках. В них данные попадают из аналитических форм.

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

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

в справочнике выделяется диапазон ячеек с названиями бизнес-единиц, ему присваивается уникальное имя (например, «Спр_БЕ») с помощью команды меню Вставка – Имя – Присвоить;
далее выполняется команда меню Данные – Проверка. В появившемся диалоговом окне нужно выбрать тип данных «Список», в поле «Источник» указать заданное ранее имя диапазона («=Спр_БЕ»).

Ячейке на титульном листе, где отображается наименование отчитывающейся бизнес-единицы, также можно присвоить имя (к примеру, «БЕ»). Это упростит организацию ссылок на эту ячейку в формах и отчетах. А теперь подробнее о принципах подготовки аналитических и мастер-форм.

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

Структура шаблонной формы с одним аналитическим признаком идентична структуре соответствующего справочника. Разница лишь в колонках «План», «Факт», в которые заносятся числовые данные. Настроить такую форму под конкретную бизнес-единицу поможет инструмент «Автофильтр» (Данные – Фильтр – Автофильтр по столбцу «Бизнес-единица»), он скроет пустые строки, а также строки, относящиеся к другим подразделениям.

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

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

Рисунок 2 Свободная аналитическая форма

Наконец, значения из заполненных аналитических форм переносятся в мастер-формы – отчет о прибылях и убытках, отчет о движении денежных средств и баланс. Для суммирования и увязки значений показателей лучше использовать функцию СУММЕСЛИ (критерий поиска для функции – значение аналитики или ее код). Так в случае каких-либо изменений в справочниках все формулы в формах останутся рабочими, ссылки не перепутаются – не придется ничего править. Например, показатель «Выручка» мастер-формы «Отчет о прибылях и убытках» (ОПУ) рассчитывается по формуле: «=СУММЕСЛИ(’Форма_Прибыль по продуктам’!F:F;B4;’Форма_Прибыль по продуктам’!G: G)». Она позволяет выбрать из формы «Отчет о прибыли по продуктам» (см. рис. 4) все строки, в которых в графе «Статья» (столбец F) содержится слово «Выручка» (это кодовое слово указано в ОПУ в ячейке B4), а затем просуммировать по ним выручку (столбец G, где как раз и указаны значения этого показателя).

Рисунок 4 Шаблонная аналитическая форма

Поскольку формы в бизнес-единицах могут заполнять одновременно несколько человек, стоит предусмотреть возможность совместной работы над одним и тем же файлом (Сервис – Доступ к книге – Разрешить изменять файл нескольким пользователям. На вкладке «Подробнее» можно создать журнал изменений и задать порядок действий в случае противоречивых изменений файла).

Нравится статья? Добавьте страницу в закладки, распечатайте или поделитесь с коллегой.

Построение управленческого отчета в Excel

Преобразовать в управленческий отчет в Excel данные форм поможет такой инструмент, как «Сводные таблицы». С его помощью можно сформировать самые разнообразные отчеты, в любом разрезе, актуальном для менеджмента, а также их консолидировать. Главное, что в таких отчетах вручную не меняется ни аналитика (она задана справочниками), ни сами данные (их источник – формы), то есть риск ошибки из-за опечатки или неправильной ссылки отсутствует. Этот подход обеспечивает гибкость отчетности, ведь пользователь может подготовить на основе всего пары форм практически любые отчеты*.

Чтобы избежать путаницы, стоит упомянуть, что заполненные формы по своей сути также могут служить отчетами. Но, в отличие от настраиваемых отчетов (сводных таблиц), у них очень жесткая структура – нет возможности отобразить информацию в разных разрезах. Например, в форме «Отчет о прибыли по продуктам» (рис. 4) затраты на те или иные виды продукции отображаются в детализации по статьям расходов. С ее помощью нельзя разобраться, как выглядит структура расходов по продуктам. Можно, конечно, построить еще одну форму с теми же показателями, но с другой иерархией представления аналитических признаков, и предложить бизнес-единицам ее также заполнять, но это никому не нужный лишний труд и повод для ошибок. Гораздо проще воспользоваться сводными таблицами. Тем более что с их помощью можно создавать как консолидированные отчеты, так и отчеты по одной или нескольким бизнес-единицам.

Настраиваемые управленческие отчеты в Excel могут готовиться на основании одной или нескольких форм с фактическими данными. Если планируется задействовать несколько форм (главным образом, необходимо при консолидации), то для создания сводного отчета понадобится сделать дополнительную промежуточную таблицу – «База для консолидации» (в том же файле, где размещаются формы) и с помощью ссылок скопировать в нее содержимое консолидируемых форм. Пример такой базы представлен на рисунке 5.

Рисунок 5 База для консолидации

Для построения консолидированной управленческой отчетности в Excel по всей группе компаний потребуется еще одна дополнительная таблица – «Сводная база» (создается в центральном офисе). В нее ссылками последовательно переносятся данные из таблиц-баз консолидации всех бизнес-единиц, с запасом в несколько пустых строк (для исключения внутригрупповых оборотов соответствующие суммы отражаются со знаком «минус»). А уже затем к ней применяется инструмент «Сводные таблицы». Кстати, в эту же базу из справочников можно перенести и дополнительные признаки аналитики, не задействованные в формах, но нужные при анализе (например, «Регион»).

В заключение пара рецептов, как можно минимизировать ошибки пользователей при работе с созданной моделью отчетности. Во-первых, в формах для ввода фактических данных стоит выделить цветом ячейки, которые предстоит заполнять бизнес-единицам (акцентировать на них их внимание). Во-вторых, остальные ячейки, как и справочники, и отчеты, полезно защитить от редактирования. Сделать это можно с помощью команды меню Сервис – Защита – Защитить лист. Но предварительно, чтобы дать возможность вносить значения на заблокированном для правки листе, нужно снять защиту с пустых ячеек, предназначенных для ввода данных (Правка – Перейти – Выделить – Пустые ячейки, а затем Формат – Ячейки– Защита, где снять галочку «Защищаемые ячейки»).

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

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

Скачать файл с примером управленческой отчетности в Excel, состоящей из справочников, форм и отчетов, можно по ссылке в конце статьи.

Team Foundation Server . Ключевым преимуществом отчетов Excel является простота использования сводной таблицы и подключения к кубу для генерации отчетов.

Для создания отчета откройте Microsoft Excel ( рис. 24.1), выберите на ленте вкладку Данные (1) и щелкните на кнопке Из других источников (2).

Из выпадающего списка меню выберите Из служб аналитики ( рис. 24.2).

Рис. 24.2.

На первой странице мастера подключения данных укажите сервер баз данных и учётные данные для входа ( рис. 24.3). На рис. 24.3 указан сервер баз данных 406-tfs. При выполнении лабораторной работы имя сервера баз данных необходимо узнать у администратора сети и баз данных.

Рис. 24.3.

На странице ( рис. 24.4) выберите базу данных Tfs_Analysis (1), которая содержит куб и список таблиц (перспектив) для анализа данных. Для проведения анализа рабочих элементов командного проекта выберите таблицу Work Item - Рабочие элементы (2) и нажмите кнопку Далее .

Рис. 24.4.

На следующей странице мастера ( рис. 24.5) нажмите кнопку Готово для сохранения файла подключения данных.

Рис. 24.5.

В диалоговом окне Импорт данных ( рис. 24.6) отметьте переключатель Отчет сводной таблицы .

Рис. 24.6.

Формирование отчета в Microsoft Excel

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

Создадим отчет о распределении рабочих элементов (пользовательские описания функциональности и задачи) между участниками проектной группы ( рис. 24.8). Добавим в окно Значение поле Количество рабочих элементов . В окно Названия строк добавим поле Кому назначено . В окно Фильтр отчета - поля Рабочий элемент.Тип рабочего элемента и Рабочий элемент.Состояние .

На рис. 24.8 приведена табличная форма сформированного отчета, а на рис. 24.9 и рис. 24.10 диаграммы отчетов.

Для фильтра можно установить конкретное значение . При задании значения фильтра для элемента Рабочий элемент.Тип = Пользавательские описания функциональности диаграмма будет иметь вид, приведенный на рис. 24.12 . При задании значения фильтра для элемента Рабочий элемент.Тип = Задача диаграмма будет иметь вид, приведенный а на

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

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

Инструменты анализа Excel

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

Средства анализа «Что-если»:

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

Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных .

Другие инструменты для анализа данных:

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

Сводные таблицы в анализе данных

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

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

К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).

Составить отчет можно с помощью «Сводной таблицы».

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

Анализ «Что-если» в Excel: «Таблица данных»

Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» - «Таблица данных».

Важные условия:

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

Процедура создания «Таблицы данных»:

Анализ предприятия в Excel: примеры

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

Служба Power BI

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

Не волнуйтесь. В Power BI превосходный отчет можно создать практически мгновенно.

В этом примере мы загрузим в систему файл Excel, создадим новый ответ и предоставим к нему доступ коллегам - и все это в Power BI.

Подготовка данных

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

Теперь отформатируем данные в виде таблицы. В группе "Стили" на вкладке "Главная" в Excel выберите Форматирование таблицы . Выберите стиль таблицы, который нужно применить к листу. Теперь лист Excel готов к отправке в Power BI.

Отправка файла Excel в Power BI

Power BI подключается к нескольким источникам данных, включая файлы Excel на вашем компьютере. Чтобы начать работу, войдите в Power BI. Если вы еще не зарегистрировались, это можно сделать бесплатно .

Создайте новую панель мониторинга. Откройте Моя рабочая область и выберите значок + Create (+ Создать).

Выберите Информационная панель , введите имя и выберите Создать . Новая информационная панель отображается без данных.

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

На странице "Файлы" выберите Локальный файл . Перейдите к файлу книги Excel на компьютере и выберите его для отправки в Power BI. Выберите команду Импортировать .

Создание отчета

После того как файл Excel будет импортирован в Power BI, приступайте к созданию отчета. Когда появится сообщение Your dataset is ready (Набор данных готов), выберите Просмотреть набор данных . Power BI откроется в режиме редактирования и отобразит холст отчета. Справа находятся панели "Визуализации", "Фильтры" и "Поля".

Данные из таблицы Excel отображаются на панели "Поля". Заголовки столбцов перечисляются под названием таблицы как отдельные поля.

Теперь можно приступать к созданию визуализации. Ваш руководитель хочет увидеть прибыли за определенный период. Перетащите поле Прибыль с панели "Поля" на холст отчета. По умолчанию в Power BI отображается линейчатая диаграмма. Затем перетащите на холст отчета поле Даты . Power BI обновит линейчатую диаграмму и покажет прибыль по датам.

СОВЕТ . Если диаграмма выглядит не так, как вы ожидали, проверьте агрегированные значения. Например, в области Значение щелкните правой кнопкой мыши только что добавленное поле и убедитесь, что вычисление данных выполняется надлежащим образом. В нашем примере используется суммирование .

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

А что насчет визуального представления продаж по продуктам и сегментам рынка? Это легко. На панели "Поля" установите флажки рядом с полями "Продажи", "Продукт" и "Сегмент". Power BI мгновенно создаст линейчатую диаграмму. Измените тип диаграммы, выбрав один из значков в меню "Визуализации". Например, вы можете преобразовать ее в линейчатую диаграмму с накоплением. Чтобы отсортировать данные на диаграмме, щелкните многоточие (...) и выберите Сортировка .