EXCELクロス集計（双方向が複数選択項目）

質問

（先日質問を出したのですが回答が付かないので改めて）

アンケート集計の結果をEXCELに落とし込んであります。
例：
質問A：好きな食べ物は？（次から選択）⇒ケーキ、アイスクリーム、チョコレート
質問B：好きな観光地は？（次から選択）⇒京都、鎌倉、上高地

回答は複数選択可能です。

回答者１：ケーキ／京都、鎌倉
回答者２：アイスクリーム、チョコレート／上高地
回答者３：鎌倉
・・・
回答者n：チョコレート／京都、上高地

この様なデータ結果に対して、
「ケーキ」選択者の嗜好する観光地は？
「アイスクリーム」選択者の嗜好する観光地は？
「チョコレート」選択者の嗜好する観光地は？
という集計を行いたいです。

ピボットテーブルでは集計が「１対多」の場合は可能ですが、このように「多対多」の場合はどのようにしたら効率よくできるでしょうか？

回答No.4

keithin · ベストアンサー

＞関数を駆使して自在に結果を出すのは難しい

あなたがご自分の手を動かすのをメンドクサがらないなら、実際にやってることは

ケーキの京都：
=CORREL($B2:$B8,E2:E8)
と記入して右にコピー

アイスの京都：
=CORREL($C2:$C8,E2:E8)
と記入して右にコピー

チョコの京都：
=CORREL($D2:$D8,E2:E8)
と記入して右にコピー

のようにするだけです。

実際には「たくさんのデータ処理をしたい」というお話だったようですし、こちらも説明のためにこれだけ数式をいちいち説明するのは大変なので、一括で計算できるよう余計な関数を組み合わせました。ムズカシく感じたなら、説明が悪かったですね。ごめんなさい。

お礼

補足のご説明ありがとうございます。難しく感じたのはOFFSETやMATCH等の関数に慣れていないという私のレベルが原因ですが、より便利な方法を教えていただきまして感謝しております。

回答No.3

keithin

添付図：
好きな欄に１を、１でない欄にはゼロを記入する

Ｂ１２：
=CORREL(OFFSET($A$2:$A$8,0,MATCH($A12,$B$1:$G$1,0)),OFFSET($A$2:$A$8,0,MATCH(B$11,$B$1:$G$1,0)))
右にコピー、下にコピー。

解説：
相関係数（CORREL）は、２つのデータの傾向が「似ている」か「逆である」かを計算します。
数値が１に最も近い「チョコ－上高地」の組み合わせが、１とゼロの並びが一番よく似ている（共通している＝チョコが好きな人は上高地も好き）という事を示します

結果が－１に最も近い「ケーキ－上高地」の組み合わせが、１とゼロの並びが最も逆になっている（反対の傾向にある＝ケーキが好きな人は上高地が嫌い）という事を示します。

結果が１にも－１にも近くない組み合わせは、好きとも嫌いとも言えない（相関関係が認められない)ことを示します。

お礼

ありがとうございます！
Correl関数、素晴らしいですね。使えそうです。
ところで、中でOFFSETとMATCHを使っており、これを理解するのに時間が掛かりました。
言葉で整理しますと、ケーキ×京都の場合、
CORREL関数の引数として、
配列１：元データからケーキ列全体データを取得する
配列２：元データから京都列全体データを取得する
を行っているのですね。

このように関数を駆使して自在に結果を出すのは難しいですね・・・

回答No.2

MackyNo1

どのような方法を利用するにしろ、最終的にどのようなリストを作成したいのかを説明しないと具体的な回答は得られないと思います。

そもそも、ピボットテーブルを含めて、二次元のリストではすべての項目を同時には分析できないので、３次元目の項目はページフィールドに配置するなどして、そこから分析項目を絞り込んだリストを作成することになります。

まず、基本としてお尋ねしますが、現在Excel上にどのような集計データがあるのでしょうか？

