エクセルで書式設定とフィルタの組み合わせでうまく行かないのですが

エクセルで表の中の上位３位に着色し、色フィルターで上位３位の値をSUBTOTALで平均を求めたいのですが、例えば上位３つ（２つ）が同じ値の場合、次の４位の値がフィルターで抽出されます。
この状態で色フィルターを書けると、無着色の４位までが抽出されます。
フィルター前には上位３位に選ばれているために着色されてしまいます。

この状態で色フィルターを書けると、無着色のセルなのに抽出されて色付セルの平均に組み込まれてしまうので結果がズレてしまうので困っています。

フィルター後に着色セルをVBAで集計しようとしましたが、下記で
＝ColorCount、＝ColorSumともに０になってしまいます。
VBAは初心者なので出来れば関数で何とかしたいのですが、下記程度なら使えそうなのですが何が悪いのかアドバイスいただければ助かります。


Function ColorCount(R1 As Range, C As Range)
Dim r As Range
Application.Volatile
ColorCount = 0
For Each r In R1
If r.Interior.Color = C.Interior.Color Then
ColorCount = ColorCount + 1
End If
Next r
End Function
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
Function ColorSum(R1 As Range, C As Range)
Dim r As Range
Application.Volatile
ColorSum = 0
For Each r In R1
If r.Interior.Color = C.Interior.Color Then
ColorSum = ColorSum + r.Value
End If
Next r
End Function

No.4 ベストアンサー 回答者： ママチャリ 回答日時： 2022/10/08 11:02

本来の目的は何ですか？

着色することですか？
フィルタを掛けることですか？
それとも、上位3までの平均を出すことですか？

もし、平均を出すだけなら、こんな数式でも行けると思いますよ。
=(LARGE($A:$A,1)+LARGE($A:$A,2)+LARGE($A:$A,3))/3

この回答へのお礼

＞上位3までの平均を出すこと
です。

上位に同値がある場合の懸念事項の解消で、LARGE関数があることをすっかり失念しておりました。

大昔に使ったことはあるのですが、「条件付き書式」と「色フィルター」で出来ると思い込んでしまっていました。

一発解決しました。

お礼日時：2022/10/11 09:35

No.3 回答者： fujillin 回答日時： 2022/10/07 13:21

こんにちは

＞出来れば関数で何とかしたいのですが
関数ではフィルターをかけたり色を参照することはできません。
一方で、ご質問の趣旨は
　「セル範囲中の上位3つの値の平均を求めたい」
ということと解釈しました。
それだけであるなら、関数でも可能です。

上位から並べた際に同値で3位（あるいは２位、1位）が複数あって、順位的に3位までが個数としては4つを超えてしまう可能性がありますが、その際でも「上から3つ」だけの平均を求めたいという意味と解釈しました。


添付図では、A列に対象の数値が並んでいるものしています。
上位から並べると、100、90、80、80・・・と3位が複数ありますが、あくまでも上位３つの平均（＝(100+90+80)/3）を求めるという例です。

添付図では、C1セルに
　=AVERAGE(INDEX(SORT(A1:A10,,-1),ROW(A1:A3)))
の式を入力して求めています。
（※ 365環境を想定しています）
A列の値を変えて、試してみてください。

この回答へのお礼

＞上位から並べた際に同値で3位（あるいは２位、1位）が複数あって、順位的に3位までが個数としては4つを超えてしまう可能性がありますが、その際でも「上から3つ」だけの平均を求めたいという意味と解釈しました。

まさにご推察の通りのことで悩んでいました。
ご回答参考になりました。
が、今回は#No4さんのご回答がピッタリですのでこちらをBSにさせて頂きます。
ありがとうございました。

お礼日時：2022/10/11 09:33

No.2 回答者： Qchan1962 回答日時： 2022/10/07 11:17

＃１です

ごめんなさい！！
＞VBAは初心者なので出来れば関数で何とかしたいのですが

ユーザー定義関数の場合、.DisplayFormatは使えなかったか・・
別法があった気がしたけれど少なくとも直接はダメだったかと・・

関数で何とか・・　Function ColorCountの内容を変えるしかないかな
例えばこんな感じ

Function ColorCount(R1 As Range, C As Range)
Dim r As Range
Application.Volatile
ColorCount = 0
For Each r In R1
If Not r.EntireRow.Hidden Then
If r.Interior.Color = C.Interior.Color Then
ColorCount = ColorCount + 1
End If
End If
Next r
End Function

これで隠れた行はカウントしないはずです

セルに　=ColorCount(範囲,調べる色の付いたセル)
例　=ColorCount(A2:A20,A1)

ColorSumも同様に　If Not r.EntireRow.Hidden Then　End If　を追加すれば出来るかな・・

この回答へのお礼

ご丁寧なご回答ありがとうございます。
非常に参考になりました。

今後同様の問題の時には使わせて頂きます。
有用なご回答はリストにしていますので追加しておきます。

お礼日時：2022/10/11 09:31

No.1 回答者： Qchan1962 回答日時： 2022/10/07 10:45

こんにちは

同様なFunctionなので、取り合えずColorCountについて

Function ColorCount(R1 As Range, C As Range)の使い方？
内容は、対象範囲がR1　比較する色のセルがCになっています

比較する（数える）色のセルが必要ですね

Interior.Colorなので条件書式で付いている色は対象になりません
（ここで引っ掛かっているかも）
条件書式の色なら
.Interior.Color　を　.DisplayFormat.Interior.Color　に各所する


また、フィルターを掛けているのなら
.SpecialCells(xlCellTypeVisible)で可視セルを対象（探す範囲）にします

現状、Function ColorCountをどのように呼んでいるのでしょうか？


例：（想定）A1セルに見つける色を付けて　見つける範囲はA2～最終行
フィルタを掛けている状態で
（例はブック、シートを省略　ActiveWorkbook　ActiveSheet）

Sub test01()　'実行プロシージャ（表示の当該色を数える）
Debug.Print ColorCount(Range(Cells(2, 1), Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible), Range("A1"))
End Sub

Function ColorCount(R1 As Range, C As Range)
Dim r As Range
Application.Volatile
ColorCount = 0
For Each r In R1
If r.DisplayFormat.Interior.Color = C.DisplayFormat.Interior.Color Then
ColorCount = ColorCount + 1
End If
Next r
End Function

この回答へのお礼

早々のご回答ありがとうございます。
週末出かけていてお礼が遅くなりました。

なるほど！！です。
＞Interior.Colorなので条件書式で付いている色は対象になりません
（ここで引っ掛かっているかも）

ご明察の通りここで引っかかっていました。
ただし、今回の質問では＃No4さんのご回答で解決できました。
目的にピッタリの関数がありました。

お手数をお掛けしました。

お礼日時：2022/10/11 09:29