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

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

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

Але не лякайтеся, це не так уже й складно. У Microsoft Excel цей процес зробили досить простим.

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

Що ж, починаємо!

Розраховуємо зміну у відсотках.

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

Скажімо, ціна товару була 60 карбованців, а стала 80 карбованців. Тепер треба вирахувати, наскільки ціна піднялася у відсотках.

Погляньмо на приклади.

Розрахунок процентної зміни, коли ціна товару піднялася

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

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

У нашому випадку це обчислюється таким чином:

= Різниця цін / Стара ціна

А так вже обчислюється саме в нашому випадку:

=(B2-A2)/A2

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

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

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

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

Якщо раптом вам подобається працювати з дробами, а не відсотками. Ви можете регулювати кількість цифр після коми таким чином:

У тому ж розділі «Число» натискайте на ці кнопки, як вказано на малюнку нижче.

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

Зверніть увагу, що, як і в звичайній математиці, Excel спочатку виконує дію в дужках.

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

Розрахунок процентної зміни, коли ціна товару впала

І тут все аналогічно.

Припустимо, цифри у цінах у нас залишилися ті ж самі, просто помінялися місцями.

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

Сама формула залишиться такою ж:

=(B2-A2)/A2

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

Але в результаті ми отримаємо вже число зі знаком «-». Це правильно, тому що ціна впала, а значить, вона знизилася на якийсь відсоток (у нашому випадку на 25%).

Вираховуємо нову вартість після відсоткової зміни

Допустимо, у нас є таблиця з такими даними. Нам слід визначити нове значення.

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

Ось як ми розрахуємо нове значення:

=A2*(1+B2)

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

Щоб розрахувати «Нове значення» для всіх елементів у стовпчику C, скопіюйте комірку C2 та вставте у всі комірки у стовпчику C.

У цій формулі спочатку обчислюється загальний відсоток (наприклад, B2 = 30%, отже, щоб отримати нове значення, A2 * (130%)), а після вже множиться на старі значення, так ми отримуємо результат.

Це буде актуально як позитивної зміни у відсотках, так негативного.

Відсоткова зміна стовпчика із заздалегідь відомим відсотком.

Припустимо, ми маємо табличку, де вже є відомі «Старі значення». Також ми знаємо, що нове значення має бути більше на 10%.

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

У такому разі зараз ми збільшимо всі старі значення на 10%.

Щоб зробити це, використовуємо цю формулу:

=A2*(1+10%)

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

Таким чином, ми отримуємо нові значення. Щодо моменту у формулі (1+10%), це робиться для того, щоб осередок A2 помножився не на 10%, а на 110%, так ми отримуємо нове значення.

Так само і зі зменшенням значення змінюється тільки знак «+» у формулі на «-», ось:

= A2 * (1-10%)

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

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

У ситуації, коли ми маємо значення, ми можемо помістити у формулу посилання на комірку D2(яка містить у собі відсоток), який потрібно змінити саме значення.

Допустимо, ми маємо відсоток у D2, тоді давайте використовуємо цю формулу:

=A2*(1+$D$2)

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

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

Розрахунок зміни у відсотках у прикладі із числом 0.

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

Допустимо, ваше старе число дорівнює 0, а нове – 200. Що буде тоді? Якою буде зміна у відсотках?

Давайте спробуємо застосувати щойно отримані знання і складемо таку ж формулу, яку ми складали попередніх прикладах. Вийде так:

=(100-0)/0

Всі ми знаємо, що в математиці не можна ділити на 0. Тому вам просто видасть помилку, результату не буде.

І справа тут не в Excel, а в математиці.

Насправді такі ситуації вже давно вирішені, люди просто домовилися, у таких випадках вважати, що зміна у відсотках дорівнює 100%.

Це досить легко запам'ятати і зрозуміти, але що робити, якщо ситуація протилежна?

Наприклад, значення змінюються не від 0 до 100, а навпаки, від 100 до 0. Яка зміна у відсотках буде тоді?

Ми можемо скласти формулу і подивитися, якою буде відповідь.

Це буде виглядати так:

=(100-0)/100

Excel відповість вам, що відсоткова зміна дорівнює 100. І це правильно.

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

Формули зміни у відсотках із негативними числами

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

У таких ситуаціях буває два варіанти:

  1. Або обидва числа негативні.
  2. Або негативне лише одне, а друге — позитивне.

Ми розглянемо обидва випадки.

Обидва числа негативні

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

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

Але нам потрібно вирахувати, яка буде зміна у відсотках.

З радістю повідомляю вам, коли обидва числа є негативними, ви можете застосувати знання, отримані в попередніх розділах статті. Грубо кажучи, використати те, що ми вже робили.

Ось так:

=(A2-B2)/A2

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

І це буде правильним підходом.

Одне число негативне, а друге – позитивне

У цьому випадку, знову ж таки, у нас 2 варіанти:

  1. Старе – позитивне, а нове значення негативне.
  2. Старе негативне, а нове значення позитивне.

Спочатку розберемося з першим випадком.

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

У цьому випадку нам знову підійде стара формула. Така сама як у прикладі, коли обидва числа негативні.

Правильно ось так:

=(B2-A2)/A2

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

А тепер давайте розбиратися з другим варіантом.

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

Тут потрібна одна невелика зміна.

Допустимо, старе число “-10”, а нове “10”.

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

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

Виправити це не складно, у цьому прикладі, якщо використати старий метод, ми отримаємо «-200%». Рішення зовсім поруч, потрібно просто прибрати знак “-” і все. Відповідь буде – “200%”.

Але Excel має окрему функцію для таких ситуацій, ви можете включити її у формулу:

=(B2-A2)/ABS(A2)

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

Ця функція перетворює негативні числа на позитивні. От і все.

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

Сподіваюся, вам допомогло це керівництво!

nBook
Додати коментар