GoogleFormでの複数回答の扱い

2021年1月10日 16:30

※2024/02/11追記
ExcelやGoogleスプレッドシートも機能追加があって、もう少し簡単な方法があります。こちらへどうぞ

GoogleForm簡単にアンケートが作れて便利なんですが、複数回答の扱いにクセがあるように思います。もっといい方法があるのかもしれないのですが、とりあえず私はこうしようかなという説明です。

複数回答とは

図01　簡単な複数回答の例

こういうやつですね。□（チェックボックス）は複数回答が可能な項目とななります。ちなみに○（ラジオボタン）は択一になります。この場合、本当だったら（複数回答可）とか説明文につけないといけないのですが。
図01のアンケートデータがあつまると、こんな感じになります。

図02　集計データ

Google Formは回答した時間がタイムスタンプとしてA列に入るので、それを削除するとこんな感じになります。

図03　Excelにエクスポートしてタイムスタンプを削除

GoogleFormのデータをそのまま集計すると

図03はExcelにエクスポートしてあります。Googleスプレッドシートのままでも問題ないのですが、個人的にExcelの方が慣れているのでここはExcelにしました。
で、本題ですがこの状態だと集計できないのですよ。

図04　ピボットテーブルで集計した状態

なぜなら、図04のようになるからです。図04はExcelのピボットテーブルですが、Googleスプレッドシートでも同じです。
これだと例えば、「バス」、「自転車、バス」、「自転車、電車、バス」「電車、バス」は別のデータとなって、「バスを使っている人は何人？」という集計が出ないことになります。

図05　欲しいのはこっちの集計

知りたいのはこっちの集計です。「バス」、「自転車、バス」、「自転車、電車、バス」「電車、バス」じゃなくて、「バス」を使ってたらカウントして欲しいのです。
そのためには、データそのものが図03の形ではダメなのです。

複数回答の集計をするためのデータの形

図06　複数回答集計用のデータ

複数回答の集計をするには、図06のように項目があてはまれば1、あてはまらなければ0となるようなデータになっていないと、図05のような集計ができないのです。複数回答の項目（この場合なら「徒歩のみ」「自転車」「電車」「バス」）を全て列に項目として設定して、図06のような形式にする必要があります。しかし、GoogleFormの複数回答は図02のような形で入力されてしまうので、自力で図06のような形に編集するしかありません。
回答を見ながら、手作業で図03を図06のようにするのは大変ですし、入れ間違いが絶対に起きます。こういうときに、Excelの関数を使います。ここではExcelファイルにしたのでExcel関数を使っていますが、GoogleスプレッドシートもExcelと関数は同じなので、これから説明するやり方が使えます。
まず、1行目に複数回答の項目（この場合なら「徒歩のみ」「自転車」「電車」「バス」）を全て入力します。一つ一つのセルに分けて入力します。なので、複数回答の項目が多いと、ズラーッと横に長いシートになります。データの準備はこれで終わりです

項目の有無を判断して0、1入力されるようにする

図07　関数を入力して、自動で判断されるようにする

自動で判断されるようにするには、残念ながらそれ専用の関数があるわけではないので、複数の関数を組み合わせて使います。まず、FIND関数を使って、その変数があるかないかをチェックします。上の例では、B1セルの中（徒歩のみ）にA2セルの内容（自転車、電車）があるかどうかを見ています。FIND関数（FIND(B$1,$A2)）は、条件があっていればTRUEと表示され、合っていないとエラー（#VALUE）が表示されます。

このままだと、TRUEと#VALUEという表示となって、少し見にくいです。そこで、ISERROR関数とIF関数を組み合わせて、エラーになったら0、そうでないなら1が表示されるようにします。ISERROR関数は、エラーがあると1，なければ0を返す関数です。つまり、Errorが存在する（IS）かということを判定する関数なのです。図07の場合、ISERROR(FIND(B$1,$A2)はエラー（#VALUE）になるので1を返します。

そして、IF関数を組み合わせて0と1の入力を行います。IF関数は IF（条件式,成立した場合,成立しなかった場合）という関数です。なので=IF(ISERROR(FIND(B$1,$A2)=1),0,1)　はISERROR(FIND(B$1,$A2)が成立する（B1セルにA2の項目が入っていない場合、エラーになるのでISERRORが成立し1を返す）なら0を、ISERROR(FIND(B$1,$A2)が成立しない（B1セルにA2の項目が入っている場合、ISERRORは成立しないので0が返る）なら1と表示します。

探すセルと判定する内容の入っているセルに絶対参照を使用すれば、あとはコピーだけでカウントしてくれて図06のようなデータになります。そのデータに対してピボットテーブルで集計をすると、図05のような結果が得られます。

まとめ

GoogleFormの複数回答は、どうしても1セルに全部のデータが入ってしまいます。仕方が無いので、1セルになっているデータを
=IF(ISERROR(FIND(元のデータセル,カウントしたい変数のセル)=1),0,1)　
という形で判定する関数を記述して、別々のセルに別れるようにします。これはExcelでもGoogleスプレッドシートでも同じです。

可能なら=IF(FIND(B$1,$A2)=1),1,0)のように、シンプルな記述にしたいのですが、FIND関数はTRUE（1）でない場合、FALSE（0）ではなく#VALUEというエラーを返してしまうので、IF関数の条件式でそのまま使うことができません。なのでISERROR関数を使っています。もっとうまい方法があるような気がするのですが。
なお、今回はISERRORの代わりにISERR関数を使っても同じ結果になります（FIND関数が返すエラーが#VALUEなので、どちらの関数でも使用できるるため）。

この記事おしまい

追記：根本的に違うフォームの作り方で複数回答をとる方法はこちら

追記 2021/01/10 22:40 記述を一部加筆修正
追記 2021/12/21 14:25 記述を一部修正