Перейти к основному содержанию

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

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


Основные формулы для расчета процентного изменения

Существует две основные формулы для расчета процентного изменения между двумя числами:

Формула 1:

=(new_value - old_value) / old_value

Формула 2:

=new_value / old_value – 1

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


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

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


Рассчитать процентное увеличение

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

Шаг 1: Примените формулу, чтобы получить результат

Выберите пустую ячейку (в данном случае D2), введите одну из следующих формул и нажмите кнопку Enter ключ к получению результата. Выделите эту ячейку и перетащите ее Ручка заполнения вниз, чтобы получить остальные результаты. Результат отображается в виде общих чисел, как показано на следующем снимке экрана:

=(C2-B2)/B2
=C2/B2-1

Шаг 2. Отформатируйте результат в процентах.

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

Результат

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

Внимание: В формуле C2 это ячейка, содержащая новое значение и B2 — это ячейка, содержащая старое значение.

Рассчитать процентное снижение

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

Шаг 1: Примените формулу, чтобы получить результат

Выберите пустую ячейку (в данном случае D2), введите одну из следующих формул и нажмите кнопку Enter ключ к получению результата. Выделите эту ячейку и перетащите ее Ручка заполнения вниз, чтобы получить остальные результаты. Результат отображается в виде общих чисел, как показано на следующем снимке экрана:

=(C2-B2)/B2
=C2/B2-1

Шаг 2. Отформатируйте результат в процентах.

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

Результат

Теперь подсчитано процентное снижение этих цен. Смотрите скриншот ниже:

Внимание: В формуле C2 это ячейка, содержащая новую цену и B2 — ячейка, содержащая старую цену.

Расширенные операции

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


Рассчитать процентное изменение с отрицательными числами

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

проиллюстрируем каждый из этих сценариев конкретными примерами:

Оба числа отрицательны

Стандартная формула процентного изменения эффективна, даже если оба значения отрицательны.
Например, рассмотрим компанию, убытки которой увеличились с 10,000 10,000 долларов в прошлом квартале (старое значение -50,000 50,000) до XNUMX XNUMX долларов в этом квартале (новое значение -XNUMX XNUMX). Как показано ниже, используя стандартную формулу (= (C2-B2) / B2) дает увеличение на 400%, что указывает на то, что потери увеличились в четыре раза с абсолютного значения в 10,000 50,000 долларов США до XNUMX XNUMX долларов США.

Одно из чисел отрицательное

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

Старое значение положительное, новое значение отрицательное

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

Например, стоимость акций изменилась с положительной на отрицательную. В прошлом месяце его оценили в 10 долларов (старая стоимость — 10). В этом месяце из-за рыночного спада его стоимость упала до -10 долларов (новое значение - -10). Чтобы рассчитать процентное изменение между этими двумя числами, я применяю стандартную формулу (= (C2-B2) / B2), чтобы получить результат -200%. Это представляет собой значительное снижение, указывающее на переход от прибыли к убытку.

Старое значение отрицательное, новое значение положительное

Например, бизнес движется от убытка к прибыли. В прошлом году этот малый бизнес понес убыток в размере 10000 10000 долларов США (старое значение -20000 20000), а в этом году получил прибыль в размере XNUMX XNUMX долларов США (новое значение XNUMX XNUMX). Если вы примените стандартную процентную формулу для расчета процентного изменения, результат будет неверным.

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

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

=(C2-B2)/ABS(B2)

Здесь результат 300% указывает на фактическое положительное изменение от убытка к прибыли.

Внимание:
  • Имейте в виду, что метод ABS может дать ошибочные результаты. Как показано на скриншоте ниже, все предприятия переходят от убытков к прибыли, причем компания F является наиболее прибыльной. Однако он показывает меньшее процентное изменение по сравнению с другими, что не имеет смысла. Поэтому используйте этот метод осторожно!

Рассчитать процентное изменение с нуля

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

Старое значение равно нулю

Если старое значение равно нулю, стандартная формула возвращает значение # DIV / 0! Значение ошибки. Смотрите скриншот:

В этом случае наиболее приемлемым решением будет принять процентное изменение за 100%. В базовой математике изменение от 0 до любого положительного числа будет рассматриваться как 100% увеличение.

Вот основные формулы:

Формула 1:

=IFERROR((new value - old value) / old value, 1)

Формула 2:

