Excel約6分で読めます

Excelで複数回答のアンケートを集計する|区切り文字で分ける

Excelで複数回答のアンケートを集計するを表したイメージ
記事の内容を表したイメージ(AI生成)

アンケートの回答欄に「A・C・E」や「読書, 映画, 旅行」のように複数の選択肢が一つのセルに入っている場合、そのままピボットテーブルにかけても「A・C・E」全体が一つの値として数えられてしまいます。選択肢ごとの集計ができず、手作業で数え直す羽目になるのが、複数回答集計でよく起きる詰まりどころです。

この記事では、区切り文字(カンマ、スラッシュ、読点など)で区切られた複数回答を「一行に一選択肢」の形に展開し、COUNTIFや作業列を使って各選択肢の回答数を出す手順を説明します。Power Queryを使う方法と、関数だけで処理する方法の両方を示します。どちらも元のデータは変えず、別シートや別列で処理するのが基本です。

作業を始める前に、元ファイルを必ずコピーして保存してください。名前に日付を付けた別ファイル(例:アンケート集計_20261021_元データ.xlsx)を手元に残しておくと、手順を誤っても元に戻せます。


複数回答データの形を確認する

まず手元のデータがどの形かを確認します。以下の三つのパターンがよく見られます。

表は横にスクロールできます

パターンセルの中身の例区切り文字
カンマ区切り読書,映画,旅行,(半角カンマ)
読点区切り読書、映画、旅行、(全角読点)
スラッシュ区切り読書/映画/旅行/

区切り文字の種類が混在していないかも確かめます。たとえば「半角カンマ」と「全角カンマ」が混在していると、後の処理で片方が抜けます。セル内をよく見るか、=LEN(A2)-LEN(SUBSTITUTE(A2,",","")) のような数式で区切り文字の個数を確認しておくと安心です(この例は半角カンマの個数を数えます)。

また、スペースが入り込んでいることがあります。読書, 映画 のようにカンマの後にスペースが付くと、後でCOUNTIFが「映画」を数えられなくなります。整形の手順はこの後説明します。


Power Queryで展開する方法(推奨)

Power Query(ExcelではPower Queryエディターとして利用できます)は、区切り文字による列の分割を繰り返し適用できるため、データが増えても更新ボタン一つで再集計できます。画面の表示は版や設定により異なります。

手順

  1. 元データを選択し、「データ」タブから「テーブルまたは範囲から」を選んでPower Queryエディターを開きます。
  2. 複数回答が入っている列(以下では「選択肢」列と呼びます)を選択します。
  3. 「列の変換」または「変換」タブにある「列の分割」→「区切り記号による分割」を選びます。
  4. 区切り記号(カンマなど)を入力し、「各区切り記号の出現」を選択、さらに「行ごとに分割」を選びます。これで一行に一選択肢の形になります。
  5. スペースが余分に入っている場合は、列を選択して「変換」タブの「トリミング」を実行します。
  6. 「ホーム」→「閉じて読み込む」で結果を新しいシートに出力します。

この操作の詳細な手順は公式のヘルプ(Microsoft Learnの Power Query ドキュメント)で確認できます。

展開後のシートはこのような形になります(架空の例です)。

表は横にスクロールできます

回答者ID選択肢
001読書
001映画
001旅行
002映画
003読書

この展開済みデータにピボットテーブルをかけると、「選択肢」ごとの件数が正しく集計されます。


関数だけで処理する方法

Power Queryが使えない環境や、件数の少ないアンケートには、作業列とCOUNTIF関数を組み合わせる方法が有効です。

スペース除去と集計の考え方

各選択肢の回答数を出したいだけなら、展開しなくてもCOUNTIFで代替できます。ただし、部分一致の誤カウントに注意が必要です。たとえば選択肢に「映画」と「映画鑑賞」が両方ある場合、ワイルドカードの *映画* は両方に反応します。選択肢に似た文字列が含まれていないかを事前に確認してください。

B列に選択肢一覧、A列に複数回答データ(行2以降)がある場合、架空の例として次の数式を作業列C2に入れます(架空の例です)。

=COUNTIF($A$2:$A$100,"*"&B2&"*")

この数式は、A列のどこかにB2の選択肢名が含まれているセルを数えます。B列を選択肢の一覧にしておけば、下にコピーするだけで全選択肢の件数が並びます。

カンマ区切りで「,映画,」の形を探したい場合は、先頭・末尾に区切り文字を付けた変換を挟む方法もあります。A列の各セルを ","&A2&"," のように補助列で包んでから COUNTIF を当てると、先頭と末尾にある選択肢の取りこぼしが減ります(架空の例です)。

=COUNTIF($C$2:$C$100,"*,"&B2&",*")

ここで C列 には =","&A2&"," を入れた補助列を用意しています。

スペースが混在している場合

=SUBSTITUTE(A2," ","") でスペースを除いた補助列を作り、その列を集計の対象にします。全角・半角を両方除きたい場合は二重にSUBSTITUTEを使います。

=SUBSTITUTE(SUBSTITUTE(A2," ","")," ","")

(外側が全角スペース、内側が半角スペースです。)

補助列を増やしすぎると管理が煩雑になります。変更の多いファイルではPower Queryの方が長く使いやすいことが多いです。


迷いやすい例外と判断基準

「その他(自由記述)」が混じっている場合

選択肢欄に「その他:〇〇」のように自由記述が含まれていると、COUNTIF系の集計では拾われず、Power Queryで展開しても一行として残ります。集計前に「その他」を含むセルを別列にフラグ立て(例:=IF(ISNUMBER(FIND("その他",A2)),1,0))し、別途手作業で確認する列として残しておくのが安全です。

回答数がゼロの選択肢を表に含めたい場合

COUNTIFはゼロも返すので、選択肢一覧をB列に手で入力しておけばゼロも集計表に現れます。Power Queryで展開する場合は、ゼロ件の選択肢は展開後に行が現れないため、ピボットの結果にも出てきません。ゼロを見せたい場合は、選択肢マスタのリストとVLOOKUPなどで照合し、件数のない選択肢も0として表示する列を別途作ります。

個人情報を含む名簿と一緒に管理している場合

名前・連絡先・所属など個人を特定できる情報が同じシートに含まれている場合、集計のために外部サービスや生成AIにファイルを貼り付けることは避けてください。個人情報の取り扱いは組織の規定を確認し、集計作業はExcel単体かイントラネット内のツールで行うのが原則です。


集計結果を確認する

集計が終わったら、合計件数と総チェック数を照合します。設問が「あてはまるものをすべて選んでください」であれば、各選択肢の件数を合計した値は、必ず総回答者数以上になります。もし総回答者数を下回っている場合は、取りこぼし(スペースの混入、区切り文字の不一致など)が起きている可能性があります。

手軽な確認の方法として、元データの各行で区切り文字の個数を数え、「選択肢数の合計」を出す列を作ります。

=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

(A2が空でない場合の選択肢数。空白行には注意してください。)

この合計と、展開後の行数または各選択肢の件数合計が一致すれば、展開が正しく完了しています。


次の行動

元データのコピーが手元にあることを確認してから、区切り文字の種類とスペースの有無を一列だけ目視で確認してください。Power Queryが使えるならまず「行ごとに分割」を試し、展開後の行数と元データの選択肢個数合計が合うかを照合します。関数だけで進める場合は補助列を一本作り、COUNTIFで一つの選択肢だけ先に試してから残りに展開すると手戻りが少なくなります。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