Porównanie budżetu planowanego do faktycznego na wykresie

Z lekcji dowiesz się:

• Jak stworzyć dobrze prezentujący się wykres, czytelny z interesującą kolorystyką?

• Przy jakich poleceniach przedstawić zależność budżetu: faktycznego i planowanego?

• Jak opracować przejrzystą legendę?

W poniższym zadaniu nauczymy się jak zrobić wykres przedstawiający zależność budżetu planowanego od faktycznego. Standardowo przy tworzeniu nietypowego wykresu musimy sobie przygotować odpowiednio dane pomocnicze. Nie wystarczą tutaj dane podstawowe, czyli wartości budżetu faktycznego i planowanego dla podanych województw (rys. 1).

(Rys. 1) Podstawowe dane – wartości budżetu planowanego i faktycznego

Pierwszym dodatkowym obliczeniem jest różnica między budżetem faktycznym, a planowanym. Zapis formuły powinien wyglądać następująco:

=D3-C3

Po zatwierdzaniu formuły przeciągamy (kopiujemy) ją na wiersze poniżej. Otrzymujemy różnicę budżetów przedstawioną na rys. 2.

(Rys. 2) Różnica między budżetem faktycznym i planowanym

Jeśli otrzymana różnica jest wartością dodatnią to chcemy brać pod uwagę budżet faktyczny, natomiast jeśli jest to wartość ujemna – budżet planowany. Do tego celu użyjemy prostej funkcji JEŻELI. Dla wartości dodatnich funkcja jeżeli powinna wyglądać następująco:

=JEŻELI(E3>0;D3;0)

Pierwszym argumentem funkcji jest test_logiczny, czyli sprawdzenie czy podana wartość jest większa od zera (E3>0). Drugi argument funkcji to wartość_jeżeli_prawda, czyli wartość jaką należy brać pod uwagę, kiedy test logiczny jest spełniony (budżet faktyczny). Trzeci argument funkcji to wartość_jeżeli_fałsz, czyli wartość, jaką ma zwrócić funkcja, kiedy warunek z testu logicznego nie zostaje spełniony – wartość 0. Tak przygotowaną formułę zatwierdzamy przyciskiem Enter i kopiujemy na wiersze poniżej. Otrzymamy wartości zwrócone przez funkcję JEŻELI w kolumnie Plus (rys. 3).

(Rys. 3) Wyniki funkcji JEŻELI w kolumnie Plus (dla dodatnich wartości w kolumnie Różnica)

W kolumnie Minus oczekujemy otrzymać budżet planowany, kiedy wartość w kolumnie Różnica jest ujemna. Ponownie użyjemy funkcji JEŻELI. Zapis funkcji powinien wyglądać następująco:

=JEŻELI(E3<=0;C3;0)

Pierwszym argumentem funkcji jest test_logiczny, czyli sprawdzenie czy podana wartość jest mniejsza bądź równa zero (E3<=0). Drugi argument funkcji to wartość_jeżeli_prawda, czyli wartość jaką chcemy brać pod uwagę kiedy test logiczny jest spełniony (budżet planowany). Trzeci argument funkcji to wartość_jeżeli_fałsz, czyli wartość, jaką ma zwrócić funkcja, kiedy warunek z testu logicznego nie zostaje spełniony – wartość 0. Tak przygotowaną formułę zatwierdzamy przyciskiem Enter i kopiujemy na wiersze poniżej. Otrzymamy wartości zwrócone przez funkcję JEŻELI w kolumnie Minus (rys. 4).

(Rys. 4) Wyniki funkcji JEŻELI w kolumnie Minus (dla ujemnych wartości w kolumnie Różnica)

W kolejnym kroku przygotujemy etykiety na plus i na minus dla danych. W kolumnie Etykiety plus można użyć funkcji MAX, czyli znaleźć wartość maksymalną pomiędzy wartościami z kolumny Różnica a wartością zero. Zapis funkcji powinien wyglądać następująco:

=MAX(E3,0)

Powyższą formułę zatwierdzamy przyciskiem Enter i kopiujemy na wiersze poniżej. Otrzymamy tylko wartości dodatnie w kolumnie Etykiety plus.

Analogicznie aby otrzymać wartość ujemną użyjemy funkcji MIN, czyli znajdziemy wartość minimalną między wartościami z kolumny Różnica a wartością zero. Zapis funkcji powinien wyglądać następująco:

=MIN(E3,0)

Powyższą formułę zatwierdzamy przyciskiem Enter i kopiujemy na wiersze poniżej. Otrzymamy tylko wartości ujemne w kolumnie Etykiety minus (rys. 5).

