Jak stworzyć wykres słupkowy z linią wskazującą np. zakładaną wartość sprzedaży?

Z filmu dowiesz się:

• Jak stworzyć przejrzysty wykres słupkowy?

• W jaki sposób pokazać, które dane znajdują się poniżej założeń?

• Do czego jeszcze może służyć linia oddzielająca?

Z tej lekcji dowiesz się jak stworzyć wykres słupkowy z pionową linią określającą np. wymagany współczynnik, średnią czy jakąś istotną dla nas wartość w odniesieniu do słupków z danymi. Wykres ten będzie się prezentował jak ten przedstawiony na rys. 1.

(Rys. 1) Przykładowy wykres słupkowy z linią określającą wymagany poziom danej wartości

W tabeli (dane wejściowe) podany został współczynnik konwersji z reklam oraz wymagany współczynnik. W kolumnach Powyżej i Poniżej są obliczone aktualne współczynniki, które przekraczają wymagany wynik oraz te, które nie osiągnęły wymaganego poziomu (rys. 2). Współczynniki te zostały obliczone za pomocą funkcji JEŻELI.

(Rys. 2) Dane wejściowe do zadania

Współczynnik, który przekracza wymagany próg obliczymy poprzez sprawdzenie warunku w funkcji JEŻELI - czy współczynnik z kolumny B jest większy od wartości wymaganej. Jeśli warunek jest spełniony, funkcja ma zwrócić wartość z kolumny B - jeśli nie, to chcemy otrzymać wartość zero. Zapis powinien wyglądać następująco:

=JEŻELI(B2>C2;B2;0)

(Rys. 3) Obliczenie współczynników przekraczających wymaganą wartość

Analogicznie obliczamy współczynnik nie przekraczający wymaganej wartości, z tą różnicą, że wartość w kolumnie B ma być mniejsza od wartości w kolumnie C.

Dane z tabeli z rysunku 2 przedstawimy na wykresie. Najpierw należy zaznaczyć w tabeli wartości, które w pierwszej kolejności chcemy przedstawić na wykresie, czyli zaznaczamy kolumnę Kanał dostępu, następnie przytrzymując klawisz Ctrl zaznaczamy kolumny Powyżej i Poniżej. Po zaznaczeniu odpowiednich danych, rozwijamy ikonę polecenia Wstaw wykres kolumnowy lub słupkowy (punkt 2 na rys. 4) z karty Wstawianie (punkt 1) i wybieramy typ wykresu Słupkowy standardowy (grupowany – punkt 2).

(Rys. 4) Ścieżka dostępu do wstawienia wykresu słupkowego grupowanego

Zostanie wstawiony domyślny wykres, przedstawiony na rys. 5.

(Rys. 5) Domyślny wykres słupkowy na podstawie zaznaczonych danych

Słupki mają dwa kolory, wynika to z tego, że są dwie serie danych (współczynniki wyższe i niższe od wymaganej wartości). Na tym etapie zajmiemy się edycją wykresu. W tym celu korzystając ze skrótu klawiszowego Ctrl+1, otwieramy panel Formatowania serii danych. W panelu tym przechodzimy na grupę poleceń Opcje serii (ikonka oznaczona 1 na rys. 6), następnie zwiększamy współczynnik Nakładanie serii do wartości 100 % (punkt 2). Widać, że na domyślnym wykresie słupki są poprzesuwane względem nazw stron www. Wynika to z tego, że jest już zaplanowane miejsce na drugą serię (tą o wartości zero). Zwiększenie nakładania serii powoduje, że wartości zerowe są niejako ukryte pod słupkami z innymi wartościami.

(Rys. 6) Opcje serii (nakładanie serii)

Ponadto opcją Szerokość odstępu (punkt 3 na rys. 6) można zwiększyć szerokość słupków. Powstaje wykres przedstawiony na rys. 7.

(Rys. 7) Wykres po zwiększeniu Szerokości odstępu i Nakładania serii

