
添付画像(左)のようなデータがあり、例えば区分「A」でフィルタした際に
直近2シリーズの値の平均値をVBAで求めて表示したいです。
日付が同日もしくは連続した日は1シリーズと考えます。
日付が2日以上あいた場合は別シリーズと考えます。
添付画像の例(右)だと、
赤枠で1シリーズ、青枠で1シリーズ、黄色範囲(直近2シリーズ)を対象範囲に
平均値をVBAで表示したいです。
SUBTOTAL関数を使ったらなんとなくできそうなイメージはできるのですが、
対象範囲をどのようにしたらよいかがわからず、ご教授ください。

A 回答 (4件)
- 最新から表示
- 回答順に表示
No.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は不要です
No.3
- 回答日時:
こんばんは、
#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は一か所で設定しているので
変数や配列、範囲ループなどで代入してください。
取り敢えずメッセージボックスで
結果は配列に入れています
該当データが抽出できない場合 エラーが返りますので対策を加えてください。
No.2
- 回答日時:
こんばんは
>平均値を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でなければダメという場合は、スルーしてください。
No.1
- 回答日時:
>日付が同日もしくは連続した日は1シリーズと考えます。
>日付が2日以上あいた場合は別シリーズと考えます。
4/2と4/3が該当しない理由は何でしょう?(4/1~4/3と考えても良いのか?)
と初級者レベルは感じました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
foreachで上限回数指定方法また...
-
VB.NET で 二次元のハッシュは...
-
pdfファイルの複数添付 引数の型
-
リストボックス複数選択抽出方法
-
超難問 Scripting.Dictionary ...
-
マッチング処理(1:N)
-
executeの実行が一回しかされない
-
プルダウンメニューでCSVデータ...
-
順位を付ける時のスコアの重複...
-
プルダウンメニューにDBの内容...
-
PHP掲示板で新着順に表示させた...
-
file_existsでファイル名の部分...
-
プログラミングのPythonのnoteb...
-
「ローマ字 -> ひらがな」へPHP...
-
FortranのOPEN文
-
テーブルの行と列の入れ替えは可?
-
codeigniterのページネーション...
-
PHPのPOSTでの半角スペース
-
PDOのバインドをforeachでまと...
-
System.String.Splitでエラー
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
マッチング処理(1:N)
-
foreachで上限回数指定方法また...
-
pdfファイルの複数添付 引数の型
-
foreachの間にテーブルの<TR>を...
-
POSTで渡されるデータの数がわ...
-
配列からプルダウン用のHTMLを生成
-
Excel VBAでフィルター後の対象...
-
VB.NET で 二次元のハッシュは...
-
foreachとかの勝手な省略?
-
phpでforeachの中にforeachがあ...
-
PHPの構文で間違えが分からない
-
3つの連想配列を交互に代入し...
-
forを使わずにforeach文のみで...
-
textより$$にはさまれた文字列...
-
PHPでこのコード自体に意味は無...
-
Smartyでインクリメント
-
sqlのデーターを『あ行』『か行...
-
多次元配列の中で条件に合う要...
-
Arduino 全部のピンをチェック...
-
変数に格納された文字列でdefin...
おすすめ情報