アンケート集計を例にピボットテーブルの機能を使ってみる 2

こちら の続き。

アンケートの集計をしています。
20 名分のアンケート結果がテーブル (リスト) にまとめられているので、ピボットテーブルで [問1] の集計結果をレポート化しました。
01.png
 

ピボットテーブルをコピーする

今回は、[問2] と [問3] の集計をするピボットテーブルを作ります。

1 つ目 (問1) と同じように 2 つ目と 3 つ目を作成してもたいした手間ではないのですが、元データは同じだし、更新をしたら 3 つのピボットテーブルがすべて更新されるようにしておきたいので、キャッシュ (ファイルの内部で持っている集計元データのコピー) を分けたくないし、せっかく列幅の自動調整のオフといった設定もしてあるから、今回はコピーして再利用します。

02.png
 

  1. ピボットテーブルを選択して、コピーを実行して貼り付けます。
    03.png

    04.png
     
  2. コピーしたピボットテーブルで、集計に不要なフィールド ([問1]) を削除します。
    05.png
     
  3. 集計に必要なフィールド ([問2]) を選択して配置します。
    06.png
     
  4. 最初に [値] エリアに配置したフィールドの設定 (集計方法、フィールド名) を変更し、[比率] を求めるフィールドを追加します。

    下図のようにするには、問1 のピボットテーブルと同じように、データの並べ替えを行い、必要であればスタイルを変更します。
    07.png

 

レイアウトを変更 (ラベルを変更) する

現在のピボットテーブルは、左上に「行ラベル」というラベルが表示されています。そのため、どの問いのピボットテーブルなのかがわかりにくいですね。

このセル (ラベル) はセルを編集して任意のラベルにすることもできますが、レポートのレイアウト機能を使って、元のフィールドの名前が表示されるようにすることもできます。
08.png

レポートのレイアウトには、[コンパクト形式]、[アウトライン形式]、[表形式] の 3 つがあります。
3 つの違いは、下図で確認してください。[行ラベル] の表示のされ方が違いますね。

加えて、[アウトライン形式] と [表形式] は、既定でフィールド名がラベルとして表示されます。
09(2).png

今回のピボットテーブルのように、[行ラベル] にフィールドが 1 つしかないのなら、レイアウトを変えても階層の表示には影響がないので、「フィールド名をラベルとして表示したい」という目的で、[アウトライン形式] か [表形式] に変更してもよいです。

  1. [ピボットテーブル ツール] の [デザイン] タブの [レイアウト] グループの [レポートのレイアウト] をクリックして運用するレイアウトをクリックして選択します。
    10.png
     
  2. [アウトライン形式] か [表形式] を選択すると、左上のラベルにフィールド名 (問いの番号) が表示されます。
    11.png

    2 つ目 (問2) のピボットテーブルにもどうぞ。
    12.png

 

アイテムのないデータを表示する

アンケートの集計でよくあるのは、選択肢は用意されていたけれど回答する人がいなかったので、集計元データにその値が含まれておらず、ピボットテーブルに表示されない、というケースです。

そりゃそうだ。ピボットテーブルは集計元データがすべてですもん。ないものはでてきません。

たとえば、問3 のピボットテーブルを作ってみたら、集計元データに「1」がなかったので、ピボットテーブル上にも表示されていません。
13.png

しかし、こんな感じ ↓ で、「1 は 0 でした」と表示したいというリクエストがよくあります。

これをやってみましょう。
14.png
 

  1. もともと、集計元データのテーブルには、No20 までの 20 件のデータが入っています。
    このテーブルにラベルを作るためのダミー データをいれます。

    今回の場合は、[問3] に「1」という値がほしいだけなのですが、[問1]、[問2] を空欄にしてしまうと、一時的にでも「(空白)」というアイテムが表示されてしまうので、私はすべてのフィールドに「1」を入れました。

    どうせ削除するダミー データなので、そこまでこだわらなくてもいいと思いますが、[問3] に「1」は入れないとだめです。
    15.png
     
  2. ピボットテーブルを更新すると、追加したダミー データが反映されるため、[問3] のピボットテーブルに「1」という選択肢の集計結果が追加されます。
    16.png
     
  3. 集計元データからダミー データの行を削除します。

    なお、テーブルの場合はデータの削除に合わせてテーブルの範囲をちゃんと縮小してください。
    (空白の行がない状態にしてください。それをしないと「(空白)」というアイテムが残りますよ。
    17.png
     
  4. ピボットテーブルを更新します。

    あら、「1」がまた消えちゃった! という状態で正解。
    表示されていないだけで、キャッシュには「1」というラベルは残っています。
    18.png
     
  5. [行ラベル] エリアを右クリックして [フィールドの設定] ダイアログ ボックスを表示し、[レイアウトと印刷] タブの [データのないアイテムを表示する] をオンにして、[OK] をクリックします。
    19.png
     
  6. 集計元データに「1」というデータはありませんが、[行ラベル] エリアにラベルが表示されます。
    20.png

 

空白セルに「0」を表示する

データがないところは空白セルになっています。
空白ではなく、「0」などを明示的に表示するには、[ピボットテーブル オプション] ダイアログ ボックスで設定します。
 

  1. [ピボットテーブル オプション] ダイアログ ボックスの [レイアウトと書式] タブの [空白セルに表示する値] をオンにし、右側に表示したい値を入力して [OK] をクリックします。
    21.png
     
  2. 空白セルに指定した値が表示されます。
    22.png

 


アンケート集計を例に、ピボットテーブルの機能や設定について 2 回に分けてご紹介しました。

ピボットテーブルは、集計をするためにフィールド (列) を指定するだけで思い通りのレポートになることはほとんどありません。
おそらくなんらかの設定変更や追加、工夫をしなければなりません。

その場合の一例として、ご紹介した内容を参考にしてもらえたらうれしいです。


石田かのこ