Google スプレッドシートのインポート機能を使用して生産性を高める

アメリカでは、スポーツはビッグビジネスです。 約 1 億 5,400 万人が少なくとも月に 1 回はスポーツの生中継を視聴しています。 そして、その数は毎年増加すると予想されています。

スポーツ ブームに伴い、ファンタジー スポーツ、DFS、プロップ ベッティング サイトなどの家内産業は、ファンに視聴する別の理由を与えました (そしてより熱心に取り組みます)。 これらのゲームでは、賢明な視聴者が他のプレーヤー (およびスポーツブック) を出し抜いて大金を稼ぎ、収益性の高いオンライン コンテストに勝つことができます。

ファンタジー ゲーマーがアドバンテージを得る主な方法は、独自のアルゴリズムを使用してプレーヤーのパフォーマンスを投影することです。 プレーヤーの統計をマッサージすることで、ゲーマーは、より多くのコンテストに勝つために活用できるあいまいな統計の宝石を見つけることができます.

今日は、Google スプレッドシートを使用してこれを実現する方法を見ていきます。 公開 Web サイトからプレイヤーの統計情報をインポートして、架空のファンタジー ドラフトの準備をします。 次に、関数の力を使用して、このデータを好みに合わせて変換、操作、フィルター処理、および並べ替えます。

wpchtmlp id=15056

1. ターゲット データ ソースをオンラインで特定する

ファンタジー フットボール プライズ リーグの参加者は、リーグ ドラフトの前に選手を評価してランク付けすることがいかに重要かを知っています。 ファンタジー プレイヤーは通常、プレイヤー ランキングを作成する際に、前シーズンの統計から開始します。 次に、さまざまな要因に基づいてプレーヤーのランキングを調整します。

●怪我の可能性

●ポジショナルバトル

●ファンタジーポイントプロジェクション

●リーグの採点ルール

●など

この例では、ファンタジー フットボール リーグのクォーターバック ポジションの選手ランキングを作成しているとします。 ナショナル フットボール リーグのウェブサイトで公開されている必要なデータを見つけました。

私たちの目標は、この統計データを Google シートにインポートして、今シーズンのカスタム ランキングを作成することです。

2. 表形式のデータを Google スプレッドシートにインポートする

データのインポートを実行するために使用する関数は IMPORTHTML です。 この関数の Google スプレッドシートのドキュメントは、3 つの入力を提供する必要があることを示しています。

1. 調べるページのURL
2. 実行したいクエリ (対象とする構造マークアップに応じて、「リスト」または「テーブル」のいずれか)。
3. HTML ソースからインポートする必要があるテーブルを示すインデックス (1 から始まる)。

対象のページの URL は既に特定されています。 情報は HTML テーブルに格納されているため、指定したいクエリは文字列「table」です。

注: 箇条書き (または番号付き) のリストをインポートする場合は、2 番目のパラメーターとして「テーブル」ではなく「リスト」を指定します。

最後に、ターゲット テーブルがページ自体の HTML ソース コード内のどこに (順番に) 存在するかを判断する必要があります。 これを行うには、ブラウザ ウィンドウの任意の場所を右クリックし、[ソースの表示] を選択します。 次に、文字列「<table」を検索して、正しいインデックスを決定できます。

ターゲット テーブルはこのページの最初の (そして唯一の) テーブルであるため、インデックスは 1 になります。

これが最初の式です。

=IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “表”, 1)

数式を A1 に入力すると、データは次のように表示されます。

すごい、簡単でした！ しかし、Google スプレッドシートの機能をさらに使用して、このプロセスを改善することができます。 現在のプロセスにはいくつかの欠点があります。

1. ソース テーブルのすべての列は必要ありません。 ランキングを作成するために必要なフィールドのみにデータを制限したいと考えています。
2. プレーヤーをインポートする前に、プレーヤーが満たさなければならない最低限の基準を指定したい場合があります (私には基準があります!)。
3. プレーヤーがシーズン全体でどの程度の成績を収めたかをまとめた 1 つの追加の列が必要です。
4. これらのプレーヤーをランク付けしているので、最高のプレーヤーが一番上にあるはずです。

これらの目標を達成する方法を見てみましょう。

3.インポートする列を制限する

スプレッドシートに不要なデータを詰め込んでも意味がありません。 幸いなことに、Google スプレッドシートには、必要な列のみをインポートする方法が用意されています。

ターゲット テーブルの列を見ると、いくつかの列にしか関心がありません。

1. プレーヤー (列 1)

2. パスヤード (列 2)

3. TD (列 7)

4. INT (列 8)

これらの列は、次のシーズンに向けてこれらのプレーヤーをランク付けするために必要なすべてのデータです.

インポートされる列を制限するには、IMPORTHTML 関数をクエリ関数でラップする必要があります。 クエリ関数は次の 2 つのパラメーターを取ります。

1. クエリを実行するセルの範囲を表すデータ。
2. Google Visualization API クエリ言語で記述された、実行するクエリ。

data パラメーターには、作成済みの IMPORTHTML 関数全体を使用します。 クエリについては、インポートに含めたい列を対象とするクエリを提供します。

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “table”, 1), “select Col1, Col2, Col7, Col8”)

これで、インポートされたデータは次のようになり、不要な列がなくなりました!

これで、最終的なシートに表示される列を制限しながら、目的のデータ セットを正常にインポートできるようになりました。 しかし、やるべきことはまだあります。

4. カスタム条件に基づいてインポートされた行をフィルタリングする

私のランキングでは、少なくとも 3000 ヤードを投げたクォーターバックのみを考慮したいと思います。 より一般的には、特定の列の基準に基づいて、インポートされた行をフィルター処理したいと考えています。

