こちら の続き。
アンケートの集計をしています。
20 名分のアンケート結果がテーブル (リスト) にまとめられているので、ピボットテーブルで [問1] の集計結果をレポート化しました。
ピボットテーブルをコピーする
今回は、[問2] と [問3] の集計をするピボットテーブルを作ります。
1 つ目 (問1) と同じように 2 つ目と 3 つ目を作成してもたいした手間ではないのですが、元データは同じだし、更新をしたら 3 つのピボットテーブルがすべて更新されるようにしておきたいので、キャッシュ (ファイルの内部で持っている集計元データのコピー) を分けたくないし、せっかく列幅の自動調整のオフといった設定もしてあるから、今回はコピーして再利用します。
-
ピボットテーブルを選択して、コピーを実行して貼り付けます。
-
コピーしたピボットテーブルで、集計に不要なフィールド ([問1]) を削除します。
-
集計に必要なフィールド ([問2]) を選択して配置します。
-
最初に [値] エリアに配置したフィールドの設定 (集計方法、フィールド名) を変更し、[比率] を求めるフィールドを追加します。
下図のようにするには、問1 のピボットテーブルと同じように、データの並べ替えを行い、必要であればスタイルを変更します。
レイアウトを変更 (ラベルを変更) する
現在のピボットテーブルは、左上に「行ラベル」というラベルが表示されています。そのため、どの問いのピボットテーブルなのかがわかりにくいですね。
このセル (ラベル) はセルを編集して任意のラベルにすることもできますが、レポートのレイアウト機能を使って、元のフィールドの名前が表示されるようにすることもできます。
レポートのレイアウトには、[コンパクト形式]、[アウトライン形式]、[表形式] の 3 つがあります。
3 つの違いは、下図で確認してください。[行ラベル] の表示のされ方が違いますね。
加えて、[アウトライン形式] と [表形式] は、既定でフィールド名がラベルとして表示されます。
今回のピボットテーブルのように、[行ラベル] にフィールドが 1 つしかないのなら、レイアウトを変えても階層の表示には影響がないので、「フィールド名をラベルとして表示したい」という目的で、[アウトライン形式] か [表形式] に変更してもよいです。
-
[ピボットテーブル ツール] の [デザイン] タブの [レイアウト] グループの [レポートのレイアウト] をクリックして運用するレイアウトをクリックして選択します。
-
[アウトライン形式] か [表形式] を選択すると、左上のラベルにフィールド名 (問いの番号) が表示されます。
2 つ目 (問2) のピボットテーブルにもどうぞ。
アイテムのないデータを表示する
アンケートの集計でよくあるのは、選択肢は用意されていたけれど回答する人がいなかったので、集計元データにその値が含まれておらず、ピボットテーブルに表示されない、というケースです。
そりゃそうだ。ピボットテーブルは集計元データがすべてですもん。ないものはでてきません。
たとえば、問3 のピボットテーブルを作ってみたら、集計元データに「1」がなかったので、ピボットテーブル上にも表示されていません。
しかし、こんな感じ ↓ で、「1 は 0 でした」と表示したいというリクエストがよくあります。
これをやってみましょう。
-
もともと、集計元データのテーブルには、No20 までの 20 件のデータが入っています。
このテーブルにラベルを作るためのダミー データをいれます。
今回の場合は、[問3] に「1」という値がほしいだけなのですが、[問1]、[問2] を空欄にしてしまうと、一時的にでも「(空白)」というアイテムが表示されてしまうので、私はすべてのフィールドに「1」を入れました。
どうせ削除するダミー データなので、そこまでこだわらなくてもいいと思いますが、[問3] に「1」は入れないとだめです。
-
ピボットテーブルを更新すると、追加したダミー データが反映されるため、[問3] のピボットテーブルに「1」という選択肢の集計結果が追加されます。
-
集計元データからダミー データの行を削除します。
なお、テーブルの場合はデータの削除に合わせてテーブルの範囲をちゃんと縮小してください。
(空白の行がない状態にしてください。それをしないと「(空白)」というアイテムが残りますよ。
-
ピボットテーブルを更新します。
あら、「1」がまた消えちゃった! という状態で正解。
表示されていないだけで、キャッシュには「1」というラベルは残っています。
-
[行ラベル] エリアを右クリックして [フィールドの設定] ダイアログ ボックスを表示し、[レイアウトと印刷] タブの [データのないアイテムを表示する] をオンにして、[OK] をクリックします。
-
集計元データに「1」というデータはありませんが、[行ラベル] エリアにラベルが表示されます。
空白セルに「0」を表示する
データがないところは空白セルになっています。
空白ではなく、「0」などを明示的に表示するには、[ピボットテーブル オプション] ダイアログ ボックスで設定します。
-
[ピボットテーブル オプション] ダイアログ ボックスの [レイアウトと書式] タブの [空白セルに表示する値] をオンにし、右側に表示したい値を入力して [OK] をクリックします。
-
空白セルに指定した値が表示されます。
アンケート集計を例に、ピボットテーブルの機能や設定について 2 回に分けてご紹介しました。
ピボットテーブルは、集計をするためにフィールド (列) を指定するだけで思い通りのレポートになることはほとんどありません。
おそらくなんらかの設定変更や追加、工夫をしなければなりません。
その場合の一例として、ご紹介した内容を参考にしてもらえたらうれしいです。
石田かのこ