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

Часть 1
Подготовка данных

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

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

1.1 Приводим в порядок исходные данные

Популярная проблема: сотрудники делают отчеты так, как им удобно, не задумываясь, как с этими данными будут работать другие. Получаются сложные для восприятия таблицы со вспомогательными расчетными столбцами, комментариями, выделениями цветом. Иногда это автоматически сформированный отчет из 1С или другой системы, который потом обрастает примечаниями и группировками.

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

Покажу еще не самый страшный пример – таблицу с данными по расходам на персонал.

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

● Правее в столбцах идут месяцы в объединенных ячейках, а под ними – группировка план/факт. То есть мы имеем двухуровневую шапку таблицы.

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

Решение: исходную матричную таблицу нужно перевести в так называемый плоский формат.

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

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

Как преобразовать данные в плоскую таблицу

Плоская таблица с исходными данными должна содержать:

● строку заголовков с названиями категорий;

● 5 столбцов по количеству категорий;

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

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

Перевести таблицу с группировкой в плоский формат можно несколькими способами – от ручного копирования данных в нужные ячейки до использования макросов или техник 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. Заполнили строки соответствующими данными.

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

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

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

https://rebrand.ly/table-flat

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

https://rebrand.ly/database_fot

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

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

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

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

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

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

● фильтры;

● столбцы;

● строки;

● значения.

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

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

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

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

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

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

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

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

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

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

Способ 1

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

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

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

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

Способ 2

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

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

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

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

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

Способ 3

Форматировать как таблицу

Перед тем как вставлять сводную таблицу, давайте преобразуем исходную плоскую таблицу в так называемую умную (смарт-таблицу).

Для этого, находясь в любой ячейке, на вкладке меню «Главная» нажимаем «Форматировать как таблицу» и выбираем формат из предложенных. Форматирование в данном случае – это не просто шаблон оформления ячеек на листе, а хранение этого диапазона как отдельного объекта внутри Excel.

Затем в появившемся окне обязательно проверьте, чтобы стояла галочка «Таблица с заголовками», и нажмите «ОК».

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

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

Всегда давайте название таблице – так вы всегда будете знать, с какими данными работаете. Например, нашей исходной таблице можно дать имя «Фонд_оплаты» (без пробела).

В дальнейшем при добавлении данных в исходную таблицу Excel автоматически будет расширять диапазон умной таблицы: вам не потребуется проделывать дополнительные действия, останется только нажать на кнопку «Обновить все» на вкладке «Данные» – и никаких пустых строк.

Резюме

Уже при создании сводных таблиц важно учитывать, что их придется обновлять, – только так данные на дашборде будут оставаться актуальными. Но само собой это в Excel не происходит.

Лайфхак, который сэкономит ваши силы, – отформатируйте подготовленную плоскую таблицу как умную.

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

2. Этот способ экономит время. При каждом изменении плоской таблицы не придется проводить лишние манипуляции: для обновления сводной таблицы будет достаточно клика по кнопке «Обновить все».

Проблемы обновления данных: урок на YouTube

https://rebrand.ly/data-update

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

https://rebrand.ly/smart-table

1.3 Делаем выборки данных для визуализаций

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

Для дашборда «Анализ фонда оплаты труда» нам потребуются 3 выборки:

● динамика выплат;

● расходы по подразделениям;

● расходы по статьям.

Создадим их на подготовленных данных и выберем подходящие диаграммы и графики для каждой. Останавливаться на вопросе выбора визуальных элементов пока не будем – эту тему подробно рассмотрим позже.

Как работает отчет сводной таблицы

Все возможности для дальнейшей настройки – на правой панели «Поля сводной таблицы». В верхней части по умолчанию находится перечень всех доступных полей, то есть столбцов из плоской таблицы. Ниже – раздел для настройки, состоящий из 4 областей:

● значения;

● строки;

● столбцы;

● фильтры.

Отображение панели можно изменить, кликнув по «шестеренке» и выбрав вариант «Разделы полей и областей рядом» – так будет виден длинный список полей.

Чтобы добавить поле в выборку, поставьте галочку возле его названия в списке полей или перетащите оттуда мышкой в нужную область: «Фильтры», «Столбцы», «Строки» или «Значения». Так же можно и удалить поле из выборки: убрать галочку возле его названия или перетащить мышкой из конкретной области в список полей.

Сводная таблица автоматически агрегирует данные в столбцах, то есть объединяет их по какому-либо признаку. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если же в них есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться количество ячеек.

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

Способ 1

Нажмите правой кнопкой мыши по любой ячейке нужного поля в сводной таблице и выберите другой способ агрегирования.

Способ 2

Выберите нужный тип агрегации через меню: «Анализ сводной таблицы» → «Активное поле» → «Параметры поля» → вкладки «Операции» или «Дополнительные вычисления» в открывшемся окне.

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

Делаем первую выборку

Начнем с выборки для будущей визуализации с динамикой выплат. Для этого на листе со сводной таблицей в панели «Поля сводной таблицы» отмечаем галочками нужное: «Месяц», «Факт» и «План». Это все – первая выборка готова.

Находясь в любой ячейке созданной выборки, заходим на вкладку «Вставка», нажимаем кнопку «Вставить график или диаграмму с областями» и выбираем вид «График с маркерами».

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

Чтобы не запутаться, давайте выборкам названия.

Способ 1

