如图,后面的合并单元格是前面合并单元格对应的单元格的 和/平均数 ,想用个公式直接出来110/55,行数不确定,不一定只有两行,后面的平均数最好能取整
右击你的表格标签——查看代码,粘贴下面的代码:
Sub SumAvg()
'D列中显示C列的“和/平均数”,平均数取整
Dim nR As Long
Dim i As Long, N As Integer, intSum As Integer, intAvg As Integer
nR = Range("C65535").End(xlUp).Row 'C列最后一个数字所在的行
i = 2 '假设有一行标题,数据从第二行开始
Do While i < nR
N = Range("D" & i).MergeArea.Rows.Count
intSum = CInt(Application.WorksheetFunction.Sum(Range("C" & i & ":C" & i + N - 1)))
intAvg = CInt(Application.WorksheetFunction.Average(Range("C" & i & ":C" & i + N - 1)))
Range("D" & i).NumberFormatLocal = "@" '设置为文本格式
Cells(i, 4).Value = intSum & "/" & intAvg
i = i + N
Loop
End Sub
示例:
vba也行啊