10 графических отчетов для руководителя в Excel
ПОДГОТОВКА ПОЛУГОДОВОЙ ОТЧЕТНОСТИ В EXCEL
При подготовке полугодовой отчетности специалисты сталкиваются со следующей проблематикой:
Чтобы успешно справиться с указанными проблемами, подготовить для руководства четкие и понятные отчеты за полугодие, предлагаем использовать графическую форму. Для объемных таблиц, подробных аналитик информативно применить спарклайны и условное форматирование, для компактных таблиц и выбранных ключевых показателей оптимально использовать диаграммы.
Визуализируем данные табл. 1 «Анализ продаж с помощью спарклайнов», выполнив следующие действия:
- В таблице 1 создаем столбец или строку для размещения следующей графической информации:
- «Динамика за I полугодие»;
- «Анализ выручки по продуктовым группам»;
- «Анализ рентабельности по продуктовым группам».
2. Выполняем команду: Вставка → Спарклайны → График (см. рис. 1). В окне «Создание спарклайнов» задаем диапазон данных и диапазон расположения (рис. 2).
3. Копируем графический элемент в нужное количество ячеек. При необходимости редактируем график с помощью Конструктора (рис. 3). Например, отмечаем флажком отметку максимальных точек или маркеров, изменяем цвет графика.
4. Для «Анализа выручки по продуктовым группам» и «Анализа рентабельности по продуктовым группам» используем тип спарклайна «Столбец».
Дополненная спарклайнами табл. 1 представлена в «Сервисе форм» (код доступа — на с. 119). Из таблицы видно, что только выручка по красителям снижается, имеет нисходящую динамику графика-спарклайна. По остальной продукции и в целом по компании выручка растет (графа «Динамика за первое полугодие»).
Строка «Анализ выручки по продуктовым группам» показывает, что наибольший объем выручки компания получает от реализации кирпича стандартного гладкого — 4003,9 из 42 068,82 тыс. руб.
Важный момент: в отчете нужно указать причину визуализированных изменений. Возможная причина изменений в рассматриваемом случае: в начале сезона продаж (весна) предоставили акционные скидки для стимулирования объемов продаж, привлечения новых клиентов.
При копировании таблицы со спарклайнами из Excel в документ Word графики могут быть не видны. В таком случае используют вставку в формате рисунка.
Условное форматирование
В отношении расходов руководство часто требует подробную аналитику. Это могут быть бюджеты расходов по разным направлениям, подробный отчет по фонду оплаты труда, платежи поставщикам. В отчетах нужно выделять важную информацию и нестандартные значения. Восприятие данных улучшится, если использовать гистограммы, цветовые шкалы, наборы значков.
Подготовим табл. 2 «Бюджет общезаводских расходов за I полугодие (факт)» к сдаче руководству с помощью функций условного форматирования:
- Выделяем в таблице диапазон, информацию которого нужно представить более наглядно (в примере — столбцы январь-март). Задаем: Главная → Условное форматирование → Цветовые шкалы. Выберем информативную цветовую шкалу «зеленый-белый» (рис. 4). Пользователи могут выбрать шкалу цвета на свое усмотрение.
Высокие расходы окрасились в привлекающий внимание зеленый цвет:
Чем выше сумма расходов, тем насыщеннее цвет в ячейке.
- Дополняем табл. 2 визуализацией итоговых показателей по статьям расходов. Выделим цифры в столбце факта за полугодие и выполним действия: Главная → Условное форматирование → Наборы значков → Другие правила. Сделаем индивидуальные настройки (рис. 5).
Выбираем «Форматировать все ячейки на основании их значений», а затем подходящий «Значок». В нашем случае это гистограммы. Определяем «Значение» и «Тип»:
- заполненная гистограмма (четыре деления) — для значений более 500 тыс. руб.;
- гистограммы с тремя делениями — от 100 до 500 тыс. руб.;
- гистограмма с одним делением — все значения менее 100 тыс. руб.
Параметры экономист задает исходя из их разброса, средних значений и правил, принятых в компании.
Бюджет общезаводских расходов для отправки руководству выглядит следующим образом (табл. 2).
На примере таблицы «Кредиторская задолженность на конец периода» рассмотрим еще один способ удобно представить показатели.
Материал публикуется частично. Полностью его можно прочитать в журнале «Планово-экономический отдел» № 6, 2018.
Лабораторная работа №6
По умолчанию при создании таблицы Excel ей присваивается стандартное имя: Таблица1, Таблица2 и т.д. Если имеется только одна таблица, то можно ограничиться этим именем. Но удобнее присвоить таблице содержательное имя.
[expert_bq id=»1570″]Отредактируем диаграммы добавим названия и подписи данных, скроем кнопки полей, изменим цвет диаграмм, уменьшим боковой зазор, уберём лишние элементы линии сетки, легенду, нули после запятой у подписей данных. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Выделим диапазон таблицы, перейдём на ленте в раздел Вставка → Диаграммы → Вставка диаграммы → Выберем нужный тип диаграммы → ОК:
7 причин полюбить именованные диапазоны — Различные инструменты Excel — Excel — Каталог статей — Perfect Excel
- на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
- в поле Имя введите: Месяц ;
- в поле Область выберите лист Книга ;
- в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
- нажмите ОК.
Как это работает. Наша формула образно звучит так: «Возьми ячейку над текущей и увеличь результат на единицу». А так как формула тянется вниз, то всё значение каждой ячейки увеличивается на один — и происходит автоматическая нумерация.
[expert_bq id=»1570″]Функция ЕСЛИ отвечает за такое условие если ячейка B2, где у нас лежит фамилия, пустая, то так и оставить её пустой, иначе посчитать общее количество заполненных ячеек. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Функция СЧЁТЗ считает количество заполненных ячеек в диапазоне
Функция ЕСЛИ проверяет ячейку на соответствие какому-то условию. Если условие истинно, выполняется одно действие. Если условие ложно, выполняется другое действие
Диапазон данных в excel — IT Новости из мира ПК
Для формирования динамических диапазонов будем использовать функцию СМЕЩ() , которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Можно задавать высоту и ширину диапазона, а также смещение по строкам и столбцам.
[expert_bq id=»1570″]С одной стороны нужно учитывать тот факт, что пользователь может добавлять продажи за следующие после апреля месяцы май, июнь , с другой стороны Выпадающий список не должен содержать пустые строки. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]
Если вам необходимо постоянно добавлять значения в столбец, то для правильной работы Ваших формул, Вам наверняка понадобятся динамические диапазоны, которые автоматически увеличиваются или уменьшаются в зависимости от количества ваших данных.
10 графических отчетов для руководителя в Excel
К таблице можно добавить дополнительную, итоговую строку, в которой будут размещены результаты выполнения различных функций, примененных к данным некоторых или всех полей.
Порядок действия следующий:
