Карточки KPI и условное выделение

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

Хорошая карточка состоит из трёх частей: понятной подписи, крупного числа из формулы и, часто, небольшого сравнения, например изменения относительно предыдущего периода.

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

Начните с основного показателя. Общая выручка на листе Sales — это просто SUM по столбцу с суммами.

Разместите эту формулу в ячейке значения карточки:

Чтобы посчитать количество заказов, используйте COUNTA для столбца, в котором всегда есть значение, например для ID заказа. Подпись карточки — обычная текстовая ячейка над числом — объясняет, что оно означает. Ограничьте каждую карточку одним числом, чтобы её смысл считывался мгновенно.

Многие KPI являются отношениями. Средняя стоимость заказа — это общая выручка, разделённая на количество заказов. Рассчитайте её на основе двух агрегированных показателей.

Один компактный способ — использовать AVERAGE напрямую:

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

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

Отклонение — это текущий показатель минус целевой:

Можно также выразить результат как долю цели с помощью =SUM(Sales!C2:C500)/B1. Значение 1.12 означает, что достигнуто 112 процентов цели. Сравнение превращает простое число в понятную картину происходящего.

Читателям важно видеть динамику. Рассчитайте процентное изменение относительно предыдущего периода. Предположим, итог за этот период находится в D2, а за предыдущий — в D3.

Формула роста:

Если результат за этот период равен 120, а за предыдущий — 100, Вы получите 0.2, то есть рост на 20 процентов. Задайте для ячейки процентный формат. Такой небольшой индикатор роста или снижения превращает статичную карточку в показатель тенденции, который виден сразу.

Направление изменения можно показать словами или символами с помощью IF. Считайте значение ячейки с изменением и выберите подпись.

Эта формула возвращает маркер роста или снижения с процентом:

Если изменение в E1 положительное, отображается сообщение о росте; в противном случае — о снижении. Объединение текста и отформатированного числа сохраняет компактность и понятность карточки без использования диаграммы.

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

Чтобы выделить красным цветом любую карточку, значение которой ниже цели, используйте правило с формулой, например:

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

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

Чтобы обозначить результат относительно цели:

Формула показывает On Track, когда цель достигнута, и Behind, когда результат ниже цели. Такой столбец состояния хорошо сочетается с условным форматированием, которое окрашивает слово, предоставляя и визуальный, и текстовый сигнал.

У настоящих KPI часто бывает больше двух состояний: хорошее, требующее внимания и критическое. Функция IFS проверяет условия по порядку и возвращает первое совпадение, поэтому такая запись понятнее, чем вложенные IF.

Чтобы разделить результат на три уровня:

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

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

Выберите диапазон, откройте раздел «Условное форматирование» и выберите цветовую шкалу, например от зелёного к красному.

• Наибольшие значения будут зелёными, наименьшие — красными, а средние окрасятся промежуточными оттенками.
• Шкала автоматически пересчитывается при изменении данных.

Цветовые шкалы прекрасно дополняют карточки KPI: карточки показывают главный показатель, а окрашенный столбец — положение каждого значения относительно остальных.

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

• Карточка 1: Total Revenue, =SUM(Sales!C2:C500), с отклонением от цели.
• Карточка 2: Orders, =COUNTA(Sales!A2:A500).
• Карточка 3: Avg Order Value, =AVERAGE(Sales!C2:C500), с процентом роста.

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

Вы создали ключевой слой информационной панели:

• Формулы агрегирования, такие как SUM, COUNTA и AVERAGE, сформировали крупные значения KPI.
• Формулы отклонения и роста добавили контекст сравнения с целью и предыдущим периодом.
• IF и IFS преобразовали числа в слова, обозначающие состояние, например On Track, Watch и Critical.
• Условное форматирование с правилами на основе формул автоматически окрасило ячейки и выделило важные значения.

Теперь, объединив сводные таблицы, сводные данные на основе формул и интерактивные раскрывающиеся списки из предыдущих уроков, Вы можете создать полноценную живую информационную панель, управляемую формулами.