![](https://iknow-base.cdn.bcebos.com/lxb/notice.png)
EXCEL如何用公式统计带颜色的单元格个数? 100
1、电脑打开Excel表格。
2、电脑打开Excel表格后,Alt+F11进入宏,然后点击插入模块。
3、点击插入模块后,输入代码:
Function SUMColor(rag1 As Range, rag2 As Range)
Application.Volatile
For Each i In rag2
If i.Interior.ColorIndex = rag1.Interior.ColorIndex Then
SUMColor = SUMColor + 1
End If
Next
End Function
4、输入代码后,不用关闭宏,直接返回Excel界面,在一个单元格中输入公式=SUMColor(A1,$A$1:$B$6),A1就是要筛选的颜色的单元格,$A$1:$B$6是要筛选的区域,框选区域后,按F4键就可以了。
5、输入公式后,就可以得到带颜色的单元格个数了。
这个需要自定义函数
Function colorcount(y As Range, rng)
Application.Volatile
Dim c As Double
Dim x As Range
For Each x In rng
If x.Interior.ColorIndex = y.Interior.ColorIndex Then
c = 1
Else
c = 0
End If
colorcount = colorcount + c
Next x
End Function
GetColor "Sheet1", 1, 1, 3, 3, 1, 6
End Sub
Sub GetColor(SheetName As String, StartX As Long, StartY As Long, EndX As Long, EndY As Long, ShowX As Long, ShowY As Long)
'sheetname 表名
'startx 开始坐标的列数 从1开始
'starty 开始坐标的行数 从1开始
'endx 结束坐标的列数 从1开始
'endy 结束坐标的行数 从1开始
'showx 显示统计结果的坐标的列数
'showy 显示统计结果的坐标的行数
'65535 是黄色的数值
Dim I As Long, J As Long, TotalNum As Long
For I = StartY To EndY
For J = StartX To EndX
ThisWorkbook.Sheets(SheetName).Cells(I, J) = ThisWorkbook.Sheets(SheetName).Cells(I, J).Interior.Color
If ThisWorkbook.Sheets(SheetName).Cells(I, J).Interior.Color = 65535 Then
TotalNum = TotalNum + 1
End If
Next J
Next I
ThisWorkbook.Sheets(SheetName).Cells(ShowY, ShowX) = TotalNum
End Sub
看不懂,要具体操作方法
..放到宏里用。。