(Rys. 5) Wartości Etykiet plus i Etykiet minus, obliczone za pomocą funkcji MAX i MIN

W ten sposób stworzyliśmy wszystkie dodatkowe obliczenia, których potrzebujemy do stworzenia na wykresie zależności budżetu - faktycznego od planowanego. W pierwszym wierszu znajdują się podpowiedzi do czego są potrzebne poszczególne obliczenia. Wartości z kolumn Budżet i Faktyczny będą seriami pomocniczymi na wykresie, natomiast wartości z kolumn Plus i Minus będą seriami głównymi. Należy zaznaczyć odpowiednie kolumny, czyli: Województwo, Budżet i Faktyczny, a następnie przytrzymując klawisz Ctrl zaznaczyć dodatkowo kolumny: Plus i Minus (rys. 6).

(Rys. 6) Zaznaczenie odpowiednich kolumn do stworzenia wykresu

Rozwijamy ikonkę z poleceniem wykres kolumnowy (punkt 2 na rys. 7) z karty Wstawianie (punkt 1), a następnie wybieramy polecenie Skumulowany kolumnowy (punkt 3).

(Rys. 7) Ścieżka dostępu do polecenia Skumulowany kolumnowy

Od Excela 2013 bez problemu można wykonać taki wykres. Otrzymujemy automatyczny wykres na podstawie zaznaczonych danych przedstawiony na rys. 8.

(Rys. 8) Automatyczny wykres na podstawie zaznaczonych danych

Stworzony automatycznie wykres pokazuje dane w sposób, który nie odpowiada naszym potrzebom, dlatego trzeba go nieco zmodyfikować. Przede wszystkim w legendzie pojawiają się nazwy województw zamiast serii. Można to zmienić za pomocą polecenia Przełącz wiersz/ kolumnę z karty Projekt wykresu (rys. 9).

(Rys. 9) Polecenie Przełącz wiersz/ kolumnę

Otrzymujemy wykres przedstawiony na rys. 10 z zamienionymi seriami danych.

(Rys. 10) Wykres kolumnowy z zamienionymi seriami danych

Kolorystyka wykresu zależy od wybranego aktualnie schematu kolorystycznego. W celu zmiany kolorystyki rozwijamy polecenie Kolory z karty Układ strony i wybieramy np. Aerodynamiczny (rys. 11).

(Rys. 11) Układ kolorystyczny dla wykresu (polecenie Kolory z karty Układ strony)

Dla poprawy czytelności danych serie - Budżet i Faktyczny – można włożyć na oś pomocniczą. Od Excela 2013 można to zrobić za pomocą polecenia Zmień typ wykresu z karty Projekt wykresu (rys. 12).

(Rys. 12) Polecenie Zmień typ wykresu

Po użyciu polecenia otworzy się okno Zmieniania typu wykresu. Przechodzimy na kartę Wszystkie wykresy (punkt 1 na rys. 13) i wybieramy wykres Kombi (punkt 2). W polu Wybierz typ wykresu i oś dla serii danych ustawiamy serie Budżet i Faktyczny jako Oś pomocniczą (zaznaczamy checkboxy w punktach 3 i 4). Następnie ustawiamy Typ wykresu dla poszczególnych serii, czyli dla serii Budżet i Faktyczny wybieramy typ Kolumnowy grupowany, natomiast dla serii Plus i Minus wybieramy typ Skumulowany kolumnowy. Dzięki takiemu ustawieniu uzyskujemy efekt, że seria Plus i Minus znajduje się w tle wykresu za budżetem planowanym i faktycznym. Tak przygotowane parametry zmieniania typu wykresu zatwierdzamy przyciskiem OK.

(Rys. 13) Parametry Zmieniania typu wykresu

Otrzymamy zmieniony wykres przedstawiony na rys. 14.

(Rys. 14) Wykres po zmianie typu

Efekt wizualny można wzmocnić zmieniając odstępy pomiędzy poszczególnymi kolumnami. Zaznaczamy dowolną serię, następnie za pomocą skrótu klawiszowego Ctrl+1, otwieramy okno Formatowania serii danych, gdzie w Opcjach serii znajdujemy parametr Szerokość odstępu i ustawiamy go na poziomie 70% (rys. 15).

(Rys. 15) Formatowanie serii danych, zmiana szerokości odstępu

Analogicznie zmniejszamy szerokość odstępu dla pozostałych serii danych. Dzięki zmniejszeniu szerokości odstępu, serie danych się poszerzą. Otrzymamy poprawiony wykres przedstawiony na rys. 16.

(Rys. 16) Wykres po zmianie szerokości serii danych

