Дашборды стали весьма популярными в последнее время, и хотя, количество программных продуктов для создания дашбордов растет, доля Excel занимает в этой области значительную часть.
Что такое дашборд?
Дашборд – это визуальное отображение наиболее значимой информации, которая полностью вписана в компьютерный экран. Более подробно о дашбордах и их преимуществах мы рассматривали в предыдущей статье.
Проблема прокрутки
В данной статье мы рассмотрим вопрос подгонки отчета на один экран монитора. Представьте, что у вас имеется список, состоящий из 100 или более пунктов (например, материалы, регионы продаж…) с соответствующими ключевыми показателями производительности (например, цена, себестоимость, продажи … ) и вы хотите отобразить эту информацию на дашборде. Вся таблица не поместится на одном экране компьютера. В большинстве случаев будет отображаться около 10 наиболее важных пунктов. Но что если пользователь захочет прокрутить вниз и посмотреть остальные данные? Конечно, вы можете обучить его выбрать лист с данными, чтобы он увидел всю информацию. Но это неудобно и небезопасно, и не является подходом построения дашбордов.
Решение
Таблицы дашбордов не требуют дополнительных навыков. Единственное отличие от миллионов других числовых таблиц, созданных в Excel, является полоса прокрутки между названиями элементов и данными. Данная полоса прокрутки позволяет просматривать весть список элементов без необходимости покидать лист с дашбордом. Такая таблица занимает мало места и позволяет сохранить пространство для других таблиц и диаграмм на экране.
Загрузите Excel файл с таблицей для дашборда для лучшего понимания
Реализация
Разделим данные. Необходимо поместить исходные данные на отдельном листе. Не думаю, что у вас могут возникнуть с этим сложности, поэтому детально на этом шаге останавливаться не будем.
Далее создаем таблицу, состоящую из 10 строк, для нашего дашборда.
Вставляем полосу прокрутки. Находим в панели управления Разработчик –> Элементы управления –> Вставить — > Элементы управления формы –> Полоса прокрутки. Рисуем на листе полосу прокрутки.
Назначаем связь с ячейкой. Щелкаем правой кнопкой мыши по полосе прокрутки, выбираем Формат объекта. В появившемся диалоговом окне Формат элемента управления на вкладке Элемент управления заполняем поля, как показано на рисунке
Вставляем формулу =СМЕЩ() для отображения любой последовательности 10 записей. Формула СМЕЩ используется в дашборде, чтобы вернуть 10 строк из листа с необработанными данными. Пример формулы выглядит таким образом =СМЕЩ(Данные!B3;Расчеты!$C$2;0;1;1), где Данные!B3 – ссылка на колонку, содержащую необходимые данные, Расчеты!$C$2 – текущее значение полосы прокрутки.
Вот и все, теперь у вас есть небольшая табличка, где вы сможете просмотреть все необходимые данные.
Спасибо!!!!!
Вот это действительно шикарная хитрость…
Полезно. Вот только жаль что если добавлять значение в таблицу, нет возможности автоматически увеличить макс. значение полосы прокрутки.
На самом деле, можно реализовать данный функционал с помощью макросов, на момент написания этого дашборда такой необходимости не было
Pingback: Excel дашборд по обслуживанию клиентов — добавление макросов [часть 4 из 4] | Exceltip
Скажите,а почему в ячейке максимальная позиция ползунка =СЧЁТ(Лист2!A1:A101)-9
почему минус 9 ?
Максимальное значение указывает максимальную позицию верхнего значения при раскрытом списке. Плюс к этому еще необходимо прибавить количество позиций отображаемых при раскрытом списке. -9 указывает, сколько позиций необходимо отнять от общего числа значений, чтобы не выйти за границы значений с данными
Добрый день!
Подскажите пожалуйста, как можно создать горизонтальный ползунок, влево о в право (а не вверх и вних)?
Добрый день, Ерке.
Чтобы сделать полосу прокрутки горизонтальной, щелкните на нее правой кнопкой мыши, в выпадающем меню выберите Свойста. В появившемся диалоговом окне, в опции Orientation выберите fmOrientationHorizontal. Обратите внимание, что при создании полосы прокрутки необходимо выбирать не элемент управления формы, а объект ActiveX
Очень хороший совет….но только одно но, куда и как вставлять формулу? Если делать всё как написано…ничего не получается.