#
格#填充颜色统计
篇一:excel表格颜色统计公式
excel表格颜色统计公式
EXCEL表格,单元格中除了有相关文字信息外,还有多种颜色表示,我想统计一下每种颜色总共有多少格。而且随着颜色的变化,统计的结果也随着变化。我已经找
找了一年了。向高手请教了。先谢谢了。
问题补充:我说的颜色,是在编辑区域内单元格中的填充颜色,而且颜色分三种吧,有红色,蓝色,无填充色。比如在80个单元格范围内,有这么多颜色,而且颜色的位置是不固定的,不是整列,也不是整行,是杂乱无序的,而且随时都要撤销颜色,或者更换颜色的位置,或者更换颜色,并且我要随时统计即时的各种颜色单元格的数量,比如,红色,而且我统计的结果要随着颜色的更换,删除,调整结果要自动更新。
比较麻烦,请大侠给指点指点。
大致意思就如下图。需随时统计表中蓝色,红色,为填充的单元格数量。能自动更新的。谢谢。
问题补充:详细一点,用什么公式,怎么操作。谢谢
1
问题补充: 要步骤,而且按照表格的统计要求,设置自动变化。谢谢
STEP 1 :打开你的excel;
STEP 2 :菜单栏:工具,宏,Visual Basic 编辑器;
STEP 3 :Visual Basic 编辑器菜单栏:插入,模块
STEP 4 :贴入下面这段代码
Function Countcolor(col As Range, countrange As
Range)
Dim icell As Range
Application.Volatile
For Each icellIncountrange
If icell.Interior.ColorIndex = col.Interior.ColorIndex Then
Countcolor = Countcolor + 1
End If
Next icell
End Function
STEP 5 :保存并关闭Visual Basic 编辑器
STEP 6 :使用函数countcolor(所要统计的颜色所在单元格,统计的区域)
本例中,在C17单元格输入公式:
=Countcolor(B17,$B$7:$G$15)
然后下拉公式到C20单元格。
2
在C21单元格输入公式:
=SUM(C17:C20)
由于在统计区域中有6个单元格是面积,所以,C20单元格在统计可售套数时要减去6。
? 人人网新浪微博开心网MSNQQ空间5
篇二:EXCEL中单元格的颜色统计
EXCEL中单元格的颜色统计
其实,这个问题要用到一个特别的函数:get.cell
解决这个问题的思路是,你首先得让系统知道你每种颜色的代码是多少。得到这个代码就用到这个函数。设你要统计的单元格在A列,B列为空列,操作如下:
1,点插入,名称,定义,弹出的窗口第一行名称名内写入一个自定义的名称名字,可以是中文也可以是英文,比如XX,最下边一行的引用位置一行内填入:
=get.cell(63.A1)确定。
2,在B1中输入=XX,光标指向B1单元格右下角的小黑点,光标变成小黑实心十字时,双击左键。完成填充。
这样操作以后,在B列中会出现各个单元格中底色的代码。
3,用countif()函数对你要统计的单元进行统计,如:
=countif($B$1:$B$100,5)
这个统计公式意思是统计B1至B100中颜色代码是5的
3
单元格有多少个。
用辅助列可以做到
假设列A为原数据列,选择列B为第一辅助列
1.在插入-名称-定义里定义一个名称,X=GET.CELL(24,SHEET1!A1)
2.在B1中输入=X
这样你就会看到列B中有对应的列A颜色返回的数值,假定黑色格子的颜色数值为1
再利用一辅助列C,在列C输入公式=IF(OR(B:B=1,B:B=57,B:B=0),A:A+3,A:A),即可得所需变更后的全部数值列
最后便是利用选择性拷贝的方法,将C列的值复制到A列,就可以完成全部操作了
注意:
1、GET.CELL(24,SHEET1!A1) 其中24代表字体颜色,换成38代表背景颜色
用EXCEL来根据单元格的颜色来计数和求和
步骤1 :打开你的excel;
步骤2 :按Alt+F11键
步骤3 :Visual Basic 编辑器菜单栏: 插入,模块
步骤4 :粘贴下面这段函数
Function Countcolor(col As Range, countrange As Range)
4
Dim icell As Range
Application.Volatile
For Each icell In countrange
If icell.Interior.ColorIndex = col.Interior.ColorIndex Then
Countcolor = Countcolor + 1
End If
Next icell
End Function
步骤5 :保存
步骤6 :粘贴下面这段函数
Function Sumcolor(col As Range, sumrange As Range)
Dim icell As Range
Application.Volatile
For Each icell In sumrange
If icell.Interior.ColorIndex = col.Interior.ColorIndex Then
Sumcolor = Application.Sum(icell) + Sumcolor
End If
Next icell
End Function
步骤7:保存并关闭Visual Basic 编辑器。
使用函数 countcolor统计单元格数量,其
为:
countcolor(所要统计的颜色所在单元格,统计的区域)注:
5
括号内为参数,下同。
使用函数sumcolor来求和(所要统计的颜色所在单元格,
统计的区域)
///若是字体颜色
Function Sumfontcolor(col As Range
, sumrange As Range)
Dim icell As Range
Application.Volatile
For Each icell In sumrange
If icell.Font.ColorIndex = col.Font.ColorIndex Then
Sumfontcolor = Application.Sum(icell) + Sumfontcolor
End If
Next icell
End Function
1、 建立Excel对象
set objExcelApp = CreateObject(Excel.Application)
objExcelApp.DisplayAlerts = false 不显示警告
objExcelApp.Application.Visible = false 不显示界面
2、 新建Excel文件
objExcelApp.WorkBooks.add
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
6
set objExcelSheet = objExcelBook.Sheets(1)
3、 读取已有Excel文件
strAddr = Server.MapPath(.)
objExcelApp.WorkBooks.Open(strAddr & \Templet\Table.xls)
set objExcelBook = objExcelApp.ActiveWorkBook
set objExcelSheets = objExcelBook.Worksheets
set objExcelSheet = objExcelBook.Sheets(1)
4、 另存Excel文件
objExcelBook.SaveAs strAddr &
\Temp\Table.xls
5、 保存Excel文件
objExcelBook.Save (笔者测试时保存成功,页面报错。)
6、 退出Excel操作
objExcelApp.Quit 一定要退出
set objExcelApp = Nothing
三、 操作Excel生成数据表
1、 在一个范围内插入数据
objExcelSheet.Range(B3:k3).Value = Array(67, 87, 5, 9, 7, 45, 45, 54, 54, 10)
2、 在一个单元格内插入数据
objExcelSheet.Cells(3,1).Value=Internet Explorer
7
3、 选中一个范围
4、 单元格左边画粗线条
5、 单元格右边画粗线条
6、 单元格上边画粗线条
7、 单元格下边画粗线条
8、 单元格设定背景色
9、 合并单元格
10、 插入行
11、 插入列
四、 操作Excel生成Chart图
1、 创建Chart图
objExcelApp.Charts.Add
2、 设定Chart图种类
objExcelApp.ActiveChart.ChartType = 97
注:二维折线图,4;二维饼图,5;二维柱形图,51
3、 设定Chart图标题
objExcelApp.ActiveChart.HasTitle = True
objExcelApp.ActiveChart.ChartTitle.Text = A test
Chart
4、 通过表格数据设定图形
objExcelApp.ActiveChart.SetSourceData
objExcelSheet.Range(A1:k5),1
8
5、 直接设定图形数据(推荐)
objExcelApp.ActiveChart.SeriesCollection.NewSeries
objExcelApp.ActiveChart.SeriesCollection(1).Name = =333
objExcelApp.ActiveChart.SeriesCollection(1).Values = ={1,4,5,6,2}
6、 绑定Chart图
objExcelApp.ActiveChart.Location 1
7、 显示数据表
objExcelApp.ActiveChart.HasDataTable = True
8、 显示图例
objExcelApp.ActiveChart.DataTable.ShowLegendKey = True
我假设你的工作表已经有很多设置好背景颜色的单元格.
如你上面讲的红.黄.蓝
视图,工具栏,窗体打上勾.在窗体工具条上点击按钮在工
作表上拉出一个按钮.在弹出来的指定宏对话框中点新建这
时会出现VBA窗口
在自动生出来的
Sub按钮1_单击() '这是自动生出来的
DimaAsRange
ForEachaInSheets(sheet1).UsedRange'在使用过的
9
单元格内搜索
Ifa.Interior.ColorIndex=6Then
a=1000:a.Font.ColorIndex=3 '如果条件为真,在这
个单元格内输入数值.并改变字体颜色
ElseIfa.Interior.ColorIndex=5Then
a=500:a.Font.ColorIndex=6
ElseIfa.Interior.ColorIndex=3Then
a=100:a.Font.ColorIndex=5
EndIf
Nexta
EndSub '这也是自动生出来的.
请注意:复制此段代码时,不要将第一句Sub按钮1_单击()和最后一句EndSub这两句复制.选中中间的代码将它粘贴
进去就可以了.
如果想判断颜色后,不想再要这些背景颜色的话,将它修改
一下就可以了.
如下面:
Sub按钮1_单击()
DimaAsRange
ForEachaInSheets(sheet1).UsedRange
Ifa.Interior.ColorIndex=6Then
a.Interior.ColorIndex=0:a=1000:a.Font.ColorIndex=3
10
ElseIfa.Interior.ColorIndex=5Then
a.Interior.ColorIndex=0:a=500:a.Font.ColorIndex=6
ElseIfa.Interior.ColorIndex=3Then
a.Interior.ColorIndex=0:a=100:a.Font.ColorIndex=5
EndIf
Nexta
EndSub
这样只要你点击一下工作表上你拉出来的这个按钮.就会完成你的要求
我这个代码还是粗糙了一点.如果哪位高手有更好的方法也可以贴出来.权当是一次VBA编写练习.如果不想要单元格的字体颜色设置.可将这些类似的删
除.:a.Font.ColorIndex=3
如果是单元格内一开始设置的字体颜色.但单元格内并末输入数据.而想用设定的字体颜色来改变为数值的话,用下面这段代码.
用上面的方法拉出一个按钮.
Sub按钮2_单击()
DimaAsRange
ForEachaInSheets(sheet1).UsedRange'在使用过的单元格内搜索
Ifa.Font.ColorIndex=6Then
11
a=1000
ElseIfa.Font.ColorIndex=5Then
a=500
ElseIfa.Font.ColorIndex=3Then
a=100
EndIf
Nexta
EndSub
复制粘贴代码的方法和上面相同.
还有a=100和a=1000以及a=500
这个数值随你自己调整.比如调整为a=5000等
应Yiqun_Zhao(蓝帆?雨轩Mail:yiqun_zhao.NITTOOS@gg.nitto.co.jp)的要求.我写了一个自定义的函数.以下为代码
操作方法如下:按ALT+F11打开VBE.点击插入,模块.在右边的界面将下面的代码粘贴过去即可
PrivateFunctionhhh(aaAsRange) '自定义函数hhh()
Application.Volatile
b=aa.Font.ColorIndex
Ifb=6Then
hhh=1000
ElseIfb=5Then
12
hhh=500
ElseIfb=3Then
hhh=100
Else
hhh=50
篇三:按指定的填充颜色计算单元格的个数及求和
按指定的填充颜色计算单元格的个数及求和
这个要使用到自定义函数.
VBA编辑器中.新建一模块,加入代码如下:
Function SumByColor(Ref_color As Range, Sum_range
As Range)
Application.Volatile
Dim iCol As Integer
Dim rCell As Range
iCol = Ref_color.Interior.ColorIndex
For Each rCell In Sum_range
If iCol = rCell.Interior.ColorIndex Then
SumByColor = SumByColor + rCell.Value
End If
Next rCell
End Function
Function CountByColor(Ref_color As Range,
13
CountRange As Range)
Application.Volatile
Dim iCol As Integer
Dim rCell As Range
iCol = Ref_color.Interior.ColorIndex For Each rCell In
CountRange
If iCol = rCell.Interior.ColorIndex Then
CountByColor = CountByColor + 1
End If
Next rCell
End Function
上述两个自定义函数,一个是 SumByColor ,可以对区
域按指定单元格的颜色求和。另一个是 CountByColor ,可
以统计区域中某种颜色的个数。这两个自定义函数都有两个
参数,前一个参数指定包含某种颜色的单元格,后一个参数
为求和或计数区域。
假如要求和或计数的区域在 A1:B10 区域中。
求出该区域中单元格底纹颜色为红色的所有单元格数值
之和,在单元格中输入公式:=sumByColor(A1,A1:B10)
求出该区域中单元格底纹颜色为红色的所有单元格的个
数,在单元格中输入公式:=CountByColor(A1,A1:B10)
这个方法不适用条件格式
14
虽然不是很方便.但比使用宏表函数并加辅助列要好的多了.同理也可以计算有字体颜色的数字个数及求和.
代码以后再上
15