Рассчитать процентное изменение между двумя числами в 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. Отформатируйте результат в процентах.
Чтобы отформатировать числа в процентах, оставьте выделенными ячейки результатов и выберите Процентный стиль в Номер регистрации группы под Главная меню.
Результат
Теперь подсчитан процентный прирост пользователей за год. Смотрите скриншот ниже:
Рассчитать процентное снижение
Если новое значение меньше старого, это уменьшение. На скриншоте ниже видно падение цен на продукцию. Чтобы вычислить процентное снижение этих цен, поступите следующим образом.
Шаг 1: Примените формулу, чтобы получить результат
Выберите пустую ячейку (в данном случае D2), введите одну из следующих формул и нажмите кнопку Enter ключ к получению результата. Выделите эту ячейку и перетащите ее Ручка заполнения вниз, чтобы получить остальные результаты. Результат отображается в виде общих чисел, как показано на следующем снимке экрана:
=(C2-B2)/B2
=C2/B2-1
Шаг 2. Отформатируйте результат в процентах.
Чтобы отформатировать числа в процентах, оставьте выделенными ячейки результатов и выберите Процентный стиль в Номер регистрации группы под Главная меню.
Результат
Теперь подсчитано процентное снижение этих цен. Смотрите скриншот ниже:
Расширенные операции
В этом разделе демонстрируются различные ситуации, с которыми вы можете столкнуться при расчете процентного изменения между двумя числами.
Рассчитать процентное изменение с отрицательными числами
При расчете процентных изменений вы можете столкнуться с отрицательным числом следующим образом:
проиллюстрируем каждый из этих сценариев конкретными примерами:
Оба числа отрицательны
Стандартная формула процентного изменения эффективна, даже если оба значения отрицательны.
Например, рассмотрим компанию, убытки которой увеличились с 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)
Рассчитать новое значение на основе процентного изменения
Противоположная ситуация: вы знаете процентное изменение и исходное значение и вам необходимо определить новое значение. Например, если объем продаж в этом году составляет 10,000 20 долларов США, и вы планируете, чтобы продажи в следующем году были на XNUMX% выше, чем в этом году, как бы вы подсчитали продажи в следующем году?
Основная формула:
=Old value * (1 + Percentage Change)
Выберите пустую ячейку, например C2, введите следующую формулу и нажмите кнопку Enter ключ для получения результата.
=B2*(1+A2)
В заключение, умение рассчитывать процентное изменение между двумя числами в Excel — это универсальный навык, который может значительно расширить ваши возможности анализа данных. Независимо от того, идет ли речь о простых увеличениях и уменьшениях или о более сложных сценариях, включающих отрицательные числа или нулевые значения, в этом руководстве представлены различные формулы для решения этих задач. Для тех, кто хочет глубже изучить возможности Excel, наш веб-сайт может похвастаться множеством учебных пособий. Дополнительные советы и рекомендации по работе с Excel можно найти здесь..
Статьи по теме
Создайте гистограмму с процентным изменением
В этом руководстве показано, как создать столбчатую диаграмму с процентным изменением в Excel.
Показывать проценты в виде столбчатой диаграммы с накоплением
В этом руководстве показано, как отображать проценты в столбчатой диаграмме с накоплением в Excel.
Вычислить процентное соотношение между двумя датами
В этом посте обсуждается, как рассчитать процент выполненных задач на основе сегодняшней даты.
Лучшие инструменты для офисной работы
Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!