Excelピボットテーブルで、値フィールドに文字列をそのまま表示できずに困ったので解決方法のメモ。
ピボットテーブルにあるPower Pivot なら次のようにピボットテーブルの値に、文字列が表示できました。
なお、動作確認のExcelは「Microsoft® Excel® for Microsoft 365 MSO」（バージョン2311）です。
Power Pivot は、最近のExcelに標準で含まれている機能で、Power Pivot が含まれている Office のバージョンはこちらです。
内容
アウトプットイメージ
上記の元データは、次のような表。年収順位を「行」、年度を「列」、区分を「値」フィールドに設定して、上記のアウトプットが作成可能。
作成手順
PowerPivotのメジャーという機能を使い、次のような数式を設定する。
=MAX(columnName)
columnNameには、列の名前を入れる。メジャーのMAXでは、昇順で並び替えた一つの文字列が取得可能でMINでも良い。
ただし、一つのみ表示なので当該の固有な値が２つ以上存在する場合は、別途紹介する”CONCATENATEX関数”を使用する。
具体的には、次のとおり。
ピボットテーブルの作成
まず、いつもと同様にデータ範囲を指定してピボットテーブルを作成する。ただし、この時、「このデータをデータモデルに追加する」に必ずチェックする。
チェックしないと、この後のメジャーという機能が使用できない。
作成したピボットテーブルで、メジャーの追加（MAX）
シートのようなアイコンで、右クリック
メジャーの追加
メジャーの名前と、数式を入力
例） =MAX('範囲'[区分])
'範囲'[区分]の箇所は、該当の値として表示させたい列名を記載する。
MAXなので、昇順で並び替えた”一つの文字列”ということを理解して使用する。
式に問題なければ、fxで始まるメジャーが追加される。
作成したメジャーを値フィールドにし、列と行も設定すると、次のようになる。
例えば、更に数値を表示したい場合など、通常のピボットテーブルのように設定すれば、次のような表も可能。
以上、Power PivotのメジャーでMAXを使用し、Excelピボットテーブルの値フィールドに、文字列をそのまま表示する方法でした。
上記の記事で使用したExcelファイルを「note」で販売しています。
24時間以内であれば、返金申請が可能ですので、上記の手順で不明な場合や、実際に動作しているファイルを確認したい場合などにご利用ください。
他の方法との違い
Excelで同様のことができる他の方法との違いは次のとおりです。
ただし、現状、今回紹介したPower PivotのメジャーでMAX（もしくは、MIN）を使った方法が最も手軽な印象です。
１．Power Pivotのメジャーで、"HASONEVALUE"と"VALUE"を使う方法
上記事例でMAXの数式箇所を次のように記載して表示可能。ただし、IF文を含む数式となり、数式のシンプルさに欠ける。
例） =IF(HASONEVALUE('範囲'[区分]),VALUES('範囲'[区分]),"")
２．Power Pivotのメジャーで"CONCATENATEX"を使う方法
総計欄に余分な文字列ができるが非表示で目的の実現可能で、同じセル内に値を２つ表示することも可能。
shikumika.org
３．最近のExcelに標準でついているPower Queryを使った方法
Power Queryは、ピボットテーブルとしてのレイアウトの自由度、作成、修正の容易さに課題がある印象。手順は次のとおりです。
４．SELECTEDVALUEは、Power Pivotのメジャーになかった
なお、メジャーでSELECTEDVALUEという式は、Power BI でないと使えなさそう。
５．Excelピボットテーブルで、簡易的に文字列をそのまま表示
また、Excelピボットテーブルの一覧で、文字列をそのまま表示することが目的なら、レイアウトの制限はあるが、次の方法でも簡易的に可能です。
参考情報
データの集計・可視化の操作事例を以下にまとめています。
また、パワーピボットに関心を持たれた方は、次の書籍で何ができるのか全体像が理解できると思います。
