すぐに応用できる  「アンケート調査結果の集計」 Home

Close

匠への技
 「匠への技」第3弾。アンケート調査結果の集計です。
個人経営でも企業でも市場調査・意識調査には欠かすことのできないアンケート。
調査しても思うように集計できない現実があります。
関数の組み合わせでアンケート調査結果の集計手法をお届けします。

 基本画面とアウトライン
 タイトル「アンケート結果の集計」です。
想定したシナリオは、設問が3題で5段階評価です。評価は、1:最高 2:良好 3:普通 4:悪化 5:最悪です。
回収したアンケートの入力は設問1がA列、設問2がB列、設問3がC列となり各々のセル列にアンケートに記入された評価を入力します。1アンケート1行が基本です。

<画面デザイン>


 E,F,G,H列に仕組んであるのがアンケートの集計と評価です。
今回の目玉は、MODE関数を使った上位3傑の求め方となります。


     ※後続の章「シナリオに基づく関数の説明」への戻り先です。

        シナリオ1   シナリオ2   シナリオ3


 使用する関数の種類    この色は新たに用いた関数です。
  1. IF(論理式,真の条件,偽の条件)  
  2. COUNTA(値1,値2,...)  
  3. COUNTIF(範囲,検索条件)  
  4. MODE(数値1,数値2,...)
・・・・・3回目登場
・・・・・2回目登場
・・・・・2回目登場
・・・・・初回       (配列数式)

今回も回答上位3傑のうち2位と3位を求める書式において配列数式を使っています。VOL-2でも説明していますがもう一度おさらいします。

配列数式とは?
1つまたは複数の値セットに基づいて複数の計算を実行し、1つまたは複数の結果を返す数式。配列式は中括弧{}で囲まれ、Ctrl + Shift キーを押しながら Enter キー押下により配列数式が定義されます。


 表作成のシナリオ
  1. 回収したアンケートを入力
  2. 設問毎、評価別の件数をカウントする併せて有効回答数と無効回答数も求める
  3. 設問毎の評価から上位3傑を求める


 シナリオに基づく関数解説
■ シナリオ 「1」                    》 基本画面とアウトラインへJUMP

@回収したアンケートを設問毎評価値を入力する。
 アンケート入力は、設問1がA列、設問2がB列、設問3がC列で定義しており、2行目からがアンケートの設問毎評価を入力します。アンケート1枚につき1行です。


■ シナリオ 「2」                    》 基本画面とアウトラインへJUMP<
A設問毎、評価別の件数をカウントする。そして、設問単位の有効回答数と無効回答数を求める。
 まず、最初に設問毎、評価別の集計です。セルF4の関数式を見てみます。
  =COUNTIF($A$2:$A$26,E4)  このCOUNTIF関数は、範囲セルA2〜A26(設問1)の範囲からE4(評価:1)の条件と合致する件数を求める関数式です。入力後にオートフィル機能でセルF8まで書式を複写します。$記号を覚えていますか? 
絶対セルです。判定する範囲 セルA2:A26 が関数書式を複写すると列行ともに変化し正確な値を求めることができません。絶対セル $ で定義し変化しないようにします。この関数式で変化するセルは E4 のみです。下方向に複写するのでE5,E6,E7,E8と変化します。

B次に有効回答数を求めます。セルF9の関数式です。
  =COUNTA(A2:A26)  COUNTA関数は、指定範囲の無記入の行を除く件数をカウントします。セルH9までフィルドラッグします。

※無回答は0入力ではなく無記入とする必要があります。

C次に無効回答を求めます。セルF10の関数式です。
  =COUNTIF(A2:A26,"")  COUTIF関数です。設問1の範囲A2:A26から無記入の行の条件 "" に合致する件数を求めます。セルH10までフィルドラッグします。


■ シナリオ 「3」                   》 基本画面とアウトラインへJUMP
D設問毎の評価から上位3傑を求める。
 設問毎の5段階評価から上位3傑を求めます。
まず、1位を求める関数式です。セルF13の関数式を見ましょう。
  =MODE(A2:A26)  初めて登場したMODE関数です。
MODE関数は、配列またはセル範囲 A2:A26 として指定されたデータ範囲から最も頻繁に出現する値 (最頻値) を返します。データの全体的な傾向を知るための統計的な手段として利用できます。
セルH13までフィルドラッグします。

E次に2位です。  1位の評価値が拾われないように工夫する必要があります。
2位を求めるセルF14の関数式です。
  {=MODE(IF(A2:A26=F13,"",A2:A26))}  前回登場した { } って???  その通りです。配列数式の宣言をしています。第3位も同様に配列数式で定義します。MODE関数IF関数の組み合わせです。MODE関数は、最も頻繁に出現する値を返すことを説明しました。
がしかし、セルF14は最も頻繁に出現する値の次に出現する値を求めますので、IF関数の配列数式により2番目の値を返すように定義します。論理式 A2:A26=F13 この条件が設問1の範囲で1位と同じ値の条件を判定しています。真の場合は "" 値を返し、偽の場合は A2:A26 設問1の範囲から1位を除外し次に出現する配列値が返されます。セルH14まで書式を複写します。

F最後に3位です。3位は当然1位と2位の評価が拾われないように工夫する必要があります。
3位を求めるセルF15の関数式です。
  {=MODE(IF(A2:A26=F13,"",IF(A2:A26=F14,"",A2:A26)))}  この関数式も配列数式の宣言を必要とします。
IF関数のネストです。最初に1位の値セルF13を除外する論理式 A2:A26=F13  により真の場合は値を "" を返し、偽の場合ネストIFで2位のセルF14を除外する論理式 IF(A2:A26=F14 と定義します。この論理式が真の場合は "" を返し、偽の場合は A2:A26 1位と2位に続く3番目に出現する値が返されます。この書式をセルH15までフィルドラッグします。


これである程度のアンケート分析が可能になりました。
このアンケートで注意すべき点は上位3傑の中に無回答が入り込んだ場合です。もし可能性的にある場合は、関数式の組み合わせで無回答を除外する式を定義してください。
また、アンケートの範囲最終行を26としていますが可変にする場合は「匠への技」VOL-2で説明したINDIRECT関数COUNTA関数のコンビネーションを参考に定義してください。

アンケート評価結果をグラフにしますと傾向分析としてますます格が上がることでしょう。

以上です。


 もひとつ おまけ
アンケートデータの入力は、メニューバー 「データ」 → 「フォーム」 の機能を使うと便利です。
起動方法は次の通りです。
 @セルA1をアクティブにします。
 Aメニューバー 「データ」 → 「フォーム」を選択します。

    

  
※データ入力時の留意として項目間の移動は「TAB」キーを押下します。


○「匠への技」VOL-3で作成したExcelブックのダウンロード

  演習できるExcelブックを用意しています。下図のダウンロードボタンをクリックして下さい。

     Excel Book Name: takumivol-003.xls  Download file: takumivol-003.lhz

    




Close