Excelピボットテーブルで、値フィールドに文字列をそのまま表示できずに困ったので解決方法のメモ。
ピボットテーブルにあるPower Pivot なら次のようにピボットテーブルの値に、文字列が表示できました。
Power Pivot は、最近のExcelに標準で含まれている機能。Power Pivot が含まれている Office のバージョンはこちら。
内容
アウトプットイメージ
上記の元データは、次のような表。年収順位を「行」、年度を「列」、区分を「値」フィールドに設定して、上記のアウトプットが作成可能。
作成手順
PowerPivotのメジャーという機能を使い、次のような数式を設定する。
=MAX(columnName)
columnNameには、列の名前を入れる。メジャーのMAXでは、昇順で並び替えた一つの文字列が取得可能でMINでも良い。
ただし、一つのみ表示なので当該の固有な値が２つ以上存在する場合は、別途紹介する”CONCATENATEX関数”を使用する。
具体的には、次のとおり。
ピボットテーブルの作成
まず、いつもと同様にデータ範囲を指定してピボットテーブルを作成する。ただし、この時、「このデータをデータモデルに追加する」に必ずチェックする。
チェックしないと、この後のメジャーという機能が使用できない。
作成したピボットテーブルで、メジャーの追加
シートのようなアイコンで、右クリック
メジャーの追加
メジャーの名前と、数式を入力
例） =MAX('範囲'[区分])
'範囲'[区分]の箇所は、該当の値として表示させたい列名を記載する。
MAXなので、昇順で並び替えた”一つの文字列”ということを理解して使用する。
式に問題なければ、fxで始まるメジャーが追加される。
作成したメジャーを値フィールドにし、列と行も設定すると、次のようになる。
例えば、更に数値を表示したい場合など、通常のピボットテーブルのように設定すれば、次のような表も可能。
他の方法との違い
今回は、Power Pivotを使用した方法のメモ。Excelでその他に同様のことができる方法との違いは次のとおりです。現状、今回紹介したメジャーでMAX（もしくは、MIN）を使った方法が手軽そう。以前は、HASONEVALUEを使用した方法でしていたが、目的的にはMAX（もしくはMIN）と、Excelでは次の方法が良さそう。
- Power Pivotのメジャーで、CONCATENATEXを使う方法
総計欄に余分な文字列ができるが非表示で目的の実現可能で、同じセル内に値を２つ表示することも可能。
- 最近のExcelに標準でついているPower Queryを使った方法
ピボットテーブルとしてのレイアウトの自由度、作成、修正の容易さに課題がある印象。手順は次のとおり。
なお、メジャーでSELECTEDVALUEという式は、Power BI でないと使えなさそう。
また、Excelピボットテーブルの一覧で、文字列をそのまま表示することが目的なら、レイアウトの制限はあるが、次の方法でも簡易的に可能です。
パワーピボットに関心を持たれた方は、次の書籍で何ができるのか全体像が理解できると思います。
個人的には、VBAもまだ捨てきれませんが、パワーピボットはとても便利です。
パワーピボットなら次のように比較結果を見やすいレイアウトも可能です。