エクセルについて（その２）

質問

前回、「塗りつぶしたセルをカウントしたい」を質問させて頂きましたが、これに関係し、恥をしのんでお尋ねします。
出来ることなら色別に塗りつぶしたセルをカウントしたい（４種類くらいの色を）のですが、どのようにしてユーザー定義関数をいじれば良いのか分かりません！！
お手数をお掛け致しますが、是非、教えて下さい！！
宜しくお願い致します！！

回答No.16

ja7awu · ベストアンサー

No.15 のレスに対してですが・・・　

> 本質問の趣旨から離れそうですね。

質問者のmaro-hさんを混乱させているだけです。

> SelectionChangeイベントだけでは、私がやって見ると、反応しませんでしたが、
> そういうのは、どこか間違ってますでしょうか。

間違っていると思います。　良く読んでいれば解ることですが・・・

この質問のタイトルに(その２)とあり、先頭に「前回・・・」とありますよね。
ということは、前回の質問と関連ある訳ですよね。

ですので、前回の質問の回答を最初から《良ぉ～く読んで》ください。
または、この質問の No.2 と No.1に対しての質問者からの「回答に対する補足」欄を
良ぉ～く読んでください。　そのあとで No.5 を見てください。

これらを読んで（その通り実行して）頂ければ、解ると思います。

しかし、説明にも書いておきましたが、色を変更した場合、カウントし直すのは、
《他のセルをアクティブにした時点》ですので、完璧な方法では、ありません。
このシートをビジネスで使用し、色をカウントすることが、商取引に影響する
重要なことですと、この方法は、使用しない方が無難です。
（そうかといって他の方法は、気付きません。無いかも？）

maro-hさん　混乱させてごめんなさいね。

お礼

ja7awu様、感激です。
私の質問に対し、これほどまでにご丁寧に色々と教えて下さり、本当に感謝しています。
正直な所、私はエクセルの関数云々に関しては、ほぼ初心者でして、仕事上、ごくごく単純に、「セルを色別にカウントしたいなぁ」と考え、いつまでも悩んでいたのです。
で、偶然にもこのサイトを見つけ、「もしかすると、誰かが知っているかも…」程度の気持ちで投稿してみたのです。
そして、ja7awu様をはじめとする数多くの方が回答して下さり、勉強と共に励みにもなりました。
…でも、やはり、今の私のレベルではかなり荷が重い内容の質問だったらしく、皆様の回答についていくのが精一杯で…いや、ついていけていなかったのが、事実なのです。
ですので、今は単純に、一から教えて頂ければ、それが私にとっては最高の回答だったのです。
本当にお恥ずかしい話なのですが、結局、何をどうすれば私の投稿が解決するのか、未だに分かりません…。
ですが、これに懲りず、これからもエクセルに取り組んでいきたいと思っています。
この度は有難うございました！！

回答No.15

imogasi

#12等です。＃１４のご指摘に対し
＞「塗りつぶしたセルをカウントしたい」ですよ。
それは初めから判っています。目指すものはそれなんですが、私も今回Ｆｕｎｃｔｉｏｎプロセジュアーが、書式
変更には反応しないことを、実感したようなことです。
○＃５のご回答で、Ｆｕｎｃｔｉｏｎプロセジュアーで回答があリますが、この制約を免れることは出来ないと思いました。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　Calculate
End Sub
を入れておられるのは、その手当てのためと理解して良いでしょうか。
しかし上記SelectionChangeイベントだけでは、私が
やって見ると、反応しませんでしたが、そういうのは、どこか間違ってますでしょうか。
反応しない以上、Calculate も出番がない
と言うことかなと思います。
元々Ｆｕｎｃｔｉｏｎプロセジュアーは値を変えるために
使うもので、その引数に指定されている範囲のセル値や式が変ると反応してくれるものだと言うことが、今更ながら、勉強させてもらいました。
上記の点明確に教えていただければありがたいですが、本質問の趣旨から離れそうですね。

