【Excel】エクセルで複数回答を集計する方法（カンマ区切りのデータを数える）

アンケートや申込フォームの回答をExcelに取り込むと、複数選択した項目が「りんご,みかん,ぶどう」のように1つのセルへカンマ区切りで入ることがあります。

通常のCOUNTIF関数では、このような複数回答を正確に数えられず、集計表の件数が合わないこともあります。

この記事では、カンマ区切りで入力された複数回答を、選択肢ごとに重複なく集計する方法を、数式、区切り位置、Power Query、Microsoft 365の関数まで順番に解説します。

集計する前に、元データに半角カンマと全角カンマが混在していないか、余分な空白が入っていないかも確認しておきましょう。

 

カンマ区切りの複数回答を数える数式

それではまず、元の回答表をそのまま残しながら、選択肢ごとの件数を数える基本数式について解説していきます。

A列	B列	E列	F列
回答者	好きな果物	集計項目	件数
田中	りんご,みかん	りんご	3
佐藤	ぶどう,りんご	みかん	2
鈴木	みかん	ぶどう	2

 

SUMPRODUCTとSEARCHを組み合わせる集計式

最も汎用性が高い方法は、SUMPRODUCT関数とSEARCH関数を組み合わせる方法です。

ここでは、1行目に見出しがあり、複数回答がB2からB100まで、集計したい選択肢がE2に入力されているものとして考えます。

F2セルへ次の数式を入力します。

入力後、F2セルの右下に表示される小さな四角を下方向へドラッグすると、E列の選択肢ごとの件数をまとめて表示できます。

この式では、まずSUBSTITUTE関数によって回答内の半角スペースを削除します。

フォームの回答では「りんご, みかん」のようにカンマ後ろへ空白が入ることがあり、そのままでは文字列比較で一致しない場合があるためです。

次に、回答データの先頭と末尾へカンマを追加し、検索したい選択肢にも前後のカンマを付けます。

たとえばB2セルが「りんご,みかん」、E2セルが「りんご」の場合、SEARCH関数は「,りんご,」を「,りんご,みかん,」から探します。

この判定により、セル内の単なる一部文字列ではなく、カンマで区切られた1つの回答項目として一致しているかを確認できます。

SEARCH関数は文字列を見つけると位置の数字を返し、見つからない場合はエラーを返します。

ISNUMBER関数は位置の数字をTRUE、エラーをFALSEへ変換し、二重マイナス記号がTRUEを1、FALSEを0へ変換します。

最後にSUMPRODUCT関数が1の合計を計算するため、該当する回答者数が求められます。

この方法の利点は、元の表を分解せずに計算できることです。

回答者ごとの原票を維持したい場合や、集計対象が少数の場合には特に扱いやすいでしょう。

ただし、B列全体のように範囲を指定すると計算量が増えるため、実際のデータ件数に合わせてB2:B1000などの必要な範囲を設定することが大切です。

 

部分一致による誤集計を防ぐ区切り記号

カンマ区切りの集計で特に注意したいのが、部分一致による誤集計です。

たとえば「東京」と「東京都」、「営業」と「営業企画」のように、短い選択肢が別の選択肢に含まれるケースがあります。

単純にSEARCH関数で「東京」を検索すると、「東京都」も該当として数えてしまいます。

そこで、前後へカンマを付けて「,東京,」という形で検索する必要があります。

これなら「,東京都,」の中に「,東京,」は存在しないため、別の項目として正しく扱えます。

集計式で使用している「”,”&E2&”,”」と「”,”&回答範囲&”,”」は、この誤集計を防ぐための重要な部分です。

選択肢の文字が似ているアンケートほど、前後のカンマを省略しないことが正確な集計につながります。

また、最後の回答だけを選んだ人や、最初の回答だけを選んだ人も正しく数えられます。

前後のカンマを追加していないと、「りんご」だけが入ったセルでは、検索文字列の末尾カンマが合わずに見つからないことがあります。

回答文字列の両端へカンマを補うことで、先頭、途中、末尾のどの位置にある選択肢も同じルールで判定可能です。

全角カンマの「，」が含まれているデータでは、半角カンマだけを探しても一致しません。

その場合は、SUBSTITUTE関数をもう1つ重ねて、全角カンマを半角カンマへ置換します。

この数式では、内側のSUBSTITUTE関数が全角カンマを半角カンマへ直し、外側のSUBSTITUTE関数が不要な半角スペースを削除します。

データの入力ルールが不統一な職場では、このような補正を式へ組み込むと、集計作業のやり直しを減らせます。

ただし、選択肢そのものにカンマを含める運用は避けましょう。

区切り記号と選択肢の文字が同じになると、どこで項目が分かれているのか判定できなくなるためです。

運用を決める段階で、選択肢の名称にはカンマを使用せず、複数選択の区切りは半角カンマに統一すると安全です。

 

数式をコピーする前の参照範囲

数式を入力するときは、固定するセルと動かすセルを区別することが必要です。

複数回答があるB2:B100の範囲は、どの選択肢を集計するときも同じです。

そのため、式では$B$2:$B$100のようにドル記号を付けた絶対参照にします。

一方で、集計項目が入力されたE2セルは、下へコピーしたときにE3、E4へ変わる必要があります。

こちらはE2のまま相対参照にしておきます。

F2セルに式を入力したら、Enterキーで確定し、F2セルを選択します。

セル右下のフィルハンドルをダブルクリックすると、隣接するE列の入力済み行まで数式が自動でコピーされます。

集計項目の途中に空白セルがある場合は、ダブルクリックでは途中までしかコピーされないことがあります。

そのようなときは、コピー元のF2セルをコピーし、貼り付けたいF3:F20などの範囲を選択して貼り付けます。

式をコピーした後は、合計値も確認しましょう。

複数回答では1人が複数の項目を選べるため、各選択肢の件数合計は回答者数を超えることがあります。

これは異常ではなく、複数選択集計では自然な結果です。

回答者数と選択回数の合計は別の指標として扱うことが、報告資料を作る際の基本になります。

回答者数を別に表示したい場合は、氏名や回答IDが入ったA列をCOUNTA関数で数える方法が使えます。

空白を含む可能性がある場合や、重複を除いた回答者数が必要な場合は、別途UNIQUE関数やピボットテーブルで確認する方法もあります。

数式の結果がすべて0になる場合は、E列の選択肢に余分な空白が入っていないか、B列で使われているカンマが半角か全角かを見直してください。

セルをクリックして数式バーを確認すると、見た目では分かりにくい空白や記号の違いを発見しやすくなります。

 

COUNTIF関数を使った簡易的な集計

続いては、データの内容が単純な場合に使えるCOUNTIF関数による簡易的な数え方を確認していきます。

B列の回答例	E列の項目	使用場面
赤,青	赤	選択肢名が重複しない場合
青,黄	青	概算を素早く確認する場合

 

ワイルドカードを利用するCOUNTIF式

COUNTIF関数では、検索条件にアスタリスクを付けることで、セル内に指定した文字が含まれている件数を数えられます。

複数回答がB2:B100にあり、集計項目がE2にある場合、F2セルに次の式を入力します。

この式は、B2:B100の中でE2の文字を含むセルを数えます。

アスタリスクは任意の文字列を表すワイルドカードであり、検索語の前後にどのような文字があっても一致できます。

たとえばE2が「赤」で、B2が「赤,青」なら該当します。

数式が短く、Excelを使い始めた人でも理解しやすいことが大きな利点です。

一方で、この方法は「赤」と「赤色」のように選択肢名が重なる場合に誤集計します。

「赤」を検索すると「赤色」も数えてしまうため、厳密な集計には向きません。

COUNTIFのワイルドカードは文字を含むかどうかを調べるだけで、区切り位置までは判定しません。

選択肢が「はい」「いいえ」「未回答」のように明確に異なり、他の項目に含まれないなら、COUNTIFでも実用的です。

