知っているだけで頼りにされるかも？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の作業を繰り返して、各問ごとに回答をグラフ化してください。
これでグラフ化の完了です。