ExcelのVBAを使ってカウントしてみよう!【フォントの色別にセルを数える方法】

更新:2022/09/30

ITスキル


「Excelで文字色ごとにセルをカウントしたい!」

たとえばテストの点数表で、赤文字で書かれた「赤点の人」だけ、黒文字で書かれた「合格の人」だけを数えてくれる機能があったら便利ですよね。

実は、エクセルでは「数値」をもとに計算処理をする仕様になっており、色などの書式を条件とする関数は用意されていません。そのため文字色ごとにセルをカウントするには少々手を加える必要があります。

そこで今回は、フォントの色別にセルを数える方法を3種類ご紹介。

1.VBAでカウントする
2.「関数+フィルター」でカウントする
3.「4.0マクロ+関数」でカウントする

エクセルではVBA(Visual Basic for Applications)を利用したマクロを機能させることで、より多くの作業ができるようになります。難しく感じるかもしれませんが、今回の記事でコードをコピー&ペーストで実装できるようにしていますので、ぜひ試してみてください。

「VBAと聞くだけでハードルが高い…」という人は、残りの2パターンもご覧になり、自分に合ったものから試してみてくださいね。

Excelの関連記事はこちら

VBAで文字色別にセルをカウントする方法

「フォントが赤色のセルをカウントしたい」という場合には、Excel標準関数ではカウントすることができません。VBAを使えば、以下の手順でカウントすることができます。

カウント関数をVBAでコーディングする手順

(1)エクセルを開いた状態で、「Alt + F11」キーを同時に押し、VBAウィンドウを表示します。

カウント関数をVBAでコーディングする手順1

(2)「VBAProject(エクセルファイル名)」を右クリック>「挿入」>「標準モジュール」を選択します。

カウント関数をVBAでコーディングする手順2

(3)下記のVBAのコードを入力します。

カウント関数をVBAでコーディングする手順3


Public Function CountColor(ByVal area As Range, ByVal colorCell As Range) As Long
   
Dim targetRange As Range
Dim wkCount As Long
 
wkCount = 0
 
For Each targetRange In area
    If targetRange.Font.Color = colorCell.Font.Color Then
        wkCount = wkCount + 1
    End If
Next
 
CountColor = wkCount
 
  End Function

作成したVBAコードの利用方法

(1)「=CountColor(検索範囲,検索文字色セル)」を入力します。

作成したVBAコードの利用方法1

「検索文字色セル」には、検索したい対象の文字色と同じ文字色のセルを指定します。
今回の画像の例であれば、黒字の場合は「A6」セル赤字の場合は「B6」セル青字の場合は「C6」セルですね。
セルの文字色情報を読み取り、その情報を元に検索範囲内の同一文字色のセルをカウントします。

VBAを利用する際の注意点

検索範囲の文字色を変更した場合、自動的にカウント結果に反映されません

作成したVBAコードの利用方法2

その場合は、計算セルをダブルクリックし、Enterを入力します。そうすることで再度処理が実行され、カウント結果が更新されます。

作成したVBAコードの利用方法3

作成したVBAコードの利用方法4

また、ファイルを保存する場合は、作成したコードごと保存できる「.xlsm」形式で保存してください。通常の「.xlsx」形式では、VBAコードが保存されないため、「CountColor」関数が使えなくなります。

VBAは「開発」タブがあると便利

上部のリボンに「開発」タブを追加することでより、スムーズにVBAを使用することができます。

(1)上部にあるリボンから「ファイル」を選択します。

VBAの使用をすぐに開始できる方法1

(2)「オプション」を選択します。

VBAの使用をすぐに開始できる方法2

(3)「リボンのユーザー設定」を選択し、右側の「開発」にチェックを入れます。

VBAの使用をすぐに開始できる方法3

(4)「開発」タブが表示されることを確認します。

VBAの使用をすぐに開始できる方法4

「関数+フィルター」でカウントする方法

