こんにちは。
今日は採点履歴をEXCELで整理する場合に利用可能なEXCELのPowerQueryについて紹介したいと思います。
https://support.office.com/ja-jp/article/microsoft-power-query-for-excel-%E3%81%AE%E6%A6%82%E8%A6%81-6e92e2f4-2079-4e1f-bad5-89f6269cd605
上記のリンクではjsonデータファイルについては説明されてませんが、現在のEXCELはjsonデータも取り込めます。
まず前提としてjsonデータ、XMLデータについての説明ページのリンクを貼っておきます。
jsonとは
XMLとは
ざっくり説明するとどちらもデータの構造を記述可能なテキストで書かれたデータ記法と思っていただければいいかと思います。
JOYSOUNDはWeb画面を作成するにあたり、API経由で取得したデータベースからの情報をjsonデータとして持ってそれを利用して採点履歴画面などを作っています。
DAMはWeb画面を作成するにあたり、API経由で取得したデータベースからの情報をXMLデータとして持ってそれを利用して採点履歴画面などを作っています。
APIは通常XHRとして呼び出しているのでChrome付属のDevToolで呼出し後の戻り値(レスポンス)のデータを見ることができます。
データはWebブラウザが画面表示用に取得した採点履歴データなだけなので生データを見ても自分は別に問題ないと思っています。
ではjsonデータが取得できてテキストファイルにした体で話をします。
EXCELのメニューから「データ」を選んで「データの取得」を押して「ファイルから」→「JSONから」を選択します。
取得してあるJSONファイルを選択します。
読み込み後左上の「テーブルに変換」を押します。
ここでListとある箇所を選んで、また「テーブルに変換」を押します。(間違えたら右側の小窓のところのデータ処理の手順の箇所で「×」を押してひと手順戻ります)
この状態でRecord行がたくさん出てきたと思います。列の見出しのところに左右に開くようなボタンがあるのでそれを押します。
列が展開されたら、そのままデータに書き出してもいいのですが、時間の列のデータがUNIX時間で表記されていて、文字長が長いので対数表示になっているのでこの列をテキストデータにしておきます。
以上で、左上の「閉じて読み込む」を押すとダイアログ窓が閉じてデータがEXCEL表に貼り付けられます。
以上で、採点履歴1ページ分の20行のデータが追加されます。
同じような手順で残りのページを1シートずつ表にしていき、セル参照などで月毎のデータのまとめシートを作ればいいかと思います。
1ページ目のQueryを作っておけば、別のページのデータでも同じ手順で読み込むので、Queryの複製をして、詳細を編集してjsonファイル名を書き換えれば簡単です。
画面コピペとEXCEL用にテキストを整形する手間が省けるのでいいかと思います。
PowerQueryではhttpリクエストを投げてWebから直接jsonデータを取得して同様に表にできますが、そのあたりはご自分のリスクでお願いします。
同様にDAMさんのデータもXMLデータを取得してXMLファイルにしてEXCELの表にできます。今回は割愛します。
以上、参考まで。
