Excelでフィルター機能を使ったら合計も自動的に変更する「SUBTOTAL関数」
競馬の結果データで予想仮説を検証するときに良く使う集計機能と関数では「フィルター機能＋SUBTOTAL関数」が一番使う頻度が高い。
例えばAccessやSQL-Serverなどのデータベースからある条件で抽出したデータを更にExcelシートに展開して分析していく場合や参考資料に纏めたいときなどは、まだ研究段階なので、いちいち全ての条件をクエリーやＳＱＬを発行して、データを取りに行くよりもExcelシートの方が簡単に集計できる上に条件を都度入れなくても、Excelのフィルター機能を併用して使えば、条件ごとの集計やカウントは簡単に行うことが可能だ。
Excelフィルター機能＋SUBTOTAL関数を使って条件別集計を簡素化
競馬予想の仮説を過去のデータを参考にして、仮説を立証する場面はデータ項目が都度可変になったり集計条件を増減したり、データを更に絞り込んだりを繰り返すことが多発する。その度にクエリーやＳＱＬを発行していては、かなりの数を作成しなければならず、かなり面倒くさい。
そこである程度当たりをつけて、データベース側でサンプルデータを抽出し複数条件をセレクトしながら集計を掛けるといった方法をとる手法として、EXCELのフィルター機能＋SUBTOTAL関数は秀逸なので、私もよく利用する機能と関数の組み合わせだ。ここでそこそこの当たりがついたら実際にクエリーやＳＱＬの発行を行い分析結果のまとめをプログラミングして、今後のデータを追跡しやすくする仕組みを作っていくということをよくやっている。
さてそれでは、実際の競馬データで説明してみよう～(￣▽￣)
競馬DBから「条件を指定」しデータ抽出する
過去の結果データから「条件を設定」してデータベースから必要なデータを抽出する。
今回の条件設定：Accessで選択クエリーを使う
- 競馬ＤＢから前走１～３人気で４着以降に負けた馬
- クラス：未勝利
- ダートのみ
※2019.2.2～2021.2.28の結果データを上記の条件で検索すると、私の競馬DBでは1118件のデータがヒットした。これをExcelにコピペして分析していく。
| 開催日 | 場名 | Ｒ | ダ芝 | 距離 | 外回 | クラス | 出走頭数 | 着順 | 単勝人気 | 単勝倍率 |
| 2019/02/02 | 京都 | 2 | ダ | 1800 | 未勝利 | 14 | 3 | 4 | 5.80 | |
| 2019/02/02 | 京都 | 2 | ダ | 1800 | 未勝利 | 14 | 1 | 3 | 5.10 | |
| 2019/02/02 | 京都 | 3 | ダ | 1400 | 未勝利 | 16 | 6 | 5 | 6.60 | |
| 2019/02/02 | 中京 | 1 | ダ | 1800 | 未勝利 | 13 | 1 | 2 | 4.10 | |
| 2019/02/03 | 京都 | 1 | ダ | 1800 | 未勝利 | 14 | 4 | 2 | 4.90 | |
| 2019/02/03 | 中京 | 2 | ダ | 1800 | 未勝利 | 15 | 8 | 1 | 2.80 | |
| 2019/02/03 | 中京 | 2 | ダ | 1800 | 未勝利 | 15 | 13 | 5 | 9.30 | |
| 2019/02/03 | 東京 | 1 | ダ | 1400 | 未勝利 | 16 | 2 | 4 | 9.20 | |
| 2019/02/03 | 東京 | 2 | ダ | 2100 | 未勝利 | 16 | 4 | 2 | 4.10 | |
| 2019/02/03 | 東京 | 2 | ダ | 2100 | 未勝利 | 16 | 14 | 12 | 91.40 | |
| 2019/02/03 | 東京 | 2 | ダ | 2100 | 未勝利 | 16 | 3 | 7 | 23.50 | |
| 2019/02/03 | 東京 | 2 | ダ | 2100 | 未勝利 | 16 | 1 | 4 | 7.00 | |
| 2019/02/03 | 東京 | 3 | ダ | 1600 | 未勝利 | 15 | 4 | 1 | 3.40 | |
| 2019/02/03 | 東京 | 3 | ダ | 1600 | 未勝利 | 15 | 2 | 3 | 4.10 | |
| 2019/02/09 | 小倉 | 2 | ダ | 1700 | 未勝利 | 11 | 1 | 1 | 1.60 |
※これが1118件ある
①先頭行を見出し固定してExcelデータを見やすくする
データベースからExcelにデータをコピペすると、一行目が見出しになるのでコレを固定すれば下にスクロールしても、見出しが消えないので便利な機能として利用する。
メニューバー から
「表示」 → 「ウインドウ枠の固定」 → 「先頭行の固定」
②見出しにフィルターを設定する
Ａ１を選択（今回は見出しならどこでも良い）
メニューバー から
「データ」 → 「フィルター」
※[CTRL+SHFT+L] をキーボード入力すると「フィルターON/OFF」できる。
※見出しの１行目に▼が表示される。
③着順の▼を押して、１ を選択すると着順＝１着だけのデータが表示される
この状態で、「単勝倍率」の列どこでもよいので選択し、CTRL+↓を押すと最終行にセルが移動するので１行開けて、=SUBTOTAL(9,K2:K1120) と入力。
更に、その入力したセルを左横のセル：Ｊ1121にコピぺもしくは、=SUBTOTAL(3,J2:J1120) と入力し、I1121に cnt： と見出しをつけてみる。
これで分析用Excelシートの完成となる。（簡単でしょ～）
またSUBTOTAL関数が思い出せない場合は、1着を選択した状態で、メニューバーから
ホーム → ΣオートＳＵＭ を押すと、通常ならSUM関数が表示されるが、フィルター選択状態だとSUBTOTAL関数が表示されるので、一度試してほしい。
これで毎度々、SUM関数やSUMIFS関数などを書かなくても、フィルターで選択した項目の件数と合計が表示されるというわけだ。
分析用としてデータベースからの項目が可変になる度に、埋込関数を触らないといけない様な関数シートを作成してしまうと、分析というメイン業務がExcelシート修正業務に変わってしまい本来の目的である分析業務が疎かになるという時間ロスはなるべく避けたい。
またこれを毎度々、SQL文を叩いてデータベースから検索したり、検索クエリーを大量に発行するとなると大量の検索クエリーの名前を分かりやすくつけるのは、結構頭を悩ます。
あくまでも、まだ分析段階なので、ある程度の当たりが付くまではＳＱＬや選択クエリーの保存をするよりもExcelシートでの保存の方が私は管理しやすく時間効率も良く感じている。
それでは、またね～～😎