Создание интерактивных дашбордов в Excel: срезы, спарклайны и скрытие интерфейса

Создание интерактивных дашбордов в Excel позволяет превратить обычные таблицы в мощные инструменты для визуализации данных и принятия решений, которые не будут выглядеть как стандартная книга Excel. В этом руководстве мы разберём ключевые инструменты: срезы (Slicers), спарклайны (Sparklines) и приёмы для скрытия интерфейса.

Основные компоненты дашборда

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

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

Как использовать срезы для фильтрации нескольких сводных таблиц

1. Создайте первую сводную таблицу на листе дашборда.
2. Выберите её и на вкладке Анализ нажмите Вставить срез. Добавьте срезы для нужных полей, например, «Регион» и «Линейка».
3. Настройте внешний вид срезов на вкладке Параметры срезов: измените цвет, количество столбцов и размер.
4. Изначально срез связан только с первой сводной таблицей. Чтобы подключить его к другим таблицам, выделите ячейку во второй сводной таблице и выберите Подключения фильтров (ФИЛЬТР (Filter) Connections).
5. В диалоговом окне отметьте галочками срезы, которые должны влиять на эту таблицу. Обычно подключают все срезы, но бывают исключения.

Бонус: Замена длинного среза на выпадающий список

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

1. Создайте срез и скройте его за пределами видимой области.
2. Создайте новую сводную таблицу, подключённую к этому срезу.
3. Поместите поле (например, «Локация») в область Фильтры сводной таблицы. Это создаст выпадающий список в ячейке.
4. При выборе значения в этом списке скрытый срез обновится, что повлияет на все связанные с ним сводные таблицы.
5. Скрыв заголовок фильтра, вы получите компактный выпадающий список, занимающий гораздо меньше места.

Работа со спарклайнами (Sparklines)

Спарклайны — это мини-графики, размещаемые в одной ячейке. Они бывают трёх типов: линейные, столбчатые и «выигрыш/проигрыш».

1. Выделите диапазон данных для спарклайнов.
2. На вкладке Вставка выберите нужный тип спарклайна.
3. В диалоговом окне укажите диапазон данных и место для спарклайнов.
4. Чтобы увеличить спарклайн, можно объединить ячейки или увеличить высоту строки.

Настройка спарклайнов

• Выделение точек: На вкладке Конструктор спарклайнов отметьте Высокая точка и Низкая точка, затем задайте для них цвета.
• Масштабирование: По умолчанию каждый спарклайн масштабируется независимо. Для единообразия в настройках оси выберите Одинаково для всех спарклайнов для минимума и максимума. Часто минимум устанавливают на 0.

Как скрыть интерфейс Excel, чтобы дашборд не выглядел как таблица

Чтобы ваш дашборд выглядел профессионально, скройте стандартные элементы интерфейса Excel:

• Выделите все ячейки и примените светлый цвет заливки, чтобы убрать сетку.
• На вкладке Вид снимите галочки: Строка формул, Заголовки и Сетка.
• Сверните ленту: нажмите Ctrl + F1 или щёлкните по стрелке в правом верхнем углу ленты.
• С помощью клавиш со стрелками переместите активную ячейку так, чтобы она скрылась за диаграммой или срезом.
• Скройте все листы, кроме листа с дашбордом.
• В Файл → Параметры → Дополнительно в разделе Параметры отображения для этой книги отключите полосы прокрутки и ярлычки листов.

Бонус: Выравнивание секций дашборда с разной шириной столбцов

Если части дашборда требуют разной ширины столбцов, используйте Связанное рисунок (Paste Picture Link).

1. Постройте нужный отчёт в отдельной области книги.
2. Скопируйте его ячейки (Ctrl + C).
3. На листе дашборда выберите место для вставки.
4. На вкладке Главная нажмите на нижнюю часть кнопки Вставить и выберите Связанный рисунок (последняя иконка).

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

Бонус: Отображение выбранных значений среза в заголовке

Чтобы не печатать громоздкий срез, можно вывести список выбранных в нём значений в одну ячейку с помощью функции ОБЪЕДИНИТЬ (TEXTJOIN).

1. Скопируйте сводную таблицу, связанную со срезом (Ctrl + C).
2. Вставьте её за пределами печатаемой области.
3. Измените новую таблицу: поместите поле среза в область строк и удалите итоги.
4. В ячейке заголовка дашборда используйте формулу:

   ="Отчёт для "&ОБЪЕДИНИТЬ(", ";ИСТИНА;З4:З26) (="Отчёт для "&TEXTJOIN(", ";ИСТИНА;I4:I26))

   где I4:I26 — диапазон с выбранными значениями из новой сводной таблицы.

Заключение

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