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.
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.
Następnie jako sposób prezentacji danych wybieramy Tabelę zaczynającą się w komórce A1.
Dla tak przygotowanej tabeli odłączamy źródło danych z Power Query a następnie konwertujemy tabelę na zwykły zakres danych.
Uzyskaną tabelę formatujemy i czyścimy ze zbędnych danych, aby uzyskać poniższy efekt.
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ę.
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.
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".
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.
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!.
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.
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
-usunąć zbędne elementy wykresu
Gotowy wykres powinien wyglądać jak poniżej.
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.
