План-факт в Excel: продажи регионов, итог по суммам и ошибки

Руководителю мало знать, сколько продано за месяц: важно, сколько это от плана и где провал. Справка Microsoft показывает изменение в процентах формулой «новое минус исходное, делённое на исходное» и на её примере получает 6,75%^[1]. Тот же приём лежит в основе отклонения факта от плана. План-факт в Excel превращает две колонки чисел в ответ на вопрос «где мы отстаём».

Порядок работы такой: найти план и факт по каждой строке, ввести формулы отклонения и процента выполнения, проверить итог по суммам, а не по среднему, исправить #ДЕЛ/0! там, где план нулевой, а затем применить таблицу к своим данным и выделить отклонения цветом. План-факт в Excel собирается из четырёх формул и пересчитывается, как только приходит новый факт. Остальные темы анализа собраны в каталоге уроков на главной тренажёра Excel.

Ключевые факты: план-факт в Excel

• Процент изменения справка Microsoft считает как разность нового и исходного значения, делённую на исходное^[1].
• В примере справки изменение с 2342 до 2500 составляет 6,75%, а с 2500 до 2425 — минус 3%^[1].
• При нулевом или пустом плане деление возвращает #ДЕЛ/0!^[2].
• Справка Microsoft предупреждает, что ЕСЛИОШИБКА скрывает все ошибки, а не только #ДЕЛ/0!^[2].
• Итог по суммам в отличие от среднего процентов учитывает вес строк: 100,66% против 99% на примере^[5].

Что такое план-факт в Excel: отклонение и процент выполнения

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

Процент выполнения и отклонение в процентах связаны: 107% выполнения — это отклонение +7%. Формула деления даёт десятичную дробь, например 0,84 для 42 из 50, а проценты она показывает только после процентного формата^[1]. Формат включают кнопкой Percent Style на вкладке «Главная» или сочетанием Ctrl+Shift+%^[4].

Знак отклонения читается по-разному для доходов и расходов. Для выручки плюс — хорошо, для расходов плюс означает перерасход. Поэтому статьи доходов и расходов лучше держать в отдельных блоках таблицы, а не смешивать в одном итоге. Если всё же нужна общая таблица, добавьте столбец «тип статьи» и считайте итоги отдельно по доходам и по расходам.

Разобравшись с формулами, переходим к примеру — на нём видны и хорошие строки, и ошибка.

Пошаговый пример: выполнение плана по регионам и #ДЕЛ/0!

План-факт в этом примере сравнивает план и факт продаж четырёх направлений за месяц и показывает, где план недовыполнен. План стоит в столбце B, факт — в C.

1. Введите план и факт по направлениям в B2:C5.
2. В D2 введите =C2-B2 и протяните до D5 — отклонения: 35 000, −42 000, 0 и 15 000^[5].
3. В E2 введите формулу выполнения с ЕСЛИОШИБКА и протяните до E5.
4. Задайте E2:E6 процентный формат: Север — 107%, Юг — 90%, Запад — 100%^[5].
5. Проверьте итог: план 1 220 000, факт 1 228 000, выполнение 100,66%^[5].

Отрицательное отклонение у Юга сразу показывает провал, а нулевое у Запада — точное попадание в план.

Строка «Новый канал» показывает частый случай: план по ней не ставили, и деление дало бы #ДЕЛ/0!. Справка Microsoft объясняет эту ошибку делением на ноль или на пустую ячейку^[2], поэтому формула выполнения обёрнута в ЕСЛИОШИБКА и выводит прочерк. Второй аргумент функции — то, что выводится вместо ошибки: в справке это ноль^[2], здесь — прочерк, чтобы нулевое выполнение не путали с отсутствием плана. Соберите эту таблицу в уроке тренажёра: урок меняет план и факт и проверяет, что итог считается по суммам.

Однако итоговый процент легко посчитать неверно, если взять среднее процентов строк.

Итог по суммам или среднее процентов: чем отличаются два ответа

Процент выполнения плана по итогу отличается от среднего процентов строк тем, что учитывает вес каждой строки. В примере средний процент трёх направлений с планом — 99%, а итог по суммам — 100,66%^[5].

Разница между ответами появляется потому, что Север с планом 500 000 перевыполнил план, а Юг с планом 420 000 недовыполнил на 10%^[5]: у крупных строк вес больше. Чем сильнее планы направлений отличаются друг от друга, тем дальше среднее процентов уходит от правильного итога, и тем опаснее путать два способа в одном отчёте. Выполнение плана по компании считают только по суммам плана и факта.

Отдельный вопрос — включать ли строки без плана. Новый канал добавил 15 000 к факту и поднял итог выше 100%, хотя плановые направления в сумме план не выполнили: 99,43%^[5]. Честный отчёт показывает обе цифры и подписывает, что вошло в итог. Для этого достаточно двух строк «Итого по плановым направлениям» и «Итого с новыми каналами».

Именно поэтому, кроме формул, отчёту нужна наглядная разметка отклонений.

Разметка отклонений на практике: значки, полосы и прочерки

Отклонения в таблице план-факт удобнее читать, когда они размечены цветом, значками или полосами. Справка Microsoft описывает путь «Главная» → «Условное форматирование» → «Наборы значков» или «Гистограммы»^[3].

Для план-факта пороги значков удобнее задать числами, а не процентами: например, 1 — выполнено, 0,9 — близко к плану. Для статей расходов правило переворачивают: красным выделяют выполнение выше 100%.

ЕСЛИОШИБКА скрывает любые ошибки, а не только #ДЕЛ/0!: справка Microsoft советует сначала убедиться, что формула работает, и лишь потом добавлять обработку ошибок^[2]. Иначе опечатка в ссылке тоже превратится в аккуратный прочерк. Если нужно скрыть только деление на ноль, используйте проверку делителя =ЕСЛИ(B5;C5/B5;"—") — она срабатывает только при пустом или нулевом плане^[2]. В Google Таблицах ЕСЛИОШИБКА устроена так же, а без второго аргумента возвращает пустое значение^[6].

Остаётся понять, в каких случаях таблица план-факт введёт в заблуждение.

Когда план-факт в Excel не подойдёт

План-факт в Excel не подойдёт, если план и факт посчитаны по разным правилам или за разные периоды. Проверьте условия ниже, прежде чем показывать отчёт.

• Если план равен нулю или не задан, процент выполнения не считается: деление вернёт #ДЕЛ/0!^[2].
• Когда план месячный, а факт накоплен с начала года, разница бессмысленна — приведите оба числа к одному периоду.
• Если в одном итоге смешаны доходы и расходы, процент выполнения ничего не говорит: перерасход и перевыполнение сложатся.
• При итоге, посчитанном средним процентов строк, отчёт исказит картину — считайте по суммам^[5].
• Если в факте есть возвраты со знаком минус и план мал, результат в процентах может уйти в минус больше чем на 100% — подпишите такие строки и объясните в комментарии, откуда взялся минус.

Формулы процентов справка Microsoft относит к Microsoft 365, включая Mac, и к Excel 2024 — Excel 2016^[1], а разбор #ДЕЛ/0! — ещё и к Excel в браузере и на телефоне^[2]. Перед отправкой отчёта проверьте три вещи: одинаковый период у плана и факта, итог по суммам и отсутствие ошибок под прочерками. Сами проценты от числа, НДС и наценка разобраны на странице о расчёте процентов, а остальные ошибки формул — на странице об ошибках и их исправлении.