色付きセルの件数をカウントする [CountColor]
前回記事 ［ 色付きセルのみを合計する ］
SUMIFのように動かしたSumColorですが、今度はCOUNTIF版を作ってみます。
もともとこの定義関数を作るきっかけになったのは、次年度の勤務表を作成する為に休日日数をカウントしたい。というのが目的でした。
私の会社では年間休日日数というのが決まっています。その日数を大型連休につぎ込むのか、3連休を作るのか・・・・思案するのですが、カレンダーで色の付けた日＝休日なのでその休日日数を関数で合計できれば、年間カレンダーの作成が楽になるのです。
前回の問題点
前回の問題点として、縦1列しか合計できませんでした。
カレンダーのような2次元テーブルに対応できなかったのが重大な問題でした。
そこで前回のコードではRowsしか使っていなかったんですが、Columnsと組み合わせる事で対応してみます。
Rowは縦、Columnは横の意味です。
A1:C10の範囲指定の中で、Rows(3).Columns(2) となればB3セルのことを示します。
CountColor
| CountColor( 計算範囲 , 条件色セル ) | |
| 計算範囲の中で条件色セルと同じ色のセルをカウントする 範囲指定は複数行、複数列可能 |
|
1 2 3 4 5 6 7 8 9 10 11 |
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
「できると良いな～」くらいの気持ちで探していたので、コピペしただけで実現できてうれしいです。
簡単でわかりやすくて、とても助かりました。どうもありがとう！
はじめまして
相模原市の清家と申します。よろしくお願いします。
２０１３年の勤務カレンダーを作成しておりましたところ、検索で目にとまりました。例示と同じエクセル表を色を付けた休日セルをカウントしたいのですが、初心者です。具体的に関数計算はどのように入力すればいいのでしょうか？教えていただけませんでしょうか？
> はじめまして
> 相模原市の清家と申します。よろしくお願いします。
> ２０１３年の勤務カレンダーを作成しておりましたところ、検索で目にとまりました。例示と同じエクセル表を色を付けた休日セルをカウントしたいのですが、初心者です。具体的に関数計算はどのように入力すればいいのでしょうか？教えていただけませんでしょうか？
実際のセルに入力する式は
[色:0000FF]=CountColor(B3:H8,K3)[/色]
になります。
ただ元々Excelにある関数では無いので、VBAを設定してください。
設定方法はこちらの記事参考。
http://excel-magic.com/blog-entry-21.html
はじめまして。
会社の2013年度カレンダー作成しておりました。
セルの色の集計したかったので大変助かります。
ただ、自分のやり方がまずいのか、、、
セルの色を変更してもすぐに計算式に反映されないのです。
計算式のセルをダブルクリックすると再計算されます。
ちなみにExcelは2010です。
ご教授願います。
はじめまして
CountColor大変活用させて頂いております。
教えてくださいm(_ _)m
結合したセルを一つのセルとしてカウントすることは
可能でしょうか？
色を変えるとリアルタイムにカウントするように出来ますか？
すみません、初心者です。
上記のプログラムをそのまま写したのですが、「ＶＡＬＵＥ」エラーが出てしまいます。
ＳＵＭＣＯＬＯＲは問題なかったのですが・・・。
お手数ですが、何か良い方法はないかご教示ください。
当方の環境ではそのまま貼り付けても問題なく出ました。
Excel2007です。
そちらバージョンは何をお使いですか？
この関数でとても助かりました。出勤日数の管理が簡単になりました。ありがとうございました。
色の数を増やした場合にF9とかで簡単に更新したいです。
教えてください。よろしくお願いします。
確かにF9で再計算されないのは不便ですね。
記事に追記しましたのでそちらで対応お願いします。
とても便利に使えてます。
これにさらに、色＋条件でカウントできるとなお良いのですが・・・
セルの色が赤かつ数値「５」をカウウントするとか
条件付きで変更したセルカラーには 反応しないようです。
解決策はありませんか？
50を超えたセルは赤色に
70を超えたセルは水色に表示する設定を行いました。
赤色の水色 各々のセル数をカウントしたかったのですが、
すべて無色（デフォルト）として認識するようです。。
この関数使いました。とても便利ですね。
さらに、この色かつこの内容もカウントできるとなおいいですね。
（赤色の50とか緑色の70とか）
あと、他に書かれているかもいましたが、条件付で変更したカラーもカウントできるといいですね。
自分で作れれば良いのですが・・・・
結合されているセルを1として数えることはできないでしょうか？
お願いします。
こちら(↓)をそっくりそのままモジュールに追加すれば、Countcolorが使えるようになるのでしょうか？？
仕事の効率化を考えてサイトを探し、こちらのブログに立ち寄らせていただきました。
初心者すぎて《関数を自分で作ってみる》の記事もいまいち理解できませんでした…すみません(・・;)
はい、それをそっくりそのままモジュールに追加すれば使えます。
CountColor = 0 For y = 1 To 計算範囲.Columns.Count
ここは、Forの前で改行してくださいね。
CountColor = 0
For y = 1 To 計算範囲.Columns.Count
私も条件付きで色をつけたセルをカウントしようとして
このサイトを利用しようとしたのですが
そもそも、それこそCOUNTIFで計算できますね
非常に便利そうなのですが
どうしても16番の通りに行っているつもりですが
#VALUE!エラーが出てしまいます
エクセル2013ではだめですか？
コードはそのままモジュールにコピペされたという事ですよね。
では、セルの計算式はどのようになっていますか？