知っているだけで頼りにされるかも？Excelの関数でアンケート結果を統計グラフ化しよう
アンケート結果を集計する際、どのように行っているでしょうか。
例えば
インターネット
上でアンケートを作成することができる
Google
フォーム
を使用したアンケートであれば、集計も自動で行ってくれるため、特に作業する必要はありません。
しかし紙を使用してアンケートをとった場合は、表計算
ツール
を使用してデータをまとめなければなりません。
今回は、Excelの関数を使用してアンケート結果を統計グラフにまとめる方法を、順を追って解説します。
解説を読むだけでは手順が多く難しく見えますが、実際にやってみるとそれほど難しい作業ではありません。
画像も使用して細かく解説していますので、まずは本記事の手順どおりに、作業を進めてみることをオススメします。
なお、本記事内で想定しているアンケートは、自由記述式を除く選択式のアンケートです。
自由記述式の場合には使用できませんので、注意してください。
Excelの関数を使用してアンケート結果を統計グラフにまとめる
Excelの関数を使用してアンケート結果の集計を行う際は、大きく3つの段階を踏みます。
各段階ごとに詳しく手順をご紹介していますので、順序に従って作業を進めてください。
1.アンケート結果をExcelデータにする
step1.
まず、アンケート結果をExcelデータとしてまとめます。
この後に行う作業をより効率化するためにも、上画像のような形式でまとめてください。
上画像の1行目の項目は、それぞれ設問番号を表しています。
2行目以下は、設問に対する回答番号を記入しています。
なお、問2・問3については複数回答可の設問と想定しているため、回答番号を半角スペースを挟んで複数記入しています。
1列目の項目は、回答枚数(上画像では22枚の回答用紙を回収できた)を表しています。
step2.
データをまとめたら、関数で処理しやすいように整理します。
使用する関数は「COUNTIF関数」です。
複数回答可の設問について、数字が正しく認識されるように回答番号の先頭にアンダーバーを追記します。
まず、回答番号が記入されているセルをすべて選択状態にします。
画面上部に表示されているメニューのうち「編集」を選択し、表示されたプルダウンメニューから「置換」をクリックしてください。
step3.
「置換」のウィンドウが表示されます。
「検索する文字列」に半角スペース「 」を「置換後の文字列」に半角アンダーバー「_」を入力し「すべて置換」をクリックします。
step4.
すると「置換しました」というメッセージボックスが表示されますので「OK」をクリックします。
これで複数回答可の設問について、回答番号の先頭にアンダーバーの追記が完了です。
なお、引き続き表示されている置換ウィンドウについては「閉じる」をクリックして閉じてください。
step5.
続いて、各設問に対する回答を記入している列のすぐ左列に、空白列を挿入します。
設問数がそれほど多くない場合は手動で行っても構いませんが、設問数が多い場合は、以下のferret内の記事でご紹介している「7.1行ごとに空白を挿入する」を参照して空白列を挿入してください。
参照：Excelを使用するなら知っておきたい！便利な小技8選｜ferret
step6.
挿入した空白列のセルすべてに、半角のアンダーバーを記入します。
列の先頭に半角アンダーバーを入力すれば、オートフィルで最終行まで一度に入力することができます。
1列分の記入が終了したら、コピー＆ペーストでほかの空白列すべてに半角アンダーバーを入力してください。
step7.
上画像のように、空白列すべてに半角アンダーバーが記入され、複数回答可の設問について回答番号の先頭にアンダーバーが追記されれば、Excelデータの準備は完了です。
2.結果を関数で集計する
step1.
回答の集計をはじめる前に、Excelデータを作成したファイルと同じファイル上に、アンケート結果を処理するための数値処理シートを作成します。
シート名は任意で構いません。ここでは「数値処理シート」と表記します。
step2.
作成したExcelデータから、上画像のように項目をコピーして数値処理シートにペーストします。
step3.
数値処理シートに項目をペーストしたら、関数を入力します。
まず、上画像のように最初の設問の回答セル(B2セル)をクリックして、半角イコールを入力してください。
するとセルを参照するための青い枠がシート上に出現します。
step4.
シート上に青枠の参照セルが出現した状態で、Excelデータのシートに移動し、最初の設問の回答セルのすぐ左のセル(上画像ではC2セル)をクリックします。
そのまま半角アンド入力して最初の設問の回答セル(上画像ではD2セル)をクリックしてください。
step5.
数値処理シートを見ると、step3でクリックしたB2セルに、半角アンダーバーと最初の設問の回答が統合して表示されていることが分かります。
このセルに適用されている関数は「=アンケート結果!C2&アンケート結果!D2」です。
step6.
そのままオートフィルで最終行まで入力を行ってください。
この作業を、ほかの設問でも同じように繰り返します。
ただし列をまたいでオートフィルを行うと、正しく回答結果が表示されない場合がありますので、必ず1列ずつ作業を行うようにしてください。
step7.
回答の集計を行います。
まず、数値処理シートの最終行から下に、アンケート内の選択肢の数を入力します。
例えば、設問1で呈示している選択肢が5つだった場合は、ここに5つの項目を追加します。
なお、追加する項目の数は、設問の中で最も多い選択肢数に合わせてください。
(上画像では設問の中で8つの選択肢が最大である、と仮定されています。)
step8.
次に、設問1に対して選択肢1が何回選ばれたのかを集計する関数を入力します。
上画像の場合は設問1は、セルのB2〜B270までですので、以下の関数になります。
なおこの時、オートフィルで選択しても範囲がずれないようにするために、絶対参照を使用するようにしてください。
=COUNTIF(B$2:B$270,"_1”)
絶対参照については、以下のferret内の記事にて解説しています。
参照：今さら聞けない！Excelの知らなきゃ恥ずかしい基本の関数8種類｜ferret
step9.
絶対参照を使用した関数を入力したら、横に向かってオートフィルを行い、選択肢1についての回答数を集計します。
step10.
同様に、選択肢2が何回選ばれたのかを集計する関数を入力します。
選択肢2の場合は、以下の関数になります。
=COUNTIF(B$2:B$270,"_2”)
step8〜step10を繰り返して、最後の選択肢(上画像では選択肢8)まで集計を行ってください。
step11.
各設問に対する回答数を集計します。
この時使用する関数は「SUM」です。
設問1の最終列に以下の関数を入力してください。
=SUM(B271:B278)
同じように設問の数(上画像では4つ)、集計を行います。
step12.
無回答数の集計も行います。
回答数の下に無回答数の項目を作成し、以下の関数を入力してください。
ここではアンケートの回収枚数は269枚ですので、269から回答数を減算することで無回答数の集計結果を出します。
=269-B279
同じように設問の数(上画像では4つ)、集計を行います。
step13.
最後に、回答率を集計します。
無回答数の下に無回答数の項目を作成し、以下の関数を入力してください。
回答数を、回収枚数で除算することで回答率を算出します。
=B279/269
同じように設問の数(上画像では4つ)、集計を行います。
3.集計結果をグラフ化する
step1.
集計結果をグラフ化します。
「2.結果を関数で集計する」で作成した数値処理シート内でグラフ化を行っても構いませんが、見やすくするために再度新しいシートを作成してください。
ここでは「統計シート」と表記します。
step2.
まず、アンケートの設問・選択肢を入力します。
step3.
回答数を記入します。
選択肢1の行に半角イコールを入力し、そのまま数値処理シートに移動して選択肢1の回答数をクリックしてください。
step4.
すると、統計シート内に選択肢1の回答数が表示されます。
step5.
オートフィルで、無回答数を含む最後の選択肢まで回答数を入力します。
step6.
回答数から割合を算出します。
統計シート内の回答数の右横に、以下の関数を入力してください。
なおここで算出する回答率は、各選択肢ごとの回答率です。「2.結果を関数で集計する」で算出した回答率とは異なりますので、注意してください。
=B2/269
この作業を、すべての設問で行います。
step7.
回答率が小数点で表示されているので、パーセンテージ表示に変換します。
回答率を算出した列を選択し、右クリックをして「セルの書式設定」を開いてください。
step8.
「セルの書式設定」ウィンドウが開きますので「表示形式」を選択して「分類」から「パーセンテージ」を選択します。
小数点以下の桁数については選択できるので、任意で設定して「OK」をクリックしてください。
step9.
回答率がすべてパーセンテージ表示に変換されると、上画像のようになります。
step10.
続いてデータをグラフ化します。
回答率を問の設問分選択し、画面上部に表示されているメニューから「グラフ」を開き「円(グラフ)」を選択してください。
step11.
これで回答率が円グラフで表示されます。
このままではグラフのラベルが表示されませんので、画面上部に表示されているグラフメニューから「選択」をクリックします。
step12.
グラフデータの「選択」ウィンドウが表示されます。
「Y軸の値：」にて問1の回答率のあるC2からC6を選択します。
=統計!$C$2:$C$6
「X/項目軸のラベル：」にて、選択肢の内容が書いてあるA2からA6を選択します。
=統計!$A$2:$A$6
両項目を入力したら「OK」をクリックしてください。
step13.
円グラフを右クリックし「データラベルの追加」をクリックします。
すると、円グラフ内にそれぞれの割合を表示してくれます。
step4.
step10〜step13の作業を繰り返して、各問ごとに回答をグラフ化してください。
これでグラフ化の完了です。
まとめ
Excelを使用して作成したグラフは、コピーしてPowerPointやWordにペーストすることができるため、資料作成の際にも非常に便利です。
本記事の手順に従って作業を行えばそれほど難しいものではありませんが、Excelを使いこなしてアンケート結果をグラフ化できる、というのは意外にも重宝される技術です。
行っている業務内容にもよりますが、知っておけばいざという時に頼りにされること間違いなし。
ぜひ実際にExcelを操作して、覚えておくようにしましょう。