Как рассчитать процентное изменение в Excel [формула]

В этом руководстве показано, как рассчитать процентное изменение между числами, столбцами, строками, а также процентное увеличение и уменьшение в Excel.

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

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

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

Расчет процентного изменения в Excel

Расчет процентного изменения между двумя значениями - простая задача. Вам просто нужно найти разницу между двумя числами и разделить результат на исходное число.

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

= (новое_значение - исходное_значение) / исходное_значение

или

= (новое значение / исходное значение) - 1
  • новое_значение - это текущее / окончательное значение двух сравниваемых чисел.
  • предыдущее_значение - это начальное значение двух чисел, которые вы сравниваете для расчета процентного изменения.

Пример:

Давайте посмотрим на этот список гипотетических заказов фруктов, где столбец B содержит количество фруктов, заказанных в прошлом месяце, а столбец C содержит количество фруктов, заказанных в этом месяце:

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

= (C2-B2) / B2

Приведенная выше формула будет введена в ячейку D2, чтобы найти процентное изменение между C2 (новое_значение) и B2 (исходное_значение). После ввода формулы в ячейку нажмите Enter, чтобы выполнить формулу. Вы получите процентное изменение десятичных значений, как показано ниже.

Результат пока не отформатирован в процентах. Чтобы применить процентный формат к ячейке (или диапазону ячеек), выберите ячейку (я), затем нажмите кнопку «Стиль процентов» в группе чисел на вкладке «Главная».

Десятичное значение будет преобразовано в процент.

Вычислить процентное изменение между двумя столбцами чисел

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

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

Для этого щелкните маркер заполнения (маленький зеленый квадрат в правом нижнем углу ячейки) ячейки формулы и перетащите его вниз к остальным ячейкам.

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

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

Как видите, процентное изменение для «Яблок» увеличено на 50%, поэтому значение положительное. Процент для «Авокодо» уменьшен на 14%, следовательно, результат отрицательный (-14%).

Вы также можете использовать настраиваемое форматирование, чтобы выделить отрицательные проценты красным цветом, чтобы их можно было легко идентифицировать, когда вы их ищете.

Чтобы отформатировать ячейки, выберите ячейки, которые нужно отформатировать, щелкните правой кнопкой мыши и выберите параметр «Форматировать ячейки».

В появившемся окне «Формат ячеек» нажмите «Пользовательский» в нижней части левого меню и введите приведенный ниже код в текстовое поле «Тип:»:

0,00%; [Красный] -0,00%

Затем нажмите кнопку «ОК», чтобы применить форматирование.

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

Вы также можете использовать другую формулу для расчета процентного изменения между двумя значениями (Порядок за последний месяц и Порядок за этот месяц):

= (C2 / B2) -1

Эта формула делит новое_значение (C2) на исходное значение (B2) и вычитает «1» из результата, чтобы найти процентное изменение.

Расчет процентного изменения с течением времени

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

В приведенном ниже примере у нас есть цена на фрукты за март месяц в столбце B и цена за июль месяц через 5 месяцев.

Используйте эту общую формулу, чтобы найти процентное изменение с течением времени:

= ((Текущее_значение / Исходное_значение) / Исходное_значение) * N

Здесь N обозначает количество периодов (лет, месяцев и т. Д.) Между двумя значениями начального и текущего.

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

= ((C2-B2) / B2) / 5

Эта формула похожа на формулу, которую мы использовали ранее, за исключением того, что нам нужно разделить значение процентного изменения на количество месяцев между двумя месяцами (5).

Расчет процентного роста / изменения между рядами

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

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

= (B3-B2) / B2

Поскольку нам нужно найти процент роста для февраля (B3) от цены января (B2), нам нужно оставить первую строку пустой, потому что не сравнивается январь с предыдущим месяцем. Введите формулу в ячейку C3 и нажмите Enter.

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

Вы также можете рассчитать процентное изменение для каждого месяца по сравнению с январем (B2). Для этого вам нужно сделать эту ячейку абсолютной ссылкой, добавив знак $ к ссылке на ячейку, например $ C $ 2. Итак, формула будет выглядеть так:

