Как сделать прогресс-бар в Excel для презентации дашборда

Пример как сделать красивый прогресс-бар в Excel. Для реализации задачи будем использовать только стандартные инструменты визуализации данных и формулы Excel. Также добавим несколько фигур Excel для стильного оформления на заднем плане.

Пример создания стильного прогресс-бара для дашборда Excel

Сначала создадим таблицу с исходными данными для чарта. После заголовка строк первый столбец содержит отступ для создания дизайна. У него только эстетическая функция. Второй столбец содержит исходные значения. А в третьем столбце снова отступ для внешнего вида. И наконец четвертый столбец содержит формулу вычисления остатка от 100% за вычетом доли заполнения прогресс-бара. Также нам нужны формулы для подписей данных. В них будут использоваться логические функции.

Если прогресс-бар будет заполнен более чем на половину, то цвет подписи буден изменен. Фон чарта темный, а полоса прогресс-бара яркого цвета. Текст подписи на прогресс-баре должен быть читабельным и хорошо отображаться при темном или ярком фоне. Для этого с помощью логической формулы будем изменять цвет текста подписей. Как мы это сделаем в Excel без использования макросов? Рассмотрим далее…

Построение шаблона гистограммы для прототипирования прогресс-бара

Выделяем диапазон таблицы с A3 по E5 и создаем горизонтальную гистограмму.

В первую очередь воспользуемся кнопкой переключения строк и столбцов на чарте. Затем включим опцию реверс для вертикальной оси Y координат, чтобы в первой верхней полосе гистограммы были данные из первого столбца исходной таблицы.

Разработка дизайна прогресс-бара в Excel

На отдельном листе создаем фигуру из прямоугольника со скругленными углами. Указываем размеры высоты и ширины фигуры. Делаем градиентную заливку для фона и границы.

Нам понадобиться еще одна фигура прямоугольника со скругленными углами, но в этот раз нужно максимально скруглить углы. Указываем цвет и создаем эффект внутренней тени. Глубину внутренней тени устанавливаем, изменяя параметр размытия приблизительно на 17 пунктов. Так мы сделаем дизайн для места заполнения прогресс-бара на заднем плане. Выравниваем все фигуры по центру по горизонтали.

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

Пришло время использовать отступы для оформления дизайна. Создаем фигуру полукруга для каждого отступа. Фигуру полукруга для левых отступов следует развернуть на 180 градусов. Отразить вертикально. Каждый полукруг для каждого отступа должен иметь свой цвет.

Копируем фигуру полукруга, затем выделяем только один отступ в одной полосе бара гистограммы и через комбинацию горячих клавиш Control V вставляем фигуру в качестве заливки фона отступа на гистограмме. Эти действия следует повторить для всех шести отступов на трех полосах шаблона гистограммы.

Последнюю долю полос гистограммы делаем прозрачным цветом. Основную долю прогресс-бара заполняем градиентной заливкой так чтобы цвета в начале и в конце совпадали с цветами фигур полукругов в отступах. Так следует сделать на всех трех полосах заполнения прогресс-бара.

Временно отключаем из виду шаблон прогресс-бара и копируем фоновую фигуру с внутренней тенью. Снова делаем видимым шаблон гистограммы и располагаем новые фоновые фигуры под каждую полосу равномерно.

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

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

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

Создание кнопок управления заполнения полос прогресс-бара

Создадим таблицу мини-базы данных для тестирования интерактивных возможностей чарта при заполнении прогресс-бара. В первом столбце наименование дней недели, а далее следуют три столбца с названием заголовков строк из исходной таблицы чарта. Просто копируем их и транспонируем через специальную вставку Excel.

Заполняем таблицу случайными значениями до 100% с помощью формулы из функции RANDBETWEEN поделено на 100. Каждый столбец сортируем по возрастанию и присваиваем процентный формат ячеек.

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

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

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

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

Пример какой прогресс-бар можно сделать в Excel

Проверим работоспособность прогресс-бара под управлением срезом данных.

Скачать шаблон прогресс-бара в Excel

Как видно в Excel можно создавать интересные дизайны для прогресс-бара с интерактивными возможностями без использования макросов. Достаточно стандартных инструментов, формул, сводных таблиц и немного воображения.