Kako izračunati postotak promjene u Excelu [Formula]

Ovaj vodič vam pokazuje kako izračunati postotak promjene između brojeva, stupaca, redaka kao i postotak povećanja i smanjenja u Excelu.

Ako u svakodnevnom radu puno radite s brojevima, često ćete morati izračunati postotke. Excel to čini vrlo jednostavnim jer vam pomaže izračunati različite vrste postotaka pomoću različitih formula i metoda. Izračunavanje postotaka u Excel listu slično je izračunavanju u vašim školskim matematičkim radovima, vrlo je jednostavno za razumjeti i koristiti.

Jedan od najčešćih izračuna postotka koji ljudi rade u Excelu je izračunavanje postotne promjene između dvije vrijednosti tijekom određenog vremenskog razdoblja. Postotna promjena ili postotna varijacija koristi se za prikaz stope promjene (rasta ili pada) između dviju vrijednosti iz jednog razdoblja u drugo. Može se koristiti u financijske, statističke i mnoge druge svrhe.

Na primjer, ako želite pronaći razliku između prodaje prošle godine i prodaje ove godine, možete je izračunati s postotnom promjenom. U ovom vodiču ćemo pokriti kako izračunati postotak promjene, kao i postotak povećanja i smanjenja između dva broja u Excelu.

Izračunavanje postotnih promjena u Excelu

Izračunavanje postotne promjene između dvije vrijednosti jednostavan je zadatak. Vi samo trebate pronaći razliku između dva broja i podijeliti rezultat s izvornim brojem.

Postoje dvije različite formule koje možete koristiti za izračunavanje postotnih promjena. Njihova sintaksa je sljedeća:

=(nova_vrijednost – izvorna_vrijednost) / izvorna_vrijednost

ili

=(nova vrijednost / izvorna vrijednost) – 1
  • nova_vrijednost – je trenutna/konačna vrijednost dva broja koja uspoređujete.
  • prethodna_vrijednost – je početna vrijednost dvaju brojeva koje uspoređujete za izračun postotne promjene.

Primjer:

Pogledajmo ovaj popis hipotetskih narudžbi voća gdje stupac B sadrži broj voća naručenog prošlog mjeseca, a stupac C sadrži broj voća naručenih ovog mjeseca:

Na primjer, prošli ste mjesec naručili određeni broj voća, a ovaj mjesec ste naručili više od onoga što ste naručili prošli mjesec, možete unijeti formulu ispod da biste pronašli postotnu razliku između dvije narudžbe:

=(C2-B2)/B2

Gornja formula bit će unesena u ćeliju D2 kako bi se pronašla postotna promjena između C2 (nova_vrijednost) i B2 (izvorna_vrijednost). Nakon što upišete formulu u ćeliju, pritisnite Enter da biste izvršili formulu. Dobivate postotak promjene decimalnih vrijednosti kao što je prikazano u nastavku.

Rezultat još nije oblikovan kao postotak. Da biste primijenili format postotka na ćeliju (ili raspon ćelija), odaberite ćeliju(e), a zatim kliknite gumb "Stil postotka" u grupi Brojevi na kartici "Početna".

Decimalna vrijednost će se pretvoriti u postotak.

Izračunajte postotak promjene između dva stupca brojeva

Sada, znamo postotak promjene između dvije narudžbe za jabuke, pronađimo postotak promjene za sve artikle.

Da biste izračunali postotak promjene između dva stupca, trebate unijeti formulu u prvu ćeliju stupca rezultata i automatski ispuniti formulu niz cijeli stupac.

Da biste to učinili, kliknite ručicu za popunjavanje (mali zeleni kvadrat u donjem desnom kutu ćelije) ćelije formule i povucite je prema dolje do ostalih ćelija.

Sada ste dobili vrijednosti postotka promjene za dva stupca.

Kada je nova vrijednost viša od izvorne vrijednosti, postotak rezultata bi bio pozitivan. Ako je vrijednost nove vrijednosti niža od izvorne vrijednosti, rezultat bi bio negativan.

Kao što vidite, postotak promjene za 'Jabuke' je povećan za 50%, pa je vrijednost pozitivna. Postotak za 'Avocodo' je smanjen za 14%, pa je rezultat negativan (-14%).

Također možete koristiti prilagođeno oblikovanje za isticanje negativnih postotaka crvenom bojom, tako da se može lako prepoznati kada ga tražite.

