Jak przeprowadzić analizę odchyleń w Excelu: Krok po kroku

Aby przeprowadzić analizę odchyleń w Excel, zestaw obok siebie dane budżetowe i rzeczywiste dla każdej pozycji. Odejmij budżet od wykonania, aby otrzymać odchylenie kwotowe. Podziel to odchylenie przez budżet, aby uzyskać wartość procentową. Następnie oznacz każdą linię jako korzystną lub niekorzystną i wyjaśnij największe luki.
Ten przewodnik omawia formuły, układ arkusza oraz pełny, opracowany przykład. Obejmuje również sposób tworzenia wykresów wyników oraz metodę podziału luki przychodowej na cenę i wolumen. Kroki w Excel pochodzą ze stron pomocy technicznej Microsoft przeczytanych 10 listopada 2026 r.
Co pokazuje analiza odchyleń
Odchylenie to luka między tym, co zaplanowałeś, a tym, co się wydarzyło. Analiza odchyleń wymienia te luki pozycja po pozycji i wyjaśnia te, które mają znaczenie.
Większość zespołów porównuje wyniki rzeczywiste z jednym z trzech punktów odniesienia:
- Budżet. Plan ustalony na początku roku.
- Prognoza. Zaktualizowany plan ustalony w ciągu roku.
- Poprzedni okres. Ostatni miesiąc, ostatni kwartał lub ten sam okres ubiegłego roku.
Własna umiejętność Copilot od Microsoft do tego zadania dobrze opisuje tę pracę. Jej umiejętność analizy odchyleń „Porównuje rzeczywiste wyniki z budżetami, prognozami lub poprzednimi okresami i wyjaśnia różnice według ceny, wolumenu, miksu, stawki lub czasu”.
Wynikiem jest zazwyczaj tabela oraz kilka pisemnych notatek. Tabela pokazuje każdą lukę. Notatki wyjaśniają trzy lub cztery kwestie, na które liderzy muszą zareagować.
Podstawowe formuły
Większość pracy wykonują trzy formuły. Załóżmy, że budżet znajduje się w kolumnie B, a wykonanie w kolumnie C.
| Miara | Formuła | Co nam mówi |
|---|---|---|
| Odchylenie kwotowe | =C2-B2 | Jak daleko wynik rzeczywisty odbiega od budżetu, w walucie |
| Odchylenie procentowe | =IFERROR((C2-B2)/ABS(B2),"") | Jak duża jest luka w stosunku do planu |
| Status | =IF(D2=0,"Zgodnie z planem",IF(D2>0,"Powyżej planu","Poniżej planu")) | W którym kierunku przesunęła się linia |
Funkcja ABS pozwala zachować poprawny znak, gdy budżet jest ujemny. IFERROR pozostawia komórkę pustą, gdy budżet wynosi zero, zamiast wyświetlać błąd.
Kierunek nie jest tożsamy z oceną „dobry” lub „zły”. Przychody powyżej planu to dobra wiadomość. Koszty powyżej planu to zła wiadomość. Kolejne sekcje pokazują, jak sobie z tym radzić.
Jak przygotować arkusz
Przejrzysty układ ułatwia kopiowanie formuł. Użyj jednego wiersza na każdą pozycję i następujących kolumn:
- Pozycja. Przychody, koszt własny sprzedaży, płace, marketing i tak dalej.
- Typ. Wpisz „Przychód” lub „Koszt”, aby Excel wiedział, który kierunek jest korzystny.
- Budżet. Zaplanowana kwota na dany okres.
- Wykonanie. Kwota z eksportu księgowego.
- Odchylenie. Wykonanie minus budżet.
- Odchylenie %. Odchylenie podzielone przez budżet.
- Korzystne lub niekorzystne. Etykieta oparta na typie i kierunku.
- Wyjaśnienie. Krótka notatka dla istotnych pozycji.
Zachowaj budżet i wykonanie dla tego samego okresu i tego samego mapowania kont. Analiza odchyleń jest tylko tak dobra, jak to dopasowanie. Jeśli płace są podzielone na dwa konta w budżecie, podziel je w ten sam sposób w wynikach rzeczywistych.
Sformatuj tabelę jako tabelę Excel za pomocą Ctrl+T. Nowe wiersze będą wtedy automatycznie przejmować formuły.
Jak obliczyć odchylenia w Excel
Gdy układ jest gotowy, kroki są krótkie. Załóżmy, że powyższe kolumny zaczynają się w wierszu 2, z Typem w B, Budżetem w C i Wykonaniem w D.
- W E2 wpisz =D2-C2 dla odchylenia kwotowego.
- W F2 wpisz =IFERROR((D2-C2)/ABS(C2),"") i sformatuj jako procent.
- W kolumnie Status wprowadź test korzystności pokazany poniżej.
- Skopiuj formuły w dół do każdego wiersza.
- Dodaj wiersz sumy i oblicz jego odchylenie z sum, a nie poprzez dodawanie procentów.
Oto test korzystności dla wiersza 2 w kolumnie Status:
=IF(E2=0,"Zgodnie z planem",IF(B2="Przychód",IF(E2>0,"Korzystne","Niekorzystne"),IF(E2<0,"Korzystne","Niekorzystne")))
Formuła najpierw odczytuje kolumnę Typ. Dla przychodów dodatnie odchylenie jest korzystne. Dla kosztów ujemne odchylenie jest korzystne, ponieważ wydano mniej niż planowano.
Wartości procentowe wymagają uwagi w wierszu sumy. Dodawanie procentów z poszczególnych linii daje błędny wynik. Zamiast tego podziel całkowite odchylenie przez całkowity budżet.
Jak oznaczyć odchylenia, które mają znaczenie
Nie każda luka wymaga notatki. Większość zespołów ustala zasadę istotności, na przykład „powyżej 5% i powyżej $2,000”. Linie, które przejdą oba testy, otrzymują wyjaśnienie.
Prosta formuła pozwala je oznaczyć. Dodaj kolumnę Istotne i wpisz =AND(ABS(F2)>0.05,ABS(E2)>2000) w wierszu 2. Komórka pokaże TRUE dla istotnych linii.
Kolor ułatwia szybkie skanowanie tabeli. Formatowanie warunkowe w Excel może dodać zestawy ikon. Microsoft twierdzi, że zestaw ikon może „dodawać adnotacje i klasyfikować dane na trzy do pięciu kategorii oddzielonych wartością progową”.
Aby go dodać, zaznacz kolumnę Odchylenie %. Następnie przejdź do Narzędzia główne > Formatowanie warunkowe > Zestawy ikon. Microsoft zauważa, że w zestawie 3 strzałek „zielona strzałka w górę reprezentuje wyższe wartości”. Dla linii kosztowych odwróć kolejność ikon, aby wzrost był wyświetlany jako ostrzeżenie.
Jak wyjaśnić czynniki sprawcze
Liczby pokazują, gdzie jest luka. Czynniki sprawcze wyjaśniają dlaczego. Opis umiejętności Microsoft wymienia pięć typowych czynników: cena, wolumen, miks, stawka i czas.
Cena. Sprzedałeś lub kupiłeś po innej cenie niż planowano. Odchylenie ceny = (cena rzeczywista − cena budżetowa) × rzeczywista liczba jednostek.
Wolumen. Sprzedałeś lub zużyłeś inną ilość. Odchylenie wolumenu = (rzeczywista liczba jednostek − budżetowa liczba jednostek) × cena budżetowa.
Miks. Udział produktów, regionów lub klientów uległ zmianie. Przesunięcie w stronę produktów o niższej marży obniża zysk, nawet jeśli wolumen pozostaje bez zmian.
Stawka. Stawka kosztowa uległa zmianie, np. stawka godzinowa lub opłata dostawcy.
Czas. Pieniądze przemieściły się między okresami. Faktura z marca zapłacona w kwietniu wykazuje lukę w obu miesiącach.
Cena i wolumen razem wyjaśniają pełną lukę przychodową. Warto przeprowadzać to sprawdzenie za każdym razem, ponieważ potwierdza ono poprawność obliczeń.
Przykład analizy odchyleń
Oto opracowany przykład dla fikcyjnej firmy Cedar and Pine Coffee. Jej lider finansowy porównuje wyniki rzeczywiste za trzeci kwartał z budżetem.
| Pozycja | Typ | Budżet | Wykonanie | Odchylenie | Odchylenie % | Status |
|---|---|---|---|---|---|---|
| Przychody | Przychód | $250,000 | $236,500 | −$13,500 | −5.4% | Niekorzystne |
| Koszt własny sprzedaży | Koszt | $100,000 | $97,200 | −$2,800 | −2.8% | Korzystne |
| Płace | Koszt | $60,000 | $64,800 | $4,800 | 8.0% | Niekorzystne |
| Marketing | Koszt | $20,000 | $26,000 | $6,000 | 30.0% | Niekorzystne |
| Czynsz | Koszt | $15,000 | $15,000 | $0 | 0.0% | Zgodnie z planem |
| Zysk operacyjny | $55,000 | $33,500 | −$21,500 | −39.1% | Niekorzystne |
Suma się zgadza. Przychody spadły o $13,500, koszt własny sprzedaży przyniósł oszczędność $2,800, płace kosztowały o $4,800 więcej, a marketing o $6,000 więcej. Razem daje to −$21,500, co odpowiada luce w zysku.
Trzy linie przechodzą zasadę istotności (powyżej 5% i powyżej $2,000): przychody, płace i marketing. Koszt własny sprzedaży zmienił się tylko o 2.8%, więc nie otrzymuje notatki.
Luka przychodowa wymaga bliższego przyjrzenia się. Budżet zakładał 50,000 jednostek po $5.00. W kwartale sprzedano 43,000 jednostek po $5.50.
- Odchylenie wolumenu. (43,000 − 50,000) × $5.00 = −$35,000.
- Odchylenie ceny. ($5.50 − $5.00) × 43,000 = +$21,500.
- Suma. −$35,000 + $21,500 = −$13,500, co zgadza się z linią przychodów.
Zatem podwyżka ceny utrzymała się, ale kosztowała utratę wolumenu. Notatka dla liderów mówi dokładnie to. Marketing wydał o 30% więcej niż planowano, a mimo to liczba sprzedanych jednostek spadła. To rodzi pytanie o to, które kampanie zadziałały.
Płace wymagają własnego sprawdzenia. Luka $4,800 może wynikać ze stawki, np. podwyżki płac, lub z wolumenu, np. dodatkowych godzin. Lider finansowy pyta menedżera operacyjnego i dowiaduje się, że dwa sklepy dodały weekendowe zmiany. To sprawia, że jest to luka wolumenowa związana z godzinami otwarcia, a nie podwyżka płac.
Ostateczne podsumowanie dla liderów mieści się w jednej linii. Zysk był o $21,500 niższy od budżetu, ponieważ wzrost cen kosztował więcej wolumenu, niż przyniósł zysku, a wydatki na marketing i zmiany weekendowe przekroczyły plan.
Jak pisać notatki do odchyleń
Tabela to tylko połowa analizy odchyleń. Liderzy czytają notatki, więc każda z nich powinna odpowiadać na trzy pytania: co się zmieniło, dlaczego się zmieniło i co stanie się dalej.
Dobra notatka opiera się na prostym schemacie:
- Co. „Marketing wydał o $6,000 więcej niż w budżecie, o 30.0% powyżej planu”.
- Dlaczego. „Dwie płatne kampanie trwały dłużej niż planowano”.
- Dalej. „Wstrzymaliśmy jedną kampanię 3 października i spodziewamy się, że Q4 zakończy się zgodnie z planem”.
Każda notatka powinna mieć dwa lub trzy zdania. Zachowaj tę samą kolejność linii co w tabeli, aby czytelnicy mogli dopasować notatki do liczb.
Oddzielaj jednorazowe luki od trendów. Jednorazowy rachunek za naprawę różni się od podwyżki płac, która będzie się powtarzać co miesiąc. Wyraźnie zaznaczaj powtarzające się luki, ponieważ zmieniają one prognozę.
Pisz notatki wspólnie z właścicielem każdej linii. Zespół finansowy widzi liczby, ale właściciel budżetu zna przyczynę. Krótka konsultacja z każdym właścicielem zamienia domysły w wyjaśnienia.
Na koniec dodaj jednolinijkowe podsumowanie na górze. Dla powyższego przykładu brzmiałoby ono: „Zysk operacyjny w Q3 był o $21,500 niższy od budżetu, głównie z powodu niższego wolumenu i wyższych wydatków na marketing”.
Czas ma takie samo znaczenie jak sformułowania. Większość zespołów przeprowadza analizę odchyleń co miesiąc, po zamknięciu ksiąg. Miesięczny rytm pozwala wyłapać problemy, gdy jest jeszcze czas na działanie.
Przeglądy kwartalne są głębsze. Dodają podział na cenę i wolumen, nową prognozę oraz weryfikację, czy sam budżet nadal ma sens. Przeglądy roczne analizują cały rok w stosunku do pierwotnego planu.
Za każdym razem używaj tego samego szablonu. Gdy kolumny i zasady pozostają stałe, analiza odchyleń zajmuje mniej czasu każdego miesiąca, a trendy są łatwiejsze do zauważenia w różnych okresach.
Jak przedstawić odchylenia na wykresie
Wykres pomaga liderom zobaczyć ścieżkę od budżetu do wykonania. Najlepiej nadaje się do tego wykres kaskadowy.
Microsoft twierdzi, że wykres kaskadowy „pokazuje sumę bieżącą w miarę dodawania lub odejmowania wartości”. Dodaje, że „wykresy kaskadowe są również nazywane wykresami mostkowymi”.
Aby go zbudować, wymień kroki od zysku budżetowego do zysku rzeczywistego. Zacznij od $55,000, następnie dodaj wpływ każdej linii na zysk i zakończ na $33,500. Zaznacz dane i wybierz Wstawianie > Wstaw wykres kaskadowy > Kaskadowy.
Ustaw pierwszy i ostatni słupek jako sumy. Strona Microsoft zauważa, że kolumny początkowa i końcowa „często zaczynają się na osi poziomej”, podczas gdy środkowe słupki „pływają”. Wzrosty i spadki są kodowane kolorami, dzięki czemu największe czynniki sprawcze rzucają się w oczy.
Do śledzenia z miesiąca na miesiąc sprawdza się również prosty wykres kolumnowy odchylenia %. Nasz przewodnik o tym, jak zbudować raport budżet vs. wykonanie, obejmuje pełny raport miesięczny wokół tej tabeli.
Przeprowadź analizę odchyleń z AI
Formuły zajmują minuty, gdy tabela już istnieje. Powolną częścią jest często mapowanie kont, znajdowanie czynników sprawczych i pisanie notatek. Narzędzie AI może przygotować te kroki na podstawie Twoich eksportów.
Oto metoda w trzech krokach, przy użyciu Powerdrill Bloom.
Krok 1: Prześlij swój budżet i wyniki rzeczywiste
Wyeksportuj budżet i wyniki rzeczywiste ze swojego narzędzia księgowego lub planistycznego. AI Budget Analysis Tool akceptuje „eksporty Excel, CSV lub PDF z systemów księgowych i FP&A”.
Dołącz dane o jednostkach i cenach, jeśli chcesz uzyskać podział na cenę i wolumen. Zachowaj te same nazwy kont w obu plikach.
Krok 2: Zapytaj o odchylenia i czynniki sprawcze w języku naturalnym
Opisz wynik, którego potrzebujesz. Na przykład: „Porównaj wyniki rzeczywiste Q3 z budżetem według pozycji. Oznacz linie powyżej 5% i $2,000 oraz podziel lukę przychodową na cenę i wolumen”.
Strona narzędzia wymienia „Odchylenie według pozycji z zastosowanymi progami istotności” jako jeden z widoków. Wymienia również arkusz nowej prognozy z „oryginalnym budżetem, wynikami YTD i zrewidowanym końcem roku”.
Krok 3: Sprawdź liczby i wyeksportuj raport
Zanim cokolwiek udostępnisz, porównaj sumy z własnym arkuszem. Następnie wyeksportuj wynik. Strona Budget Analysis wymienia „Pulpit odchyleń z wyróżnionymi istotnymi liniami i dołączonymi czynnikami sprawczymi”.
Dla bieżącego podglądu, AI Finance Dashboard Builder obejmuje rachunek zysków i strat, gotówkę, kapitał obrotowy i prognozę. Wypróbuj Powerdrill Bloom z budżetem i wynikami z ostatniego kwartału.
Typowe błędy, których należy unikać
Kilka błędów pojawia się w niemal każdej pierwszej analizie odchyleń.
- Pomylenie znaku. Zdecyduj raz, czy odchylenie to wykonanie minus budżet, i stosuj to wszędzie.
- Nazywanie każdej dodatniej luki dobrą. Wyższe koszty są niekorzystne, mimo że liczba jest dodatnia.
- Dodawanie procentów. Ponownie obliczaj całkowite procenty z całkowitych kwot.
- Porównywanie różnych okresów. Wykonanie od początku roku zestawione z miesięcznym budżetem daje fałszywą lukę.
- Ignorowanie czasu. Spóźniona faktura może stworzyć dwa przeciwstawne odchylenia w dwóch miesiącach.
- Wyjaśnianie wszystkiego. Notatki o małych lukach ukrywają te, które mają znaczenie.
- Ręczne wpisywanie liczb. Wklejaj lub linkuj wyniki rzeczywiste z eksportu, aby literówka nie stworzyła fałszywej luki.
- Pomijanie sprawdzenia sumy. Odchylenia poszczególnych linii powinny sumować się do całkowitego odchylenia. Jeśli tak nie jest, brakuje wiersza lub został on zmapowany dwukrotnie.
Często zadawane pytania
Jaka jest formuła na odchylenie w Excel?
Dla odchylenia budżetowego użyj wykonanie minus budżet, np. =C2-B2. Dla procentu użyj =IFERROR((C2-B2)/ABS(B2),"") i sformatuj komórkę jako procent.
Skąd mam wiedzieć, czy odchylenie jest korzystne?
To zależy od typu linii. Dla przychodów wykonanie powyżej budżetu jest korzystne. Dla kosztów wykonanie poniżej budżetu jest korzystne. Kolumna Typ i formuła IF mogą oznaczyć każdy wiersz.
Co to jest istotne odchylenie?
To luka wystarczająco duża, by wymagała wyjaśnienia. Zespoły ustalają własne zasady, np. powyżej 5% i powyżej $2,000. Linie, które przejdą oba testy, otrzymują pisemną notatkę.
Jaki wykres jest najlepszy do analizy odchyleń?
Wykres kaskadowy, zwany również wykresem mostkowym, pokazuje, jak każda linia przesuwa sumę od budżetu do wykonania. Excel posiada go w sekcji Wstawianie > Wstaw wykres kaskadowy.
Czy Copilot w Excel może przeprowadzić analizę odchyleń?
Microsoft wymienia umiejętność analizy odchyleń dla Copilot w Excel. Porównuje on wyniki rzeczywiste z budżetami, prognozami lub poprzednimi okresami i przygotowuje szkice wyjaśnień do weryfikacji. Wymaga licencji Copilot.
Źródła: Microsoft Support, Use built-in skills with Copilot in Excel · Microsoft Support, Create a waterfall chart · Microsoft Support, Use conditional formatting to highlight information in Excel.