Jak za pomocą Power Query zrobić terminarz ligi gry FIFA 19

Aby wykonać automatycznie aktualizującą się tabelę ligi FIFA można wykorzystać w tym celu Power Query. Power Query pozwoli nam automatycznie przeliczać wyniki spotkań, oraz ustawiać porządek (sortownie) ligowe za pomocą jednego przycisku. Power Query – gdy, już go ustawimy jak chcemy – pozwoli wykonać operacje przeliczenia wyników w mig. Co więcej, pisanie aplikacji VBA zajęło by jakieś 18 roboczo-godzin, podczas gdy wykorzystanie Power Query pomoże nam zredukować ten czas o 80%.

W takim bądź razie jak zrobić samo aktualizującą się tablę na potrzeby rozgrywek FIFA?

Ten post nie przedstawia instrukcji jak zrobić w Power Query tablę rozgrywek FIFA. Post ukazuje, że w prosty sposób można wykorzystać darmowe narzędzia w Excelu, aby zarządzać amatorską ligą. Nie pokażę instrukcji krok po kroku.

Ustawienie terminarza

Aby ustawić terminarz należy skorzystać z zewnętrznych darmowych narzędzi online. Ja akurat skorzystałem z narzędzia http://www.pesleague.pl/menu,60/, ala takich narzędzi jest mnóstwo. Trochę zmodyfikowałem na potrzeby Excela wynik generatora, a do arkusza Terminarz wkleiłem mniej więcej takie dane:

Przykładowy terminarz ligi FIFA

Jest podział na Kolejki, Terminy, Nazwę Gospodarza i Gościa oraz Status i Wynik. W kolumnie F tj. Status skorzystałem z narzędzia Excel Data Validation, tak aby tylko status „Rozegrany” był możliwy do wyboru.

Wykorzystanie tabeli jako źródła danych w Power Query

Wklejone dane w arkuszu Terminarz musimy zamienić na tabelę (klik w jakąś komórkę w zasięgu tabeli i naciskamy Ctrl+T). Następnie wchodzimy do zakładki Power Query i wybieramy narzędzie „Z tabeli/zakresu”). Od Excel 2016 Power Query jest wbudowanym narzędziem importu danych. W wersji poniżej 2016 trzeba zainstalować dodatek Power Query.

Power Query import danych z tabeli
Power Query: Import danych z tabeli

W wyniku tej operacji wchodzimy do edytora Power Query, gdzie dokonamy pierwszych operacji.

Power Query okno edycji
Power Query okno edycji

W pierwszym czerwonym obszarze nadałem nazwę naszego zapytania w Power Query. W drugim prostokącie pokazane są wszystkie wykonane i zarejestrowane operacje na zaimportowanych danych.

Większość operacji wykonywałem bez zapisu danych do arkusza, o czym świadczy informacja przy nazwie zapytania „Tylko połączenie” widoczne w Excelu:

Power Query tylko połączenie
Power Query tylko połączenie

Jak skorzystać z opcji Tylko Połączenie?

Gdy załadujemy dane, dokonamy wymaganych zmian możemy zapisać nasze zmiany poprzez wybranie Zamknij i załaduj -> Zamknij i załaduj do…, by następnie wybrać opcję „Utwórz tylko połączenie”, aby nie ładować danych do arkusza.

Power Query zamknij i załaduj do
Power Query: Zamknij i załaduj do
Power Query utwórz tylko połączenie
Power Query: Utwórz tylko połączenie

Zapytanie WszyscyGracze

Musimy stworzyć bazę pod główną tablę rozgrywek FIFA. W tym celu musimy wyodrębnić wszystkich graczy danej ligi. Z arkusza Terminarz (z przygotowanej wcześniej tabeli) pobieramy dane do Power Query. Nadajemy naszemu zapytaniu nazwę WszyscyGracze i modyfikujemy zapytanie o następujące kroki.

Power Query kroki zapytania WszyscyGracze
Power Query: Kroki zapytania WszyscyGracze

Source: określa skąd pobrane są dane.
Changed Type: zamiana formatu kolumn z bramkami z tekstu na liczby całkowite (opcjonalnie). Aby tego dokonać należy zaznaczyć kolumnę i np. w karcie Narzędzia główne zmienić typ danych.
Grouped Rows: to grupowanie po określonych rekordach z wybranych przez nas kolumny. Ja wybrałem kolumnę Gospodarz, aby zdobyć unikalne nazwy Graczy, oraz pozostawiłem kolumnę Count jako agregację.
Renamed Columns: Zmiana nazwy kolumny z Gospodarz na Gracz.
Removed Colulmns: Usunięcie niepotrzebnej kolumny Count.
Added Customs: Dodanie Niestandardowej Kolumny z własną funkcją IF (karta Dodaj kolumnę): = if [Gracz] <> „” then 0 else 0.

