W dzisiejszym wpisie zajmiemy się Polecanymi wykresami z karty Wstawianie. Opcję tą omówimy na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Polecenie to znajduje się na karcie Wstawianie (rys. nr 2)
rys. nr 2 — Polecane wykresy
Udało mi się zainstalować licencję Microsoft MVP 365 i teraz
mam dostępne wszystkie najnowsze dodatki (w tym nowe wykresy), funkcjonalności
do Excela, zmieniła się również wstążka.
Mamy podstawowe dane, które chcielibyśmy przedstawić na wykresie. Kiedy klikniemy na polecenie Polecane wykresy, otworzy nam się okno Wstawianie wykresu (rys. nr 3)
rys. nr 3 — Okno wstawianie wykresu
Ciekawe jest to, że od razu możemy zobaczyć jak nasze dane
będą się prezentować w zależności od wybranego wykresu (kolumnowe, skumulowane,
słupkowe, lejkowe itp.). Wybierzmy wykres liniowy, Zatwierdzamy klikając
przycisk OK. i otrzymujemy wykres z naszymi danymi (rys. nr 4).
rys. nr 4 — Wykres liniowy
Omówimy
kilka rodzajów wykresów w zależności od typów danych, jakie chcemy
zaprezentować. Mamy przykładowe dane z liczbą mieszkańców w kilku województwach
Polski. Przy takich danych Excel sam wykrył, że w danych są nazwy województw i
daje nam możliwość (w Polecanych wykresach) przedstawienia ich na wykresie
Kartogram (rys. nr 5).
rys. nr 5 — Kartogram
My wybierzemy wykres kołowy i nasze dane zaprezentują się
następująco (rys. nr 6).
rys. nr 6 — Wykres kołowy
Jeśli w danych mamy wiersz z Sumą, to polecane wykresy będą
nieprawidłowe, ponieważ ten wiersz wstawią jako część danych do wykresu (rys.
nr 7).
rys nr 7 — Błędne wykresy z SUMĄ
W tej sytuacji musimy zaznaczyć konkretny obszar (bez wiersza
z sumą) i wtedy w poleceniu Polecane wykresy otrzymamy propozycje prawidłowych
wykresów. Aby pokazać jak najwięcej wykresów tym razem wybierzemy sobie Wykres
Pareto (rys. nr 8)
rys. nr 8 — Wykres Pareto
Kolejnym omówionym wykresem będzie histogram. Mamy w danych
wyniki z egzaminu. Przed Excelem 2013 musielibyśmy pogrupować nasze dane a teraz
korzystając z polecenia Polecane wykresy Excel sam przedstawi nam odpowiednio
wyniki na wykresie histogramu (rys. nr 9).
rys. nr 9 — Histogram
Dzięki opcji Polecane wykresy, nie musimy się zastanawiać,
który wykres najlepiej pasuje do naszych danych. Excel sam nam podpowie i
pokaże jak niektóre wykresy będą wyglądać.
Polecane wykresy są bardziej inteligentne niż zwykłe
wstawiane wykresy. Jeśli zaznaczymy jedną komórkę i wstawimy wykres liniowy
ręcznie, Excel pokaże nam za dużo serii danych (lata), wykres będzie mało
czytelny. Kiedy przy zaznaczeniu tej samej komórki użyjemy Polecanych wykresów,
to lata zostaną rozpoznane jako nazwa kategorii a serią będą kwoty sprzedaży
(rys. nr 10).
rys. nr 10 — Wykres liniowy
Kolejnym omówionym przykładem będzie wykres X‑Y. Chcemy
przedstawić jak ilość godzin nauki wpływa na wynik egzaminu i według polecenia
Polecane wykresy najodpowiedniejszy do tego będzie wykres punktowy (rys. nr 11)
rys. nr 11 — Wykres punktowy
Na koniec omówimy wykres pomocniczy, gdzie jedna wartości z
danych jest dużo mniejsza od pozostałych. Przy takich danych Excel poleci nam
wykres Kombi, gdzie główne dane (większe wartości) będą przedstawione na
wykresie kolumnowym, a pomocnicze dane (mniejsze wartości) będą przedstawione
na wykresie liniowym na osi pomocniczej (rys. nr 12).
rys. nr 12 — Wykres Kombi
Podsumowując polecenie Polecane wykresy sprawdza się
doskonale do wstawiania podstawowych wykresów, ponadto podpowiada nam, który
wykres najlepiej będzie prezentował nasze dane. Funkcjonalność ta jest świetnym
rozwiązaniem oszczędzającym nasz czas.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Dziś chcemy stworzyć w Excelu wykres schodkowy, czyli taki, który obrazuj, w którym momencie (w jaki dniu/dacie) nastąpiła zmiana parametru.
Omówimy go na prostym przykładzie stanu konta – mamy przykładowy zbiór danych, który ilustruje ile wynosiłstan konta w danym dniu, czyli 2017-04-04 stan konta wynosił 540zł i nie zmienił się do 2017-04-10, kiedy to zmienił się na 1630zł itd.
Jeśli od razu zamieścilibyśmy nasze dane na wykresie liniowym to prezentowałyby sięźle, ponieważ wyglądałoby, że stan konta zmienia się z dnia na dzień, a faktycznie tak nie jest.
Czyli zamiast wznoszącej się linii przez pierwsze 6 dni powinna być pionowa linia skoku dopiero dnia 2017-04-10.
Jak możemy zmienić nasze dane, żeby dobrze się prezentowały? Przede wszystkim ważne jest, żeby były posortowane od najstarszej daty do najnowszej. Następnie skopiujemy je sobie obok, żeby zachować oryginał. Kolejnym krokiem będzie ponowne skopiowanie danych, ale już bez nagłówków, tuż pod naszej tabeli.
Dodatkowo od razu wypełniliśmy dwie komórki na żółto, bo będziemy chcieli je usunąć z przesunięciem w górę.
Dlaczego je usuwamy? Bo potrzebujemy oryginalnej serii danych razem z serią, która jest przesunięta o 1 punkt.
Czyli, po przesunięciu serii/usunięciu komórek, mamy zaczynając od pierwszej daty parę:
2017-04-04 540 zł
Chociaż ta para nie jest już w pierwszym wierszu danych, ale ważne że kolejnym datom przypisane są dwie wartości:
2017-04-10 540 zł
2017-04-10 1 630 zł
Jedna z poprzedniej daty i druga – zmieniony stan na dany dzień.
To już wszystkie zmiany jakich potrzebujemy – zaznaczamy zakres danych i wstawiamy wykres liniowy ze znacznikami (żebyśmy widzieli położenie punktów danych).
Pozostaje nam co najwyżej dopracowanie szczegółów, jak zmiana kolorów, tytułu, wartości maksymalnych itp.
Uwaga! Czasami Excel może nie rozpoznać, że ma do czynienia z datami na osi x i musimy to sami zmieniać, czyli klikamy w oś x, naciskami Ctrl + 1 i we właściwościach osi zmieniamy ją na oś daty.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Czasami potrzebujesz porównać ze sobą wartości "przed" i "po" dla wielu np.: produktów. Jednym ze sposobów na to jest skorzystanie z tzw. slopegraphu, czyli wykresu, którego pomysłodawcą Edward Tufte.
Ten wykres opiera się na wykresie liniowym, ale ważnym jego elementem są etykiety danych na końcach linii. Standardowe podejście tworzenia tych etykiet uwzględnia dużo ręcznej pracy, dlatego na podstawie swoje wiedzy o wykresach spróbowałem swoich sił i chce zaproponować inne podejście. Do tego potrzebujemy stworzyć kolumny pomocnicze, które będą zawierały tylko wartość 1 lub 2 oraz jeszcze jedną kolumnę pomocniczą, która będzie przechowywać połączone wartości, które chcemy wyświetlać w etykietach danych.
=KWOTA(C3;0)&" "&A3
Korzystamy tu z funkcji KWOTA, żeby liczbę przedstawić jako walutę w złotówkach z dokładnością do zera miejsc po przecinku.
Slopegraph opiera się o wykres liniowy, więc od niego zaczniemy – zaznaczymy zakres komórek od A2 do C11 i z karty Wstawianie wstawimy wykres liniowy.
Tylko nie jest to wykres liniowy jakiego potrzebujemy, bo są na nim tylko 2 serie, a my potrzebujemy serii dla każdego produktu. Dlatego musimy kliknąć w polecenie Przełącz wiersz/kolumnę z karty Projektowanie narzędzi wykresów, żeby zamienić miejscami serie i kategorie.
Uzyskujemy już wykres, który bardzo przypomina slopegraph, ale musimy go jeszcze oczyścić z wszystkich niepotrzebnych rzeczy. Nie są nam potrzebne:
• Legenda
• Osie siatki
• Etykiety osi y
Dlatego wszystkie je możemy usunąć zaznaczając je i naciskając klawisz Delete.
Warto tu wspomnieć o pewnej sztuczce, żeby wartości 2014 i 2017 zostały użyte jako etykiety na osi x, a nie zostały dołożone do serii danych – przed nimi wstawiamy pojedynczy cudzysłów ' dzięki czemu Excel traktuje te liczby jako tekst i nie dodaje je do serii danych.
Slopegraph powinien być wyższy dlatego klikamy na obszar kreślenia i odpowiednio go poszerzamy.
Teraz potrzebujemy dodać jeszcze jedną serię, której zaraz będziemy musieli zmienić typ wykresu, więc nie ma dużego znaczenia jakie będzie miała ona wartości, żeby tylko nie została podpięta pod którąś z istniejących serii. Jednym z prostszych sposobów na to jest skopiowanie komórki A2 (Ctrl + C) z tekstem, zaznaczenie wykresu i wklejenie skopiowanego obszaru (Ctrl + V). Nie powinno być widać żadnych widocznych zmian na wykresie, ale jeśli skorzystasz z polecenia Zaznacz dane z karty Projektowanie to powinieneś zobaczyć, że dodała się nowa seria (Seria 10) do serii wykresu.
Ta seria posłuży nam do dodawania etykiet danych, ale musimy jej zmienić typ wykresu na punktowy. Czyli najpierw musimy ją zaznaczyć. Najprościej to zrobić korzystając z listy rozwijanej na karcie Formatowanie narzędzi wykresów.
Teraz możemy skorzystać z polecenie Zmień typ wykresu z karty Projektowanie narzędzi wykresów.
Dużo prościej wykonuje się to od wersji Excela 2013, bo istnieją wtedy wykresy kombi, gdzie możemy łatwo kontrolować, jakiego rodzaju wykresami są poszczególne serie.
Też musimy upewnić się, żeby nasza seria nie trafiła na oś pomocniczą.
Teraz jak zmieniliśmy typ wykresu dla serii możemy w końcu wskazać dla niej prawidłowe dane, czyli wchodzimy w polecenie Zaznacz dane z karty Projektowanie, a następnie odszukujemy naszą serię i ją Edytujemy i zaznaczamy odpowiednie zakresy danych i zatwierdzamy ją. Kolumna z 1 musi trafić na oś x. Wtedy nasza seria będzie "zaznaczać" wszystkie punkty serii z lewej strony, czyli pierwszej punkty.
Nawet jeśli nie korzystamy z wykresu punkowego to każda wartość wykresu ma swoją określoną pozycję na osi y i osi x. Właśnie z tej właściwości korzystamy, żeby móc szybciej dodawać inne etykiety z lewej i prawej strony serii.
Zaraz po dodaniu serii z lewej strony możemy od razu dodać serię z prawej strony, ponieważ Excel domyślne skorzysta z poprzednio dodanego typu wykresu, czyli punktowego, którego potrzebujemy, żeby oznaczyć końce serii z prawej strony (dane z kolumny C – oś y i kolumny E – oś x).
Jak mamy dodane punkty z lewej i prawej strony możemy im dodać etykiety danych (na razie zostawimy punkty, ale na koniec sprawimy, żeby były niewidoczne).
W etykietach danych chcemy mieć dwie wartości – nazwę serii odpowiadającą konkretnemu punktowi (nie jest to nazwa serii punktów) i wartość liczbową dla punktu. Od Excela 2013 jest to dużo prostsze, bo możemy w etykietach danych umieszczać dane z komórek – wystarczy, że zaznaczymy na etykiety danych, naciśniemy Ctrl + 1, żeby przejść do właściwości etykiet i w zakładce Opcje etykiet zaznaczyć checkbox Wartości z komórek. Wtedy otworzy się okno z polem, w którym możesz zaznaczyć zakres komórek, z którego będą pobierane wartości do etykiet osi.
Przed Excelem 2013 musiałbyś dla każdego punktu (etykiety) tworzyć połączoną wartość, która ma się wyświetlać w etykiecie i żeby tą połączoną wartość pobrać do konkretnej etykiety musiałbyś ją zaznaczyć, nacisnąć równa się i kliknąć na komórkę, której wartość chcesz, żeby pojawiła się w etykiecie, a na koniec zatwierdzić swój wybór Enterem. Dlatego nie miałoby sensu tworzenie tych dodatkowych serii tylko od razu dodawać i modyfikować etykiety dla pojedynczych linii.
Po dodaniu etykiet musimy jeszcze ewentualnie poszerzyć wykres i poprzesuwać trochę etykiety, żeby nie nachodziły na siebie. Od Excela 2013 możemy zmieniać rozmiar pojedynczej etykiety danych, a wcześniej jest ona mocno powiązana z rozmiarem wykresu i obszaru kreślenia.
Podobnie postępujemy z serią punktów po prawej stronie, czyli też dodajemy do nich etykiety, tylko przy nich potrzebujemy pobrać z Wartości komórek z kolumny F, ponieważ chcemy żeby liczba wyświetlała się bliżej linii.
Teraz możemy ukryć punkty, czyli zaznaczamy serię punktów, naciskamy Ctrl + 1 i we właściwościach, na zakładce Opcje znaczników, zaznaczamy pole wyboru Brak.
Na koniec jeszcze możemy uzupełnić nazwę naszego wykresu, a dokładniej, żeby była ona połączona z komórką A1, czyli zaznaczamy tytuł, piszemy znak równa się, klikamy komórkę A1 i zatwierdzamy nasz wybór Enterem. Uzyskujemy tytuł powiązany z wartością komórki. Podobnie jak wcześniej tworzyliśmy etykiety danych.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
Potrzeba powiedzieć kilka rzeczy o wykresach liniowych i wykresach punktowych, czym od siebie się różnią i kiedy powinieneś stosować wykres liniowy, a kiedy punktowy.
Jako przykład posłuży nam sytuacja, gdzie chcemy przedstawić zależność 3 parametrów od jednej zmiennej (częstotliwości). Dodatkowo będziemy chcieli jeszcze dodać jeszcze 1 parametr do wykresu, którego parametry były wyliczony dla innych wartości zmiennej (częstotliwości).
Jeśli na podstawie pierwszej tabeli wstawisz wykres liniowy, to będą na nim 4 serie – częstotliwość zostanie dodana jako czwarta fioletowa seria.
Musisz ją zaznaczyć i usunąć. Wystarczy, że naciśniesz klawisz Delete po jej zaznaczeniu.
Ponieważ chcesz, żeby wartości częstotliwości były pokazane na osi poziomej, to musisz wybrać polecenia Zaznacz Dane z karty Projektowanie, a następnie musisz edytować oś kategorii.
Później wystarczy, że zaznaczysz zakres komórek z częstotliwością:
Po zatwierdzeniu edycji osi kategorii każdy punkt zostanie odpowiednio podpisany, ale jeśli przyjrzysz się uważnie punktom, to zauważysz, że wszystkie są w tej samej odległości, mimo, że nie zawsze różnica pomiędzy poszczególnymi wartościami jest taka sama.
Czyli wykres liniowy nie nadaje się do sytuacji, gdy chcesz prezentować zależności parametrów od właściwości (y od x). Za to świetnie nadaje się, gdy odstępy czasu lub liczb są identyczne, albo to tekstowe pozycje z listy.
Czyli do tego przykładu powinniśmy wykorzystać wykres punktowy (dokładnie punktowy z prostymi liniami i znacznikami), tylko Excel może niepoprawnie rozmieścić dane na wykresie.
Wynika to z tego, że Excel przy wstawianiu wykresu punktowego pierwszą kolumnę interpretuje jako oś poziomą (x), a pozostałe kolumny wkłada jako serie na oś pionową (y).
Dlatego w tym przykładzie musimy zaznaczyć kolumnę z częstotliwością, złapać z krawędź jej zaznaczenia i przytrzymując klawisz Shift odpowiednio ją przesuną w lewo. Po przesunięciu Excel poprawnie zinterpretuje dane i będziesz mógł zauważyć, że niektóre punkty znajdują się bliżej siebie.
Przydałoby się jeszcze pewnie zmniejszyć maksimum na osi poziomej (x) do 10, ale ważniejsze jest dla nas dodanie nowej serii danych. Ma ona całkiem inne punkty i jest ich mniej niż dla wcześniejszych trzech parametrów. Niemożliwe byłoby ich poprawne dodanie do wykresu liniowego, ale do punktowego to całkiem prosta sprawa. Wystarczy, że ponownie klikniesz w polecenie Zaznacz dane z karty Projektowanie narzędzi wykresów, tylko tym razem wybierzesz przycisk dodaj Serię:
Ponieważ jest to wykres punktowy, więc będą aż trzy pola do wypełnienia – nazwa serii, wartości x i y.
Teraz wystarczy zatwierdzić wpisane dane i już masz poprawny wykres z dodanym czwartym parametrem (fioletowym). Możesz zobaczyć, że nie dochodzi ona aż tak daleko jak poprzednie trzy, bo nie była mierzona dla tych wartości częstotliwości.
Twoje zadanie na dziś to przedstawić na wykresie temperatury mierzone co przykładowe 10 minut. Jak masz dane odpowiednio uporządkowane, to sprawa jest prosta.
Zaznaczasz pojedynczą komórkę w danych, a następnie wstawiasz wykres liniowy.
Niestety prezentuje się on fatalnie ze względu na dane na osi poziomej (kategorii). Wynika to z tego, że są tutaj daty z godziną w ciągu dnia, a minimalny okres czasu jaki Excel systemowo potrafi pokazywać na osi wykresu to dzień.
Dlatego musimy zmienić sposób wyświetlania na osi poziomej. Wystarczy, że w nią klikniesz (zaznaczysz ją), a następnie naciśniesz Ctrl + 1, żeby wyświetliło się okno właściwości.
Wystarczy, że zmienisz typ osi, na oś tekstu i już dane będą wyglądały znacznie lepiej.
Ewentualnie możesz jeszcze w części etykiet możesz zmienić jednostkę interwału na dwanaście, zamiast domyślnej, to wtedy będzie pokazywał się czas w odstępach dwóch godzin.
Powyższe obrazy były z Excela 2013, ale w Excelu 2010 i 2007 wygląda to podobnie tylko opcje są w ciut innych miejscach, ale też dostajesz się do nich przez zaznaczenie poziomej osi (kategorii) i naciśnięcie skrótu Ctrl + 1.
