Wykres cykliczności w Excelu

Szybki sposób na wykres cykliczności w Excelu

Posted on Posted in wykresy

Wykres cykliczności w Excelu, z ang. "small multiples" jest jednym z moich ulubionych typów wykresów. Często wykorzystuję go przy porównaniach wartości w czasie, gdy zależy mi np. na odnalezieniu powstałego trendu. W tym wypadku sprawdza się on dużo lepiej, niż standardowy wykres kolumnowy. W dzisiejszym poście przedstawię dwa ciekawe triki, pozwalające przyspieszyć proces przygotowywania danych do tego wykresu. Wykorzystamy dodatek PowerQuery oraz opcję edytowania listy niestandardowej. Przedstawione techniki pozwolą nam stworzyć wykres cykliczności, którego etykiety osi będą idealnie wyśrodkowane względem siebie. Gotowy plik z wykresem można pobrać tutaj.

 

Czego chcemy uniknąć...

Sposobów na stworzenie wykresu cykliczności jest kilka. Cała sztuka polega jednak na właściwym ułożeniu danych do wykresu. Posługiwanie się pozostałymi metodami skutkuje tym, że pomiędzy serie wykresu wkradają się zbędne luki poprzez, które etykiety opisujące oś nie są należycie wyśrodkowane.

 

Etykiety na wykresie cykliczności

 

Aby tego uniknąć nasze dane muszą być ułożone w następujący sposób.

 

Rozmieszczenie etykiet do wykresu cykliczności

 

Trik nr 1 - Anulowanie przestawienia kolumn - PowerQuery

Powyższa prezentacja danych w tabeli nie zdarza się zbyt często. Zazwyczaj tabele dla tego rodzaju danych ułożone są horyzontalnie. Naszym zadaniem będzie przekonwertowanie tabeli do podanego wzoru. Na początek zaznaczamy tabelę z danymi, przechodzimy na zakładkę PowerQuery i klikamy na ikonkę Z tabeli.

 

Export danych do PowerQuery

 

Kiedy nasze dane są już w PowerQuery, zaznaczamy kolumny z wartościami dla poszczególnych kwartałów i przechodzimy na kartę Przekształć, z której wybieramy ikonkę Anuluj przestawienie kolumn. Ten prosty zabieg sprawił, że dokonała się tzw. translacja kolumn na pary atrybut-wartość, połączone z resztą wartości w każdym wierszu.

 

Anulowanie przestawienia kolumn

 

Otrzymane rezultaty zapisujemy jako tabela w skoroszycie. W kolejnym kroku dodajemy do tabeli dodatkową kolumnę, która pomoże nam później przy budowie tabeli przestawnej. W kolumnie pierwszą wartość wpisujemy ręcznie, a następnie za pomocą funkcji JEŻELI tworzymy prosty warunek numerujący poszczególne wiersze.

 

Kolumna pomocnicza w Excelu

 

Z tak powstałej kolumny tworzymy tabelę przestawną, dla której usuwamy wszelkie podsumowania i wyświetalmy ją w formie tabelarycznej.

 

Trik nr 2 - Edytowanie listy niestandardowej

Układ naszej tabeli jest dokładnie taki jaki chcieliśmy, za wyjątkiem jednej rzeczy. Zauważcie, że poszczególne regiony zostały posortowane alfabetycznie, zatem nie tak, jak było to w pierwotnej tabeli.

 

Tabela przestawna do wykresu cykliczności

 

Aby to zmienić należy skorzystać z opcji sortowania danych według zdefiniowanej listy niestandardowej. W tym celu w dowolnym miejscu tworzymy listę naszych regionów, w posortowanej już kolejności, a następnie w opcjach Excela, klikamy na zakładkę Zaawansowane i z działu Ogólne wybieramy Edytowanie listy niestandardowej.

 

Ustawienie listy niestandardowej

 

W okienku, które się pojawi zaznaczamy komórki, które staną się naszą listą, a następnie klikamy przycisk importuj. Teraz przechodzimy do tabeli przestawnej i sortujemy kolumnę Region, wybierając opcję Ręcznie --> Więcej opcji -->Odhaczamy przycisk sortowania automatycznego i rozwijanej listy wybieramy naszą nową listę, zatwierdzając OK.

 

Wybór listy niestandardowej

 

Kiedy przejdziemy ponownie do tabeli przestawnej i wybierzemy opcję sortuj A-Z, nasze regiony posortują się tak jak chcieliśmy. W tym momencie nie pozostaje nam już nic innego jak zaznaczyć tabelę przestawną, i na jej podstawie stworzyć wykres liniowy ze znacznikami, a po kilku obróbkach kosmetycznych będzie on wyglądał jak poniżej.

 

Wykres cykliczności w Excelu

 

Powiązane posty

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *