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

ユーザー定義関数

35-1.png

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

前回の問題点

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

CountColor

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

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

前回記事 [ 色付きセルのみを合計する [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が使えるようになるのでしょうか??

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

  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のユーザー定義関数で対応するのではなく、マクロを動かす必要があります。
      たとえば以下のマクロを都度チェック時に動かせばどうでしょうか。

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

  25. しまりす より:

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

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

コメントをどうぞ

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

  • ▼お小遣い稼ぎしませんか?月3万円稼げます!▼
    日々の生活にhappyをプラスする|ハピタス
    クレジットカード・FX口座を作るだけ
ユーザー定義関数 偏差値 バーコード 条件付き書式 テンプレート クイズ COLUMN 目標 無料 COUNTIF HLOOKUP 印刷 ワイルドカード 日付 コピペ SUMIF INDEX XLSTART グラフ VLOOKUP STDEV MATCH 図形 フロー図 検索 作業効率UP SUM DATE TODAY NA()
  • 関西在住の30代サラリーマン。事務職で毎日Excelと戯れています。
    システム導入なんて資金が無い!Excelでなんとかやりたい零細中小の社長さん・事務員さんの力になればと思います。
    詳しいプロフィール


PAGE TOP ↑