Как быстро сделать визуальный анализ отчетов в Excel
Фото: voxpopuli.kz
Работа с массивами данных и их обработка – задача непростая, а донести потом главные итоги до руководства – еще тяжелее. Сделать это быстро и эффективно, а также упростить собственный экономический анализ вам поможет визуализация данных в Excel (как известно, около 90% информации человек усваивает именно с помощью зрения).
В большинстве случаев, когда речь идет о визуализации данных, все сразу вспоминают о графиках и диаграммах. Но ведь есть и другие способы! В данной статье цикла материалов об инструментах Exсel для экономистов и бухгалтеров (см. Информбанки «ЭГ» № 20 от 16.03.2018 г., № 31 от 27.04.2018 г. и № 34 от 11.05.2018 г.) мы разберем условное форматирование и спарклайны как более удачную, удобную и быструю альтернативу общепринятым диаграммам в некоторых случаях.
Условное форматирование
Данный способ визуального анализа позволяет автоматически изменять вид ячеек в зависимости от их содержимого.
Предположим, у нас есть данные с фактической выручкой по магазинам сети (помесячно за год). Мы бы хотели отследить все точки, в которых годовая реализация ниже среднего показателя сети, выделив их с помощью красной заливки ячейки. Для этого создается правило, при котором должно осуществляться изменение формата, и далее все ячейки проходят проверку на выполнение нашего условия. Если введенное значение ниже среднего, то заливка нашей ячейки изменится на красный цвет, в противном случае ничего не произойдет.
Теперь для того, чтобы отследить значения, нет необходимости совершать дополнительные вычисления в виде отдельного расчета среднего показателя, а затем сравнивать с ним выручку по каждому магазину и выделять вручную красным все ячейки, которые не удовлетворяют условию – все за вас автоматически сделает Excel. К тому же, если вы работаете с одной и той же формой таблицы и лишь подставляете в нее данные по завершению периода, то при изменении показателей в ячейках данные будут продолжать проходить проверку на выполнение правила, что также очень удобно.
Для того чтобы добавить условное форматирование, необходимо выделить диапазон ячеек, к которым будет применяться правило, и перейти на вкладку Главная – Стили – Условное форматирование (см. рис.).
Рисунок
При нажатии перед вами появится список с группами условного форматирования:
1) правила выделения ячеек. Условия этой группы направлены на сравнение ячейки с заданным значением;
2) правила отбора первых и последних значений. Правила группы позволяют выделить первые и последние 10% значений или первые и последние 10 значений, а также показатели больше или меньше среднего;
3) гистограммы. Отображает данные в виде горизонтальных столбиков, длина которых пропорциональна значению в ячейке. Может служить простой и быстрой альтернативой диаграмм;
4) цветовые шкалы. Фоном ячеек будет градиентная заливка, цвет которой определяется значением каждой ячейки;
5) наборы значков. В каждую анализируемую ячейку добавляется значок, соответствующий заданному правилу;
6) создать правило. Позволяет вносить собственное правило условного форматирования, в том числе задавать их логической формулой (см. табл. 1).
Таблица 1
После выбора одного из правил перед вами появится окно, в котором задаются параметры вида ячеек. Его вид зависит от типа выбранного условного форматирования. Продемонстрируем алгоритм заполнения диалогового окна на нашем примере с выручкой сети, описанном выше.
Напоминаем, нам необходимо залить красным все ячейки с показателями ниже среднего значения по сети. Для этого мы выделяем столбец с годовой выручкой и осуществляем переход: Главная – Условное форматирование – Правило отбора первых и последних значений – Ниже среднего.
Далее в окне выбираем, каким образом мы хотим отформатировать значения. По умолчанию Excel предлагает 6 типов, однако вы также можете задать свой пользовательский формат.
Диалоговые окна есть во всех типах условного форматирования. Исключение составляет самостоятельное внесение правила формулой:
Условное форматирование – Создать правило – Использовать формулу для определения форматируемых ячеек.
Здесь необходимо внести функцию, при выполнении условий которой будут форматироваться наши значения, и задать стиль ячеек.
В таблице 1, где условным форматированием выступает собственная формула. Функция выглядит следующим образом: =ЕСЛИ(J17>СРЗНАЧ($J$17:$J$26);1;0).
То есть Excel проверяет ячейку с выручкой по магазину и сравнивает ее со средним показателем по сети. При выполнении условия программа применяет форматирование (Истина-1), а при невыполнении ничего не происходит (Ложь-0). Подробнее структуру данной функции мы рассматривали в статье, опубликованной в Информбанке «ЭГ» № 31 от 27.04.2018 г.
Отличие данного способа состоит в диапазоне, к которому мы применяем форматирование. То есть если в примере со значениями «Ниже среднего» мы выделяли показатели сразу, и Excel их проверял, то здесь мы выделяем названия магазина, а проверяем все ту же выручку.
Справочно. Если вы захотите скорректировать или убрать условное форматирование, то сделать это не получится, просто удалив значения в ячейках. С этой целью необходимо войти в Условное форматирование – Управление правилами, и уже там либо внести корректировки, либо вовсе его удалить. Еще один вариант очистки диапазона от условного форматирования –Условное форматирование – Удалить правила.
Как вы можете видеть, данный инструмент многогранен и при этом очень гибок, что позволяет удовлетворить любую вашу потребность в визуальном анализе экономических отчетов, который делается с помощью Excel.
Спарклайны
Данный вид визуализации представляет собой небольшую диаграмму, отображенную в одной ячейке. Выбор между диаграммой и гистограммой зависит от наших целей и задач.
Продолжим разбирать пример с выручкой по сети за год помесячно. Если мы хотим сравнить реализацию магазинов между собой в каждом месяце, то для этого нам больше подойдет обычная диаграмма, если же отследить динамику каждого магазина – спарклайн.
Существует 3 вида спарклайнов (см. табл. 2):
1) график. Является аналогом линейной диаграммы;
2) гистограмма. Схожа со столбиковой диаграммой;
3) выигрыш/проигрыш. Позволяет отследить отрицательные показатели за период.
Таблица 2
Для того чтобы создать спарклайн, необходимо выделить данные и перейти на вкладку Вставка – Спарклайны, выбрав подходящий тип. Перед вами появится окно, в котором нужно подтвердить диапазон, на основании которого будет создана наша визуализация, а также указать ячейку ее местоположения. При нажатии на спарклайн появится новая вкладка Конструктор – здесь вы можете управлять спарклайном.
Данный вид визуализации словно «вселяется» в ячейку, т.е. если ее расширить либо объединить с другими ячейками, наш спарклайн тоже увеличится. Кроме того, обычным delete его так просто не удалить. Чтобы очистить ячейку от спарклайна, необходимо нажать на него, а далее на вкладке Конструктор выбрать Очистить.
Появляющаяся вкладка Конструктор при наведении на спарклайн позволяет производить дополнительные настройки нашей визуализации. В основном они связаны с форматом спарклайна, но также есть возможность выделять на нем некоторые элементы. На вкладке Конструктор – Показать с помощью галочек можно выделять точки:
– максимальные/минимальные;
– отрицательные;
– первая/последняя точка;
– маркеры (все точки).
* * *
Итак, мы рассмотрели с вами еще два способа для визуального управления экономическими отчетами в Excel, кроме стандартных диаграмм и графиков. Более универсальным методом является условное форматирование – это гибкий и многогранный инструмент. Однако и спарклайну тоже есть место в отчетах экономистов и бухгалтеров – нужно лишь правильно «ставить» перед ним цели.