しかし、商品名、部署名、地域名など、名称の一部が重なる可能性があるデータでは、前章のSUMPRODUCTとSEARCHを使う方法が安心です。

集計表を他の人へ渡す場合は、式が簡単だからという理由だけでCOUNTIFを選ばず、項目の将来的な追加も考えておきましょう。

後から「東京西部」のような新しい選択肢が加わると、以前は問題なかった「東京」の集計が突然ずれることがあります。

集計対象が一度きりの少量データなのか、毎月追加される業務データなのかによって、選ぶ方法を変えるのが実務的です。

 

完全一致だけを数えるCOUNTIFの使い分け

回答セルに選択肢が1つだけしか入らない場合は、ワイルドカードを使わず完全一致で数えます。

たとえば、B列が「満足」「普通」「不満」という単一回答だけで構成されているなら、F2セルへ次の式を入力します。

この数式では、B列とE2セルの文字列が完全に同じ場合だけを数えます。

「満足,普通」のように複数回答が1セルへ入るデータでは一致しないため、結果は0となります。

ここを理解せずにCOUNTIFの完全一致式を使うと、データがあるのに件数が表示されず、数式が壊れたように見えるかもしれません。

実際には数式の故障ではなく、データ構造と集計方法が合っていない状態です。

集計前には、B列のセルを数件クリックし、数式バーで回答の形式を確認しましょう。

カンマが含まれていれば複数回答形式であり、完全一致のCOUNTIFではなく別の方法を選びます。

改行で複数回答が入っている場合もあります。

セル内改行は見た目で気付きにくいため、数式バーで確認することが大切です。

改行区切りなら、CHAR(10)を区切り文字として使った式へ調整する必要があります。

データを作成する側が入力規則を設定できるなら、単一回答と複数回答を別列に分ける設計も有効です。

ただし、既にGoogleフォームや外部システムから取り込んだCSVを処理する場合は、元データの形に合わせて集計式を選ぶことになります。

数式を決める前に、1セルの中に回答が何個入る設計かを確認することが、集計ミスの予防になります。

単一回答と複数回答が混在していても、SUMPRODUCTとSEARCHの方法なら両方を数えられます。

将来データの仕様が変わる可能性があるなら、初めから複数回答にも対応できる式を採用しておくと修正が少なく済みます。

 

COUNTIFで集計結果を確認する注意点

COUNTIFを使って概算件数を出した場合でも、結果をそのまま確定値として扱う前に確認作業を行いましょう。

特に、選択肢の追加や名称変更があった期間のデータでは、表記ゆれが混ざりやすくなります。

「オンライン」「オンライン参加」「オンライン　参加」のように入力者ごとに表現が異なると、同じ意味でも別の回答として扱われます。

COUNTIFの式は表記の違いを自動で理解しません。

集計表の隣に候補語一覧を作り、実際の回答をフィルターで絞り込んで確認すると、想定外の表記を見つけやすくなります。

Excelのフィルターは、B列の見出しセルを選択してからデータタブのフィルターをクリックすると設定できます。

フィルターの検索欄へ選択肢名を入力すれば、該当しそうな回答だけを目視で確認できます。

この確認は、数式を疑うためではなく、元データの品質を確認するための作業です。

複数回答の集計では、1件の入力ミスが複数の集計結果へ影響することもあります。

たとえば「りんご,みかん,,ぶどう」のようにカンマが連続している場合、数式によっては空の項目が含まれているように見えることがあります。

回答を整形する工程を用意すると、後続のグラフ作成や割合計算も安定します。

簡易式で件数を出した後は、件数の大きい項目と似た名称の項目を優先して確認すると効率的です。

少数のデータではCOUNTIFでも問題が表面化しにくい一方、数百件を超えるとわずかな誤集計が資料全体の印象を変えることがあります。

正式な報告資料には、可能であればSUMPRODUCT式、Power Query、または分割後のピボットテーブルによる集計結果を採用しましょう。

 

TEXTSPLIT関数による複数回答の分割