VBAとかマクロはできれば使いたくない、という方は「関数+フィルター」の合わせ技でカウントする方法がおすすめ。ただし、こちらは一列にフィルター機能をかけていくため、データが一列に並んでいる場合に適しています
(上記のようにデータが縦3列に並んでいるような場合は、各列を計算してからすべてを合計するなどの対策が必要です。)

ここに、簡単な一覧表を用意しました。点数の下の方に「集計欄」を作っておきます。

関数+フィルターでカウント

(1)集計欄に「=SUBTOTAL(3,A2:A15)」(※実際はすべて半角)と入力しEnter

関数+フィルターでカウント2

・数式の「3」は「データの個数を求める」という指示を表します。
「A2:A15」のところは、データの範囲を指定してください。実際のデータを上から下まで選択することでも自動入力できます。

現時点では、集計欄は全員の人数である14がカウントされます。ここからフィルターをかけていきます。

(2)タイトル部分(ここでは点数)を選択してから「データ」→「フィルター」

関数+フィルターでカウント3

(3)タイトル部分に現れたボタンをクリック→「色別フィルター」

関数+フィルターでカウント4

(4)下図のように、フォントの色を「赤」と「自動」で選べます。

関数+フィルターでカウント5

(5)赤を選択すると、赤点データだけがチョイスされて集計欄は「5」になりました!

関数+フィルターでカウント6

(6)集計欄は都度変化するため、別の表にコピペでまとめていきます。「ペースト」→「値の貼り付け」にすれば、シンプルに数字の「5」として持ってくることができます。

関数+フィルターでカウント7

続いてフォントの色「自動(黒)」も同じ手順で、表を完成させます。

関数+フィルターでカウント8

「4.0マクロ+関数」でカウントする方法

3つ目の方法は「4.0マクロ+関数」でカウントする方法です。マクロを使うといっても先ほどのVBAコードのように長くないので、応用しやすいかもしれません。ただし、この方法は色をいったん数値に置き換えるための列が隣に必要です。

注)Excel4.0マクロは2022年9月時点ではMicrosoftでサポートされていますが、VBAの最新バージョンへの移行が推奨されていますので、念の為ご承知おきください。

先ほどと同じ点数表を使ってやってみましょう。

(1)隣のセル(ここではB2)を選択しておいて、「数式」→「名前の定義」と進みます。

4.0マクロ+関数1

※見え方はExcelのバージョンにより多少異なります。

(2)「名前」の部分に『color』と入力し、「参照」の部分には『=GET.CELL(24,A2)+NOW()*0』と入力し「OK」。

4.0マクロ+関数2

・上記マクロ上の「24」「フォントの色を数値化する」という指示を表します。
・そして「A2」は、B2セルの隣を指しています。

(3)B2セルに戻り『=color』と入力しEnter

4.0マクロ+関数3

(4)すると赤色を表す「3」が表示されます。そのまま下にコピーすると、黒は「1」と表示されていることが分かります。

4.0マクロ+関数4

(5)数値化できたので、これを集計していきます。COUNTIF関数を使って『=COUNTIF(B2:B15,3)』と入力。「3と書かれたセルの数を数えてください」という関数です。

4.0マクロ+関数5

関数は、上図右側のようなナビゲーションでサポートしてもらうのも分かりやすいです。

同様に、黒文字を数えるときは『=COUNTIF(B2:B15,1)』となりますね。これで完成です!B列は必要なとき以外は隠しておいても良いかもしれません。

4.0マクロ+関数6

まとめ

今回は、文字色ごとにセルをカウントするための方法を3つご紹介しました。

1.VBAを使う
2.「関数+フィルター」の合わせ技
3.「4.0マクロ+関数」の合わせ技

あなたのExcelデータに一番合いそうなものから試してみてくださいね。VBAは一見難しそうに見えますが、コピー&ペーストで実装できるのでおすすめ。一度やってしまえば、とても楽に集計ができるようになるはずですよ。

文:マイナビ学生の窓口編集部

関連記事

新着記事

もっと見る

HOT TOPIC話題のコンテンツ

注目キーワード

 ビジネス用語・カタカナ語80選

 キャリアロードマップの一歩目

  • ピックアップ