Незалежно від того, чи йдеться про бізнес-аналіз, академічні дослідження чи управління особистими фінансами, оволодіння обчисленнями відсоткових змін є важливим для ефективного аналізу даних. Ця стаття розпочнеться з основних формул для обчислення збільшення та зменшення у відсотках у Excel, а потім заглибиться в більш складні операції, зокрема обробку від’ємних чисел, роботу з нульовими значеннями та зворотне обчислення вихідних або нових значень на основі заданих процентних змін.

• Обчисліть збільшення у відсотках
• Обчисліть відсоток зменшення

• Обчисліть відсоткову зміну з від’ємними числами
• Обчисліть відсоткову зміну з нулем
• Розрахувати старе значення на основі зміни у відсотках
• Обчисліть нове значення на основі зміни у відсотках

Основні формули для обчислення відсоткової зміни

Існує дві основні формули для обчислення відсоткової зміни між двома числами:

формула 1:

=(new_value - old_value) / old_value

формула 2:

=new_value / old_value – 1

Ці формули можуть допомогти вам визначити, наскільки певне число збільшилося або зменшилося у відсотках. Нижче ви знайдете два приклади того, як ці формули можна використовувати для обчислення відсоткової зміни між двома числами.

Обчисліть відсоткову зміну між двома числами

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

Обчисліть збільшення у відсотках

Якщо нове значення більше за старе, результатом є збільшення у відсотках. Як показано на знімку екрана нижче, припустімо, що у вас є веб-сайт, кількість зареєстрованих користувачів якого зростає з кожним роком. Щоб розрахувати відсоток збільшення користувачів на рік, ви можете зробити наступне.

Крок 1. Застосуйте формулу, щоб отримати результат

Виберіть порожню клітинку (у цьому випадку D2), введіть одну з наступних формул і натисніть Що натомість? Створіть віртуальну версію себе у ключ до отримання результату. Виберіть цю клітинку та перетягніть її Ручка заповнення вниз, щоб отримати решту результатів. І результат відображається як загальні числа, як показано на наступному знімку екрана:

=(C2-B2)/B2

=C2/B2-1

Крок 2: Відформатуйте результат у відсотках

Щоб відформатувати числа у відсотках, залиште клітинки результатів вибраними, перейдіть до вибору Процентний стиль кнопка в Номер група під Головна Вкладка.

Результат

Наразі підраховано відсоткове збільшення кількості користувачів на рік. Перегляньте знімок екрана нижче:

Обчисліть відсоток зменшення

Якщо нове значення менше старого значення, це зменшення. На скріншоті нижче показано падіння цін на продукти. Щоб обчислити відсоткове зниження цих цін, виконайте наступні дії.

Крок 1. Застосуйте формулу, щоб отримати результат

Виберіть порожню клітинку (у цьому випадку D2), введіть одну з наступних формул і натисніть Що натомість? Створіть віртуальну версію себе у ключ до отримання результату. Виберіть цю клітинку та перетягніть її Ручка заповнення вниз, щоб отримати решту результатів. І результат відображається як загальні числа, як показано на наступному знімку екрана:

=(C2-B2)/B2

=C2/B2-1

Крок 2: Відформатуйте результат у відсотках

Щоб відформатувати числа у відсотках, залиште клітинки результатів вибраними, перейдіть до вибору Процентний стиль кнопка в Номер група під Головна Вкладка.

Результат

Зараз підраховано процентне зниження цих цін. Перегляньте знімок екрана нижче:

Розширені операції

Цей розділ демонструє різні ситуації, з якими ви можете зіткнутися під час обчислення відсоткової зміни між двома числами.

Дивовижний! Використання ефективних вкладок у Excel, таких як Chrome, Edge, Firefox і Safari!
Заощаджуйте 50% свого часу та зменшуйте тисячі клацань миші щодня!

Обчисліть відсоткову зміну з від’ємними числами

Під час обчислення процентних змін ви можете зіткнутися з від’ємним числом таким чином:

• Обидва числа від’ємні
• Одне з чисел від’ємне
  • Старе значення позитивне, нове значення негативне
  • Старе значення від'ємне, нове значення додатне

Давайте проілюструємо кожен із цих сценаріїв конкретними прикладами:

Обидва числа від’ємні

Стандартна формула для відсоткової зміни ефективна, навіть якщо обидва значення від’ємні.
Наприклад, розглянемо компанію, збитки якої зросли з 10,000 10,000 доларів США в минулому кварталі (старе значення -50,000 50,000) до XNUMX XNUMX доларів США в цьому кварталі (нове значення - -XNUMX XNUMX). Як показано нижче, використовуючи стандартну формулу (=(C2-B2)/B2).

Одне з чисел від’ємне

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

Старе значення позитивне, нове значення негативне

Стандартна формула відсоткової зміни також добре працює, коли нове значення від’ємне.

Наприклад, вартість акції змінилася з позитивної на негативну. Минулого місяця його оцінили в 10 доларів (стара вартість – 10). Цього місяця через падіння ринку його вартість впала до -10 доларів (нове значення - -10). Щоб обчислити відсоткову зміну між цими двома числами, я застосовую стандартну формулу (=(C2-B2)/B2), щоб отримати результат -200%. Це означає значне зменшення, що вказує на перехід від прибутку до втрати.

Старе значення від'ємне, нове значення додатне

Наприклад, бізнес переходить від збитків до прибутку. Минулого року цей малий бізнес мав збиток у розмірі 10000 10000 доларів США (старе значення – -20000 20000) і отримав прибуток у розмірі XNUMX XNUMX доларів США (нове значення – XNUMX XNUMX) цього року. Якщо для розрахунку зміни у відсотках застосувати стандартну формулу відсотка, результат буде неправильним.

Тут результат -300 є оманливим у фактичному сценарії. Це означає негативну зміну, маючи на увазі, що ситуація погіршилася, але насправді ситуація покращилася зі збитків на прибуток.

У цьому випадку потрібно скористатися Функція ABS щоб знаменник був додатним, це гарантує, що результат зміни у відсотках точно відображає зсув від негативного до позитивного. Ось нова формула:

=(C2-B2)/ABS(B2)

Тут результат 300% вказує на фактичну позитивну зміну від збитку до прибутку.

Обчисліть відсоткову зміну з нулем

Хоча розрахувати відсоткові зміни в 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, введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.

=B2/(1+A2)

Обчисліть нове значення на основі зміни у відсотках

У протилежній ситуації ви знаєте зміну у відсотках і початкове значення, і вам потрібно визначити нове значення. Наприклад, якщо продажі цього року становлять 10,000 20 доларів США, а ви прагнете, щоб продажі наступного року були на XNUMX% вищими, ніж цього року, як би ви розрахували продажі наступного року?

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

=Old value * (1 + Percentage Change)

Виберіть порожню клітинку, наприклад C2, введіть наступну формулу та натисніть Що натомість? Створіть віртуальну версію себе у ключ, щоб отримати результат.

=B2*(1+A2)

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

Статті по темі