メディア掲載: レバテックフリーランス様のサイトで当ブログが紹介されました

カンマやセミコロンで区切られた回答データを集計する方法(アンケートフォームの複数選択など)

GoogleフォームやMicrosoft Formsでアンケートを行う際、「複数選択」(チェックボックス)はよく使いますよね。こんな感じのやつです。

Googleフォームの複数選択(チェックボックス)
Googleフォームだと、複数選択(チェックボックス)の設問はこんな感じ

回答データをスプレッドシートやExcelで集計しようとした際、複数選択(チェックボックス)の回答は、区切り文字(カンマやセミコロン)で区切って1つのセルにまとめられてしまうため、集計に苦戦することがあるかと思います。

Googleフォームの複数選択(チェックボックス)質問の回答をスプレッドシートに出力した
Googleフォームの集計結果をスプレッドシートに出力した場合。
複数選択して回答すると、1つのセルにカンマ区切りで出力される。(例: 中野, 中野坂上 )
Microsoft Formsの複数選択(チェックボックス)質問の回答をスプレッドシートに出力した
Microsoft Formsの集計結果をExcelに出力した場合。
複数選択して回答すると、1つのセルにセミコロン区切りで出力される。(例: 中野;中野坂上; )

上図のようなデータから、「中野」を選んだ回答の数、「中野坂上」を選んだ回答の数・・・を数える方法を説明します。

集計に利用する数式

まず、空いている列に各選択肢(新中野、中野、中野坂上)を入力します。
次に、その右のセルに数式を入力しました。

Googleフォームの場合

COUNTIFを使って、Googleフォームの複数選択の回答から、各選択肢の件数を数える
Googleフォームの場合のサンプル
=COUNTIF(B:B,D2)+COUNTIF(B:B,"*, "&D2&", *")+COUNTIF(B:B,D2&", *")+COUNTIF(B:B,"*, "&D2)

数式内のB:Bは回答が記載されている列、D2は選択肢を記載したセルです。ご自身のデータにあわせて変更してください。

Microsoft Formsの場合

COUNTIFを使って、Microsoft Formsの複数選択の回答から、各選択肢の件数を数える
Microsoft Formsの場合のサンプル
=COUNTIF(F:F,"*;"&H2&";*")+COUNTIF(F:F,H2&";*")

数式内のF:Fは回答が記載されている列、H2は選択肢を記載したセルです。ご自身のデータにあわせて変更してください。

数式の説明

COUNTIF関数の基本

とある範囲から、条件に合致するセルの数を数えるには、COUNTIF関数を利用します。
=COUNTIF("B:B","中野")とすると、B列から中野完全一致するセルの数を算出できます。
あくまで完全一致なので、例えば新中野, 中野 のように、他の文字が入っているセルはカウントされません。

COUNTIF関数で部分一致するセルを数える

COUNTIF関数は、検索条件にアスタリスク*を使用することで、部分一致するセルの数を算出することができます。

例えば、
=COUNTIF("B:B","中野, *")とすれば、中野, から始まるセル(例:中野, 中野坂上)の数を、
=COUNTIF("B:B","*, 中野")とすれば、, 中野で終わるセル(例:新中野, 中野)の数を、
=COUNTIF("B:B","*, 中野, *")とすれば、, 中野,が他の選択肢の間に入っているセル(例:新中野, 中野, 中野坂上)の数を数えることができます。

=COUNTIF("B:B",*中野*)にすれば、式1つで全部のパターンいけるじゃん!
・・・と思うかもしれませんが、中野とか中野坂上もカウントしてしまうのでNGです。

Googleフォーム用の数式について

COUNTIFを使って、Googleフォームの複数選択の回答から、各選択肢の件数を数える(解説)

Googleフォームの回答をスプレッドシートに出力した場合の特徴は下記です。

  • 複数選択した場合、, (カンマ+半角スペース)で区切られる
  • 1つしか選ばなかった場合は、区切り文字, はつかない
  • 末尾には区切り文字, がつかない

これらを考慮すると、例えば中野を識別する条件は4パターンあげられます。
その4パターンそれぞれのCOUNTIF式を作り、それらを+で足し合わせればOKですね。

NoパターンCOUNTIF式
(「中野」を直接指定)
COUNTIF式
(「中野」ではなくD3セルを指定)
1中野=COUNTIF("B:B","中野")=COUNTIF("B:B",D3)
2…他の選択肢, 中野, 他の選択肢…=COUNTIF("B:B","*, 中野, *")=COUNTIF("B:B","*, "&D3&" *")
3中野, 他の選択肢…=COUNTIF("B:B","中野, *")=COUNTIF("B:B",D3&", *")
4…他の選択肢, 中野=COUNTIF("B:B","*, 中野")=COUNTIF("B:B","*, "&D3)

Microsoft Forms用の数式について

COUNTIFを使って、Microsoft Formsの複数選択の回答から、各選択肢の件数を数える(解説)

Microsoft Formsの回答をExcelに出力した場合の特徴は下記です。

  • 複数選択した場合、;(セミコロン)で区切られる
  • 1つしか選ばなかった場合でも、区切り文字;がつく
  • 末尾にも区切り文字;がつく

これらを考慮すると、例えば中野を識別するパターンは2パターンあげられます。
その2パターンそれぞれのCOUNTIF式を作り、それらを+で足し合わせればOKですね。

NoパターンCOUNTIF式
(「中野」を直接指定)
COUNTIF式
(「中野」ではなくH3セルを指定)
11) …他の選択肢;中野;他の選択肢…;
2) …他の選択肢;中野;
=COUNTIF("F:F
","*;中野;*")
=COUNTIF("F:F","*;"&H3&";*")
21) 中野;
2) 中野;他の選択肢…;
=COUNTIF("F:F","中野;*")=COUNTIF("F:F",H3&";*")

上述のGoogleフォームよりも中野を識別するためのパターンが少ないですね。
その理由は、Microsoft Formsから出力したExcelデータでは、必ず末尾に区切り文字;が付与されるため、中野;中野の2つが発生しないためです。(中野;;中野;になる)

コメント

タイトルとURLをコピーしました