Če delate s številkami v programu Microsoft Excel, je zelo verjetno, da morate redno izračunavati spremembe v odstotkih. Na srečo Excel ponuja več formul in tehnik, ki vam pomagajo enostavno izračunati odstotno spremembo ali razliko med dvema številoma ali celicama in predstavili jih bomo v tej vadnici.
Odstotna sprememba, znana tudi kot odstotna variacija ali razlika, se uporablja za prikaz razlike med dvema vrednostma. Z drugimi besedami, odstotek spremembe je način, kako povedati, koliko se je nekaj povečalo ali zmanjšalo v odstotkih v primerjavi s svojo prejšnjo vrednostjo.
Z njim lahko na primer določite odstotek povečanja ali zmanjšanja števila učencev v šoli med lanskim in letošnjim letom oz. razlika v ceni izdelka v primerjavi s prejšnjim mesecem ali število obiskovalcev spletne strani pred in po izvedbi marketinške akcije, itd.
V tem članku vam bom pokazal, kako izračunati odstotek spremembe med dvema številkama v Excelu in kako prešteti odstotek spremembe, ko imate negativne ali ničelne vrednosti.
-
Poiščite odstotek spremembe v Excelu.
- Formula 1.
- Formula 2.
- Oblikovanje odstotnega rezultata (decimalna mesta, barva).
- Poiščite odstotek spremembe pri negativnih vrednostih.
- Delo z odstotno spremembo in ničelnimi vrednostmi.
- Preštejte odstotek spremembe med celicami v istem stolpcu.
- Poiščite odstotek spremembe referenčne številke/vrednosti.
Kako prešteti odstotek povečanja ali zmanjšanja v Microsoft Excelu.
Iskanje odstotne spremembe med dvema številkama v Excelu ni težko in ga je mogoče storiti z uporabo ene od naslednjih dveh (2) formul:
- =(nova_vrednost–stara_vrednost)/stara_vrednost
- =(nova_vrednost/stara_vrednost)–1
Kje:
● nova_vrednost: se nanaša na najnovejšo ali končno vrednost dveh primerjanih števil.
● stara_vrednost: je začetna vrednost med obema številoma v primerjavi za izračun odstotne spremembe.
primer: Recimo, da želite vedeti, za koliko odstotkov (v odstotkih) se je cena seznama izdelkov zvišala ali znižala od prejšnjega meseca. Če želite dokončati to nalogo, morate izračunati odstotek spremembe vsakega izdelka med njegovo trenutno ceno (new_value) in njegovo prejšnjo ceno (old_value).
1. Odprite novo preglednico v Excelu.
2. Vnesite naslove stolpcev v celice prve vrstice, kot sledi:*
- V prvem stolpcu v celici A1 tip "Predmeti" za opis izdelka.
- V drugem stolpcu v celici B1 tip "Cena prejšnjega meseca" ("stara_vrednost").
- V tretji stolpec v celico C1 vnesite "Cena tega meseca" (nova_vrednost")
3. Zdaj pod vsako rubriko vnesite ustrezne informacije (tj. opis izdelka, ceno prejšnjega meseca in ceno tekočega meseca).
4.Izbirno: Po izpolnitvi cen izdelkov lahko po želji določite vrsto valute (npr. dolarji ($), evri (€), itd.), kot sledi:
Kliknite na eno od črk stolpcev s cenami (npr. "B") in nato v domov kliknite zavihek puščica poleg dolarja $ in izberite obliko valute [npr. "$ angleščina (Združene države)].
5. Nato v prvi celici prvega praznega stolpca (npr.D1" v tem primeru), vnesite "Odstotek spremembe".
6. Zdaj uporabite eno od dveh (2) metod/formul spodaj, da poiščete spremembo v odstotkih.
Metoda A. Izračunajte odstotek spremembe z: =(nova_vrednost–stara_vrednost)/stara_vrednost"
Če želite prešteti odstotek v našem primeru z uporabo formule:
=(nova_vrednost–stara_vrednost)/stara_vrednost
a. Izberite prvo prazno celico pod "Odstotek spremembe" (npr. celica "D2), vrsta spodaj formula in pritisnite Vnesite (ali kliknite eno prazno celico). *
=(C2-B2)/B2
* Opomba: Tukaj je stara vrednost (B2) se odšteje od nove vrednosti (C2) in rezultat se deli s staro vrednostjo (B2).
b. Ko pritisnete Enter, bo odstotek spremembe v decimalnih vrednostih, kot je prikazano spodaj. Če želite spremeniti rezultat v odstotno obliko, izberite celico D2 in kliknite Odstotni slog gumb (%) v številka skupina.
* Opomba: Lahko se tudi prijavite Odstotek formatiranje v celico z rezultati, preden vnesete formulo.
c. Zdaj imate odstotek spremembe med dvema vrednostma v celicah B2 & C2, v celici D2 kateri je 10%.
6. Po izračunu odstotne spremembe za prvi izdelek (npr. »»pametni telefon« v tem primeru) lahko uporabimo isto formulo za ogled odstotne spremembe za vse druge elemente. Če želite to narediti:
Kliknite in povlecite the Ročaj za polnjenje (majhen zeleni kvadrat v spodnjem desnem kotu celice s formulo) navzdol, da kopirate formulo čez celice, ki jih želite zapolniti.
Metoda B. Izračun odstotne spremembe z: =(nova_vrednost/stara_vrednost)–1"
Druga metoda za ugotavljanje odstotne spremembe v Excelu je uporaba formule:
=(nova_vrednost/stara_vrednost)–1
npr. Če želite najti odstotek spremembe med obema vrednostma v celicah B2 in C2, vnesite naslednjo formulo:
=(C2/B2)-1
Kot lahko vidite, nam lahko druga formula da enak rezultat kot prva formula. Kopirajte formulo v preostale celice spodaj za vse izdelke in končali ste!
Kako oblikovati decimalna mesta in barvo odstotka rezultata.
Kot boste videli v našem primeru, je rezultat v odstotkih prikazan brez decimalnih mest in z negativnim predznakom, ko pride do odstotnega zmanjšanja.
Če želite prikazati natančen odstotek povečanja ali zmanjšanja (tj. z decimalnimi mesti) in oblikovati npr. the negativne odstotke v drugi barvi (npr. rdeči), da boste lažje razumeli razliko, naredite naslednje:
1. Nato izberite vse celice s spremembo v odstotkih desni klik in izberite Oblikuj celice iz kontekstnega menija.
2. V oknu »Oblikuj celice« kliknite na Po meri možnost v levem meniju vnesite spodnjo kodo v Tip: besedilno polje in kliknite V REDU. *
0,00 %; [rdeča]-0,00 %
* Opombe:
1. Če uporabljate Vejica (,) kot decimalni simbol, zamenjati the pika (.) z vejica (,). npr.: "0,00 %; [rdeča]-0,00 %"
2. Za oblikovanje pozitivne odstotne spremembe v npr. "Zelena" barva in negativ z "rdeča" vrsta: "[Zelena]0,00 %; [Rdeča]-0,00 %"
3. To bo formatiralo vse padajoče odstotne spremembe v rdeči barvi in prikazalo tudi dve (2) decimalni mesti za natančne odstotke.
Kako izračunati odstotek spremembe pri negativnih vrednostih.
Če imate v svojih vrednostih nekaj negativnih števil, vam običajna formula odstotne razlike morda ne bo dala pravilnega rezultata. Natančneje:
● Če oba star in nove vrednote so negativno oz pozitivno, lahko brez težav uporabljate formule za odstotke.
● Če je stara vrednost je pozitivna in nova vrednost je negativna, lahko uporabite tudi podane formule za odstotke, da dobite pravilen odgovor.
● Vendar, če stara vrednost je negativna in nova vrednost je pozitivna, vam bo odstotna formula dala napačen rezultat, kot lahko vidite v celicah "C4" & "C7« na spodnjem posnetku zaslona.
Če želite odpraviti to težavo, vstavite »ABS" funkcijo, da imenovalec postane pozitivno število, z uporabo in uporabo te formule za vse odstotne celice:
=(nova_vrednost – stara_vrednost) / ABS(stara_vrednost)
* Opomba: To bo zagotovilo pravilno delovanje formule ne glede na to, katera vrednost je negativno število).
npr. V našem primeru bo formula: "=(B1-A1)/ABS(A1)"
Kako najti odstotek spremembe, če imate vrednosti z nič (s)
Če vaše celice vsebujejo nič vrednosti v stari ali novi vrednosti, lahko naletite na #DIV/0! obvestilo o napaki. To se zgodi, ker v matematiki ne morete deliti nobenega števila z ničlo.
Če želite odpraviti to težavo, lahko vstavite »IFERROR" funkcijo v vaši Excelovi formuli, tako da če formula za odstotno spremembo naleti na kakršno koli napako (npr. napaka »#DIV/0!«), vrne "0%" kot rezultat:*
=IFERROR((nova_vrednost – stara_vrednost) / ABS(stara_vrednost),0)
* Opomba: Če uporabljate vejico (,) kot decimalni simbol, zamenjati vejica","v formuli s podpičjem";" in uporabite spodnjo formulo:
=IFERROR((nova_vrednost – stara_vrednost) / ABS(stara_vrednost);0)
npr. V našem primeru (kjer uporabljamo vejico (,) kot decimalni simbol) bo formula: "=IFERROR((B1-A1)/ABS(A1);0)"
Kako izračunati odstotek spremembe med celicami istega stolpca.
Način merjenja odstotne spremembe med celicami v istem stolpcu je skoraj enak, kot je opisano zgoraj za merjenje odstotne spremembe med celicami v isti vrstici.
primer: Za izračun odstotka povečanja ali zmanjšanja vašega dobička iz meseca v mesec.
1. Odprite prazen Excel dokument in vnesite npr. mesece in njihov dobiček.
2. Nato izberite prazno celico, kjer želite prikazati odstotek spremembe med prvima dvema mesecema (npr.C3" v tem primeru) in vnesite to formulo za štetje odstotnih sprememb med prvima dvema mesecema (januar in februar): *
=(B4-B3)/B3
* Kje:B4 = zaslužek drugega meseca, in B3 = zaslužek prvega meseca.
3. Nato uporabite formulo za preostale stolpce (C3 do C14) in nato, če želite, uporabite tudi barvno oblikovanje po meri (kot je opisano zgoraj) za negativne odstotke.
Kako najti odstotek spremembe referenčne vrednosti.
Če želite izračunati odstotek spremembe glede na določeno celico (referenčna vrednost), dodajte "$" znak pred črko stolpca in številko vrstice referenčne celice.
primer: Za izračun odstotne spremembe dobička za februar (B4) v primerjavi z januarjem (B3) kot referenčni mesec je formula:
=(B4-$B$3)/$B$3
Nato uporabite formulo za preostale celice, da dobite odstotek spremembe za vsak mesec v primerjavi z januarjem.
To je to! Povejte mi, če vam je ta vodnik pomagal, tako da komentirate svojo izkušnjo. Všečkajte in delite ta vodnik, da pomagate drugim.