Перейдите в меню «Анализ сводной таблицы» → кнопка «Параметры» слева → в открывшемся окне задайте имя таблице: «Динамика расходов».

Способ 2

Кликните правой кнопкой мыши по первой ячейке в строке с заголовками и выберите из контекстного меню «Параметры сводной таблицы».

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

Это диалоговое окно также можно вызвать с вкладки меню «Анализ сводной таблицы» → «Параметры».

Тиражирование выборки

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

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

Чтобы превратить скопированную выборку в новую, делаем следующее:

ставим курсор на любую ячейку скопированной выборки;

на панели «Поля сводной таблицы» убираем галочку у поля «Месяц»;

там же ставим галочку у поля «Подразделение».

В результате получаем выборку для заготовки визуального элемента «Расходы по подразделениям». Добавляем на лист заготовку: на вкладке меню «Вставка» нажмите «Вставить гистограмму или линейчатую диаграмму» и выберите вид «Гистограмма с группировкой».

Таким же образом повторим процесс тиражирования выборки для визуализации «Расходы по статьям»:

переносим копию второй готовой выборки на новые строки, ставим курсор на любую ее ячейку;

убираем галочку у поля «Подразделение» на панели «Поля сводной таблицы»;

добавляем галочку у поля «Статья расходов».

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

Ошибка «Перекрытие отчетов сводных таблиц не допускается». Что делать?

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

Решение: добавьте нужное число пустых строк (лучше с запасом) после выборки с ошибкой и еще раз обновите информацию.

При размещении выборок друг под другом оставляйте между ними пустые строки, чтобы при добавлении данных было достаточно места для автоматического продления вниз, или размещайте их по горизонтали с отступом в 1–2 столбца.

Резюме

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

1. Выделяем таблицу (следите, чтобы выделились все ячейки, иначе выборка не будет работать).

2. Для тиражирования копируем и вставляем на лист со сводной таблицей новую выборку.

3. Отмечаем нужные для новой выборки поля в панели «Поля сводной таблицы». Даем новой выборке название.

4. Стоя на любой ячейке выборки, выбираем и добавляем заготовку для диаграммы – оформлять ее будем позже.

5. То же самое делаем с другими выборками и заготовками визуальных элементов.

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

Как делать выборки на сводных таблицах: урок на YouTube

https://rebrand.ly/selections

Как избежать перекрытия отчетов: урок на YouTube

https://rebrand.ly/overlap

1.4 Настраиваем интерактив

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

Интерактивность дашборда можно настроить с помощью фильтров или срезов. Фильтры привычны многим, но у них есть свои недостатки, которые делают работу с дашбордом неудобной. Рассмотрим оба инструмента, чтобы подстроиться под предпочтения любого заказчика.

Старые добрые фильтры

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

Я не люблю стандартные фильтры по нескольким причинам.

● Сразу не видно, сколько элементов в списке, – может, 4, а может, 40. Нужно раскрывать список, чтобы выбрать элемент.

● Для выбора нескольких элементов надо нажимать отдельную галочку.

● Для каждой выборки нужно добавлять свои фильтры, даже если они уже были настроены для другой выборки.

● Они занимают лишнее место на листе.

Многие до сих пор добавляют множество фильтров и ставят галочки в длинных списках. Хотя еще в 2010 году появилась современная альтернатива – кнопки-срезы. Во многом благодаря им дашборд получает интуитивно понятный интерфейс.

Волшебные срезы

Как создать срез:

ставим курсор на ячейку внутри сводной таблицы;

на вкладке меню «Вставка» нажимаем «Срез»;

ставим галочку в поле с нужной категорией данных;

получаем интерактивный срез.

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

Нажимаете на кнопку среза – и таблица с графиком динамически фильтруется по выбранному подразделению.

Если вы хотите выбрать несколько подразделений, то выбирайте их с зажатой клавишей Ctrl, а если это вам неудобно, то можете перейти в режим выбора нескольких объектов и по очереди выделять.

Чтобы сбросить фильтр, нажмите на знакомую кнопку справа вверху.

Подключаем срезы к отчетам

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

Способ 1

Через кнопку на ленте меню

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

Способ 2

Через контекстное меню

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

У меня нет предпочтений, по ситуации применяю оба способа. В итоге открывается окно «Подключения к отчетам (Подразделение)», в нем отмечаем галочками все остальные таблицы.

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

Можно добавлять сразу несколько срезов, если в окне «Вставка срезов» поставить несколько галочек возле названий столбцов.

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

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

Выборки и срезы за 5 шагов

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

Шаг 1

Создайте на новом листе первую выборку из полей сводной таблицы «Месяц», «План» и «Факт».

Шаг 2

Откройте параметры сводной таблицы на вкладке меню «Анализ» → «Параметры» или с помощью контекстного меню (клик правой кнопкой мыши).

Шаг 3

В открывшемся окне настроек уберите галочку в пункте «Автоматически изменять ширину столбцов при обновлении». Если этого не сделать, при каждом использовании среза ширину столбцов придется настраивать заново.

Шаг 4

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

Чтобы выбрать на срезе несколько категорий, нажмите на кнопку «Выбор нескольких объектов» возле его заголовка и уберите выделение с ненужных позиций. Еще один вариант – нажать на клавиатуре Ctrl и мышью выбрать нужные позиции.

Шаг 5

Осталось тиражировать базовую настроенную выборку и добавить все нужные заготовки визуальных элементов.

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