＞回答者１：ケーキ／京都、鎌倉
＞回答者２：アイスクリーム、チョコレート／上高地
＞回答者３：鎌倉

上記の例示は１つのセルにデータが入力されているのでしょうか？
ピボットテーブルや関数を使用する場合は、それぞれのデータを質問系列ごとにあるいは回答項目ごとに列を作成して入力する必要があります。

＞この様なデータ結果に対して、
「ケーキ」選択者の嗜好する観光地は？
「アイスクリーム」選択者の嗜好する観光地は？
「チョコレート」選択者の嗜好する観光地は？
という集計を行いたいです。

この部分だけなら観光地と嗜好品のクロス集計を基本とした分析を行うことになります。
これ以外の集計項目があるなら、ピボットテーブルを使用する場合は、これらをページフィールドに配置するのが最も簡便な対応です。

質問には「集計が「１対多」の場合は可能ですが」とピボットテーブルで解析できるような説明がされていますが、現在のデータベースで本当にピボットテーブルが作成できているのでしょうか？

＃最終的にどのような集計リストが作成したいのか不明なので、具体的に回答しにくいのですが、ピボットテーブルや関数を使用して集計リストを作成する場合は、それに対応した元データの形式が必要になりますので（おそらく現在のデータベースでは集計できないので）、改善策を含めた具体的な回答が得たい場合は、どのような解析をしたいのか最終目的を整理して（解析の切り口を明示して）補足質問するようにしてください。

お礼

ありがとうございます！
現在考えついている集計結果は、先の回答No.1で回答いただいた添付画像の下の表の形です。元データも上の表のように、既に１つのSheetに落としています。
最後に各観光地の比率を％で表示すれば傾向が表現できるのではと思っています。
ケーキ好物の人が嗜好する観光地：京都60％、鎌倉20％、上高地10％
というかたちです。ひとまず現在の実現可能です。

最終目的は、現在のデータを用いて、2つの項目の相関関係を効果的に表現することです。表からグラフ作成も想定しています。効果的、つまり資料を見た人がより直感的にわかるような表現方法が他にあればそれを採用したいと思っております。

なお、「１対多」のケースは、男・女、年齢区分（10代、20代、30代…）のように一つのみ選択される項目と複数選択可能な項目をクロス集計することです。このようなケースではピボットテーブル作成の1回操作で期待通りの結果が得られております。

ページフィールドの機能、調べてみました。今のところはしなくてもよさそうですが勉強になりました。ありがとうございます。

回答No.1

nishi6

ピボットテーブルは便利なんですが、実務では適用できるケースは少ないように思います。更新が必要だったりして気を付ける点もあります。

ピボットテーブルが使えそうもない場合は、大体Sumproduct関数を使っています。うまく作れば更新不要で大体のことはできます。（当方、Excel2010です）

この質問の場合、添付図の上のような集計表を想定してみました。算式を簡単にするためにセル範囲名を付けます。

セルB1:G8を選択して、数式タブ＞選択範囲から作成＞上端行＞Ok
これで、データの縦列に表題の範囲名が付きます。

添付図の下で、セルB12に
=SUMPRODUCT((INDIRECT($A12)=1)*(INDIRECT(B$11)=1))

とします。後は上下にコピーします。添付図の下では表頭、表側が算式に組み込まれています。
実際、項目数はこれよりも多いと思いますが、範囲名の設定などは簡単でしょう。

また、算式を工夫すれば、複数選択している人など、大体集計できます。算式を工夫してみてください。

お礼

ありがとうございます。

Sumproduct、INDIRECT関数、「セル範囲名」も勉強になりました。今までピボットテーブルばかりに頼ってきましたが中でどのように計算しているかは意識してなかったですが、Sumproductを理解することで仕組みが分かってきました。

ところでピボットテーブルで、ケーキ、アイスリーム、チョコレートで１行ずつ観光地数を集計して、切り貼りしても同じ表ができることが分かりました。

引き続き勉強していきます。