Заставьте данные говорить. Как сделать бизнес-дашборд в Excel. Руководство по визуализации данных

● неповторяющиеся данные в строках.

В строках не должно быть пустых значений: признак подразделения, статья и месяц должны указываться для каждой строки.

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

Начнем с ручного копирования данных – этот способ потребует немного времени, однако для неподготовленного пользователя он проще остальных.

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

Шаг 1

Столбец А в исходной таблице содержит две категории данных – «Подразделение» и «Статья расхода». В плоской таблице они должны находиться в разных столбцах. Вот как их разделить:

Добавляем новый столбец слева от столбца А. Способ 1, самый простой: выделяем столбец А, вызываем контекстное меню правой кнопкой мыши, выбираем «Вставить». Способ 2: ставим курсор на любую ячейку в столбце А, в меню на вкладке «Главная» выбираем в разделе «Ячейки» кнопку «Вставить…» и в подменю кнопку «Вставить столбцы на лист».

В новый столбец перетаскиваем значения ячеек с названиями подразделений. Для этого выделяем ячейки, подводим курсор к границе этого блока и переносим в новое место.

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

Даем столбцам А и B правильные названия в строке над данными – «Подразделение» и «Статья расходов» соответственно. В этой же строке будем указывать заголовки остальных столбцов.

Шаг 2

Теперь из таблицы нужно убрать лишние данные.

Удаляем строки с суммарными значениями, то есть с общими итогами и промежуточными по подразделениям. В противном случае данные останутся суммированными несколько раз и мы получим некорректный результат.

Шаг 3

Добавляем и заполняем столбец с данными по месяцам.

Вставляем новый столбец слева от столбца С со статьями расходов.

Копируем название месяца в первую пустую ячейку нового столбца.

Выделяем эту ячейку и за правый нижний угол рамки протягиваем ее вниз – столбец автоматически заполнится месяцами по их порядку (то же самое будет с датой или последовательностью чисел);

Сразу же меняем эту последовательность, потому что сначала нужно собрать данные за январь. В правом нижнем углу выделенного блока нажимаем на появившуюся иконку меню «Параметры автозаполнения» и выбираем «Копировать ячейки».

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

Дальше надо будет переместить данные по остальным месяцам из соседних колонок в строки ниже, опираясь на этот шаблон.

Шаг 4

Переместим плановые и фактические данные за февраль в столбцы D и E ниже значений за январь. Рядом с ними, в столбце С, протянем значение «Февраль».

Шаг 5

Повторим шаг 4 с данными за остальные месяцы. Названия всех месяцев у нас переезжают в столбец С, плановые показатели – в столбец D, а фактические – в столбец E.

Шаг 6

Содержание столбцов А и B дублируем ниже копированием или протягиванием, заполняя таким образом пустые ячейки.

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

Как сократить число кликов при копировании ячеек

Если выделять ячейки, нажимать Ctrl+C (копирование), ставить курсор в нужное место и нажимать Ctrl+V (вставка), это займет много времени. Есть пара способов ускорить этот процесс.

Способ 1

Выделяем ячейки, подводим курсор к границе выделенного блока и нажимаем Ctrl – возле курсора появляется «+». Удерживая клавишу Ctrl, мышкой перетаскиваем копию данных в нужное место.

Способ 2

Выделяем нужные ячейки и кликаем дважды по правому нижнему углу выделенного блока: ячейки заполнятся ниже.

Результат будет одинаковый, но я предпочитаю второй способ – он быстрее.

Резюме

Анализ исходной кросс-таблицы показал, что она не подходит для создания интерактивного дашборда.

Мы выделили 5 категорий данных и преобразовали таблицу.

1. Распределили категории данных по 5 столбцам.

2. Удалили строки с суммарными значениями.

3. Заполнили строки соответствующими данными.

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