excel2003,VBAの質問です

Question

こんばんは、よろしくお願いします。
小計を計算したいのですが、このような表があります

|A|B|C|D|E|F|G|
|:--|:--:|--:|
|カツ丼|玉子丼|小計|カツ丼|玉子丼|小計|総合計|
|2|1|3|4|1|5|8|

ここでＣ列の小計とＦ列の小計を足したものがＧ列の総合計になっています。
Ｇ２のセルには数式　=SUM(C2,F2)　が入っていて総合計が出るようになっています。
そこでＧ列の前に列を３行追加して新たにカツ丼、玉子丼、小計の列を作ります。

|A|B|C|D|E|F|G|H|I|J|
|:--|:--:|--:|
|カツ丼|玉子丼|小計|カツ丼|玉子丼|小計|カツ丼|玉子丼|小計|総合計|
|2|1|3|4|1|5|6|2|8|8|

このような表を作りたいのですが、表を作るのに自動化したいです。
この時、J2のセルにはまだ、数式 =SUM(C2,F2)が入っているのですが、
これを自動で　=SUM(C2,F2,I2)に変えてJ2の値が16になるようなコードを作りたいです。
I2をアクティブにして

Dim Rng1 as string
Dim Vlu1 as string

Rng1 = ActiveCell.address
Activecell.Offset(0,1).select
Vlu1 = Activecell.Formula
Activecell.Formula = Vlu1 + Rng1 'こんなようにして、計算式に３つおきのセルの
　　　　　　　　　　　　　　　　　　 '値を増やしていきたいのですが

このように、総合計の計算式に３行ごとに小計のセルの値を足していきたいのですが、
どのように増やしていいのかわかりません、セル番地の取得方法と、数式にくっつけるやり方などわかりません、アドバイスよろしくお願いいたします。

Answer 1

そもそも総合計の計算に動的な数式を毎回書き換えるのは効率的ではありません。
SUMIF関数を使うことで小計のみを合計することができるので、あらかじめその数式を設定しておけばよいかと思います。
総合計列2行目に以下を入力。

=SUMIF(INDIRECT("A1:" & ADDRESS(1,COLUMN()-1)), "小計",INDIRECT("A2:" & ADDRESS(2,COLUMN()-1)))

もしくは質問コードにある

Activecell.Formula=

に上記を設定する。

Comments

[kintarock777]

回答ありがとうございます。
SUMIF関数を試してみました、３列増やしてみても期待通りの結果がでました。
毎日３列づつ増えてく作業に対応できそうです。
ただ、実際は行数がたくさんあるのでその行数分コピーしながら、行の数値を一つづつ変えなければならないので、そこが手間取りました。一度作ってしまえば問題ないのですが、一ケ月ごとに作り直すので、
関数のところが自動化で数値を変えられるようにもう少し考えてみます。

[ttyp03]

行に関してですが、質問の例では1行しかなかったの2行目固定で書いてしまいました。
「自身の行」を対象に行うのであれば、次のようにROW関数を組み合わせることで、全ての行に同じ数式を使うことが可能です。
=SUMIF(INDIRECT("A1:" & ADDRESS(1,COLUMN()-1)), "小計",INDIRECT("A"&ROW()&":" & ADDRESS(ROW(),COLUMN()-1)))

[kintarock777]

自分で作った例題で=SUMIF(INDIRECT("A1:" & ADDRESS(1,COLUMN()-1)), "小計",INDIRECT("A"&ROW()&":" & ADDRESS(ROW(),COLUMN()-1)))ところ、うまく機能しました
本来は縦横逆の表で =SUMIF(INDIRECT("B3:"&ADDRESS(ROW()-1,1)),"小計",INDIRECT(COLUMN()&"3:" & ADDRESS(ROW()-1,COLUMN())))と書いたんですがうまく機能しません、どうしてでしょうか？

[ttyp03]

おはようございます。回答遅くなり申し訳ありません。

こうでしょうか。
=SUMIF(INDIRECT("A3:"&ADDRESS(ROW()-1,1)),"小計",INDIRECT("B3:" & ADDRESS(ROW()-1,2)))
単純に参照している範囲が間違っているだけです。
どこが間違っていたかは良く見比べてみてください。

[kintarock777]