回答No.14

ja7awu

No.12 についてですが・・・

> セルの値を変えるような操作

問題を良く読んでください。

「塗りつぶしたセルをカウントしたい」ですよ。

「セルの値を変えた時」とありますか？？

回答No.13

sakenomo

No.6です。

＞ユーザー定義関数では難しそうなので、

これは、"作るのが"という意味です。誤解を招きそうなので・・・。
ひとつのセルに入れた関数だけで色別の結果を出したい、と思っていましたので。

で、私も一つ作ってみました。

引数の「範囲」は、調べたいセル範囲を。
「基準」は、調べたい色で塗られたセル（一個）を指定してください。

「基準」のセルは、「範囲」の中でも、外でもかまいません。

Function CountInCol(範囲 As Range, 基準 As Range)
　　Dim c As Range, i As Long
　　For Each c In 範囲
　　　　If c.Interior.ColorIndex = 基準.Interior.ColorIndex Then
　　　　　　i = i + 1
　　　　End If
　　Next c
　　CountInCol = i
End Function

お礼

お礼の挨拶が遅くなり恐縮です。
sakenomo様には、何かとプログラムを考えて頂き、本当に感謝しています。と同時に、「凄いなぁ」とも感服しています。
何はともあれ、本当に有難うございました。

回答No.12

imogasi

＃１１です。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　Calculate
End Sub
の有無に関係なく、セルの値を変えるような操作
・値を入れる
・下矢印でセル値を移動する
・ＤＥＬキーでデータを消す
などをすると
＃９のルーチンだけの場合も、「色変更のセルの数え直し」を実行してくれるようです。
Ｆｕｎｃｔｉｏｎプロシージュアは「セルの値の変更」に反応して、全Ｆｕｎｃｔｉｏｎプロシージュアを実行し直する仕組みであって、SelectionChangeのcalculateだけでは反応しないようです。だからSelectionChangeのプロシージュアのcalculateの後にダミーの演算を入れると、いつも反応しました。

回答No.11

imogasi

#9です。
＃１０でご指摘があり、良く見ると、同じロジックで既に
回答が出ていました。それ以外のを見て、複雑だなと思ってしまい、良く見ておらず失礼しました。
・色を変更してもカウントは、変わらないと
この点不完全でした。
数式の計算のユーザー関数では変更が即時繁栄されるのに
、本件では様子が違うようで、今後勉強します。
・nは（変数宣言）していませんが、どうしてでしょう
良くはないですが、字数を少なくするため、私のは変数宣言を省略してます。エラーが出ることはないと思いますが。
・色を設定した場合、F9キーを押すなどで再計算しないと値が変更されません。（＃１）
Ｆ９キーを押しても変更されませんでした。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
　　Calculate
End Sub
も２回目から思ったようになりません。「変更される」ときもあり良く判りません。
関数式を入れたセルをポイントし、数式バーにカーソルを
置いて、エンターを押す（式を再設定する）しないと確実には変りませんでした。
以上お詫びかたがた、不完全ですがご報告させていただきます。

お礼

お礼が遅くなり、恐縮です。
皆様に教えて頂いた内容の物をそっくりそのまま、使わせて頂きましたが、どうも私の理想とは異なり、未だに「？？？」といった感じです。
ですが、色々とお話をして下さった事は、感謝しております。この場を借りて厚く御礼申し上げます。

回答No.10

ja7awu

No.9 の内容ですが、同じようなコードを書いてますね。
しかもこれでは、色を変更してもカウントは、変わらないと思いますが・・・
cは変数宣言して、nはしていませんが、どうしてでしょう。
初心者ですと、エラーが出て実行できないと思いますが・

回答No.9

imogasi

既回答は複雑ですが、下記で良いですか。
ＶＢＥの標準モジュールに下記を貼りつける。

