【アプリ版】サポートOS変更のお知らせ

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

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

添付画像の例(右)だと、
赤枠で1シリーズ、青枠で1シリーズ、黄色範囲(直近2シリーズ)を対象範囲に
平均値をVBAで表示したいです。

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

「Excel VBAでフィルター後の対象範」の質問画像
gooドクター

A 回答 (4件)

#3です


でたらめでした。
ご質問の画像内容の場合は処理できるかも知れませんが
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は不要です
    • good
    • 0

こんばんは、


#2様の回答を読んでなるほど、、と

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は一か所で設定しているので
変数や配列、範囲ループなどで代入してください。

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

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

こんばんは



>平均値をVBAで表示したいです。
関数でも良ければ・・・

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

平均値を出したいセルに、
=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でなければダメという場合は、スルーしてください。
    • good
    • 0

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


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

4/2と4/3が該当しない理由は何でしょう?(4/1~4/3と考えても良いのか?)
と初級者レベルは感じました。
    • good
    • 1

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング