VBA 変動する総計を半期合計で計算したい。

質問

ピボットテーブルの総計行が元データが追加されるので変動してしまいます。
他のセルに（C37）式を入れていますが、ピボットの行が更新されるたびに 総計行の範囲を毎回張りなおさなければならない状態です。

変動しても他のセル（C37)に、合計が計算できるようにしたいです。
その総計行に対する合計の数式は以下です。（C37に入ってる式です。）
=SUMIF(21:21,">=2007/10/1 ",29:29 )-SUMIF(21:21,">=2008/4/1 ",29:29 )
29行目が総計で常に変動します。
そこで、A行に総計という文字がでたら、1つ右隣から始まる範囲での式がほしいです。
C37セルにその合計の値が表示されるようにしたいです。
21行目（固定）は日付で「2007/10/1」「2008/2/1」と2/1のように
月初の日付がB21セルから未来へ向かって表示。空白もあります。
（年度集計行を20行目に入れたため。）
then以下どう書くか教えてください。

Sub 総計()
Dim myRng As Range
For Each myRng In Range("A:A")
If myRng.Value = "総計" Then

※※※※※※※※※※

End If
Next myRnｇ

End Sub

回答No.2

xsizukux · ベストアンサー

No.1です。
INDIRECT(MATCH("総計",A:A,0)&":"&MATCH("総計",A:A,0),1)
上記の関数はA列全体から"総計"の行を検索して、"総計"の行全体を範囲として返します。
例)"総計"が31行目にある時、"31:31"を範囲として返してます。

C37の式は、
SUMIF(21:21,">=2007/10/1 ",INDIRECT(MATCH("総計",A:A,0)&":"&MATCH("総計",A:A,0),1))-SUMIF(21:21,">=2008/4/1 ",INDIRECT(MATCH("総計",A:A,0)&":"&MATCH("総計",A:A,0),1))
になるかと思います。

お礼

合計 / 金額 納期年 納期日
2007 2007 集計 2008
発注確度 2007/1/1 2008/1/1 2008/2/1 2008/3/1 2008/4/1
A：内示 確度高
C：今年度案件 11
D：次年度案件
Y：受注 9 9 5 37 30.2
Z：失注 22
(空白)
総計 1 9 2 3 4 5

どうもありがとうございます！
しかしまだうまくいきません。上の図の答えは10にしたいです。
2007/10/1から2008/4/1までの総計数値を出したいです。
発注確度（日付）のあいているセルは合計しないので、総計でいう
ところの9は足す必要ないセルになります。
あと、総計という言葉が上のセルにもう一つあるので、「A:A」という
範囲を「A20：A35」にしたいのですが、そうしたら、式の答えが
「0」になってしまいました。

回答No.3

xsizukux

No.1です。
「A20：A35」を範囲指定する場合は
SUMIF(21:21,">=2007/10/1",INDIRECT(MATCH("総計",A20:A35,0)+19&":"&MATCH("総計",A20:A35,0)+19,1))-
SUMIF(21:21,">=2008/4/1",INDIRECT(MATCH("総計",A20:A35,0)+19&":"&MATCH("総計",A20:A35,0)+19,1))
になります。
"+19"はA20以前の行(1～19行分)を足して正確な行番号にしています。

正解が出ない場合、空白のセルで
=SUM(INDIRECT(MATCH("総計",A20:A35,0)+19&":"&MATCH("総計",A20:A35,0)+19,1))
試してみてください。
"総計"行の合計値が表示され、正解ならこの関数には問題が無く
SUMIF()関数の指定に問題があることになります。

あと、
発注確度 2007/1/1 2008/1/1 2008/2/1 2008/3/1 2008/4/1
総計 1 9 2 3 4 5
発注確度(日付)データ5に対して総計データ6あるのですが、2007/1/1
に対応する総計値はどれなのでしょうか?
日付に9 2 3 4 5を対応させても正解の10にはなりません^^;

お礼

ありがとうございます。
うまくいきました。

>あと、
>発注確度 2007/1/1 2008/1/1 2008/2/1 2008/3/1 2008/4/1
>総計 1 9 2 3 4 5
>発注確度(日付)データ5に対して総計データ6あるのですが、2007/1/1
>に対応する総計値はどれなのでしょうか?
>日付に9 2 3 4 5を対応させても正解の10にはなりません^^;
すみません。説明不足でした。
総計「9」の数値の発注確度（日付）がないんです。年度の集計列
だったので。この表で言う2008/3/1までの計がほしかったのでした。

とてもむずかしい関数ですが（理解を超えてる）、回答いただけて感謝です。

回答No.1

xsizukux

参考までに

MATCH関数、INDIRECT関数の組み合わせで"総計"の範囲を取得されたら
どうでしょうか?
セルC37の式"29:29"の箇所を変更
INDIRECT(MATCH("総計",A:A,0)&":"&MATCH("総計",A:A,0),1)

お礼

ありがとうございます。

発注確度 2007/1/1 2007/10/1 2008/1/1 2008/2/1
A：内示 確度高 5
B：入札 正式見積
C：今年度案件 8 8 5
D：次年度案件
Y：受注 11 11 9 15
Z：失注 4
(空白)
総計 8 11 19 9 29

※こんな感じの表です。
=SUMIF(21:21,">=2007/10/1 ",INDIRECT(MATCH("総計",$A$21:$A$34,0)&":"&MATCH("総計",$A$21:$A$34,0),1))-SUMIF(21:21,">=2008/4/1 ",INDIRECT(MATCH("総計",$A$21:$A$34,0)&":"&MATCH("総計",$A$21:$A$34,0),1) )

式を頂いた回答に加えてみましたが、エラーになりませんが、
正解の数字が出てきません。どこをみているかが分からないです。