Excel合并单元格求和求平均自定义函数

如下图所示的表格，求每种产品的总重量和平均重量。

自定义函数代码：

Rem 合并单元格求和

Rem Rng1表示求和起始单元格

Function DiySum(Rng1 As Range) As Double

       Dim temp As Double, i As Integer

       For i = 1 To Application.ThisCell.MergeArea.Rows.Count

                temp = temp Val(Rng1.Cells(1).Offset(i - 1, 0).Value)

       Next i

       DiySum = temp

End Function

Rem 合并单元格求平均值

Rem Rng1表示求平均起始单元格

Function DiyAvg(Rng1 As Range) As Double

       Dim temp As Double, i As Integer

       For i = 1 To Application.ThisCell.MergeArea.Rows.Count

                temp = temp Val(Rng1.Cells(1).Offset(i - 1, 0).Value)

       Next i

       DiyAvg = temp / Application.ThisCell.MergeArea.Rows.Count

End Function

 

自定义函数位置与用法：

由于自定义函数是宏代码，所以使用前需要启用宏。