|
本帖最后由 一沫昔阳虹w 于 2016-12-8 19:29 编辑
- Function GetAverageIf(ByVal rngs As Range, ByVal colorRng As Range, Optional ByVal val As Integer = 0, _
- Optional ByVal k As Integer = 0) As Single
- 'rngs为选择区域,colorRng为颜色单元格,val用作指定旁边的单元格,k用作判断
- Dim rng As Range, n As Long, sum As Long
- Application.Volatile
- If k = 0 Then Set rngs = Range(rngs, rngs.End(xlDown)) '自动向下扩展,不需要自动向下填充,设置k不等于0
- For Each rng In rngs
- If rng.Interior.Color = colorRng.Interior.Color Then
- n = n + 1
- sum = sum + rng.Offset(, val) '向右偏移Val个单元格,默认为0
- End If
- Next
- GetAverageIf = sum / n '平均值
-
- End Function
复制代码 C2:=GetAverageIf(B2,B3) G2:=GetAverageIf(E2,E3,1)
|
评分
-
查看全部评分
|