人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

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

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

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



●質問者: kon39392
●カテゴリ:コンピュータ
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

質問者から

(表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のとき
:


1 ● きゃづみぃ
●50ポイント

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

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

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

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


kon39392さんのコメント
すみません。 表はイメージをお伝えすることを目的に記載したため、 数値は適当なものです。(表も実際は項目数やデータ量が膨大なものです) 記載するからにはきちんとした(合計値が合うような)ものを 記載するべきでしたね。

きゃづみぃさんのコメント
http://hamachan.info/excel/piboto.html <img src="http://hamachan.info/excel/piboto6.gif"> この状態までやります。 次にピボットテーブルのフィールドリストで 質問1を選択してから行範囲が表示されていることを確認して「追加」ボタンを押下します。 で 業種を 列のフィールドにドラッグして 区分を データアイテムをドラッグと書いてある場所にドラッグします。 で、フィールドの設定で、合計かデータの個数などを選択すればいいでしょう。

kon39392さんのコメント
ピボットテーブルの使い方は大変参考になりました。 ただ、求められる結果が少し違うような気がします。 このデータ構造の場合は、ピボットでは実現できないのかもしれません。 ありがとうございました。

2 ● gong1971
●50ポイント ベストアンサー

提示された集計前の生データから、ご希望のピボットテーブルは作成出来ないように思います。ご希望のピボットテーブルを作成するのであれば、生データを下記(表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
: ※以下同様に


kon39392さんのコメント
ありがとうございます。 試してみまして、実際に希望する表ができることを確認しました。 大変参考になりました。 ピボットテーブルを使用するには、それを想定したデータ構造が必要なのですね。 要は現場のユーザに今後は自分でやってほしいために、Excelレベルでできる方法を模索していたのですが、countif関数あたりが確かに適しているかもしれません。 参考になりました。

kon39392さんのコメント
よく考えるとcountifは複数条件(業種='1' && 質問1='2')での抽出ができないことを考えると、フィルタ機能しか選択肢はないのかもですね。 当初の依頼内容が「現在フィルタ機能でカウントをとっているんだけど、時間がかかりすぎるから一発で出す方法を教えて」とのことだったのでした。 とすると、最初っからご提案頂いたようなデータ構造でデータを入力してもらうか、データ構造の変換だけこちらでプログラムでやってやるか。のどちらかになりますね。 Excelしか使えない人にとっては以外と選択肢が少ないものですね。困りました。 ありがとうございました。

gong1971さんのコメント
すみません、よくよく調べてみたら今回の質問の場合、 関数で集計するならSUMPRODUCT関数が良さそうですね。 下記ページの「(2) countifで複数条件」が参考になるかと思います。 http://www.excel.studio-kazu.jp/mag2/backnumber/mm20040824.html 他にも上記ページの【sumproduct以外の方法】にあるように、 文字列連結したセルを対象にCOUNTIFを使ったり、 配列数式、DCOUNT関数なんかもありますね。 ただ、ピボットテーブルなら慣れれば色々な集計が簡単に 出来るので、元のデータ構造を変えてピボットテーブルを 利用した方が良さそうかなと思います。 以上、ご参考までに。
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