A列が1から始まる連番で、Ｃ列を合計欄として結合

質問

添付画像のようにA列が1から始まる連番で、Ｃ列を合計欄として結合し、結合したＣ列に隣り合う、Ｂ列の合計を出す場合のC列の関数を

=IF(INDEX($A:$A,ROW())="","",SUM(INDEX($B:$B,ROW()):INDEX($B:$B,IF(COUNTIF(INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),"*?"),MATCH("*?",INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),0)-1+ROW(),ROWS($A:$A)))))

とした場合画像の左のように合計が合いません。
画像の右のようにC列を計算するには
=IF(INDEX($A:$A,ROW())="","",SUM(INDEX($B:$B,ROW()):INDEX($B:$B,IF(COUNTIF(INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),"*?"),MATCH("*?",INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),0)-1+ROW(),ROWS($A:$A)))))
をどのように修正すればいいのでしょうか？

回答No.3

bunjii · ベストアンサー

提示の数式には無駄が多く考え方に無理があります。
尚、C列がセルの結合をしていますが数式を設定する前に結合するとセルのコピーができません。
考え方を変えて次の数式を試されては如何でしょうか？
C1=IF($A1="","",SUM(OFFSET($A$1,MATCH(MAX($A$1:$A1),$A:$A,0)-1,1,IFERROR(MATCH(MAX($A$1:$A1)+1,$A:$A,0),MAX(INDEX(ROW($A:$A),0)))-MATCH(MAX($A$1:$A1),$A:$A,0))))

お礼

参考になりました。ありがとうございます。

回答No.5

bunjii

回答No.3の別解です。
C1=IF(A1="","",SUM(B1:B$10)-SUM(C2:C$10))
C1セルを下へ必要数コピーしてからセルに結合を行ってください。
但し、B列のデータは質問に提示されている10行目までとします。
実際のデータに合わせて最大行を修正してください。
尚、A列の値は文字列/数値の何れでも結果は同じになります。

回答No.4

imogasi

関数の式が長く、関数の組み合わせの仕組の理解がむつかしいと思い、ＶＢＡでやってみた。参考に。
ＶＢＡが全く分からない場合は意味ないのですが。
ＶＢＡでの下記の処理ロジックは、初歩的なものです。
標準モジュールに
Sub test01()
Dim i As Long
lr = Range("B100000 ").End(xlUp).Row 'Ｂ列最終行　
MsgBox lr
s = 0
mx = Cells(1, "A")
For i = 1 To lr
x = Cells(i, "A").MergeArea(1, "A").Value
If mx = x Then 'ｘが前の行と同じ間は足しこみ
s = s + Cells(i, "B")
Else ’変わった場合
Cells(i - 1, "C") = s 'Ｃ列に合計代入
s = 0
s = s + Cells(i, "B")
End If
mx = x
Next i
Cells(i - 1, "C") = s
End Sub
Ａ列セルが結合されているとして、Ｂ列の数字を、Ａ列の結合セルの区切りのＣ列での一番下のセルに合計を入れる。
ーー
テストデータで実行すると
1　 100 0
200
50 350
2　 300
30 　330
3　 20
50
50
120
180 　420
4　 30
40
60 　130
Ａ列の数字（番号？）は頼りにしていない。

回答No.2

xyz37005

=IF(INDEX($A:$A,ROW())="","",SUM(INDEX($B:$B,ROW()):INDEX($B:$B,IF(COUNTIF(INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),INDEX($A:$A,ROW())+1),MATCH(INDEX($A:$A,ROW())+1,INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),0)-1+ROW(),ROWS($A:$A)))))

元の数式の"*?"をINDEX($A:$A,ROW())+1に置換。
A列が1から始まる連番ということので、「なんらかの値」ではなく「A列の同じ行で入力されている数値+1」を探すように変えた。
なのでA列が連番じゃないと動きません。

元の式はこれ↓が
COUNTIF(INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),"*?")
常に0になるせいでIF文の動作がおかしく、合計する行が「A列で次に空白以外になる行まで」ではなく「A列の末行まで」になっているような。
あと
MATCH("*?",INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),0)
もエラーじゃないのかな。
というかエクセル標準のワークシート関数で正規表現("*?"の部分)って使えたっけ？

あと、他人の回答を否定するわけじゃないけど、No.1さんの数式の「SUM(INDIRECT("C"&ROW()+1&":"&"C"&COUNTA($B:$B)))」だけど、A10にも数値が入っていた場合、C10での値が「SUM(C11:C10)」になってエラーになるんじゃないかな。

お礼

参考になりました。ありがとうございます。

回答No.1

yoko14820

これが正解だと思います。

=IF(INDEX($A:$A,ROW())="","",SUM(INDEX($B:$B,ROW()):INDEX($B:$B,IF(COUNTIF(INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),"*?"),MATCH("*?",INDEX($A:$A,ROW()+1):INDEX($A:$A,ROWS($A:$A)),0)-1+ROW(),ROWS($A:$A))))-SUM(INDIRECT("C"&ROW()+1&":"&"C"&COUNTA($B:$B))))

isi999さんの数式にから「SUM(INDIRECT("C"&ROW()+1&":"&"C"&COUNTA($B:$B))))」の値を減算しました。

つまり、入力しているセル（Ｃ列）の真下から最下行までの合計値を差し引いたわけです。

お礼

参考になりました。ありがとうございます。