続いては、Microsoft 365やExcel 2021以降で使えるTEXTSPLIT関数を利用し、複数回答を分割して数える方法を確認していきます。

B列	分割後のイメージ
りんご,みかん,ぶどう	りんご　みかん　ぶどう
みかん,りんご	みかん　りんご

 

TEXTSPLITとTOCOLを使う集計数式

Microsoft 365では、TEXTSPLIT関数でカンマ区切りの文字を分割し、TOCOL関数で1列に並べ直す方法が使えます。

元データがB2:B100、集計したい選択肢がE2にある場合は、F2セルへ次の数式を入力します。

TEXTSPLIT関数は、指定した区切り記号で文字列を分割する関数です。

この例ではカンマを指定しているため、「りんご,みかん」は「りんご」と「みかん」に分かれます。

複数行の回答範囲を処理すると、分割結果が横方向と縦方向へ広がることがあります。

そこでTOCOL関数を使い、分割された結果を1列の一覧へ変換します。

TOCOL関数の第2引数に1を指定すると、空白セルを無視できます。

最後にCOUNTIF関数が、その一覧の中からE2と同じ項目を数えます。

この方法は、回答を実際に項目単位へ分けてから数えるため、複雑な集計へ発展させやすい特徴があります。

分割後の一覧を別セルへ表示すれば、どの選択肢が何回出現したかを目視で確認しやすくなります。

ただし、TEXTSPLIT関数は古いExcelでは利用できません。

数式を他の人へ共有する場合は、相手のExcelバージョンも確認しましょう。

 

空白と全角カンマを整える前処理

TEXTSPLIT関数を使う場合も、区切り記号や空白の表記ゆれには注意が必要です。

「りんご, みかん」のようにカンマ後ろへ空白があるデータを分割すると、「 みかん」の先頭に空白が残ることがあります。

この状態で「みかん」とCOUNTIFで比較すると、文字列が完全に同じではないため数えられません。

そのため、TRIM関数を組み合わせて分割後の余分な空白を取り除きます。

SUBSTITUTE関数は全角カンマを半角カンマへ置き換えます。

TEXTSPLIT関数は統一された半角カンマで文字列を分割します。

TOCOL関数で縦1列へまとめた後、TRIM関数が各項目の前後にある空白を削除します。

最後にCOUNTIF関数が集計項目と比較します。

このように処理を順番に重ねると数式は長くなりますが、どの段階で何を直しているかを理解すると管理しやすくなります。

全角スペースが混ざっているデータでは、TRIM関数だけでは取り切れない場合があります。

その場合は、SUBSTITUTE関数で全角スペースを半角スペースへ置き換えてからTRIM関数を使う方法があります。

入力フォームの仕様を変更できる場合は、回答の出力形式を半角カンマに統一するのが最善です。

一方、過去データをすぐに集計する必要がある場合は、数式側で吸収するほうが早いでしょう。

データをきれいに整える処理と、件数を数える処理を分けて考えると、エラーの原因を探しやすくなります。

まず置換後の文字列を別列に表示し、次に分割結果を確認し、最後にCOUNTIFで数えるという順に検証すると安心です。

 

UNIQUE関数で選択肢一覧を自動作成する方法

あらかじめ集計項目の一覧が用意されていない場合は、分割した回答からUNIQUE関数で選択肢一覧を作れます。

たとえばH2セルに次の式を入力すると、B2:B100の複数回答を分割して重複のない項目一覧を表示できます。

UNIQUE関数は同じ文字を1つにまとめ、SORT関数は結果を並べ替えます。

この一覧が作成できたら、隣のI2セルにCOUNTIF式を入力し、表示された選択肢に対応する件数を計算します。

スピル機能に対応したExcelでは、H2から下方向へ一覧が自動で広がります。

そのため、H2の下側に入力済みのセルがあるとスピルエラーになります。

一覧を表示する場所は、十分な空白がある列を選びましょう。

自動作成された一覧には、入力ミスによる項目もそのまま出てきます。

