エクセルの合計について教えてください。

質問

エクセルの合計について教えてください。
すごく単純な質問です。
商品の今月の毎日の売り上げリストです。
毎日売り上げがあるので、日々、行数が増えていきます。
何行増えるかはわかりません。
トータルの値も、もちろんそれに応じて増えていきます。
で、
上のほうに合計を表示するなら分かるのですが、
画面の一番下に、合計を常に表示させたいのです。
でも、タイトル行も固定したい。
できますか？

回答No.6

tom04 · ベストアンサー

ほんとぉ～！に何度もごめんなさい。
前回のコードは正しく表示されませんでした。

E列の最終行の取得がうまく機能していなかったようなので、
データがない場合でもE列の合計セルの「0」と手入力して↓のコードでマクロを実行してみてください。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j As Long
i = Cells(Rows.Count, 5).End(xlUp).Row - 2
j = 4
j = Target.Column
If j <> 4 Then Exit Sub
Range(Cells(i + 1, 1), Cells(i + 1, 5)).Copy
Range(Cells(i + 2, 1), Cells(i + 2, 5)).Insert (xlDown)
Cells(i + 3, 5).Value = WorksheetFunction.Sum(Range("E2:E" & i + 1))
Application.CutCopyMode = False
Cells(i, 1).Offset(1).Select
End Sub

こんどはうまく表示されると良いのですが・・・

本当に何度も失礼しました。m(__)m

お礼

本当に、色々検討していただいて感謝します。
Eの総合計にゼロを入れたらうまくいきましたよ。
ありがとうございます。

紙ベースの帳簿って、トータルは一番下ですよね。
エクセルでも集計にすれば下に合計が来ます。
見やすさから言ってもそれが自然です。
単純な「総合計を表示する」ことがなぜできないのかなと不思議だったのです。
ありがとうございました。

回答No.5

tom04

No.3・4です！
何度もごめんなさい。

前回の投稿で間違いがありました。
＞行の最終アクティブ列（手入力する最後のセルの列番号）は「4」としています。
結局E列をTagetとしていますので・・・

の部分は
E列がTargetではなく「D列」の間違いです。
どうも失礼しました。

それからコードの説明が不十分のような気がしますので、
もう一度コードと説明文を載せておきます。
（またまた・・・前回より少しコードが変わっています）

Private Sub Worksheet_Change(ByVal Target As Range)
'変数の宣言
Dim i, j As Long
'「i」はE列の最終行（合計セル）より1行上の行番号を設定
i = Cells(Rows.Count, 5).End(xlUp).Row - 1
'変数「j」を「4」に設定
j = 4
'「j」をTarget（選択セル）の列番号に設定
j = Target.Column
'「j」が4以外であればマクロを実行しない
If j <> 4 Then Exit Sub
'A列の最終行の1行上～E列の最終行の1行上（空白行）をコピー
Range(Cells(i, 1), Cells(i, 5)).Copy
'A列～E列のコピー元の行の下に行を挿入して、貼り付け
Range(Cells(i + 1, 1), Cells(i + 1, 5)).Insert (xlDown)
'合計セルの値はE2～E列の最終行より1行上までの合計を表示
Cells(i + 2, 5).Value = WorksheetFunction.Sum(Range("E2:E" & i))
'コピーモードを解除
Application.CutCopyMode = False
'A列の最終行（合計文字列セルは除く）の1行下のセルを選択
Cells(i, 1).Select '←ここでの「i」は行挿入前の「i」の値になる
End Sub

上記のコードは説明文も含めてこのままコピー＆ペーストしても構いません。
以上、あくまで一例ですので、他に良い方法があるかもしれません。

どうも何度もごめんなさいね。m(__)m

回答No.4

tom04

No.3です！
補足を読ませてもらいました。
昨日はあわててコードを入力したので細かいところは確認せずに投稿してごめんなさい。

＞質問1　に関して
おっしゃられるように合計セルに数式を入れていると3行目以降にデータがあると
間違った数値が表示されますので、
当初のE4セルには何もしません。
コードで最後にE2～合計行の1行上までを合計するようにしています。

＞質問2　に関して
今こちらでもう一度表を作成（前回の表の配置通り）を作成し、同じコードを張り付けて実行してみました。
エラーは確認されませんでした。

