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