たとえば「みかん」と「みかん 」が混在していれば、本来は同じ項目でも別々に表示される可能性があります。

TRIM関数を入れておくことで、前後の半角スペースによる重複は減らせます。

ただし、「蜜柑」と「みかん」のような表記そのものの違いは自動統合されません。

結果を確認し、必要なら置換機能や対応表を使って表記を統一します。

UNIQUE関数で一覧を自動生成すると、想定していなかった回答や表記ゆれの発見にも役立ちます。

定期的に回答が増えるファイルでは、範囲をテーブル化しておくと更新がさらに楽になります。

元データ範囲を選択してCtrlキーとTキーを押し、先頭行をテーブルの見出しとして設定すると、追加された行を数式の対象へ反映しやすくなります。

 

区切り位置とピボットテーブルによる集計

続いては、数式を複雑にしたくない場合に便利な、区切り位置とピボットテーブルを使う集計方法を確認していきます。

回答者	元の回答	分割列の例
田中	りんご,みかん	りんご　みかん
佐藤	ぶどう,りんご	ぶどう　りんご

 

区切り位置でカンマごとに列へ分ける操作

Excelの区切り位置機能を使うと、カンマ区切りの文字を複数の列へ分けられます。

元データを残すため、まずB列を右側の空いている列へコピーしてから作業するのがおすすめです。

コピーした列を選択し、データタブの区切り位置をクリックします。

表示された画面で「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選び、次へ進みます。

区切り文字の画面で「コンマ」にチェックを入れます。

データによっては「スペース」にもチェックを入れたくなりますが、選択肢名に空白を含む場合は分割され過ぎるため注意しましょう。

プレビューで「りんご」「みかん」のように区切られていることを確認し、完了をクリックします。

すると、1つのセル内にあった複数回答が横方向の列へ分かれます。

この操作は分かりやすい反面、元の列の右側にあるデータを上書きする危険があります。

区切り位置を実行する前に、分割先の右側列が空いているかを必ず確認してください。

空いていない場合は、あらかじめ新しいシートへコピーするか、必要な数だけ列を挿入します。

回答数が人によって異なる場合、最大で何個まで選ばれているかによって必要列数が変わります。

選択肢が最大5個なら、少なくとも5列分の空きが必要です。

分割後の列には空白セルが生じますが、これはその回答者がその位置の選択肢を選ばなかったことを示します。

次の工程でピボットテーブルを使うには、横へ分かれたデータをさらに縦へ並べ直す必要があります。

 

横に分けた回答を縦に並べ替える方法

ピボットテーブルで項目別件数を集計するには、基本的に1行へ1つの回答がある縦長の表が扱いやすくなります。

区切り位置で横に分けただけでは、同じ人の複数回答が複数列へ散らばった状態です。

少量のデータであれば、分割した列をコピーして1列へ貼り付け、空白を除外する方法でも対応できます。

たとえばD列からH列へ分割された回答を、それぞれ別の場所へ値として縦に貼り付けます。

すべての列を貼り付けた後、データタブのフィルターで空白を除外すれば、選択肢が1列に並びます。

貼り付け時は、数式ではなく値として貼り付けると、元データの変更による参照切れを避けられます。

ただし、この手作業は回答数が多い場合や毎月更新する場合には向きません。

大量データならPower Queryの列のピボット解除を使うほうが確実です。

縦長の表では、1列目に回答者ID、2列目に選択肢という構成にすると、選択肢の件数だけでなく、誰が何を選んだかも追跡できます。

同じ回答者が誤って同じ選択肢を2回含めていた場合も、縦長表なら発見しやすくなります。

複数回答を集計しやすいデータ構造は、1セルに複数項目が入った横持ち形式ではなく、1行に1項目が入った縦長形式です。

今後も継続してアンケートを実施するなら、集計用のシートでは縦長の形式へ変換する流れを標準化するとよいでしょう。

元のフォーム出力は保管用として残し、集計専用シートで加工するようにすれば、原票を失わずに済みます。

 