それからコードの説明を少しさせていただきます。
前回の表で「小計」の列に数式が入っているとしてのコードですので
当初のE列の数式（E2セルの数式）は
=IF(COUNTBLANK(C2:D2),"",C2*D2)　としてE3セルまでオートフィルでコピーしています。

それからコード内の「i」の値を勝手に「4」としていますが
5列目は数式が入っている前提としていますので、E列がアクティブになることはないということで
行の最終アクティブ列（手入力する最後のセルの列番号）は「4」としています。
結局E列をTagetとしていますので、E列のアクティブが変更されるとマクロが実行されるようにしています。
ご自身の表で「j」部分は適宜変更してみてください。

最後に合計行の1行上の空白セルを行挿入し、コピー＆ペーストしていますが
単純に行挿入だけでは空白行にある数式が反映されませんので、コピー＆ペーストにしています。

尚、前回のコードではこのカット＆ペーストモードがそのままですし、
余計なコードもありましたので少しコードを訂正させてください。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j As Long
i = Cells(Rows.Count, 2).End(xlUp).Row - 2
j = 4
j = Target.Column
If j <> 4 Then Exit Sub
Range(Cells(i + 1, 1), Cells(i + 1, 5)).Copy
Range(Cells(i + 2, 1), Cells(i + 2, 5)).Insert (xlDown)
Cells(i + 3, 5).Value = WorksheetFunction.Sum(Range("E2:E" & i + 1))
Application.CutCopyMode = False
Cells(i, 1).Offset(1).Select
End Sub

ではどうでしょうか？

以上、長々と失礼しました。
何とかエラーにならないことを祈っています。m(__)m

回答No.3

tom04

こんばんは！
参考になるかどうか判りませんが・・・
元の表がどのような感じかわからないので
勝手に↓のような表を作ってみました。
E列には数式が入っているものとしています。

最初の表作成段階で合計と入力行の間は必ず2行あけるようにしています。

操作したいSheet見出し上で右クリック　→　白い画面が出ますので
↓のコードをコピー＆ペーストしてみてください。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i, j As Long
i = Cells(Rows.Count, 2).End(xlUp).Row - 2
j = 4
j = Target.Column
If j <> 4 Or Cells(i, 5) = "" Then Exit Sub
Range(Cells(i + 1, 1), Cells(i + 1, 5)).Copy
Range(Cells(i + 2, 1), Cells(i + 2, 5)).Insert (xlDown)
Cells(i + 3, 5).Value = WorksheetFunction.Sum(Range("E2:E" & i + 1))
Cells(i, 1).Offset(1).Select
End Sub

これでD列（単価）が確定すると行が1行挿入され、最下段に合計が表示されると思います。
画像では右側の表が左側のように変化していきます。

以上、この程度ですが
参考にならなかったら読み流してくださいね。m(__)m

補足

ありがとうございます！
とても興味深いです。
もう少し、詳しくお教え願えますか？
空白の2行の表までは作りました。

質問１
小計の合計欄に入れる数式、（当初Ｅ４）は
=SUM(E2:E3)　でいいのでしょうか。
行が増えていったらおかしいですよね？

質問2
空白の1行目に日付からデータを入力し始めた時、
どのセルに入力しても、エラー表示が出ます。

実行時エラー1004
アプリケーション定義またはオブジェクト定義のエラーです。
デバック
If j <> 4 Or Cells(i, 5) = "" Then Exit Sub　が黄色表示

質問３
1行目の入力が終わっても上記のエラーで新しい1行は増えません、
しかし、空欄の2行を入力すると、1行目のコピーが1行増えます。

わたしは、関数は理解していますが、ＶＢはさっぱりです。
出来れば、一連のコードに書かれている意味をお教え願えませんでしょうか。
ずうずうしくて申し訳ないんですが。

回答No.2

noname#187541

こんにちは。

1行目がタイトルで2行目から売り上げが入力されているとします。
合計は1行空けて表示します。

列はどこでもいいですが、仮にA列とします。
合計の行には　=SUM(INDIRECT("A2:A"&ROW()-2))　と入力します。

売り上げの入力が何もない状態だと　合計は3行目になります。
売り上げの入力はちょっと面倒ですが、合計行の一つ上、合計が3行目に有れば2行目を選択して行を挿入してから入力すれば、合計行は下に下がります。

あとはVBA(マクロ)を使う方法でしょうか。

回答No.1

noname#145943

http://okwave.jp/qa/q2795126.html
か
http://okwave.jp/qa/q5704611.html
くらいしかなさそうです。