Wykres kod kreskowy w excelu

Wykres kod kreskowy w Excelu

Posted on Posted in wykresy

W dzisiejszym wpisie, chciałbym przedstawić kolejny ciekawy wykres przypominający swoją budową kod kreskowy. Wykres dobrze sprawdza się jako narzędzie do ogólnej prezentacji danego zjawiska w czasie. Swoją budową przypomina serię ułożonych obok siebie kolumn, różniących się kolorem w zależności od kategorii lub wartości prezentowanych danych. W naszym przypadku wykorzystamy wykres, do przedstawienia historii spotkań dwóch rywalizujących ze sobą drużyn angielskiej Premier League. Użyty w tym przypadku wykres à la kod kreskowy, pozwoli na szybkie zorientowanie się w rezultatach odbytych meczy. Gotowy wykres do pobrania tutaj.

 

Przygotowanie danych

Za źródło danych do wykonania wykresu posłuży nam strona prowadząca statystki piłkarskie worldfootball.net. Możliwości skopiowania tabeli z wynikami do Excela jest kilka. Ja wybiorę opcję z wykorzystaniem dodatku Power Query. W tym celu kopiuję link z wynikami meczy i z zakładki Power Query --> grupy Pobieranie danych zewnętrznych wybieram opcję Z sieci Web gdzie wklejam swój link.

 

Pobieranie danych z sieci web

 

Po chwili ładowania danych pokaże się nam nowe okienko, z którego wybieramy tabelę nr 1 a następnie klikamy na przycisk Załaduj --> Załaduj do.

 

Wybór tabeli i miejsca załadowania danych

 

Następnie jako sposób prezentacji danych wybieramy Tabelę zaczynającą się w komórce A1.

 

Wybór miejsca załadowania danych power query

 

Dla tak przygotowanej tabeli odłączamy źródło danych z Power Query a następnie konwertujemy tabelę na zwykły zakres danych.

 

Konwertowanie tabeli na zakres

 

Uzyskaną tabelę formatujemy i czyścimy ze zbędnych danych, aby uzyskać poniższy efekt.

 

Tabela z danymi

 

Aby stworzyć wykres obrazujący historię spotkań pomiędzy odwiecznymi rywalami Manchesterem United i Manchesterem City, będziemy potrzebowali kilku kolumn pomocniczych. Przygotowanie pierwszych czterech pozwoli nam na stworzenie osi czasu z interwałem 2 letnim. A zatem w kolumnie "pomoc 1", w komórce E2 wpisujemy formułę =LEWY(A2;4), która wyszczególni nam początkowy rok każdego sezonu. Dane z kolumny "pomoc 1" zaznaczamy i wklejamy w to samo miejsce jako wartości. Następnie usuwamy pojawiający się komunikat o błędzie, wybierając opcję Konwertuj na liczbę.

 

Konwertowanie wartości na liczbę

 

W kolejnym kroku sortujemy nasze dane w kolejności rosnącej, a następnie ustawiamy filtr na danych, aby usunąć wszystkie wartości poniżej roku 1990.

 

Ustawienie filta danych

 

Po tej operacji powinny nam pozostać 43 wiersze danych. W kolejnej kolumnie, o nazwie "pomoc 2" posłużymy się formułą =JEŻELI(A2=F1;"";A2), która usunie duplikujące się lata z kolumny "pomoc 1". Następnie w "pomoc 3", wpisujemy =LEWY(F2;4) aby ponownie uzyskać listę początkowych lat danego sezonu. Na koniec w kolumnie Oś czasu, komórce H2, wpisujemy pierwszy rok analizy tzn. 1990 a w komórce poniżej, wklejamy funkcję =JEŻELI.BŁĄD(JEŻELI(MOD($H$2-G3;2)=0;G3;"");""). Funkcja ta sprawdzi czy reszta z dzielenia przez 2, pomiędzy różnica z daty 1990 a datą z tabeli "pomoc 3" wynosi 0. Jeżeli zależność ta zostanie spełniona, funkcja wpisze do kolumny wartość z tabeli "pomoc 3".

 

Dane do tabeli

 

Kolejne kolumny, które stworzymy posłużą nam już do zbudowania samego wykresu. Pierwsze z nich o nagłówkach Gospodarze i Goście zostaną zapełnione danymi uzyskanymi przy pomocy funkcji =LEWY(D2;1) oraz =FRAGMENT.TEKSTU(D2;3;1). Funkcje te wybiorą z kolumny Wynik, rezultat bramkowy uzyskany przez każdy z zespołów. Wskazane funkcje przeciągamy w dół tabeli, a następnie kopiujemy je i wklejamy w to samo miejsce jako wartości. W kolejnej kolumnie Zwycięzca wpisujemy =JEŻELI(I2>J2;B2;JEŻELI(I2=J2;"remis";C2)) aby na podstawie wyniku meczu ustalić jego zwycięzcę lub wynik remisowy.

 

Dane do tabeli

 

 

Na koniec pozostały nam jeszcze 4 kolumny, za pomocą których powstaną poszczególne kolumny wykresu kod kreskowy. Za pomocą funkcji JEŻELI utworzymy warunki, które sprawdzą czy w kolumnie Zwycięzca, dany wiersz równy jest któremuś z nagłówków kolumny o nazwie (Manchester United, Manchester City lub remis). Jeżeli równość zostanie spełniona do kolumny podana zostanie wartość 1, w przeciwnym razie w kolumnie znajdzie się wartość #N/D!.

 

Warunek w funkcji jeżeli

 

 

Zapisz

Zapisz

Budowa wykresu

O ile proces przygotowania danych mógł wydać się pracochłonny, o tyle zrobienie samego wykresu zajmuje naprawdę chwilkę. Zaznaczamy dane z kolumn Manchester United, Manchester City oraz remis i na ich podstawie sporządzamy wykres kolumnowy.

 

Wykres kolumnowy w excelu

 

W celu uzyskania efektu wykresu kodu kreskowego musimy jeszcze wykonać kilka modyfikacji:

- ustawić kolor słupków na barwy danego klubu w modelu RGB  Manchester United (210,25,32), Manchester City (122,178,225) oraz remis na jasny odcień szarego

- ustawić etykiety osi poziomej na wartości z kolumny Oś czasu

- przeskalować szerokość przerwy pomiędzy słupkami oraz nakładanie się serii danych

 

Szerokość przerwy

 

-usunąć zbędne elementy wykresu

Gotowy wykres powinien wyglądać jak poniżej.

 

Wykres kod kreskowy w excelu

 

Do uzyskanego wykresu dodałem jeszcze różnicę w bramkach osiągniętą w każdym ze spotkań drużyn. W tym celu wykorzystałem wykres lizakowy, o którym więcej możecie przeczytać tutaj.

 

Wykres kod kreskowy w excelu

Zapisz

Zapisz

Dodaj komentarz

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