ピボットテーブルで件数を表示する手順

選択肢が1列にまとまったら、ピボットテーブルで項目ごとの件数を表示できます。

縦長にした表の中の任意のセルをクリックし、挿入タブからピボットテーブルを選択します。

表示された作成画面では、テーブルまたは範囲が正しいことを確認し、新規ワークシートを選ぶと作業しやすくなります。

OKをクリックすると、新しいシートにピボットテーブルのフィールド一覧が表示されます。

選択肢のフィールドを「行」エリアへドラッグし、同じ選択肢フィールドを「値」エリアへもドラッグします。

値エリアが「合計」になった場合は、値フィールドの設定を開き、「データの個数」を選択します。

これで、選択肢ごとの出現回数が一覧になります。

ピボットテーブルでは、件数が多い順へ並べ替えたり、グラフを作成したりできます。

行ラベルのセルを右クリックし、並べ替えから降順を選ぶと、人気の項目を上位から確認できます。

元データを更新した後は、ピボットテーブルを右クリックして更新を実行します。

更新しない限り、新しい回答は集計へ反映されません。

ピボットテーブルは自動更新ではないため、データ追加後には更新操作が必要です。

回答者数に対する選択率を出したい場合は、件数を回答者総数で割る計算を別セルで行います。

複数選択形式では、すべての割合を合計すると100パーセントを超えることがあります。

表やグラフに注記を付け、複数回答であることを明示すると誤解を防げます。

 

Power Queryで大量データを整形する方法

続いては、毎月のアンケートや大量のCSVファイルを扱う場合に便利なPower Queryによる整形方法を確認していきます。

元データ	Power Query後
田中　りんご,みかん	田中　りんご
佐藤　ぶどう,りんご	田中　みかん
鈴木　みかん	佐藤　ぶどう

 

Power Queryへ元データを取り込む手順

Power Queryは、Excel内のデータを取り込み、分割、置換、並べ替えなどの加工を記録できる機能です。

元データの範囲を選択し、CtrlキーとTキーを押してテーブル化します。

テーブルの作成画面で「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認してOKをクリックします。

次に、データタブを開き、「テーブルまたは範囲から」をクリックします。

Power Queryエディターが開き、元の表がプレビュー表示されます。

複数回答が入った列の見出しをクリックし、ホームタブまたは変換タブから列の分割を選択します。

区切り記号による分割を選び、区切り記号にコンマを指定します。

このとき、詳細設定で「行に分割」を選択することが重要です。

列に分割を選ぶと、回答が横方向へ広がり、集計しやすい縦長形式になりません。

Power Queryでは、カンマで分割した結果を行に分割する設定を選ぶことで、1回答を1行へ展開できます。

操作が完了すると、同じ回答者名が複数行に複製され、各行に1つずつ選択肢が入ります。

これはデータの重複ではなく、複数回答を正しく表すための構造です。

元データはExcelシート上に残っているため、Power Query内の加工を失敗しても原票を復元できます。

取り込む範囲をテーブル化しておけば、新しい回答行を追加した後に更新するだけで、同じ処理を繰り返せます。

 

空白行と表記ゆれをPower Queryで修正する方法

分割後のデータには、空白や余分なスペースが残ることがあります。

Power Queryエディターでは、複数回答の列を選択して変換タブを開き、書式からトリムを選ぶと、文字列の前後にある空白を削除できます。

クリーンを選ぶと、印刷できない文字を取り除くこともできます。

データを外部フォームやCSVから取り込んだ場合は、見えない制御文字が含まれることもあるため、トリムとクリーンを順に実行すると安心です。

全角カンマが混ざっている場合は、列を分割する前に値の置換を使います。

変換タブまたはホームタブの値の置換を開き、検索する値へ全角カンマ、置換後の値へ半角カンマを入力します。

その後に半角カンマで分割すれば、記号の違いを吸収できます。

空の回答がある場合は、フィルターの矢印からnullや空白を除外します。

