Excel VBAでフィルター後の対象範囲の平均値を求めたい

添付画像（左）のようなデータがあり、例えば区分「A」でフィルタした際に
直近２シリーズの値の平均値をVBAで求めて表示したいです。

日付が同日もしくは連続した日は１シリーズと考えます。
日付が２日以上あいた場合は別シリーズと考えます。

添付画像の例（右）だと、
赤枠で１シリーズ、青枠で１シリーズ、黄色範囲（直近２シリーズ）を対象範囲に
平均値をＶＢＡで表示したいです。

ＳＵＢＴＯＴＡＬ関数を使ったらなんとなくできそうなイメージはできるのですが、
対象範囲をどのようにしたらよいかがわからず、ご教授ください。

No.4 回答者： Qchan1962 回答日時： 2021/06/02 00:22

＃３です

でたらめでした。
ご質問の画像内容の場合は処理できるかも知れませんが
cnt = Application.CountIf(Range("B2:B" & lastRow), ">=" & DateAdd("d", -2, key1)) + 1
key2 = Application.Large(Range("B2:B" & lastRow), cnt)
は、必ず 　DateAdd("d", -2, key1)　になる訳でないので　ダメダメです

関数を考えるよりVBAなので下記の様に訂正してください

cnt = Application.CountIf(Range("B2:B" & lastRow), ">=" & DateAdd("d", -2, key1)) + 1
key2 = Application.Large(Range("B2:B" & lastRow), cnt)
を
For Each c In Range("B2:B" & lastRow).SpecialCells(xlCellTypeVisible)
If DateAdd("d", -1, key1) > CDate(c) Then
If key2 < CDate(c) Then key2 = CDate(c)
End If
Next

で大丈夫かな？

ちなみに　c　は　Dim c As Range　で　Dim cnt As Longは不要です

No.3 回答者： Qchan1962 回答日時： 2021/06/01 23:52

こんばんは、

＃２様の回答を読んでなるほど、、と

AGGREGATE関数(14,6を上手く使えませんでした。。

やり方は色々あると思いますが、フィルタとシート関数の組み合わせで
ループでべた処理の方がすっきりするかも、、です

Sub test()
Dim key1 As Date, key2 As Date
Dim lastRow As Long
Dim cnt As Long
Dim Ans(1)

If ActiveSheet.AutoFilterMode = True Then ActiveSheet.Range("A1").AutoFilter
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
With ActiveSheet.Range("A1:C" & lastRow)
.AutoFilter Field:=1, Criteria1:="A"
key1 = CDate(Application.Large(Range("B2:B" & lastRow).SpecialCells(xlCellTypeVisible), 1))
cnt = Application.CountIf(Range("B2:B" & lastRow), ">=" & DateAdd("d", -2, key1)) + 1

key2 = Application.Large(Range("B2:B" & lastRow), cnt)

.AutoFilter Field:=2, Criteria1:="<=" & key1, _
Operator:=xlAnd, _
Criteria2:=">=" & DateAdd("d", -1, key1)

Ans(0) = (Application.Subtotal(1, Columns(3)))

.AutoFilter 2
.AutoFilter Field:=2, Criteria1:="<=" & key2, _
Operator:=xlAnd, _
Criteria2:=">=" & DateAdd("d", -1, key2)
Ans(1) = (Application.Subtotal(1, Columns(3)))
MsgBox ("シリーズ1 Average：" & Ans(0) & vbCrLf & "シリーズ2 Average：" & Ans(1))
.AutoFilter
End With
End Sub

第一条件の　Aは一か所で設定しているので
変数や配列、範囲ループなどで代入してください。

取り敢えずメッセージボックスで
結果は配列に入れています

該当データが抽出できない場合　エラーが返りますので対策を加えてください。

No.2 回答者： fujillin 回答日時： 2021/06/01 22:38

こんばんは

＞平均値をＶＢＡで表示したいです。
関数でも良ければ・・・

起こり得る可能性がいろいろとありすぎるため、エラーチェックを省いていますので、最低一つ以上該当するデータがあることを条件にしています。
（エラーチェックを全部行うと、大変複雑な式になってしまうので…）

平均値を出したいセルに、
=SUMPRODUCT((A2:A15="A")*(B2:B15>IFERROR(AGGREGATE(14,6,B2:B15/(A2:A15="A")/(B2:B15<AGGREGATE(14,6,B2:B15/(A2:A15="A"),1)-2),1),0)-3)*C2:C15)/SUMPRODUCT((A2:A15="A")*(B2:B15>IFERROR(AGGREGATE(14,6,B2:B15/(A2:A15="A")/(B2:B15<AGGREGATE(14,6,B2:B15/(A2:A15="A"),1)-2),1),0)-3))

※ フィルター操作は不要です。
※ 上記は"A"の場合の例です。ご提示のデータの例では、結果は 163/6 ＝ 27.16666 になります。
※ "A"以外の値で絞込みしたい場合は、式中の"A"部分を"B"などに置き換えればよいですが、どこかのセルに「A」、「B」などを入力することにして、式中の"A"をセル参照に置き換える方が、可用性は高くなるでしょう。


もちろん、VBAで算出することも可能ですが、そちらは他の方にお任せいたします。
VBAでなければダメという場合は、スルーしてください。

No.1 回答者： めぐみん_ 回答日時： 2021/06/01 17:44

＞日付が同日もしくは連続した日は１シリーズと考えます。

＞日付が２日以上あいた場合は別シリーズと考えます。

4/2と4/3が該当しない理由は何でしょう？（4/1～4/3と考えても良いのか？）
と初級者レベルは感じました。