Da biste formatirali ćelije, odaberite ćelije koje želite formatirati, kliknite desnom tipkom miša i odaberite opciju "Formatiraj ćelije".

U prozoru Format Cells koji se pojavi kliknite na "Prilagođeno" na dnu lijevog izbornika i upišite donji kod u tekstualni okvir "Vrsta:":

0,00%; [crveno] -0,00%

Zatim kliknite gumb "U redu" da biste primijenili formatiranje.

Sada će negativni rezultati biti istaknuti crvenom bojom. Također, ovo oblikovanje povećava broj decimalnih mjesta kako bi se prikazali točni postoci.

Također možete koristiti drugu formulu za izračunavanje postotne promjene između dvije vrijednosti (narudžba prošlog mjeseca i ovog mjeseca):

=(C2/B2)-1

Ova formula dijeli new_value (C2) s izvornom vrijednošću (B2) i oduzima '1' od rezultata kako bi se pronašla postotna promjena.

Izračunavanje postotne promjene tijekom vremena

Možete izračunati postotak promjene od razdoblja do razdoblja (promjena iz mjeseca u mjesec) da biste razumjeli stopu rasta ili pada iz jednog razdoblja u sljedeće (za svaki mjesec). Ovo je vrlo korisno kada želite znati postotak promjene za svaki mjesec ili godinu u određenom vremenskom razdoblju.

U donjem primjeru imamo cijenu voća za mjesec ožujak u stupcu B i cijenu za mjesec srpanj, 5 mjeseci kasnije.

Upotrijebite ovu generičku formulu da pronađete postotak promjene tijekom vremena:

=((Trenutačna_vrijednost/Izvorna_vrijednost)/Izvorna_vrijednost)*N

Ovdje N predstavlja broj razdoblja (godine, mjeseci, itd.) između dvije vrijednosti početne i tekuće.

Na primjer, ako želite razumjeti stopu inflacije ili deflacije cijena za svaki mjesec više od 5 mjeseci u gornjem primjeru, možete sljedeću formulu:

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

Ova formula je slična formuli koju smo koristili prije, osim što trebamo podijeliti vrijednost postotne promjene s brojem mjeseci između dva mjeseca (5).

Izračunavanje postotnog rasta/promjene između redaka

Recimo da imate jedan stupac brojeva koji navodi mjesečnu cijenu benzina za više od godinu dana.

Sada, ako želite izračunati postotak rasta ili pada između redaka tako da možete razumjeti promjene cijena iz mjeseca u mjesec, pokušajte sljedeću formulu:

=(B3-B2)/B2

Budući da moramo pronaći postotak rasta za veljaču (B3) od cijene za siječanj (B2), prvi red moramo ostaviti praznim jer ne uspoređujemo siječanj s prethodnim mjesecom. Unesite formulu u ćeliju C3 i pritisnite Enter.

Zatim kopirajte formulu u ostale ćelije da odredite postotnu razliku između svakog mjeseca.

Također možete izračunati postotak promjene za svaki mjesec u odnosu na siječanj (B2). Da biste to učinili, morate tu ćeliju učiniti apsolutnom referencom dodavanjem znaka $ referenci ćelije, npr. $C$2. Dakle, formula bi izgledala ovako:

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

Kao i prije, preskočimo prvu ćeliju i unesemo formulu u ćeliju C3. Kada kopirate formulu u ostale ćelije, apsolutna referenca ($B$2) se ne mijenja, dok će se relativna referenca (B3) promijeniti u B4, B5 i tako dalje.

Dobit ćete postotak stope inflacije ili deflacije za svaki mjesec u odnosu na siječanj.

Izračunavanje postotnog povećanja u Excelu

Izračunavanje postotnog povećanja slično je izračunavanju postotne promjene u Excelu. Postotak povećanja je stopa povećanja do početne vrijednosti. Trebat će vam dva broja za izračunavanje postotka povećanja, izvorni broj i broj New_number.

Sintaksa:

Postotak povećanja = (Novi_broj - Izvorni_broj) / Izvorni_broj

Sve što trebate učiniti je oduzeti izvorni (prvi) broj od novog (drugog) broja i rezultat podijeliti s izvornim brojem.

Na primjer, imate popis računa i njihovih iznosa za dva mjeseca (travanj i svibanj). Ako su vam računi za travanj povećani u mjesecu svibnju, koliki je onda postotak povećanja od travnja do svibnja? Možete koristiti formulu u nastavku kako biste saznali:

