昨日の続き：行挿入時の合計範囲について

質問

昨日の質問の件ですが、合計行が明細の最後ではなく、明細の先頭にある場合は難しいですよね。いろいろ試行錯誤してみたのですが、いい方法が見つかりません。
（知識不足もありますが）
何か方法があれば、ご教授ください。
よろしくお願いします。

回答No.7

Thiotimoline · ベストアンサー

↓でわかりますか？
B C
2 Ａ合計行 4 [C2]=SUM(OFFSET(C2,1,0):OFFSET(C7,-1,0))
3 Ａ明細 1
4 Ａ明細 1
5 Ａ明細 1
6 Ａ明細 1
7 Ｂ合計行 40 [C7]=SUM(OFFSET(C7,1,0):OFFSET(C12,-1,0))
8 Ｂ明細 10
9 Ｂ明細 10
10 Ｂ明細 10
11 Ｂ明細 10
12 datend ----

お礼

分かりました。
試してみましたが、うまくいきました。
本当にありがとうございました。

回答No.10

chayamati

＞昨日の続き？
　この事情はわかりませんが
　合計行が1行だけなら明細行の外であればどこにあってもよいです。
　私は、見出し行の次に合計行、以下を明細行としています

お礼

ご指摘ありがとうございます。

回答No.9

bunjii

別解
B1セル（A合計）に次の数式を設定します。
=SUM(OFFSET(A1,1,1,COUNTIF(OFFSET(A1,1,0,COUNTA(A:A)),LEFT(A1,1)&"*")))
B1をB5セル（B合計）へコピーすれば自動的にA合計とB合計が算出されます。
但し、A列の?明細nは中間にブランクがあると正しい値になりません。

お礼

いろんなやり方を教えて頂き、ありがとうございました。

回答No.8

bunjii

回答No.5への補足にある模擬データでしたらB1セルへ次の数式を入力してください。
=SUMIF(A2:A5,LEFT(A1,1)&"*",B2:B5)
B5セルの数式はA明細とB明細の行数が同じなのでB1セルをコピー&ペーストでB5セルへ複写すれば数式の行番号が自動的に参照範囲に変化します。

B1=SUMIF(A2:A5,LEFT(A1,1)&"*",B2:B5)
　　　　　　↓　コピー&ペースト
B5=SUMIF(A6:A9,LEFT(A5,1)&"*",B6:B9)

お礼

このやり方も出来ました。
本当にありがとうございました。

回答No.6

msMike

[No2補足]へのコメント、
内容が支離滅裂でサッパリポンです～ゥ

回答No.5

bunjii

>合計行が明細の最後ではなく、明細の先頭にある場合は難しいですよね。
明細は最後の行に順次追記するだけですか？
それとも、最後の行の下へセルの挿入して追記するのでしょうか？

>データは、Ａ合計行+Ａ明細+Ｂ合計行+Ｂ明細・・・の繰り返しの為
何を言っているのか読み取れません。
模擬データを提示してください。

補足

すみません。具体的に書いた方がいいですよね。
　1　Ａ合計　　100　←　(1)1行目～4行目の間であれば、Ａの明細を
　2　Ａ明細1　　20　　　挿入した時に1行目のＡ合計行の関数範囲が
　3　Ａ明細2　　30　　　変わる
　4　Ａ明細3　　50　←　(2)4行目・5行目の間に、Ａ明細を挿入した
　5　Ｂ合計　　170　　　時は、1行目のＡ合計行の関数範囲が変わら
　6　Ｂ明細1　　50　　　ない。
　7　Ｂ明細2　　50
　8　Ｂ明細3　　70

(2)の時にも、関数の範囲が変わって1行目の合計が正常に計算されるようにしたいとということです。
よろしくお願いします。

回答No.4

HohoPapa

VBAを使った「カスタム関数」でよければ
次のようなコードで期待の動作を実現できます。

=spTotal(E2,E:E)
第一引数は合計するセル群の最上位セルです。
第二引数は合計を求めるセル群を含む列を指定します。

動作は
第一引数のセルから開始して
下方向に、空欄のセル、または計算式の埋まったセルの直前までの
セルたちの合計を求めます。

なお、第二引数は計算には無関係です。
合計対象範囲が変化したときに再計算させるために与えています。

Option Explicit

Function spTotal(rng As Range, DmyRange As Range) As Double
　Dim RowCnt As Long
　Dim wkTotal As Double

　RowCnt = 0
　wkTotal = 0
　Do
　　If rng.Offset(RowCnt, 0).Value = "" Then Exit Do
　　If rng.Offset(RowCnt, 0).HasFormula = True Then Exit Do
　　wkTotal = wkTotal + rng.Offset(RowCnt, 0).Value
　　RowCnt = RowCnt + 1
　Loop
　spTotal = wkTotal

End Function

お礼

細かく記述して頂き、ありがとうございます。
後で、試させて頂きます。

回答No.3

Thiotimoline

>合計行が明細の最後ではなく、明細の先頭にある場合

ということは、私の回答は試されてないのですね。
応用すれば、
=SUM(OFFSET(C1,1,0):C10)
でＯＫなことはすぐわかると思います。

補足

説明不足で申し訳ありません。
データは、Ａ合計行+Ａ明細+Ｂ合計行+Ｂ明細・・・の繰り返しの為
一括で集計することができません。Ａ、Ｂ・・・それぞれ集計する必要があります。
　↓
Ｂ合計行の直前にＡの明細を追加した時に
「=SUM(OFFSET(C1,1,0):C10)」の「C10」が自動的に「C11」に変わってほしいのですが。
Ｂ合計行の直前ではなく、Ａの明細の途中に追加した時はC11に変わります。
何度もすみません。

回答No.2

msMike

C1: =SUM(C2:C1000)

お礼

ご回答ありがとうございます。

補足

説明不足で申し訳ありません。
データは、Ａ合計行+Ａ明細+Ｂ合計行+Ｂ明細・・・の繰り返しの為
一括で集計することができません。Ａ、Ｂ・・・それぞれ集計する必要があります。

回答No.1

meitoku

C1にSUM(C1:C10)をコピーして
SUM(C2:C11)と書き換えれば良いだけです。
Ｃ11）の後ろにカソールをあてれば計算範囲が現れます。
ＳＵＭ（）の数値は削除・書き換えができます。