エクセルで合計欄を結合し、左の複数セルの合計を算出

質問

添付画像のようにＣ列を合計欄として結合し、
結合したＣ列に隣り合う、Ｂ列の合計を出す場合の操作方法を教えてください。

回答No.13

kagakusuki

>同じような表で、Ｂ列とＣ列に数字があり、Ｂ列とＣ列の合計を結合セルのＤ列に表示させたい場合は、どのようなマクロになるのでしょうか。

　一応、マクロも作ってみました。
　念の為に、A列やD列に結合されたセルが最初から存在していて、マクロで処理を行った前後で、結合されているセル範囲が変わってしまう様な場合であっても、「結合されていないセル」の水平方向の罫線が消えてしまう事のない様にしております。（そのために行う前処理の部分が、マクロの記述の半分以上を占めてしまう事になってしまいました）
　但し、もしも、「A列～C列の列範囲において、空欄ではないセルが存在している行の中の最下段の行」よりも下の行に、結合されているセルが存在していた場合には、その結合されているセルの結合は解除されません。（結合セルの中の一部の行が、「A列～C列の中の空欄ではない最下段の行」と同じかそれよりも上に位置しているものに関しては、結合の解除と、水平罫線の復活は行われます）
　又、「結合されているセルが存在しているのはA列とD列だけである」という想定の下でマクロを組んでおりますので、A列とB列にまたがって存在している結合セルやC列とD列にまたがって存在している結合セルが存在していた場合には、結合は解除されますが、垂直方向の罫線や、B列とC列の罫線は復活しません。

Sub Macro0()

Dim lr As Long
Dim tr As Long
Dim br As Long
Dim c As String
Dim b As Variant

lr = 0
If Application.WorksheetFunction.CountIf(Range("A2:A" & Rows.Count), "*?") Then _
lr = Application.WorksheetFunction.Match(Chr(1), Columns("A:A"), -1)
If Application.WorksheetFunction.Count(Range("A2:A" & Rows.Count)) Then _
If lr < Application.WorksheetFunction.Match(9E+307, Columns("A:A")) Then _
lr = Application.WorksheetFunction.Match(9E+307, Columns("A:A"))
If lr = 0 Then Exit Sub
If Application.WorksheetFunction.Count(Range("B2:B" & Rows.Count)) Then _
If lr < Application.WorksheetFunction.Match(9E+307, Columns("B:B")) Then _
lr = Application.WorksheetFunction.Match(9E+307, Columns("B:B"))
If Application.WorksheetFunction.Count(Range("C2:C" & Rows.Count)) Then _
If lr < Application.WorksheetFunction.Match(9E+307, Columns("C:C")) Then _
lr = Application.WorksheetFunction.Match(9E+307, Columns("C:C"))
If lr = 0 Then Exit Sub
c = ""
Do
If c = "A" Then c = "D"
If c = "" Then c = "A"
Range(c & "2:" & c & lr).Select
Selection.HorizontalAlignment = xlGeneral
Selection.VerticalAlignment = xlCenter
Set b = Selection.Borders(xlEdgeBottom)
Selection.UnMerge
With Selection.Borders(xlInsideHorizontal)
.LineStyle = b.LineStyle
.ColorIndex = b.ColorIndex
.Weight = b.Weight
End With
Loop Until c = "D"
Range("D2:D" & Rows.Count).ClearContents
'ここまでが前処理

br = Range("A1").Row
Do
tr = br + 1
If Application.WorksheetFunction.CountIf(Range("A" & tr + 1 & ":A" & lr + 1), "*?") + _
Application.WorksheetFunction.Count(Range("A" & tr + 1 & ":A" & lr + 1)) Then
Do
br = br + 1
Loop While Range("A" & br + 1).Value = ""
Else
br = lr
End If
If br > tr Then
Application.DisplayAlerts = False
Range("A" & tr & ":A" & br).Merge
Range("D" & tr & ":D" & br).Merge
Application.DisplayAlerts = True
End If
Range("D" & tr).Value = Application.WorksheetFunction.Sum(Range("B" & tr & ":C" & br))
Loop Until br = lr
Range("A1").Select

End Sub

回答No.12

MackyNo1

>数式を下方向にコピーする方法はありませんか？

数式を入力するセルが大きさの異なる結合セルですから、通常のコピー貼り付けはできません。
そこで、特殊な方法となる「数式」でのコピー貼り付けの方法を提示しました。

私の提示した方法で、すべてのセルにまとめてご希望の表示ができると思うのですが・・・・

ちなみに、B列とC列の2つの列の集計なら数式を以下のように変更してください。

=SUM(OFFSET($B2,0,0,MIN(INDEX(($A3:$A11="")*10000+ROW($A3:$A11),))-ROW($A2),2))

お礼

ご回答ありがとうございます。
出来ました。
前回のご回答が理解できていませんでした。

