Excelで丸(〇)の数を数える方法!複数条件設定も!

Excelで丸(〇)の 数を数える方法! 複数条件設定も!
Excelのアンケート集計で、丸(○)の数を数えたいときはないでしょうか。

けど、そんな中で悩むことは、

・COUNTIF関数で○を数えようとしても、うまく数えられない
・「○○○」のように1つのセルに複数入った○を数える方法がわからない
・設問が複数あるときに、まとめて数える方法が知りたい

ですよね。

今回はそんなお悩みを解決する

・Excelで丸(○)の数を数える方法(COUNTIF・SUMPRODUCT・LEN+SUBSTITUTE)

についてまとめます!

完成イメージ

アンケート結果の○の数を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,"○")
COUNTIF関数の入力画面

Q1の○の数

Q1の○は 6個 と求められました。
カンタンですね。
Q1の○の数を数えた結果

Q2・Q3も同じ手順で数える

Q2とQ3の欄にも、範囲をずらしたCOUNTIF関数を入れます。
H3セルは

=COUNTIF(C2:C11,"○")

H4セルは

=COUNTIF(D2:D11,"○")

です。

タカヒロ
タカヒロ
となり合う列なら、H2セルをフィルハンドルで下にコピーするだけでもかまいません。

3設問の○の数がそろいましたね。
3設問すべての○の数

STEP3. 全設問をまとめて数える

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

=SUMPRODUCT((B2:D11="○")*1)

H5セルを選んで、数式バーに入力します。
SUMPRODUCT関数の入力画面

配列数式にしなくても、そのままEnterで確定できます。

全設問合計の○の数を出した結果

Q1〜Q3を合わせた○は 17個 になりました。
内訳は Q1=6個・Q2=6個・Q3=5個なので、足すと17個で合っています。

3列ぶんを1つの数式で数えられるので、設問が増えても安心です。

全設問合計の○の数

STEP4. 行ごとの○の数を数える

「この人はいくつ○を付けた?」という行ごとの数え方も見てみましょう。
E列に

=COUNTIF(B2:D2,"○")

と入力します。
B2セルからD2セルまで(その行の3設問)の中の○を数える式です。
これをE11セルまでコピーすれば、行ごとの○の数が一覧になります。

範囲が横に広がっているので、SUM関数と同じように下へコピーできます。

POINT

行ごとに数えるときは範囲を横(B2:D2)に、

設問ごとに数えるときは範囲を縦(B2:B11)にします。
同じCOUNTIFでも、範囲の向きで「行の○」と「設問の○」を切り替えられます。

行ごとの○の数

1つのセルに「○○○」と複数入っている場合の数え方

ここまでは「1セル=1つの○」でした。
けど、自由記述のように1つのセルに○が複数入っていることもあります。
この場合はCOUNTIFでは数えられません。
COUNTIFは「そのセルが○かどうか」を1つとして数えるからです。
そこで LEN関数 と SUBSTITUTE関数 を組み合わせます。
考え方はかんたんです。
「元の文字数」から「○を消したあとの文字数」を引くと、○の数が残ります。

サンプルでは B14セル に「○○○」が入っています。
C14セルを選び、次の数式を入力します。

=LEN(B14)-LEN(SUBSTITUTE(B14,"○",""))
LEN+SUBSTITUTE関数の入力画面

「○○○」は 3個、「○△○」は 2個、「✖○」は 1個 と数えられていますね。

SUBSTITUTE関数の使い方さえ覚えれば、あとはLEN関数との組み合わせなのでカンタンです。
1セル内の○の数を数えた結果

複数条件で数えたいとき(COUNTIFS)

「Q1とQ2の両方に○を付けた人は何人?」のように、条件を2つ以上にしたいときは COUNTIFS関数 を使います。
集計表のH7セルに次の数式を入れます。

=COUNTIFS(B2:B11,"○",C2:C11,"○")

COUNTIFSは、範囲と条件のペアをカンマでつなげて増やせます。
今回は 3人 という結果になりました。
条件が増えるほど便利な関数なので、覚えておくと集計の幅が広がります。
COUNTIFSで複数条件を数えた結果

4つの数え方の比較

ここまでに出てきた数え方を表にまとめました。
数えたい単位に合わせて選ぶと迷いません。

使う関数 数えられるもの 向いている場面
COUNTIF 1つの範囲にある○の数 設問ごと・行ごとに○を数える
COUNTIFS 複数条件を満たす○の数 「Q1とQ2の両方が○」を数える
SUMPRODUCT 複数列をまとめた○の数 全設問の合計を1つの数式で出す
LEN+SUBSTITUTE 1セル内に入った○の数 「○○○」のように複数入っているとき
POINT

迷ったら、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つで集計の準備が整います。

タカヒロ

タカヒロ
○を数えるだけならCOUNTIFでじゅうぶんです。

条件が増えたり、セルの中に○が複数あるときだけ、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で丸(○)の数を数える方法(COUNTIF・SUMPRODUCT・LEN+SUBSTITUTE)

についてまとめました。

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

ぜひ試してみてください。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。






コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

CAPTCHA ImageChange Image