EXCELで複数回答のアンケート2問のクロス集計をしたい

複数回答のアンケートが2問あります。
例をあげます。
問1「好きな動物はなんですか」
１．犬　２．猫　３．ハムスター　４．亀
問2「好きな食べ物はなんですか」
１．カレー　２．ラーメン　３．おにぎり　４．卵

これの回答が、各選択肢ごとにEXCELの一列に、
回答していれば1、していなければ0と集計されています。
例えば、A列には犬を好きな人は1、好きでない人は0が入っています。

この2問のクロス集計をとりたいのですが、うまくいきません。
1問ずつの単純集計をとる場合は、
ピボットテーブルの真ん中のデータのところに、
各選択肢の列をドラッグして、各肢の合計を表示させていました。

犬　　　　　　40
猫　　　　　　35
ハムスター　　20
亀　　　　　　 5
というようになっていました。

これを、クロス集計にして、
　　　　　　　カレー　　ラーメン　　おにぎり　　卵
犬　　　　　　　15　　　　　15　　　　　7　　　　3
猫　　　　　　　13　　　　　 7　　　　　10　　　 5
ハムスター 5 10 3 2
亀 4 0 0 1

のようにしたいのです。

うまいやり方を教えてください。

No.1 ベストアンサー 回答者： shkwta 回答日時： 2005/02/02 19:29

この条件ですと、８種類全部が好きな人は１６か所すべてに重複カウントされることになりますね。

この構造はピボットテーブルでは無理そうなので、SUMPRODUCTとINDIRECTを使った方法を考えてみました。
まず、Ａ列に犬、Ｂ列に猫、Ｃ列にハムスター、Ｄ列に亀、Ｅ列にカレー、Ｆ列にラーメン、Ｇ列におにぎり、Ｈ列に卵のデータが入っているとします。また、データは100件で、A2からH101の範囲に0か1のどちらかが入っているとします。クロス集計表はI1～M5に作ることにします。

(1)J1～M1に、それぞれA,B,C,Dと入力します。
(2)I2～I5に、それぞれE,F,G,Hと入力します。
(3)J2に、次の式を入力します。
=SUMPRODUCT(INDIRECT(J$1&"2:"&J$1&"101"),INDIRECT($I2&"2:"&$I2&"101"))
(4)J2の式をJ2～M5にコピーします。
これで、クロス集計表が得られます。たとえば、J2には、A列とE列が同時に1である行の数が求められています。

この方法で、INDIRECTの中では、たとえば"A2:A101","E2:E101"という文字列を生成しています。INDIRECTは、それらの文字列を範囲の実態に置き換えます。そして、SUMPRODUCTが範囲同士の積の和を算出しています。
あとは、適宜応用してください。

この回答へのお礼

ありがとうございます！
こんなややこしいのに考えていただいて、
本当に助かりました。

J$1&"2:"&J$1&"101"等の表記方法をまだ完全には理解できていないのですが、
勉強して応用させていただきたいと思います。
ありがとうございました。

お礼日時：2005/02/04 16:52