Jak obliczyć zmianę procentową w programie Excel [Formuła]

Ten samouczek pokazuje, jak obliczyć procentową zmianę między liczbami, kolumnami, wierszami, a także procentowy wzrost i spadek w programie Excel.

Jeśli w codziennej pracy dużo pracujesz z liczbami, często będziesz musiał obliczyć wartości procentowe. Program Excel bardzo to ułatwia, pomagając obliczać różne rodzaje wartości procentowych przy użyciu różnych formuł i metod. Obliczanie procentów w arkuszu Excela jest podobne do obliczania w szkolnych pracach matematycznych, jest bardzo łatwe do zrozumienia i użycia.

Jednym z najczęstszych obliczeń procentowych, jakie ludzie wykonują w programie Excel, jest obliczenie zmiany procentowej między dwiema wartościami w określonym czasie. Zmiana procentowa lub wariancja procentowa służy do pokazania tempa zmiany (wzrostu lub spadku) między dwiema wartościami z jednego okresu do drugiego. Może być używany do celów finansowych, statystycznych i wielu innych.

Na przykład, jeśli chcesz znaleźć różnicę między sprzedażą z ubiegłego roku a sprzedażą w tym roku, możesz obliczyć ją ze zmianą procentową. W tym samouczku omówimy, jak obliczyć zmianę procentową, a także procentowy wzrost i spadek między dwiema liczbami w programie Excel.

Obliczanie zmiany procentowej w Excelu

Obliczanie zmiany procentowej między dwiema wartościami jest łatwym zadaniem. Wystarczy znaleźć różnicę między dwiema liczbami i podzielić wynik przez oryginalną liczbę.

Istnieją dwie różne formuły, których możesz użyć do obliczenia zmiany procentowej. Ich składnia jest następująca:

=(nowa_wartość – oryginalna_wartość) / oryginalna_wartość

lub

=(nowa wartość / oryginalna wartość) – 1
  • Nowa wartość – to bieżąca/końcowa wartość dwóch porównywanych liczb.
  • poprzednia_wartość – jest początkową wartością dwóch liczb, które porównujesz w celu obliczenia zmiany procentowej.

Przykład:

Przyjrzyjmy się tej liście hipotetycznych zamówień owoców, w której kolumna B zawiera liczbę owoców zamówionych w zeszłym miesiącu, a kolumna C zawiera liczbę owoców zamówionych w tym miesiącu:

Na przykład, w zeszłym miesiącu zamówiłeś określoną liczbę owoców, a w tym miesiącu zamówiłeś więcej niż w zeszłym miesiącu, możesz wprowadzić poniższy wzór, aby znaleźć procentową różnicę między tymi dwoma zamówieniami:

=(C2-B2)/B2

Powyższa formuła zostanie wprowadzona w komórce D2, aby znaleźć procentową zmianę między C2 (nowa_wartość) a B2 (oryginalna_wartość). Po wpisaniu formuły w komórce naciśnij klawisz Enter, aby wykonać formułę. Otrzymasz procentową zmianę wartości dziesiętnych, jak pokazano poniżej.

Wynik nie jest jeszcze sformatowany jako procent. Aby zastosować format procentowy do komórki (lub zakresu komórek), zaznacz komórki, a następnie kliknij przycisk „Styl procentowy” w grupie Numer na karcie „Strona główna”.

Wartość dziesiętna zostanie przeliczona na procent.

Oblicz zmianę procentową między dwiema kolumnami liczb

Teraz znamy zmianę procentową między dwoma zamówieniami na jabłka, znajdźmy procentową zmianę dla wszystkich przedmiotów.

Aby obliczyć procentową zmianę między dwiema kolumnami, musisz wprowadzić formułę w pierwszej komórce kolumny wynikowej i Autouzupełnij formułę w dół całej kolumny.

Aby to zrobić, kliknij uchwyt wypełniania (mały zielony kwadrat w prawym dolnym rogu komórki) komórki z formułą i przeciągnij go w dół do pozostałych komórek.

Teraz masz wartości zmiany procentowej dla dwóch kolumn.

Gdy nowa wartość jest wyższa niż wartość pierwotna, wynik procentowy będzie dodatni. Jeśli wartość new value jest niższa niż wartość pierwotna, wynik byłby ujemny.

Jak widać, zmiana procentowa dla „jabłek” jest zwiększona o 50%, więc wartość jest dodatnia. Odsetek „Awokodo” zmniejsza się o 14%, stąd wynik jest ujemny (-14%).

Możesz także użyć niestandardowego formatowania, aby podświetlić ujemne wartości procentowe na czerwono, aby można je było łatwo zidentyfikować, gdy go szukasz.