= (B3- $ B $ 2) / $ B $ 2

Как и раньше, мы пропускаем первую ячейку и вводим формулу в ячейку C3. Когда вы копируете формулу в остальные ячейки, абсолютная ссылка ($ B $ 2) не изменяется, а относительная ссылка (B3) изменится на B4, B5 и т. Д.

Вы получите процентную ставку инфляции или дефляции за каждый месяц по сравнению с январем.

Расчет процентного увеличения в Excel

Расчет процентного увеличения аналогичен расчету процентного изменения в Excel. Увеличение в процентах - это скорость увеличения до начального значения. Вам понадобятся два числа для расчета увеличения в процентах: исходное число и число New_number.

Синтаксис:

Увеличение в процентах = (New_number - Original_number) / Original_number

Все, что вам нужно сделать, это вычесть исходное (первое) число из нового (второго) числа и разделить результат на исходное число.

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

= (C2-B2) / B2

Здесь мы вычитаем счет за электроэнергию за майский месяц (C2) из ​​счета за апрельский месяц (B2) и делим результат на счет за апрельский месяц. Затем скопируйте формулу в другие ячейки, используя маркер заполнения.

Если вы получили положительный результат (24,00 долл. США), процентное соотношение увеличилось за два месяца, а если вы получили отрицательный результат (например, -13,33 долл. США), то процент фактически уменьшился, а не увеличился.

Расчет процентного уменьшения в Excel

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

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

Синтаксис:

Уменьшение в процентах = (Исходное_число - Новое_число) / Исходное_число

Предположим, у вас есть список устройств хранения и их цены на два года (2018 и 2020).

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

= (B2-C2) / B2

Здесь мы вычитаем цену 2018 года (B2) из ​​цены 2020 года (C2) и делим сумму на цену 2018 года. Затем мы копируем формулу в другие ячейки, чтобы найти процентное уменьшение для других устройств.

Если вы получили положительный результат (20,00 долларов США), процентное соотношение уменьшилось между двумя годами, а если вы получили отрицательный результат (например, -13,33 доллара США), то процент фактически увеличился, а не уменьшился.

Распространенные ошибки, возникающие при использовании вышеуказанных формул

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

  • # DIV / 0!: Эта ошибка возникает, когда вы пытаетесь разделить число на ноль (0) или на пустую ячейку. Например. Значение B6 в формуле «= (B6-C6) / B6» равно нулю, поэтому # DIV / 0! ошибка.

Когда вы вводите «ноль» в формате валюты, он будет заменен тире (-), как показано выше.

  • #ЦЕННОСТЬ: Excel выдает эту ошибку, если вы ввели значение неподдерживаемого типа или если ячейки оставлены пустыми. Это часто может происходить, когда входное значение содержит пробелы, символы или текст вместо чисел.
  • NUM!: Эта ошибка возникает, когда формула содержит недопустимое число, в результате чего число оказывается слишком большим или слишком маленьким для отображения в Excel.

Преобразование ошибки в ноль

Но есть способ избавиться от этих ошибок и отображать вместо них «0%» при возникновении ошибки. Для этого вам необходимо использовать функцию «ЕСЛИОШИБКА», которая возвращает настраиваемый результат, когда формула вызывает ошибку.

Синтаксис функции ЕСЛИОШИБКА:

= ЕСЛИОШИБКА (значение; значение_если_ошибка)

Где,

  • ценность значение, ссылка или формула для проверки.
  • value_if_error - это значение, которое мы хотим отобразить, если формула возвращает значение ошибки.

Давайте применим эту формулу в одном из примеров ошибок (# DIV / 0! Error):

= ЕСЛИ ОШИБКА ((B6-C6) / B6,0%)

В этой формуле аргумент «значение» - это формула процентного изменения, а аргумент «значение_если_ошибка» - «0%». Когда формула процентного изменения обнаруживает ошибку (# DIV / 0! Error), функция ЕСЛИОШИБКА отображает «0%», как показано ниже.

Это все, что вам нужно знать о вычислении процентного изменения в Excel.