【命題】以下のアンケート結果を集計する
| A | B | C | D | E | F | G | H | I |
1 | no | 回収日 | 質問1 | 質問2 | 質問3 | 質問4 | 質問5 | 質問6 | 質問7 |
2 | Ans1 | 12/5 | 1 | 2 | 3 | 2 | 2 | 5 | 3 |
3 | Ans2 | 12/2 | 4 | 5 | 5 | 3 | 1 | 3 | 3 |
4 | Ans3 | 12/2 | 1 | 4 | 3 | 2 | 5 | 5 | 1 |
5 | Ans4 | 12/4 | 1 | 4 | 3 | 2 | 5 | 5 | 1 |
6 | Ans5 | 12/3 | 3 | 3 | 1 | 3 | 3 | 1 | 4 |
7 | Ans6 | 12/4 | 5 | 2 | 5 | 5 | 1 | 1 | 1 |
8 | Ans7 | 12/2 | 2 | 1 | 3 | 3 | 4 | 4 | 4 |
9 | Ans8 | 12/1 | 5 | 1 | 1 | 5 | 4 | 1 | 1 |
10 | Ans9 | 12/1 | 5 | 1 | 4 | 1 | 4 | 2 | 3 |
11 | Ans10 | 12/2 | 2 | 4 | 2 | 4 | 1 | 1 | 3 |
【考察】
まず、上記の形のデータはピボットテーブルで集計するには多少扱いにくい面があります。実際ピボットテーブルを
作成してみるとわかりますが、集計データとして「質問1」単独、「質問2」単独の場合はそれなりに見やすい
ピボットテーブルが作成できますが、複数のフィールドを集計した場合、それぞれの質問がクロス集計されてしまい、
かえって見にくい集計表になってしまいます。
そこで、アンケート結果を質問の種類ごとに1レコードとなるように展開したものをピボットテーブルで集計するよう
にします。
【手順】
上記の表をもとに手順を例示します。
質問の種類ごとのレコードへ展開するための一手法として、アンケート結果の一覧表に名前を作成します。
- A1:I11を範囲選択
- メニュー[挿入(I)]>[名前(N)]>[作成(C)]
- 「名前の作成」ダイアログで[上端行(T)]、[左端列(L)]へチェックして[OK]
続いて、アンケート結果を展開します。
- Shift+F11で展開用の新規シート挿入
- A1へ「no」 B1へ「質問項目」 C1へ「回収日」 D1へ「回答」
- A2:D2へ下記の数式を入力
| A | B | C | D |
1 | no | 質問項目 | 回収日 | 回答 |
2 |
="Ans"&ROUNDUP(ROW(A1)/7,0) |
="質問"&ROW(A1)-TRUNC((ROW()-2)/7)*7 |
=INDIRECT(A2) INDIRECT($C$1) |
=INDIRECT(A2) INDIRECT(B2) |
- A2:D2の数式を必要行数フィルドラッグ。
- A3から最終データまでを選択してCtrl+C(コピー)
- 選択範囲をそのままで、メニュー[編集(E)]>[形式を選択して貼り付け(S)]
「形式を選択して貼り付け」ダイアログボックスで[値(V)]にチェックして[OK]
(9.の手順は数式を2行目だけ残して値固定化して、再計算負担を軽減するため)
以上でアンケート結果を展開したレコードが作成できました。
| A | B | C | D |
1 | no | 質問項目 | 回収日 | 回答 |
2 | Ans1 | 質問1 | 12/5 | 1 |
3 | Ans1 | 質問2 | 12/5 | 2 |
4 | Ans1 | 質問3 | 12/5 | 3 |
5 | Ans1 | 質問4 | 12/5 | 2 |
6 | Ans1 | 質問5 | 12/5 | 2 |
7 | Ans1 | 質問6 | 12/5 | 5 |
8 | Ans1 | 質問7 | 12/5 | 3 |
9 | Ans2 | 質問1 | 12/2 | 4 |
10 | Ans2 | 質問2 | 12/2 | 5 |
11 | Ans2 | 質問3 | 12/2 | 5 |
12 | Ans2 | 質問4 | 12/2 | 3 |
13 | Ans2 | 質問5 | 12/2 | 1 |
14 | Ans2 | 質問6 | 12/2 | 3 |
15 | Ans2 | 質問7 | 12/2 | 3 |
16 | Ans3 | 質問1 | 12/2 | 1 |
17 | ・ | ・ | ・ | ・ |
18 | ・ | ・ | ・ | ・ |
続いて、ピボットテーブルの作成。
- [データ(D)]>[ピボットテーブルとピボットグラフレポート(P)]
- ウィザードでデータ範囲、ピボットテーブル作成範囲を設定し、「完了」
- 「回収日」アイテムを行フィールド、「質問項目」「回答」アイテムを列フィールド、
さらに、「回答」アイテムをデータフィールドへドラッグアンドドロップ。
以上で下記のようなピボットテーブルが作成できます。
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P |
1 | | | | | | | | | | | | | | | | |
2 | | | | | | | | | | | | | | | | |
3 | 合計 / 回答 | 質問項目 | 回答 | | | | | | | | | | | | | |
4 | 質問1 | 質問1 データの個数 | 質問2 | 質問2 データの個数 | 質問3 |
5 | 回収日 | 1 | 2 | 3 | 4 | 5 | 1 | 2 | 3 | 4 | 5 | 1 | … | … |
6 | 12/1 | | | | | 2 | 2 | 2 | | | | | 2 | 1 | … | … |
7 | 12/2 | 1 | 2 | | 1 | | 4 | 1 | | | 2 | 1 | 4 | | … | … |
8 | 12/3 | | | 1 | | | 1 | | | 1 | | | 1 | 1 | … | … |
9 | 12/4 | | 1 | | | 1 | 2 | | 1 | | | 1 | 2 | | … | … |
10 | 12/5 | 1 | | | | | 1 | | 1 | | | | 1 | | … | … |
11 | 総計 | 2 | 3 | 1 | 1 | 3 | 10 | 3 | 2 | 1 | 2 | 2 | 10 | 2 | … | … |
(上記は作成したピボットテーブルの一部)