W świecie analizy danych, prezentacja sumy wartości na wykresie kolumnowym skumulowanym może znacznie zwiększyć czytelność i wartość informacyjną naszych wizualizacji. Umieszczenie wartości zbiorczej na szczycie wykresu pozwala odbiorcom na szybkie uchwycenie całościowego obrazu prezentowanych danych, co jest szczególnie przydatne podczas prezentacji biznesowych lub raportowania.
Technika ta wymaga kilku prostych, ale precyzyjnych kroków w programie Excel, które znacząco podniosą jakość twojego wykresu.
Dlaczego warto pokazywać sumę na wykresie kolumnowym skumulowanym?
Wykresy kolumnowe skumulowane są doskonałym narzędziem do przedstawiania zależności między częściami a całością. Jednak standardowo Excel nie pokazuje sumy wszystkich wartości, co czasami jest kluczową informacją dla odbiorcy. Dodanie wartości sumy na szczycie każdej kolumny pozwala na natychmiastowe zrozumienie wielkości całkowitej, bez konieczności sumowania poszczególnych segmentów "w głowie".
W moim wideo pokazuję, jak można elegancko rozwiązać ten problem, tworząc profesjonalnie wyglądające wykresy, które zawierają zarówno szczegółowe dane w postaci segmentów kolumn, jak i sumy całkowite widoczne na pierwszy rzut oka.
Krok po kroku: Tworzenie wykresu z wartościami sumy
Przygotowanie danych i stworzenie podstawowego wykresu
Pierwszym krokiem jest odpowiednie przygotowanie danych. Oprócz wartości dla poszczególnych kategorii, które chcemy przedstawić w kolumnach skumulowanych, musimy obliczyć sumę tych wartości. Ta suma będzie później widoczna na szczycie każdej kolumny.
Aby stworzyć podstawowy wykres:
Zaznacz dane, wraz z obliczonymi sumami
Przejdź do karty "Wstawianie"
Wybierz opcję "Wykres kolumnowy skumulowany"
Po wykonaniu tych czynności, suma pojawi się jako ostatni element w każdej kolumnie skumulowanej. Jest to jednak dopiero początek procesu, ponieważ chcemy, aby suma była zapisana na wykresie, a nie stanowiła po prostu kolejny element kolumny.
Zmiana typu wykresu dla serii sumy
Kluczowym etapem jest zmiana sposobu prezentacji sumy. Zamiast być częścią kolumny skumulowanej, suma powinna być przedstawiona jako oddzielny element. W tym celu należy:
Kliknąć prawym przyciskiem myszy na serię danych reprezentującą sumę
Wybrać opcję "Zmień typ wykresu seryjnego"
W otwartym oknie dialogowym wybrać inny typ wykresu dla serii sumy
W moim wideo pokazuję, że najlepszym wyborem jest wykres punktowy (bez osi pomocniczej). Wykres punktowy umieszcza wartości dokładnie w punktach odpowiadających poszczególnym kolumnom (wartości 1, 2, 3 na osi X), co idealnie pasuje do naszego celu. Można również rozważyć wykres liniowy, ale wykres punktowy daje nam większą kontrolę nad formatowaniem.
Dostosowywanie wyglądu i czytelności wykresu
Dodawanie i formatowanie etykiet danych
Po zmianie typu wykresu dla serii sumy, kolejnym krokiem jest dodanie etykiet danych, które będą pokazywać wartości sumy. W tym celu:
Kliknij na serię punktową reprezentującą sumy
Użyj ikony "plus" obok wykresu i wybierz "Etykiety danych"
Dostosuj pozycję etykiet wybierając opcję "Powyżej"
Etykiety danych są kluczowym elementem, ponieważ to właśnie one pokazują wartości sum, które chcemy wyeksponować. Warto zadbać o ich odpowiednie sformatowanie, aby wyróżniały się na wykresie.
Usuwanie zbędnych elementów i poprawianie wyglądu
Aby nasz wykres był czytelny i profesjonalny, musimy usunąć zbędne elementy i odpowiednio sformatować pozostałe:
Usuń znaczniki punktowe z serii sumy (zaznacz serię, użyj Ctrl+1, przejdź do opcji znaczników i wybierz "Brak")
Rozważ usunięcie poziomych linii siatki dla lepszej czytelności
Dodaj etykiety danych również do pozostałych serii, jeśli jest to potrzebne
Dostosuj formatowanie etykiet sumy, na przykład dodając delikatne obramowanie w innym kolorze (np. pomarańczowym), aby podkreślić ich odrębność
Szczególnie istotne jest usunięcie znaczników punktowych, które mogą niepotrzebnie zaśmiecać wykres. Chcemy, aby widoczne były tylko etykiety z wartościami sum, a nie punkty, które je reprezentują.
Poprawianie legendy i tytułu wykresu
Ostatnim etapem jest dostosowanie legendy i dodanie odpowiedniego tytułu:
Kliknij raz na legendę, aby zaznaczyć całą legendę
Kliknij na element legendy reprezentujący sumę
Naciśnij klawisz Delete, aby usunąć ten element z legendy
Dodaj odpowiedni tytuł wykresu, który można powiązać z komórką lub skopiować z arkusza
Usunięcie sumy z legendy jest ważnym krokiem, ponieważ wartości te są już widoczne jako etykiety na wykresie, a ich obecność w legendzie mogłaby wprowadzać zamieszanie. Pamiętaj, że dobra wizualizacja danych powinna być jak najbardziej czytelna i pozbawiona redundantnych elementów.
Praktyczne zastosowania techniki
Technika pokazywania sum na wykresach kolumnowych skumulowanych jest szczególnie przydatna w wielu sytuacjach biznesowych i analitycznych. Może być wykorzystana do prezentacji:
Sprzedaży produktów w podziale na kategorie z łączną wartością sprzedaży
Budżetów departamentów z całkowitym budżetem firmy
Wyników finansowych w podziale na kwartały z rocznym podsumowaniem
Statystyk demograficznych z całkowitą liczbą badanej populacji
Zastosowanie tej techniki wizualizacji znacząco podnosi wartość informacyjną wykresu, pozwalając odbiorcom na szybkie uchwycenie zarówno struktury danych, jak i ich całkowitej wartości. Jest to szczególnie cenne podczas prezentacji, gdy czas na analizę danych przez odbiorców jest ograniczony.
Zalety i potencjalne problemy
Główną zaletą przedstawionej techniki jest zwiększenie czytelności danych i umożliwienie odbiorcom szybszego zrozumienia prezentowanych informacji. Suma widoczna na pierwszy rzut oka pozwala na natychmiastowe uchwycenie skali prezentowanego zjawiska.
Należy jednak pamiętać o potencjalnych pułapkach. Jeśli mamy wiele kolumn o znacznie różniących się wysokościach, etykiety sum mogą być trudne do odpowiedniego rozmieszczenia. W takich przypadkach warto rozważyć dodatkowe dostosowanie pozycji etykiet lub wykorzystanie innych technik wizualizacji.
W przypadku bardziej złożonych danych, gdzie mamy wiele kategorii i podkategorii, warto również zastanowić się nad zastosowaniem innych typów wykresów, takich jak wykresy hierarchiczne lub wykresy kaskadowe, które mogą lepiej oddawać złożone zależności między danymi.
W tym poście nauczymy się, jak wstawić dwa okręgi na wykresie punktowym albo tak naprawdę dowolne kształty.
Zaczniemy od tego, że równanie okręgu o środku w punkcie (0,0) powinno wyglądać następująco: X2+Y2=R2, gdzie X,Y to współrzędne środka okręgu, a R to promień.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
Na potrzeby Excela potrzebujemy odpowiednią ilość punktów (ich współrzędnych X i Y). Najpierw zakładamy sobie wartość promienia naszego okręgu i generujemy ciąg wartości X, następnie za pomocą funkcji PIERWIASTEK musimy obliczyć współrzędną Y. Wystarczy przekształcić wyżej podany wzór. Zapis formuły powinien wyglądać następująco:
=PIERWIASTEK($B$1^2-A4^2)
Ponieważ nasz okrąg jest jak sama nazwa wskazuje okrągły to dla każdej wartości X ma dwie wartości Y. Wyznaczamy sobie wartości dla X od ‑4 do 4. Następnie musimy wyznaczyć wartości przeciwne, czyli do naszej formuły musimy dołożyć na początku znak minus (rys. nr 2). Wynika to z wyliczania pierwiastka.
Rys. nr 2 – dołożony znak minus przed formułą
Ponadto musi być zachowana kolejność, czyli najpierw nasze wartości X rosną (od ‑4 do 4), a następnie maleją (od 4 do ‑4). Na potrzeby Excela wystarczy przeskok wartości punktów o 0,1.
Po obliczeniu potrzebnych punktów, możemy do wykresu dodać parametr A i B, które spowodują przesunięcie naszego wykresu względem środka układu współrzędnych, czyli punktu (0,0). O wartość parametru AX przesunie się środek koła w poziomie, a o wartość parametru BY przesunie się w pionie. Współrzędne koła uwzględniające przesunięcie zostały przedstawione na rys. nr 3.
Rys. nr 3 — współrzędne koła uwzględniające przesunięcie
Zaznaczamy zakres współrzędnych X2 i Y2 do końca danych za pomocą skrótu klawiszowego Ctrl+Shift+Strzałka w dół, następnie rozwijamy grupę poleceń wykresów punktowych (punkt 2 na rys. nr 4) z karty Wstawianie (punkt 1) i wybieramy wykres punktowy z wygładzonymi liniami (punkt 3).
Rys. nr 4 – wstawianie wykresu punktowego
Excel wstawi automatyczny wykres, który później będziemy edytować na nasze potrzeby (rys. nr 5).
Rys. nr 5 – automatycznie wstawiony wykres punktowy z wygładzonymi liniami
Przede wszystkim musimy ręcznie zmienić wielkość w poziomie tak, aby jednostki w poziomie pokrywały się z wielkością jednostek pionowych (aby otrzymać koło a nie elipsę) – rys. nr 6.
Rys. nr 6 – wykres po zmianie wielkości
Analogicznie w danych obok mamy obliczone współrzędne drugiego okręgu i jego współrzędne po przesunięciu środka. Podany jest również promień, po czym widać, że będzie ono mniejsze (rys. nr 7).
Rys. nr 7 – współrzędne drugiego okręgu
Punkty te chcemy dołożyć do naszego wykresu, czyli drugi okrąg. Zaznaczamy kolejną partię danych, czyli współrzędnych okręgu z przesunięciem XX2 i YY2, następnie kopiujemy je za pomocą skrótu klawiszowego Ctrl+C, zaznaczamy nasz wykres i dopiero wtedy wklejamy dane za pomocą skrótu klawiszowego Ctrl+V. Otrzymamy coś dziwnego, bo niestety po takim wklejeniu dodatkowych punktów Excel głupieje i wstawia jakieś dziwne kształty (rys. nr 8).
Rys. nr 8 – wykres po dodaniu nowych współrzędnych
Wynika to z tego, że Excel pamięta wcześniejszą serię danych i dlatego każde nowe dodane punkty traktuje jako nową serię. Przez to wychodzą takie dziwne kształty, pomarańczowy kolor to wartości X a szare to Y. Wracamy do stanu sprzed wklejenia punktów za pomocą skrótu klawiszowego Ctrl+Z. Aby poradzić sobie z tym problemem, musimy przejść do karty Projekt wykresu i wybrać polecenie Zaznacz dane (rys. nr 9).
Rys. nr 9 – polecenie Zaznacz dane
Musimy sami ręcznie zaznaczyć odpowiednie dane. Otworzy nam się okno Wybieranie źródła danych, gdzie w polu Wpisy legendy (serie danych) klikamy przycisk Dodaj (rys. nr 10).
Rys. nr 10 – okno Wybierania źródła danych
Otworzy nam się okienko Edytowania serii, gdzie w polu Nazwa serii (punkt 1 na rys. nr 11) zaznaczamy komórkę z nazwą YY2 (L3), następnie w polu Wartość X serii (punkt 2) zaznaczamy dane z zakresu K4:K124 i w polu Wartość Y serii (punkt 3) zaznaczamy zakres L4:L124. Podane serie zatwierdzamy przyciskiem OK.
Rys. nr 11 – okno Edytowania serii
Excel wróci do okna Wybierania źródła danych, które zatwierdzamy przyciskiem OK. Otrzymamy Wykres przedstawiony na rys. nr 12.
Rys. nr 12 – wykres dwóch okręgów
Jeśli potrzebujemy coś zmienić na naszym wykresie, to zaznaczamy jeden okrąg i za pomocą skrótu klawiszowego Ctrl+1, uruchamiamy okno Formatowania serii danych, gdzie w Wypełnieniach (punkt 1 na rys. nr 13) możemy zmienić Szerokość linii (punkt 2).
Rys. nr 13 – zmiana szerokości linii wykresu
Otrzymamy pogrubioną linię zaznaczonego wykresu przedstawioną na rys. nr 14.
Rys. nr 14 – wykres po zmianie szerokości linii
Podstawą do wykonania takich wykresów jest przygotowanie listy punktów (współrzędnych). Jeśli nasz wykres jest bardziej okrągły to wybieramy wykres punktowy o wygładzonych liniach, a jeśli jest kanciasty (trójkąt itp.) to wybieramy wykres z prostymi liniami. Ponadto przy wykresach z liniami prostymi wystarczy mniej punktów, żeby się dobrze prezentował.
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.
Wykres schodkowy dla dat jest prosty do stworzenia (patrz porada 301). Jednak gdy na wykresie przykładowo chcemy przedstawić stan czujnika w poszczególnych godzinach, to sprawa jest trudniejsza. Przede wszystkim nie będziemy mogli skorzystać z wykresu liniowego, bo nie rozpoznaje on czasu, tak jak rozpoznaje dat. Dlatego będziemy musieli skorzystać z wykresu punktowego.
Standardowo musimy odpowiednio przygotować dane. Mamy dane źródłowe, które pokazują o której godzinie stan czujnika się zmienił i na jaki – są 3 stany –„OK”, „Czuwa” i „Błąd”.
Stanów zapisanych jako tekst nie jesteśmy w stanie przedstawić na wykresie, dlatego przypisujemy im wartości liczbowe w tabeli pomocniczej. Ważne, w późniejszym kroku, że jedna z tych wartości jest dodatnia, druga ujemna, a trzecia wynosi zero.
Za pomocą funkcji WYSZUKAJ.PIONOWO odnajdujemy odpowiednie wartości dla poszczególnych punktów.
=WYSZUKAJ.PIONOWO(B2;Tabela13;2;0)
Podobnie jak w poradzie 301 przygotowane dane kopiujemy w inne miejsce dwukrotnie pod sobą (ważne, że nasze dane są posortowane od najmniejszych czasów do największych).
Analogicznie jak wcześniej chcemy usunąć pierwszą komórkę z czasem (pomalowaną na żółto) oraz ostatnie komórki związane z wartościami (2 żółte komórki na powyższym obrazie). Oba usuwania z przesunięciem do góry. Analogicznie jak przy datach spowoduje to, że dla każdej godziny (poza pierwszą) będą dwa stany czujnika przed i po zmianie.
Po usunięciu komórek musimy dane posortować ponownie rosnąco po czasie i dopiero możemy wstawić wykres punktowy z liniami i znacznikami, żeby wyglądał jak wykres schodkowy. Zaznaczamy do niego kolumnę z czasem i wartościami (stanu nie zaznaczamy).
Ten wykres musimy mocniej sformatować, żeby lepiej się prezentował. Przede wszystkim zaczynamy monitorować stan czujnika dopiero po godzinie 6:00, a kończymy około 21:00, więc musimy przypisać odpowiednie wartości minimum i maksimum dla osi. Musimy tutaj pamiętać, że Excel przechowuje czas jako liczbę dziesiętną (część dnia, która minęła) i w opcjach osi nie możemy wpisać 6:00, bo Excel tego nie zrozumie, tylko odpowiadającą tej godzinie liczbę 0,25 (=6/24). Analogicznie 21:00 to 0,875.
Pozostaje w analogiczny sposób dopasować jednostki główne na 0,125 (3 godziny).
Musimy również dopasować jednostki na osi y, bo nasze maksimum to 1, minimum ‑1, a przeskok (jednostka główka to 1). Przy okazji możemy usunąć linie siatki (uważam, że utrudniają odczyt wykresu) i trochę zmniejszyć wysokość wykresu.
Wykres schodkowy dla czasu jest już gotowy, ale do pełni szczęścia brakuje nam, żeby na osi y zamiast wartości 1, 0 i ‑1 na wykresie były zapisane odpowiednie stany licznika. Możemy uzyskać taki efekt dzięki niestandardowemu formatowaniu liczbowemu.
Zaznaczamy kolumnę z wartościami i naciskamy Ctrl + 1, żeby otworzyć okno Formatowania komórek. Na zakładce liczby, przechodzimy do kategorii niestandardowej i wpisujemy taki kod formatowania liczbowego.
"OK";"Błąd";"Czuwa"
Zatwierdzamy formatowanie liczbowe przyciskiem OK i teraz zamiast liczb Excel wyświetla w komórkach tekst. Wystarczy jedna zwrócić uwagę na sposób wyrównania tego tekstu (do prawej), żeby wiedzieć, że Excel traktuj go jako liczb, bo faktycznie w tej komórce jest przechowywana liczba (wynik funkcji WYSZUKAJ.PIONOWO).
Trzeba powiedzieć kilka słów o niestandardowym formatowaniu liczbowym – przede wszystkim jest dzielone na sekcje za pomocąśredników. Pierwsza sekcja oznacza jak mają być formatowane liczby dodatnie. W naszym przykładzie wpisaliśmy tekst „OK”, czyli Excel zamiast każdej liczby dodatniej (w tym przykładzie plus jedynka) będzie wyświetlał słowo OK. druga sekcja odpowiada za liczby ujemne. Tutaj chcemy wyświetlać słowo „Błąd” (w tym przykładzie minus jedynka). Trzecia sekcja to sposób formatowania zera – w tym przykładzie słowo „Czuwa”. Jest jeszcze czwarta sekcja formatująca tekst, ale tutaj jest nam niepotrzebna.
W zależności od Twojej wersji Excela, wykres może zrozumieć zmianę formatowania danych (komórek) źródłowych lub nie. Jeśli zrozumiał, to na wykresie na osi y zamiast wartości liczbowych będą wyświetlały się słowa. Jeśli Excel nie zrozumiał tej zmiany musisz wstawić/dodać ją ręcznie, czyli zaznaczamy oś y, naciskamy Ctrl + 1 i odszukujmy sekcję formatowania liczb. Tam wpisujemy nasz niestandardowy kod formatowania liczbowego i klikamy przycisk Dodaj.
Na koniec można jeszcze wspomnieć, że stworzony przez nas wykres nadaje się to przedstawienia stanu jednego czujnika, jeśli dołożymy dane drugiego czujnika to wykres przestanie być czytelny. Dlatego jeśli potrzebujemy monitorować więcej czujników, to żeby wynik był czytelny każdy czujnik przedstawiamy na innym wykresie. A później ustawiamy je równo pod sobą.
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
