google フォーム クロス 集計と検索する人は、回答の総数はもう見えていて、その先が知りたい状態にあります。全体では満足度が高いのに、ある層だけ低い。全体の参加率は伸びているのに、特定の地域からの申し込みだけ止まっている。こうした「誰がそう答えたのか」はフォームの画面だけでは出てきません。この記事では、Googleフォームの回答をクロス集計する手順を、表の整え方、ピボットテーブルと関数の使い分け、割合を出すときの分母の決め方、そして属性を取るための設問設計まで順に整理します。
クロス集計は「2つの設問を掛け合わせて見る」こと
クロス集計は、2つ以上の設問を掛け合わせて、片方の属性ごとに答えの分布を見る集計です。単純集計が「満足が60人、不満が20人」と全体の数を出すのに対し、クロス集計は「初参加の人は満足が30人で不満が15人、2回目以降は満足が30人で不満が5人」という形で、内訳の違いを出します。
なぜこれが必要なのかは、判断に繋がるかどうかで決まります。全体の満足度が高いという結果からは、次に何を変えるべきかが出てきません。初参加だけ不満が集まっているという結果なら、案内の書き方か当日の受付を直す、という次の一手が決まります。報告を受ける側が知りたいのも、全体の平均ではなく「どこを直せば数字が動くか」です。
Googleフォームの回答タブにある概要は、設問ごとの単純集計を見せる画面です。棒グラフや円グラフが自動で出るのは便利ですが、そこに属性を掛け合わせる仕組みはありません。運用の現場からも、この点は繰り返し指摘されています。
Googleフォームは、回答の集計結果をそのまま確認できるのが魅力ですが、質問形式によって表示されるグラフが限られていたり、集計期間の絞り込みができなかったりと、ビジネス利用にはやや物足りなさを感じることがあります。 出典: form.run
つまり、クロス集計はフォームの外でやる作業になります。回答をスプレッドシートに繋いで、ピボットテーブルか関数で組む。これが最短の道で、追加の費用もかかりません。作業時間は15分ほどで、慣れれば5分で終わります。
クロス集計の前に、回答の表を整える
ピボットテーブルを作ろうとして数が合わない場合、原因はほぼ表の形です。集計の道具は正しく動いていて、渡している表が集計向きになっていません。先に4か所を確かめてください。
1つ目は、列の見出しです。Googleの公式ヘルプは、ピボットテーブルを追加する手順の中で、列ごとに見出しを付ける必要があると明記しています。フォームから繋いだシートなら1行目に設問文が入っているので通常は問題ありませんが、手で行を挿入して見出しの上に空行を作ってしまうと、範囲の指定がずれます。
2つ目は、表記のばらつきです。所属や地域を自由記述で聞いていると「東京都」「東京」「とうきょう」が混ざり、同じ属性が3つの行に分かれます。この状態でピボットテーブルを作ると、行が延々と増えた読めない表が出てきます。集計に使う予定の設問は選択式にしておくのが唯一の確実な対策で、すでに集めた分は置換で揃えるしかありません。
3つ目は、空欄です。必須にしていない設問は空欄で届きます。ピボットテーブルでは空欄も1つの分類として並ぶので、「未回答」という行が出てきます。これは消すのではなく、残したうえで割合の分母から外すか含めるかを決めてください。判断せずに消すと、報告の数字が実態より良く見えます。
4つ目は、タイムスタンプの型です。月ごとや週ごとに切って見たいなら、日時が文字列ではなく日付として入っている必要があります。列を選んで表示形式を日付に直しておけば、ピボットテーブル側で月単位にまとめられます。
スプレッドシートのピボットテーブルでクロス集計を作る手順
表が整ったら、手順そのものは短いです。Googleの公式ヘルプが案内している流れに沿って進めます。
まず、回答用のスプレッドシートを開いて、集計に使う範囲のセルを選びます。次に上部のメニューから「挿入」の「ピボットテーブル」をクリックし、ピボットテーブル用のシートを開きます。サイドパネルで「行」の横の「追加」をクリックして、属性にしたい設問を選びます。参加回数や地域、年代など、分けて見たい軸がここに入ります。
続けて「列」の横の「追加」から、見たい答えの設問を選びます。満足度や希望する形式など、分布を知りたい設問がこちらです。最後に「値」の横の「追加」から、数えるための項目を選びます。回答の件数を出したいだけなら、タイムスタンプの列を選んで集計方法をCOUNTAにするのが素直です。これで、行に属性、列に答え、交点に件数が入った表ができます。
公式ヘルプには、便利な機能も書かれています。ピボットテーブルのセルをダブルクリックすると、そのセルに対応するソースデータの行が新しいシートに表示されます。「この2件は誰の回答なのか」を確かめたいとき、元のシートを目で探す必要がありません。数が合わないときの原因を追うときにも、この機能から入るのがいちばん速いです。
サイドパネルで「値」の横の「追加」から計算フィールドを選ぶと、ピボットテーブルの中に新しい指標を作れることも案内されています。割合を出したい、2つの数を割ってみたいという場合は、元のシートに列を足すのではなく、ここで作ったほうが表が汚れません。
関数で組む場合｜COUNTIFSとピボットテーブルの使い分け
決まった形の表を毎回同じ場所に出したいなら、関数のほうが向きます。クロス集計に使うのはCOUNTIFSで、Microsoftの公式ドキュメントは、複数の範囲のセルに条件を適用して、すべての条件が満たされた回数をカウントする関数だと説明しています。
書き方は、条件範囲と検索条件を対にして並べる形です。属性の列を1つ目の条件範囲に、その属性の値を1つ目の検索条件に、答えの列を2つ目の条件範囲に、その答えを2つ目の検索条件に入れます。表の左端に属性を、上端に答えを並べておき、交点のセルに数式を1つ書いて縦横に引けば、クロス集計の表が一度に埋まります。
公式ドキュメントには、追加の範囲と条件について、最大127組の範囲と条件のペアを指定できると書かれています。掛け合わせる軸が3つ4つに増えても足りなくなることはありません。ただし、各追加範囲は1つ目の範囲と同じ数の行および列である必要がある、という注意も添えられています。列ごとに範囲の終わりの行番号が違うと、正しい数が出ません。
条件には、半角の疑問符や半角のアスタリスクをワイルドカードとして使えることも書かれています。自由記述の中に特定の言葉が入っているかで分けたいときは、アスタリスクで囲んだ条件が使えます。文字としての疑問符やアスタリスクを探したいときは、その前にチルダを付けます。
使い分けの目安は、更新の頻度です。回答が届くたびに最新の数を同じ場所で見たいなら関数、その時点の内訳を掘って眺めたいならピボットテーブル。報告書の決まった枠に数字を入れる作業が毎月あるなら、関数で組んで貼り替えるだけの形にしておくと、作業が数分で終わります。
Excelに持ち出してクロス集計する場合の注意
集計をExcelでやりたい場合は、回答をCSVで落とすか、スプレッドシートからExcel形式で書き出して持っていきます。ピボットテーブルの考え方は同じで、行と列に設問を置き、値に件数を入れます。
ここで気をつけたいのは、持ち出した時点でデータが止まることです。CSVは落とした瞬間を切り取ったファイルなので、その後に届いた回答は入りません。締め切り後にまとめて分析するなら問題ありませんが、受付中の途中経過を見る用途だと、古い数字で議論することになります。途中経過を見たいなら、スプレッドシート側で組んでおくほうが安全です。
文字コードの問題も出ます。GoogleフォームのCSVはUTF-8で書かれているため、Excelでそのまま開くと日本語が化けることがあります。Excelの「データ」タブから「テキストまたはCSVから」を選び、ファイル元を「65001: Unicode (UTF-8)」にして読み込めば直ります。電話番号や郵便番号の先頭のゼロを残したいときは、取り込み時に列の書式を文字列にしてください。
行数が多い場合も確かめておきます。Microsoftの公式ドキュメントによれば、ワークシートの行数と列数は1,048,576行と16,384列です。アンケートの回答でここに届くことはまずありませんが、フィルターのドロップダウンの一覧に表示されるアイテムは10,000という制限もあるので、自由記述の値をフィルターで選ぼうとすると途中で切れることがあります。属性で分けるときは、フィルターではなく集計側で分けたほうが確実です。
時期で切って見るクロス集計
属性と答えを掛けるほかに、時期と答えを掛ける見方があります。フォームの回答には必ずタイムスタンプが入っているので、追加の設問なしで作れるのが利点です。
やり方は、日時の列から月や週を取り出す列を1つ足して、それを行に置くだけです。表示形式を日付にしてあれば、ピボットテーブルの側で月単位にまとめる操作もできます。行に月、列に答えを置けば、月ごとの内訳が並びます。
この見方が効くのは、施策の前後を比べたいときです。案内文を書き換えた月、受付の画面を変えた月、募集の経路を増やした月に印を付けておき、その前後で内訳がどう動いたかを見ます。全体の件数だけを追っていると、増えた理由が案内の変更なのか季節なのか分かりません。内訳まで見れば、どの層が増えたのかが出てきて、理由の見当が付きます。
締め切りのある受付では、日ごとに切るのも役に立ちます。申し込みは締め切りの直前に集中するのが普通で、その山がどれくらいの大きさになるかが分かると、当日の人手の配り方を決められます。去年の同じ受付の分布を持っていれば、今年の見込みも立てられます。
ただし、時期で切ると1マスの人数はさらに小さくなります。月ごと、属性ごと、答えごとと3つ掛けると、たいていのアンケートでは数人単位のマスばかりになります。時期を掛けるときは、属性の軸を1つに絞るか、時期を四半期などの粗い単位にまとめます。
タイムスタンプは受け付けた時刻であって、回答者が書き始めた時刻ではありません。長い時間をかけて書いた人も、一瞬で送った人も、同じ1点として並びます。どれくらいの時間がかかったかを知りたい場合は、フォームの記録からは分からないので、別の方法で測ることになります。
出した表を報告に載せるときの形
集計そのものより、報告に載せる形で迷う場面のほうが多くなります。表の見た目で伝わり方が変わるので、載せ方も決めておきます。
まず、生のピボットテーブルをそのまま貼らないことです。行にも列にも全部の分類が並んだ表は、作った本人には読めても、受け取る側には読めません。報告に載せるのは、伝えたい1点に絞って作り直した表です。行を3つ、列を3つ程度まで落とすと、そこで何が起きているかが一目で分かります。
件数と割合は、両方載せます。割合だけだと母数が見えず、件数だけだと比較ができません。1つのマスに「12件（24%）」のように並べて入れる形が扱いやすく、母数の小さいマスもその場で分かります。分母が何かは、表の外に1行で書いておきます。
色を付けるのは、伝えたいマスだけにします。全体に色を塗ると、どこを見ればよいのか分からなくなります。ほかと違う動きをしているマスに1色だけ付けて、その理由を表の下に1文で書きます。
グラフにするかどうかは、伝えたい形で決めます。属性ごとの構成の違いを見せたいなら、100%の横棒を属性ごとに並べる形が読みやすくなります。時期による動きを見せたいなら折れ線にします。数字そのものを確認してほしい場面では、無理にグラフにせず表のままにします。グラフにすると、正確な数が読み取れなくなります。
そして、表の隣に必ず「だから何をするか」を書きます。クロス集計の表だけを回覧すると、読んだ人それぞれが違う解釈をして議論が散ります。作った本人が読み取った内容と、次にやることを1行ずつ添えるだけで、報告としての役割を果たします。
クロス集計で間違えやすい3つのこと
数字が出たあとに間違えるのは、たいてい読み方です。3つだけ覚えておけば、報告で撤回する事故が防げます。
1つ目は、分母の取り違えです。クロス集計で割合を出すとき、全体を分母にするのか、その属性の中の人数を分母にするのかで、まったく違う数字になります。「不満と答えた人のうち初参加が70%」と「初参加の人のうち不満が20%」は別の話です。前者は不満の内訳で、後者は初参加の傾向を示します。報告に書くときは、どちらの分母かを文の中に必ず入れてください。
2つ目は、母数が小さいセルです。属性で分けると、1つのセルに入る人数は一気に減ります。全体で100人の回答でも、地域と年代で分ければ1マス3人や5人になります。3人のうち2人が不満だったからといって、その層の不満率が67%だとは言えません。母数がひと桁のセルは、数だけ書いて割合を書かない、という決めごとにしておくのが無難です。
3つ目は、複数回答の扱いです。チェックボックスの設問は、1つのセルにカンマ区切りで複数の答えが入ります。この形のままピボットテーブルに渡すと、「オンライン, 対面」という組み合わせが1つの分類として並び、選択肢ごとの数は出ません。項目ごとに列を足して、含まれているかどうかを1と0で置き直してから集計します。この場合、各列の合計を足した数は回答者数を超えるので、割合の分母は回答者数のままにしてください。
自由記述をクロス集計に載せる
クロス集計の軸に使えるのは、基本的に選択式の設問です。自由記述は値がばらけるので、そのままでは行にも列にも置けません。それでも、自由記述こそ理由が書かれている場所なので、捨てずに載せる方法を用意します。
最も単純なのは、分類の列を手で足す方法です。元のシートの右側に列を1つ作り、自由記述を読みながら、あらかじめ決めた分類のどれかを入れていきます。分類は多くても6つまでにします。これ以上に増やすと、入れる側の判断が揺れて、同じ内容が別の分類に入ります。分類の定義は、迷ったときに読み返せるよう、別のシートに書いておきます。
分類の列ができれば、あとは選択式の設問と同じです。属性を行に、分類を列に置けば、どの層がどんな理由を書いているかが表になります。満足度が低い層に、どの分類の記述が集まっているかを見るのが、この集計のいちばん使える形です。
言葉を数える方法もあります。自由記述の列に対して、特定の言葉が入っているかどうかで1と0を付ける列を作ります。COUNTIFSの検索条件では、半角のアスタリスクをワイルドカードとして使えるので、条件の書き方を工夫すれば列を足さずに数えることもできます。ただし、言葉を数える方法は文脈を落とします。「待ち時間が短くて良かった」と「待ち時間が長かった」は、待ち時間という言葉では同じに数えられます。数えた結果は目安にとどめ、必ず元の文章を読みます。
分類を入れる作業は時間がかかります。200件程度なら1時間ほどで終わりますが、それを超えると1日仕事になります。全部に分類を付けるのではなく、満足度が低い層の記述だけを読む、直近の1か月だけを読む、といった絞り方をしても、判断に必要な材料は十分に集まります。
自由記述を報告に引くときは、原文をそのまま載せます。読みやすく直すと、書いた人の温度が消えます。個人が特定できる言葉だけを外して、あとは手を入れずに載せるのが基本です。
属性を取るための設問は、受付を始める前に決める
クロス集計でいちばん多い行き止まりは、掛け合わせる属性の設問が無いことです。満足度だけ聞いて、誰が答えたのかを聞いていなければ、何を掛けても分けられません。設問の設計は、集計の設計と同じ作業です。
先に決めるのは、「どの軸で違いを見たいか」です。イベントなら初参加か2回目以降か、申し込みの経路、参加した形式。採用なら応募の経路、希望する職種、経験年数。問い合わせなら問い合わせの種類、既存の利用者か新規か。この軸を3つまでに絞って、それぞれを選択式の設問にしておきます。
軸を増やしすぎないことも大事です。属性の設問が増えるほど回答者の手間が増え、途中でやめる人が出ます。よく聞くのは、属性を7つも8つも聞いた結果、回答数そのものが落ちて集計に耐えなくなったという話です。3つまでに絞り、残りは自由記述で拾うか、次回に回すほうが結果が使えます。
選択肢の作り方にも注意が要ります。年代を「20代」「30代」と5つに分けると、クロス集計のマスが5倍になります。あとから2つにまとめることはできますが、細かく分けなかったものを細かくすることはできません。迷ったら細かく取って、集計時にまとめる。これが原則です。
もうひとつ、属性を取ると回答者は身元が分かると感じます。匿名で本音を聞きたいアンケートで属性を細かく取ると、答えが当たり障りのないものに寄ります。何を優先するかは目的で決めてください。
集計用のシートを、回答用のシートと分ける
作業を続けていると必ず起きるのが、回答用のシートを直接いじってしまう事故です。ここを分けるだけで、やり直しの手間が消えます。
Googleフォームから繋いだシートには、回答が届くたびに新しい行が追加されます。この動きがあるため、回答の行の途中に自分で行や列を挿入すると、後から届いた回答とのずれが起きます。並べ替えを実行した場合も、その後に届く回答は末尾に追加されるので、並びが崩れます。回答用のシートは、届いたままの状態で置いておくのが原則です。
集計は別のシートで行います。同じスプレッドシートの中に集計用のシートを1枚足して、そこから回答用のシートを参照します。ピボットテーブルはソースの範囲を指定できるので、別シートに作っても問題なく動きます。関数で組む場合も、参照先を回答用のシートにしておけば、回答が増えるたびに数字が更新されます。
参照の範囲は、列の全体を指すのではなく、余裕を持った行番号まで指定する形にします。公式のヘルプでも、特定の行や列を開始と終了として指定しない参照より、範囲を区切った参照のほうが計算が速くなると案内されています。数千行のシートで全列を参照すると、開くたびに待たされるようになります。
共有の設定にも気をつけます。フォームを共同編集者と共有していると、その人はフォームにリンクされているスプレッドシートにもアクセスできます。担当が外れたときは、フォームとスプレッドシートの両方からそれぞれ外す必要があります。片方だけ外して安心していると、回答の中身は見えたままになります。
回答が増えるとクロス集計が続かなくなる地点
クロス集計は最初の数回は面白い作業ですが、続けていると止まります。止まり方には2つの型があります。
1つは、件数が増えて画面が追いつかなくなる型です。同じフォームを何年も使い回していると、1枚のシートに数年分の回答が積み上がり、概要の表示にも集計にも時間がかかるようになります。過去の分と今年の分が混ざったまま集計すると、数字の意味も曖昧になります。対策は単純で、年度や募集ごとにフォームを分けることです。Googleフォームには回答の受け付けを終了日で締める機能と、受け付ける回答数の上限を決める機能があるので、区切りを人の記憶に頼らずに作れます。過去の分を参照したいときは、シートを別に保管しておけば足ります。
もう1つは、集計そのものが目的から外れる型です。クロス集計をしたい理由は、たいてい「次に何を直すか決めたい」からです。ところが表を作る作業が毎月の仕事になると、表を作ることが目的にすり替わり、出た数字に対して誰も動かなくなります。集計の前に「この数字が出たら何をするか」を1行書いておくと、この型は防げます。
受付の実務では、集計よりも1件ごとの処理のほうが時間を食います。返信したかどうか、誰が担当なのかを追う作業です。この部分をどこでやるかを整理したい場合は、Googleフォームとの比較に、どんな使い方なら乗り換える理由が無いのかを含めて並べています。担当の割り当てや対応状況の記録といった機能の一覧はできることにあり、実際の画面は動くところを見るから登録なしで触れます。
受付の型ごとに、見るべきクロスは違う
内部リンクをたどれる範囲を見ていくと、受付の種類ごとに見るべき掛け合わせが違うことが分かります。同じクロス集計という言葉でも、何と何を掛けるかが変わります。
イベントや講座の申し込みでは、経路と参加率を掛けるのがいちばん効きます。どの案内から申し込んだ人が実際に来て、どの経路の人が来ないのか。これが分かると、次の案内でどこに力を入れるかが決まります。申し込みから当日までの流れはイベントの申し込みで確かめられます。
採用の応募受付では、応募の経路と選考の通過を掛けます。経路ごとに応募数が違うのは当たり前で、見るべきは通過率です。応募が多い経路が通過率も高いとは限らず、むしろ逆のことがよくあります。この掛け合わせを見るには、選考の結果が回答と同じ表に載っている必要があります。応募の受付でどんな並びになるかは採用の応募受付にまとめています。
問い合わせの受付では、問い合わせの種類と返信までの時間を掛けます。特定の種類だけ返信が遅れているなら、その種類の担当を決めるか、返信の定型文を用意するだけで直ります。ただしこの掛け合わせには、返信した日時が記録されている必要があります。フォームの回答には届いた日時しか入っていないので、返信の記録を別に持つか、記録が残る仕組みに変えることになります。送信されたあとの道具がそろっているかどうかが効くのはこの型で、具体的な流れは問い合わせの受付で確かめられます。
集計の道具をどこまで揃えるかで迷ったときは、費用の見当から先に付けるのが早いです。人数ごとの金額は料金に出ており、過去の回答の持ち出しや保存先についての疑問にはよくある質問に答えがあります。
Q1. Googleフォームの画面だけでクロス集計はできますか？
できません。回答タブの概要は設問ごとの単純集計を見せる画面で、属性を掛け合わせる仕組みがありません。回答用のスプレッドシートに繋いで、挿入メニューのピボットテーブル、またはCOUNTIFS関数でクロス集計の表を作ります。追加の費用はかかりません。
Q2. ピボットテーブルと関数は、どちらで作るのがよいですか？
その時点の内訳を掘って眺めたいならピボットテーブル、決まった形の表を毎回同じ場所に出したいならCOUNTIFSが向きます。毎月の報告書に数字を入れる作業があるなら、関数で組んでおけば回答が増えても貼り替えるだけで済みます。
Q3. クロス集計で割合を出すとき、分母はどう決めますか？
見たいものによって変わります。その属性の傾向を示したいなら属性ごとの人数を分母にし、答えの内訳を示したいなら全体を分母にします。報告に書くときはどちらの分母かを文の中に入れてください。母数がひと桁のセルは、割合を出さず件数だけ書くのが安全です。
Q4. チェックボックスの設問はクロス集計に使えますか？
そのままでは使えません。複数選べる設問は1つのセルにカンマ区切りで入るため、項目ごとに列を足して含まれているかどうかを1と0で置き直してから集計します。各列の合計を足した数は回答者数を超えるので、割合の分母は回答者数のままにしてください。