回答No.11

tom04

No.10です。
画像のアップを忘れていました。

どうも失礼しました。m(_ _)m

回答No.10

tom04

No.3です！

補足の件について・・・
↓の画像のような感じでよいのでしょうか？
そうであればコードを以下に変更してみてください。

Sub 合計2() 'この行から
Dim i As Long, k As Long, endRow As Long
endRow = Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
Range("A:A").UnMerge
For i = 2 To endRow
If Cells(i, "A") = "" Then
Cells(i, "A") = Cells(i - 1, "A")
End If
Next i
For i = 2 To endRow
'↓ココを変更
Cells(i, "D") = WorksheetFunction.SumIf(Range("A:A"), Cells(i, "A"), Range("B:B")) _
+ WorksheetFunction.SumIf(Range("A:A"), Cells(i, "A"), Range("C:C"))
'↑ここまで変更
Next i
Application.DisplayAlerts = False
For i = 2 To endRow
If Cells(i, "A") = Cells(i - 1, "A") Then
k = i
Do While Cells(k, "A") = Cells(i, "A")
k = k + 1
Loop
Range(Cells(i - 1, "A"), Cells(k - 1, "A")).Merge
i = k
End If
Next i
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub 'この行まで

これではどうでしょうか？m(_ _)m

お礼

ご回答ありがとうございます。
簡単にできました。

回答No.9

kagakusuki

>同じような表で、Ｂ列とＣ列に数字があり、Ｂ列とＣ列の合計を結合セルのＤ列に表示させたい場合は、どのようなマクロになるのでしょうか。

　回答No.7はマクロによる方法ではなく、関数を使った方法なのですが、マクロによる方法でなければ駄目なのでしょうか？
　もし、関数を使った補法でも宜しければ、回答No.6の方法を少し変更した次の様な関数をD列に入力されると良いと思います。

=IF(INDEX($A:$A,ROW())="","",SUM(INDEX($B:$B,ROW()):INDEX($C:$C,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.8

keithin

＞「＃ＮＡＭＥ？」と表示されてしまいます。
＞何か操作を間違えているのでしょうか。

えぇ、やり方を間違えているのが原因です。
当たり前の対処だと思いますが、もう一度「回答をよく読みながら、書かれてる通りに行ってください」。

回答No.7

kagakusuki

もし、下の添付画像の様にB列の最後の行に合計値が表示されていて、A列の最後の行に「合計」と表示されている場合や、B列には合計値が表示される様になっていない場合には、次の様な関数を使ってもA列/C列が結合されている範囲内の合計を求める事が出来ます。

=IF(INDEX($A:$A,ROW())="","",SUM(INDEX($B:$B,ROW()):INDEX($B:$B,ROWS(C:C)))-SUM(INDEX(C:C,ROW()+1):INDEX(C:C,ROWS(C:C)))-SUMIF($A:$A,"合計",$B:$B))

　但し、この関数は、表のA列において最後の区分の名称が入力されている行の所まで、コピーしておく事で初めて正しい値を表示する事が出来ます。

お礼

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

補足

同じような表で、Ｂ列とＣ列に数字があり、Ｂ列とＣ列の合計を結合セルのＤ列に表示させたい場合は、どのようなマクロになるのでしょうか。

回答No.6

kagakusuki

A列に入力されている区分が必ず文字列データである場合には、次の様な関数となります。

=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.5

MackyNo1

例示のレイアウトなら、空白列の2行目に以下の式を入力し、このセルを選択して右クリック「コピー」、Ｃ２セル以下の結合セル範囲を選択して、右クリック「形式を選択して貼り付け」で「数式」にしてください（結合セルが最大10行までの数式です）。

=SUM(OFFSET($B2,0,0,MIN(INDEX(($A3:$A11="")*10000+ROW($A3:$A11),))-ROW($A2),1))

お礼

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

補足

数式を下方向にコピーする方法はありませんか？

回答No.4

keithin

ＡＬＴ＋Ｆ１１を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

public function sumleft() as variant
　application.volatile
　with application.caller.mergearea
　　sumleft = application.sum(.offset(0, -1).resize(.rows.count, 1))
　end with
end function

ファイルメニューから終了してエクセルに戻る
Ｃ列の各セルに
=sumleft()
と，この通りに記入する。

Ｃ列のセルは勿論，「結合されていなくても」計算できます。Ａ列は見てないので，必要なＣ列セルを漏れなく結合して計算します。

＃言わずもがなですが，Ｃ列の各セルは「異なるサイズのセル結合」されているため，下向けに数式をコピーして記入するふつーの使い方はできません。

お礼

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

補足

Ｃ列に「=sumleft()」と入力すると、「＃ＮＡＭＥ？」と表示されてしまいます。
何か操作を間違えているのでしょうか。