色付きセルの件数をカウントする [CountColor]
前回記事 ［ 色付きセルのみを合計する ］
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
「できると良いな～」くらいの気持ちで探していたので、コピペしただけで実現できてうれしいです。
簡単でわかりやすくて、とても助かりました。どうもありがとう！
はじめまして
相模原市の清家と申します。よろしくお願いします。
２０１３年の勤務カレンダーを作成しておりましたところ、検索で目にとまりました。例示と同じエクセル表を色を付けた休日セルをカウントしたいのですが、初心者です。具体的に関数計算はどのように入力すればいいのでしょうか？教えていただけませんでしょうか？
> はじめまして
> 相模原市の清家と申します。よろしくお願いします。
> ２０１３年の勤務カレンダーを作成しておりましたところ、検索で目にとまりました。例示と同じエクセル表を色を付けた休日セルをカウントしたいのですが、初心者です。具体的に関数計算はどのように入力すればいいのでしょうか？教えていただけませんでしょうか？
実際のセルに入力する式は
[色: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が使えるようになるのでしょうか？？
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仕事の効率化を考えてサイトを探し、こちらのブログに立ち寄らせていただきました。
初心者すぎて《関数を自分で作ってみる》の記事もいまいち理解できませんでした…すみません(・・;)
はい、それをそっくりそのままモジュールに追加すれば使えます。
CountColor = 0 For y = 1 To 計算範囲.Columns.Count
ここは、Forの前で改行してくださいね。
CountColor = 0
For y = 1 To 計算範囲.Columns.Count
私も条件付きで色をつけたセルをカウントしようとして
このサイトを利用しようとしたのですが
そもそも、それこそCOUNTIFで計算できますね
非常に便利そうなのですが
どうしても16番の通りに行っているつもりですが
#VALUE!エラーが出てしまいます
エクセル2013ではだめですか？
コードはそのままモジュールにコピペされたという事ですよね。
では、セルの計算式はどのようになっていますか？
とても簡単にできました。
ありがとうございます。
使わせて頂いております。
とてもシンプルで分かり易くて、他のサイトと比較しても一番使いやすいです。
ありがとうございました。
はじめまして
今回、まさに色付けしたセルをカウントしたいくてこのページを探しました。
ダウンロードしたファイルでデータを作成してもエラーになってしまいます。
VBAの標準モジュールに張り付けて以下のような関数を打っても#VALUE!になってしまいます。
いろいろサイトをみて、頑張ってみたのですができません。
教えていただきたくよろしくお願いいたします。
=CountColor(C4:AG4,3)
使わせていただいております。
最初、自分で関数を作るという事が全くわからず、
エクセルにある「関数」でなく、オリジナルなモノを創造した
という事がわかってから感動しております。
本当に簡単にできました。
ありがとうございました。
通常のセル書式 で色付けしたものをカウントできるので 感動しました。ただ、条件書式で 色付けしたセルを カウントすることはできなかったのですが、VBA記述によっては可能でしょうか？
条件付書式の色セルをカウントする方法としては、むしろその条件付書式の内容をカウントしてしまえば良いのでは？と思いました。
たとえばセルが”あいうえお”という文字であれば赤色にする、という条件付書式であれば、ただ単に”あいうえお”のものをカウントすれば良いので
=COUNTIF(範囲,”あいうえお”)
で良いかなと。
ただし簡単な条件の時には有効ですが、複雑な条件設定されてるとCOUNTIFもややこしくなってきますよね、、
条件付書式で変わったセルの色をカウントするVBAを作ることは出来ます。少々お時間いただければ。
早速のご回答ありがとうございます。
同じ形式の表(個人表)が 複数 縦にならんでおります。
ラベルみたいなイメージ。
構造と列種別は同じですが、表としては 間があいており連続していません。
個人表は多人数分あります。
ひとり分の表の中で、エラーとして 認識する条件があり そこに色をつけさせています。
この飛び飛びの 個人票に 色がつく＝エラーがある ということで、なければ まるっと印刷、エラーーありなら 見に行く感じにしたいと思っています。
個人表に呼んできている条件自体が そこそこ複雑なので 色で判別したいとおもっております。
わかりづらい説明かもしれませんが、もしも可能でしたらうれしいです。
条件が複雑であるので結果の書式で判断したい、という事ですね。なるほど。
色々と弄ってみたのですが、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ユーザー定義関数で出来なくもなさそうですが、ちょっと時間がかかりそうなので、まずは簡単なマクロ対応の方からオススメさせて頂きます。
ありがとうございます！！
しばらくのあいだ、月末月初処理の繁忙に入ってしまうので少し日にちがあくかもしれませんが また 書き込みに来させて頂きます！！
頂いたマクロ、試してみます！
ありがとうございました。
とても助かります！
条件付セルでの塗りつぶしはカウント出来ないですが、これをカウントするようにするにはどうしたらいいですか？
まさに探していた物です
コピペしたのですが、構文エラーとなってしまいます
オフィス2007を使用しております
どうしたら良いでしょうか？
アドインのダウンロードで対応出来ませんか？
また、こちらの記事もご確認下さい
http://excel-magic.com/post-21/
構文エラーは出なくなりましたが、答が合いません
引用しているセルは合っていますが…
どうしたら良いでしょうか？
色番号は合っていますか？
似たような色でも違う番号の可能性もあります。
はじめまして
この記事と近い内容なんですが、
特定の色付き
数値入力あり
この二つの条件を満たしたセルだけをカウントする方法はあるんでしょうか？
はい、IF文のところを以下のように変更すれば
・色条件
・数値入力あり
の２つの条件を満たした場合のみカウントします。
はじめまして。
特定の文字が入った色付きのセルをカウントしたいのですが、この2つの条件を満たしたセルをカウントするにはどのようにすればよいかご教授いただけましたら幸いです。
特定の文字は″有”と”休” です。
はじめまして。
質問がありコメントさせていただきました。
例にカレンダーの赤く色付けした日付の日数を数えていますが、その日付を合計することはできますか？
Sumcolorの2次元テーブル版のような…
拙い説明ですみません。お時間ありましたら教えてください。
バカみたいにCountcolor関数使えた。
本当にありがとうございます。
初めまして。全くの初心者なのですが、この関数を使ってちゃんと合計されたのですが、結合したセルをバラバラに計算されてしまうのですが、3つのセルを結合して、それを1つと計算してほしいのてますが、どうすればいいですか？教えて頂けると助かります。
はじめまして。
countcolor使いたく、色々試しているのですが、中々上手くいきません。セルの部分には、どのように入力していますか。例えば、D2列～L11列の範囲で、O15の色をカウントしたいです。この場合=countcolor どのように打てばいいですか？
=CountColor(D2:L11,O15)
これでいけませんか？
初めまして！
とても参考になりました。
この条件に、さらに2つ条件を加えることはできますか？
例えば、指定した範囲にある特定の文字 かつ 別の指定した範囲にある特定の文字 かつ 色の付いたセル をカウントです。
よろしくお願いします
初めまして！
とても参考になりました。
この条件に、さらに2つ条件を加えることはできますか？
例えば、指定した範囲にある特定の文字 かつ 別の指定した範囲にある特定の文字 かつ 色の付いたセル をカウントです。
よろしくお願いします