Формула процента прироста в Excel: =(новое-старое)/старое. Если значение прошлого года в B2, а этого года в C2, введите =(C2-B2)/B2 и примените к ячейке процентный формат. Положительный результат означает рост, отрицательный снижение.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Last year | This year | Change |
| 2 | Coffee | $12,400 | $14,880 | 20.0% |
| 3 | Tea | $8,600 | $7,740 | -10.0% |
| 4 | Juice | $5,200 | $5,460 | 5.0% |
| 5 | Water | $3,100 | $4,650 | 50.0% |
=(C2-B2)/B2Coffee выросло на 20,0%, а Tea упало на 10,0%, это показано как -10,0%. Water выросло на 50,0%. Измените число в столбце C, и процент обновится. Скобки важны: без них =C2-B2/B2 сначала делит и вычитает 1 из продаж этого года.
Два способа записать одну формулу
=(C2-B2)/B2 the change divided by the old value
=C2/B2-1 the new value as a share of the old, minus 100%
Обе формулы дают одинаковый результат. Первая читается как определение, поэтому её проще проверить потом. В любом случае делить всегда нужно на старое значение. Деление на новое значение самая частая ошибка, и оно даёт другое число: с 80 до 100 это +25%, а 20, делённое на 100, это 20%.
Когда ни одно из чисел не старое, например при сравнении двух магазинов, делите разницу на среднее двух чисел. Это относительная разница, и для 80 и 100 она даёт 22,2% независимо от порядка:
=ABS(B2-C2)/AVERAGE(B2,C2)
В русском Excel: =ABS(B2-C2)/СРЗНАЧ(B2;C2).
Изменение месяц к месяцу в процентах
Для ряда во времени сравнивайте каждую строку с предыдущей. Первому месяцу сравнивать не с чем, поэтому формула начинается со второй строки.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Visitors | Change |
| 2 | Jan | 4200 | |
| 3 | Feb | 4620 | 10.0% |
| 4 | Mar | 4389 | -5.0% |
| 5 | Apr | 5047 | 15.0% |
| 6 | May | 4795 | -5.0% |
| 7 | Jun | 5754 | 20.0% |
=(B3-B2)/B2Feb выше Jan на 10,0%, а Mar ниже на 5,0%. Чтобы сравнивать каждый месяц с Jan, закрепите базу знаками доллара: =(B3-$B$2)/$B$2. Знак $ объясняет страница абсолютные ссылки.
Процент изменения от нуля
Когда старое значение 0, формула делит на ноль и возвращает #ДЕЛ/0! (по-английски #DIV/0!; таблицы на этой странице показывают ошибки под английскими именами). У роста с нуля нет процента, поэтому решите, что ячейка должна показать вместо него, и проверьте этот случай:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Last year | This year | Plain | Checked |
| 2 | Coffee | 12400 | 14880 | 20.0% | 20.0% |
| 3 | Cocoa | 0 | 2100 | #DIV/0! | new |
| 4 | Soda | 0 | 640 | #DIV/0! | new |
=(C2-B2)/B2D3 и D4 показывают #DIV/0!; столбец с проверкой показывает «new». В русском Excel формула проверки пишется =ЕСЛИ(B2=0;"new";(C2-B2)/B2), и таблица примет её и в таком виде, с точкой с запятой. =ЕСЛИОШИБКА((C2-B2)/B2;"new") даёт здесь тот же результат, но скрывает и настоящие ошибки, например текст в столбце продаж. В целом тема разобрана на странице ошибка деления на ноль.
Практика: напишите процент изменения
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Old price | New price | Change |
| 2 | Bread | $2.40 | $2.76 |
Ваша очередь: В D2 посчитайте процент изменения от старой цены в B2 к новой цене в C2. Процентный формат у ячейки уже задан.
Процент изменения и процентные пункты
Когда сами значения проценты, «изменением» называют два разных ответа. Конверсия, выросшая с 4% до 5%, выросла на 1 процентный пункт, и она же выросла на 25%.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Page | Before | After | Points | Percent change |
| 2 | Signup | 4.0% | 5.0% | 1.0% | 25.0% |
=C2-B2D2 вычитает ставки и показывает 1,0%, и об этом говорят «1 процентный пункт». E2 показывает 25,0%. Уточняйте, что имеете в виду, потому что «конверсия выросла на 1%» может означать и то, и другое.
Отрицательные старые значения
Когда старое значение отрицательное, например убыток превращается в прибыль, обычная формула даёт неверный знак: с -200 до 100 =(C2-B2)/B2 возвращает -150%. Делите вместо этого на модуль старого числа:
=(C2-B2)/ABS(B2)
С -200 до 100 эта формула возвращает 150%, рост. Для роста за несколько лет среднегодовой темп полезнее одного большого процента: =(C2/B2)^(1/5)-1 превращает изменение за пять лет в среднегодовой темп роста (CAGR).
Часто задаваемые вопросы
Какая формула процента прироста в Excel?
=(C2-B2)/B2, где B2 старое значение, а C2 новое, в ячейке с процентным форматом. =C2/B2-1 даёт тот же результат. С 80 до 100 результат равен 25%.
Как посчитать процент уменьшения в Excel?
Той же формулой, =(C2-B2)/B2. Когда новое значение меньше, результат отрицательный: со 100 до 80 это -20%. Отдельной формулы для уменьшения в Excel нет.
Как посчитать процент изменения, если старое значение 0?
У изменения от 0 нет процента, и =(C2-B2)/B2 возвращает #ДЕЛ/0!. Покажите в этом случае что-то другое: =ЕСЛИ(B2=0;"n/a";(C2-B2)/B2).
Как посчитать процент изменения с отрицательными числами?
Делите на модуль старого числа: =(C2-B2)/ABS(B2). Убыток -200, ставший прибылью 100, тогда показывает +150%, рост, вместо обманчивых -150%, которые даёт обычная формула.