Następnym elementem wykresu, który zmodyfikujemy będzie kolejność kanałów dostępu, ponieważ nie odpowiada ona kolejności w danych wejściowych. Zaznaczamy opisaną oś Y, następnie używając skrótu klawiszowego Ctrl+1, otwieramy właściwości wykresu (Formatowanie osi). Przechodzimy na zakładkę Opcje osi i zaznaczamy checkbox przy opcji Kategorie w kolejności odwrotnej (punkt 1 na rys. 8). Ponadto musimy pamiętać o zmianie Przecięcia z osią poziomą, gdzie zaznaczamy opcję Przy kategorii maksymalnej (punkt 2), ma to na celu przeniesienie wartości pod wykres.

(Rys. 8) Modyfikacja wyświetlania danych

W kolejnym etapie powinniśmy poprawić oś Y, ponieważ ma ona zbyt dużą dokładność procentową, która została pobrana z danych wejściowych. Na wykresie dwa miejsca po przecinku nie są potrzebne, więc zaznaczamy wartości na osi i otwieramy Formatowanie osi za pomocą skrótu klawiszowego Ctrl+1. W Opcjach osi przechodzimy na kategorię Liczby, a następnie w polu „miejsca dziesiętne” wpisujemy wartość 0 i zatwierdzamy przyciskiem Enter (rys. 9). Excel automatycznie odznaczy checkbox przy opcji Połączone ze źródłem, skąd została pobrana dokładność.

(Rys. 9) Zmiana dokładności wyświetlanych wartości

Otrzymamy wykres po zmianach kolejności danych na osi Y oraz dokładności miejsc po przecinku na osi X przedstawiony na rys. 10.

(Rys. 10) Zmodyfikowany wykres (zmiana kolejności danych na osi Y i dokładności na osi X)

Pionowe linie pomocnicze na wykresie nie będą potrzebne, czyli zaznaczamy te linie, a następnie usuwamy za pomocą przycisku Delete.

Teraz zajmiemy się tytułem wykresu, nie będziemy wpisywać go ręcznie, ale połączymy z konkretną komórką w danych wejściowych (B1). Zaznaczamy tytuł wykresu, następnie wpisujemy znak równa się (=), który pokaże się w pasku formuły i klikamy komórkę B1 z nazwą Współczynnik konwersji (zaznaczone strzałką na rys. 11). Wpisane odwołanie zatwierdzamy przyciskiem Enter. Otrzymamy wykres ze zmienionym tytułem.

(Rys. 11) Zmiana tytułu wykresu poprzez odwołanie do konkretnej komórki

Podsumowując - tytuł wykresu jest połączony z komórką B1, dzięki temu zmieniając nazwę danych w komórce B1, tytuł wykresu zmieni się wtedy automatycznie.

Teraz zajmiemy się zmianą kolorystyki wykresu przyjmując, że kolorem jasnoniebieskim mają być zaznaczone dane, które przekraczają wartość wymaganą, a kolorem czerwonym dane, które są poniżej wymaganej wartości. Zaznaczamy serię danych oznaczoną kolorem ciemnoniebieskim, a następnie rozwijamy polecenie Wypełnienie Kształtu (punkt 2 na rys. 12) z karty Formatowanie (punkt 1) i wybieramy kolor jasnoniebieski z palety barw (punkt 3).

(Rys. 12) Zmiana koloru wypełnienia danych przekraczających wymagany współczynnik

Następnie zmieniamy kolor konturów tych słupków, czyli rozwijamy polecenie Kontury kształtu (punkt 2 na rys. 13) z karty Formatowanie (punkt 1) i wybieramy trochę ciemniejszy kolor niebieski z palety barw (punkt 3).

(Rys. 13) Zmiana koloru konturów danych przekraczających wymagany współczynnik

Analogicznie postępujemy z danymi, które nie przekraczają wymaganej wartości, mianowicie zmieniamy kolor wypełnienia kształtu na jasnoczerwony i konturów kształtu na ciemnoczerwony. Otrzymamy wykres przedstawiony na rys. 14.

(Rys. 14) Wykres po zmianie kolorystyki (wypełnienie i kontury kształtu)

Otrzymaliśmy wykres, na którym od razu widać, które dane są powyżej, a które poniżej wymaganej wartości. Celem jest teraz dodanie linii, która pokaże na wykresie, ile brakuje wartościom poniżej wymaganej - do jej osiągnięcia. Musimy dodać Wymagany współczynnik do wykresu. W większości przypadków wystarczy, że zaznaczymy kolumnę Wymagany współczynnik i skopiujemy ją za pomocą skrótu klawiszowego Ctrl+C, a następnie zaznaczymy wykres i wkleimy te dane za pomocą skrótu klawiszowego Ctrl+V niejako bezpośrednio do wykresu. Doda się wtedy nowa seria danych przedstawiona na rys. 15.