Aby sformatować komórki, wybierz komórki, które chcesz sformatować, kliknij prawym przyciskiem myszy i wybierz opcję „Formatuj komórki”.

W wyświetlonym oknie Formatowanie komórek kliknij „Niestandardowy” na dole lewego menu i wpisz poniższy kod w polu tekstowym „Typ:”:

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

Następnie kliknij przycisk „OK”, aby zastosować formatowanie.

Teraz negatywne wyniki zostaną podświetlone na czerwono. Ponadto to formatowanie zwiększa liczbę miejsc dziesiętnych, aby pokazać dokładne wartości procentowe.

Możesz również użyć innej formuły, aby obliczyć zmianę procentową między dwiema wartościami (zamówienie z ostatniego miesiąca i zamówienie z tego miesiąca):

=(C2/B2)-1

Ta formuła dzieli nową_wartość (C2) przez pierwotną wartość (B2) i odejmuje „1” od wyniku, aby znaleźć zmianę procentową.

Obliczanie zmiany procentowej w czasie

Możesz obliczyć procentową zmianę okresu do okresu (zmiana z miesiąca na miesiąc), aby zrozumieć tempo wzrostu lub spadku z jednego okresu do drugiego (dla każdego miesiąca). Jest to bardzo przydatne, gdy chcesz poznać procentową zmianę dla każdego miesiąca lub roku w określonym przedziale czasu.

W poniższym przykładzie mamy cenę owoców za miesiąc marzec w kolumnie B oraz cenę za miesiąc lipiec 5 miesięcy później.

Użyj tego ogólnego wzoru, aby znaleźć procentową zmianę w czasie:

=((Aktualna_wartość/Oryginalna_wartość)/Oryginalna_wartość)*N

Tutaj N oznacza liczbę okresów (lat, miesięcy itp.) między dwiema wartościami początkowym i bieżącym.

Na przykład, jeśli chcesz zrozumieć stopę inflacji lub deflacji cen dla każdego miesiąca przez ponad 5 miesięcy w powyższym przykładzie, możesz zastosować poniższy wzór:

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

Ta formuła jest podobna do formuły, której używaliśmy wcześniej, z wyjątkiem tego, że musimy podzielić wartość zmiany procentowej przez liczbę miesięcy między dwoma miesiącami (5).

Obliczanie procentowego wzrostu/zmiany między rzędami

Załóżmy, że masz jedną kolumnę liczb zawierającą miesięczną cenę benzyny przez ponad rok.

Teraz, jeśli chcesz obliczyć procent wzrostu lub spadku między wierszami, aby zrozumieć zmiany cen z miesiąca na miesiąc, wypróbuj poniższy wzór:

=(B3-B2)/B2

Ponieważ musimy znaleźć procent wzrostu za luty (B3) na podstawie ceny ze stycznia (B2), pierwszy wiersz należy pozostawić pusty, ponieważ nie porównujemy stycznia z poprzednim miesiącem. Wprowadź formułę w komórce C3 i naciśnij Enter.

Następnie skopiuj formułę do pozostałych komórek, aby określić procentową różnicę między poszczególnymi miesiącami.

Możesz również obliczyć zmianę procentową dla każdego miesiąca w porównaniu ze styczniem (B2). Aby to zrobić, musisz uczynić tę komórkę odwołaniem bezwzględnym, dodając znak $ do odwołania do komórki, np. 2 C$. Formuła wyglądałaby więc tak:

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

Tak jak poprzednio, pomijamy pierwszą komórkę i wpisujemy formułę w komórce C3. Gdy skopiujesz formułę do pozostałych komórek, odwołanie bezwzględne ($B$2) nie zmieni się, podczas gdy odwołanie względne (B3) zmieni się na B4, B5 i tak dalej.

Otrzymasz procentową stopę inflacji lub deflacji za każdy miesiąc w porównaniu do stycznia.

Obliczanie procentowego wzrostu w Excelu

Obliczanie wzrostu procentowego jest podobne do obliczania zmiany procentowej w programie Excel. Procentowy wzrost to stopa wzrostu do wartości początkowej. Do obliczenia procentowego wzrostu potrzebne będą dwie liczby: pierwotna liczba i nowa_liczba.

Składnia:

Procentowy wzrost = (Nowy_numer - Oryginalny_numer) / Oryginalny_numer

Wystarczy odjąć pierwotną (pierwszą) liczbę od nowej (drugiej) i podzielić wynik przez pierwotną liczbę.

Na przykład masz listę rachunków i ich kwoty za dwa miesiące (kwiecień i maj). Jeśli Twoje rachunki za kwiecień wzrosną w maju, to jaki jest procentowy wzrost od kwietnia do maja? Możesz skorzystać z poniższego wzoru, aby dowiedzieć się:

