Как да изчислим процентната промяна в Excel [Формула]

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

Ако работите много с числа в ежедневната си работа, често ще трябва да изчислявате проценти. Excel прави това наистина лесно, като ви помага да изчислявате различни видове проценти, като използвате различни формули и методи. Изчисляването на проценти в лист на Excel е подобно на изчисляването във вашите училищни математически документи, много е лесно за разбиране и използване.

Едно от най-често срещаните процентни изчисления, които хората правят в Excel, е да изчислят процентната промяна между две стойности за определен период от време. Процентната промяна или процентната вариация се използва за показване на скоростта на промяна (нарастване или спад) между две стойности от един период в друг. Може да се използва за финансови, статистически и много други цели.

Например, ако искате да намерите разликата между продажбите от миналата година и продажбите за тази година, можете да я изчислите с процентната промяна. В този урок ще разгледаме как да изчислим процентна промяна, както и процентно увеличение и намаляване между две числа в Excel.

Изчисляване на процентна промяна в Excel

Изчисляването на процентна промяна между две стойности е лесна задача. Просто трябва да намерите разликата между две числа и да разделите резултата с оригиналното число.

Има две различни формули, които можете да използвате за изчисляване на процентна промяна. Техният синтаксис е както следва:

=(нова_стойност – оригинална_стойност) / оригинална_стойност

или

=(нова стойност / оригинална стойност) – 1
  • нова_стойност – е текущата/крайната стойност на двете числа, които сравнявате.
  • предишна_стойност – е началната стойност на двете числа, които сравнявате за изчисляване на процентна промяна.

пример:

Нека да разгледаме този списък с хипотетични поръчки на плодове, като колона B съдържа броя на плодовете, поръчани миналия месец, а колона C съдържа броя на плодовете, поръчани този месец:

Например, поръчахте определен брой плодове миналия месец и този месец сте поръчали повече от това, което сте поръчали миналия месец, можете да въведете формулата по-долу, за да намерите процентната разлика между двете поръчки:

=(C2-B2)/B2

Горната формула ще бъде въведена в клетка D2, за да се намери процентната промяна между C2 (нова_стойност) и B2 (оригинална_стойност). След като въведете формулата в клетка, натиснете Enter, за да изпълните формулата. Получавате процентната промяна в десетичните стойности, както е показано по-долу.

Резултатът все още не е форматиран като процент. За да приложите процентен формат към клетка (или диапазон от клетки), изберете клетката(ите), след което щракнете върху бутона „Процентен стил“ в групата Числа на раздела „Начало“.

Десетичната стойност ще бъде преобразувана в процент.

Изчислете процентната промяна между две колони с числа

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

За да изчислите процентната промяна между две колони, трябва да въведете формулата в първата клетка на колоната с резултати и автоматично да попълните формулата надолу по цялата колона.

За да направите това, щракнете върху манипулатора за запълване (малкия зелен квадрат в долния десен ъгъл на клетката) на клетката с формула и я плъзнете надолу до останалите клетки.

Сега имате стойностите на процентната промяна за две колони.

Когато новата стойност е по-висока от първоначалната стойност, резултатът ще бъде положителен. Ако новата стойност е по-ниска от първоначалната стойност, тогава резултатът ще бъде отрицателен.

Както можете да видите, процентната промяна за „Ябълки“ се увеличава с 50%, така че стойността е положителна. Процентът за „Авокодо“ е намален с 14%, следователно резултатът е отрицателен (-14%).

Можете също да използвате персонализирано форматиране, за да подчертаете отрицателните проценти в червен цвят, така че да може лесно да бъде идентифициран, когато го търсите.

За да форматирате клетките, изберете клетките, които искате да форматирате, щракнете с десния бутон и изберете опцията „Форматиране на клетки“.

В прозореца за форматиране на клетки, който се показва, щракнете върху „По избор“ в долната част на лявото меню и въведете кода по-долу в текстовото поле „Тип:“:

0,00%;[червено]-0,00%

След това щракнете върху бутона „OK“, за да приложите форматирането.

Сега отрицателните резултати ще бъдат подчертани в червен цвят. Също така това форматиране увеличава броя на десетичните знаци за показване на точни проценти.

Можете също да използвате друга формула за изчисляване на процентна промяна между две стойности (поръчка за последния месец и поръчка за този месец):

=(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). За да направите това, трябва да направите тази клетка абсолютна препратка, като добавите знака $ към препратката към клетката, напр. $C$2. Така че формулата ще изглежда така:

=(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:

=АКО ГРЕШКА(стойност, стойност_ако_грешка)

Където,

  • стойност е стойността, препратката или формулата за проверка.
  • стойност_ако_грешка е стойността, която искаме да покажем, ако формулата върне стойност за грешка.

Нека приложим тази формула в един от примерите за грешки (#DIV/0! грешка):

=АКО ГРЕШКА((B6-C6)/B6,0%)

В тази формула аргументът „стойност“ е формулата за процентна промяна, а аргументът „value_if_error“ е „0%“. Когато формулата за процентна промяна срещне грешка (#DIV/0! грешка), функцията IFERROR ще покаже „0%“, както е показано по-долу.

Това е всичко, което трябва да знаете за изчисляването на процентна промяна в Excel.