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

Шаг 4


Шаг 5

Шаг 6


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

Как сократить число кликов при копировании ячеек
Если выделять ячейки, нажимать 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 «автоматизировали» обновление данных. При построении сводной таблицы они выделяли не таблицу с данными, а все столбцы, включая пустые строки ниже. То есть брали максимальный диапазон строк до самого конца листа.
При таком способе новые строки попадают в сводный отчет при нажатии кнопки «Обновить все». Но минус в том, что в каждой таблице будет строка «(пусто)».

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