ExcelのPower Queryでアンケート結果を集計してみる
多くの人が仕事でExcelを使っていると思いますが、
多分99%以上の人が使っていないであろう機能がてんこ盛りだと思います。
なお私はExcelでセルを方眼紙のようにして書類を作るのは絶対に許せない派です。
今回はExcelに入っているPower Queryを使ってみました。
ことの始まり
大雑把にいうと、こういうことを行う必要がありました。
・オフラインでのアンケート集計機能を低コスト（重要）で作りたい
・アプリ中のおまけ機能なので、簡素な作りでいい
・Excelで見れると便利だね
今回の解決方法はこちら
・回答者は共用マシンにインストールされたアプリでアンケートに答える
・アプリでは回答結果を1行ずつcsvファイルに書き出す
・そのファイルをExcelで読み込み、集計して表にする
しかし集計のために数式を書くのはまだしも、
データ更新のためにファイルを読み込んで整形したりそれを行うマクロを書いたりするのはちょっと面倒です。
そこでPower Queryを使うことにしました。
Power Queryとは
MicrosoftのPower BIとも関係があったりしますが、
今回ここで扱うのはMicrosoftが公開しているExcelのアドインです。
データを外部から取得してExcelで扱えるようにしたりする、なんか凄い奴です。（手抜き説明）
市販のパッケージではOffice 2013以降で無料で使用できます。Officeを買えば無料で使用できます。（Office 2010ではProfessional Plusというボリュームライセンスの製品でのみ使用できました）
Download Microsoft Power Query for Excel from Official Microsoft Download Center
また、Office 2016では「取得と変換」という標準機能となり、アドインをダウンロードする必要も無くなりました。
使用例
CSVファイルの準備
最初にヘッダだけ書き込まれたCSVファイルを用意し、アプリ側でアンケートに答える度に、その結果をカンマ区切りで１件につき１行ずつ書き込むだけのシンプルな実装です。
シンプルイズベスト。
ちゃんとするならDBにテーブル作っておいて１行ずつ書き込んだりするべきなのかなーと思いました。
Excelでの取り込み
まずExcelを起動し、空白のブックを作ります。
データリボンの「新しいクエリ ＞ ファイルから ＞ CSVファイルから」
を選択し、アンケートの結果が入った（という想定の）CSVファイルを取り込み先とします。
これでCSVファイルからデータを取得するクエリが作成され、
さらにテーブルの内容が書かれている新規シートが作成されました。
今回はCSVファイルからデータを取得していますが、
SQL ServerやODBCを通してRDBMSから取得するようにも出来ます。
というかそれが本来の使い方だと思います
シートの編集
数式で集計したり、見やすいように色を付けたりして結果を分かりやすくします。
今回はこれだけ編集しました。
・テーブルのA列のデータ部分を範囲選択して「クイック分析」→「合計＞個数」
（集計行がテーブルの下部に追加され、A7セルではSUBTOTAL関数でレコードの件数が表示されるようになりました。）
・COUNTIF関数で各項目の回答を集計し、その下ではA7で割って回答率を計算
・AVERAGE関数で点数項目の平均点を計算
・セルの書式設定を「0"件"」などにして見やすく
データ更新
アンケートの結果が集まりました。CSVファイルが更新されています。（という設定）
ここでデータの「すべて更新」を押すなどして、データを再取得します。
書式などはそのままに、テーブルだけが更新されました。便利！
まとめ
このようにアンケート集計の仕組みを簡単に作成出来ました。
多分極めれば色々なことが出来ると思います。少なくともマクロを書いて弄るよりはずっと楽でした。
Power Queryは強力なツールです。200m先のコンビニに行くのにタクシーを呼ぶような贅沢感がありました。
