Заставьте данные говорить. Как сделать бизнес-дашборд в 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
Кликните правой кнопкой мыши по первой ячейке в строке с заголовками и выберите из контекстного меню «Параметры сводной таблицы».
В открывшемся диалоговом окне в поле «Имя сводной таблицы» введите название, которое будет отображать суть данных этой выборки. При дальнейшей работе вы всегда будете знать, с какой выборкой работаете.
Это диалоговое окно также можно вызвать с вкладки меню «Анализ сводной таблицы» → «Параметры».
Тиражирование выборки
Для каждой диаграммы требуется отдельный отчет сводной таблицы. Но для этого не нужно возвращаться на
