- EXCELを使って業務を効率化したい方
- EXCELでデータの前処理をしたい方
▶ 以下の記事でPowerQueryを使った自動処理を紹介しています。
はじめに
通常、ピボットテーブルは集計なので合計や個数などの数値データとなります。ただ、値がテキストデータであるときにそのまま表示させたいことはないですか？今回はピボットテーブルに文字列をそのまま表示させる方法を基本から解説します。
ピボットテーブルの値フィールドに文字列を表示させる
ピボットテーブルでは文字列が格納されたフィールドを値フィールドに設定すると、「値フィールドの設定」では、「集計方法」は「個数」「計算の種類」は「計算なし」となります。
このように値フィールドは数値データとし表示されます。値フィールドに普通に文字列のフィールドを指定してもできないので、あらたにメジャーを追加する必要があります。
サンプルデータ
今回はどうぶつたちの試験結果についてまとめた次のデータを使います。
ピボットテーブルでは「値」に「コメント」フィールドを指定すると個数の処理がされてしまいます。↓
今回はここに「コメント」の文字列が表示される方法を解説します。この表だけだと、ピボットテーブルにしなければ、テーブルそのものでいいんじゃないか？と思うかもしれませんが、テーブル間でリレーションを組んでいたり、複数のフィルタをかけた状態で文字列を表示させたいときにこの方法を知っておくと便利です。
利用するDAX関数（HASONEVALUE）
どのようなメジャーを追加すればよいでしょうか？文字列の値が格納されているので、文字列をそのまま返す関数を定義すればよいですね。ただ、値が2つあった場合にはエラーとなってしまいます。そこで、値が１つであることを判定して、値が１つの時はそのまま値（文字列）を返し、値が1つでないときは空白を返す関数を定義しましょう。値が一つであるかどうかを判定する、HASONEVALUEというDAX関数が用意されています。（はじめなんて読むんだろう、と思ったんですが、 has one valueなんですね。これなら覚えやすいですね。）まずはこの関数の書式を確認しておきましょう。
columnName のコンテキストが 1 つの個別の値のみにフィルター処理されている場合、TRUE を返します。 それ以外の場合は、FALSE を返します。
https://learn.microsoft.com/ja-jp/dax/hasonevalue-function-dax
少しわかりにくいですが、値が1つに定まればTrue、それ以外の時はFalseを返すということですね。なので新たなメジャーを定義するときにIF関数とこのHASONEVALUE関数を組み合わせるとよさそうです。
メジャーの追加
ではメジャーを追加していきましょう。「ピボットテーブルのフィールド」のテーブル名のところで右クリックをします。（下図の①の部分です）
右クリックメニューが表示されるので「メジャーの追加」をクリックします。すると「メジャー」ダイアログが表示されます。
ここではまず、「メジャーの名前」と「値の説明」を入力しましょう。その後、数式の入力に進みます。先ほど確認したようにIF関数とHASONEVALUE関数で値が一つならその値を、値が一つに定まらないときは空白を出力する設定としましょう。
式を入力したら式の確認をします。すぐ上に「DAX式を確認」のボタンがあるのでこれをクリックします。すると、ダイアログ下部に式の確認結果が表示されます。ここでエラーがないことを確認したら「ＯＫ」をクリックします。
EXCELのSheetに戻ると、ピボットテーブルのフィールドに設定したメジャーが追加されています。
※「メジャーの名前」で指定した名前とその前に関数を表す記号fxがついています。
これを「値フィールド」にいれれば文字列の表示ができます。やってみましょう。
うまくできました！
まとめ
今回はピボットテーブルの値フィールドに文字列を表示する方法を紹介しました。すぐに使うことはないかもしれませんが、知らないとできないと思うので頭の片隅に入れておきましょう。
コメント
こちらで扱われている方法をぜひ実践したいのですが、メジャーの追加の説明の部分で→「ピボットテーブルのフィールド」のテーブル名のところで右クリックをします。と説明があるのですが、ピボットテーブルのフィールドにテーブル名が表示されません。これはエクセルのバージョンによるものでしょうか？ご教示いただけると幸いです。
blogを読んでいただきありがとうございました。
エクセルのバージョンによる場合もあるかもしれませんが、次のようにすると出てくるかもしれません。
おそらくピボットテーブルのもととなるテーブルがどこかのSheetにあると思いますが、
これを次の手順でデータモデルとして読み込んでみてもらえますか？
① 対象のテーブルまたはデータの範囲選択をしてから、「データ」メニューの「データの取得と変換」リボンから「テーブルまたは範囲から」を選択
② PowerQueryが立ち上がります。「ホーム」メニューの「閉じる」リボンの「閉じて読み込む」のプルダウンメニューから「閉じて次に読み込む」を選択
③ 「このデータをデータモデルに追加する」にチェックをしてOKをする
④ 「挿入」メニューの「テーブル」リボンから「ピボットテーブル」のプルダウンを表示させて、」データモデルから」を選択します。
こうして作成したピボットテーブルのテーブル名（データモデル）上で右クリックすると「メジャーの追加」メニューが表示されませんか？
お手数ですが、試していただけると幸いです。
ご丁寧にご教示いただきありがとうございます。
実践できました！！