SAM関数に挿入行分も含むように

投稿者 : blue     投稿日時 : 2024/07/07(Sun) 20:51:58     OS : 未指定     EXCEL : 未指定

---

ご質問させてください。

●シートA
　 A列　　　B列
1　田中　　 12
2 鈴木　　 24
3 佐藤　　 58
4 山田　　 10
5 合計　　 =SUM(B1:B4)
6 空白　　空白
7 後藤　　 11
8 今井　　 12

上記シートがあり、自作vbaで挿入行を指定し、新規の人を行追加するものを作成しました。
しかし、5行目に新しい人を追加すると、追加した行がSUM関数に含まれず、手作業でSUM関数を修正しています。（下記のようになる）

●シートA（困っている状況）
　 A列　　　B列
1　田中　　 12
2 鈴木　　 24
3 佐藤　　 58
4 山田　　 10
5　新規　　 77 ←5行目に追加
6 合計　　 =SUM(B1:B4)　 ←SUM関数に5行目が含まれない
7 空白　　空白
8 後藤　　 11
9 今井　　 12

ご助力お願いできないでしょうか。。。
よろしくお願いします。

投稿者 : テーブル     投稿日時 : 2024/07/07(Sun) 21:15:47

---

A1～A4までをテーブルとしておく
A5に=sum(A1:A4)

この状態で
テーブル範囲内の行に行を挿入しても,
sum関数の範囲は自動で拡大してくれます
やってみ

投稿者 : てらてら     投稿日時 : 2024/07/08(Mon) 06:38:14

---

こんにちは。

名前の定義を使ってはいかがでしょうか？
A1:B4を選択して、メニューから「数式」＞「名前の定義」で適当な名前を登録しておきます。
この範囲で、行の挿入や削除が行われてもSUM関数などは追従します。

VBAでやりたければマクロの記録でコードを確認できます。

投稿者 : higeru     投稿日時 : 2024/07/08(Mon) 08:20:33

---

> 上記シートがあり、自作vbaで挿入行を指定し、新規の人を行追加するものを作成しました。

　ということは何行目に挿入したかは分かっている訳で、その行番号を r で、その行の下が SUM だとしたら

Cells(r + 1, "B") = "=SUM(B1:B" & r & ")"

とSUM関数の引数を変えてやればよいかと。

投稿者 : tek     投稿日時 : 2024/07/08(Mon) 10:05:59

---

失礼します。
セル.value=値とするとautosumが崩れますが、
セルに手入力で値を入れるか、
セルに数式バーから値を入れるとautosumが活きます。
sendkeysを使っていますので、Excel画面から実行してください。

sendkeysに変わる方法が何かあった気がしますが耄碌していて思い出せ（思いつき）ません。
どなたかご存じ有りませんか？

Sub main()
    Const 値 = 77
    Dim ret
    
    データセット
    数式セット
    行挿入
    ret = InputBox("1:Valueで入れる" & vbCrLf & "0:数式バーから入れる", , 0)
    If ret = "0" Then
        キー入力 値
    ElseIf ret = "1" Then
        Selection.Value = 値
        Selection.Offset(1).Select
    End If
End Sub
Private Sub データセット()
    Range("A:B").Clear
    Range("A1:B5").Value = [{"田中",12;"鈴木",24;"佐藤",58;"山田",10;"合計",""}]
    Range("A7:B8").Value = [{"後藤",11;"今井",12}]
    Range("B5").Interior.Color = vbYellow
End Sub

Private Sub 数式セット()
    Range("B5").Formula = "=SUM(B1:B4)"
End Sub

Private Sub 行挿入()
    Rows("5:5").Insert
    Range("A5").Value = "新規"
    Range("B5").Select
End Sub

Private Sub キー入力(a)
    DoEvents: DoEvents
    
    With CreateObject("WScript.Shell")
        .SendKeys "{F2}" & a & "{ENTER}"
    End With
End Sub

投稿者 : ごんぼほり     投稿日時 : 2024/07/08(Mon) 17:47:02

---

最初にB5セルの式を
=SUM(B1:OFFSET(B5,-1,0))
としてはどうでしょう