エクセルの関数でアンケートのクロス集計をしたいです

質問

以下、表(1)のような表があるとき、表(2)のように算出できる関数を教えてください。
※表(1)、(2)の画像を添付しました。

【表(1)】
A列　→回答者ID
B～F列　→回答者の属性（性別、部署等を数値化）
J～R列以降　→I1行の質問番号ごとに回答した選択肢番号

【表(2)】
属性ごとの回答を集計したもの。
（属1-1の場合、B2～G2に選択肢番号を設定しています）

以上の場合で、例えば、
表(1)の属1（B列）で1を選び、かつ、質問1（J列）で1を選んだ回答者の数について、
表(2)の表に入れる式（この場合、表(2)の属1-1）が知りたいです。
実際(2)は、シートを分けて作成したいと考えています。

また、こちらの都合で、今回、ピボットテーブルやマクロ、VBAでなく、
関数で作成する必要があり困っています。面倒なことになり大変恐縮ですが、
ぜひお知恵を拝借願えればありがたいです。

よろしくお願いいたします。

回答No.1

web2525 · ベストアンサー

属性1-1の表計算式（属性番号はC1セルにあるものとして）
=SUMPRODUCT((Sheet1!$B$2:$B$11=$C$1)*(OFFSET(Sheet1!$I$2,0,$A3,10)=B$2))

=SUMPRODUCT((属性範囲=$C$1)*(回答範囲=B$2))

Sheet1の属性範囲と回答範囲に名前を定義するとわかりやすくなるとおもう

属性1-2以降の表に関しては属性範囲と属性番号が異なるので
(Sheet1!$B$2:$B$11=$C$1)
↑
この部分の計算式の変更が必要

お礼

教えていただいたとおりにやってみました。
自分なりに落とし込むのに時間がかかってしまいましたが、
ようやく何とかできました。ありがとうございました！

回答No.2

MackyNo1

データベースが例示のレイアウトで（Sheet1の場合）、表示したいシートのB1セルに属性番号、C1セルに質問番号を入力してあるなら、以下の式をB3セルに入力して右方向および下方向にオートフィルしてください。

=SUMPRODUCT((INDEX(Sheet1!$B$2:$H$30,,$B$1)=$C$1)*(INDEX(Sheet1!$J$2:$N$30,,$A3)=B$2))

ただし、この数式は配列数式ですので、上記の数式は１枚のシートだけに入力して、B1セルとC1セルの値を順次変更して、そのデータ範囲を、別シートに形式を選択して貼り付けで「値」貼り付けするとよいと思います。

お礼

教えていただいたとおりにやってみました。
自分なりに落とし込むのに時間がかかってしまいましたが、
何とかできました。ありがとうございました！