shikumika’s diary

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

Excelピボットテーブルの値フィールドに、文字列をそのまま表示する方法

Excelピボットテーブルで、値フィールドに文字列をそのまま表示できずに困ったので解決方法のメモ。

ピボットテーブルにあるPower Pivot なら次のようにピボットテーブルの値に、文字列が表示できました。

Power Pivot は、最近のExcelに標準で含まれている機能。Power Pivot が含まれている Office のバージョンはこちら

 

アウトプットイメージ

ピボットテーブルに値表示のイメージ

ピボットテーブルに値表示

 

上記の元データは、次のような表。年収順位を「行」、年度を「列」、区分を「値」フィールドに設定して、上記のアウトプットが作成可能。

ピボットテーブルに値表示させたい元データ

 

作成手順

PowerPivotのメジャーという機能を使い、次のような数式を設定する。

=IF(HASONEVALUE(columnName),VALUES(columnName),"")

columnNameには、列の名前を入れる。

 

具体的には、次のとおり。

ピボットテーブルの作成

まず、いつもと同様にデータ範囲を指定してピボットテーブルを作成する。ただし、この時、「このデータをデータモデルに追加する」に必ずチェックする。

チェックしないと、この後のメジャーという機能が使用できない。

データ範囲を指定してピボットテーブルを作成のイメージ

 

作成したピボットテーブルで、メジャーの追加


シートのようなアイコンで、右クリック

ピボットテーブルでメジャー作成のイメージ1

 

メジャーの追加

ピボットテーブルでメジャー作成のイメージ2

 

メジャーの名前と、数式を入力

例) =IF(HASONEVALUE('範囲'[区分]),VALUES('範囲'[区分]),"")

'範囲'[区分]の箇所は、該当の値として表示させたい列名を記載する。

数式HASONEVALUE等の設定イメージ

 

式に問題なければ、fxで始まるメジャーが追加される。

数式HASONEVALUE等の設定結果のイメージ

 

作成したメジャーを値フィールドにし、列と行も設定すると、次のようになる。

ピボットテーブルに値表示のアウトプットイメージ

 

例えば、更に数値を表示したい場合など、通常のピボットテーブルのように設定すれば、次のような表も可能。

ピボットテーブルに値表示のアウトプットイメージ

他の方法との違い

今回は、Power Pivotを使用した方法のメモ。Excelでその他に同様のことができる方法との違いは次のとおりです。現状、今回紹介したメジャーでHASONEVALUEを使った方法が手軽そう。

  • 最近のExcelに標準でついているPower Queryを使った方法
    習得すると簡単だがPowerQueryの習得に時間かかる印象。

  • Power Pivotのメジャーで、CONCATENATEXを使う方法
    アウトプットで、余分な文字ができてしまい、レイアウト調整が必要

なお、メジャーでSELECTEDVALUEという式は、Power BI でないと使えなさそう。

 

また、Excelピボットテーブルの一覧で、文字列をそのまま表示することが目的なら、レイアウトの制限はあるが、次の方法でも簡易的に可能です。

shikumika.org