Excelで、下図の左側のような元データから右側のような年度別の順位推移表(マトリクス表)を作成する方法についての備忘録。順位表に値をそのまま表示するようなケースです。
なお、結論は、次のPower Pivot(Excelの標準機能) を利用する方法が基本。繰り返し発生する業務や、分析軸を編集したいなど後日のメンテナンス性を考えた場合はPower Pivotの方法が最適となるケースが多い印象。
別の方法として、同じくExcelの標準機能であるPowerQueryの「列のピボット」を利用しても作成可能。ただし、ピボットテーブルとしてのレイアウトの自由度、作成、修正の容易さなどは、上述のPower Pivotの方法より劣る印象。
こんなPowerQueryの「列のピボット」ですが、後工程や前工程のデータ処理の都合で、PowerQueryで処理した方が便利な時もあります。なので、以下はPowerQueryでしたい時の備忘録。
PowerQueryでの作成手順
基本の手順は、次のとおりです。ただし、少しイメージが持ちづらいので、以下で補足。
補足手順
横軸にしたいフィールドを選択後、「変換」-「列のピボット」
横軸にしたいフィールドを選んでおくこと。
値列と、値の集計関数を設定
「値の集計関数」は、「最小値」を選択しておく。
補足:
「最小値・最大値・中央・集計しない」のいずれかで、値をそのまま表示可能。
重複する値がある場合は「最小値」は昇順で表示され、「集計しない」はErrorとなり空白になる。「集計しない」を選び、いくつかステップを重ねる複雑な処理をするとエラーになることがあったので、「最小値」や「最大値」の方が良さそう。
上記で「OK」をクリック後、「閉じて読み込む」をクリックすると、冒頭のマトリクス表ができる。
注意点と補足
PowerQueryの「列のピボット」で注意が必要な点は、行フィールドや値フィールドに該当する項目を2つ以上選択できない、という仕様みたい。他に方法があるかもしれないが、3列を超えるデータの場合は、事前に列削除などが必要。
なので、上記のように4列ある場合、次のような表ができてしまいます。
以上、PowerQueryの「列のピボット」を使用した手順でした。