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

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

Шаг 4

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

Шаг 5

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

Шаг 6

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

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

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

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

Способ 1

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

Способ 2

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

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

Резюме

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

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

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

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

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

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

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

Как сделать плоскую таблицу в Excel: урок на YouTube

https://rebrand.ly/table-flat

Скачать таблицу с исходными данными

https://rebrand.ly/database_fot

1.2 Готовим основу для дашборда

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

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

Для создания сводной таблицы выделять плоскую не обязательно – просто поставьте курсор на любую ячейку и на вкладке «Вставка» выберите подменю «Сводная таблица».

Убедитесь, что в открывшемся окне указан весь необходимый диапазон данных. Сводную таблицу необходимо разместить на новом листе (это вариант по умолчанию, так что просто можете жать «ОК»).

На новом листе у вас откроется панель справа (вид по умолчанию):

● фильтры;

● столбцы;

● строки;

● значения.

Как это работает

Числовые данные попадают в «Значения» (ставим галочки «План» и «Факт»).

Категории данных попадают в строки (ставим галочку «Месяц»).

Если добавим еще поле с подразделениями, их названия попадут в строки. Их можно перенести в столбцы перетаскиванием.

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

Как правильно обновлять данные

Смысл бизнес-дашборда в том, чтобы один раз настроить красивую выходную форму отчета, а потом подгружать новые данные. Графики должны автоматически обновиться. Но с этим в Excel тоже не все так просто.

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

Потом возвращаемся на сводную таблицу и пока что не видим июнь. Кажется логичным, что нужно нажать кнопку «Обновить все» на вкладке «Данные». Но и это не дает результата.

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

Способ 1

Изменить диапазон

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

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

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

Способ 2

Выделить столбцы целиком

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

При таком способе новые строки попадают в сводный отчет при нажатии кнопки «Обновить все». Но минус в том, что в каждой таблице будет строка «(пусто)».

Конечно, пустое значение можно скрыть, проделав дополнительные манипуляции с фильтрами. Это не так сложно, но в реальных корпоративных отчетах такое «(пусто)» постоянно вылезает то на графике, то в фильтре и раздражает