shikumika’s diary

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

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

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

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

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

 

内容

 

アウトプットイメージ

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

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

 

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

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

 

作成手順

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

=MAX(columnName)

columnNameには、列の名前を入れる。メジャーのMAXでは、昇順で並び替えた一つの文字列が取得可能でMINでも良い。

ただし、一つのみ表示なので当該の固有な値が2つ以上存在する場合は、別途紹介する”CONCATENATEX関数”を使用する

 

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

ピボットテーブルの作成

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

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

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

 

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

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

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

 

メジャーの追加

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

 

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

例) =MAX('範囲'[区分])

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

MAXなので、昇順で並び替えた”一つの文字列”ということを理解して使用する。

 

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

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

 

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

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

 

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

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

他の方法との違い

今回は、Power Pivotを使用した方法のメモ。Excelでその他に同様のことができる方法との違いは次のとおりです。現状、今回紹介したメジャーでMAX(もしくは、MIN)を使った方法が手軽そう。以前は、HASONEVALUEを使用した方法でしていたが、目的的にはMAX(もしくはMIN)と、Excelでは次の方法が良さそう。

  • Power Pivotのメジャーで、CONCATENATEXを使う方法
    総計欄に余分な文字列ができるが非表示で目的の実現可能で、同じセル内に値を2つ表示することも可能

     

    shikumika.org

  • 最近のExcelに標準でついているPower Queryを使った方法
    ピボットテーブルとしてのレイアウトの自由度、作成、修正の容易さに課題がある印象。手順は次のとおり。

    shikumika.org

     

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

 

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

shikumika.org

 

パワーピボットに関心を持たれた方は、次の書籍で何ができるのか全体像が理解できると思います。

 

 

個人的には、VBAもまだ捨てきれませんが、パワーピボットはとても便利です。

パワーピボットなら次のように比較結果を見やすいレイアウトも可能です。

shikumika.org