GoogleSpreadSheetでDBのテーブルを模擬的に作成して簡単なクエリで集計する。

こんにちは、kisseです。
スポーツの大会の運営に関わる機会があったんですが、その集計がかなり手作業に寄る部分が多かったので、その集計を自動化してしまおうと思って作ったので共有です。

想定する読者

なんとなくデータベースわかる。SQLも調べればわかる。
(「第1正規形」って単語を知ってる or ググれる)

集計したい内容

  • ゴール数で勝敗が決定するようなスポーツ
  • 選手ごとにゴールとアシスト数を集計する

以上の2点を満たすシートを作成します。



シートのユーザー

Excel, GoogleSpreadSheetで入力などはできるが、関数使ったりとかはできない人が使うことを想定して作ります。

ほんとはデータベースを使ってキチンと作ったら良いものができあがりそうなんですよね。
でも僕以外にそれを管理できる人がいなくなってしまうので、次回以降も僕が運営させられる可能性があります。それは避けたい。
なら、スプレッドシートで作ってしまって、作り方とかを残してしまえば良いんじゃないかと思ったわけです。

ついでに、基本的にシートのユーザーは入力するだけで良くて、なんなら入力内容とかも制限できると良いですね。

作っていきましょう

とりあえず、データを入力するための表を作っていきます。
列は(日にち, 試合, 得点チーム, 得点時間, アシストプレーヤー, ゴールプレイヤー)で作ります。

これで、行単位でゴールに関する情報を入力することができますね。
このシートの名前は”データ”としておきましょう。

では別シートにこれを集計するようなものを書いて行きましょう。
画面下から新たなシート作成を行い、左上のセルに入力を行って行きます。

ここでQUERY関数の説明を行います。
QUERY関数では、シート内のデータをデータベースのテーブルのように見て、簡単なクエリを使ってデータの抽出を行うことができる関数です。
この関数の構文はQUERY(データ, クエリ, [見出し])となっています。
見出しの部分はデフォルト値でとりあえず大丈夫なので省略します。

早速やっていきましょう。
集計したい範囲はOFFSET(‘データ’!A:E, 1, 0)です。
A:Eのようにデータを指定すると、列名まで集計対象に入ってしまいます。
それを避けるために、OFFSET関数で1行目は無視してデータを取得します。

QUERY関数ではデータを列名で指定します。今回はプレイヤー別にゴール数を集計します。
チーム名はC列、ゴールプレイヤー名はE列にあります。
このようなクエリは“SELECT C, E, COUNT(E) GROUP BY C, E ORDER BY COUNT(E) DESC”となりますね。

QUERY関数に上記の値を突っ込むと、=QUERY( OFFSET( ‘データ’!A:E, 1, 0 ), “SELECT C, E, COUNT(E) GROUP BY C, E ORDER BY COUNT(E) DESC” )となります。

“データ”シートの方に適当なデータを突っ込んで、確かめてみてください。
これで、データが増えても自動で集計ができるようになります。

ついでに

“データ”シートの方に意図しない入力がされるのを避けましょう。

意図しない入力がされた時には、警告を発することができます。
“データの入力規則”機能を利用してみましょう。
セルを右クリックして下の方に出現します。

ここでは、セル範囲を指定して入力できる値を制限することができます。
例えば、得点チームの列は、(A, B, C)の3チームしか記入できないようにするには、範囲を‘データ’!C2:C1000にして、条件を”リストで直接指定”で”A,B,C”と入力してあげればおっけーです。
これで、意図しない入力を制限していくことができます。



おわり

かなりざっくりですが、スプレッドシートで模擬的にDBのテーブルを再現してクエリを投げる方法を説明しました。

最後まで読んでいただきありがとうございます!

あわせて読みたい