在利用EXCEL表格制作一些平面直观图时,会根据每个不同类型的物品添加背景色而加以区分,需要对不同颜色的表格数进行计数,为方便引申至相同的图表,在此使用宏程序和相关函数加以解决。
以数据中心的标准机柜的平面直观图为例,图示为机柜正视图。交换机背景色为蓝色,个性色5,淡色60%;服务器背景色为绿色,个性色6,淡色60%。
示例1
1.使用宏程序定义求和颜色表格函数
我们在EXCEL顶部属性栏,打开开发工具选项,在Visual Basic中编辑宏程序:
Option Explicit
'统计相同颜色表格的数量
Function SUMColor(rag1 As Range, rag2 As Range)
Dim i
Application.Volatile
For Each i In rag2
If i.Interior.ColorIndex = rag1.Interior.ColorIndex Then
SUMColor = SUMColor + 1
End If
Next
End Function
VBA语言相关请参阅微软官方文档:
2.在表格中引用SUMcolor方法
在SUMcolor(参数1,参数2)方法中,参数1代表表格背景色示例(即要统计的表格背景色要与所选位置的表格背景色一致),参数2代表所要统计区域。
如示例1
网络设备的数量即为:=SUMColor(D49,B3:D48)/2
注:统计网络设备的前提是此设备在图示中只占1行,由于此方法统计的是表格合并之前的小表格数,故应除以合并的列数。
服务器的数量即为:=(COUNTIF(C3:D48,"*")-SUMColor(D49,B3:D48)/2)
注:服务器的数量=总设备数量-网络设备的数量;
COUNTIF函数
COUNTIF 是一个统计函数,用于统计满足某个条件的单元格的数量;
COUNTIF 的最简形式为:=COUNTIF(要检查哪些区域? 要查找哪些内容?)
=COUNTIF(C3:D48,"*"),统计单元格C3 到D48 中包含任何文本的单元格的数量。 通配符星号 (*) 用于匹配任意字符。
已占U数为:=(SUMColor(D49,C3:D48)+SUMColor(D50,C3:D48))/2
注:即为所选区域两种颜色所占表格数(合并之后的表格按合并之前的小表格计算)
3.根据背景色统计表格的相关问题
一、上述解决问题的方法为最笨的方式,暂无法找出解决问题的最简单的方式,如有其他方式还请指导。
二、如何对合并之后的表格进行计数,为什么用此函数统计的是合并之前所占的小表格的数量,该用什么样的约束条件来进行统计。
三、如何求有背景色的表格所占的行数,前述使用的是两种背景色的表格数之和,此方法复杂,在遇到更多种背景色表格统计时,公式会更加繁琐冗余。