Заставьте данные говорить. Как сделать бизнес-дашборд в 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

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

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

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

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

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