Якщо ви працюєте з числами в Microsoft Excel, дуже ймовірно, що вам потрібно регулярно обчислювати зміни у відсотках. На щастя, Excel пропонує кілька формул і прийомів, які допоможуть вам легко обчислити зміну у відсотках або різницю між двома числами чи клітинками, і ми представимо їх у цьому посібнику.
Відсоткова зміна, також відома як відсоткова варіація або різниця, використовується для відображення різниці між двома значеннями. Іншими словами, відсоткова зміна – це спосіб сказати, наскільки щось збільшилося або зменшилося у відсотках порівняно з попереднім значенням.
Наприклад, ви можете використовувати його, щоб визначити відсоткове збільшення чи зменшення кількості учнів у школі між минулим і цим роком, або різниця в ціні продукту порівняно з минулим місяцем або кількість відвідувачів веб-сайту до та після реалізації маркетингової кампанії, тощо
У цій статті я покажу вам, як обчислити відсоткову зміну між двома числами в Excel і як підрахувати відсоткову зміну, коли у вас від’ємні або нульові значення.
-
Знайдіть відсоткову зміну в Excel.
- Формула 1.
- Формула 2.
- Форматування відсоткового результату (десяткові знаки, колір).
- Знайти відсоткову зміну, коли є від’ємні значення.
- Робота з відсотковими змінами та нульовими значеннями.
- Підрахуйте відсоткову зміну між клітинками в одному стовпці.
- Знайти відсоткову зміну контрольного номера/значення.
Як порахувати збільшення або зменшення у відсотках у Microsoft Excel.
Знайти відсоткову зміну між двома числами в Excel нескладно, і це можна зробити за допомогою однієї з наступних двох (2) формул:
- =(нове_значення–старе_значення)/старе_значення
- =(нове_значення/старе_значення)–1
Де:
● нове_значення: відноситься до останнього або остаточного значення двох чисел, що порівнюються.
● старе_значення: це початкове значення між двома числами для обчислення зміни у відсотках.
приклад: Припустімо, ви хочете знати, на який відсоток (у відсотках) ціна на список продуктів зросла або знизилася з попереднього місяця. Щоб виконати це завдання, вам потрібно обчислити відсоткову зміну кожного продукту між його поточною ціною (new_value) і попередньою ціною (old_value).
1. Відкрийте нову електронну таблицю в Excel.
2. Введіть заголовки стовпців у клітинки першого рядка таким чином:*
- У першому стовпчику клітинки A1 тип "Предмети" для опису товару.
- У другій колонці в клітинці B1 тип "Ціна минулого місяця" ("старе_значення").
- У третьому стовпці клітинки C1 введіть "Ціна цього місяця" (нове_значення")
3. Тепер під кожним заголовком введіть відповідну інформацію (тобто опис продукту, ціну попереднього місяця та ціну поточного місяця).
4.Додатково: Після заповнення цін на продукти ви можете, за бажанням, вказати тип валюти (наприклад, долари ($), євро (€) тощо), як показано нижче:
Клацніть на одну з літер стовпців із цінами (наприклад, «В»), а потім у додому натисніть вкладку стрілка поруч із доларом $ і виберіть формат валюти [наприклад, "$ англійська (Сполучені Штати)].
5. Потім у першій клітинці першого порожнього стовпця (наприклад, "D1" у цьому прикладі), введіть "Відсоткова зміна".
6. Тепер скористайтеся одним із двох (2) методів/формул нижче, щоб знайти відсоткову зміну.
Спосіб А. Обчисліть відсоткову зміну за допомогою: =(нове_значення–старе_значення)/старе_значення"
Щоб підрахувати відсоток у нашому прикладі за допомогою формули:
=(нове_значення–старе_значення)/старе_значення
a. Виберіть першу порожню клітинку під "Відсоткова зміна" (наприклад, клітинка "D2), типу нижче формула і натисніть Введіть (або клацніть в одній порожній клітинці). *
=(C2-B2)/B2
* Примітка: Тут старе значення (B2) віднімається від нового значення (C2) і результат ділиться на старе значення (B2).
b. Після натискання клавіші Enter відсоткова зміна буде представлена в десяткових значеннях, як показано нижче. Щоб змінити результат на відсотковий формат, виберіть клітинку D2 і натисніть кнопку Відсотковий стиль кнопка (%) в Номер група.
* Примітка: Ви також можете подати заявку Відсоток форматування в клітинку результату перед введенням формули.
в. Тепер у вас є відсоткова зміна між двома значеннями в клітинках B2 & C2, в клітинку D2 який 10%.
6. Після обчислення відсоткової зміни для першого продукту (наприклад, «смартфон» у цьому прикладі), ми можемо застосувати ту саму формулу, щоб переглянути відсоткову зміну для всіх інших елементів. Щоб зробити це:
Натисніть і перетягнути в Ручка заповнення (маленький зелений квадрат, розташований у нижньому правому куті клітинки формули) вниз, щоб скопіювати формулу між клітинками, які ви хочете заповнити.
Метод B. Обчислення відсоткової зміни за допомогою: =(нове_значення/старе_значення)–1"
Другий спосіб дізнатися відсоткову зміну в Excel - це за допомогою формули:
=(нове_значення/старе_значення)–1
напр. Щоб знайти відсоткову зміну між двома значеннями в клітинках B2 і C2, введіть таку формулу:
=(C2/B2)-1
Як бачите, друга формула може дати той самий результат, що й перша формула. Скопіюйте формулу в решту клітинок нижче для всіх продуктів, і готово!
Як відформатувати десяткові знаки та колір результату у відсотках.
Як ви побачите в нашому прикладі, результат у відсотках відображається без десяткових знаків і з від’ємним знаком, коли є відсоткове зменшення.
Якщо ви хочете відобразити точне збільшення або зменшення у відсотках (тобто з десятковими знаками) і відформатувати, наприклад, в від’ємні відсотки іншим кольором (наприклад, червоним), щоб ви могли легко зрозуміти різницю, виконайте наступне:
1. Потім виберіть усі клітинки з відсотковою зміною клацніть правою кнопкою миші і виберіть Формат клітинок з контекстного меню.
2. У вікні «Формат клітинок» натисніть на Custom у меню ліворуч введіть наведений нижче код у поле тип: текстове поле та натисніть В ПОРЯДКУ. *
0,00%; [червоний]-0,00%
* Примітки:
1. Якщо ви використовуєте Кома (,) як десятковий символ, замінити в крапка (.) з кома (,). наприклад: "0,00%;[червоний]-0,00%"
2. Щоб відформатувати додатну відсоткову зміну, наприклад, "Зелений"колір і негатив з"Червоний" тип: "[Зелений]0,00%; [Червоний]-0,00%"
3. Це відформатує всі зменшення відсоткових змін червоним кольором, а також відобразить два (2) знаки після коми для точних відсотків.
Як обчислити відсоткову зміну, якщо є від’ємні значення.
Якщо у ваших значеннях є від’ємні числа, звичайна формула відсоткової різниці може не дати правильного результату. Більш конкретно:
● Якщо обидва старий і нові цінності є негативний або позитивний, ви можете без проблем використовувати формули відсотка.
● Якщо старе значення позитивне і нове значення від'ємне, ви також можете використовувати наведені формули відсотка, щоб отримати правильну відповідь.
● Але, якщо старе значення від’ємне і нове значення позитивне, формула відсотка дасть вам неправильний результат, як ви можете бачити в клітинках "C4" & "C7" на скріншоті нижче.
Щоб вирішити цю проблему, вставте "ABS" щоб зробити знаменник додатним числом, використовуючи та застосовуючи цю формулу до всіх відсоткових комірок:
=(нове_значення – старе_значення) / ABS(старе_значення)
* Примітка: Це забезпечить правильну роботу формули незалежно від того, яке значення є від’ємним числом).
напр. У нашому прикладі формула буде такою: "=(B1-A1)/ABS(A1)"
Як знайти відсоткову зміну, якщо значення мають нуль (s)
Якщо ваші клітинки містять нульові значення в старому або новому значенні, ви можете зіткнутися з #DIV/0! повідомлення про помилку. Це відбувається тому, що в математиці не можна ділити жодне число на нуль.
Щоб вирішити цю проблему, ви можете вставити "ЯКЩО ПОМИЛКА" у вашій формулі Excel, тож якщо формула відсоткової зміни виявить будь-яку помилку (наприклад, помилка "#DIV/0!"), поверне "0%" в результаті:*
=IFERROR((нове_значення – старе_значення) / ABS(старе_значення),0)
* Примітка: Якщо ви використовуєте кому (,) як десятковий символ, замінити кома ","у формулі з крапкою з комою";" і використовуйте наведену нижче формулу:
=IFERROR((нове_значення – старе_значення) / ABS(старе_значення);0)
напр. У нашому прикладі (де ми використовуємо кому (,) як десятковий символ) формула буде такою: "=ЯКЩОПОМИЛКА((B1-A1)/ABS(A1);0)"
Як обчислити відсоткову зміну між клітинками одного стовпця.
Спосіб вимірювання зміни у відсотках між клітинками в одному стовпці майже такий самий, як описано вище для вимірювання зміни у відсотках між клітинками в одному рядку.
приклад: Щоб розрахувати відсоткове збільшення або зменшення вашого прибутку з місяця в місяць.
1. Відкрийте порожній документ Excel і введіть, наприклад, місяців і їхній прибуток.
2. Потім виберіть порожню комірку, у якій потрібно відобразити зміну у відсотках між першими двома місяцями (наприклад, "C3" у цьому прикладі) і введіть цю формулу, щоб підрахувати відсоткову зміну між першими двома місяцями (січень і лютий): *
=(B4-B3)/B3
* Де:B4 = заробіток за другий місяць, і B3 = заробіток за перший місяць.
3. Далі застосуйте формулу до решти стовпців (C3 до C14), а потім, якщо потрібно, також застосуйте спеціальне форматування кольору (як описано вище) для від’ємних відсотків.
Як знайти відсоткову зміну еталонного значення.
Якщо ви хочете обчислити зміну у відсотках відносно певної комірки (еталонного значення), додайте "$" поставити знак перед літерою стовпця та номером рядка клітинки посилання.
приклад: Розрахувати процентну зміну прибутку за лютий (B4) порівняно з січнем (B3) як базовий місяць формула має такий вигляд:
=(B4-$B$3)/$B$3
Потім застосуйте формулу до решти клітинок, щоб отримати відсоткову зміну для кожного місяця порівняно з січнем.
Це воно! Дайте мені знати, чи допоміг вам цей посібник, залишивши свій коментар про свій досвід. Будь ласка, поставте лайк і поділіться цим посібником, щоб допомогти іншим.