パソコンを買うなら今!最新セール情報まとめ

色別も!Excelで色付きセルをカウントする方法

色別も!Excelで色付きセルをカウントする方法

Excelで何らかの意味を持たせてセルに色を付けている場合、色の付いたセルをカウントできれば、意味のあるデータを簡単に集計できて便利です。

そこで今回は、Excelで色付きセルをカウントする方法をご紹介します。

Excelで色付きセルをカウントする方法

Excelで色付きセルをカウントする方法には、以下のようなものがあります。

  1. SUBTOTAL関数を使う
  2. 検索機能を使う
  3. ユーザー定義関数を使う

SUBTOTAL関数を使う

SUBTOTAL関数(サブトータル関数)とは、指定された方法で集計する関数です。

対応バージョン365 / 2021 / 2019 / 2016 / 2013

SUBTOTAL関数の構文

SUBTOTAL(集計方法, 範囲1, 範囲2, …)

集計方法(必須)
集計方法を1~11で指定する。
  • 1:AVERAGE
  • 2:COUNT
  • 3:COUNTA
  • 4:MAX
  • 5:MIN
  • 6:PRODUCT
  • 7:STDEV
  • 8:STDEVP
  • 9:SUM
  • 10:VAR
  • 11:VARP
範囲1(必須)
集計する範囲を指定する。
範囲2(省略可)
集計する追加の範囲を指定する。

(例)A1:B6の名簿(先頭行は見出し)から黄色で塗りつぶされた人数をカウントしてD2に結果を表示する。

D2に=SUBTOTAL(3,A2:A6)と入力します。

Excel

A1を選択して「データ」タブ⇒「フィルター」をクリックします。

Excel

A1の右の矢印をクリックし、「色フィルター」⇒黄色を選択します。

Excel

これでD2に黄色で塗りつぶされた人数が表示されます。

Excel

フィルターを元に戻すには、A1を選択して「データ」タブ⇒「クリア」をクリックします。

Excel
mono
mono

黄色で塗りつぶされたセルを抽出できればいい場合は、SUBTOTAL関数は使わず、色フィルターをかけるだけでOKです。

検索機能を使う

検索機能を使って色付きセルをカウントするには、まずカウントしたい範囲を選択⇒[Ctrl]+[H]で「検索と置換」を表示⇒「検索」タブ⇒「オプション」⇒「書式」を押します。

Excel

「書式の検索」が表示されたら、「塗りつぶし」タブを開き、カウントしたい色を選択して「OK」を押します。

Excel

「検索と置換」に戻ったら、「すべて検索」を押します。すると、該当するセルがある場合、左下に「○セルが見つかりました」というメッセージが表示されます。この数字が色付きセルのカウント結果となります。

Excel

ユーザー定義関数を使う

SUBTOTAL関数や検索機能を使うよりスマートに色付きセルをカウントするには、VBAで色付きセルをカウントする関数を作成し、その関数を使います。このようにVBAで作成した関数のことを「ユーザー定義関数」をいいます。

ユーザー定義関数を作成するには、VBEを使用します。VBEの使い方がわからない方は、まず先に以下の記事をご覧ください。

VBEを起動し、標準モジュールに以下のコードを貼り付けます。

Excel
Function CountColor(rng As Range, cellColor As Range)

    Dim buf As Range
    
    Application.Volatile
    
    CountColor = 0
    
    For Each buf In rng
        If buf.Interior.color = cellColor.Interior.color Then
            CountColor = CountColor + 1
        End If
    Next buf
    
End Function

これで、=CountColor(範囲,カウントしたい色で塗りつぶされたセル)と入力すると、範囲内の指定した色で塗りつぶされたセルがカウントされます。

コードを貼り付けたらExcelの画面に戻ります。たとえば、B2:F4の範囲で、それぞれその行のH列の色で塗りつぶされたセルをカウントし、その行のI列に結果を表示するには、I2に=CountColor(B2:F2,H2)と入力し、この数式をI4までオートフィルします。

Excel

ただし、塗りつぶしを解除したり、別のセルを新たに塗りつぶしたりしても、結果には反映されません。

Excel

結果を反映させるには、塗りつぶしを変更するごとに[F9]または[Ctrl]+[Alt]+[F9]を押す必要があります。

Excel

Excelで条件に一致したセルをカウントする方法

条件付き書式を使うなどして条件に一致したセルに色が付くようにしている場合、その条件に一致したセルをカウントすることで同じ結果を得られます。

そこで例として、

  1. ○が表示されたセルをカウントする方法
  2. 開始日から終了日までのセルをカウントする方法

この2つの方法をご紹介します。

○が表示されたセルをカウントする方法

○が表示されたセルをカウントするには、COUNTIF関数を使います。

COUNTIF関数(カウントイフ関数)とは、単一の条件に一致するセルの個数を求める関数です。

対応バージョン365 / 2021 / 2019 / 2016 / 2013

COUNTIF関数の構文

COUNTIF(範囲, 検索条件

範囲(必須)
検索する範囲を指定する。
検索条件(必須)
検索する条件を指定する。

(例)C2:C6の○の数をカウントしてE2に結果を表示する。

E2に=COUNTIF(C2:C6,"○")と入力します。

Excel

開始日から終了日までのセルをカウントする方法

開始日から終了日までのセルをカウントするには、DAYS関数を使います。この方法は、セルをカウントするというよりは、日数を数える方法になります。

DAYS関数(デイズ関数)とは、2つの日付間の日数を求める関数です。

対応バージョン365 / 2021 / 2019 / 2016 / 2013

DAYS関数の構文

DAYS(終了日, 開始日

終了日(必須)
日数の終点となる日付を指定する。
開始日(必須)
日数の始点となる日付を指定する。

(例)B列の日付からC列の日付までの期間を、それぞれ同じ行のO列に表示する。

O2に=DAYS(C2,B2)+1と入力し、この数式をO4までオートフィルします。

Excel

まとめ

今回は、Excelで色付きセルをカウントする方法をご紹介しました。

Excelの標準機能に「色付きセルをカウントする」というものはないので、標準機能で色付きセルをカウントしようとすると、スマートなやり方ではなくなってしまいます。簡単に色付きセルをカウントしたい場合は、ユーザー定義関数を使う方法がおすすめです。

ガントチャートを作成する目的で色付きセルをカウントしたいと思っている場合は、以下の記事も参考になるかもしれません。こちらは実績をセルの塗りつぶしではなく矢印で表す方法になります。