GoogleFormやMicrosoftFormの複数回答の集計をもっと簡単にする方法
もっと簡単というのは、簡単でない方法がここにあるからですｗ
GoogleスプレッドシートやExcelにデータの分割機能が実装されたので、それを使うと複雑に関数を組まなくても複数回答の集計ができます。まあ、手間がかかるのと、複数回答なのでそこから検定とかに使うには難があるという本質的な問題は変わりませんが。
なお、プロセスとしては
・1セルにまとまっているデータを分割する
・分割したデータを1，0のデータにする
という2段階です。後のプロセスでCOUNTIF関数を使います。
GoogleFormやMicrosoftFormで複数回答のデータをとった場合
前のnoteでも書きましたが、GoogleFormやMicrosoftFormで複数回答のデータをとった場合、データは1セルに突っ込まれます。
GoogleFormだと、カンマ（,）と半角スペース2文字で区切られてデータが入ります。MicrosoftFormの場合はここがセミコロン（;）1文字だけの区切りとなります。
データの分割機能を使うと楽
で、GoogleスプレッドシートやExcelなどの表計算アプリは、「一つのセルの中身は一つの塊」と思うので、上の例で言うと「自転車, 電車」と「電車, バス」の「電車」は同じデータとは認識してくれないわけです。それが困るので、前のノートのように関数を駆使して、エラーができないようにデータを分割したりしていたわけです。
ところが、最近のGoogleスプレッドシートやExcelにデータの分割機能が実装されたので、それを使ってデータを分割しておいて、でそのセルの中身をcountif関数で数えるという少し楽できる方法が使えます。
データの分割方法
データの分割は簡単で、分割したい範囲を選択してメニュー選択をするだけです。ExcelでもGoogleスプレッドシートでもできます。ExcelはWeb版（Microsoft365）でも使えます。まずは、Excelを使って集計する場合を説明します。
Excelの場合
Excelの場合は、データを選択してから「データ」の「区切り位置」をクリックします。
最初に、セル内のデータが何で区切られているかを指定するので「元のデータの形式」の「データのファイル形式を選択してください」を「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」をチェックします。
次に区切り文字を指定します。ここで、以下のように指定します。
・MicrosoftFormでデータをとった場合
区切り文字は 「セミコロン」
・GoogleFormでデータをとった場合
区切り文字は 「コンマ」 と 「スペース」 の2つ
「連続した区切り文字は1文字として扱う」にチェック
Googleスプレッドシートはコンマの後ろにスペースがついていて、これを指定しないと、後で集計の時に面倒なことになります。また「連続した区切り文字は1文字として扱う」にチェックをつけておかないと、区切りが2個連続することになり空白の列ができてしまいます。
区切り文字を指定したら、「完了」をクリックして構いません。
Googleスプレッドシートの場合
Googleスプレッドシートは、やはりデータを選択してから「データ」メニューの下から3つめにある「テキストを列に分割」をクリックします。
Googleスプレッドシートの場合は、区切り文字はうまいこと判定してくれることが多いようで、すぐに分割してくれます。うまくいかないときは分割直後に下の方に「区切り文字」の設定メニューがフロートで表示されるので、デフォルトの「自動的に検出」を変更します。私が試した限りではそのままで分割できなかったことはありませんでした。もし、手動でこの画面から分割を行う場合は、
・MicrosoftFormでデータをとった場合
区切り文字は 「セミコロン」
・GoogleFormでデータをとった場合
区切り文字は 「コンマ」
となります。Googleスプレッドシートでは、GoogleFormで取ったデータの区切りのスペースは無視するようで、設定しないで分割されます。基本は「自動的に検出」で問題ありません。
もっとも、MicrosoftFormでデータを取ってGoogleスプレッドシートでデータを処理するケースはレアだと思いますが。
Web（Microsoft365）版のExcel
Web版のExcelはアプリ版のExcelとちょっと画面が違うんですよね。PCやMacでアプリ版のExcelを使っている方はほぼ使わないと思うのですが、iPadでExcel使っていると、iPadアプリのExcelにない機能があるんでたまに使ったりします。ということで。
Web版のExcelでMicrosoftFormのデータを分割する場合は簡単で、「データ」メニューから「テキストを列に分割する」をクリックします。クリックすると図08のように区切り文字を設定する画面になります。MicrosoftFormで取ったデータの場合は、自動で「セミコロン」に判定されているので、判定されていればそのまま、判定されていないときは「セミコロン」をチェックして「適用する」をクリックすれば、分割されます。
問題は、GoogleFormで取ったデータを分割する場合です。普通はそのまま
Googleスプレッドシートを使うと思うので、レアケースだと思うのですが。
どういうことかというと、Web版のExcelのデータ分割には「連続した区切り文字は1文字として扱う」の設定がないので、図09のように「コンマ」と「スペース」を両方指定すると空の列ができてしまいます。「コンマ」だけにすると後ろ側のデータの最初に半角スペースがついてしまい、「スペース」だけにすると前のデータの後ろにコンマが残ります。後で集計するときにとても具合が悪いです。
解決方法としては
・「コンマ」と「スペース」を両方指定し、空の列は後で削除
・先に半角スペースを削除しておく
のどちらかです。後者のほうはデータ記述の中に半角スペースが入っているとそちらも削除されてしまう危険があるので、個人的には前者がおすすめです。まあ、分割にGoogleスプレッドシートを使えば無問題なのですが。
分割したら集計 の前にしなくてはいけないこと
分割されたら集計なのですが、そうはいかないのです。分割したデータは順番がバラバラになります。図10でわかりやすいのは「バス」の回答ですが、最初のセルや2番目のセル、3番目のセルにも「バス」があります。これだと同じ内容が違う列に存在して集計ができません。
なので、図11のようにして、0と1にしたデータでピボットテーブルとかで集計をしたいのです。
余談ですが、GoogleFormは選択肢の順番にデータが得られますが、MicrosoftFormはチェックした順番になります。この質問の選択肢は、「徒歩のみ」「自転車」「電車」「バス」の順です。GoogleFormの場合は「バス」が「徒歩のみ」「自転車」「電車」より前になることはありません。ところがMicrosoftFormの場合はチェック順になるので、「バス」を先にチェックして後から「電車」をチェックすると「バス」が「電車」の前に来ます。
図11のようなデータにするのは、COUNTIF関数を使います。GoogleスプレッドシートでもExcelでも同じです。ただ、分割しているのでエラー処理が不要となるので、単純にCOUNTIF関数を使うだけとなります。
COUNTIF関数は先に範囲を指定して、次に検索条件を指定します。範囲内に検索条件に当てはまる物があると1を返し、なければ0を返します。範囲指定のセル番地の＄マークは絶対参照です。絶対参照についてはこちらとかこちらやこちらを参照してください。
こつは、図12のように1行目に選択肢の行を作っておくことです。
これで準備ができたので、あとはピボットテーブルなどで集計します。
選択肢に「その他」があったらどうするか
GoogleFormでもMicrosoftFormでも、選択肢に「その他」を設定できます。そして自由に記述ができるのですが、それは「その他」としてはカウントされません。それはそのはずで、COUNTIF関数で数えているので、「その他」という記述がない限り「その他」が1になることはありません。
図14の上2行は「その他」に自由記述があって、1行目の回答は「車で送ってもらう」で2行目は電車は選択肢ですが、その他に「バイク」という答えがあります。その他は何が記述されるかわからないので、記述内容をすべて用意して集計するのは現実的ではありません。せめて、その他の数がどれだけあるか、どの回答者にその他があるかが把握できれば、集計作業が少しは楽になります。
さて、その他のカウントですが、あまりいい方法がないのですが私は以下の方法で逃げています。
「その他」の数を出したいセルで、元の分割した解答のある範囲を、COUNTA関数を使って回答数を数えます。COUNT関数ではなくてCOUNTA関数なのは、文字のあるセルをカウントするためです。COUNT関数は数値のあるセルしか数えませんが、COUNTA関数は文字のあるセルの数もカウントしてくれます。そして、あるなしを1，0にしたセルの合計でCOUNTAの結果を引きます。こうすることで、回答数から選択肢を選んでいる数を引くことになり、結果として回答数にはあるけど選択肢にはない解答＝その他ということで、その他の数をカウントできます。
ただ、その他の数はわかりますが、その他の中身はわからないのでその他のある回答者だけ選んで、なんと答えているか探す必要はあります。
なお根本的にはその他が多くなるのは、選択肢の設計に問題がある可能性が高い（回答者の多くが回答する内容が選択肢にない）ことが多いです。
また、調査する側からすると選択肢以外の情報を得る必要がないのなら、その他を設けない方がいいでしょう。いずれにしろ、これは質問紙設計の話になるのでこれ以上は深入りしません。
なお、複数回答のデータをExcelやGoogleスプレッドシートのピボットテーブルで集計する方法はこちらから。
まとめ
Formで集めた複数回答のデータは、そのままではうまく集計できません。
1つのセルに入っているデータを分割する
選択肢ごとに選ればれているかどうか1，0に置き換える
その他の扱いは考える
基本的にはこのステップが大事だと思います。また、そもそも複数回答でなければいけないのか、その他が必要なのかということは質問紙設計の段階で考えておく必要があります。
余談ですが、私が学部・院生だった頃は、500件程度のアンケートデータを集めるにも、印刷代や送信・返送の郵送代などで最低10万円程度が必要でした。Webでのアンケートは、実施やデータ入力の金銭的負担が格段に少ないのでとても素晴らしいと思います。質問紙調査のハードルは確実に低くなったでしょう。しかし、必要なデータがちゃんと取ることができるのか、倫理的な基準は満たしているかということは方法にかかわらず考えなくてはいけないことです。作成したアンケートフォームは、誰かに見てもらって、問題がないか確認するようにしましょう。
この記事おしまい
この記事が気に入ったらサポートをしてみませんか？