色付きセルの件数をカウントする [CountColor]

ユーザー定義関数

35-1.png



前回記事 [ 色付きセルのみを合計する
SUMIFのように動かしたSumColorですが、今度はCOUNTIF版を作ってみます。
もともとこの定義関数を作るきっかけになったのは、次年度の勤務表を作成する為に休日日数をカウントしたい。というのが目的でした。
私の会社では年間休日日数というのが決まっています。その日数を大型連休につぎ込むのか、3連休を作るのか・・・・思案するのですが、カレンダーで色の付けた日=休日なのでその休日日数を関数で合計できれば、年間カレンダーの作成が楽になるのです。

前回の問題点

前回の問題点として、縦1列しか合計できませんでした。
カレンダーのような2次元テーブルに対応できなかったのが重大な問題でした。
そこで前回のコードではRowsしか使っていなかったんですが、Columnsと組み合わせる事で対応してみます。
Rowは縦、Columnは横の意味です。
A1:C10の範囲指定の中で、Rows(3).Columns(2) となればB3セルのことを示します。

CountColor

関数名
CountColor( 計算範囲 , 条件色セル )
機能
計算範囲の中で条件色セルと同じ色のセルをカウントする
範囲指定は複数行、複数列可能

Function CountColor(計算範囲, 条件色セル)
 Application.Volatile
 CountColor = 0
 For y = 1 To 計算範囲.Columns.Count
 For x = 1 To 計算範囲.Rows.Count
 If 計算範囲.Rows(x).Columns(y).Interior.ColorIndex = 条件色セル.Interior.ColorIndex Then
 CountColor = CountColor + 1
 End If
 Next
 Next
End Function

アレンジすればいろいろな事が出来そうですね。こんなのに使えた!という報告いただければ嬉しいです。

前回記事 [ 色付きセルのみを合計する [SumColor] ]
関連記事 [ 自分で関数を作ってみる

2014/1/30追記
F9の再計算に対応していない為、対応出来る様コードを追加

Application.Volatile
をFunctionプロシージャに入れる事でF9の再計算の対象となります。
通常の状態だとユーザー定義関数は再計算の非対象です。これはExcelの仕様だとMicrosoftは謳っています。

アドインダウンロード

ダウンロードページに当記事で紹介したCountColorのアドインファイルを掲載しております。

Comment

  1. でんぼ より:

    「できると良いな~」くらいの気持ちで探していたので、コピペしただけで実現できてうれしいです。
    簡単でわかりやすくて、とても助かりました。どうもありがとう!

  2. 清家 秀章 より:

    はじめまして
    相模原市の清家と申します。よろしくお願いします。
    2013年の勤務カレンダーを作成しておりましたところ、検索で目にとまりました。例示と同じエクセル表を色を付けた休日セルをカウントしたいのですが、初心者です。具体的に関数計算はどのように入力すればいいのでしょうか?教えていただけませんでしょうか?

  3. isakat より:

    > はじめまして
    > 相模原市の清家と申します。よろしくお願いします。
    > 2013年の勤務カレンダーを作成しておりましたところ、検索で目にとまりました。例示と同じエクセル表を色を付けた休日セルをカウントしたいのですが、初心者です。具体的に関数計算はどのように入力すればいいのでしょうか?教えていただけませんでしょうか?
    実際のセルに入力する式は
    [色:0000FF]=CountColor(B3:H8,K3)[/色]
    になります。
    ただ元々Excelにある関数では無いので、VBAを設定してください。
    設定方法はこちらの記事参考。
    http://excel-magic.com/blog-entry-21.html

  4. 橋本 より:

    はじめまして。
    会社の2013年度カレンダー作成しておりました。
    セルの色の集計したかったので大変助かります。
    ただ、自分のやり方がまずいのか、、、
    セルの色を変更してもすぐに計算式に反映されないのです。
    計算式のセルをダブルクリックすると再計算されます。
    ちなみにExcelは2010です。
    ご教授願います。

  5. kouhei より:

    はじめまして
    CountColor大変活用させて頂いております。
    教えてくださいm(_ _)m
    結合したセルを一つのセルとしてカウントすることは
    可能でしょうか?

  6. ツル より:

    色を変えるとリアルタイムにカウントするように出来ますか?

  7. nozawa より:

    すみません、初心者です。
    上記のプログラムをそのまま写したのですが、「VALUE」エラーが出てしまいます。
    SUMCOLORは問題なかったのですが・・・。
    お手数ですが、何か良い方法はないかご教示ください。

  8. Isakat より:

    当方の環境ではそのまま貼り付けても問題なく出ました。
    Excel2007です。
    そちらバージョンは何をお使いですか?

  9. 悩み相談 より:

    この関数でとても助かりました。出勤日数の管理が簡単になりました。ありがとうございました。
    色の数を増やした場合にF9とかで簡単に更新したいです。
    教えてください。よろしくお願いします。

  10. 管理人(isakat) より:

    確かにF9で再計算されないのは不便ですね。
    記事に追記しましたのでそちらで対応お願いします。

  11. BIGINER より:

    とても便利に使えてます。
    これにさらに、色+条件でカウントできるとなお良いのですが・・・
    セルの色が赤かつ数値「5」をカウウントするとか

  12. こっこ より:

    条件付きで変更したセルカラーには 反応しないようです。
    解決策はありませんか?
    50を超えたセルは赤色に
    70を超えたセルは水色に表示する設定を行いました。
    赤色の水色 各々のセル数をカウントしたかったのですが、
    すべて無色(デフォルト)として認識するようです。。

  13. 便利ですね より:

    この関数使いました。とても便利ですね。
    さらに、この色かつこの内容もカウントできるとなおいいですね。
    (赤色の50とか緑色の70とか)
    あと、他に書かれているかもいましたが、条件付で変更したカラーもカウントできるといいですね。
    自分で作れれば良いのですが・・・・

  14. お願いします。 より:

    結合されているセルを1として数えることはできないでしょうか?
    お願いします。

  15. 初心者でございます。質問です。 より:

    こちら(↓)をそっくりそのままモジュールに追加すれば、Countcolorが使えるようになるのでしょうか??

    Function CountColor(計算範囲, 条件色セル)
      Application.Volatile
      CountColor = 0  For y = 1 To 計算範囲.Columns.Count
      For x = 1 To 計算範囲.Rows.Count
        If 計算範囲.Rows(x).Columns(y).Interior.ColorIndex = 条件色セル.Interior.ColorIndex Then
          CountColor = CountColor + 1
        End If
      Next
      Next
    End Function
    

    仕事の効率化を考えてサイトを探し、こちらのブログに立ち寄らせていただきました。
    初心者すぎて《関数を自分で作ってみる》の記事もいまいち理解できませんでした…すみません(・・;)

  16. 管理人(isakat) より:

    はい、それをそっくりそのままモジュールに追加すれば使えます。
    CountColor = 0 For y = 1 To 計算範囲.Columns.Count
    ここは、Forの前で改行してくださいね。
    CountColor = 0
    For y = 1 To 計算範囲.Columns.Count

  17. 曽我 より:

    私も条件付きで色をつけたセルをカウントしようとして
    このサイトを利用しようとしたのですが
    そもそも、それこそCOUNTIFで計算できますね

  18. わたなべ より:

    非常に便利そうなのですが
    どうしても16番の通りに行っているつもりですが
    #VALUE!エラーが出てしまいます
    エクセル2013ではだめですか?

    • isakat より:

      コードはそのままモジュールにコピペされたという事ですよね。
      では、セルの計算式はどのようになっていますか?

  19. きらら より:

    とても簡単にできました。
    ありがとうございます。

  20. 名無し より:

    使わせて頂いております。
    とてもシンプルで分かり易くて、他のサイトと比較しても一番使いやすいです。
    ありがとうございました。

  21. 活用したい! より:

    はじめまして

    今回、まさに色付けしたセルをカウントしたいくてこのページを探しました。

    ダウンロードしたファイルでデータを作成してもエラーになってしまいます。

    VBAの標準モジュールに張り付けて以下のような関数を打っても#VALUE!になってしまいます。

    いろいろサイトをみて、頑張ってみたのですができません。
    教えていただきたくよろしくお願いいたします。

    =CountColor(C4:AG4,3)

  22. 匿名 より:

    使わせていただいております。

    最初、自分で関数を作るという事が全くわからず、
    エクセルにある「関数」でなく、オリジナルなモノを創造した
    という事がわかってから感動しております。

    本当に簡単にできました。
    ありがとうございました。

  23. しまりす より:

    通常のセル書式 で色付けしたものをカウントできるので 感動しました。ただ、条件書式で 色付けしたセルを カウントすることはできなかったのですが、VBA記述によっては可能でしょうか?

    • isakat より:

      条件付書式の色セルをカウントする方法としては、むしろその条件付書式の内容をカウントしてしまえば良いのでは?と思いました。
      たとえばセルが”あいうえお”という文字であれば赤色にする、という条件付書式であれば、ただ単に”あいうえお”のものをカウントすれば良いので
      =COUNTIF(範囲,”あいうえお”)
      で良いかなと。
      ただし簡単な条件の時には有効ですが、複雑な条件設定されてるとCOUNTIFもややこしくなってきますよね、、

      条件付書式で変わったセルの色をカウントするVBAを作ることは出来ます。少々お時間いただければ。

  24. しまりす より:

    早速のご回答ありがとうございます。

    同じ形式の表(個人表)が 複数 縦にならんでおります。
    ラベルみたいなイメージ。
    構造と列種別は同じですが、表としては 間があいており連続していません。
    個人表は多人数分あります。
    ひとり分の表の中で、エラーとして 認識する条件があり そこに色をつけさせています。
    この飛び飛びの 個人票に 色がつく=エラーがある ということで、なければ まるっと印刷、エラーーありなら 見に行く感じにしたいと思っています。
    個人表に呼んできている条件自体が そこそこ複雑なので 色で判別したいとおもっております。

    わかりづらい説明かもしれませんが、もしも可能でしたらうれしいです。

    • isakat より:

      条件が複雑であるので結果の書式で判断したい、という事ですね。なるほど。

      色々と弄ってみたのですが、Excel2010以降であればDisplayFormatオブジェクトというものが使え、条件付書式発動後のセル色を取得出来ます。
      しかしこれはユーザー定義関数では動かないようです・・・(Excelの仕様の問題ですね)
      ですので、対応としてはCountColorのユーザー定義関数で対応するのではなく、マクロを動かす必要があります。
      たとえば以下のマクロを都度チェック時に動かせばどうでしょうか。

      Sub test()
          Dim R As Range
          i = 0
          Set R = Range("A1:B2")  'チェックする範囲を指定
          Set C = Range("C1")     '条件色セルを指定
          For y = 1 To R.Columns.Count
              For x = 1 To R.Rows.Count
                  If R(x, y).DisplayFormat.Interior.ColorIndex = C.Interior.ColorIndex Then i = i + 1
              Next
          Next
          MsgBox ("一致セル数 : " & i)
      End Sub

      ユーザー定義関数で出来なくもなさそうですが、ちょっと時間がかかりそうなので、まずは簡単なマクロ対応の方からオススメさせて頂きます。

  25. しまりす より:

    ありがとうございます!!
    しばらくのあいだ、月末月初処理の繁忙に入ってしまうので少し日にちがあくかもしれませんが また 書き込みに来させて頂きます!!
    頂いたマクロ、試してみます! 

    ありがとうございました。

  26. ほーく より:

    とても助かります!
    条件付セルでの塗りつぶしはカウント出来ないですが、これをカウントするようにするにはどうしたらいいですか?

  27. たえぞう より:

    まさに探していた物です
    コピペしたのですが、構文エラーとなってしまいます

    オフィス2007を使用しております
    どうしたら良いでしょうか?

  28. ゆう より:

    はじめまして

    この記事と近い内容なんですが、

    特定の色付き
    数値入力あり

    この二つの条件を満たしたセルだけをカウントする方法はあるんでしょうか?

    • isakat より:

      はい、IF文のところを以下のように変更すれば
      ・色条件
      ・数値入力あり
      の2つの条件を満たした場合のみカウントします。

      If 計算範囲.Rows(x).Columns(y).Interior.ColorIndex = 条件色セル.Interior.ColorIndex and IsNumeric(Cells(x,y).value) = True Then
      
      • mm より:

        はじめまして。

        特定の文字が入った色付きのセルをカウントしたいのですが、この2つの条件を満たしたセルをカウントするにはどのようにすればよいかご教授いただけましたら幸いです。

        特定の文字は″有”と”休” です。

  29. やま より:

    はじめまして。
    質問がありコメントさせていただきました。
    例にカレンダーの赤く色付けした日付の日数を数えていますが、その日付を合計することはできますか?

    Sumcolorの2次元テーブル版のような…

    拙い説明ですみません。お時間ありましたら教えてください。

  30. 匿名 より:

    バカみたいにCountcolor関数使えた。
    本当にありがとうございます。

  31. 会社人 より:

    初めまして。全くの初心者なのですが、この関数を使ってちゃんと合計されたのですが、結合したセルをバラバラに計算されてしまうのですが、3つのセルを結合して、それを1つと計算してほしいのてますが、どうすればいいですか?教えて頂けると助かります。

  32. きなこもち より:

    はじめまして。
    countcolor使いたく、色々試しているのですが、中々上手くいきません。セルの部分には、どのように入力していますか。例えば、D2列~L11列の範囲で、O15の色をカウントしたいです。この場合=countcolor どのように打てばいいですか?

  33. かい より:

    初めまして!
    とても参考になりました。
    この条件に、さらに2つ条件を加えることはできますか?

    例えば、指定した範囲にある特定の文字 かつ 別の指定した範囲にある特定の文字 かつ 色の付いたセル をカウントです。

    よろしくお願いします

  34. かい より:

    初めまして!
    とても参考になりました。
    この条件に、さらに2つ条件を加えることはできますか?

    例えば、指定した範囲にある特定の文字 かつ 別の指定した範囲にある特定の文字 かつ 色の付いたセル をカウントです。

    よろしくお願いします

コメントをどうぞ

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

  • ▼お小遣い稼ぎしませんか?▼ 日々の生活にhappyをプラスする|ハピタス

    クレジットカード・FX口座を作るだけで簡単に3万円GETです。一切支出はありません。

SUM 印刷 SUMIFS DATE 条件付き書式 置換 VLOOKUP ユーザー定義関数 ワイルドカード テンプレート 図形 検索 作業効率UP 目標 日付 HLOOKUP TODAY MATCH COLUMN 変換 フロー図 COUNTIF グラフ XLSTART SUMIF 無料 INDEX バーコード コピペ NA()
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