Месячный рост в Excel и месяц, которого нет

1. Сначала доберитесь до одной строки в месяц

Рост сравнивает два числа, поэтому прежде всего вам нужно одно число за месяц. Два маршрута.

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

SUMIFS против списка начала месяца, что требует большего набора текста и большего контроля. Стройте границы с DATE и EOMONTH вместо того, чтобы вводить их, поэтому лист ведет себя одинаково на всех машинах:

=SUMIFS(Orders[Revenue],
        Orders[OrderDate], ">="&A2,
        Orders[OrderDate], "<="&EOMONTH(A2,0))

При наличии A2 на 1 января 2026 года получается 2630. Та же самая формула, указанная для 1 апреля, возвращает 0, что и является основной причиной выбора этого маршрута, а в четвертом разделе рассказывается, почему.

В любом случае, прежде чем идти дальше, проверьте общую сумму: месяцы должны добавиться к общей сумме. Здесь 2630 + 2245 + 2585 + 2430 = 9890, что соответствует полному доходу таблицы, поэтому при группировке ничего не потерялось.

2. Формула роста

Одна строка в месяц, сначала самые старые, а рост — это строка выше.

=B3/B2 - 1

Форматируйте результат в процентах, а не умножайте на 100, поскольку ячейка, отформатированная в процентах, по-прежнему содержит исходное число, и ее можно усреднить, отобразить на диаграмме и сравнить. Умножение на 100 превращает его в число, которое выглядит как процент.

За четыре месяца присутствуют данные:

Month   Revenue    Growth
Jan       2630         -
Feb       2245    -14.6%
Mar       2585    +15.1%
May       2430     -6.0%

Проверьте вручную: 2245 ÷ 2630 = 0,8536, минус 1 равно -0,1464, что составляет -14,6%. Эквивалентная форма =(B3-B2)/B2 дает тот же ответ и больше похож на определение; используйте то, что узнаете через шесть месяцев.

Над первой строкой ничего нет, поэтому оставьте ее пустой, а не показывайте ноль. Ноль там утверждает плоский рост за месяц, где рост не определен, и кто-то будет усреднять столбец.

3. Месяц, которого нет

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

Апрель. Апрельских заказов нет, поэтому апрель так и не стал строкой, а формула в майской строке разделилась на строку над ней, то есть март.

What the sheet says      May, -6.0%
What it computed         2430 / 2585 - 1
What that measures       May against March
What April was           0, in a month nobody looked at

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

Он также незаметно меняет все, что вычислено в столбце. Среднемесячный доход по четырем представленным рядам составляет 2472,50. За прошедшие пять месяцев, если считать апрель нулевым, он составил 1978,00. Это разница в 494,50 в месяц по сравнению с несуществующим рядом.

=AVERAGE over the four rows present        2472.50
=AVERAGE over five months, April as 0      1978.00

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

4. Календарная таблица, поэтому пробел становится нулевым.

Исправление – это не более умная формула. Это список каждого месяца за период, записанный независимо от данных, с сопоставлением с ним итоговых значений.

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

A2:  =DATE(2026,1,1)
A3:  =EDATE(A2,1)        and fill down

Затем суммируйте его с помощью SUMIFS, точно так же, как в первом разделе. Теперь каждый месяц существует в виде строки, независимо от того, произошло ли в нем что-нибудь:

Month   Revenue    Growth
Jan       2630         -
Feb       2245    -14.6%
Mar       2585    +15.1%
Apr          0   -100.0%
May       2430    #DIV/0!

Это выглядит хуже, но это намного лучше. Виден 0, -100,0% — верное утверждение об апреле, а ошибка в строке «Май» — это то, что Excel отказывается делить на ноль, что является правильным ответом на вопрос, на который нет ответа. Версия без календаря скрыла все три за кругленькими -6,0%.

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

5. Деление на ноль и что вместо него показывать

До пояснения: в прошлом месяце было ноль, а в этом месяце 2430. Скажите, какой процент роста.

