Выделение цветом числовых значений с учетом отклонений в Excel

Каждый торговый представитель на фирме получает определенный месячный план продаж, который нужно выполнить. За прошлый месяц били получены определенные показатели по выполнению плана продаж. Естественно некоторые торговые агенты перевыполнили план, а некоторые не достигли выполнения плана. Допустимая норма отклонения от плана 15%. Следует выделить все показатели, которые вложились в норму с учетом допустимого отклонения.

Выделение цветом значений с допустимыми отклонениями

Для примера возьмем простую таблицу плана продаж и показатели его выполнения:

Следует автоматически выделить цветом все значения в столбце «Продажи», которые на 15% меньше или больше установленной нормы в столбце «План». Для этого:

Выделите диапазон ячеек C2:C10 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».

Выберите опцию: «Форматировать только ячейки, которые содержат».

В разделе «Изменение описание правила:» в каждое поле вводится своя формула отклонения сначала в меньшую а потом в большую сторону. Заполняем все параметры: 1–Значение ячейки, 2–между, 3–=B2*0,85, 4–=B2*0,15.
Нажмите на кнопку «Формат», чтобы задать желаемый цвет заливки для ячеек. Например, зеленый. И нажмите ОК на всех открытых окнах.

Все относительные сумы фактических продаж, которые вкладываются в норму плана с учетом отклонения 15% – выделились цветом:

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

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

Нижняя граница: =B2-(B2*$D$2)
Верхняя граница: =B2+(B2*$D$2)

Обратите внимание, что ссылка на ячейку $D$2 должна быть абсолютной!

Так как мы используем в формулах относительные значение, у нас появилась возможность гибко настраивать пороги нормы отклонения просто указав их в ячейке D2 с процентным форматом:

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