(Rys. 15) Nowa seria danych dodana do wykresu

Gdyby powyższy sposób nie zadziałał poprawnie, to po zaznaczeniu danych z kolumny Wymagany współczynnik i ich skopiowaniu, rozwijamy polecenie Wklej z karty Narzędzia główne i wybieramy polecenie Wklej specjalnie (rys. 16).

(Rys. 16) Polecenie Wklej specjalnie

Otwiera się okienko Wklejania specjalnego do wykresu, gdzie można ustalić szczegóły wstawienia danych, czy np. można wstawić dane jako punkty czy jako serie. W polu Dodaj komórki jako wybieramy opcję Nowe serie, w polu Wartości (Y) wybieramy Kolumny i zaznaczamy checkbox przy poleceniu Nazwy serii w pierwszym wierszu. Tak ustawione parametry wklejania specjalnego zatwierdzamy przyciskiem OK (rys. 17).

(Rys. 17) Parametry wklejania specjalnego

Otrzymamy taki sam wykres jak na rys. 15, z dodaną nową serią danych. Chcąc aby ta seria danych znalazła się na osi pomocniczej, zaznaczamy ją i za pomocą skrótu klawiszowego Ctrl+1 otwieramy panel Formatowania serii danych. Przechodzimy na zakładkę Opcje serii i zaznaczamy polecenie Oś pomocnicza (zaznaczone strzałką na rys. 18).

(Rys. 18) Polecenie Oś pomocnicza

W kolejnym etapie należy zmodyfikować tą oś pomocniczą, aby jej wartość maksymalna była taka sama jak dla reszty danych. Zaznaczamy tę oś (dane nad wykresem), za pomocą skrótu klawiszowego Ctrl+1 przechodzimy do Formatowania osi, gdzie w grupie poleceń Opcje osi, wpisujemy wartość maksymalną (Granica) jako 16 (zaznaczone strzałką na rys. 19). Otrzymamy wykres z poprawioną osią pomocniczą.

(Rys. 19) Ustawienie maksymalnej wartości na osi pomocniczej

Kiedy już mamy dodatkową serię danych dopasowaną do maksymalnej wartości osi, można zaznaczyć tę oś i za pomocą klawisza Delete ją usunąć. Otrzymujemy wykres przedstawiony na rys. 20.

(Rys. 20) Wykres po usunięciu osi pomocniczej

Oś pomocnicza nie jest istotna, ważne jest natomiast, że słupki są na odpowiedniej wysokości. Do tych słupków dołożymy linię trendu. W tym celu zaznaczamy serię wymagany współczynnik, a następnie klikamy znak plusa obok prawego górnego rogu wykresu (Elementy wykresu) i zaznaczamy checkbox przy poleceniu Linia trendu (rys. 21).

(Rys. 21) Elementy wykresu – linia trendu

Linia ta jest zaznaczona w takim samym kolorze jak cała seria, dlatego może być słabo widoczna. Teraz musimy ukryć słupki wymaganego współczynnika. Zależy nam aby było widać tylko linię trendu. Zaznaczamy serię, którą chcemy ukryć, następnie rozwijamy polecenie Wypełnienie Kształtu z karty Formatowanie i wybieramy opcję Brak wypełnienia (rys. 22).

(Rys. 22) Polecenie Brak wypełnienia

Następnie rozwijamy polecenie Kontury kształtu i analogicznie wybieramy polecenie Brak konturów. Otrzymamy wykres z niewidoczną serią danych Wymagany współczynnik przedstawiony na rys. 23.

(Rys. 23) Wykres z niewidoczną serią danych Wymagany współczynnik

Te serie nadal są na wykresie, po prostu są niewidoczne, można je zaznaczyć ręcznie jeśli wiemy gdzie się znajdują lub wybrać tę serię danych z listy rozwijanej na karcie Formatowanie (rys. 24).

(Rys. 24) Możliwość zaznaczenia serii danych niewidocznych na wykresie

