shikumika’s diary

日々の事務作業で手間なことを簡単にできる仕組み(自動化、方法など)、困ったことの解決方法を調べた備忘録

【Excel】年度別の順位推移表のようなマトリクス表を作成する方法

Excelで、下図の左側のような元データから右側のような年度別の順位推移表(マトリクス表)を作成する方法についての備忘録。順位表に値をそのまま表示するようなケースです。

一覧データからマトリクス表(例:年度別のランキング推移表)

なお、結論は、次のPower Pivot(Excelの標準機能) を利用する方法が基本。繰り返し発生する業務や、分析軸を編集したいなど後日のメンテナンス性を考えた場合はPower Pivotの方法が最適となるケースが多い印象。

shikumika.org

 

別の方法として、同じくExcelの標準機能であるPowerQueryの「列のピボット」を利用しても作成可能。ただし、ピボットテーブルとしてのレイアウトの自由度、作成、修正の容易さなどは、上述のPower Pivotの方法より劣る印象。

 

こんなPowerQueryの「列のピボット」ですが、後工程や前工程のデータ処理の都合で、PowerQueryで処理した方が便利な時もあります。なので、以下はPowerQueryでしたい時の備忘録。

PowerQueryでの作成手順

基本の手順は、次のとおりです。ただし、少しイメージが持ちづらいので、以下で補足。

support.microsoft.com

 

補足手順

横軸にしたいフィールドを選択後、「変換」-「列のピボット」

横軸にしたいフィールドを選んでおくこと。

 

値列と、値の集計関数を設定

「値の集計関数」は、「最小値」を選択しておく。

補足:

「最小値・最大値・中央・集計しない」のいずれかで、値をそのまま表示可能。

重複する値がある場合は「最小値」は昇順で表示され、「集計しない」はErrorとなり空白になる。「集計しない」を選び、いくつかステップを重ねる複雑な処理をするとエラーになることがあったので、「最小値」や「最大値」の方が良さそう。

 

上記で「OK」をクリック後、「閉じて読み込む」をクリックすると、冒頭のマトリクス表ができる。

注意点と補足

PowerQueryの「列のピボット」で注意が必要な点は、行フィールドや値フィールドに該当する項目を2つ以上選択できない、という仕様みたい。他に方法があるかもしれないが、3列を超えるデータの場合は、事前に列削除などが必要。


なので、上記のように4列ある場合、次のような表ができてしまいます。

 

以上、PowerQueryの「列のピボット」を使用した手順でした。