b-2轰炸机|老板让我根据颜色求和与计数，我不会，同事用宏表函数轻松搞定 excel

文章图片

文章图片

Hello ，大家好，之前跟大家分享过使用查找颜色来解决根据单元格颜色进行数据统计的问题，比较遗憾的是无法实现数据的自动刷新，今天跟大家分享下如何使用宏表函数对带有颜色的单元格进行数据统计，这个方法是可以实现数据刷新的，操作也不能难，下面就让我们来一起操作下吧
【b-2轰炸机|老板让我根据颜色求和与计数，我不会，同事用宏表函数轻松搞定】一、get.cell函数
get.cell是一个宏表函数，他可以获取单元格中的信息
语法：=GET.CELL(Type_numReference)
第一参数：Type_num ，用数字表示的单元格信息的类型，输入范围是1-66
第二参数：Reference ，引用的单元格
在这里我们想要获取单元格的背景色的颜色值，只需要将第一参数设置为63即可
宏表函数是excel早期的产物，已经逐渐被抛弃了，但是仍然可以使用，只不过不需要使用定义名称来调用它。下面就让我们看下它是如何操作的吧
二、定义get.cell函数
首先我们点击公式功能组，然后点击定义名称，我们将名称设置为颜色，然后在引用位置中输入公式：=GET.CELL(63Sheet1!B2) ，点击确定即可，至此定义名称就定义完毕了，如下图
在这里公式为：=GET.CELL(63Sheet1!B2)
第一参数：63 ，在这里63就代表返回单元格格背景色对应的颜色值
第二参数：Sheet1!B2 ，就是我们开始统计的第一个单元格数据所在的位置B2 ， sheet1就是工作表名称
三、获取颜色值
我们只需要在旁边输入我们定义的名称， =颜色，然后点击回车向下填充即可，可以看到在这里黄色对应的数值是6 ，绿色对应的数值是10 ，没有填充的单元格对应的数值是0 ，至此我们就得到了颜色对应的数值
四、根据条件求和与计数
在这里我们已经得到了每种颜色对应的数字，这个时候我们只需要使用sumif函数或者counif函数即可实现对填充色进行求和或者计数的操作，比如在这里我们想要对绿色进行求和与计数
求和公式：=SUMIF(C2:C1310B2:B13)
计数公式：=COUNTIF(C2:C1310)
在这里我们需要注意的是绿色对应的颜色值是10.所以我们需要将条件设置为10 ，才能计算到正确的结果，如果要计算黄色的结果，只需要将10更改为6即可
五、根据颜色进行多条件求和与计数
根据颜色进行多条件求和或者计数我们需要用到sumifs函数或者countis函数，比如在这里我们来计算下颜色是黄色大于300的结果
求和：=SUMIFS(B2:B13C2:C136B2:B13\">300\")
第一参数：B2:B13 ，需要求和的数据区域
第二参数：C2:C13 ，第一个条件区域，颜色值
第三参数：6 ，第一个条件，黄色对应的数值
第四参数：B2:B13 ，第二个条件区域，销量区域
第五参数：\">300\" ，第二个条件，大于300
计数：=COUNTIFS(C2:C136B2:B13\">300\")
这个函数的参数与sumifs参数类似，就不跟大家多做介绍了，如果我们想要计算绿色只需要将6更改为10即可
六、设置vba实现自动刷新
使用宏表函数有一个缺点就是无法实现数据结果的实时刷新，这个时候我们就需要设置vba让公式实现自动刷新，首先我们点击visualbasic ，右侧双击sheet1这个工作表名称，将类型更改为worksheet输入单词Calculate然后将这个界面关闭掉即可，这样的话公式就能实现自动更新了