=(C2-B2)/B2

Tutaj odejmujemy rachunek za maj za energię elektryczną (C2) od rachunku za kwiecień (B2) i dzielimy wynik przez rachunek za kwiecień. Następnie skopiuj formułę do innych komórek za pomocą uchwytu wypełniania.

Jeśli uzyskasz wynik pozytywny (24,00 USD), procent wzrósł między dwoma miesiącami, a jeśli wynik negatywny (np. -13,33 USD) to w rzeczywistości odsetek ten się zmniejsza, a nie zwiększa.

Obliczanie procentowego spadku w Excelu

Zobaczmy teraz, jak obliczyć procentowy spadek między liczbami. Obliczanie procentowego spadku jest bardzo podobne do obliczania procentowego wzrostu. Jedyna różnica polega na tym, że nowa liczba będzie mniejsza niż pierwotna.

Formuła jest prawie identyczna z obliczaniem procentowego wzrostu, z wyjątkiem tego, że odejmuje się pierwotną wartość (pierwszą liczbę) od nowej wartości (druga liczba) przed podzieleniem wyniku przez oryginalną wartość.

Składnia:

Procentowy spadek = (Numer_oryginalny - Nowy_numer) / Oryginalny_numer

Załóżmy, że masz tę listę urządzeń pamięci masowej i ich ceny na dwa lata (2018 i 2020).

Na przykład ceny urządzeń pamięci masowej były wyższe w 2018 roku, a ceny spadły w 2020 roku. Jaki jest procentowy spadek cen w 2020 roku w porównaniu do 2018 roku? Możesz użyć tej formuły, aby dowiedzieć się:

=(B2-C2)/B2

Tutaj odejmujemy cenę 2018 (B2) od ceny 2020 (C2) i dzielimy sumę przez cenę 2018. Następnie kopiujemy formułę do innych komórek, aby znaleźć procentowy spadek dla innych urządzeń.

Jeśli uzyskasz wynik dodatni (20,00 USD), procent zmniejszył się w ciągu tych dwóch lat, a jeśli wynik negatywny (np. -13,33 USD) to w rzeczywistości odsetek ten się zwiększa, a nie zmniejsza.

Typowe błędy, które można uzyskać podczas korzystania z powyższych formuł

Korzystając z powyższych formuł, od czasu do czasu możesz natknąć się na tę listę typowych błędów:

  • #DZIAŁ/0!: Ten błąd występuje, gdy próbujesz podzielić liczbę przez zero (0) lub przez komórkę, która jest pusta. Np. Wartość B6 we wzorze ‘=(B6-C6)/B6’ jest równa zeru, stąd #DIV/0! błąd.

Gdy wpiszesz „zero” w formacie waluty, zostanie ono zastąpione myślnikiem (-), jak pokazano powyżej.

  • #WARTOŚĆ: program Excel zgłasza ten błąd, gdy wprowadzono wartość, która nie jest obsługiwanym typem lub gdy komórki pozostaną puste. Może się to często zdarzyć, gdy wartość wejściowa zawiera spacje, znaki lub tekst zamiast liczb.
  • NUM!: Ten błąd występuje, gdy formuła zawiera nieprawidłową liczbę, która powoduje liczbę, która jest zbyt duża lub zbyt mała, aby można ją było wyświetlić w programie Excel.

Konwertuj błąd na zero

Ale jest sposób na pozbycie się tych błędów i wyświetlenie „0%” w ich miejscu, gdy wystąpi błąd. Aby to zrobić, musisz użyć funkcji „JEŻELI.BŁĄD”, która zwraca niestandardowy wynik, gdy formuła wykaże błąd.

Składnia funkcji JEŻELI.BŁĄD:

=JEŻELI.BŁĄD(wartość; wartość_jeśli_błąd)

Gdzie,

  • wartość to wartość, odwołanie lub formuła do sprawdzenia.
  • value_if_error to wartość, którą chcemy wyświetlić, jeśli formuła zwróci wartość błędu.

Zastosujmy tę formułę w jednym z przykładów błędów (błąd #DIV/0!):

=JEŻELIBŁĄD((B6-C6)/B6,0%)

W tej formule argument „wartość” to formuła zmiany procentowej, a argument „wartość_jeśli_błąd” to „0%”. Gdy formuła zmiany procentowej napotka błąd (błąd #DZIEL/0!), funkcja JEŻELI.BŁĄD wyświetli „0%”, jak pokazano poniżej.

To wszystko, co musisz wiedzieć o obliczaniu zmiany procentowej w programie Excel.