Смысл бизнес-дашборда в том, чтобы один раз настроить красивую выходную форму отчета, а потом подгружать новые данные. Графики должны автоматически обновиться. Но с этим в Excel тоже не все так просто.
Итак, на предыдущем шаге мы получили сводную таблицу, в которой видим факт по месяцам – с января по май. Теперь проведем тест: в исходную плоскую таблицу добавим еще одну строку, в которой укажем «Июнь» (вы можете просто скопировать последнюю строку массива и поменять месяц).
Потом возвращаемся на сводную таблицу и пока что не видим июнь. Кажется логичным, что нужно нажать кнопку «Обновить все» на вкладке «Данные». Но и это не дает результата.
Многие пропускают этот шаг и потом долго мучаются, добавляя новые данные в сводную таблицу. Давайте разберемся, как наладить этот процесс.
Способ 1
Изменить диапазон
При создании сводной таблицы Excel пунктирной линией выделяем фиксированный диапазон ячеек. Если изменить значение внутри него, эти данные обновятся в отчете. Но новая строка с июнем находится за рамками этого диапазона. Чтобы ее добавить, перейдите на вкладку меню «Анализ сводной таблицы» (или просто «Анализ») и нажмите «Источник данных».
В открывшемся диалоговом окне нужно изменить диапазон ячеек: для этого нажимаем на кнопку со стрелкой вверх и мышкой выбираем ячейки для анализа на листе плоской таблицы. После этого данные в сводной таблице изменятся.
Этот вариант работает корректно, но он самый неудобный. При любом добавлении новых строк нужно будет протягивать диапазон вручную. А когда в плоской таблице много данных, то легко ошибиться и не захватить какие-то столбцы или строки.
Способ 2
Выделить столбцы целиком
Я наблюдал, как многие пользователи Excel «автоматизировали» обновление данных. При построении сводной таблицы они выделяли не таблицу с данными, а все столбцы, включая пустые строки ниже. То есть брали максимальный диапазон строк до самого конца листа.
При таком способе новые строки попадают в сводный отчет при нажатии кнопки «Обновить все». Но минус в том, что в каждой таблице будет строка «(пусто)».
Конечно, пустое значение можно скрыть, проделав дополнительные манипуляции с фильтрами. Это не так сложно, но в реальных корпоративных отчетах такое «(пусто)» постоянно вылезает то на графике, то в фильтре и раздражает боссов.
В общем, этот способ настройки обновления сводной таблицы неоптимальный, он требует дополнительных действий для скрытия пустых значений.
Способ 3
Форматировать как таблицу
Перед тем как вставлять сводную таблицу, давайте преобразуем исходную плоскую таблицу в так называемую умную (смарт-таблицу).
Для этого, находясь в любой ячейке, на вкладке меню «Главная» нажимаем «Форматировать как таблицу» и выбираем формат из предложенных. Форматирование в данном случае – это не просто шаблон оформления ячеек на листе, а хранение этого диапазона как отдельного объекта внутри Excel.
Затем в появившемся окне обязательно проверьте, чтобы стояла галочка «Таблица с заголовками», и нажмите «ОК».
У умной таблицы в заголовках столбцов всегда есть фильтр. Кроме того, для нее доступна вкладка «Конструктор таблиц», где можно задать имя таблице (по умолчанию это «Таблица 1») и сразу создать на ее основе сводную кликом по кнопке «Сводная таблица».
В этом случае уже будет указан не диапазон ячеек с данными, а название умной таблицы. Только обратите внимание, название нужно писать без пробелов, иначе Excel выдаст ошибку.
Всегда давайте название таблице – так вы всегда будете знать, с какими данными работаете. Например, нашей исходной таблице можно дать имя «Фонд_оплаты» (без пробела).
В дальнейшем при добавлении данных в исходную таблицу Excel автоматически будет расширять диапазон умной таблицы: вам не потребуется проделывать дополнительные действия, останется только нажать на кнопку «Обновить все» на вкладке «Данные» – и никаких пустых строк.
Резюме
Уже при создании сводных таблиц важно учитывать, что их придется обновлять, – только так данные на дашборде будут оставаться актуальными. Но само собой это в Excel не происходит.
Лайфхак, который сэкономит ваши силы, – отформатируйте подготовленную плоскую таблицу как умную.
1. Умной таблице можно задать имя. Это удобно, потому что при дальнейшем создании сводных таблиц всегда будет понятно, какие данные в них содержатся.
2. Этот способ экономит время. При каждом изменении плоской таблицы не придется проводить лишние манипуляции: для обновления сводной таблицы будет достаточно клика по кнопке «Обновить все».
Проблемы обновления данных: урок на YouTube
https://rebrand.ly/data-update
Как сделать умную таблицу в Excename = "note" урок на YouTube
https://rebrand.ly/smart-table
1.3 Делаем выборки данных для визуализаций
В основе каждого визуального элемента на дашборде – отдельный отчет сводной таблицы. Под термином «отчет» я уже понимаю комплексное представление данных, состоящее из нескольких таблиц и диаграмм. Поэтому отдельные сводные таблицы буду называть выборками данных.
Для дашборда «Анализ фонда оплаты труда» нам потребуются 3 выборки:
● динамика выплат;
● расходы по подразделениям;
● расходы по статьям.
Создадим их на подготовленных данных и выберем подходящие диаграммы и графики для каждой. Останавливаться на вопросе выбора визуальных элементов пока не будем – эту тему подробно рассмотрим позже.
Все возможности для дальнейшей настройки – на правой панели «Поля сводной таблицы». В верхней части по умолчанию находится перечень всех доступных полей, то есть столбцов из плоской таблицы. Ниже – раздел для настройки, состоящий из 4 областей:
● значения;
● строки;
● столбцы;
● фильтры.
Отображение панели можно изменить, кликнув по «шестеренке» и выбрав вариант «Разделы полей и областей рядом» – так будет виден длинный список полей.
Чтобы добавить поле в выборку, поставьте галочку возле его названия в списке полей или перетащите оттуда мышкой в нужную область: «Фильтры», «Столбцы», «Строки» или «Значения». Так же можно и удалить поле из выборки: убрать галочку возле его названия или перетащить мышкой из конкретной области в список полей.