Интерактивные раскрывающиеся списки и связанные показатели

Статический отчёт показывает одно фиксированное представление. Интерактивная информационная панель позволяет читателю выбрать нужные данные, а числа реагируют мгновенно. Ключевой инструмент — раскрывающийся список, связанный с формулами.

Идея проста: одна ячейка хранит выбор пользователя, например регион или месяц. Каждый показатель на панели ссылается на эту ячейку. Измените значение в раскрывающемся списке — и вся панель пересчитает данные с учётом нового выбора.

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

Раскрывающийся список создаётся с помощью проверки данных. Выберите ячейку выбора, например B1, затем откройте раздел «Данные», пункт «Проверка данных» и выберите вариант «Список».

В качестве источника можно указать диапазон допустимых вариантов:

• Диапазон источника: =Lists!A2:A6, содержащий значения East, West, North, South, All.
• Или создайте список формулой, например =SORT(UNIQUE(Sales!A2:A500)), во вспомогательном столбце и укажите его в качестве источника проверки.

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

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

Первый связанный показатель — общая сумма продаж для выбранного региона. Если в B1 хранится выбор:

Выберите West в B1 — формула вернёт общую сумму для West. Выберите North — результат сразу обновится. Одна формула, бесконечное число представлений.

Добавьте второй связанный показатель: количество заказов в выбранном регионе. COUNTIF считывает значение из той же ячейки выбора.

Разместите эту формулу рядом с итогом:

Поскольку и итог, и количество ссылаются на B1, они всегда описывают один и тот же выбор. Настройте каждый элемент панели так, чтобы он считывал управляющую ячейку, — тогда показатели никогда не будут противоречить друг другу.

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

Используйте IF, чтобы разделить вычисления в зависимости от выбора All:

Когда в B1 указано All, Вы получаете общий итог; в остальных случаях — итог по отфильтрованным данным. Этот шаблон сохраняет представление всех данных и не нарушает логику условий.

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

Под показателями разместите:

Выберите East — появятся все строки для East; выберите West — блок обновится. Третий аргумент выводит понятное сообщение, если совпадений нет, поэтому при пустом результате на панели не отображается некрасивое сообщение об ошибке.

В настоящих информационных панелях часто используется несколько элементов выбора, например Region в B1 и Quarter в B2. Объедините их, считав оба значения в одной формуле.

Используйте SUMIFS, чтобы одновременно учитывать оба выбора:

Теперь читатель может одновременно сузить представление по региону и кварталу. Каждый новый раскрывающийся список — это ещё одна пара условий, получаемых из собственной управляющей ячейки.

Продуманная информационная панель отображает текущий выбор в заголовке, чтобы читатели понимали, какие данные они видят. Создайте динамический заголовок, объединив текст со значением ячейки выбора.

В ячейке заголовка введите:

Если в B1 указано North, заголовок будет выглядеть так: Sales Summary for North. Оператор & объединяет текст и значения ячеек. Эта небольшая деталь делает интерактивную информационную панель завершённой и понятной без дополнительных пояснений.

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

Во вспомогательной области разместите:

Затем укажите для проверки данных этот разлитый диапазон, используя ссылку с решёткой, например =Lists!A2#. По мере появления новых регионов список будет расширяться, и раскрывающийся список автоматически предложит их. Управляющая ячейка останется актуальной без ручного редактирования.

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

Создайте динамическую подпись в свободной ячейке:

Затем настройте в диаграмме ссылку заголовка на эту ячейку. Теперь при смене B1 с East на West подпись диаграммы также изменится. Каждый видимый элемент — и числа, и визуальные компоненты — отслеживает одну управляющую ячейку.

Несколько принципов помогут сделать интерактивные информационные панели надёжными:

• Одна управляющая ячейка для каждого выбора: направляйте каждый элемент выбора в одну чётко обозначенную ячейку.
• Считывайте, а не дублируйте: каждый элемент панели должен ссылаться на управляющую ячейку, чтобы все показатели совпадали.
• Учитывайте варианты «Все» и пустого результата: корректно обрабатывайте просмотр всех данных и отсутствие совпадений.

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

Проверьте, понимаете ли Вы, как сохранить работоспособность представления всех данных.

Вы превратили статический отчёт в интерактивную информационную панель:

• Проверка данных создала раскрывающийся список в одной управляющей ячейке, например B1.
• SUMIF и COUNTIF связали показатели с этим выбором, а ветвление с помощью IF обработало вариант All.
• FILTER сформировала таблицу подробных данных на основе той же управляющей ячейки, а SUMIFS объединила два раскрывающихся списка.
• Заголовок с объединённым текстом и разлитый список проверки данных помогли сохранить понятность и актуальность панели.

Далее Вы создадите карточки с ключевыми показателями и условные выделения, отмечающие наиболее важные числа.