ポケモンで学ぶ Excel データ分析 ― Pivot Table & Power Query
ポケモンファンのみなさんも Excel ファンのみなさんもこんにちは。
この記事は以下の２タイプの人向けに書いています。
Excel 使いこなしたい勢：簡単な集計やグラフ作成くらいはしたことがあるけど、もう少しデータ分析などしてみたい人
ポケモン対戦ガチ勢：対戦記録を Excel に付けている、あるいはこれから付けようと思っている人で、その分析方法を知りたい人
この記事の要点を図にするとこんな感じになります。
ポケモンなんてやらないよ、対戦記録なんてつけてないよという方でも読みやすいようにサンプルデータを置いておきますが、自分のデータがある人はぜひそれを使ってください。もう一つお楽しみデータを最後に置いておりますので最後まで読んでいただけたらうれしいです。
注：Excel のバージョンや設定によって画面が異なる場合があります。
この記事中に出てくるのは Excel 2019 です。
注：私が対戦しているのはポケモンGOですのでパーティは3体になります。
少し効率的なデータ入力
分析はデータを集めるところから始まります。測定データやお店の売上データのように使っている機器等からデータを入手できる場合もありますが、今回はデータを手入力で集めることになります。
こんな感じですね。
1行に1戦を記録する形にしています。同じパーティを使って複数戦するからと言ってセルの結合などしてはいけません。分析しやすいデータを作りたいならセルの結合はご法度です。
さて。既に見た目がちょっといい感じになっていますが、これは「テーブル」という機能を使っています。テーブルにしたい範囲を選択して、メニューから「テーブル」にしてください（下図）。
データを手入力する場合に考えるべき点は以下の２つです。
・入力の手間をできるだけ省く
・入力ミスをできるだけ減らす
これらを実現するために使える Excel の機能が「データの入力規則」です。たとえば、勝敗は〇か×かーの3種類のうちいずれかを入力していますので、それしか入力できないという規則を作ってあげましょう。
まずは、入力規則を作りたい範囲を選択します。この場合は一列全体を選択したいので表の上にあるアルファベットの部分をクリックしました。下図で言うとＩの部分になります。
範囲が選択出来たら次は「データの入力規則」ダイアログを表示します。
下図のようにダイアログが表示されたら、入力値の種類は「リスト」を選択して、元の値には「〇,×,－」とカンマ区切りで値を入力します。
これで勝敗に「〇,×,－」以外の値を入力しようとするとエラーが表示されるようになりましたので入力ミスのチェックができるようになりました。また、入力時にはプルダウンメニューが表示されて簡単に入力できるようにもなりました。
データは用意できましたでしょうか。まだだという人はまずプレイしてきてから戻ってきてくださいね。記録をつけながらプレイすると少し冷静にプレイできるような気がしています。ご武運を！
ピボットテーブルによる集計
それでは、対戦記録データを分析していきましょう。Excel をやったことがある人は SUM やら COUNT やら IF やらいろいろな関数を使うのをイメージするかもしれませんが、ここではそんなものは使いません。ピボットテーブルを使えば、ポチポチしているだけで簡単な集計はできてしまいます。
まず、分析するテーブルを選択します。丁寧に全体を選択しなくても、どこかのセルをクリックしておけばOKです。選択できたらメニューから「ピボットテーブルで集計」を実行します（下図）。表示されるダイアログの設定はそのままでOKを押してください。新しいシートが作成されます。
ピボットテーブルについては、ぐだぐだ説明するよりも実際に触ってみるのが一番です。「ピボットテーブルのフィールド」設定画面（下図右側）のように、元データのフィールド（＝列）が表示されますので、集計表の行・列・値に来るべきフィールドを選んで配置してください。
上図は、自分の初手ポケモン毎の勝敗結果を集計してみたところです。列と値に「勝敗」を、行に「初手」を配置しています。他にも様々な集計がポチポチするだけでできますので実際に手を動かして試してみてください。たとえば、複数のフィールドを「行」に配置することなんかもできます。
ピボットテーブル最高ですよね？ Excel を教えるのにピボットテーブルを教えない人がいるとしたら、ラグラージを使うのにハイドロカノンを覚えさせないトレーナーみたいなものです。
集計前に行うデータの抽出＆加工 (Power Query)
ポケモン対戦勢として外せない分析が「ポケモンの選出率」です。対戦でよく使われるポケモンを知ることで少し有利になれるでしょう。ポケモンに詳しくない人は「今日はじゃんけんでチョキを出す人が多いみたいだからグーを出したら勝てそうだな」みたいなもんだと思っておいてください。
ピボットテーブルを知ったみなさんもおそらく選出率を出してみようと思ったのではないかと思いますし、実際にやってみたら初手採用率は簡単に出せたと思います。ところが、初手と控えを合計した採用率は意外とうまく集計できなかったのではないでしょうか？
ピボットテーブル機能だけで初手と控えを合計した採用率がうまく出せなかったのがなぜかというと1行＝1データになっていないからです。ピボットテーブルは1行＝1データというシンプルなデータの形になっているからこそシンプルな操作で集計ができる機能なのです。
ピボットテーブルでポケモンの選出率を出したければ下図のようなデータを用意する必要があるというわけです。
対戦記録を付けつつ、選出率記録も別に手でつけるのはやってられませんので、対戦記録データを選出率記録データに変換したいところです。ご安心ください。Excel は、データを集計にかける前にデータを整えるのに使える機能「Power Query」を搭載しています。
実際にやってみましょう。変換したいテーブルを選択して「データの取得と変換＞テーブルまたは範囲から」を実行します（下図）。
すると Power Query エディターという新しいウィンドウが開きます。ここでどういう変換をするか入力していきます。別ウィンドウになっていますが、本家 Excel と操作性は似ています。たとえば、変換後の分析で使わない列などは削除してしまってもOKです。変換結果は新しいテーブルとして Excel に読み込まれて元のデータは変更されませんのでご安心ください。
さて、今やりたいのは「1行に3つのデータがある状態を3行のデータに変換する」ことですね。まずその3つの行を選択します。Shift キーを押しながら見出しのところを順番にクリックしていくと複数列を選択できます。下図のように選択出来ればOKです。
3列が選択されている状態で「列のピボット解除」を実行します（下図）。
あら簡単。3行のデータになりました。
「ピボット解除」というのはわかりにくい日本語ですね。英語では unpivot でこれまたわかりにくい。ピボットは和訳するとしたら「軸」とか。
これで大事なところはだいたいできたのですけど、Power Query に慣れるためにもう少しデータをキレイキレイしていきましょう。たとえば、「相手初手・相手控え１・相手控え２」をシンプルな「初手・控え」に変換したいので、値の置換を行います（下図）。変換したい値が入っている列を選択した状態で実行してください。
たとえば「相手」を消したいので以下のように設定します。
同じように「１」と「２」を消して、そんでもって見出しの名前を「位置」と「ポケモン」に変えた結果が以下の通り、変換完了です。
Power Query 上でお望みの変換結果が表示されたら「閉じて読み込む」で Excel に戻ることができますが、その前に「詳細エディター」を開いてみるとちょっとおもしろいものが見れます（下図）。
うおお！これってプログラムじゃねーか！そうなんです。 Power Query は、ポチポチしているだけで舞台裏ではこんなものができちゃうすごいやつなんです。パワー！
プログラミングに慣れている人は時にこのプログラムを直接編集したりしますが、必要ないことがほとんどです。「閉じて読み込む」で Excel に戻ってください。
新しいシート上に変換後のテーブルができているはずです。これは自分で手入力したテーブルと同じように扱うことができますので、ここからピボットテーブルを作ることももちろんできます。ここまでくればあとは簡単にできるはずです。
まとめと振り返り：ここから学んでほしいこと
この作業を通じて、みなさんには単なる Excel の使い方以上のことを学んでほしいと思っています。
データの分析はデータを集めるところから始まる：集計後のデータを渡されてそれをきれいなグラフにするだけで Excel を終わらせてはもったいないです。Excel は上図に示した収集・分析・プレゼンまでの全体をサポートしてくれる強力なアイテムです。
人はミスをするものだから機械によるチェックを活用すべき：人がミスするのはデータ入力だけではありません。身の回りに自動化できずにどうしても人手でやっていることはないでしょうか？機械でチェックできるものはないでしょうか？考えてみる機会にしてみてください。
元データがシンプルな形であれば、分析もシンプルにできる：世の中、難しそうなことをしている方が偉いみたいな風潮がありますが、簡単にできることを難しくしているだけなのはダメですよね。データ分析に限らず、おおもとの部分が複雑になっているせいで、関係者が全員大変な思いをしていることなどよくありそうですよね。
演習：次はこれをやってみよう
・ポケモン選出率のピボットテーブルを作ろう。
・他の入力規則を設定してみよう。例：対戦日には日付しか入力できない。
・分析結果からグラフを作ろう。
・分析結果を活用して Go Battle League で LEGEND になろう。
最後まで読んでくださってありがとうございました。
最後まで読んでくださった方へのお礼として2021年9月16日から24日までの実際のGBLデータ（約300戦分）とそれを分析した結果を置いておきます。本記事で書いた分析に加えて、パーティの集計もしています。
ここまで読んでくださってありがとうございます。サポートいただけましたら意欲ある学生を支援するのに使わせていただきます。