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:

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.

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

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:

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.

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.

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.

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.

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).

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

Dodaj Kol Punkty:

Dodaj Kol Wygrane:

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.

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.