【フォームズの使い方】Excelでアンケート結果を簡単に集計する方法
フォームを利用してアンケートを行っている方に向けて、ログデータからアンケート結果を集計する方法をご紹介します。
ログデータのダウンロード方法についてはこちらのページをご確認ください。
今回は下記フォームの「Ｑ１.すきな季節は？」の回答を集計して表にしたいと思います。
この記事の目次
Excelの各部名称の確認
今回の表の作成方法を説明する際に登場する各部名称がこちらです。
確認しながら読み進めてください。
ログデータを整理
表の作成を行う前にログデータの整理を行います。
ダウンロードしたばかりのログデータを開くと、表示できる文字数を超えているセルは「#######」と表示されていたり、途中で切れてしまっていたりととても見辛いので、列幅を整え、いらない列は削除してしまいましょう。
ダウンロードしたデータはそのまま使わず、コピーしてバックアップを取っておいてください。
列の削除
まずは必要ないなと思う列があれば削除しましょう。
今回はA列の日付項目を削除します。
列番号Aの上にカーソルを移動させ右クリック。
メニューが表示されるので削除を選択するとA列が削除されます。
列の幅を整える
列番号の間にカーソルを移動させると、カーソルが黒い十字に変わるのでダブルクリックして下さい。
列の中で１番幅が広いセルに合わせて調整されます。
項目タイトルを固定する
一番上の行にフォームの項目名が表示されていますが、フォームの投稿数が多いとログデータもどんどん縦長になり、今はどの項目の回答を見ているのかわからなくなってしまうこともあります。
なのでスクロールしても常に項目名が表示されている状態にするため項目名の列を固定します。
行番号１のセルを選択します。どこでも構いません。
選択したら表示タブに移動しウィンドウ枠の固定をクリック。
先頭行の固定を選択してください。
befor
after
アンケート結果を集計する
シートを追加する
ログデータと同じシートでもいいのですが今回は分けます。
シート見出しの隣の十字マークをクリックすると「sheet1」が追加されます。
シート見出しダブルクリックで名前の変更が可能です。
「COUNTIF関数」で人数を数える
「COUNTIF関数」とは「範囲の中から検索条件に一致したセルの数をカウントする関数」です。
初めにSheet2のA1に項目のタイトルを、その下に表の縦軸となる選択肢の「春・夏・秋・冬」をそれぞれ入力します。
選択肢が多い場合は、フォーム編集画面からコピーしてくるとはやいです。
先ほどのように横幅を調節して下さい。
B1に「人数」と入力し、B2に「COUNTIF関数」を使って各選択肢の選択人数をカウントします。
- 選択肢「春」の隣のセル(B2)に、半角で「=COUNTIF」を入力
- 候補に「COUNTIF」が表示されるのでダブルクリック
セルの中に「＝COUNTIF(」と入力されすぐ下に「COUNTIF(範囲,検索条件)」という注釈が表示される - ログデータのページに移動、列番号AをクリックしF4キーを押して、半角で「,」を入力
- 「sheet2」に移動して「春(セルA2)」をクリックしF4キーを3回押す。
- 数式バーに「=COUNTIF(log_S70219519!$A:$A,Sheet1!$A2)」と表示されているのを確認してエンター
- もう一度セルB2をクリックしセル右下の四角にカーソルを移動させるとカーソルが十字になるので、「冬」の隣「セルB5」までドラッグ
完成
絶対参照
(４)で列番号Aを選択すると、始め「=COUNTIF(log_S70219519!A:A」と表示されていますが、F4キーを押すことで「=COUNTIF(log_S70219519!$A:$A」になります。
「$」がついているものを絶対参照といい、式内のセルを固定するときに使用されます。
「A:A」はセルではなく列なので列番号のみですが、「A1」などのセルを絶対参照にすると「$A$1」のように列番号・行番号のどちらにも「$」がつきます。
セルB3の数式を見てみると「=COUNTIF(log_S70219519!$A:$A,Sheet1!$A3)」となっており、最期の数字が「2」から「3」になっているのがわかります。
ドラッグしたことによって検索条件が自動的に変更されたことを意味するのですが、アンケートを集計する際、設定した範囲が移動してしまっては困ります。
「A:A」はセルではなく列ですが絶対参照にして範囲の固定をします。
複合参照
「$A2」のように列番号のみ(または行番号のみ)に「$」がついているものを複合参照といいます。
絶対参照が列・行のどちらも固定するのに対し、複合参照は片方のみを固定したい場合に用います。
今回の場合、検索条件の列はAに固定したまま、行は変更したいので複合参照で「$A2」にします。
保存する前にファイルの種類を変更
ログデータはCSV形式でダウンロードされますが、CSV形式は複数のシートを保存できません。
なのでファイルの種類をExcelに変更し、ひとつのブックで複数のシートを保存できるようにします。
変更するにはまずファイルタブのエクスポートを選択。
ファイルの種類の変更をクリックし「ブック(*.xlsx)」を選択したら、名前を付けて保存をクリックし保存を完了して下さい。