空白を残したままピボットテーブルへ渡すと、「空白」という行ラベルが表示され、集計表が見づらくなることがあります。

Power Queryの変換手順は右側の適用したステップに記録されるため、どの加工を行ったか後から確認できます。

設定を間違えた場合は、適用したステップの削除ボタンで該当処理だけを取り消せます。

シート上で何度も手作業の置換を繰り返すより、処理内容が残るPower Queryのほうが、定期業務では再現性を保ちやすいでしょう。

表記ゆれを完全に自動で統合することは難しいため、よくある誤記は置換ルールとして追加し、判断が必要な項目は別途確認する運用が現実的です。

 

集計表へ読み込んで更新する手順

データの整形が終わったら、Power Queryエディターのホームタブから閉じて読み込むをクリックします。

通常はテーブルとして新しいワークシートへ読み込む設定を選ぶと、元データと加工済みデータを分けて管理できます。

読み込まれた縦長の表を使い、挿入タブからピボットテーブルを作成します。

選択肢の列を行と値に配置すれば、項目別件数を集計できます。

翌月の回答を元のテーブルの末尾へ追加した場合、まずPower Queryの出力テーブルを右クリックして更新します。

次にピボットテーブルも右クリックして更新すると、新しい回答が件数へ反映されます。

Power Queryだけ更新しても、ピボットテーブルが以前の状態のままの場合があります。

反対にピボットテーブルだけ更新しても、Power Queryの出力が更新されていなければ新規回答は反映されません。

更新の順番は、元データ、Power Query、ピボットテーブルの順です。

定期作業の手順書へこの順番を書いておくと、担当者が変わっても集計結果を再現しやすくなります。

Power Queryはデータを整える仕組み、ピボットテーブルは整えたデータを集計する仕組みとして役割を分けて考えましょう。

元データの列名を変更すると、Power Queryのステップでエラーになる場合があります。

運用中のファイルでは、見出し名をむやみに変更しないことも大切です。

どうしても変更する場合は、クエリの設定を開き、参照している列名を修正します。

初めて導入する場合は小さなサンプルデータで操作を試し、期待した行数と件数になるか確認してから本番データへ適用すると安全です。

 

複数回答の集計結果を確認するポイント

続いては、集計式やピボットテーブルで出した件数を正しく活用するための確認ポイントを解説していきます。

確認項目	確認内容
回答者数	回答IDや氏名の件数
選択回数	すべての選択肢件数の合計
選択率	選択肢件数÷回答者数

 

回答者数と選択回数の違い

複数回答を集計するときは、回答者数と選択回数を混同しないように注意します。

回答者が100人いて、1人あたり平均2項目を選んだ場合、選択肢の件数合計はおおよそ200件になります。

この200件は回答者数ではなく、選ばれた回数の合計です。

各選択肢の件数を合計して100を超えたとしても、数式のエラーではありません。

報告書では「複数回答可」「回答者100人」「選択総数200件」のように区別して表記すると、読み手が数値を理解しやすくなります。

各選択肢の選択率を出すには、項目の件数を回答者数で割ります。

ここでF2が選択肢の件数、H2が回答者数です。

H2は下へコピーしても変わらないよう、ドル記号を付けて絶対参照にします。

セルの表示形式をパーセンテージへ変更すると、割合として見やすく表示できます。

選択率の合計は100パーセントを超えることがあります。

複数回答の選択率は、全回答に対する構成比ではなく、回答者のうちその項目を選んだ人の割合です。

円グラフは全体で100パーセントになる構成比を表す用途に向くため、複数回答の選択率をそのまま円グラフへ表示すると誤解を招きます。

複数回答の結果は、横棒グラフや縦棒グラフで項目ごとの割合を比べる形式が分かりやすいでしょう。

グラフのタイトルにも「複数回答」「回答者数を分母とする」などの注記を入れると、資料の信頼性が高まります。

 

重複回答と表記ゆれの確認

同じ回答者が同じ項目を2回入力している場合、単純な件数集計では2回として数えられます。

