В отчётах по продажам, выполнению KPI или производственных показателях важно быстро увидеть, где факт отстаёт от плана. Особенно если отклонение превышает допустимый порог — например, 10%. Пробегать глазами сотни строк и сравнивать каждую пару «факт — план» — долго. Условное форматирование делает это автоматически: задаёте формулу, и все ячейки с фактом, который ниже плана на 10% и более, подсвечиваются цветом.
Шаг 1: Подготавливаем данные
Предположим, таблица устроена так:
Шаг 2: Выделяем диапазон для форматирования
Шаг 1: Подготавливаем данные
Предположим, таблица устроена так:
- В столбце D — плановые показатели.
- В столбце E — фактические показатели.
- Выделять нужно ячейки в столбце E, где факт ниже плана на 10% и более.
Шаг 2: Выделяем диапазон для форматирования
- Выделите столбец с фактическими значениями.
Шаг 3: Создаём правило условного форматирования
- Перейдите на вкладку «Главная».
- Нажмите «Условное форматирование» → «Создать правило».
3.Выберите тип правила: «Использовать формулу для определения форматируемых ячеек».
Шаг 4: Вводим формулу для расчёта отклонения
В поле «Форматировать значения, для которых следующая формула является истинной» введите формулу:
=E2<D2*0,9
В поле «Форматировать значения, для которых следующая формула является истинной» введите формулу:
=E2<D2*0,9
Разберём формулу:
Шаг 5: Настраиваем формат выделения
- D2 — ячейка с плановым значением (в первой строке вашего диапазона).
- E2 — ячейка с фактическим значением.
- D2*0,9 — 90% от плана (план, уменьшенный на 10%).
- < — проверяет, меньше ли факт, чем 90% плана. Если да — значит, отклонение составляет более 10%, и условие истинно.
Шаг 5: Настраиваем формат выделения
- Нажмите кнопку «Формат».
2.На вкладке «Заливка» выберите цвет фона (например, светло-красный или жёлтый).
3.На вкладке «Шрифт» можно сделать текст полужирным или изменить цвет.
4.Нажмите «ОК» в окне выбора формата, затем ещё раз «ОК».
4.Нажмите «ОК» в окне выбора формата, затем ещё раз «ОК».
Шаг 6: Проверяем результат
Ячейки с фактическими значениями, которые на 10% и более ниже плановых, выделятся цветом. Например:
Хотите увидеть этот процесс вживую и узнать ещё больше полезных советов?
Смотрите наглядный пример в нашем Telegram-канале: https://t.me/Natalia_ProExcel/604
Заключение
Выделение ячеек, где факт отстаёт от плана на заданный процент, помогает мгновенно видеть проблемные зоны. Не нужно вручную вычислять и сравнивать — Excel делает это за вас. Задаёте формулу один раз, и таблица сама подсвечивает всё, что требует внимания.
Освоить условное форматирование и десятки других способов визуального контроля данных можно на курсе «С Excel На ты». Мы учим создавать отчёты, в которых проблемы видны сразу.
Подсвечивайте критические отклонения автоматически, узнав подробнее о курсе «С Excel На ты».
Возникли вопросы?
Пишите нашей команде поддержки: @ProfUpgrade_Support
Ячейки с фактическими значениями, которые на 10% и более ниже плановых, выделятся цветом. Например:
- План 100, факт 85 → выделяется.
- План 100, факт 90 → выделяется (отклонение 10%).
- План 100, факт 92 → не выделяется (отклонение 8%).
Хотите увидеть этот процесс вживую и узнать ещё больше полезных советов?
Смотрите наглядный пример в нашем Telegram-канале: https://t.me/Natalia_ProExcel/604
Заключение
Выделение ячеек, где факт отстаёт от плана на заданный процент, помогает мгновенно видеть проблемные зоны. Не нужно вручную вычислять и сравнивать — Excel делает это за вас. Задаёте формулу один раз, и таблица сама подсвечивает всё, что требует внимания.
Освоить условное форматирование и десятки других способов визуального контроля данных можно на курсе «С Excel На ты». Мы учим создавать отчёты, в которых проблемы видны сразу.
Подсвечивайте критические отклонения автоматически, узнав подробнее о курсе «С Excel На ты».
Возникли вопросы?
Пишите нашей команде поддержки: @ProfUpgrade_Support
