Googleフォームの回答をスプレッドシートで連携して自動集計する方法について解説します。
Googleフォームとはオンラインフォームを簡単に作成して共有できるツールです。
アカウントがあれば誰でも無料で使うことができるため、アンケートの無料からデータ収集･分析するのにとても便利です。
グラフも自動で作ってくれるので、集計結果を簡単に確認することができます。
ただこのグラフ…、細かい分析や集計をしたいときはとても見づらい･･････。
自分が欲しい情報だけを抽出したいと思ったときに少し不便です。
この問題を解決するためにGoogleフォームとスプレッドシートを連携して、様々な角度からデータを見れるようにしてみました。
よろしければ最後までご覧ください。
記事の最後にはスプレッドシートとフォームも無料で公開しています。
- Googleフォームの回答結果をスプレッドシートに自動集計したい
- 回答データを様々な条件で分類したい
- 関数を使いこなして特定の条件下でデータを集計･抽出したい
Googleフォームの作成方法
まずはGoogleフォームの作成です。
今回は商品満足度を調査するアンケートをサンプルとして説明します。
以下の手順でGoogleフォームを作成していきます。
- Googleドライブを開く
- アンケートを管理するフォルダを作成
- 左上の『+新規』ボタンをクリック
- アンケートを作成する
サンプルとして作ったアンケート内容は以下の通りです。
- 性別
- 年齢
- 商品を知ったメディア
- どこで買ったか
- 購入理由
- 全体の満足度
- 味
- 量
- 価格
- その他の感想
Googleフォームのアンケートサンプルはこちらのボタンから確認できます↓
アンケートの作り方を1から知りたい方は、こちらの記事を御覧ください。
作り方を画像付きで詳しく解説しています↓
Googleフォームとスプレッドシートとの連携
Googleフォームが完成したらスプレッドシートと連携します。
スプレッドシートとの連携が完了しました。
先程のスプレッドシートに集計用のシートを追加しておきます。
左下の『＋』ボタンを選択してください。新シートが追加されます。
分かりやすいようにシート名も変更しましょう！
- フォームの回答1 → アンケート結果
- シート2 → フォームの集計
- シート3 → フォームの抽出
以上でGoogleフォームとスプレッドシートの連携･準備は完了です。
連携に関する注意点はこちらの記事にまとめています。
回答を自動集計する
シートとの連携が完了したら、いよいよ集計方法の説明です。
これから紹介する方法はすべて｢COUNTIFS関数｣と｢それ以外の関数｣を組み合わせたものです。
男性･女性でアンケート回答数を分ける【COUNTIFS関数】
それではさっそく集計してみましょう。
まずは男性･女性でアンケート結果を集計します。
=COUNTIFS(‘アンケート結果’!B2:B,“男性”)
=COUNTIFS('アンケート結果'!B2:B,"男性")オレンジマーカーがカウントしたい範囲、つまり『アンケート結果』シートの1行目を除いたB列『B2:B』です。
B2:B15としても良いのですが、｢15｣を除くと列全体を指定できます。
今後アンケートの回答数が増えても自動でカウントしてくれるので上記のように設定しょう。
このままでも使えますが、このセルに入力した計算式をコピペしたときに範囲がずれてしまいます。
なので範囲を｢絶対参照｣に変更します！
難しいことは考えず｢B2:B｣と範囲をマウスでクリックしてキーボードの｢F4｣ボタンを押してください。
そうすると｢$（ダラーマーク）｣が付きます。英語の両端に｢$｣が付いたらOKです。
絶対参照となりコピペしても選択した範囲がずれなくなります。
仕上げにカウントの条件となる｢“男性”｣の箇所を｢A7｣セルにしましょう！
これによってセルに入力されている文字列を参照することが出来るようになります。
最終式は以下のとおりです。
=COUNTIFS(‘アンケート結果’!$B$2:$B,A6)
=COUNTIFS('アンケート結果'!$B$2:$B,A6)セルに直接入力してもいいですが、『リスト』を作成してあげれば入力してほしい文字をクリックで選択することが可能です。このスプレッドシートではリストを活用していきます。
日時を月ごとに振り分ける
次はアンケートの提出数を月ごとに計算していきます。
月末、月初めをEOMONTH関数を使用して求めます。
EOMONTH関数とは指定された日付から特定の月末日を返すために使用されます。
この関数の『0』の部分を『-1』に、そして計算式として『+1』を付け足せば月初日が求められます。
まずはこちらを利用してシートに各月の月初を表示します。
=EOMONTH(B10,-1)+1
=EOMONTH(B10,-1)+1隣のセル｢C12｣は
=EOMONTH(B12,0)+1
=EOMONTH(B12,0)+1D12〜F12は上記をコピペすればOKです。
次にCOUNTIFS関数の設定です。
=COUNTIFS(‘アンケート結果’!$A$2:$A,”>=”&B12,‘アンケート結果’!$A$2:$A,”<=”&EOMONTH(B$12,0))
=COUNTIFS('アンケート結果'!$A$2:$A,">="&B12,'アンケート結果'!$A$2:$A,"<="&EOMONTH(B$12,0))COUNTIFS関数の中にEOMONTH関数を使用して2つの条件を設定しました。
- 条件① 指定した日付の月初以上（オレンジマーカー）
- 条件② 指定した日付の月末以下（緑マーカー）
条件①は日付セルB12で月初を求めているので『 “>=”& 』でB12以上の日付という条件式を設定します。
条件②はB12の月末をEOMONTH関数を使用し、『 “<=”& 』でB12セルの月末未満という条件式を設定しました。
範囲はアンケート結果シートのタイムスタンプを範囲指定しています。絶対参照を設定する『F4』も忘れずに押してください！
最後に日付の表示を変更します。2023/02/01のままだとその日の集計結果と勘違いされそうなので
表示形式→数字→2008年9月を選択すれば年と月だけ表示されます。
◯◯◯◯年◯月と表示が変わればOKです。
これで月ごとのアンケート回答数の分類は完了です。
COUNTIFS関数を使った期間指定の方法はこちらの記事↓で詳しく解説しています。
性別･年齢ごとに『知った理由』を振り分ける
次に集計結果を｢性別｣｢年齢｣｢知った理由｣ごとに振り分けます。
=COUNTIFS(‘アンケート結果’!$B$2:$B,$A17,‘アンケート結果’!$C$2:$C,$B17,‘アンケート結果’!$D$2:$D,C$16)
=COUNTIFS('アンケート結果'!$B$2:$B,$A17,'アンケート結果'!$C$2:$C,$B17,'アンケート結果'!$D$2:$D,C$16)条件を整理すると以下のとおりです。
- 条件① アンケート結果のB列（B2:B）が｢男性（A17）｣である
- 条件② アンケート結果のC列（C2:C）が｢10〜19歳（B17）｣である
- 条件③ アンケート結果のD列（D2:D）が｢雑誌（C16）｣である
先ほどと同様、検索するセルを参照するようにしました。入力する文字を間違えないよう注意してください。
また複数条件の場合、指定する範囲が異なるとエラーが出ますのでこちらも注意してください。
例）『B2:B』 と 『C:C』では計算できない
ここでもセルをコピペするので範囲に『絶対参照』を指定するため『F4』を押します。
1つ違うのは｢性別｣と｢年齢｣は列の絶対参照、｢知った理由｣の参照は行の絶対参照にしてください。
- 性別 $A17（ローマ字の前）
- 知った理由 C$16 （ローマ字と数字の間）
『$』マークの位置が違うのがお分かりでしょうか？
これも『F4』ボタンを連打すれば変わっります。
購入した理由（複数回答）を数える
次に購入した理由を分類していきます。
これは少し面倒です。
Googleフォームで複数回答できる項目は一つのセルにまとまって入力されてしまいます…。
COUNTIFSの検索条件はセルが完全に一致していなければカウントされません。
なので引数･条件を『“*”&（ダブルクオーテーション･アスタリスク･アンド）』で挟むことで、
条件の部分一致で検索が可能になります。
=COUNTIFS(‘アンケート結果’!$B$2:$B,$A24,‘アンケート結果’!$C$2:$C,$B24,’アンケート結果’!$E$2:$E,”*”&C$23&”*”)
=COUNTIFS('アンケート結果'!$B$2:$B,$A24,'アンケート結果'!$C$2:$C,$B24,'アンケート結果'!$E$2:$E,"*"&C$23&"*")絶対参照の設定を忘れずに！最後に必要箇所にコピペして完成です。
回答の平均･最大･最小値を求める
平均値、最大値、最小値はそれぞれ『AVERAGEIFS（平均）』『MAXIFS（最大）』『MINIFS（最小）』を使用します。
使い方はCOUNTIFS関数と似ていますが、こちらは計算する範囲を設定後にそれぞれの条件を設定していきます。
全体の満足度の平均値を求める【AVERAGEIFS関数】
AVERAGEIFS関数は、指定された条件に一致する値の平均値を計算します。
全体の満足度は｢アンケート結果｣のF列にあります。
=AVERAGEIFS(‘アンケート結果’!$F$2:$F,‘アンケート結果’!$B$2:$B,$A32,‘アンケート結果’!$C$2:$C,$B32)
=AVERAGEIFS('アンケート結果'!$F$2:$F,'アンケート結果'!$B$2:$B,$A32,'アンケート結果'!$C$2:$C,$B32)条件は以下の通りです。
- 範囲 アンケート結果のF列（F2:F）の平均値を求める範囲
- 条件① アンケート結果のB列（B2:B）がA32（性別）と同じ
- 条件② アンケート結果のC列（C2:C）がB32（年齢）と同じ
こちらもカウントイフで説明したときと同様、コピペしてください。
全体満足度の最大･最小値を求める【MAXIFS･MINIFS関数】
次に全体満足度の最大値（MAXIFS関数）と最小値（MINIFS関数）を求めます。
設定範囲はAVERAGEIFS関数と同様です。
【最大値】
=MAXIFS(‘アンケート結果’!$F$2:$F,‘アンケート結果’!$B$2:$B,$A32,‘アンケート結果’!$C$2:$C,$B32)
=MAXIFS('アンケート結果'!$F$2:$F,'アンケート結果'!$B$2:$B,$A32,'アンケート結果'!$C$2:$C,$B32)【最小値】
=MINIFS(‘アンケート結果’!$F$2:$F,‘アンケート結果’!$B$2:$B,$A32,‘アンケート結果’!$C$2:$C,$B32)
=MINIFS('アンケート結果'!$F$2:$F,'アンケート結果'!$B$2:$B,$A32,'アンケート結果'!$C$2:$C,$B32)- 範囲 アンケート結果のF列（F2:F）の最大値･最小値を求める範囲
- 条件① アンケート結果のB列（B2:B）がA32（性別）と同じ
- 条件② アンケート結果のC列（C2:C）がB32（年齢）と同じ
絶対範囲を指定してコピペして完了です。
項目ごとの満足度の平均･最大･最小値をもとめる
次に｢味｣｢量｣｢価格｣ごとの満足度を平均をAVERAGEIFS関数を使って計算します。
先程ご紹介した｢全体の満足度の点数｣のように、セルの参照先を変更すれば各項目ごとの計算が出来ます。
これをもっとスマートにするため、『名前付き範囲』と『INDIRECT関数』を加えます。
（ちょっと中級クラスの関数の組み合わせになると思います。難しければ読み飛ばしてください。）
まずは名前付き範囲の設定からです。名前付き範囲はその名の通り、指定した範囲に名前を付けることできます。
『アンケート結果』のG2:G15（味の満足度）の範囲を選択し『右クリック』を押します。
『セルで他の操作項目を表示』→『名前付き範囲を定義』を選択します。
するとシートの右側に『名前付き範囲』を設定出来るようになります。
①に『味』、②の範囲が書かれている箇所の｢G2:G15｣と書かれている箇所｢15｣を消してください。
これで『味』とう名前付きの範囲が出来ました。
同様の手順で『量』と『価格』という名前付き範囲を作成していきます。
設定完了後、フォーム抽出シートに戻り以下のように式を入れてください。
=AVERAGEIFS(味,‘アンケート結果’!$B$2:$B,$A41,‘アンケート結果’!$C$2:$C,$B41)
=AVERAGEIFS(味,'アンケート結果'!$B$2:$B,$A41,'アンケート結果'!$C$2:$C,$B41)- 範囲 アンケート結果の『味（G2:G）』の範囲
- 条件① アンケート結果のB列（B2:B）がA41（性別）と同じ
- 条件② アンケート結果のC列（C2:C）がB41（年齢）と同じ
最後に『INDIRECT関数』を使用します。
INDIRECTは文字列で指定されたセルアドレスを参照することを可能にする関数です。
これを使って先程の条件式を以下のように書き換えます。
=AVERAGEIFS(INDIRECT(C$38),‘アンケート結果’!$B$2:$B,$A41,‘アンケート結果’!$C$2:$C,$B41)
=AVERAGEIFS(INDIRECT(C$38),'アンケート結果'!$B$2:$B,$A41,'アンケート結果'!$C$2:$C,$B41)これでコピペすれば完成です！
INDIRECT関数は名前付き範囲と組み合わせることで動的なセルの参照が可能になります。
馴れが必要ですが、使いこなせば分析の幅が確実に広がります。
直接範囲指定に慣れてきたら活用してみてください。
必要な情報を抽出する
フォームで回答された内容はスプレッドシートのフィルターを使えば特定の項目に該当する回答内容を抽出することが出来ます。
アンケート回答全体にフィルターをかけてもいいのですが、｢今のフィルター何だ？｣となりがちなので、
FILTER関数を使用して視覚的にも分かりやすいようにしていきましょう。
性別･年齢ごとに回答内容を抽出する【FILTER関数】
FILTER関数の使い方は以下のとおりです。
FILTER(抽出するセル範囲, (条件1)*(条件2)…)
複数条件を選択したい場合は『()』カッコで条件を囲み、『*』アスタリスクでつなぎます。
=FILTER(‘アンケート結果’!D:J,(‘アンケート結果’!B:B=A3)*(‘アンケート結果’!C:C=B3))
=FILTER('アンケート結果'!D:J,('アンケート結果'!B:B=A3)*('アンケート結果'!C:C=B3))条件の設定は以下の通りです。
- 範囲 アンケート結果の『知った理由〜その他の感想まで（D:J）』の範囲
- 条件① アンケート結果のB列（B:B）がA3（性別）と同じ
- 条件② アンケート結果のC列（C:C）がB3（年齢）と同じ
上記の式を｢A6｣セルだけに先程の式を入力すれば該当するデータがセルに抽出されます。
今回はコピペしないので絶対範囲の指定はしませんでした。
グラフ化するともっと見やすくなる
ここまでスプシで集計方法できればグラフ化も容易です。
スプシの範囲を選択 → 挿入 → グラフを選択して視覚的にもわかりやすくしてあげることが出来ます。
スプレッドシートは無料で公開
フォームとスプレッドシートは無料公開してます。
こちらのリンクから参考にしてください。
まとめ
今回はGoogleフォームの回答をスプレッドシートで自動集計したり、特定の項目を抽出したり分けたりする方法を解説しました。
スプレッドシートの関数に慣れていない人には少し難しい内容だったかもしれません。
なんじゃこりゃ？となっている場合は私のシートをコピペして使用していただければと思います。
関数を理解して使いこなすことが出来れば分析の幅がぐんと広がります。
スプレッドシートと連携して、ぜひ解像度の高い分析をしてみてください。
Googleスプレッドシートを学ぶなら
スプレッドシートについて学びたいのであれば以下の書籍がおすすめです。
『Kindle Unlimited』に入っていれば月額以上のお支払い無しで読むことが出来ます。
ポチップ
仕事の生産性スキルを上げたいなら
この記事を読まれた方はおそらく仕事を頑張っている方だと思います。
エクセル･スプレッドシートといたパソコンスキルを向上させたいのであればオンライン学習の『Udemy』がおすすめです。
月1回開催されるセールを利用すれば90％オフなどで有益なオンラインコースを受けることが可能です。
パソコンスキルを上げて周りから一目置かれる存在になってください。
それでは！！
