行を追加した時の、値の合計範囲

質問

行を追加するマクロがあり、実行すると「合計」行の1行上に行が追加されます。
行が追加された分「合計」のある行が下がっていくんですが、その関数が変動してくれなくて困っています。

　　　Ａ　　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　　　Ｆ　　　Ｇ　　　Ｈ　　　Ｉ
1 　　　商品の基本データ
・　　　　・
・　　　　・
14　　　商品の基本データ
15
16　　 　　日付 　入庫 　出庫　　　　　　　　　　　　　　 本日在庫　
17　2012/04/01 　100　　　　　　　　　　　　　　　　　　 　　100
18　2012/04/05 　50　　　　　　　　　　　　　　　　　　　 　 150
19　2012/05/03　 　　　　100　　　　　　　　　　　　　　　 　50
20　2012/05/04 　300　　　　　　　　　　　　　　　　　　　　 350
21　　　　・　　　　　　　　　150　　　　　　　　　 　　　　　　 200
22　　　　・　　　　　　　　　　50　　　　　　　　　　　　　　　　 150
23　　　　・　 　　　　200　　　　　　　　　　　　　　　　　　　 　350
24　　　　・　　　　　　　　 　10　 　　　　　　　　　　　　　　 　340
(ここに行が挿入されていく)
25 合計　　　　　　　(1)　　 (2)　　　　　　　　　　　　　 　　340(=(1)-(2))

(1)=IF(COUNT($C$17:$C24)=0,"",SUM($C$17:$C24))
(2)=IF(COUNT($D$17:$D24)=0,"",SUM($D$17:$D24))

現在こんな感じで式を入れています。$C24と$D24が$C25...、$D25...等動いていってくれるといいんですが・・。
他にもSUMPRODUCTやOFFSETなど色々試しましたが、上手くいきません。
行が増えていく(セル参照範囲が変わってくれない)、C:CやD:Dとして設定できない、データが連続していない、などが原因だと思っています。

A列には何もありません。
E～H列は他にもデータが入っています。
マクロで、B17からHの合計の1行上までを日付でソートすることがあります。

合計行の上に空白行C25を入れ、合計行C26にSUM(C17:C25)とし、
挿入する位置を空白行C25の上とすれば、SUMは挿入行を抱えていってくれるんですが、
この場合空白の行25を高さ0の不可視にさせなければならず、利用する側としては21・22・23・24・26と、25が飛んでいるのが気になるものと思います。
できればそういう疑問を持たせることなく可動させたいです。

因みにVBAでWorksheet_ChangeやWorksheet_Selectionhangeなどは既に使用しているので、
マクロで自動で合計値を入力することはできないと思います。
ボタンで動かすマクロでなら出来そうですが、合計を逐一表示するのにマクロボタンを設置するのもどうかと思うので・・。

いくらネットで探しても方法が見つからなかったので、何方かご教授宜しくお願い致します。

回答No.2

keithin · ベストアンサー

(1)について
=IF(COUNT($C$17:INDEX(C:C,ROW()-1))=0,"",SUM($C$17:INDEX(C:C,ROW()-1)))

(2)について
=IF(COUNT(INDIRECT("R17C:R[-1]C",FALSE))=0,"",SUM(INDIRECT("R17C:R[-1]C",FALSE)))

などのようにしてみます。
同様の工夫はOFFSET関数を使っても，勿論出来ます。

お礼

そのままコピペで動作致しました！
例では合計は25行目にしたんですが、実際には40行目くらいにいたもので
ご回答頂いた関数やコードを自分で行の数値などを変えて当てはめなければいけないなと思っていたので、そのまま使えたことに感激しました。

(1)と(2)で関数の使い方が違いますが、これはどちらでも使えるということですよね。
ROWやR17Cというセルの参照の仕方など、自分にはまだ理解できていないところなのですが、どちらかの関数式をＣとＤの両方に使いたいと思います。

(1)についてくださった式はＤにオートフィルすると崩れるようなので、Ｄに合わせたセルの参照をし直さないといけないようですが
(2)についてくださった式はＣにオートフィルしてもそのまま使えました。

今回は直すのに簡単そうな(1)の関数式を使わせて頂きたいと思います。
ご回答ありがとうございました！

回答No.3

silverfd

これが一番簡単ですね。

(1)=SUM(INDIRECT("C2:C"&ROW()-1))
(2)=SUM(INDIRECT("D2:D"&ROW()-1))

Indirect関数とは、文字列を数式的な意味にしてくれる関数です。

ちなみに、余計なお世話ですが、マクロについて述べさせて頂きます。

＞VBAでWorksheet_ChangeやWorksheet_Selectionhangeなどは既に使用しているマクロで自動で合計値を入力することはできないと思います。

分岐を使えば（例えば集計行に範囲名をつけて最初に取得、処理後に行位置が変動していれば合計処理に分岐とか）、イベントプロシージャでも可能ですし、

＞ボタンで動かすマクロでなら出来そうですが、合計を逐一表示するのにマクロボタンを設置するのもどうかと思うので・・。

行挿入マクロの後処理で合計計算処理を追加すれば、ボタンなどこのためには必要ないです。

一応、マクロの可能性について感じていただきたかったので、あえて追加しましたが、このケースでは上記Indirect関数を利用した数式で充分なのは変わりません。

お礼

keithinさんと同じ作りの関数を頂けたのですが、空白の時の場合の処理を入れたまま関数を考えて下さったkeithinさんの方をベストアンサーとさせて頂きました。

マクロの件ですが、今回Worksheet_Changeは入力後セルから指定セルへの移動に使い、
Worksheet_Selectionhangeは範囲によってエンターで進む方向を設定するために使いました。
ここにどういう風に合計処理を組み込むのかはもっと勉強しないと出来そうにありません・・ｏｒｚ
ただそういうことも出来るのだと教えて頂けたのは大きな収穫です。
為になるご回答をありがとうございました！
今後も関数やマクロは利用していかないといけないので、今回のご回答を参考に考えていきたいと思います。

回答No.1

kybo

行を追加するときにマクロを使っているのであれば、
その行を追加のマクロの下の方にでも、数式自体を変更するようにしておけばいいのでは。

例えば合計行が25行目だとして、

gokei_gyo = 25
Range("C" & gokei_gyo).Formula = "=IF(COUNT($C$17:$C" & gokei_gyo - 1 & ")=0,"""",SUM($C$17:$C" & gokei_gyo - 1 & "))"
Range("D" & gokei_gyo).Formula = "=IF(COUNT($D$17:$C" & gokei_gyo - 1 & ")=0,"""",SUM($D$17:$C" & gokei_gyo - 1 & "))"

とすれば、数式もかわります。

お礼

なるほど！゜д゜
確かに、行追加のマクロの最後に組み込めばいいだけですね・・
その発想は思いつきませんでした。盲点でした・・・。

ただ、くださったマクロコードを行追加マクロの後半に入れてみたのですが、上手く動作させることができませんでした。
gokei_gyoというのがよくわからないのですが、
「合計」のある行、例えばここでは25行目を一括で範囲指定し、「gokei_gyo」と名前をつければよかったのでしょうか。
VBAはやはり独学ではキツイですねｗ；

ご回答ありがとうございました！