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

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

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

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

集計に利用する数式

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

Googleフォームの場合

=COUNTIF(B:B,D2)+COUNTIF(B:B,"*, "&D2&", *")+COUNTIF(B:B,D2&", *")+COUNTIF(B:B,"*, "&D2)

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

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",*中野*)にすれば、式１つで全部のパターンいけるじゃん！
・・・と思うかもしれませんが、新中野とか中野坂上もカウントしてしまうのでNGです。

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

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

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

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

Microsoft Forms用の数式について

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

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

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

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