Google Sheets約5分で読めます

Googleフォームの回答件数と参加人数が合わない|家族申込とキャンセルを分けて集計する方法

Googleフォームの回答件数と参加人数が合わないを表したイメージ
記事の内容を表したイメージ(AI生成)

Googleフォームの「回答件数」は送信ごとに1件として記録されますS1。家族で一緒に申し込んだ場合も1件、キャンセルの連絡を別フォームで受けた場合は、その別フォームに1件として記録されます。受付担当者がこの件数をそのまま定員と照らし合わせると、実際の参加予定人数とずれが生じます。

この記事では、回答件数・有効申込件数・参加人数・キャンセル人数を別々に把握する集計の考え方と、スプレッドシートで使える数式の構成を説明します。数式は公式の構文仕様から作成したものであり、Excel・Google Sheetsでの実機検証は行っていません。自環境での動作は実際に試して確認してください。

なぜ回答件数と参加人数がずれるのか

Googleフォームは1回の送信を1件として記録しますS1。「参加人数」を回答する設問がある場合でも、回答件数がその数値の合計になるわけではありません。また、キャンセルの連絡をフォームで受けても、元の申込回答が削除されるわけではありません。

結果として、回答件数だけを見ていると次のことが把握できません。

  • 1件の回答に何人分の申込が含まれているか
  • キャンセルが入った申込が何人分だったか
  • 人数の記入漏れがある回答が何件あるか

集計に必要な列を用意する

フォームの回答をスプレッドシートに出力した後、少なくとも次の3列を確認できる状態にします。

表は横にスクロールできます

列内容備考
回答ID各送信を区別する識別子重複せず、並べ替えても変わらない固定値を使用
人数申込人数(数値)未入力・文字列の行は後述の保留扱いに
状態確定/キャンセル/確認中など受付担当が手動で記入または別フォームで管理

「状態」列はフォームが自動で付けるものではありません。受付側でルールを決めて入力します。

架空例で構造を確認する

編集部が検算用に作成した架空データ(responses.csv)の構造は次のとおりですS5。実在の申込者情報ではありません。CSVは値と見出しだけの練習用データで、数式や入力規則を含む完成ブックではありません。

表は横にスクロールできます

回答ID人数状態
R11確定
R23確定
R32キャンセル
R4(未入力)確認中

この架空例から導かれる集計値をローカル検算で確認しましたS5。

表は横にスクロールできます

集計項目値
回答件数4件
有効申込件数(確定かつ人数あり)2件
参加予定人数4人
キャンセル人数2人
保留(人数未入力)1件

R4は人数が入力されていないため、参加人数の合計に含めません。人数未入力を自動的に1人として補完することはしません。

スプレッドシートでの数式構成

以下の数式は、A列=回答ID、B列=人数、C列=状態、データ行が2〜5行目(4件分)という配置を前提にしています。数式は公式の構文仕様S3S4S2をもとに作成しており、Excel・Google Sheetsでの実機実行はしていません。実際のデータに合わせてセル範囲を変更してください。

回答件数(IDが入力されている行数)

集計セルをE2とした場合:

=COUNTIF($A$2:$A$5,"<>")

COUNTIFは1条件に合うセル数を返しますS3。架空例での期待値:4。

人数が正の整数かを先に確認し、小数・負数・不明な値は「確認中」にします。次の式の ">0" は正の値を選ぶ条件であり、整数かどうかを検査する条件ではありません。

有効申込件数(事前確認済みの確定申込)

集計セルをE3とした場合:

=COUNTIFS($C$2:$C$5,"確定",$B$2:$B$5,">0")

COUNTIFSは複数条件をすべて満たす件数を数えますS4。条件範囲の行数は同じにする必要があります。架空例での期待値:2。

参加予定人数(確定かつ人数が正の整数の合計)

集計セルをE4とした場合:

=SUMIFS($B$2:$B$5,$C$2:$C$5,"確定",$B$2:$B$5,">0")

SUMIFSは複数条件に一致する値を合計しますS2。架空例での期待値:4。

キャンセル人数

集計セルをE5とした場合:

=SUMIFS($B$2:$B$5,$C$2:$C$5,"キャンセル")

架空例での期待値:2。

保留行と追加申込の扱い

人数が未入力または文字列の行は「確認中」などの状態にして、参加人数の合計から外します。人数が文字列(例:「3名」)のままだと数値として計算されないため、B列の型はあらかじめ数値に統一することを推奨します。ただし、CSVをスプレッドシートで開くと列の型が自動変換されることがあるため、インポート後に確認してください。

キャンセル後に別の参加者が追加される場合は、キャンセルした申込の記録を残し、新しい申込を別のIDで記録します。元の申込を「キャンセル」へ変更し、新しい申込の状態と人数を確認してから集計します。

定員との比較は「参加予定人数(E4)」を定員と照らし合わせます。回答件数(E2)との比較では家族申込の人数が反映されません。

対応しない範囲

フォームの受付終了・締切設定の手順はこの記事の対象外です。また、フォームの受付終了操作はキャンセルを自動で反映するものではなく、集計側の状態列を更新する操作とは別です。

次の作業

まず回答データに「状態」列が存在するかを確認します。なければ1列追加して、既存の回答行に「確定」「キャンセル」「確認中」のいずれかを手入力します。その後、上記の数式をE2〜E5に入力して、架空例と同じ配置になっているかをデータ件数で確認してください。数式のセル範囲はデータ行数に合わせて変更が必要です。

この記事の確認用ファイル

架空データの記入例や、手元で確認するための素材です。

参考資料

公式資料と架空データを使って編集しています。配布CSVは値と見出しのみで、数式・書式・保護設定を含む完成ブックではありません。Excel・Sheetsの実機操作は未検証です。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