前言|职场实例

Excel已经成为职场人士必备的技能。作为个人电脑普及以来用途最广泛的办公软件之一,在很多公司,Excel已经完全成为了一种生产工具,无论何种行业,只要需要和数据打交道,Excel几乎是不二的选择。

Excel中的公式功能是非常强大的,但是有些函数系统并不自带,需要我们自己自定义设计生成。

比如下图所示:

我们想要按背景填充颜色统计数据源中的数据个数和数据之和;以及按字体颜色统计数据源中的数据个数和数据之和。

但是通过普通的Excel可视化函数并不能实现这样的操作。所以今天小编要向大家介绍用VBA代码的方法自定义函数解决这样的颜色统计。因为Excel本身是支持VBA的,用代码,就可以对很多函数公式无法实现的功能,重新进行设计。

excel单元格颜色和字体颜色计算(Excel以单元格填充颜色或字体颜色统计数据)(1)

步骤|解决方案

首先,我们右击工作表名称标签,点击“查看代码”命令。进入VBA编辑窗口界面。

excel单元格颜色和字体颜色计算(Excel以单元格填充颜色或字体颜色统计数据)(2)

点击插入-模块,将下面一段代码复制粘贴到VBA模块编辑窗口中。保存关闭。回到Excel主界面。

excel单元格颜色和字体颜色计算(Excel以单元格填充颜色或字体颜色统计数据)(3)

代码如下:

Function CountByBGColor(Col As Range, CountRange As Range) '根据背景颜色计数 Application.Volatile Dim iCell As Range CountByBGColor = 0 For Each iCell In CountRange If iCell.Interior.Color = Col.Interior.Color Then CountByBGColor = CountByBGColor 1 End If Next End Function Function SumByBGColor(Col As Range, SumRange As Range) '根据背景颜色求和 Application.Volatile Dim iCell As Range SumByBGColor = 0 For Each iCell In SumRange If iCell.Interior.Color = Col.Interior.Color Then SumByBGColor = SumByBGColor Application.WorksheetFunction.Sum(iCell) End If Next End Function Function CountByFontColor(Col As Range, CountRange As Range) '根据字体颜色色计数 Application.Volatile Dim iCell As Range CountByFontColor = 0 For Each iCell In CountRange If iCell.Font.Color = Col.Font.Color Then CountByFontColor = CountByFontColor 1 End If Next End Function Function SumByFontColor(Col As Range, SumRange As Range) '根据字体颜色求和 Application.Volatile Dim iCell As Range SumByFontColor = 0 For Each iCell In SumRange If iCell.Font.Color = Col.Font.Color Then SumByFontColor = SumByFontColor Application.WorksheetFunction.Sum(iCell) End If Next End Function

当我们保存代码后,返回到工作表界面,像常规公式一样,在编辑栏输入函数,就可以实现正常的统计了。

按背景填充颜色统计数据个数

我们在H2单元格输入函数:

=CountByBGColor(G2,$B$2:$E$6)

向下填充函数

excel单元格颜色和字体颜色计算(Excel以单元格填充颜色或字体颜色统计数据)(4)

按背景填充颜色统计数据之和

我们在I2单元格输入函数:

=SumByBGColor(G2,$B$2:$E$6)

向下填充函数

excel单元格颜色和字体颜色计算(Excel以单元格填充颜色或字体颜色统计数据)(5)

按字体颜色统计数据个数

我们在H9单元格输入函数:

= CountByFontColor(G9,$B$9:$E$13)

向下填充函数

excel单元格颜色和字体颜色计算(Excel以单元格填充颜色或字体颜色统计数据)(6)

按字体颜色统计数据之和

我们在I9单元格输入函数:

=SumByFontColor(G9,$B$9:$E$13)

向下填充函数

excel单元格颜色和字体颜色计算(Excel以单元格填充颜色或字体颜色统计数据)(7)

,