全国採点GP年次集計表の作成

コピペした、曲毎に3行あるテキストデータをエディタで整形したいのですが、順位、人数、点数、曲名、アーティスト名で1行になり、タブ区切りにして、順位や人数、点数の「位」や「人」「点」などや、「自慢する」「マイうた」は削除したいです。

まずは行の頭から1行編集して次の行の行頭に行くまでをエディタのキー操作の記録で記録してマクロを作ります。

使ったキーは、行頭に飛ぶctrl+Q S、1word右に行くctrl+>、DELETE、TAB、そして行末に飛ぶctrl+Q D、BACKSPACE、DOWNのキーのみです。

マクロの内容です(秀丸エディタのものです)

このマクロを行数分実行して、月毎の採点結果テキストデータを整形します。

整形後です。

では各月の整形後のデータをスプレッドシートに貼り付けたいと思います。

ここで、googleドキュメントのスプレッドシートの出番です。

Chromeブラウザだと、左上のアプリというところを開くと、このような画面になって、スプレッドシートがすぐに使えます。(Chromeブラウザにgoogleアカウントでログインしている場合)

スプレッドシートに下のように貼り付けます。

年、月カラムは最初は空白にしておいて、

3カラム目に1月分のテキストデータのコピーを貼って

2カラム目に1月の「1」を入れて、オートフィル

1カラム目に年の「2017」を入れてオートフィル

これを12ヶ月分繰り返します。

点数カラムは、小数点以下3桁表示にしておきます。

これで、2017年のスプレッドシートデータはできたので、

このデータからピボットテーブルを用いて、

曲毎の今年の年間最高点表を作ってみましょう。

まずは、メニューの「データ」→「ピボットテーブル」でピボットテーブルエディタを起動

曲名はアーティストが違っても同じ曲名なものがあるので

まずは「行」に「アーティスト名」を追加して「曲名」を追加。

「値」に「点数」を追加。

この時に「点数」は「MAX」つまり最大値を表示するようにしておきます。

「アーティスト名」「曲名」の「合計の表示」もいらないのでチェックボックスをOFFにします。

このような、年間最高得点表が出来上がります。

点数のMAXのカラムは小数点以下3桁表示に変更します。

では次に、月毎の採点曲数集計表を作ります。

同じように、元データの表で、「データ」→「ピボットテーブル」

「行」に「年」「月」を追加。

「値」に「曲名」を追加。

自動で「曲名」はCOUNTAになりますが、これで曲名の「件数」が集計されます。

採点曲数集計表です。

このように、ピボットテーブルで簡単に集計できます。

では次に、曲の月毎の点数の分布のピボットテーブルを作ってみます。

「行」に「アーティスト名」「曲名」を追加。合計ははずしておきます。

「列」に「月」を追加。合計は表示にしておきます。

「値」に点数を追加。集計方法は「MAX」にしておきます。

これで、月次曲毎の点数表が出来上がります。





「列」に「年」を追加して、列の順番を「年」が先に来るようにすれば、元シートに来年のデータを追加しても同じような体裁で来年の年月が追加されたピボットテーブルが出来上がります。

では次に、月別順位分布表を作ってみます。
「行」に「順位」を追加。
「列」に「年」「月」を追加。
「値」に「曲名」を追加。集計はデフォルトで「COUNTA」になりますがこの件数でOKです。
このようなピボットテーブルが出来上がります。

元データをピボットテーブルで加工する方法によって、いろんな角度から見た情報が簡単に確認できて、意外と面白いと思いませんか？

また、元データのシートを開いた状態で、画面右下のデータ探索のボタンを押すと、様々なグラフが自動で作成されたものが用意されていますので、自由にグラフをシートに挿入したりして確認できます。

このように、年間の全国採点状況を振り返ることができるので、是非年始の休みには、全国採点の年次集計表を作ってみてください。

参考になれば幸いです。

追記
年間最高得点表(11月までのデータ)です。
Googleスプレッドシートで作成した、最高得点表を「形式を指定してダウン」ロードで「Webページ」を選んだものを貼り付けました。