=IFERROR((new value / old value - 1, 1)

Чтобы отобразить результат как 100%, а не #DIV/0! Ошибка, вы можете применить одну из следующих формул:

=IFERROR((C2 - B2) / B2, 1)
=IFERROR((C2 / B2) -1, 1)

Новое значение равно нулю

Когда новое значение равно 0, результат равен -1, что составляет -100%. Результат -100% представляет собой полное уменьшение. В этом случае результат является разумным, поскольку изменение от положительного значения до 0 будет означать 100% уменьшение. Результат приемлемый.


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

Иногда вы можете столкнуться с ситуацией, когда вы знаете процентное изменение и последнее значение, теперь вам нужно определить исходное значение. Например, если продажи в этом году составят 12,000 20 долларов США и увеличится на XNUMX% по сравнению с прошлым годом, как бы вы определили прошлогодние продажи? В таких случаях можно использовать следующую формулу:

Основная формула:

=New value / (1 + Percentage Change)

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

=B2/(1+A2)

Внимание: В этой формуле B2 это ячейка, содержащая новое значение, а ячейка A2 содержит процентное изменение.

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

Противоположная ситуация: вы знаете процентное изменение и исходное значение и вам необходимо определить новое значение. Например, если объем продаж в этом году составляет 10,000 20 долларов США, и вы планируете, чтобы продажи в следующем году были на XNUMX% выше, чем в этом году, как бы вы подсчитали продажи в следующем году?

Основная формула:

=Old value * (1 + Percentage Change)

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

=B2*(1+A2)

Внимание: В этой формуле B2 это ячейка, содержащая старое значение, а ячейка A2 содержит процентное изменение.

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

Лучшие инструменты для офисной работы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

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

Описание


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (26)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
((new value/old value)-1)*100 For % change
This comment was minimized by the moderator on the site
=((new value/old value)-1)*100 For % change
This comment was minimized by the moderator on the site
doesnt work
This comment was minimized by the moderator on the site
it is possible to calculate three values with the percentage change?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hi Edi Silva,
You can try this formula: =(first value-second value)/(third value-second value)
This comment was minimized by the moderator on the site
I have 0 in A1, 5 in B1, increase in percentage should be 500%. However with the given formula (B1-A1)/A1 = (0-500)/0 gives me a #DIV/0! error.In case if i apply =IF(A2<>0,(A3-A2)/A2,"0"), it return s me "0". neither of them helps. Any solution to this???
This comment was minimized by the moderator on the site
In case anyone still needs an answer for this problem, use this =IF(A1<>0, (B1-A1)/A1, (B1-A1))
using the percent style format will make (B1-A1) in this case (5-0) = 5 become 500%.
This comment was minimized by the moderator on the site
Hey Stanley,

Thanks for the formula!
Would you mind explaining the formula, please?
This comment was minimized by the moderator on the site
SMH SMH SMH SMH SMH
This comment was minimized by the moderator on the site
I hope you're not that dumb, dividing by 0
This comment was minimized by the moderator on the site
Any help for picking up value only it falls between 85% to 99% else blank.
Such as A is 10 and in B if we put any number , and in C it show the value only if it is fals between 85% to 99% of A
This comment was minimized by the moderator on the site
When a cell is blank, and another cell Has a number, how do you
This comment was minimized by the moderator on the site
Hello, Need help on how to write an formula with this situation.
So i have a report card design to show the grade of each performance of the work output. I set a standard for a specific task in amount of 350. However, based on available volume, not all the month output can be reach to 350. Therefore, we calculated that for this month, the person can achieve their 100% goal by just reaching 174. However, that person can go beyond that number to reach 350. What i want to do here is that our grade level is showing below. If they achieve 174, they would get a grade level B which is between 95% to 105%. But in order to get to maximum 120% of grade A+, the person will need to reach 350. This is where i stuck with. So i know that if a person reaches 180, he/she would get somewhere 102 or 103%. But i dont know how to calculate this and be able to set a maximum 120% between the number of 174 and 350

So right now if the number double from 174 to 348, the % is 200%.... but i need to set this maximum 120% only. How can i do this?

SCORE GRADE

0.00% -

40.00% E-

45.00% E

50.00% E+

55.00% D-

65.00% D

70.00% D+

75.00% C-

80.00% C

85.00% C+

90.00% B-

95.00% B

105.00% B+

110.00% A-

115.00% A

120.00% A+
This comment was minimized by the moderator on the site
Percentage 'change' and percentage 'difference' are two different things. This formula represents percentage change, for example if you are are comparing values of the same statistic over time (e.g. product sales this year compared to product sales last year). Percentage difference is different. Difference can be used when comparing two different statistics to each other (e.g. Mary's annual sales compared to Martha's annual sales). Mary didn't have an increase or decrease on Martha's sales over the same time period, they were just different. To calculate percentage difference you take the absolute value of the difference between two numbers. Then divide it by the average of those two numbers and then multiple by 100 to get the percentage. For example, if the two values are 40 and 60. The difference is 20, ignoring whether it's positive or negative. The average is 50. 20/50 = .4 X 100 = 40%. The percentage increase between these two numbers, if it is an increase, would be 50%. The percentage decrease would be 33%. Close, but all very different.
This comment was minimized by the moderator on the site
1.The percentage increase between these two numbers, if it is an increase, would be 50%. The percentage decrease would be 33%. Close, but all very different.....  How did you get the 50 and the 33?2. If you say Marias YOY is 8% and Martha's YOY is -25%, how do you calculate the difference between those two YOYs?
This comment was minimized by the moderator on the site
The formula that works in all cases is "=(new_value - old_value) / abs(old_value)"
This comment was minimized by the moderator on the site
How can I chnage regular percent change into Year over Year PCH?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations