Użyj funkcji importowania Arkuszy Google, aby zwiększyć produktywność

W Stanach Zjednoczonych sport to wielki biznes . Około 154 miliony ludzi ogląda sport na żywo przynajmniej raz w miesiącu. Oczekuje się, że te liczby będą rosły każdego roku.

Wraz z boomem sportowym, branże chałupnicze, takie jak sporty fantasy, DFS i strony z propami, dały fanom kolejny powód do oglądania (i większego zaangażowania). Te gry pozwalają sprytnym widzom zarabiać duże pieniądze, przechytrzając innych graczy (i bukmacherów) w celu wygrania lukratywnych konkursów online.

Podstawowym sposobem, w jaki gracze fantasy zyskują przewagę, jest używanie zastrzeżonych algorytmów do projekcji wyników gracza. Masowanie statystyk graczy pozwala graczom znaleźć niejasne klejnoty statystyczne, które mogą wykorzystać, aby wygrać więcej konkursów.

Dzisiaj przyjrzymy się, jak możesz to zrobić za pomocą Arkuszy Google. Przygotujemy się do hipotetycznego projektu fantasy, importując statystyki graczy z ogólnodostępnej strony internetowej. Następnie użyjemy mocy funkcji do przekształcania, manipulowania, filtrowania i sortowania tych danych zgodnie z naszymi upodobaniami.

wpchtmlp id=15056

1. Zidentyfikuj swoje docelowe źródło danych online

Uczestnicy lig fantasy football award wiedzą, jak ważna jest ocena i ranking graczy przed draftem do ligi. Gracze fantasy zwykle zaczynają od statystyk z poprzedniego sezonu, kiedy rozwijają swoje rankingi graczy. Następnie dostosowują swoje rankingi graczy na podstawie różnych czynników:

● Potencjalne obrażenia

● Bitwy pozycyjne

● Projekcje Fantasy Point

● Zasady punktacji ligowej

● itd.

W tym przykładzie wyobraźmy sobie, że tworzymy rankingi graczy na pozycję rozgrywającego w lidze fantasy football. Niezbędne dane znalazłem na stronie internetowej Narodowej Ligi Piłki Nożnej.

Naszym celem jest zaimportowanie tych danych statystycznych do arkuszy Google, aby stworzyć nasze niestandardowe rankingi dla bieżącego sezonu.

2. Importuj dane tabelaryczne do Arkuszy Google

Funkcja, której użyjemy do wykonania importu danych to IMPORTHTML. Dokumentacja Arkuszy Google dla tej funkcji pokazuje, że musimy podać trzy dane wejściowe:

1. Adres URL strony do zbadania
2. Zapytanie, które chcemy wykonać (albo „lista” albo „tabela”, w zależności od narzutu strukturalnego, na który kierujemy).
3. Indeks (zaczynający się od 1) wskazujący, która tabela ze źródła HTML powinna zostać zaimportowana.

Zidentyfikowaliśmy już adres URL strony, na którą kierujemy reklamy. Zapytanie, które chcemy określić, to ciąg „table”, ponieważ informacje są przechowywane w tabeli HTML.

Uwaga: Jeśli chcesz zaimportować listę punktowaną (lub numerowaną), określ „lista” zamiast „tabela” jako parametr dodatkowy.

Na koniec musimy określić, gdzie znajduje się nasza tabela docelowa (sekwencyjnie) w kodzie źródłowym HTML samej strony. Aby to zrobić, kliknij prawym przyciskiem myszy w dowolnym miejscu okna przeglądarki i wybierz „Wyświetl źródło”. Następnie możesz wyszukać ciąg „<tabela”, aby określić poprawny indeks.

Ponieważ nasza tabela docelowa jest pierwszą (i jedyną) tabelą na tej stronie, nasz indeks będzie wynosił 1.

Oto nasza początkowa formuła:

=IMPORTHTML("https://www.nfl.com/stats/player-stats/", "tabela", 1)

Po wpisaniu formuły do ​​A1, oto jak wyglądają dane:

Świetnie, to było łatwe! Możemy jednak użyć większej liczby funkcji Arkuszy Google, aby usprawnić ten proces. Widzę kilka niedociągnięć w naszym obecnym procesie:

1. Nie potrzebujemy WSZYSTKICH kolumn z tabeli źródłowej. Chcę ograniczyć moje dane tylko do pól, których potrzebuję do stworzenia moich rankingów.
2. Może chcę określić pewne minimalne kryteria, które gracz musi spełnić, zanim je zaimportuję (mam standardy!).
3. Chciałbym jedną, dodatkową kolumnę, która podsumowuje, jak dobrze zawodnik spisywał się przez cały sezon.
4. Ponieważ oceniam tych graczy, najlepsi gracze powinni być na szczycie.

Zobaczmy, jak możemy osiągnąć te cele.

3. Ogranicz kolumny, które chcesz zaimportować

Nie ma sensu zaśmiecać naszego arkusza kalkulacyjnego niepotrzebnymi danymi. Na szczęście Arkusze Google umożliwiają importowanie tylko żądanych kolumn.

Patrząc na kolumny w naszej tabeli docelowej, interesuje mnie tylko kilka z nich.

1. Gracz (kolumna 1)

2. Przekaż jardy (kolumna 2)

3. TD (kolumna 7)

4. INT (kolumna 8)

W tych kolumnach znajdują się wszystkie dane, których potrzebuję, aby móc sklasyfikować tych graczy na nadchodzący sezon.

Aby ograniczyć importowane kolumny, musimy opakować naszą funkcję IMPORTHTML w funkcję zapytania. Funkcja zapytania przyjmuje dwa parametry:

1. Dane reprezentujące zakres komórek, na których należy wykonać zapytanie.
2. Zapytanie do wykonania napisane w języku zapytań interfejsu Google Visualization API.

Jako parametr data użyjemy całej funkcji IMPORTHTML, którą już zbudowaliśmy. W przypadku zapytania udostępnimy zapytanie skierowane do kolumn, które chcemy uwzględnić w naszym imporcie.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "tabela", 1), "wybierz Col1, Col2, Col7, Col8")

Teraz nasze importowane dane wyglądają tak, bez zbędnych kolumn!

Teraz możemy z powodzeniem zaimportować żądany zestaw danych, jednocześnie ograniczając kolumny, które pojawią się w naszych końcowych arkuszach. Ale wciąż jest wiele do zrobienia.

4. Filtruj importowane wiersze na podstawie kryteriów niestandardowych

W moich rankingach chcę brać pod uwagę tylko rozgrywających, którzy rzucali z co najmniej 3000 jardów. Bardziej ogólnie chcę filtrować importowane wiersze na podstawie kryteriów w określonej kolumnie.

Aby utworzyć filtr, dostosujemy naszą istniejącą funkcję zapytania tak, aby zawierała klauzulę WHERE. W szczególności wskażemy, że chcemy importować tylko wiersze, w których wartość w Col2 jest większa lub równa 3000.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "tabela", 1), "wybierz Col1, Col2, Col7, Col8 gdzie Col2 >= 3000")

Teraz, gdy przyjrzymy się zestawowi wyników, widzimy, że Lamar Jackson (jedyny gracz, który minął mniej niż 3000 jardów) jest pominięty. Powodzenia w przyszłym roku!

Uwaga: Lamar Jackson jest reproduktorem i zwykle uwzględnilibyśmy go w naszych rankingach. To jest teoretyczne, ludzie!

5. Generuj wynik Fantasy Point za pomocą pola obliczeniowego

Pomyślnie zaimportowaliśmy nasze surowe statystyki rozgrywającego, przycięliśmy kolumny według naszych upodobań i odfiltrowaliśmy niektórych niepożądanych graczy. To świetny początek tworzenia naszych niestandardowych rankingów.

Ale surowe statystyki nie są tak pomocne, jeśli chodzi o sporty fantasy. To, co naprawdę musimy zrobić, to obliczyć liczbę punktów fantazji dla każdego gracza. To znacznie lepszy wskaźnik sukcesu.

I oczywiście, moglibyśmy ręcznie dodać kolejną kolumnę, wykonując to obliczenie ręcznie w naszym arkuszu. Ale zamiast tego usprawnijmy rzeczy, wykonując obliczenia podczas procesu importowania.

Opracowanie wzoru na obliczanie punktów fantasy

Więc jaki jest wzór, którego używamy do obliczania punktów fantazji dla rozgrywających?

Okazuje się, że ta formuła będzie się różnić w zależności od ligi, w zależności od konkretnych konfiguracji punktacji. Ale w tym samouczku użyjemy formuły podobnej do standardowego systemu punktacji:

Punkty Fantasy = (Przełęcz Yds / 25) + (TD * 6) – (INT * 2)

Dostosowanie zapytania do pola obliczeniowego

Aby zintegrować pole obliczeniowe, dostosujemy nasze istniejące zapytanie tak, aby zawierało kolumnę obliczeniową. W szczególności chcemy dodać nasze obliczenia punktów fantazji do parametru zapytania funkcji zapytania.

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "tabela", 1), "wybierz Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) gdzie Col2 >= 3000”)

Teraz nasz arkusz kalkulacyjny zawiera całkowity wynik fantazji dla każdego gracza (o wiele bardziej pomocny wskaźnik).

Ale ten nowy nagłówek kolumny wygląda naprawdę dziwnie. Byłoby znacznie czyściej, gdybyśmy mogli dodać do kolumny własną etykietę (na przykład „Punkty fantazji”) l. Okazuje się, że jest to możliwe, chociaż składnia jest nieco dziwna.

To, co musimy zrobić, to dodać więcej danych na końcu naszego zapytania:

1. Słowo kluczowe „etykieta”

2. Powtórz obliczone pole

3. Żądany nagłówek kolumny jako ciąg

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "tabela", 1), "wybierz Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) gdzie Col2 >= 3000 etykiet (Col2/25)+(Col7*6)-(Col8*2) 'Punkty Fantazji'”)

Teraz to jest bardziej podobne!

Ta kolumna Fantasy Points wygląda znacznie lepiej! Ale myślę, że nie potrzebuję części dziesiętnej tych liczb.

Dla mnie cyfry dziesiętne dodają niepotrzebnego szumu tła. Zobaczmy więc, czy możemy zaokrąglić te liczby.

Aby to osiągnąć, zastosujemy do naszego zapytania niestandardowy format. W szczególności musimy określić:

1. Słowo kluczowe „FORMAT”

2. Powtórz obliczone pole

3. Niestandardowy format numeru („#” w naszym przypadku)

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "tabela", 1), "wybierz Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) gdzie Col2 >= 3000 etykiet (Col2/25)+(Col7*6)-(Col8*2) FORMAT 'Punktów Fantazji' (Col2/25)+(Col7*6)-(Col8 *2) „#””)

Teraz nasz stół wygląda czyściej.

Przeszliśmy długą drogę i usprawniliśmy nasz proces gromadzenia danych. Ale mamy jeszcze jeden krok do ukończenia tego samouczka.

Ponieważ to ćwiczenie ma na celu stworzenie rankingów dla tych graczy, przydatne byłoby posortowanie ich podczas importowania danych.

Popracujmy nad tym dalej.

6. Uporządkuj wiersze na podstawie obliczonej kolumny

Ponownie oprzemy się na naszym zapytaniu, aby określić początkową kolejność wierszy dla naszych danych. Chcemy posortować nasze wiersze według obliczonej kolumny („Punkty fantazji”). Ale chcemy to zrobić w porządku malejącym.

Sortowanie można przeprowadzić za pomocą klauzuli ORDER BY DESC. Ponownie (i niestety) będziemy musieli odwołać się do naszego pełnego pola obliczeniowego.

UWAGA: Nie można użyć nagłówka kolumny pola obliczeniowego w klauzuli ORDER BY, ponieważ w kolejności operacji SQL alias jest stosowany dopiero po ORDER BY.

Oto nasza ostateczna formuła:

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "tabela", 1), "wybierz Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) gdzie Col2 >= 3000 ORDER BY (Col2/25)+(Col7*6)-(Col8*2) Etykieta DESC (Col2/25)+(Col7*6)-(Col8*2 ) FORMAT 'Punkty Fantazji' (Col2/25)+(Col7*6)-(Col8*2) '#'”)

Nasz ostateczny zestaw danych jest teraz uporządkowany według najlepszych graczy (zgodnie z zasadami punktacji naszej ligi).

Funkcje importu danych w Arkuszach Google są proste, ale skuteczne

Niezależnie od tego, czy jesteś maniakiem statystyk, studentem czy badaczem, wszechstronność funkcji importowania danych w Arkuszach Google to ogromna oszczędność czasu. Korzystając z funkcji opisanych w tym artykule, możesz teraz importować, filtrować, sortować i dalej manipulować dowolnymi danymi, które możesz znaleźć w Internecie.

A z ponad 6 miliardami stron internetowych (i wciąż rośnie), wygląda na to, że będziesz bardzo zajęty.