|
- Function SumIFColor(RNG As Range, 颜色单元格 As Range, Optional 统计区)
- Dim arr(), Item, i As Long '声明变量
- '-------------------------------------------------------------------
- Dim count As Long, color As Long, tempRng As Range
- '-------------------------------------------------------------------
- If IsMissing(统计区) Then '如果第三参数被忽略
- '则将“条件区”当作“统计区”,并将它赋值给数组变量arr(使用数组可对代码提速)
- ' arr== Intersect(RNG, RNG.Parent.UsedRange).Value
- Set tempRng = Intersect(RNG, RNG.Parent.UsedRange)
- Else '否则
- '将“统计区”参数重置为“条件区”的相同高度和宽度,然后赋值给数组变量arr
- ' arr = 统计区(1).Resize(RNG.Rows.count, RNG.Columns.count).Value
- Set tempRng = 统计区(1).Resize(RNG.Rows.count, RNG.Columns.count)
- End If
- '-------------------------------------------------------------------
- color = 颜色单元格(1).Interior.color
- count = tempRng.count
- ReDim arr(1 To 2, 1 To count)
- For i = 1 To count '赋值
- arr(1, i) = tempRng(i)
- arr(2, i) = tempRng(i).Interior.color
- If arr(2, i) = color Then SumIFColor = SumIFColor + arr(1, i)
- Next
- '-------------------------------------------------------------------
- ' For Each Item In arr '遍历数组arr
- ' i = i + 1 '累加计数器
- ' '如果条件区中某个单元格背景色与颜色单元格区域(参照区)颜色一致
- '
- ' If RNG.Cells(i).Interior.color = 颜色单元格(1).Interior.color Then '................这句代码如何改为用数组判断
- '
- ' SumIFColor = SumIFColor + Item '将数组中对应的数值累加到SumIFColor中
- ' End If
- ' Next
- End Function
复制代码
|
评分
-
查看全部评分
|