W kolejnym kroku usuwamy osie pomocnicze z prawej strony wykresu, czyli zaznaczamy osie i usuwamy za pomocą klawisza Delete. Następnie – w analogiczny sposób - usuwamy linie pomocnicze. Otrzymujemy dzięki temu bardziej czytelny wykres przedstawiony na rys. 17.

(Rys. 17) Wykres po usunięciu zbędnych informacji

Dla przećwiczenia innych opcji załóżmy, że chcemy zmienić obecną kolorystykę. Można je zmienić osobno dla każdej serii danych. Zaznaczamy serię danych np. Budżet i rozwijamy polecenie Wypełn. Kształtu (punkt 2 na rys. 18) z karty Formatowanie (punkt 1), a następnie wybieramy kolor (punkt 3).

(Rys. 18) Zmiana koloru wypełnienia kształtu (kolor wypełnienia serii Budżet)

Następnie rozwijamy polecenie Kontury kształtu (punkt 2 na rys. 19) z karty Formatowanie (punkt 1) i wybieramy kolor – często najlepszym rozwiązaniem jest ciemniejszy kolor niż ten z wypełnienia (punkt 3).

(Rys. 19) Zmiana koloru konturu kształtu (kontur serii Budżet)

Analogicznie postępujemy dla pozostałych serii, czyli Faktyczny, Plus i Minus. Otrzymamy Wykres zmieniony kolorystycznie przedstawiony na rys. 20.

(Rys. 20) Wykres zmieniony kolorystycznie

Na wykresie zaznaczamy - wartości na osi y – i usuwamy je za pomocą klawisza Delete. Dzięki temu wykres powiększy się do szerokości strony. Jednak wartości budżetu musimy umieścić na wykresie, ale żeby były czytelne – na odpowiednich seriach. Zaznaczamy serie danych, najpierw Budżet, klikamy na ikonkę z plusem w prawym górnym rogu wykresu i rozwijamy polecenie Etykiety danych. Następnie wybieramy polecenie Koniec wewnętrzny (rys. 21).

(Rys. 21) Dołożenie Etykiet danych na wykresie

Analogicznie dokładamy etykiety danych dla serii Faktyczny (rys. 22).

Problem pojawia się przy dokładaniu etykiet danych do serii, które znajdują się z tyłu, czyli serii Plus i Minus. Przy użyciu polecenia Koniec wewnętrzny wartości te zaciemnią nam wykres. Musimy je przestawić, ale zrobimy to w późniejszym etapie pracy.

(Rys. 22) Wykres z etykietami danych

Etykiety serii danych Plus i Minus przedstawiają wartości tych kolumn. Aby wykres był bardziej zrozumiały, można zastąpić te wartości danymi z kolumn Etykiety Plus i Etykiety Minus. Od Excela 2013 dodano nową funkcjonalność, dzięki które można - zaznaczyć serię danych Plus, a następnie w Formatowaniu etykiet danych przejść na kartę Opcje Etykiet i zaznaczyć checkbox przy opcji Wartość z komórek (zaznaczone strzałką na rys. 23). Opcja ta pozwoli pokazać wartości od razu z całego zakresu danych (kolumna Etykiety plus).

(Rys. 23) Opcja Wartość z komórek

W wyniku tego otrzymamy jednak zaciemniony wykres, a to dlatego, że pozostały aktywne opcje Wartość i Pokaż linie wiodące w Opcjach etykiet. Wystarczy odznaczyć checkboxy przy powyższych opcjach i otrzymamy wykres przedstawiony na rys. 24.

(Rys. 24) Wykres z wartościami z kolumny Etykiety plus zamiast wartości z kolumny Plus

Teraz dane prezentują się o wiele lepiej, przede wszystkim dlatego, że dla województwa Mazowieckie zniknęła wartość zero. Wartości te są dodatnie, czyli przychód faktyczny był większy od przychodu planowanego, więc można zmienić ich kolor na taki sam jak seria danych. Rozwijamy ikonkę z literą A z karty formatowanie i wybieramy taki sam kolor jak dla serii danych Plus (rys. 25). Można dodatkowo pogrubić dane za pomocą skrótu klawiszowego Ctrl+B, aby były lepiej widoczne i zwiększyć czcionkę W karcie Narzędzia główne.

(Rys. 25) Zmiana koloru czcionki wartości w karcie Formatowanie

Analogicznie postępujemy dla etykiet serii danych Minus, czyli w Opcjach etykiet zaznaczamy checkbox przy opcji Wartość z komórek (wybieramy komórki z kolumny Etykiety minus) i odznaczamy checkboxy przy opcjach Wartość i Pokaż linie wiodące. Ponadto wybieramy położenie etykiety - środek. Następnie zmieniamy kolor czcionki i jej rozmiar oraz ją pogrubiamy. Otrzymamy wykres przedstawiony na rys. 26.

