← Все курсы · Курс Excel

Ловушки процента дисперсии: нулевые планы, отрицательные планы и процентные точки

Майкл Носито · Обновлено в августе 2026 г. · Все числа на этой странице были обработаны до их публикации.

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

Что вы делаете: везде используете одну формулу, =IF(budget=0,"",(actual-budget)/ABS(budget))и рассматривать ставки как баллы, а не как проценты. Оставшаяся часть страницы посвящена четырем ловушкам в том порядке, в котором они вам обойдутся.

Краткая версия. Делите по плану, принимайте размер плана, а не его знак, и никогда не публикуйте процент без абсолютного числа рядом с ним.

Ловушка 1: план нулевой

Новый регион, новый продукт, строка затрат, которой не было в прошлогоднем бюджете. Бюджет 0, факт 4200 и результаты Эксель. #DIV/0!.

Обычное исправление делает ситуацию еще хуже:

=IFERROR((actual-budget)/budget, 0)     <- do not do this

В результате получается 0,0%, что каждый читатель воспринимает как означающее, что линия прошла точно по плану. Вы заменили видимую ошибку невидимой ложью. Вместо этого верните пустую строку и позвольте столбцу абсолютной дисперсии рассказать историю:

=IF(budget=0, "", (actual-budget)/ABS(budget))

Пустая ячейка – это честно. Там написано, что здесь нет никакого процента, и это именно так.

Ловушка 2: план негативный

Убыточные строки, контрдоходы, счета хеджирования и любой бюджет, который по традиции записан как отрицательный. План -15 000, факт -9 000. Компания потеряла на 6000 меньше, чем обещала, и это хорошая новость.

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

Ловушка 3: мерой уже является процент

Валовая прибыль запланирована на уровне 20 процентов, фактическая 25 процентов. Есть два истинных предложения и они представляют собой разные числа:

• Маржа выросла 5 процентных пунктов, что равно 25 минус 20.
• Маржа выросла 25 процентов, то есть 5 разделить на 20.

Ни то, ни другое не является неправильным. Публикация любого из них без его подразделения. «Отклонение маржи: 25%» в таблице, остальные строки которой представляют собой денежные отклонения, будет читаться как перемещение на пять пунктов на половину комнаты и движение на двадцать пять процентов на другую половину.

Правило, которое выдерживает пересмотр: для любого показателя, который сам по себе является показателем, сообщайте о разнице в очкии поместите слово «точки» в заголовок столбца. Сохраняйте процентные изменения количества.

Ловушка 4: знаменатель незаметно стал действительным

План 100 000, факт 80 000. Разделите 20 000 промахов на план, и получится 20 процентов. Разделите это на фактическое значение и получите 25 процентов. А теперь переверните ситуацию: планируйте 80 000, реально 100 000. К плану это +25 процентов, к факту +20 процентов.

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

Формула стандартизации

Variance        =C2-B2
Variance %      =IF(B2=0,"",(C2-B2)/ABS(B2))
Points (rates)  =C2-B2, header reads "pts"
Flag            =IF(AND(ABS(D2)>=10000,ABS(E2)>=0.05),"Review","")

Столбец B — бюджет, C — фактический, D — отклонение, E — процент отклонения. Флагу необходимы оба условия, потому что любое из них в отдельности ставит 300-процентный перерасход на строку в 40 фунтов в начало списка.

Шпаргалка

Как применить это в своей работе

1. Откройте таблицу отклонений и нажмите Ctrl+`, чтобы отобразить формулы. Посмотрите на знаменатель каждого процента в столбце, а не только на первый.
2. Замените весь столбец на IF плюс ABS версия. Он идентичен в исправных строках, что позволяет легко проверить изменение.
3. Найдите каждого IFERROR(...,0) в рабочей тетради и спроси, что означает ноль в этой ячейке. Если ноль является реальным значением в другом месте столбца, резервное значение должно быть пустым.
4. Проверьте заголовок каждой строки тарифа и напишите баллы там, где они должны быть.
5. Выполните повторную сортировку по абсолютной дисперсии и посмотрите, изменились ли первые пять строк. Если да, то в докладе приоритет отдается небольшим базам.

Единственная привычка, которую нужно сохранить

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

Есть ли сейчас в вашем столбце процентов ноль, который означает по плану, или тот, который означает мы не могли разделить?