Power Query kolumna niestandardowa z własną funkcją if
Power Query kolumna niestandardowa z własną funkcją if

Duplicated Columns: kolumna Mecze została zduplikowana z funkcją if sześciokrotnie.
Renamed Columns1: zmieniono nazwy zduplikowanych kolumn.
Changed Type1: Data type dla kolumn przechowujących liczby został zmieniony na liczby całkowite.

Tak wygląda zmodyfikowane zapytanie WszyscyGracze. Oczywiście tworzymy Tylko Połączenie.

Power Query zapytanie WszyscyGracze
Power Query zapytanie WszyscyGracze

Zapytanie TylkoGospodarz

Ponownie do PowerQuery pobieramy dane z tabeli w arkuszu Terminarz. Tym razem obliczymy wartości dla wyników każdego z gospodarzy spotkań. Nazwy kolumn muszą być takie same jak jak w poprzednim zapytaniu (najlepiej w tej samej kolejności).

Power Query zapytanie TabelaGospodarz
Power Query zapytanie TabelaGospodarz

Musimy zbudować identyczną tabelę jak WszyscyGracze poprzez dodanie odpowiednich kroków. Opis niektórych kroków:

Dodaj Kol Punkty:

Funkcja sumująca punkty Gospodarza

Dodaj Kol Wygrane:

Funkcja sumujące wygrane gospodarza

Opisy zastosowywanych kroków podobne jak w innych punktach.

Zapytanie TylkoGoscie

Identyczne zapytanie musimy zbudować dla drużyn „przyjezdnych”. Ważne, aby nazwy kolumn były takie same jak w poprzednich dwóch zapytaniach.

Zapytanie TabelaKoncowa

To zapytanie końcowe załadujemy do arkusza, aby zaprezentować aktualną tabelę. W pierwszej kolejności złączymy zapytania WszyscyGracze, TylkoGospodarze, TylkoGoscie w jedno zapytanie.

Tak jak już wspominałem parę razy, ważne jest, aby nazwy kolumn w poszczególnych zapytaniach były identyczne, aby dane ze wszystkich zapytań zostały dodane do jednej kolumny w finalnym zapytaniu.

Power Query: Łączenie zapytań

Po wejściu w edytor zapytania musimy nasze złączone dane pogrupować (zakładka Narzędzia główne -> Grupowanie według):

Na samym końcu dodawana jest kolumna bilans. Następnie finalna tabela jest sortowana, by na końcu dodać kolumnę z indeksem, czyli w naszym przypadku pozycją w tabeli.

Ostatnim krokiem jest zapisanie i załadowanie danych do arkusza.

Aktualizacja tabeli

Za każdym razem, gdy do terminarza zostaną dodane wyniki meczów ze statusem rozegrane, osoba zarządzająca rozgrywkami musi zainicjować odświeżenie tabeli.
Poprzez zakładkę Dane, wybieramy Odśwież Wszystko. To jest tylko to co musi zrobić admin rozgrywek.

Power Query: Aktualizacja tabeli rozgrywek

Podsumowanie: Powyższy kod VBA Excel umieszczony w poście pod tytułem "Jak za pomocą Power Query zrobić terminarz ligi gry FIFA 19" zamieszczony na witrynie "exceldb.net" mógł zostać w całości pobrany z innej strony. Moim zamiarem nie jest kopiowanie czyiś rozwiązań tj. kodu VBA (dlatego zawsze staram się podać źródło kodu Excel VBA), a jedynie stworzenia miejsca skąd będę mógł pobrać działający już kod Excel VBA. Ten post pod tytułem "Jak za pomocą Power Query zrobić terminarz ligi gry FIFA 19" zawiera kod VBA Excel, który kiedyś wykorzystałem - niniejszym archiwizuje go z myślą o przyszłych aplikacjach Excel VBA wykorzystujących "Jak za pomocą Power Query zrobić terminarz ligi gry FIFA 19". Jeżeli osoby trzecie będą mogły skorzystać z tego kodu VBA "Jak za pomocą Power Query zrobić terminarz ligi gry FIFA 19" będzie mi niezmiernie miło. Niech ta witryna "exceldb.net" będzie swego rodzaju wyminą informacji pomiędzy ludźmi programującymi w Excel VBA.
Link: http://exceldb.net/blog/2019/02/26/jak-za-pomoca-power-query-zrobic-terminarz-ligi-gry-fifa-19/