Teraz zajmiemy się korektą linii trendu, mianowicie musimy zmienić ją tak, aby była lepiej widoczna. W tym celu zaznaczamy linię trendu i za pomocą skrótu klawiszowego Ctrl+1 otwieramy panel Formatowania linii trendu. Zaczynamy od zmiany kolorystyki, czyli w Opcjach linii trendu przechodzimy na grupę poleceń Wypełnienie i linia. W pierwszej kolejności w polu Kolor wybieramy z rozwijanego panelu kolor czarny, następnie w polu Typ kreski wybieramy linię przerywaną (rys. 25).

(Rys. 25) Zmiana koloru i typu linii trendu

Możemy zauważyć, że linia trendu nie dochodzi do brzegów słupków, poprawić to możemy w Opcjach linii trendu w polach Prognozy (do przodu i do tyłu). Wpisujemy dowolną wartość, może być dłuższa ponieważ i tak, linia ta nie będzie widoczna za obszarem wykresu, dopasuje się do jego wysokości (rys. 26).

(Rys. 26) Prognoza linii trendu

Mamy zaznaczone najważniejsze dane, teraz zajmiemy się upiększeniem legendy. Przede wszystkim pozycja Wymagany współczynnik nie jest widoczna na wykresie, więc jeśli potrzebowalibyśmy legendy w Excelu do dalszych działań, musielibyśmy skasować ten element legendy. Tekstu legendy nie możemy zmienić, ponieważ odnosi się ona do danych, ale możemy stworzyć własną legendę poza obszarem wykresu. W tym celu rozwijamy polecenie Ilustracje (punkt 2 na rys. 27) z karty Wstawianie (punkt 1), następnie rozwijamy polecenie Kształty (punkt 3) i wybieramy np. prostokąt (punkt 4).

(Rys. 27) Ścieżka dostępu do polecenia Kształty

Po wybraniu powyższego polecenia rysujemy niewielki prostokąt pod wykresem, następnie przechodzimy do jego edycji. W tym celu zaznaczamy narysowany kształt i rozwijamy polecenie Wypełnienie Kształtu z karty Formatowanie kształtu i wybieramy kolor jasnoniebieski (ten, którego użyliśmy na wykresie) jak na rys. 28.

(Rys. 28) Wybór koloru wypełnienia kształtu z karty Formatowanie kształtu

Potem rozwijamy polecenie Kontury kształtu z karty Formatowanie kształtu i wybieramy kolor niebieski (ponownie ten sam, jakiego użyliśmy na wykresie) – rys. 29.

(Rys. 29) Wybór koloru konturu kształtu z karty Formatowanie kształtu

Tak przygotowany kształt możemy zaznaczyć, skopiować za pomocą skrótu klawiszowego Ctrl+C, a następnie wkleić obok za pomocą skrótu Ctrl+V. Przesuwamy ręcznie nowy kształt obok poprzedniego, a następnie przechodzimy do jego modyfikacji. Analogicznie jak dla pierwszego zmieniamy kolor jego wypełnienia (jasnoczerwony) oraz kolor konturu (ciemnoczerwony). Otrzymamy dwa kształty w kolorystyce odpowiadającej seriom na wykresie.

Trzecim elementem, jakiego potrzebujemy do stworzenia własnej legendy jest przerywana linia. W tym celu rozwijamy polecenie Ilustracje (punkt 2 na rys. 30) z karty Wstawianie (punkt 1), następnie rozwijamy polecenie Kształty (punkt 3) i wybieramy linię prostą (punkt 4).

(Rys. 30) Wstawianie kształtu linii

Rysując linię możemy przytrzymać klawisz Shift, sprawi to że narysowana linia na pewno będzie prosta. Po narysowaniu linii, przechodzimy do jej formatowania. W stylach kształtu, w karcie Formatowanie kształtu, wybieramy grubość linii (rys. 31).

(Rys. 31) Wybór grubości linii

W kolejnym etapie rozwijamy polecenie Kontury kształtu (punkt 2 na rys. 32) z karty Formatowanie kształtu (punkt 1), następnie rozwijamy polecenie Kreski (punkt 3) i wybieramy linię przerywaną (punkt 4).

(Rys. 32) Ścieżka dostępu do polecenia Kreski

Przygotowane własne elementy legendy przedstawia rys. 33.

(Rys. 33) Elementy do stworzenia legendy

