Як розрахувати відсоткові зміни в Excel [Формула]

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

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

Одним із найпоширеніших обчислень у відсотках, які люди роблять у Excel, є обчислення відсоткової зміни між двома значеннями за певний період часу. Відсоткова зміна або відсоткова дисперсія використовується для відображення швидкості зміни (зростання або зниження) між двома значеннями від одного періоду до іншого. Його можна використовувати для фінансових, статистичних та багатьох інших цілей.

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

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

Обчислення відсоткової зміни між двома значеннями є простим завданням. Вам просто потрібно знайти різницю між двома числами і поділити результат на вихідне число.

Є дві різні формули, які можна використовувати для обчислення відсоткової зміни. Їх синтаксис такий:

=(нове_значення – вихідне_значення) / вихідне_значення

або

=(нове значення / вихідне значення) – 1
  • нове_значення – поточне/кінцеве значення двох чисел, які ви порівнюєте.
  • попереднє_значення – початкове значення двох чисел, які ви порівнюєте для розрахунку зміни у відсотках.

приклад:

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

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

=(C2-B2)/B2

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

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

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

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

Тепер ми знаємо відсоткову зміну між двома замовленнями для Apple, давайте знайдемо відсоткову зміну для всіх елементів.

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

Для цього клацніть маркер заповнення (маленький зелений квадрат у нижньому правому куті клітинки) клітинки формули та перетягніть її вниз до решти клітинок.

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

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

Як бачите, відсоткова зміна для «Яблука» збільшується на 50%, отже значення є додатним. Відсоток для «Авокодо» зменшено на 14%, отже, результат негативний (-14%).

Ви також можете використовувати користувацьке форматування, щоб виділити негативні відсотки червоним кольором, щоб їх можна було легко ідентифікувати, коли ви їх шукаєте.

Щоб відформатувати клітинки, виділіть клітинки, які потрібно відформатувати, клацніть правою кнопкою миші та виберіть параметр «Форматувати клітинки».

У вікні Format Cells, що з’явиться, натисніть «Custom» внизу лівого меню та введіть наведений нижче код у текстовому полі «Type:»:

0,00%;[Червоний]-0,00%

Потім натисніть кнопку «ОК», щоб застосувати форматування.

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

Ви також можете використовувати іншу формулу для обчислення відсоткової зміни між двома значеннями (замовлення минулого місяця та замовлення цього місяця):

=(C2/B2)-1

Ця формула ділить new_value (C2) на початкове значення (B2) і віднімає «1» від результату, щоб знайти зміну у відсотках.

Розрахунок відсоткової зміни з часом

Ви можете обчислити відсоток зміни періоду до періоду (зміна від місяця до місяця), щоб зрозуміти швидкість зростання або зниження від одного періоду до наступного (для кожного місяця). Це дуже корисно, якщо ви хочете знати, як змінюється відсоток за кожен місяць або рік за певний період часу.

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

Використовуйте цю загальну формулу, щоб знайти відсоткову зміну з часом:

=((Поточне_значення/Початкове_значення)/Початкове_значення)*N

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

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

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

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

Обчислення відсотка зростання/зміни між рядками

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

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

=(B3-B2)/B2

Оскільки нам потрібно знайти відсоток зростання за лютий (B3) від ціни січня (B2), нам потрібно залишити перший рядок порожнім, оскільки ми не порівнюємо січень із попереднім місяцем. Введіть формулу в клітинку C3 і натисніть Enter.

Потім скопіюйте формулу в інші клітинки, щоб визначити відсоткову різницю між кожним місяцем.

Ви також можете обчислити відсоткову зміну для кожного місяця порівняно з січнем (B2). Щоб зробити це, вам потрібно зробити цю клітинку абсолютним посиланням, додавши до посилання на клітинку знак $, напр. $2 C$. Отже, формула буде виглядати так:

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

Як і раніше, ми пропускаємо першу клітинку і вводимо формулу в клітинку C3. Коли ви скопіюєте формулу до решти клітинок, абсолютне посилання ($B$2) не зміниться, а відносне посилання (B3) зміниться на B4, B5 тощо.

Ви отримаєте відсоток інфляції або дефляції за кожен місяць порівняно з січнем.

Розрахунок відсоткового збільшення в Excel

Обчислення відсоткового збільшення схоже на обчислення відсоткової зміни в Excel. Відсоткове збільшення – це швидкість збільшення до початкового значення. Щоб обчислити відсоткове збільшення, вам знадобляться два числа: вихідне число і число New_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%» замість нього, коли виникає помилка. Для цього вам потрібно скористатися функцією «IFERROR», яка повертає користувацький результат, коли формула видає помилку.

Синтаксис функції IFERROR:

=IFERROR(значення, значення_якщо_помилка)

де,

  • значення це значення, посилання або формула для перевірки.
  • значення_якщо_помилка це значення, яке ми хочемо відобразити, якщо формула повертає значення помилки.

Давайте застосуємо цю формулу в одному з прикладів помилок (#DIV/0! помилка):

=ЯКЩО ПОМИЛКА((B6-C6)/B6,0%)

У цій формулі аргумент «value» — це формула зміни відсотка, а аргумент «value_if_error» — «0%». Коли формула зміни відсотка зустрічає помилку (#DIV/0! помилка), функція IFERROR буде відображати «0%», як показано нижче.

Це все, що вам потрібно знати про обчислення відсоткової зміни в Excel.