2010年06月05日

塗りつぶし色の付いたセルを数える。[Excel]

sort.gif
Excelで色の付いたセルの数を数えなければならない時はどうしていますか?

Excelに詳しい方は条件付書式を使用して塗りつぶし色を設定し、色を付ける前のデータの時点で数を数えるかもしれませんね。

ただ今回は、“既に塗りつぶし色を付けてあるセルの数を数える”ことを依頼されてしまったので、この方法は使用できません。
既存の関数では対処できない為、ユーザー定義関数を作って対応しました。
(*^^)/


3ヶ月ぶりですね。お久しぶりです。(^^ゞ
最近は、記事の内容に懲りすぎてしまい、ちょっと忙しくなると更新が滞ってしまって、申し訳ない。m(_ _)m

先日、知人に上記依頼をされて簡単な関数を作ったので備忘録がてら公開します。
この関数、案外使い勝手が良いんですよ。(^^)b
すべてが自前の記事なので、裏をとる必要が無くて楽〜♪♪
o(^-^o)(o^-^)o


進捗状況をチェックする表などでセル塗りつぶし色を使うことが多いかと思いますが、調べてみると、Excelにはセルの塗りつぶし情報を取得する関数はありませんでした。
Excel関数にはセルの文字揃え情報を取得できるものまであるのに、頻繁に使用されるセルの塗りつぶし情報を取得するものが無かったとは!!( ̄□ ̄;)

しょうがないので、VBAを使ってユーザー定義関数を作ってみました。
こんなことができるから、Excelって便利なんですよね〜。d(~〜~*)

サンプルを作ってみました。
Excel 2000 以降なら動くはずデス。(^^;)
ここでダウンロードしてください。

'*********************************
' 複数セル“Rng”内にある、セル塗りつぶし色(index)が'“Col_index”
' であるセル数をカウントする関数
'
' Application.Volatileを使用している為、再計算時に常に呼び出されます。
' 処理が重いようでしたら、対象範囲を狭めるか、Application.Volatileを
' 外してください

'*********************************
Function CountColor(Rng As Range, Col_index As Integer) As Long

 Dim myRng As Range
 Dim Col_cnt As Long

 '再計算時に呼び出し
 Application.Volatile
 Col_cnt = 0
 
 '対象範囲内のすべてのセルの色をチェック、カウントする。
 For Each myRng In Rng
  If myRng.Interior.ColorIndex = Col_index Then
   Col_cnt = Col_cnt + 1
  End If
 Next myRng
 CountColor = Col_cnt
 
End Function

この関数は、対象範囲内の指定した色で塗りつぶされているセルの数を取得します。
CountColor(対象範囲, 検索色(カラーインデックス))

普通の関数と同じく再計算時に動作しますが、Excelはセルの色を変えただけでは再計算がおこなわれない点に注意してください。
再計算をさせるには、セルの編集をするか「F6」キーを押す必要があります。


サンプルには指定色で塗りつぶされたセル数を取得する関数以外に、塗りつぶされているセル数(色指定なし)を取得する関数、セルの塗りつぶし色をカラーインデックスで取得する関数も入っています。

'*********************************
' 複数セル“Rng”内にある、セル塗りつぶし色が付いているセル数を
' カウントする関数
'
' Application.Volatileを使用している為、再計算時に常に呼び出されます。
' 処理が重いようでしたら、対象範囲を狭めるか、Application.Volatileを
' 外してください

'*********************************
Function CountColorA(Rng As Range) As Long

 Dim myRng As Range
 Dim Col_cnt As Long
 
 '再計算時に呼び出し
 Application.Volatile
 Col_cnt = 0
 
 '対象範囲内のすべてのセルの色をチェック、カウントする。
 For Each myRng In Rng
  If myRng.Interior.ColorIndex > 0 Then
   Col_cnt = Col_cnt + 1
  End If
 Next myRng
 CountColorA = Col_cnt
 
End Function

この関数は、対象範囲内の塗りつぶされているセル(色指定なし)の数を取得します。
CountColorA(対象範囲)


'*********************************
'対象セル“Rng”のセル色(index)を取得する関数
'*********************************
Function GetColorIndex(Rng As Range) As Integer
 GetColorIndex = Rng.Interior.ColorIndex
End Function

この関数は、対象セルの塗りつぶし色(カラーインデックス)を取得します。
GetColorIndex(対象セル)

依頼者に「XX色の“カラーインデックス”ってなに?」と毎回質問される事のないように、カラーインデックスを取得する関数も作りました。
ただし、この関数は再計算時には動作しないので、色を変更したときには、対象セルか計算式を入れたセルを編集する必要があります。
再計算時の動作が必要なら“Application.Volatile”を入れてください。


ま、VBAで動いてるので動作が遅いのはご愛嬌。
対象範囲を広く取りすぎると、再計算がおこなわれる度にもっさりとした動作になってしまいますよ。(^^ゞ

でも、これだけのコードで新しい関数を作ることができるなんて、やっぱりExcelVBAって便利です。
「欧米では小規模システムでVBAがよく使われていて、互換性の問題でVBAのバージョンを大きく上げられない。」という話をどこかで読んだ気がするのですが、案外本当なのかもしれませんね。
σ(^◇^;)


ちなみに、他のブックで使用する為にはモジュールをコピーするか、新しいブックでモジュールを作成し、上記コードをコピーする必要があります。

このコードも勝手に使用していただいてかまいませんが、不具合が起こってもクレームは受け付けませんよ。(*^_^)b
posted by day_after_day at 13:15| Comment(14) | TrackBack(0) | プログラミング | このブログの読者になる | 更新情報をチェックする
この記事へのコメント
こんにちは〜

エクセルを使って家計簿つけてるえぐざです(^^ゞ
でも初歩的な事しか知らず。。。

塗りつぶしを数える技、あったんですね!
自分も仕事で使えそうです。ありがとうございます!!
Posted by えぐざ at 2010年06月12日 12:14
【えぐざ】さん

こんにちは。(^^)/

か、家計簿つけてるんですか!
すごいっすね〜。σ(^◇^;)

塗りつぶしの技、使っていただけるならば公開した甲斐もあるってもんです!!
よろしくー!(^o^)/~
Posted by たけい at 2010年06月13日 21:36
なるほどぉ!
たぶん一番わかりやすくて無駄のないコードですね♪

以前どっかで見たのは、
ColorIndexをGET.CELL関数でシート上に書き出していって、それをCountIfかなんかで数えるものだったと思います。
これをVBAでブラッシュアップすれば色別のカウントも楽にいけそうだなぁとは思ったんですけど、たけいさんもおっしゃったように普通は条件付き書式使うから必要性見出せなくて今まで忘れてました。
Posted by codama at 2010年06月27日 23:38
【codama】さん

なるほど、GET.CELL関数ですか・・・僕は始めて知りました。

確かにColorIndexを取れるみたいですね・・・僕の環境ではエラーが出ますけど。(^^;)

ま、こんな方法もありますよって事で。σ(^◇^;)

ちなみに、僕はユーザー定義関数はほとんど使いません。
Excel標準も含めて、シート関数は常に動いてくれて動作を重くするので、あまり好きじゃないんですよ。
Posted by たけい at 2010年06月30日 07:49
色の付いたセルの個数を数える方法を色々教えてもらいありがとうございます。しかし、そのプログラムをどこに入れたら良いのか、その打ち込み方がわかりません。何方か教えて下さい。宜しくお願い致します。
Posted by 斉藤昭三郎 at 2011年10月04日 22:03
【斉藤昭三郎】さん

はじめまして。
こんな記事でも役立てばありがたいです。

ちなみにこのプログラムはExcelのVisual Basic Editorを起動して標準モジュールを作成し、そのモジュール上に書き込む必要があります。

Visual Basic Editorの起動方法がわからないようでしたら、以下のページを参考にして下さい。
http://www.moug.net/learning/exvba/exvba012-2.html

この記事のプログラムで良ければ、記事内のリンクからサンプルBookをダウンロードしてVisual Basic Editor上から標準モジュールの“Module1”をエクスポート、この関数を使用したいBookへそのファイルをインポートすれば使えるようになります。

初めての方にはわかりにくいかもしれませんが、がんばってください。o(^o^*)o
Posted by たけい at 2011年10月10日 23:19
初めまして
まさに、出勤数のカウントの為にダウンロードして早速使わせていただきました。

めちゃくちゃ便利です♪

ありがとうございました。
本当に助かりました。
Posted by れあ at 2014年02月10日 21:19
【れあ】さん

はじめまして。

出勤数のカウントって、業務に使われてるんですか!

ちゃんとお役に立てていれば良いのですが。(^^;)

こんなものでも、微力ながら業務のお手伝いが出来るなら嬉しい限りです。
便利に使ってやってください。o(__o)
Posted by たけい at 2014年02月17日 00:05
Hi, neat websites you've gotten there.|
ナイキ ランニングシューズ 2014 http://www.cnypoolplayers.com
Posted by ナイキ ランニングシューズ 2014 at 2014年05月11日 21:40
Really....such a important webpage.|
オロビアンコ バッグ 新作 http://www.fdlseafood.com
Posted by オロビアンコ バッグ 新作 at 2014年05月11日 21:40
Simply desire to say your article is as amazing. The clearness on your put up is simply spectacular and i could assume you're an expert on this subject. Fine together with your permission let me to seize your RSS feed to keep up to date with coming near near post. Thanks 1,000,000 and please carry on the enjoyable work.|
オロビアンコ バッグ メンズ http://www.onlyjav.com
Posted by オロビアンコ バッグ メンズ at 2014年06月12日 03:32
My programmer is trying to persuade me to move to .net from PHP. I have always disliked the idea because of the expenses. But he's tryiong none the less. I've been using Movable-type on numerous websites for about a year and am concerned about switching to another platform. I have heard good things about blogengine.net. Is there a way I can transfer all my wordpress content into it? Any kind of help would be really appreciated!|
トゥミ バッグ 2014 http://www.photographybyanacia.com
Posted by トゥミ バッグ 2014 at 2014年06月12日 03:32
WOW just what I was looking for. Came here by searching for %keyword%|
オロビアンコ 財布 http://www.downloadasianvideos.com
Posted by オロビアンコ 財布 at 2014年06月12日 03:32
Un regard en arri猫re ? son Greatest Moments internationaux
Posted by ray ban wayfarer polarized at 2014年06月15日 00:34
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:


この記事へのトラックバック
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。