Excelのピボットテーブルで以下を実現したい


下記(表1)のようにアンケートをExcelに打ち込んだものを渡され、
下記(表2)のような集計結果を出してほしいとの依頼を受けました。

本当は「Excelのピボット等で一発で集計をとる方法を教えてほしい」との
依頼だったのですが、私がピボットをあまり触ったことなかったため、
その場ではAccessのテーブルへ一度インポートし、VBAで「for文で回しながらSQLによる
カウント結果をExcelへ書き出していく」という処理を作成して
表2を作成するという方法でその場はしのぎました。

ピボットによる方法を知っておきたいと思い、試みているのですが
理想通りの結果を出すことができずにいます。
ピボットによる出し方の手順をご教示願います。

表1,2は補足へ記載します。

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2013/03/04 10:56:25
  • 終了:2013/03/04 14:33:23
id:kon39392

(表1)集計前の生データ

業種 | 区分 | 質問1 | 質問2 | 質問3・・・
12 | 7 | 1 | 4 | 3
3 | 9 | 3 | 1 | 2
1 | 2 | 4 | 3 | 3
4 | 1 | 5 | 1 | 3
14 | 8 | 4 | 4 | 6
13 | 9 | 4 | 5 | 1
5 | 6 | 2 | 2 | 4
※業種は1~15
※区分は1~10
※質問1,2,3の選択肢は1~5の5段階


(表2)集計結果

○業種=全て
選択肢 | 質問1(件) | 質問2(件) | 質問3(件) ・・・
1 | 98 | 67 | 62
2 | 81 | 34 | 98
3 | 23 | 76 | 29
4 | 12 | 89 | 34
5 | 38 | 28 | 98

○業種=1のとき
選択肢 | 質問1(件) | 質問2(件) | 質問3(件) ・・・
1 | 12 | 23 | 8
2 | 11 | 10 | 7
3 | 10 | 9 | 4
4 | 28 | 8 | 1
5 | 2 | 1 | 3

○業種=2のとき
:

○区分=全て
選択肢 | 質問1(件) | 質問2(件) | 質問3(件) ・・・
1 | 98 | 67 | 62
2 | 81 | 34 | 98
3 | 23 | 76 | 29
4 | 12 | 89 | 34
5 | 38 | 28 | 98

区分=1のとき
:

区分=2のとき
:

ベストアンサー

id:gong1971 No.2

gong1971回答回数443ベストアンサー獲得回数682013/03/04 12:47:55

ポイント50pt

提示された集計前の生データから、ご希望のピボットテーブルは作成出来ないように思います。ご希望のピボットテーブルを作成するのであれば、生データを下記(表1a)もしくは(表1b)のように作成(もしくはvba等で変更)する必要があります。

その上で、列ラベルに[質問番号]、行ラベルと値フィールドに[回答]を指定し、レポートフィールドに[業種]もしくは[区分]を指定してピボットテーブルを作成します。なお、値フィールドの集計方法はデータの個数とします。

また、元の生データから集計結果を作成するのであれば、ピボットテーブルではなく、統計関数(COUNTIFなど)か、配列数式を使用すれば作成可能です。

不明な点があればコメントにてお知らせください。(その際、Excelのバージョンも教えてください)

(表1a)
業種 | 区分 | 質問番号 | 回答
12 | 7 | 質問1 | 1
3 | 9 | 質問1 | 3
1 | 2 | 質問1 | 4
4 | 1 | 質問1 | 5
14 | 8 | 質問1 | 4
13 | 9 | 質問1 | 4
5 | 6 | 質問1 | 2
12 | 7 | 質問2 | 4
3 | 9 | 質問2 | 1
1 | 2 | 質問2 | 3
4 | 1 | 質問2 | 1
14 | 8 | 質問2 | 4
13 | 9 | 質問2 | 5
5 | 6 | 質問2 | 2
12 | 7 | 質問3 | 3
3 | 9 | 質問3 | 2
1 | 2 | 質問3 | 3
4 | 1 | 質問3 | 3
14 | 8 | 質問3 | 6
13 | 9 | 質問3 | 1
5 | 6 | 質問3 | 4

(表1b)
業種 | 区分 | 質問番号 | 回答
12 | 7 | 質問1 | 1
12 | 7 | 質問2 | 4
12 | 7 | 質問3 | 3
3 | 9 | 質問1 | 3
3 | 9 | 質問2 | 1
3 | 9 | 質問3 | 2
: ※以下同様に

他1件のコメントを見る
id:kon39392

よく考えるとcountifは複数条件(業種='1' && 質問1='2')での抽出ができないことを考えると、フィルタ機能しか選択肢はないのかもですね。

当初の依頼内容が「現在フィルタ機能でカウントをとっているんだけど、時間がかかりすぎるから一発で出す方法を教えて」とのことだったのでした。

とすると、最初っからご提案頂いたようなデータ構造でデータを入力してもらうか、データ構造の変換だけこちらでプログラムでやってやるか。のどちらかになりますね。

Excelしか使えない人にとっては以外と選択肢が少ないものですね。困りました。

ありがとうございました。

2013/03/04 14:53:19
id:gong1971

すみません、よくよく調べてみたら今回の質問の場合、
関数で集計するならSUMPRODUCT関数が良さそうですね。
下記ページの「(2) countifで複数条件」が参考になるかと思います。
http://www.excel.studio-kazu.jp/mag2/backnumber/mm20040824.html


