不走寻常路 按照背景色对单元格求和

俞木发

方法1：查找法

适合：单一背景色求和，要求原始数据不会再发生变化

如果工作表中标记的背景色只有一种，比如下面的工作表中，绿色标记表示质检员01抽查的数量，现在需要统计这些数字（图1）。

按下“Ctrl+F”快捷键打开“查找”对话窗口，依次点击“格式→从单元格中选择格式”，接着用鼠标点击任意一个绿色单元格，点击“查找全部”后在下方就可以将所有绿色单元格找到了。接着再按下“Ctrl+A”全选数据，在下方的状态栏中就可以看到所有单元格的求和数据为651，按提示点击即可复制。最后再将其粘贴到统计单元格中即可（图2）。这个方法使用简单，不足之处是如果原始数据发生了变化（如在图1中增加了月份和质检员01抽查的数量），那么就需要重新查找。

方法2：使用宏表函数

适合：任意背景色自动求和如果需要统计的是动态变化的数据（如条件格式标注的背景色数据），那么可以借助宏表函数将背景色转换为具体的数值实现统计。比如下表中有多种背景色的单元格，现在需要对它们分别求和，并且要将其作为模板保存方便后续使用（图3）。

1定义宏表函数

依次点击菜单栏中的“公式→定义名称→定义名称”，在打开的窗口中定义一个名称为“颜色”的公式，在“引用位置”处输入“=GET.CELL（63，Sheet2！B2）”，即以B2单元格作为基准，使用宏表函数GET.CELL求出其背景色（即参数63）的数值，点击“确定”保存退出（图4）。

2使用宏表函数获取背景色数值

返回工作表，定位到I2單元格并输入公式“=颜色+RAND （ ） * 0 ”，即在引用上述定义公式的同时加上“R AND（ ）*0”的数值。由于宏表函数不会自动计算，这里使用R A N D 函数的随机性来实现其自动重算，由于使用了“R AND（ ）*0 = 0”，这样原始数值不会变化。接着将公式向右、向下填充到N2、N9单元格（填充区域要和原始数据一致），在I2：N9区域中就会自动显示引用原始数据单元格的背景色数值了，如绿色为10，黄色为46（图5）。

3转换背景色显示

这里的背景色使用数字来表示，为了方便后续的识别使用，还可以使用IFS函数来转换。定位到O2单元格并输入公式“=IFS（I2=10，"绿色"，I2=46，"黄色"，I2=0，""）”，接着将公式向右、向下填充到T2、T9单元格。这里使用IFS函数实现多条件的判断，如果I2：N9区域单元格的值为10，那么就显示为“绿色”，如果无填充（即数值= 0）就显示为空。如果有更多的背景色数值，请参考上述的公式自行添加。完成设置后，原始数据中单元格的背景色就和I2：N9区域单元格显示的背景色字符一一对应了（图6）。

4对背景色单元格求和

选中I：T列数据区域，右击并选择“隐藏”，接着定位到B13单元格并输入公式“=SUMIF（$O$2：$T$9，"绿色" ， $B$2：$G$9 ） ”，表示以“$O$2：$T$ 9”区域中显示的“绿色”字符作为条件（它和原始数据绿色背景填充的单元格相对应），然后对“$ B$2：$G$ 9”区域中符合条件的数据求和（图7）。如果需要对其他背景色单元格求和，那么只要将公式中的“绿色”替换为相应的背景色即可。

5保存为模板使用

选中B2：G9数据区域，依次点击“开始→清除→全部清除”，然后将文件保存为“Excel启用宏的工作簿”。这样以后需要对背景色单元格求和时，只要打开需要统计数据的文档，选择需要统计的数据后复制，接着切换到上述保存的启用宏工作簿，定位到B2单元格，依次点击“开始→ 粘贴→ 保留源格式”，在B13、B14单元格中就可以自动完成数据的统计了（图8）。

此外，由于在图5所示的公式中使用了R A N D函数，这样如果原始数据发生了变化，如图8的G9单元格的数值变为2（假设此时条件格式的设置是单元格背景色变为黄色），那么只要在任意空白单元格中双击，B13、B14单元格的统计数值就会同步更新了。这样对背景色单元格的统计是不是就简单多了？