рассчитать процентное увеличение/уменьшение
В этом руководстве показано, как создать формулу Excel для увеличения или уменьшения процента и использовать ее как с положительными, так и с отрицательными числами.
В Microsoft Excel есть 6 различных функций для расчета дисперсии. Однако ни один из них не подходит для вычисления процентной разницы между двумя ячейками. Встроенные функции предназначены для нахождения дисперсии в классическом смысле, т. е. того, насколько далеко набор значений разбросан от их среднего значения. Процентная дисперсия — это нечто другое. В этой статье вы найдете правильную формулу для расчета процентного изменения в Excel.
Что такое процентное изменение?
Процентное изменение, также известное как процентная дисперсия или разница, представляет собой пропорциональное изменение между двумя значениями, исходным значением и новым значением.
Формула процентного изменения вычисляет, насколько что-то изменилось между двумя периодами в процентах. Например, можно рассчитать разницу между продажами в этом и прошлом году, между прогнозируемой и наблюдаемой температурой, между запланированной стоимостью и реальной.
Например, в январе вы заработали 1000 долларов, а в феврале — 1200 долларов, поэтому разница составляет увеличение вашего дохода на 200 долларов. Но сколько это в процентах? Чтобы узнать это, вы используете формулу процентного изменения.
Формула процентного изменения Excel
Есть две основные формулы для нахождения процентной разницы между двумя числами.
Классическая формула процентной дисперсии
Вот обычно используемая формула для расчета процентного изменения:
(новое_значение — старое_значение) / старое_значение
В математике вы обычно выполняете 3 шага, чтобы вычислить процентное отклонение между любыми двумя числовыми значениями:
- Вычтите новое значение из старого.
- Разделите разницу на старое число.
- Умножьте результат на 100.
В Excel вы пропускаете последний шаг, применяя процентный формат.
Формула процентного изменения Excel
А вот более простая формула процентного изменения в Excel, которая возвращает тот же результат.
новое_значение / старое_значение — 1
Как рассчитать процентное изменение в Excel
Чтобы найти процентную разницу между двумя числами в Excel, вы можете использовать любую из приведенных выше формул. Допустим, у вас есть предполагаемые продажи в столбце B и фактические продажи в столбце C. Предполагая, что расчетное количество является «базовым» значением, а фактическое — «новым» значением, формулы принимают следующий вид:
=(С3-В3)/В3
или же
=С3/В3-1
Приведенные выше формулы сравнивают числа в строке 3. Чтобы вычислить процент изменения во всем столбце, вам нужно сделать следующее:
- Введите формулу процентной разницы в любую пустую ячейку в строке 3, скажем, в D3 или E3.
- Выделив ячейку формулы, нажмите кнопку Процентный стиль на ленте или нажмите сочетание клавиш Ctrl + Shift + %. Это преобразует возвращаемое десятичное число в проценты.
- Перетащите формулу вниз на столько строк, сколько необходимо.
Скопировав формулу, вы получите столбец процентного изменения ваших данных.
Как работает формула процентного изменения Excel
При выполнении расчетов вручную вы берете старое (исходное) значение и новое значение, находите разницу между ними и делите ее на исходное значение. Чтобы получить результат в процентах, вы должны умножить его на 100.
Например, если исходное значение равно 120, а новое значение равно 150, процентную разницу можно рассчитать следующим образом:
=(150-120)/120
=30/120
=0,25
0,25*100 = 25%
Применение числового формата в процентах в Excel автоматически отображает десятичное число в процентах, поэтому часть *100 опускается.
Формула Excel для процентного увеличения/уменьшения
Поскольку процентное увеличение или уменьшение — это всего лишь частный случай процентной дисперсии, он рассчитывается по той же формуле:
(новое_значение — начальное_значение) / начальное_значение
Или же
новое_значение / начальное_значение — 1
Например, чтобы рассчитать процентное увеличение между двумя значениями (B2 и C2), используется следующая формула:
=(С2-В2)/В2
Или же
=С2/В2-1
Формула для расчета процентного снижения точно такая же.
Excel процентное изменение абсолютного значения
По умолчанию формула процентной дисперсии в Excel возвращает положительное значение для процентного увеличения и отрицательное значение для процентного уменьшения. Чтобы получить процентное изменение как абсолютное значение без учета его знака, оберните формулу функцией ABS следующим образом:
ABS((новое_значение — старое_значение) / старое_значение)
В нашем случае формула принимает такой вид:
=АБС((C3-B3)/B3)
Этот также будет работать нормально:
=АБС(C3/B3-1)
Рассчитать процент скидки
В этом примере показано еще одно практическое применение формулы процентного изменения Excel — вычисление процента скидки. Итак, дамы, отправляясь за покупками, помните следующее:
скидка % = (цена со скидкой — обычная цена) / обычная цена
% скидки = цена со скидкой / обычная цена — 1
Процент скидки отображается как отрицательное значение, поскольку новая цена со скидкой меньше начальной цены. Чтобы вывести результат в виде положительного числа, вложите формулы внутрь функции ABS, как в предыдущем примере:
=АБС((C2-B2)/B2)
Рассчитать значение после процентного изменения
Чтобы получить значение после процентного увеличения или уменьшения, используется общая формула:
начальное_значение*(1+процент_изменения)
Предположим, у вас есть исходные значения в столбце B и разница в процентах в столбце C. Чтобы вычислить новое значение после процентного изменения, формула в D2 скопирована:
=В2*(1+С2)
Во-первых, вы найдете общий процент, который необходимо умножить на исходное значение. Для этого просто прибавьте процент к 1 (1+C2). А затем вы умножаете общий процент на исходные числа, чтобы получить желаемый результат.
Как видите, это решение хорошо работает как для процентного увеличения, так и для уменьшения:
Чтобы увеличить или уменьшить весь столбец на определенный процент, вы можете указать процентное значение непосредственно в формуле. Скажем, чтобы увеличить все значения в столбце B на 5%, введите приведенную ниже формулу в C2, а затем перетащите ее вниз по оставшимся строкам:
=В2*(1+5%)
Здесь вы просто умножаете исходное значение на 105%, что дает значение на 5% выше.
Для удобства вы можете ввести процентное значение в предопределенную ячейку (F2) и обратиться к этой ячейке. Хитрость заключается в блокировке ссылки на ячейку знаком $, поэтому формула копируется правильно:
=B2*(1+$F$2)
Преимущество такого подхода в том, что для увеличения столбца на другой процент достаточно изменить значение только в одной ячейке. Поскольку все формулы связаны с этой ячейкой, они будут автоматически пересчитаны.
Вычисление процентной дисперсии с отрицательными значениями
Если некоторые из ваших значений представлены отрицательными числами, традиционная формула процентной разницы будет работать неправильно. Общепринятым решением является сделать знаменатель положительным числом с помощью функции ABS.
Вот общая формула Excel для процентного изменения с отрицательными числами:
(новое_значение — старое_значение) / ABS(старое_значение)
Со старым значением в B2 и новым значением в C2 реальная формула выглядит следующим образом:
=(C2-B2)/АБС(B2)
Примечание. Хотя эта корректировка ABS технически правильна, формула может давать ошибочные результаты, если исходное значение отрицательное, а новое значение положительное, и наоборот.
Ошибка деления процентного изменения Excel на ноль (#DIV/0)
Если ваш набор данных содержит нулевые значения, вы, вероятно, столкнетесь с ошибкой деления на ноль (#ДЕЛ/0!) при расчете процентного изменения в Excel, потому что вы не можете разделить число на ноль в математике. Функция ЕСЛИОШИБКА может помочь преодолеть эту проблему. В зависимости от ваших ожиданий от конечного результата используйте одно из следующих решений.
Решение 1: если старое значение равно нулю, вернуть 0
Если старое значение равно нулю, процентное изменение будет равно 0% независимо от того, равно ли новое значение нулю или нет.
=ЕСЛИОШИБКА((C2-B2)/B2, 0)
или же
=ЕСЛИОШИБКА(C2/B2-1, 0)
Решение 2: если старое значение равно нулю, вернуть 100%
Это решение реализует другой подход, предполагающий, что новое значение выросло на 100%, начиная с нуля:
=ЕСЛИОШИБКА((C2-B2)/B2, 1)
=ЕСЛИОШИБКА(C2/B2-1, 1)
В этом случае разница в процентах будет равна 100%, если старое значение равно нулю (строка 5) или оба значения равны нулю (строка 9).
Глядя на выделенные ниже записи, становится очевидным, что ни одна из формул не идеальна:
Чтобы получить лучшие результаты, вы можете объединить две формулы в одну, используя вложенный оператор IF:
=ЕСЛИ(C2<>0, ЕСЛИОШИБКА((C2-B2)/B2, 1), ЕСЛИОШИБКА((C2-B2)/B2, 0))
Эта улучшенная формула вернет:
- Процент изменяется на 0%, если и старое, и новое значения равны нулю.
- Процент изменяется на 100%, если старое значение равно нулю, а новое значение не равно нулю.
Вот как можно рассчитать процент увеличения или уменьшения в Excel. Для получения практического опыта вы можете скачать наш образец рабочей тетради ниже. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для скачивания
Формула Excel для процентного увеличения/уменьшения — примеры (файл .xlsx)
