エクセルで『複数選択可』のアンケート結果の集計表とグラフを作成する方法

Описание: 以前の動画はこちら

【自動化Excel】アンケート結果を集計してグラフ化したり、年代ごとの集計表を作成する方法
   • 【自動化Excel】アンケート結果を集計してグラフ化したり、年代ごとの集計表を作成する方法  


【目次】
0:00 はじめに
0:21 完成形の確認
0:58 行や列を追加して、情報を入力する準備をする
3:30 仮の情報を入力する(RANDBETWEEN関数)
5:09 入力した選択肢の数字に応じて、自動で集計表用の文字が入力されるようにする(VLOOKUP関数)
12:18 集計表とグラフを作成する(COUNTIF関数)
24:03 年代別の集計表を作成する(ピボットテーブル・ピボットグラフ)


チャンネル登録はこちら
   / @consul-miya  

90円で入れる応援メンバーシップはこちら
   / @consul-miya  

人生の質が上がるラジオチャンネルの登録はこちらから
   / @lifehack-radio  

Twitter
  / lifehacker_miya  


【人気の再生リスト】
・ 効率を爆上げするショートカットキー&テクニック(Windows編)(   • 効率を爆上げするショートカットキー&テクニック(Windows編)  )


【使用素材】
●イラスト
いらすとや(https://www.irasutoya.com/)

●効果音
効果音ラボ(https://soundeffect-lab.info/)

●BGM
Track: Weero & Mitte - Our Dive [NCS Release]
Music provided by NoCopyrightSounds.
Watch:    • Weero & Mitte - Our Dive | Future House | ...  
Free Download / Stream: http://ncs.io/OurDiveYO

●EDテーマ
Track: Cartoon - Your Stories (feat. Koit Toome) [NCS Release]
Music provided by NoCopyrightSounds.
Watch:    • Cartoon, Jéja - Your Stories (feat. Koit T...  
Free Download / Stream: http://ncs.io/YourStoriesYO


この動画では、エクセルで『複数選択可』のアンケート結果を集計する方法を紹介します。

以前の動画で、アンケート結果を集計する方法を紹介したんですが、『複数選択可』のアンケート結果を集計する方法について質問があったので、この動画で紹介します。

もし、全く同じ環境で試したい場合は、その動画を参考にしてエクセルファイルを用意してください。

まずは完成形の確認です。

この動画では、使用したことのあるアプリを教えてください。という質問で、
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セルの表から検索し、
その表の完全一致する文字の


#家にいながら学ぶ
#スキルアップ
#パソコン