フィルターを作成するには、既存のクエリ関数を調整して WHERE 句を含めます。 具体的には、Col2 の値が 3000 以上の行のみをインポートすることを示します。

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "Col1、Col2、Col7、Col8 を選択、Col2 >= 3000")

ここで、結果セットを調べると、Lamar Jackson (パス距離が 3000 ヤード未満の唯一のプレーヤー) が省略されていることがわかります。 来年もよろしくね！

注: ラマー ジャクソンは種牡馬であり、通常はランキングに彼を含めます。 これは理論上の話です。

5. 計算フィールドを使用してファンタジー ポイントの出力を生成する

生のクォーターバック統計を正常にインポートし、好みに合わせて列をトリミングし、望ましくないプレーヤーを除外しました。 これは、カスタム ランキングの作成に向けた素晴らしいスタートです。

しかし、ファンタジースポーツに関しては、生の統計はあまり役に立ちません。 本当に必要なのは、各プレイヤーのファンタジー ポイント出力を計算することです。 これは、成功のはるかに優れた指標です。

もちろん、別の列を手動で追加して、この計算をシート内で手動で実行することもできます。 ただし、代わりに、インポート プロセス中に計算を実行することにより、物事を合理化しましょう。

ファンタジーポイント計算式の開発

では、クォーターバックのファンタジー ポイント出力を計算するために使用する式は何ですか?

この式は、特定のスコア構成に基づいて、リーグごとに異なることがわかります。 ただし、このチュートリアルでは、標準のスコアリング システムに似た式を使用します。

ファンタジー ポイント = (パス ヤード / 25) + (TD * 6) – (INT * 2)

計算フィールドを含むようにクエリを調整する

計算フィールドを統合するには、既存のクエリを調整して計算列を含めます。 具体的には、クエリ関数のクエリ パラメータにファンタジー ポイントの計算を追加します。

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "select Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) ここで、Col2 >= 3000")

これで、スプレッドシートには、各プレイヤーの合計ファンタジー出力が含まれるようになりました (はるかに役立つ指標です)。

しかし、その新しい列ヘッダーは非常に奇妙に見えます。 カスタム ラベルを列に適用できれば (たとえば、「ファンタジー ポイント」)、はるかにすっきりします l. 構文は少し奇妙ですが、これは可能であることがわかりました。

必要なことは、クエリの最後にデータを追加することです。

1. キーワード「ラベル」

2. 計算フィールドを繰り返す

3. 文字列としての目的の列ヘッダー

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "select Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) ここで、Col2 >= 3000 ラベル (Col2/25)+(Col7*6)-(Col8*2) 「ファンタジー ポイント」)

今はもっと似ています！

そのファンタジー ポイントの列は、はるかによく見えます! しかし、これらの数値の小数部分は必要ないと思います。

私には、10 進数は不要なバックグラウンド ノイズを追加します。 それでは、これらの数値を切り上げることができるかどうか見てみましょう。

これを実現するために、カスタム形式をクエリに適用します。 具体的には、以下を指定する必要があります。

1. キーワード「FORMAT」

2. 計算フィールドを繰り返す

3. カスタム数値形式 (この場合は「#」)

=query(IMPORTHTML("https://www.nfl.com/stats/player-stats/", "table", 1), "select Col1, Col2, Col7, Col8, (Col2/25)+(Col7* 6)-(Col8*2) ここで、Col2 >= 3000 ラベル (Col2/25)+(Col7*6)-(Col8*2) 'ファンタジー ポイント' FORMAT (Col2/25)+(Col7*6)-(Col8 *2) '#'”)

これで、テーブルがきれいに見えます。

私たちは長い道のりを歩み、データ収集プロセスを合理化しました. ただし、このチュートリアルを完了するには、もう 1 つのステップがあります。

この演習では、これらのプレーヤーのランキングを作成することを目的としているため、データをインポートするときにそれらを並べ替えると便利です。

次はそれに取り組みましょう。

6. 計算列に基づいて行を並べ替える

再びクエリを使用して、データの最初の行の順序を指定します。 行を計算列 (「Fantasy Points」) で並べ替えます。 しかし、これを降順で行いたいと考えています。

並べ替えは、ORDER BY DESC 句を使用して実行できます。 繰り返しになりますが (残念ながら)、完全な計算フィールドを参照する必要があります。

注: 計算フィールドの列ヘッダーを ORDER BY 句で使用することはできません。SQL の操作順序では、エイリアスは ORDER BY の後まで適用されないためです。

最終的な式は次のとおりです。

=query(IMPORTHTML(“https://www.nfl.com/stats/player-stats/”, “table”, 1), “select Col1, Col2, Col7, Col8, (Col2/25)+(Col7*) 6)-(Col8*2) ここで、Col2 >= 3000 ORDER BY (Col2/25)+(Col7*6)-(Col8*2) DESC ラベル (Col2/25)+(Col7*6)-(Col8*2 ) 'ファンタジーポイント' FORMAT (Col2/25)+(Col7*6)-(Col8*2) '#'”)

最終的なデータ セットは、(リーグ固有のスコアリング ルールに従って) 最高の選手から順に並べられています。

Google スプレッドシートのデータ インポート機能はシンプルですが効果的です

統計オタク、学生、研究者のいずれであっても、Google スプレッドシートのデータ インポート機能の多用途性により、時間を大幅に節約できます。 この記事で詳しく説明した機能を使用すると、インターネット上で見つけたデータをインポート、フィルター処理、並べ替え、さらに操作することができます。

60 億を超える Web ページ (およびその数) があるため、非常に多忙になりそうです。