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

(Подразделение)», в нем отмечаем галочками все остальные таблицы.

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

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

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

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

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

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

Шаг 1

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

Шаг 2

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

Шаг 3

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

Шаг 4

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

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

Шаг 5

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

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

Резюме

Срезы – основа интерактивности дашборда. Они работают как кнопки, и клик по каждой обновляет все данные на экране. Удобно, понятно, просто для анализа.

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

2. Отключите режим автонастройки ширины столбцов – иначе придется задавать ширину столбцов каждый раз после использования среза.

3. Добавьте срез из выборки («Вставка» → «Срез») и в открывшейся панели настроек отметьте галочками нужные поля.

4. Чтобы деактивировать некоторые категории на срезе, выделите его, зажмите Ctrl и кликните мышкой по ненужным позициям.

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

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

Как настроить интерактивные срезы: урок на YouTube

https://rebrand.ly/slices

Быстрые трюки для excel

Умная таблица

Вот что произойдет, если преобразовать обычный список в таблицу (CTRL+T):

● шапка таблицы автоматически закрепится;

● включится автофильтр;

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

● все формулы будут автоматически копироваться на весь столбец.

Заполнение пустых ячеек

Чтобы заполнить пустоты значениями из расположенных выше ячеек:

● выделите весь диапазон;

● F5 → Выделить (Special) → Только пустые ячейки (Blanks);

● нажмите на знак «=», потом на стрелку вверх, чтобы создать ссылку на предыдущую ячейку;

● введите полученную формулу во все ячейки, нажав CTRL+Enter.

Быстрое копирование

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

Быстрое добавление новых данных в диаграмму

Просто скопируйте диапазон ячеек и вставьте его в диаграмму правой кнопкой мыши или с помощью сочетания клавиш CTRL+C и CTRL+V.

Часть 2

Сборка дашборда

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

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

2.1 Собираем по макету

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

Структуру нашего дашборда можно представить следующим образом.

● Название дашборда

● Карточки KPI

● Рабочая область

● Панель со срезами слева

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

В средней части (под карточками KPI) мы объединили два блока в один, чтобы отследить динамику расходов за более длительный срок.

Теперь у нас есть макет и заготовки визуальных элементов. Собираем их так, чтобы структура соответствовала созданному макету.

Делаем это на отдельном листе нашего файла Excel – назовем его «Чистовик».

Лист с заготовками из сводной таблицы переименуем в «Черновик». Здесь будем хранить и готовить все необходимые данные для чистовика дашборда.

Сначала перенесем на «Чистовик» все срезы. Для этого на «Черновике» выделим мышкой первый срез, а затем, удерживая Ctrl, и остальные.

Вырезаем всю группу срезов.

Переходим на лист «Чистовик» и вставляем вырезанное. Размещаем срезы слева согласно макету, оставив сверху 5 строк для названия дашборда и карточек KPI.

Затем из «Черновика» переносим на «Чистовик» подготовленные диаграммы – так же как делали это со срезами. Размещаем диаграммы на листе в соответствии с макетом:

● сверху по всей ширине экрана – график динамики;

● слева внизу – гистограмма по подразделениям;

● справа внизу – линейчатая диаграмма по статьям расходов.

Не оставляйте