回答ありがとうございます。
今回教えてもらった　　=SUMIF(INDIRECT("A3:"&ADDRESS(ROW()-1,1)),"小計",INDIRECT("B3:" & ADDRESS(ROW()-1,2)))
は、最初に教えてもらったコードの入れ替えで機能を確認できてます、まちがいなく動きました、すみません(#^.^#)
そのあと教えていただいたすべての行（縦横入れ替えですべての列）に同じ式を使いたいという方のコードの変更がまだうまく出来ていなかったのですが、
それで自分自身で書いたコードが
=SUMIF(INDIRECT("A3:"&ADDRESS(ROW()-1,1)),"小計",INDIRECT(COLUMN()&"3:" & ADDRESS(ROW()-1,COLUMN())))
です、ややこしくなってすみません。

Answer 2

私も、他回答にあるようにSUMIFを使えばいいと思います
でも、何かここでは示していない深いご事情があるのでしょう。

シンプルに”小計”だったら下のセルを足すって感じのではどうでしょうか？

例えばこんな感じで・・

Function my_sum(TargetRange As Range, TargetStr As String) As Long

Dim RangeBuf As Range
Dim Res As Long

For Each RangeBuf In TargetRange
   
   If RangeBuf.Value = TargetStr Then
      Res = Res + CLng(RangeBuf.Offset(1, 0).Value)
   End If
   
Next RangeBuf

my_sum = Res

End Function

第一引数に２行目、第二引数に、"小計"として使うイメージです。色々と問題があるコードですが・・・

Comments

[kintarock777]

回答ありがとうござします、前出のSUMIF関数を総合計のセルに埋め込みましたところ、希望通りの結果がでました、ありがとうございます。
ただrinrenさんのご推察の通り話はもう少し複雑で、毎日３列ずつ増えていく上に行数も複数あって（実際は購買年齢層や性別ごとに統計をとっている）それをすべてコピーして少しずつ変えて関数を入れていく作業はなかなか手間がかかりました、ともするとsum関数でCTRLキーを押して、セルを一つ増やしたあとそのままオートフィルでコピーしたほうが速いかもというのが、現在思っている率直な感想です。
もう少し色々試して、よいコードを作れるように試行錯誤してみます。ありがとうございます。

[kintarock777]

すみません,rinrenさんの書いてくださったfunctionは使い方がわからなくて、まだ試してないです、
できればどのように使うか教えてもらえるとたすかるのですが。

[rinren]

結果が欲しいセルに
=my_sum(項目名範囲,足したい項目名)
説明を加えると、
項目名が入った範囲は一行を想定してます。
その範囲をループして項目名だったら1行下のセルを加算します。

[kintarock777]

ありがとうございます。
試してみます。

Answer 3

小計・総合計の計算にSUMの代わりにSUBTOTALを使用してはどうでしょうか？
SUBTOTALを使用することで重複無しの合計を取得できるため、いくらか簡単になるかと思います。

SUBTOTAL 関数 - Office サポート

カツ丼	玉子丼	小計	カツ丼	玉子丼	小計	カツ丼	玉子丼	小計	総合計
2	1	=SUBTOTAL(9,A2:B2)	4	1	=SUBTOTAL(9,D2:E2)	6	2	=SUBTOTAL(9,G2:H2)	=SUBTOTAL(9,A2:I2)

カツ丼、玉子丼で行を分けた方がより楽になるかとは思いますが……

Comments

[kintarock777]

回答ありがとうございます。
subtotal関数を試してみました。subtotal関数の数式が入ったところだけ合計してくれるんですね、
期待しているものに近いと思います。
ただ、毎日３行づつ増えていくのでそのたびに数式を書き換える作業が伴っていくのでそれが自動化できるかはちょっと試してみます。
あと実際はカツ丼、玉子丼、それぞれの購買年齢層などが加わってきますので、行数が増えてきてそこをコピーできるかはまだ試していないのですが、とりあえず試作してみます、ありがとうございました。

[imihito]

SUBTOTALの処理は逆で、SUBTOTALが使われているセルを集計から除外する、といったものです。

手作業で行う場合の話ですが、総合計の前に一列追加＆参照範囲を拡張しておき、間に列を挿入していけば楽できないでしょうか？
私の回答を例にすると、
1. J列に空列挿入
2. 総合計を =SUBTOTAL(9,A2:J2) に変更
3. J-L列を選択して列挿入（総合計の数式自動拡張）
4. J-L列にGHIのセルをコピー
5. 3.に戻る
といったイメージです

[kintarock777]

回答ありがとうございます、もう一度試させていただきます。