【動画】エクセルで『複数選択可』のアンケート結果の集計表とグラフを作成する方法
この動画では、エクセルで『複数選択可』のアンケート結果を集計する方法を紹介します。
以前の動画で、アンケート結果を集計する方法を紹介したんですが、『複数選択可』のアンケート結果を集計する方法について質問があったので、この動画で紹介します。
もし、全く同じ環境で試したい場合は、その動画を参考にしてエクセルファイルを用意してください。
まずは完成形の確認です。
この動画では、使用したことのあるアプリを教えてください。という質問で、
- Word
- Excel
- PowerPoint
という回答の中から複数回答できるアンケートを想定しています。
『複数選択可』のアンケート結果を集計するには、このように、回答1つに対して、1列ずつ項目を作ります。
集計シートでは、使用経験がある人と無い人の人数を自動で集計し、その割合やグラフも表示されます。
最後には、年代別に集計する方法も紹介します。
それでは早速作成していきます。
目次
情報の追加
まずは、必要な行と列を追加します。
行を追加するには、追加したい境目の下の行の行名を右クリックし、挿入をクリックすることで追加できます。
次に列を追加します。
ここでは、
- Word
- Excel
- PowerPoint
の3つを追加したいので、3列追加します。
列を複数追加するには、追加したい境目から右の列を、追加したい列数分選択します。
選択できたら、選択したセルの上で右クリックして、挿入をクリックすることで、選択した列数の列が追加されます。
次に、1行目の見出し欄のセルを結合して、範囲を広げます。
結合したいセルの範囲を選択した状態で、上のホームタブから、配置グループの、セルを結合して中央揃えをクリックします。
すると、結合されていたセルが分離されるので、再度セルを結合して中央揃えをクリックすることで、結合することができます。
次に、罫線を消します。消したい罫線の隣接するセルを選択して、上のホームタブのフォントグループから、枠なしをクリックします。
次に、2行目の見出し欄に質問内容を入力します。
入力できたら、こちらもセルを結合します。
各見出し欄に、質問を入れることもできますが、そうするとこのように情報量が多くなってしまうので、共通する文字は統合する、と覚えておくと、コンパクトな表を作成することができます。
次に3行目に回答を入力していきます。
Word
右へ情報を入力していく場合は、Tabキーを押すことでセルの入力を確定して、選択セルを右に移動することができるので、素早く入力していくことができます。
Excel
PowerPoint
次に列幅を調整します。
列幅を調整するには、調整したい列名の右の境目をダブルクリックすることで、セル内の情報量に合わせて自動で幅を調整することができます。
上手くいかない場合は何度かダブルクリックします。
保存
ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきます。
SaveのSとおぼえます。
仮の情報入力
次に、集計などの各挙動を確認するために、仮で選択肢の数字を入力していきます。
今回、集計する質問は、複数選択可の質問ですが、それぞれのアプリに対しては
- 使用経験有りか
- 使用経験無しかの
2択なので、1か2を入力していきます。
ランダムな数字を入力するには、
=RANDBETWEEN(1,2)
と入力します。
=RANDBETWEEN(①,②)
RANDBETWEEN関数は、
①つめの引数に、ランダムに返したい数字の最小値
②つめの引数に、ランダムに返したい数字の最大値
を入力することで、その範囲からランダムな数字を返すことができる関数です。
入力できたら、そのセルを選択した状態で、セルの右下の小さい四角をドラッグして、他のセルにも適用します。
RANDBETWEEN関数は、セルを編集する度にランダムな数字が返されるので、数字を確定したい場合は確定したいセルをCtrl + Cでコピーして、アプリケーションキーを押してからVキーを押して値のみ貼り付けると、数字を固定することができます。
Escキーでコピーを解除しておきます。
保存
ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきます。
集計用
次に集計用の列を追加します。
表の一番右側に列を追加したい場合は、列を挿入する必要は無く、右側にある列を使用します。
1行目の見出しを右に3列追加したいので、1つ目の見出しから右に3つ分のセルを選択した状態で、上のホームタブから、配置グループの、セルを結合して中央揃えをクリックします。
すると、結合されていたセルが分離されるので、再度セルを結合して中央揃えをクリックすることで、結合することができます。
次に、2行目の見出し欄のアプリの使用経験とその答えの3行目の見出し欄を選択した状態で、Ctrlキーを押しながらCでコピーして、Ctrlキーを押しながらVキーを押して貼り付けます。
次に答えである3行目の見出しをアプリ(集計用)という見出しに変えていきます。
編集したいセルを選択した状態で、F2キーを押して編集モードにして編集していきます。
(集計用)の文字は繰り返し入力するので、コピーします。
Shiftキーを押しながら、方向キーの左キーを押して、(集計用)の文字を選択します。
そのまま、Ctrlキーを押しながらCキーを押してコピーします。
Tabキーで右のセルに移動したら、F2キーで編集モードにして、Ctrlキーを押しながらVキーで(集計用)を貼り付けます。
Tabキーで右のセルに移動したら、PowerPointも同じ様に、F2キーで編集モードにして、Ctrlキーを押しながらVキーで(集計用)を貼り付けたら、Enterで確定します。
次に、列幅を整えたい範囲を選択して、そのどこかの列名の境目をダブルクリックすることで複数列の列幅を調整します。
次に罫線を整えます。
列や行の罫線を一度に設定した場合、結合されているセルは正しく罫線が設定されない場合があるので、その場合は個別で設定します。
保存
ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきます。
VLOOKUP
次に、入力した選択肢の数字に応じて、自動で集計表用の文字が入力されるように設定していきます。
入力した選択肢の数字に応じて、自動で集計表用の文字が入力されるようにするには、まずこのような選択肢の数字と集計用の文字の対応表を作成します。
ここでは新しいシートに対応表を作成します。
シートを作成するには、シートを作成したい1つ左側のシートをクリックして開いた状態で、シートタブの横のプラスボタンをクリックして、新しいシートを作成します。
すると開いていたシートの右側に新しいシートが作成されます。
シート名をダブルクリックして、シート名を書き換えます。この動画ではリスト(アプリ)と書き換えます。
次にリスト(アプリ)のシートにアプリの選択肢の数字と集計用の文字の対応表を作成します。
集計用の文字は後に集計表で並び替えができるように、集計用の文字の頭には数字を付けておくと便利です。
対応表が完成したら、アンケート結果のシートに戻って
=VLOOKUP(
と入力して、D4セルをクリックします。
そのまま、
,
を入力したら、リスト(アプリ)のシートをクリックして、先ほど作成した表をドラッグして選択します。
そのままF4キーを押して、ドル記号を付け、絶対参照に設定します。
そのまま
,2,0)
というように入力して、Enterで確定します。
すると、入力した選択肢の数字に応じて、自動で集計表用の文字が入力されました。
テーブルを設定している表で、関数を入力した場合は、他の行も自動で数式が作成される場合があります。
先ほど出てきた絶対参照について知りたい方は、他の動画で解説してるので、参考にしてみてください。
VLOOKUP関数は、他の表から文字列が一致する情報を、抽出することができる関数
- 1つ目の引数には、検索する文字列
- 2つ目の引数には、検索先の対応表
- 3つ目の引数には、検索先の対応表の左から何列目の情報を抽出するか
- 4つ目の引数には、検索の方式
を入力します。
=VLOOKUP([@Word],’リスト(アプリ)’!A$1:B$2,2,0)
今回の場合、
- Word列の、この数式が入力されている行のセルに入力されている文字を、
- リスト(アプリ)シートのA1セル～B2セルの表から検索し、
- その表の完全一致する文字の
- 左から2列目を表示する。
という意味になります。
※VLOOKUP関数
テーブルを設定しているセルから同じテーブル内のセルを参照すると、1つ目の引数のように、[@列名]という形式で表示されます。
これは、列名の列とその数式が入力されている行のセルを参照するという意味になります。
今回は数式が入力されている行が4行目なので、Word列の4行目のセルを参照するという意味になります。
このように、テーブルを設定している表のテーブル名や列名を指定してセルを参照する参照方法を構造化参照と言います。
次に今、作成した数式を選択した状態で、右下の小さい四角をドラッグして、他の列にも適用します。
すると、他の列の数式も作成することができました。
罫線が崩れているので再設定します。
保存
ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきます。
集計(比率)
次に、集計シートに集計表とグラフを作成していきます。
以前の動画では、ピボットテーブルとピボットグラフという機能で集計表を作成しました。
ピボットテーブルというのは、簡単に集計表を作成することができる機能で、ピボットグラフというのは、ピボットテーブルに付随して、簡単にグラフを作成することができる機能です。
ですが、今回は複数の回答を1つの表やグラフにまとめたいので、ピボットテーブルとピボットグラフは使えません。
※ピボットテーブルとピボットグラフを使って、複数の回答を1つの表やグラフにまとめる方法があったら教えてほしいです
代わりに関数を使って集計表を作成していきます。
見出し
まずは、見出し欄を作成します。
結合したいセルの範囲を選択した状態で、上のホームタブから、配置グループの、セルを結合して中央揃えをクリックして、セルを結合します。
同じ操作を繰り返したい場合は、Ctrlキーを押しながらYキーで繰り返すことができるので、素早く操作することができます。
Tabキーで選択セルを右に移動することで、素早く右に情報を入力することができます。
Ctrlキーを押しながらCキーでコピーして、Ctrlキーを押しながらVキーで貼り付けます。
デザイン
列幅を整えたい範囲を選択して、そのどこかの列名の境目をダブルクリックして、列幅を調整します。
次に見出しの背景色を変更します。
背景色を変更したいセルを選択した状態で、上のホームタブから、フォントグループの、ペンキのアイコンから変更することができます。
ここでは、水色に設定します。
次に、見出しの文字を太字にします。
太字にしたいセルを選択した状態で、Ctrlキーを押しながらBキーを押します。
すると文字を太字にすることができます。BoldのBとおぼえます。
使用者数
次に、Wordの使用経験がある人の人数を算出します。
=COUNTIF(
と入力したら、そのままアンケート結果のシートを開いて、集計用のWordの列のアンケート結果を選択します。
そのまま、
,
を入力したら、使用経験有りのセルをクリックします。
そのままF4キーで行名の前にドル記号を入れて、絶対参照に設定します。
入力できたら、Enterで確定します。
すると、Wordの使用経験がある人の人数を算出することができました。
=COUNTIF(テーブル2[Word(集計用)],’集計(比率)’!B$28)
=COUNTIF(①,②)
COUNTIF関数は、条件に当てはまるセルの数を数えることができる関数です。
※カード COUNTIF関数の使い方
1つ目の引数にカウントするセルの範囲、
2つ目の引数にカウントする条件を入力します。
今回の場合、
テーブル2[Word(集計用)]
の範囲から
B28、つまり1. 使用経験有り
と等しいセルの数を算出する
という意味になります。
1つ目の引数のように、テーブル外のセルから、テーブルを設定している表の列を選択すると、
テーブル名[列名]
という形式で表示されます。これも先ほど紹介した、構造化参照です。
表にテーブルを設定すると、その表には、テーブル名が付与されます。
テーブル名は、
- 1つ目に作ったテーブル名であればテーブル1
- 2つ目に作ったテーブル名であればテーブル2
- 3つ目に作ったテーブル名であればテーブル3
というようにテーブル名が付与されていきます。
テーブル名は変更することもできますが、今回は変更していないので、この動画ではテーブル2というテーブル名が設定されており、そのテーブル名が参照されています。
次に、ExcelとPowerPointも集計します。
今、入力したセルを選択した状態で、Ctrlキーを押しながらCでコピーします。
そのままShiftキーを押しながら、方向キーの下キーを2回押して、下2つのセルを選択します。
そのままCtrlキーを押しながら、Vキーで貼り付けます。
すると、1つ目の引数の、列名のアプリ名を変えるだけで、他のアプリも算出することができます。
F2キーで編集モードにして、WordをExcelに書き換えます。
すると、Excelを使用したことがある人の人数も算出することができました。
その下のセルは、WordをPowerPointに書き換えます。
すると、PowerPointを使用したことがある人の人数も算出することができました。
未使用者数
次に、使用経験が無い人の人数も算出します。
今、入力したセルを選択した状態で、Ctrlキーを押しながらCでコピーして、隣のセルでCtrlキーを押しながらVキーで貼り付けます。
すると、各アプリの使用経験が無い人の人数も算出することができました。
保存
ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきます。
割合
次に、各アプリの使用経験の割合を算出していきます。
使用経験有り
まずは、Wordの使用経験が有る人の割合を算出します。
Wordの使用経験が有る人の割合を算出するには、
Wordを使用したことがある人の人数 ÷ (Wordを使用したことがある人の人数 + Wordを使用したことがない人の人数) × 100
で算出することができるので、まず
=B29/(B29
と入力したら、F4キーで列名の前にドル記号を付けます。
そのまま
+C29
と入力したら、同じくF4キーで列名の前にドル記号を付けます。
最後に
)
と入力したら、Enterで確定します。
=B29/($B29+$C29)
割り算はExcelでは、スラッシュを入力します。
本来は最後に100を掛けるんですが、ここでは、表示形式機能を利用して、100を掛けた数字を表示します。
表示形式機能というのは、セルに入力されている数字は、そのままで、表示上の形式のみ変えることができる機能です。
今回は、100を掛けて、四捨五入し、%の記号を付ける表示形式機能を適用します。
入力したセルを選択した状態で、上のホームタブから、数値グループの、%記号をクリックします。
すると、表示上のみ、100を掛けて、四捨五入し、%の記号を付けることができました。
入力したセルを選択した状態で、セルの右下の小さい四角をダブルクリックすることで、他の行にも数式を適用することができます。
そのまま、セルの右下の小さい四角を右にドラッグすると、使用経験が無い人の割合も算出することができます。
保存
ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきます。
グラフ
次に、グラフを作成します。
グラフを作成するには、まずグラフの元となるセルを選択します。
ここでは、A28セルからA31セルを選択した後、Ctrlキーを押しながら、D28セルからE31セルを選択して、複数範囲のセルを選択した状態にします。
次に、上の挿入タブの、グラフグループからグラフを作成することができます。
今回は、縦棒/横棒グラフの挿入をクリックして、2-D 横棒の100%積み上げ横棒をクリックします。
すると割合のグラフを作成することができました。
グラフをドラッグして位置を変えたり、端をドラッグしてサイズを調整することができます。
グラフタイトル
グラフタイトルは、クリックして選択した状態で、再度クリックすると、編集することができます。
Ctrlキーを押しながらAで全てを選択した状態で、文字を入力すると、全ての文字を素早く削除して、次の文字を入力することができます。
ここでは、分かりやすいように、アプリの使用経験と変えておきます。
グラフタイトル以外の部分をクリックして、入力を確定します。
軸の反転
次に、アプリの順番が逆になっているので、反転させます。
アプリ名をダブルクリックして、書式設定を開きます。
軸のオプションから、軸のオプション内の、軸を反転するにチェックを入れます。
するとアプリの順番を反転することができます。
✕ボタンで、書式設定を閉じます。
ラベル
次に、このようにグラフの各要素に数字を表示させることで、正確な数字が分かるようにします。
このように、グラフの各要素に表示させる情報のことをラベルと言います。
ラベルを表示させるには、ラベルを表示させたいグラフを選択した状態で、上のグラフデザインタブから、グラフ要素を追加をクリックします。
するとメニューが出てくるので、データラベルから、中央を選択します。
すると、ラベルを表示することができました。
このままでは、数字が読みにくいので、文字色を白色に変えます。
ラベルをダブルクリックして、書式設定を開きます。
文字オプションの、文字の塗りつぶしと輪郭の、文字の塗りつぶしの、色で文字の色を変更することができます。
使用経験無しのラベルをクリックして、同じ様に文字色を変えます。
設定できたら、✕ボタンで書式設定を閉じます。
これでグラフの完成です。
保存
ここで一旦Ctrlキーを押しながらSキーを押して、ファイルを保存しておきます。
年代別
次に年代別の集計表を作成します。
年代別の集計表は、アプリごとに分けて作成するので、ピボットテーブル機能で作成することができます。
集計元となるテーブルのどこかを選択した状態で、上の挿入タブから、ピボットテーブルをクリックします。
するとピボットテーブルの設定画面が表示されます。
まずは、集計元となる表の範囲を選択します。
通常、このように自動で入力されますが、意図しない範囲が選択されている場合は、表を選択し直します。
次に、集計表を作成する場所を指定します。今回は既存のワークシートを選択して、集計(年代別)シートを開き、既存の集計表の下のセルを選択します。
選択するとそのセルの情報が入力されるので、入力できたらEnterキーを押すか、OKをクリックします。
すると、作成先のシートが開き、右側にピボットテーブルの設定画面が開きます。
上のボックスには、集計元の表の項目名が表示されています。
ピボットテーブルの設定方法を簡単に紹介すると、上のボックスにある項目名を、このように下のボックスにドラッグして使います。
- 行のボックスには左側に表示したい項目名、
- 列のボックスには上側に表示したい項目名、
- 値のボックスには算出したい項目の項目名、
- フィルターのボックスには絞り込みをしたい項目名
を入力します。
Word
まずはWordの集計表を作成します。
今回は、左側にWordの使用経験の有無の項目を表示したいので、Word(集計用)の項目をドラッグして、行のボックスに入れます。すると、左側に、使用経験の有無の項目が表示されました。
次に、上側に年代別の項目を表示したいので、年代(集計用)の項目をドラッグして、列のボックスに入れます。すると、上側に、年代別の項目が表示されました。
次に、回答数を表示したいので、Word(集計用)をドラッグして値のボックスに入れます。
これで、Wordの使用経験の有無を年代ごとに集計することができました。
最後に、各項目名を分かりやすいように変更しておきます。
Excel
次に、Excelの集計表を作成します。
新たにピボットテーブルを設定しても良いのですが、コピーする方が素早く作成することができます。
今、作成した、表を選択した状態で、Ctrlキーを押しながらCキーを押してコピーします。
貼り付け先のセルを選択したら、Ctrlキーを押しながらVキーで貼り付けます。
次に、設定画面の行と値のボックスを削除します。
削除するには、削除したい項目をクリックして、フィールドの削除をクリックすることで、削除することができます。
削除できたら、Excel(集計用)の項目を入れます。
これで、Excelの使用経験の年代ごとの回答を集計することができました。
項目名を分かりやすいように変更しておきます。
アプリ名のWordをExcelに書き換えます。
書き換えたいセルを選択した状態で、F2キーで編集モードにして、方向キーの上キーで先頭にカーソルを移動します。そのままDeleteキーでアプリ名を削除して書き換えます。
PowerPoint
PowerPointの集計表も同じ用に作成します。
Ctrlキーを押しながらCキーでコピーして、Ctrlキーを押しながらVキーで貼り付けます。
設定画面のボックス内を削除して、
PowerPoint(集計用)の項目を入れます。
アプリ名のExcelをPowerPointに書き換えます。
保存
ここでCtrlキーを押しながらSキーを押して、ファイルを保存しておきます。
おわりに
以上が、エクセルで『複数選択可』のアンケート結果を集計する方法でした。