=(C2-B2)/B2

Ovdje oduzimamo račun za struju u svibnju (C2) od računa za mjesec travanj (B2) i rezultat dijelimo s računom za mjesec travanj. Zatim kopirajte formulu u druge ćelije pomoću ručke za popunjavanje.

Ako dobijete pozitivan rezultat (24,00 USD), postotak se povećava između dva mjeseca, a ako dobijete negativan rezultat (npr. -13,33 USD), tada se postotak zapravo smanjuje, a ne povećava.

Izračunavanje postotnog smanjenja u Excelu

Pogledajmo sada kako izračunati postotak smanjenja između brojeva. Izračun postotka smanjenja vrlo je sličan izračunu postotka povećanja. Jedina razlika je u tome što će novi broj biti manji od izvornog broja.

Formula je skoro identična izračunu postotka povećanja, osim što u ovom slučaju oduzimate izvornu vrijednost (prvi broj) od nove vrijednosti (drugi broj) prije nego što rezultat podijelite s izvornom vrijednošću.

Sintaksa:

Postotak smanjenja = (Izvorni_broj - Novi_broj) / Izvorni_broj

Pretpostavimo da imate ovaj popis uređaja za pohranu i njihove cijene za dvije godine (2018. i 2020.).

Primjerice, cijene uređaja za pohranu podataka bile su više u 2018. godini, a cijene su smanjene u 2020. godini. Koliki je postotak pada cijena za 2020. u odnosu na 2018.? Možete koristiti ovu formulu da saznate:

=(B2-C2)/B2

Ovdje oduzimamo cijenu iz 2018. (B2) od cijene iz 2020. (C2) i dijelimo zbroj s cijenom iz 2018. godine. Zatim kopiramo formulu u druge ćelije kako bismo pronašli postotak smanjenja za druge uređaje.

Ako dobijete pozitivan rezultat (20,00 USD), postotak se smanjio između dvije godine, a ako dobijete negativan rezultat (npr. -13,33 USD), tada se postotak zapravo povećava, a ne smanjuje.

Uobičajene pogreške koje dobivate kada koristite gore navedene formule

Kada koristite gornje formule, povremeno možete naići na ovaj popis uobičajenih pogrešaka:

  • #DIV/0!: Ova se pogreška javlja kada pokušate podijeliti broj s nulom (0) ili ćelijom koja je prazna. Npr. Vrijednost B6 u formuli ‘=(B6-C6)/B6’ jednaka je nuli, stoga je #DIV/0! pogreška.

Kada unesete 'nula' u formatu valute, bit će zamijenjena crticom (-) kao što je prikazano gore.

  • #VRIJEDNOST: Excel prikazuje ovu pogrešku kada ste unijeli vrijednost koja nije podržana vrsta ili kada ćelije ostanu prazne. To se često može dogoditi kada ulazna vrijednost sadrži razmake, znakove ili tekst umjesto brojeva.
  • NUM!: Ova se pogreška događa kada formula sadrži nevažeći broj koji rezultira brojem koji je prevelik ili premalen da bi se prikazao u Excelu.

Pretvorite grešku u nulu

Ali postoji način na koji se možemo riješiti ovih pogrešaka i prikazati "0%" na njegovo mjesto kada se pogreška dogodi. Da biste to učinili, trebate koristiti funkciju 'IFERROR', koja vraća prilagođeni rezultat kada formula proizvede pogrešku.

Sintaksa funkcije IFERROR:

=IFERROR(vrijednost, vrijednost_ako_pogreška)

Gdje,

  • vrijednost je vrijednost, referenca ili formula za provjeru.
  • vrijednost_ako_pogreška je vrijednost koju želimo prikazati ako formula vrati vrijednost pogreške.

Primijenimo ovu formulu u jednom od primjera pogreške (#DIV/0! pogreška):

=IFERROR((B6-C6)/B6,0%)

U ovoj formuli, argument 'vrijednost' je formula postotka promjene, a argument 'value_if_error' je '0%'. Kada formula postotka promjene naiđe na pogrešku (#DIV/0! pogreška), funkcija IFERROR bi prikazala "0%" kao što je prikazano u nastavku.

To je sve što trebate znati o izračunu postotne promjene u Excelu.