Excelで行うアンケート入力とアンケート集計

こんにちは。
エクセルごはんです。
今回は、簡単なアンケート集計を、SUMPRODUCT関数を用いて行っていこうと思います。
 
アンケートとしては、『性別』と『商品満足度』のみの簡単なアンケートを例に行います。実際には、『性別』の他に『年代』や『店舗名』などがあるかと思いますが、項目が増えても基本的に行うことは同じだと思うので、説明の分かりやすさの観点からアンケート集計の項目を『性別』と『商品満足度』の２点に絞って行います。
 
早速、アンケート集計表作りに入りたいところなのですが、アンケート集計の前に、アンケート入力の作業がありますよね。
まずは、アンケート入力表を作りましょう。
 
アンケート入力では、当てはまる項目に1を入れていくやり方で行います。
例えば、項目に『性別』として『男性』と『女性』があり、『商品満足度』に『満足』と『普通』と『不満』があるとして、『男性』の『普通』なら、『男性』に1を入力して、『女性』は空欄にして、『満足』を空欄にして、『普通』に１を入れて、『不満』を空欄にします。

ここで、アンケート集計の入力で役立つ情報！
・TABキーで右に移動する
・SHIFT＋TABキーで左に移動する
・ENTERキーで改行する
こうすることで、ENTERキーで改行した時に、表の最初の項目に移動します。
また、
・ALT+↓でドロップダウンメニューを開きます。
店舗名を選択する際に、マウスを使わずにキーボードだけで操作したい時に便利だと思います。
 
さて、ではアンケート入力をしていきましょう。
『性別』の『男性』と『女性』の項目と『商品満足度』の『満足』と『普通』と『不満』の項目を並べます。
入力する値は適当に作ってしまって大丈夫です。
アンケート集計で使うため、１２行程度、『性別』の項目と『商品満足度』の項目を1で埋めていきましょう。
注意点としては、『性別』で『男性』を１で埋めたら、『女性』は空欄にします。
また、『商品満足度』で『満足』を１で埋めたら、『普通』と『不満』は空欄にします。
 
アンケート入力表が完成したら、集計表を作りましょう。
集計表は、今回は以下のように作ります。

集計表の『男性』の『満足』にあたるセルに数式を入力してあげて、あとはオートフィルでコピーすれば完成です。
 
入れる数式は、
セルB３からセルC３に『男性』と『女性』という項目名が入っているとして、
セルB４からセルC１５に『性別』に該当する各データ（１と空欄）が入っているとして、
セルD３からセルF３に『満足』と『普通』と『不満』の項目名が入っているとして、
セルD４からセルF１５に『商品満足度』に該当する各データ（１と空欄）が入っているとして、
セルB１８は集計表の『男性』がある場所として
セルC１７は集計表の『満足』がある場所として、
次のようになります。
 
=SUMPRODUCT(INDEX($B$4:$C$15,0,MATCH($B18,$B$3:$C$3,0)),INDEX($D$4:$F$15,0,MATCH(C$17,$D$3:$F$3,0)))
 
次のように表すこともできます。
 
=SUMPRODUCT(INDEX(性別に該当する各データ,0,MATCH(集計表の男性,アンケート入力表の男性と女性の項目名,0)),INDEX(商品満足度に該当する各データ,0,MATCH(集計表の満足,アンケート入力表の満足と普通と不満,0)))
 
少し仕組みの説明をします。
INDEX関数は次のような値を指定する関数です。
INDEX(配列,行番号,列番号）
INDEX関数の行番号に０を入れることで、行方向を全部拾えるようにしています。
 
MATCH関数は次のような値を指定する関数です。
MATCH(検査値,検査範囲,照合の種類)
MATCH関数の照合の種類に０を入れることで、検査範囲内で完全一致する検査値を見つけ出します。
 
分かりにくい場合は、MATCH関数がないとどうなるかを考えてみると良いと思います。
集計表の『男性』の集計は、
=SUMPRODUCT(INDEX($B$4:$C$15,0,1))
と書けます。
INDEX関数の列番号にMATCH関数を入れているところを、そのまま『男性』の列の1と列指定しています。
 
集計表の『男性』×『満足』の集計は、
=SUMPRODUCT(INDEX($B$4:$C$15,0,1),INDEX($D$4:$F$15,0,1))
と書けます。
最初のINDEX関数の列番号にMATCH関数を入れているところを、そのまま『男性』の列の1と列指定しています。
２番目のINDEX関数の列番号にMATCH関数を入れているところを、そのまま『満足』の列の1と列指定しています。
 
INDEX関数に入れる列番号を数字で指定していくと、集計表の全ての集計に列を指定していかないといけないため、MATCH関数を使用して指定しています。
INDEX関数とMATCH関数の組合せは、よくVLOOKUP関数やHLOOKUP関数の強化版として紹介されていますね。
 
最後にSUMPRODUCT関数で合計しています。
 
今回は、以上となります。
おつかれさまでした。
簡単なアンケート集計を行いました。
説明があっさりで分かりにくかったかもしれませんが、自分で実際に手を動かしてみると感覚的に仕組みが分かってくるのかなと思います。
私もこんな感じで動くよねという理解で行っています。
それでは。