たとえば「りんご,りんご,みかん」という回答があると、分割して集計する方法ではりんごが2件増えます。

アンケートの仕様上、同一項目を重複選択できないはずなら、元データの入力エラーとして確認が必要です。

Power Queryで分割したデータには回答者IDも残しておき、回答者IDと選択肢の組み合わせで重複を確認すると見つけやすくなります。

Excelの条件付き書式や重複の削除機能を使う場合も、元データを直接書き換える前にコピーを作成しましょう。

重複の削除は取り消しにくく、意図しないデータまで消してしまう可能性があります。

表記ゆれも同様に、集計前に確認するべき要素です。

「Web」「WEB」「ウェブ」、「メール」「Eメール」のような表記が混ざると、別の項目として集計されます。

数式は入力された文字を忠実に数えるだけで、意味が同じかどうかは判断しません。

定期アンケートでは、選択肢をフォームのプルダウンやチェックボックスで固定し、自由入力を最小限にすることが有効です。

既存データを修正する場合は、置換前後の対応表を作り、どの表記をどれへ統一したか記録しておくと、後から説明しやすくなります。

件数が大きく変わった項目は、数式変更だけでなく、表記統一や重複除外の影響も確認しましょう。

集計ルールが変わった月は、前年同月との単純比較ができなくなる可能性もあります。

 

集計方法を選ぶ判断基準

カンマ区切りの複数回答を集計する方法は、データ量、Excelのバージョン、更新頻度によって選びます。

少量で一度だけ集計する場合は、SUMPRODUCTとSEARCHを使った数式が手軽です。

選択肢名が重ならず、概算をすぐ確認したいだけならCOUNTIFも使えます。

Microsoft 365を利用していて、関数で分割結果も確認したい場合はTEXTSPLIT、TOCOL、UNIQUEの組み合わせが便利です。

毎月データを追加し、ピボットテーブルやグラフまで作成するなら、Power Queryで縦長形式へ変換する方法が向いています。

他の担当者が作業を引き継ぐ予定があるなら、数式のセルだけが残る方法より、処理手順を記録できるPower Queryやピボットテーブルのほうが理解されやすいこともあります。

反対に、環境によってPower Queryが使えない、または利用者が数式に慣れている場合は、基本式を集計用シートへ整備するほうが管理しやすいでしょう。

最も高度な方法ではなく、データの更新方法と利用者に合う方法を選ぶことが重要です。

どの方法でも、元データを直接加工し続けるのではなく、原本、加工用、集計用のシートを分けておくと事故を防げます。

原本シートには外部から取得したデータを保管し、加工用シートで表記を整え、集計用シートで件数やグラフを表示する構成が分かりやすいでしょう。

ファイル名やシート名にも対象月を含めると、更新時に古いデータを混ぜるミスを減らせます。

作業が終わったら、代表的な選択肢を数件だけ手作業で数え、数式またはピボットテーブルの結果と一致するか確認します。

この小さな照合を行うだけで、範囲指定ミスや区切り記号の違いによる集計漏れを早期に発見できます。

 

まとめ エクセルでカンマ区切りの複数回答を集計する方法

エクセルでカンマ区切りの複数回答を数えるときは、単純な完全一致のCOUNTIF関数だけでは対応できません。

選択肢名が重なる可能性がある場合は、前後にカンマを付けて検索するSUMPRODUCTとSEARCHの数式が有効です。

Microsoft 365なら、TEXTSPLIT関数とTOCOL関数で回答を分割してからCOUNTIF関数で数える方法も使えます。

データ量が多い場合や、毎月の集計を繰り返す場合は、Power Queryで行に分割し、ピボットテーブルで件数を集計すると効率的です。

複数回答では、回答者数と選択回数の合計が一致しないことを前提に集計することも忘れないでください。

全角カンマ、余分な空白、表記ゆれ、重複回答を確認し、元データを残したうえで加工用シートや集計用シートを作ると、正確で再利用しやすい集計表になります。