Нет ни одного. Рост — это отношение к базе, и не существует значимого отношения к нулю: любое увеличение от нуля бесконечно в процентном выражении, поэтому Excel возвращает #DIV/0! а не большое количество.

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

=IF(B2=0, "n/a", B3/B2-1)         says growth is undefined
=IF(B2=0, "new", B3/B2-1)         says the series restarted
=IF(B2=0, "", B3/B2-1)            blank, which at least is not a claim

Чего избегают все трое, так это =IFERROR(B3/B2-1, 0), который сообщает о 0% росте за месяц, который вырос с нуля до 2430. Это не компромисс по округлению, это противоположно тому, что произошло, и это именно сделка. перенос ошибки всегда предлагает.

И что бы вы ни выбрали, сообщайте об абсолютном изменении рядом с процентом. Февральское значение -14,6% также равно -385, а значение 2430 от 0 вообще не имеет никакого процента, а просто составляет 2430. Ряд вещей и процент вещей читаются людьми по-разному, и пару вместе гораздо труднее прочитать неправильно, чем любую из них по отдельности.

6. Процент против процентных пунктов

Два разных слова для двух разных количеств, и их смешивание — самая распространенная ошибка в формулировках месячной упаковки.

Если коэффициент конверсии повысится с 5,12% до 6,85%, изменение будет 1,73 процентных пункта. Это также Увеличение на 33,8%, потому что 6,85 ÷ 5,12 - 1 составляет около 0,338. Оба верны; это ответы на разные вопросы, и цифры не близки.

6.85% - 5.12%          1.73 percentage points
6.85% / 5.12% - 1      33.8%

Правило простое, однажды сказано: вычитание двух процентов дает процентные пункты, деление их дает процент. Произнесите вслух «пункты», когда вы имеете в виду вычитание, и поместите это слово в заголовок столбца, потому что столбец с заголовком «изменение», содержащий 1,73, будет воспринят кем-то в спешке как 1,73%.

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

7. Маленькие базы и скользящая средняя

Месячный рост делится на последний месяц, поэтому чем меньше был последний месяц, тем выше процент. Регион, который сделал 40 в прошлом месяце и 120 в этом месяце, вырос на 200%, а регион, который сделал 4000 и дошел до 4400, вырос на 10%, а второй перевел в двадцать раз больше денег.

Две привычки держат это под контролем.

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

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

=AVERAGE(B2:B4)      three months ending March, calendar included:  1610.00
=AVERAGE(B3:B5)      three months ending May, calendar included:    1671.67

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

8. Вместо прошлого года

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

Годовое сравнение каждого месяца с тем же месяцем годом ранее устраняет это. Та же формула с другим партнером:

=B14/B2 - 1              twelve rows back, on a monthly series with no gaps

Это еще одна причина, по которой календарь имеет значение: «двенадцать строк назад» — это только «двенадцать месяцев назад», если каждый месяц — это строка. В таблице, построенной только на основе данных, двенадцать строк назад могут относиться к тринадцати или четырнадцати месяцам, и об этом ничего не говорится.

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

Пограничные случаи, нарушающие столбец роста

Шесть, которые пройдут.

Частичный текущий месяц. Сравнение трех недель этого месяца с полным прошлым месяцем всегда показывает падение. Либо исключите текущий месяц, либо масштабируйте его и четко обозначите как прогноз.

Текстовые даты. Если столбец даты является текстовым, сводная точка не будет группироваться, а границы SUMIFS не будут совпадать, и в обоих случаях произойдет сбой, похожий на проблему с формулой. Запустите =N(A2) сначала проверь.

Временные метки на границе месяца. Заказ в 23:40 31 января имеет порядковый номер чуть выше конца января, поэтому <=EOMONTH(...) исключает это. Использовать < Вместо этого первого числа следующего месяца, что в любом случае безопасно.

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

Отрицательная база. Если в прошлом месяце было -200, а в этом месяце - 100, формула возвращает -150%, что арифметически верно и сообщает противоположное тому, что произошло. Если значения могут стать отрицательными, сообщите об абсолютном изменении и пропустите процентное значение.

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