(Rys. 26) Wykres z nałożonymi etykietami z kolumn Etykiety plus i Etykiety minus

Jednak wykres nadal nie wygląda tak jak byśmy sobie tego życzyli.

Naszym zadaniem będzie przeniesienie poszczególnych etykiet (zielonych i czerwonej) nad słupki. Zaznaczamy pojedynczą etykietę (dwukrotne kliknięcie myszką) i ręczenie przenosimy ją nad odpowiedni słupek (zielony lub czerwony). Analogicznie przenosimy pozostałe etykiety. Otrzymamy wykres przedstawiony na rys. 27.

(Rys. 27) Wykres z przesuniętymi etykietami serii danych

W kolejnym etapie zmieniamy tytuł wykresu. W tym celu klikamy na domyślny tytuł i ręcznie wpisujemy poprawną nazwę. Możemy również zmienić położenie tytułu przeciągając go np. w bok (rys. 28).

(Rys. 28) Wykres po zmianie tytułu

Nadszedł czas na dopracowanie legendy – mamy w niej przedstawione cztery serie danych, ale nie potrzebujemy ich wszystkich. Zaznaczamy legendę i za pomocą skrótu klawiszowego Ctrl+1 otwieramy okno Formatowania legendy. W Opcjach legendy, w Położeniu legendy zaznaczamy checkbox przy opcji Na górze. Otrzymamy wtedy wykres z legendą nad wykresem. Następnie zaznaczamy legendę i ręcznie przesuwamy ją w miejsce pod Tytułem wykresu. Ponadto w legendzie nie potrzebujemy wartości Minus i Plus, więc zaznaczamy legendę jednym kliknięciem, a następnie klikamy drugi raz w konkretną część legendy – w Plus i usuwamy za pomocą klawisza Delete. Analogicznie usuwamy część legendy Minus (rys. 29).

(Rys. 29) Wykres po zmianie legendy

Przez przesuwanie poszczególnych elementów, sam wykres zajmuje zbyt mało miejsca, jego okno się zmniejszyło. Zaznaczamy okienko z samym wykresem i ręcznie poszerzamy w górę, tak aby zajmował jak najwięcej miejsca, ale nie nachodził na legendę. Pojawi się w związku z tym problem z etykietami danych, które przestawialiśmy ręcznie, ponieważ po poszerzeniu wykresu one się nie przesuną. Należy je jeszcze raz przesunąć ręcznie w odpowiednie miejsce. W tym przykładzie możemy sobie pozwolić na ręczne zmiany położenia elementów wykresu, ponieważ dane się nie zmienią, nie są dynamiczne. Otrzymamy wykres przedstawiony na rys. 30.

(Rys. 30) Wykres po poszerzeniu obszaru słupków

W danych wyjściowych zawarte były wartości zerowe, które na wykresie się nie pokazują. Dzieje się tak dzięki niestandardowemu formatowaniu liczb, zapisanemu w postaci # ##0; -# ##0;. Kopiujemy zapis formatowania i jego działanie pokażemy na kolumnach Różnica, Plus i Minus. Zaznaczamy powyższe kolumny i za pomocą skrótu klawiszowego Ctrl+1 otwieramy okno Formatowania komórek. W karcie Liczby przechodzimy na kategorię Niestandardowe, gdzie w polu Typ wklejamy wcześniej skopiowany zapis formatowania. Zatwierdzamy taki zapis przyciskiem OK (rys. 31).

(Rys. 31) Okno Formatowania komórek (formatowanie niestandardowe)

Dzięki temu niestandardowemu formatowaniu komórek z danych znikną wartości zero (rys. 32).

(Rys. 32) Dane bez wartości zero (działanie formatowania niestandardowego)

Na wykresie nie ma wartości zero, ponieważ domyślnie wykres kopiuje styl formatowania z komórek. Gdybyśmy zmienili formatowanie na ogólne w kolumnie Etykiety minus, otrzymalibyśmy wartości zero umieszczone na wykresie jak na rys. 33.

(Rys. 33) Wartości zero na wykresie przy ogólnym formatowaniu komórek w kolumnie Etykiety Minus

Podsumowując otrzymaliśmy dobrze prezentujący się wykres, czytelny, z kolorystyką ustawioną według naszego gustu. Wykres przedstawiający zależność budżetu faktycznego i planowanego (rys. 34).

(Rys. 34) Wykres zależności budżetu faktycznego do planowanego

Polecamy także:

• Wyszukiwanie bliskich wartości np. nieco tańszego i droższego laptopa!

• Jak działa X.WYSZUKAJ – najnowsza funkcja Excela!