Excelのアンケート集計で、丸(○)の数を数えたいときはないでしょうか。
けど、そんな中で悩むことは、
・「○○○」のように1つのセルに複数入った○を数える方法がわからない
・設問が複数あるときに、まとめて数える方法が知りたい
ですよね。
今回はそんなお悩みを解決する
についてまとめます!
完成イメージ
アンケート結果の○の数を1つずつ目で追って数えていると、数え間違いや時間のムダにつながります。
今回は
・各設問ごとの○の数と、全設問をまとめた○の合計を関数で出す方法
をゴールにします。
設問ごと・行ごと・全設問合計と、見たい単位で○を数えられるようになります。
なお、1つのセルに「○○○」と複数入っている場合の数え方や、うまくいかないときの対処法(トラブルシュート)もあわせてご紹介します。
早速実装して試してみましょう!

上の画像が完成形です。各設問の○の数と、全設問合計の○の数(17個)が一覧で出ています。
もくじ
STEP1. 元データを用意する
まずは数えたいアンケート結果の表を用意します。
1行目が見出しで、A列が回答者、B〜D列が設問(Q1〜Q3)です。
各セルには ○ / ✖ / △ のどれかが入っています。
今回は10人分のデータを使います。
○が複数ある行もあるので、あとで行ごとの数え方も見てみましょう。

下の表をコピーして、Excelのシートに貼り付けるとそのまま試せます。
| 回答者 | Q1 | Q2 | Q3 | 行の○数 |
|---|---|---|---|---|
| 佐藤 | ○ | ✖ | ○ | |
| 鈴木 | ○ | ○ | △ | |
| 高橋 | ✖ | ○ | ○ | |
| 田中 | ○ | △ | ✖ | |
| 伊藤 | ○ | ○ | ○ | |
| 渡辺 | ✖ | ✖ | ○ | |
| 山本 | ○ | △ | △ | |
| 中村 | △ | ○ | ✖ | |
| 小林 | ○ | ○ | △ | |
| 加藤 | ✖ | ○ | ○ |
※ 数式で求める列は空にしています。すでに値が入っている列はそのまま残して試せます。
STEP2. COUNTIF関数で○の数を数える
まずは1つの設問(Q1)の○の数を数えてみましょう。
使うのは COUNTIF関数 です。
「B2セルからB11セルの中で、○はいくつある?」という感じで数えてくれます。
どこに入れるか迷わないよう、集計用の表をG列・H列に作りました。
数式はどのセルに入れる?
集計表の H2セル を選び、数式バーに次のように入力します。
=COUNTIF(B2:B11,"○")
Q1の○の数
Q1の○は 6個 と求められました。
カンタンですね。

Q2・Q3も同じ手順で数える
Q2とQ3の欄にも、範囲をずらしたCOUNTIF関数を入れます。
H3セルは
=COUNTIF(C2:C11,"○")H4セルは
=COUNTIF(D2:D11,"○")です。
3設問の○の数がそろいましたね。

STEP3. 全設問をまとめて数える
「Q1〜Q3を合わせて○は全部でいくつ?」という結果をを出したいときは SUMPRODUCT関数 を使います。
COUNTIFは1つの範囲しか数えられませんが、SUMPRODUCTなら複数列をまとめて判定できます。
集計表の合計欄(H5セル)に次の数式を入れます。
=SUMPRODUCT((B2:D11="○")*1)H5セルを選んで、数式バーに入力します。

配列数式にしなくても、そのままEnterで確定できます。
全設問合計の○の数を出した結果
Q1〜Q3を合わせた○は 17個 になりました。
内訳は Q1=6個・Q2=6個・Q3=5個なので、足すと17個で合っています。
3列ぶんを1つの数式で数えられるので、設問が増えても安心です。
STEP4. 行ごとの○の数を数える
「この人はいくつ○を付けた?」という行ごとの数え方も見てみましょう。
E列に
=COUNTIF(B2:D2,"○")と入力します。
B2セルからD2セルまで(その行の3設問)の中の○を数える式です。
これをE11セルまでコピーすれば、行ごとの○の数が一覧になります。
範囲が横に広がっているので、SUM関数と同じように下へコピーできます。
行ごとに数えるときは範囲を横(B2:D2)に、
設問ごとに数えるときは範囲を縦(B2:B11)にします。
同じCOUNTIFでも、範囲の向きで「行の○」と「設問の○」を切り替えられます。
1つのセルに「○○○」と複数入っている場合の数え方
ここまでは「1セル=1つの○」でした。
けど、自由記述のように1つのセルに○が複数入っていることもあります。
この場合はCOUNTIFでは数えられません。
COUNTIFは「そのセルが○かどうか」を1つとして数えるからです。
そこで LEN関数 と SUBSTITUTE関数 を組み合わせます。
考え方はかんたんです。
「元の文字数」から「○を消したあとの文字数」を引くと、○の数が残ります。
サンプルでは B14セル に「○○○」が入っています。
C14セルを選び、次の数式を入力します。
=LEN(B14)-LEN(SUBSTITUTE(B14,"○",""))
「○○○」は 3個、「○△○」は 2個、「✖○」は 1個 と数えられていますね。
SUBSTITUTE関数の使い方さえ覚えれば、あとはLEN関数との組み合わせなのでカンタンです。

複数条件で数えたいとき(COUNTIFS)
「Q1とQ2の両方に○を付けた人は何人?」のように、条件を2つ以上にしたいときは COUNTIFS関数 を使います。
集計表のH7セルに次の数式を入れます。
=COUNTIFS(B2:B11,"○",C2:C11,"○")COUNTIFSは、範囲と条件のペアをカンマでつなげて増やせます。
今回は 3人 という結果になりました。
条件が増えるほど便利な関数なので、覚えておくと集計の幅が広がります。