Function cntcolor(r As Range, cl As Integer)
Dim c As Range
n = 0
For Each c In r
If c.Interior.ColorIndex = cl Then
n = n + 1
End If
Next
cntcolor = n
End Function

そして色で塗りつぶしたセル数を出したいセルへ関数式
=cntcolor(A1:A4,6)とかを入れる。
第１引数はセル範囲、第２引数はセル数を調べたい色のインデックス値です。セル範囲は好きなように、色のインデックス値はマクロの記録で調べるか、ＷＥＢで調べてください。
私のサンプルテストではＡ１，Ａ４，Ａ７，Ｂ９を黄色（色インデックス６）にして、
=cntcolor(A1:A4,6)は４
=cntcolor(A1:A10,6)は３
=cntcolor(A1:A4,6)は２となりました。

お礼

ご回答、有難うございます。
皆さんから教えて頂いたやり方を色々と試している最中ですので、新たに教えて頂いた方法も試してみたいと思います。
それにしても、エクセルって奥が深いですね。

回答No.8

happypoint

#3です。

＞どれが一番、分かりやすく簡単なのか、私には分かりません…

みなさんがいろんないい方法を考えてくださるので、
うれしい悲鳴のようですね。

要約しますと、

・とにかく簡単に解決するなら、#2さんの方法。
（VBAを使わないので、わかりやすいかと思います。
　参考ＵＲＬに、図解で説明があります。）

・作業列を作りたくないという場合は、VBAを使った方法となります。

(1)
sakenomoさんの方法は、「マクロ」を実行したら結果を書き込む方式
不要な作業列を作らないで済みますので、見た目を気にする場合や、
結果だけを素早く求めたいときに適しているでしょう。

(2)
ja7awuさんの方法は、「ユーザー定義関数」を作成して関数で結果を求める方式です。

関数を入力する画面（関数パレット）に、
色のついたセルをカウントする「CCount」関数が現れ、
あたかも通常の関数（SUMとかIFとか）のように使えるようになります。

ちょっと難しく見えますが、非常にスマートな方法です。

お礼

なるほど…単純な私にでも、何となく分かりました。
それにしても凄いですね、こんなに簡単に要約できる方がすぐにお答えして下さるなんて…。
皆さん、凄すぎです。
私も皆さんに習い、少しでもエクセルが今以上に使いこなせるよう努力したいと思います。
この度は有難うございました！！

回答No.7

sakenomo

＃６です。そそっかしいもので・・・。

＞i As Integer

上記だと調べるセル範囲が大きい時にエラーがでるかも知れないので、

i As Long

に修正してください。

お礼

分かりました。
皆さまから教えて頂いたものを一つ一つ、試していきます。
有難うございました。
(でも、正直な所、どれが一番、分かりやすく簡単なのか、私には分かりません…どなたか、まとめて下さると助かるのですが…なんて)

回答No.6

sakenomo

#4です。

ユーザー定義関数では難しそうなので、普通のマクロでやってみました。知りたいセル範囲を選択して実行してください。A列を挿入して、結果を出します。

Sub test()
Dim c As Range, r As Range, i As Integer, b As Byte
Set r = Range(Selection.Address)
For Each c In r
If c.Interior.ColorIndex <> xlNone Then
If b = 0 Then
Range("A1").EntireColumn.Insert
b = 1
End If
i = 0
Do
With Range("A1").Offset(i, 0)
If .Interior.ColorIndex = xlNone Then
.Interior.ColorIndex = c.Interior.ColorIndex
.Value = 1
Exit Do
ElseIf .Interior.ColorIndex = c.Interior.ColorIndex Then
.Value = .Value + 1
Exit Do
End If
End With
i = i + 1
Loop
End If
Next c
r.Select
End Sub

お礼

度々のご回答、本当に有難うございます。
教えて頂いた通りに実行してみたいと思います。（…時間が掛かりそうですが…)
こんなに幾度も教えて頂けるなんて、光栄に思います。
本当に感謝致します。