Prezentując dane w postaci legendy poza wykresem linie siatki psują niestety efekt wizualny. Linie siatki można wyłączyć odznaczając checkbox przy poleceniu Linie siatki w karcie Widok (zaznaczone strzałką na rys. 34).

(Rys. 34) Wyłączanie Linii siatki

Ponadto dla lepszego efektu wizualnego można sprawić, że wykres (jego obszar) stanie się przeźroczysty. W tym celu zaznaczamy obszar wykresu i rozwijamy polecenie Wypełnienie Kształtu z karty Formatowanie, a następnie wybieramy opcję Brak wypełnienia (rys. 35).

(Rys. 35) Brak wypełnienia wykresu

Analogicznie wybieramy opcję Brak konturu w poleceniu Kontury kształtu. Powstanie efekt przeźroczystości wykresu przedstawiony na rys. 36.

(Rys. 36) Brak wypełnienia i konturów wykresu

Na tym etapie trzeba stworzyć ręcznie opisy do legendy, ponieważ przy legendzie stworzonej przez Excela nie ma kontroli nad tekstem. Zaznaczamy i usuwamy legendę z obszaru wykresu za pomocą klawisza Delete. Aby stworzyć opisy do kształtów z legendy rozwijamy polecenie Ilustracje (punkt 2 na rys. 37) z karty Wstawianie (punkt 1), a następnie rozwijamy polecenie Kształty (punkt 3) i wybieramy Pole tekstowe (Punkt 4).

(Rys. 37) Ścieżka dostępu do wstawienia pola tekstowego

Rysujemy pole tekstowe obok wcześniej wstawionych kształtów, następnie zajmiemy się jego edycją. Na karcie Formatowanie kształtu zmieniamy Wypełnienie kształtu na „bez wypełnienia” oraz zmieniamy Kontury kształtu na „bez konturów”. Wpisujemy w kształt nazwę elementu legendy np. „Powyżej” - a następnie dopasowujemy kształt, czyli zmieniamy jego wielkość ręcznie oraz zmieniamy w razie potrzeby wielkość czcionki w kształcie na karcie Narzędzia główne w grupie poleceń Czcionka. Tak stworzony kształt tekstowy kopiujemy (Ctrl+C) i dwa razy wklejamy (Ctrl+V). Edytujemy wklejone kształty czyli zmieniamy ich tekst i przesuwamy je w odpowiednie miejsca jak na rys. 38.

(Rys. 38) Kształty w stworzonej legendzie

Można zauważyć że elementy legendy nie są równo rozmieszczone. Aby to zmienić przytrzymujemy klawisz Ctrl i zaznaczamy wszystkie elementy, następnie rozwijamy polecenie Wyrównaj (punkt 2 na rys. 39) z karty Formatowanie kształtu (punkt 1) i wybieramy polecenie Wyrównaj do środka w poziomie (punkt 3).

(Rys. 39) Ścieżka dostępu do polecenia Wyrównaj do środka w poziomie

W kolejnym etapie ponownie zaznaczamy elementy legendy i rozwijamy polecenie Wyrównaj z karty Formatowanie kształtu, następnie wybieramy polecenie Rozłóż w poziomie (rys. 41). Działanie to sprawi, że otrzymamy równe odległości pomiędzy elementami legendy.

(Rys. 41) Ścieżka dostępu do polecenia Rozłóż w poziomie

Ostatnią rzeczą, jaką należy zrobić z legendą jest połączenie jej elementów. W tym celu zaznaczamy wszystkie elementy i wybieramy polecenie Grupuj z karty Formatowanie kształtu (rys. 42).

(Rys. 42) Ścieżka dostępu do polecenie Grupuj

Użycie polecenia Grupuj sprawiło, że poszczególne elementy legendy teraz tworzą jedną całość, dzięki czemu można je sprawnie przesuwać (rys. 43).

(Rys. 43) Pogrupowane elementy legendy

Po połączeniu elementów legendy można ją łatwo ustawić pod wykresem. Powstaje wykres z legendą stworzoną ręcznie przedstawiony na rys. 44.

(Rys. 44) Końcowy wykres z legendą stworzoną ręcznie

Polecamy także:

• Wykres słupkowy z linią określającą np. wymaganą wartość

• Jak korzystać z filtrów zaawansowanych i z wieloma kryteriami?