Расчёт процентного изменения между 2 числами в 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: отформатируйте результат как процент

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

Результат

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

Раскройте магию Excel с помощью KUTOOLS AI

• Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно по простым командам.
• Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
• Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
• Интерпретация формул: С лёгкостью разбирайтесь в сложных формулах.
• Перевод текста: Ломайте языковые барьеры прямо в своих таблицах.

Расширьте свои возможности в Excel с помощью инструментов на базе ИИ.Скачать сейчаси ощутите эффективность как никогда раньше!

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

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

Шаг 1: примените формулу для получения результата

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

=(C2-B2)/B2

=C2/B2-1

Шаг 2: отформатируйте результат как процент

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

Результат

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

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

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

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

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

• Оба числа отрицательные
• Одно из чисел отрицательное
  • Старое значение положительное, новое — отрицательное
  • Старое значение отрицательное, новое — положительное

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

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

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

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

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

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

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

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

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

Например, компания переходит от убытков к прибыли: в прошлом году этот малый бизнес понёс убыток в размере $10 000 (старое значение — –10 000), а в этом году получил прибыль в размере $20 000 (новое значение — 20 000). При использовании стандартной формулы расчёта процентного изменения результат будет некорректным.

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

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

=(C2-B2)/ABS(B2)

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

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

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

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

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

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

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

Формула 1:

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

Формула 2:

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

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

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

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

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

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

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

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

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

=New value / (1 + Percentage Change)

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

=B2/(1+A2)

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

Обратная ситуация возникает, когда известны процентное изменение и исходное значение, а нужно определить новое значение. Например, если объём продаж в этом году составляет $10 000, а вы хотите, чтобы объём продаж в следующем году был на 20 % выше, чем в этом году, как рассчитать объём продаж за следующий год?

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

=Old value * (1 + Percentage Change)

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

=B2*(1+A2)

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

Связанные статьи