マクロを使用した表において集計を自動表示する

質問

エクセルのマクロでＡＢＣ集計表なるものを作成しています。ボタンを押す毎に下位に行が追加され表ができる形です。行を挿入する毎に右側の３列に設けたセルにA～C品の数量を記入し、その集計を各２行目のセル(色付きのセル）に表示できるようにしたいと考えています。例えばＡ品の集計をＫ６に”=SUM（ｋ７：ｋ７）”として、ボタンを押して下へセルを追加しますと、集計が正しくされず、計算式を確認すると”=SUM（ｋ８：ｋ８）”となっており、数列１のｋ７が固定されていません。
マクロ文を入れないとできないのでしょうか、ご教授下さい。よろしくお願いします。

回答No.6

neochi_life · ベストアンサー

もしも、「常に７行目に新しい数値の入力欄を作る」というのが正しい仕様であれば、今までの方法は使えません。

行の挿入マクロは元のままにしておいて、

Sub sumary()
Dim i, smr As Integer
smr = 0
For i = 11 To 13
For j = 7 To Cells(Rows.Count, i).End(xlUp).Row
smr = smr + Cells(j, i)
Next j
Cells(6, i) = smr
smr = 0
Next i
End Sub

集計用に、こんなマクロを使ったらどうでしょう？
行の挿入ボタンで常に７行目と６行目の間に行が挿入され、過去の入力は下へ送られていくわけですが、このマクロで常に７行目から最終行までの値を合計して、それぞれの６行目に書き込むことができます。

お礼

大変ありがとうどざいました。マクロについては、今回初めての初心者です。早速使用しています。

回答No.5

neochi_life

Rows("7:7").Select
Selection.Insert Shift:=xlDown

これがダメです。
行の挿入が、毎回７行目の上に挿入されてますので、集計をやる対象がずれていくのです。

現在のマクロを利用するとして

Sub Macro1()
Macro1 Macro
Sheets("（削除禁止シート）").Select
Rows("1:1").Select
Selection.Copy
Sheets("ABC集計表").Select
Dim 最終行 As Integer
Dim 基準セル As String
最終行 = Cells(Rows.Count, 11).End(xlUp).Row + 1
基準セル = "K" & 最終行
Range(基準セル).EntireRow.Insert
Range("A2:C3").Select
End Sub

これでやってみてはいかがですか？

それとも、新しい数値を入力する欄が常に集計セルの１行下にある　というのが仕様ですか？

回答No.4

neochi_life

＞=SUM(K7:K8)と入力してやってみましたが、=SUM(K8:K9)となり、K7:K9となりません
行の挿入が、６と７の間になっているからですね。

行の挿入ボタンでマクロを動かしていると思いますが

Sub inserRow()
Dim 最終行 As Integer
Dim 基準セル As String
最終行 = Cells(Rows.Count, 11).End(xlUp).Row + 1
基準セル = "K" & 最終行
Range(基準セル).EntireRow.Insert
End Sub

これでやってみてください。
ここでは、Ｋ列を基準に見てますので、Ｋ列のデータがある最終行を取得して＋１したところに行を追加しています。

うちでやってみたところ、
=SUM(K7:K8)　が狙い通りに　=SUM(K7:K9)　となりましたよ

補足

早速、試してみましたが行を挿入するたびに、K7：K8→k8:k9→k9:k10→k10:k11と変わります。
恐らく行挿入のマクロに問題があるように思います。いかがでしょうか
マクロ文は以下の通りです、
Sub Macro1()
Macro1 Macro
Sheets("（削除禁止シート）").Select
Rows("1:1").Select
Selection.Copy
Sheets("ABC集計表").Select
Rows("7:7").Select
Selection.Insert Shift:=xlDown
Range("A2:C3").Select
End Sub

回答No.3

neochi_life

ボタンを押すと数値入力欄が下に追加され、
数値入力欄のＫ列～Ｍ列それぞれの合計が
６行目に表示されるってことでいいんですね？

わざわざ、Ｋ～Ｍの右の方を使ってるということは、
Ａ～Ｊにもなんらかの表などがあって連動したいとか、
事情があるのかな？などと考えています。

---------------------------------------------

一例として
特にマクロなど組まなくても、最初からセルのK6,L6,M6に

=SUM(K7:K8) , =SUM(L7:L8) , =SUM(L7:L8)

としておき、行の追加はデータのある最終行＋１とする
（手動で行を挿入してもいいとは思いますが・・・）
８行目は空白となってますので、
行が挿入されるのは、最初７行目と８行目の間になります。
すると、集計セルの式は

=SUM(K7:K9) , =SUM(L7:L9) , =SUM(L7:L9)

と書き換わります

いかがでしょうか？

どうしても無駄な行を出したくない場合には、マクロを組みましょう

補足

早速のご回答ありがとうございました。
=SUM(K7:K8)と入力してやってみましたが、=SUM(K8:K9)となり、K7:K9となりません。何か原因があると思われます。他の方法も試してみますが、わかりましたらお知らせ下さい。

回答No.2

keithin

２行目の色つきセルに，最初から
=SUM(K7:K1000)
などのように十分下のセルまで合計する式を入れておき，あとは単純に下へ下へとデータを書き足していけばそれで終わりのようです。特にわざわざマクロを持ち込む必要が見あたりません。

マクロを使って「行を追加する」のが重要なのなら，その部分はそのまま残しておいて今後も使い，上述のようなSUMの式を事前に入れておくことで「マクロにSUMの式(集計の式)を入れさせる」のはヤメにするのでもＯＫです。

回答No.1

neochi_life

図や実際の表の画像でも添付してもらわないと、
この文章だけじゃ仕様のイメージがさっぱりわかりません。

例を書くいてあるけど、もっと具体的に。

＞右側の３列に設けたセル
具体的にどこですか？

＞集計を各２行目のセル(色付きのセル）に表示
具体的にどこですか？

＞Ｋ６に”=SUM（ｋ７：ｋ７）”
これって、Ｋ６＝Ｋ７と一緒

申し訳ないですが、もっときっちりと仕様説明してください。

補足

ファイルを添付する予定でしたがファイルの添付方法がわかりませんでした。

　　　　　　　　ボタン
　　　　　　　　　　　　　　　　　　　　　　　　　　k列　　　L列　　M列
5行　 　　　　　　　　　　　　　　　　　　　　　　　Ａ品　　Ｂ品　　Ｄ品
6行　　　　　　　　　　　　　　　　　　　　　　　　集計セル　集計セル　集計セル
7行　　　　　　　　　　　　　　　　　　　　　　　　数入力　数入力　数入力
以下、ボタンを押して下位へセルを追加して、k~Mへ数値入力して、各製品の集計をK6、L6、M6へ
セルが追加されるごとに合計を表示さたいのです。