4つの数え方の比較
ここまでに出てきた数え方を表にまとめました。
数えたい単位に合わせて選ぶと迷いません。
| 使う関数 | 数えられるもの | 向いている場面 |
|---|---|---|
| COUNTIF | 1つの範囲にある○の数 | 設問ごと・行ごとに○を数える |
| COUNTIFS | 複数条件を満たす○の数 | 「Q1とQ2の両方が○」を数える |
| SUMPRODUCT | 複数列をまとめた○の数 | 全設問の合計を1つの数式で出す |
| LEN+SUBSTITUTE | 1セル内に入った○の数 | 「○○○」のように複数入っているとき |
迷ったら、1セルに○が1つならCOUNTIF、
条件を増やしたいならCOUNTIFS、
複数列の合計ならSUMPRODUCT、
1セルに○が複数ならLEN+SUBSTITUTEがおすすめです。
VBAで○の集計式をまとめて入れる
毎回同じアンケートを集計するなら、VBAで数式をまとめて入れるのがおすすめです。
Alt+F11でVBAエディターを開き、標準モジュールに次のコードを貼り付けます。
Sub CountMaru()
Dim r As Long
Range("H2").Formula = "=COUNTIF(B2:B11,""○"")"
Range("H3").Formula = "=COUNTIF(C2:C11,""○"")"
Range("H4").Formula = "=COUNTIF(D2:D11,""○"")"
Range("H5").Formula = "=SUMPRODUCT((B2:D11=""○"")*1)"
For r = 2 To 11
Cells(r, 5).Formula = "=COUNTIF(B" & r & ":D" & r & ",""○"")"
Next r
End Sub
ボタンに登録しておけば、クリック1つで集計の準備が整います。
条件が増えたり、セルの中に○が複数あるときだけ、COUNTIFSやLEN+SUBSTITUTEの出番です。
まずはCOUNTIFから試してみてくださいね。
うまくいかないときの対処法(トラブルシュート)
実際に試すと、思ったように数えられないこともあるかもしれません。
よくあるつまずきと対処法をまとめました。
| 症状 | 原因 | 対処 |
|---|---|---|
| ○の数が0になる | 全角の○と、半角の〇や◯が別の文字として扱われている | 表記をどちらかに統一する/=COUNTIF(B2:B11,”○”)+COUNTIF(B2:B11,”〇”) のように足す |
| 空白セルまで数えてしまう | COUNTIFの範囲がデータ行より広い/条件を “” にしている | 範囲をB2:B11のようにデータ行ぴったりにする/空白はCOUNTBLANKで別に数える |
| 「はい」「いいえ」を数えたい | 数えたいのが記号ではなく文字 | 条件を =COUNTIF(B2:B11,”はい”) のように文字にする |
| 条件を2つにしたい | COUNTIFは条件を1つしか持てない | COUNTIFSに切り替えて、範囲と条件のペアを増やす |
| コピーしたら数がずれた | 参照範囲がずれた(相対参照) | 範囲を固定したいときは $B$2:$B$11 のように絶対参照にする |
関数の説明
今回使う関数の概要です。
書式と注意点を押さえておきましょう。
COUNTIF関数(カウントイフ)
| 書式 | =COUNTIF(範囲, 検索条件) |
|---|---|
| できること | 指定した範囲の中で、条件に一致するセルの個数を数えます。 |
| 戻り値 | 条件に合ったセルの数(数値) |
| こんなときに | 設問ごと・行ごとに○の数を数えたいとき。 |
| 注意 | 1つの範囲に1つの条件だけです。条件を2つ以上にしたいときはCOUNTIFSを使います。 |
=COUNTIF(B2:B11,"○")
SUMPRODUCT関数(サムプロダクト)
| 書式 | =SUMPRODUCT(配列1, [配列2], …) |
|---|---|
| できること | 配列どうしを掛けて、その合計を返します。(範囲=”○”)*1 と書くと、○を数える道具にもなります。 |
| 戻り値 | 計算結果の合計(数値) |
| こんなときに | 複数の列をまたいで、○の合計を1つの数式で出したいとき。 |
| 注意 | 配列数式にしなくてもそのまま確定できます。ただし範囲のサイズはそろえてください。 |
=SUMPRODUCT((B2:D11="○")*1)
SUBSTITUTE関数(サブスティチュート)/LEN関数
| 書式 | =SUBSTITUTE(文字列, 検索文字列, 置換文字列)/=LEN(文字列) |
|---|---|
| できること | SUBSTITUTEは指定した文字を置き換え、LENは文字数を返します。 |
| 戻り値 | 置き換え後の文字列/文字数(数値) |
| こんなときに | 1つのセルに「○○○」のように○が複数入っているとき。 |
| 注意 | =LEN(B14)-LEN(SUBSTITUTE(B14,”○”,””)) の引き算の順番を間違えないようにしましょう。 |
=LEN(B14)-LEN(SUBSTITUTE(B14,"○",""))
STEP5. 完成
アンケート集計表の完成です。
各設問の○の数と、全設問合計の○の数(17個)が一覧で出ました。
行ごとの○の数もE列に入り、誰が多く○を付けたかも一目で分かります。
手で数える作業から離れられるので、集計ミスも減らせます。
思ったとおりに動きましたか?

さいごに
いかがでしょうか。
今回は、
についてまとめました。
まずは今回のサンプル表で、ぜひ一度試してみてください。
設問ごと・行ごと・全設問合計と使い分けられるようになると、アンケート集計がぐっとラクになりますよ。
また、他にも便利なExcelの時短テクニックがありますので、よろしければご参照頂ければと思います。
ぜひ試してみてください。














コメントを残す