他にも上記ページの【sumproduct以外の方法】にあるように、
文字列連結したセルを対象にCOUNTIFを使ったり、
配列数式、DCOUNT関数なんかもありますね。


ただ、ピボットテーブルなら慣れれば色々な集計が簡単に
出来るので、元のデータ構造を変えてピボットテーブルを
利用した方が良さそうかなと思います。

以上、ご参考までに。

2013/03/05 16:41:29

その他の回答(1件)

id:taknt No.1

きゃづみぃ回答回数13539ベストアンサー獲得回数11982013/03/04 11:13:20

ポイント50pt

一度 回答を書いたのですが、ちょっと違ったようでしたので 修正します。

○業種=全て
選択肢 | 質問1(件) | 質問2(件) | 質問3(件) ・・・
1 | 98 | 67 | 62

このとき
選択肢が 1で 質問1(件)の98は 何でしょうか?

(表1)集計前の生データは 記載内容が足りないのでしょうか?

他1件のコメントを見る
id:taknt

http://hamachan.info/excel/piboto.html



この状態までやります。
次にピボットテーブルのフィールドリストで 質問1を選択してから行範囲が表示されていることを確認して「追加」ボタンを押下します。
で 業種を 列のフィールドにドラッグして
区分を データアイテムをドラッグと書いてある場所にドラッグします。
で、フィールドの設定で、合計かデータの個数などを選択すればいいでしょう。

2013/03/04 13:04:52
id:kon39392

ピボットテーブルの使い方は大変参考になりました。
ただ、求められる結果が少し違うような気がします。

このデータ構造の場合は、ピボットでは実現できないのかもしれません。
ありがとうございました。

2013/03/04 14:18:25
id:gong1971 No.2

gong1971回答回数443ベストアンサー獲得回数682013/03/04 12:47:55ここでベストアンサー

ポイント50pt

提示された集計前の生データから、ご希望のピボットテーブルは作成出来ないように思います。ご希望のピボットテーブルを作成するのであれば、生データを下記(表1a)もしくは(表1b)のように作成(もしくはvba等で変更)する必要があります。

その上で、列ラベルに[質問番号]、行ラベルと値フィールドに[回答]を指定し、レポートフィールドに[業種]もしくは[区分]を指定してピボットテーブルを作成します。なお、値フィールドの集計方法はデータの個数とします。

また、元の生データから集計結果を作成するのであれば、ピボットテーブルではなく、統計関数(COUNTIFなど)か、配列数式を使用すれば作成可能です。

不明な点があればコメントにてお知らせください。(その際、Excelのバージョンも教えてください)

(表1a)
業種 | 区分 | 質問番号 | 回答
12 | 7 | 質問1 | 1
3 | 9 | 質問1 | 3
1 | 2 | 質問1 | 4
4 | 1 | 質問1 | 5
14 | 8 | 質問1 | 4
13 | 9 | 質問1 | 4
5 | 6 | 質問1 | 2
12 | 7 | 質問2 | 4
3 | 9 | 質問2 | 1
1 | 2 | 質問2 | 3
4 | 1 | 質問2 | 1
14 | 8 | 質問2 | 4
13 | 9 | 質問2 | 5
5 | 6 | 質問2 | 2
12 | 7 | 質問3 | 3
3 | 9 | 質問3 | 2
1 | 2 | 質問3 | 3
4 | 1 | 質問3 | 3
14 | 8 | 質問3 | 6
13 | 9 | 質問3 | 1
5 | 6 | 質問3 | 4

(表1b)
業種 | 区分 | 質問番号 | 回答
12 | 7 | 質問1 | 1
12 | 7 | 質問2 | 4
12 | 7 | 質問3 | 3
3 | 9 | 質問1 | 3
3 | 9 | 質問2 | 1
3 | 9 | 質問3 | 2
: ※以下同様に

他1件のコメントを見る
id:kon39392

よく考えるとcountifは複数条件(業種='1' && 質問1='2')での抽出ができないことを考えると、フィルタ機能しか選択肢はないのかもですね。

当初の依頼内容が「現在フィルタ機能でカウントをとっているんだけど、時間がかかりすぎるから一発で出す方法を教えて」とのことだったのでした。

とすると、最初っからご提案頂いたようなデータ構造でデータを入力してもらうか、データ構造の変換だけこちらでプログラムでやってやるか。のどちらかになりますね。

Excelしか使えない人にとっては以外と選択肢が少ないものですね。困りました。

ありがとうございました。

2013/03/04 14:53:19
id:gong1971

すみません、よくよく調べてみたら今回の質問の場合、
関数で集計するならSUMPRODUCT関数が良さそうですね。
下記ページの「(2) countifで複数条件」が参考になるかと思います。
http://www.excel.studio-kazu.jp/mag2/backnumber/mm20040824.html


他にも上記ページの【sumproduct以外の方法】にあるように、
文字列連結したセルを対象にCOUNTIFを使ったり、
配列数式、DCOUNT関数なんかもありますね。


ただ、ピボットテーブルなら慣れれば色々な集計が簡単に
出来るので、元のデータ構造を変えてピボットテーブルを
利用した方が良さそうかなと思います。

以上、ご参考までに。

2013/03/05 16:41:29

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません