打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
老板让我根据颜色求和与计数,我不会,同事用宏表函数轻松搞定

Hello,大家好,之前跟大家分享过使用查找颜色来解决根据单元格颜色进行数据统计的问题,比较遗憾的是无法实现数据的自动刷新,今天跟大家分享下如何使用宏表函数对带有颜色的单元格进行数据统计,这个方法是可以实现数据刷新的,操作也不能难,下面就让我们来一起操作下吧

一、get.cell函数

get.cell是一个宏表函数,他可以获取单元格中的信息

语法:=GET.CELL(Type_num,Reference)

第一参数:Type_num,用数字表示的单元格信息的类型,输入范围是1-66

第二参数:Reference,引用的单元格

在这里我们想要获取单元格的背景色的颜色值,只需要将第一参数设置为63即可

宏表函数是excel早期的产物,已经逐渐被抛弃了,但是仍然可以使用,只不过不需要使用定义名称来调用它。下面就让我们看下它是如何操作的吧

二、定义get.cell函数

首先我们点击公式功能组,然后点击定义名称,我们将名称设置为颜色,然后在引用位置中输入公式:=GET.CELL(63,Sheet1!B2),点击确定即可,至此定义名称就定义完毕了,如下图

在这里公式为:=GET.CELL(63,Sheet1!B2)

第一参数:63,在这里63就代表返回单元格格背景色对应的颜色值

第二参数:Sheet1!B2,就是我们开始统计的第一个单元格数据所在的位置B2,sheet1就是工作表名称

三、获取颜色值

我们只需要在旁边输入我们定义的名称,=颜色,然后点击回车向下填充即可,可以看到在这里黄色对应的数值是6,绿色对应的数值是10,没有填充的单元格对应的数值是0,至此我们就得到了颜色对应的数值

四、根据条件求和与计数

在这里我们已经得到了每种颜色对应的数字,这个时候我们只需要使用sumif函数或者counif函数即可实现对填充色进行求和或者计数的操作,比如在这里我们想要对绿色进行求和与计数

求和公式:=SUMIF(C2:C13,10,B2:B13)

计数公式:=COUNTIF(C2:C13,10)

在这里我们需要注意的是绿色对应的颜色值是10.所以我们需要将条件设置为10,才能计算到正确的结果,如果要计算黄色的结果,只需要将10更改为6即可

五、根据颜色进行多条件求和与计数

根据颜色进行多条件求和或者计数我们需要用到sumifs函数或者countis函数,比如在这里我们来计算下颜色是黄色大于300的结果

求和:=SUMIFS(B2:B13,C2:C13,6,B2:B13,">300")

第一参数:B2:B13,需要求和的数据区域

第二参数:C2:C13,第一个条件区域,颜色值

第三参数:6,第一个条件,黄色对应的数值

第四参数:B2:B13,第二个条件区域,销量区域

第五参数:">300",第二个条件,大于300

计数:=COUNTIFS(C2:C13,6,B2:B13,">300")

这个函数的参数与sumifs参数类似,就不跟大家多做介绍了,如果我们想要计算绿色只需要将6更改为10即可

六、设置vba实现自动刷新

使用宏表函数有一个缺点就是无法实现数据结果的实时刷新,这个时候我们就需要设置vba让公式实现自动刷新,首先我们点击visualbasic,右侧双击sheet1这个工作表名称,将类型更改为worksheet输入单词Calculate然后将这个界面关闭掉即可,这样的话公式就能实现自动更新了

在这里我们需要注意的我们使用了宏表函数,以及vba,所以我们需要将文件的格式更改为可以保存宏的格式,所以我们需要将文件另存为xlsm格式,否则的话是无法将这个数据保存的

以上就是今天分享的方法,怎么样?你学会了吗?

我是excel从零到一,关注我,持续分享更多excel技巧

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
excel如何实现按照单元格颜色进行求和 方法一 宏表函数求和
表格数据多重复 两个函数全搞定
Excel按单元格颜色计数、求和,你都学会了吗?
EXCEL中神秘的“宏表函数”,让你相见恨晚~~
颜色求和实际是个伪命题,但还是告诉你4种方法!
EXCEL函数及数据分析技巧整理备用
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服