Ostatnio opisywałam, jak obliczyć wykonanie planu sprzedaży (tutaj). Teraz opiszę, jak obliczyć, jakie jest odchylenie od planu sprzedaży?
Matematycznie, szukane odchylenie to różnica między pełnym wykonaniem planu (czyli 100%, czyli 1) a faktycznie wykonanym planem:
odchylenie = wykonanie planu – 1
Podobnie jak poprzednio, interesuje nas sprzedaż 10 regionów w 2012 roku. Chcemy obliczyć odchylenie od planu i dodatkowo odchylenie sprzedaży z 2012 roku od sprzedaży z 2011 roku. Tabela wygląda tak:
Obliczanie wykonania opisałam poprzednio (tutaj). Zajmę się teraz samymi odchyleniami.
Aby je obliczyć, wpisz w komórkę F2 taką formułę:
=C2/D2-1
a w komórkę G2 – taką:
=C2/B2-1
Obie formuły zatwierdź enterem i skopiuj wyniki do pozostałych wierszy. Sformatuj jako procent.
Obliczanie odchylenia sprzedaż 2012 do tej 2011, jak zauważyłeś, liczy się identycznie jak odchylenie od planu. W mianowniku jedynie jest sprzedaż 2011 a nie plan, ponieważ to do sprzedaży 2011 porównujemy sprzedaż 2012 (tak jak wcześniej porównywaliśmy ją do planu).
Oto wynik:
Gotowe!
Jak widać, wszystkim regionom poszło lepiej niż w roku poprzednim – zwiększyli sprzedaż. Jednak nie każdemu udało się zrealizować plan sprzedaży. Można się tutaj jeszcze pokusić o graficzne zobrazowanie w tabeli (np. zaznaczając to kolorami), czy dany region jest powyżej czy poniżej planu (oczywiście wykres też byłby mile widziany). Takie zagadnienie jednak omówię w kolejnych artykułach.
Artykuły pokrewne:
Takie pytanko, czy da się to policzyć jeśli wartością np. planu jest zero? Bo wyskakuje błąd „dziel/0”, a jeśli zastosuję formułę =JEŻELI(LUB(A1=0;B1=0);0;A1/B1-1) zwraca wynik 0.
dla A1=0 i B1=33
Nijak nie mogę policzyć odchylenia od zera.
Byłabym wdzięczna za podpowiedź.
Pozdrawiam
Hej, a na przykładzie, w myśli: jaki wynik dałabyś, gdyby plan = 0, a realizacja 25. Co chciałabyś uzyskać w wyniku?
W sumie zastanawiałam się nad tym w myśl zasady, że jeżeli 1=100%, 2=200%…, to przy 25=2500%(?). Jednak jestem otwarta na sugestię, w każdym razie chodzi o to, żeby nie zwracało mi wartości dziel/0, 0 lub 100%(poza tym przypadkiem gdzie tak jest). Tak właściwie chciałam to wykorzystać w sytuacji troszkę bardziej złożonej, jednak aby to zrobić muszę ogarnąć podstawę.
W prawdzie, w całym arkuszu taka sytuacja zdarza się tylko kilka razy, jednak musiałabym za każdym razem przeglądać setki pozycji żeby to wyłapać i wklepać wartość ręcznie. Arkusz jest powiązany z innymi więc jeśli w tym wyskakuje mi „psikus” to leci już tak do końca.
Ok, to może jeśli chcesz nie wyświetlać błędu Dziel/0 to może wpisz formułę w funkcję: =JEŻELI.BŁĄD(tutaj pierwotna formuła; wartość, która ma się wyświetlać w rpzypadku błędu). Może po prostu tak?
Brak błędu uzyskałam tym:=JEŻELI(LUB(A1=0;B1=0);0;A1/B1-1). No nic, najwyżej ustawię sobie w formatowaniu warunkowym żeby podświetlało mi błędy i ręcznie będę wpisywać wartości. W każdym razie dzięki za pomoc 🙂
Tylko pytanie na co będziesz chciała zmieniać? Bo to możesz wpisać w drugim argumencie zaproponowanej przez siebie funkcji 🙂
Można powiedzieć, że metodą prób i błędów, i dzięki podpowiedzi, doszłam do najbardziej odpowiadających mi argumentów:
argument 1(E1): =JEŻELI(LUB(C1=0;);A1*1;D1/C1-1)
argument 2(F1): =JEŻELI(LUB(D1=0;);B1*-1;D1/C1-1)
oba te argumenty połączyłam w innej kolumnie formułą: =JEŻELI(C11=0;E1;F1)
i teraz otrzymuję dokładnie takie wyniki o jakie mi chodziło. W najgorszym razie ukryję pomocnicze kolumny i zostawię jedynie tę z wynikiem, o który mi chodzi. Jednak wolałabym jakoś je połączyć(tylko nie wiem jak),ale kiedy próbuje to zrobić zamiast konkretnego wyniku wyskakują mi: Dziel/0, Prawda, Fałsz itp.
Nie znam excela na tyle dobrze żeby wymyślić jakiej funkcji mogę użyć, a do tych co znam już mi się wyczerpały pomysły ich połączenia 🙂
Natalia, no i super! Jak już masz formuły w komórkach E1 i F1, to teraz wstaw dokładnie te formuły (tylko bez znaków równości!) wklej do funkcji w ostatniej kolumnie. Po prostu zamiast E1 wstaw funkcję w tej komórce. I to samo zrób z F1. I masz gotowe rozwiązanie :). Gratuluję Ci! 🙂
O zagnieżdżaniu funkcji mówiłam na jednym ze swoich webinarów – jeśli dalej chcesz pogłębiać ten temat, to zobacz tutaj: Zagnieżdżanie funkcji w Excelu. 🙂
Próbowałam w ten sposób wpisać ręcznie, musiałam pomijać jakiś nawias bo za nic nie chciało wskoczyć. A teraz skopiowałam i voila! Już nieraz znalazłam tu lekarstwo na swoje bolączki, lub chociaż jakiś kierunek, którym powinnam podążyć. Na pewno jeszcze nieraz tu wrócę, dzięki wielkie 🙂
Natalia, bardzo się cieszę!!! Super, że Ci się udało :). I będzie mi bardzo miło, jak tutaj zajrzysz – zapraszam serdecznie 🙂