ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 1777|回复: 26

[原创] CELL_EX函数实现根据单元格颜色或字体、字体颜色等条件进行数据统计、提取

[复制链接]

TA的精华主题

TA的得分主题

发表于 2024-7-20 14:56 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 胡汉三回来了 于 2024-7-20 15:07 编辑

如题所言,CELL_EX函数实现根据单元格颜色或字体、字体颜色等条件进行数据统计、提取,上图

CELL_EX函数根据指定颜色实现数据统计、提取

CELL_EX函数根据指定颜色实现数据统计、提取


当然也可以继续使用GET.CELL来实现,大家可以自行体验两种实现方式,附上文件,详见文末!

INFO系列函数是一组测试函数,非正式发布版,有什么问题或好的想法欢迎留言
下面是INFO的使用方法:
1、Office用户:打开Excel,文件 → 账户 → 关于Excel → 第一行写的xx位
     a) 如果是64位,选择INFO-x64
     b) 如果是32位,选择INFO-x86
2、WPS用户:
     a) 先关闭所有WPS文件
     b) 打开INFO for WPS,点击注册
     c) 选择INFO-x86(兼容WPS)
上面选择的一步是单次使用方法,如果需要长期使用到INFO的一些函数,可以使用一次操作➕后期自动加载的方式:
1、Office用户:Excel 开发工具 → Excel加载项 → 浏览 → 选择对应Excel版本的INFO版本 → 确定
2、WPS  用户:工具或开发工具 → 加载项 → 浏览 → 选择INFO-x86(兼容WPS) → 确定

嗯,就这样,欢迎大家测试反馈!


附件如下:
INFO.rar (1.06 MB, 下载次数: 30)






补充内容 (2024-7-23 13:34):
INFO-1.24.723 移步5楼下载

补充内容 (2024-8-8 19:34):
INFO-1.24.805 移步14楼下载

补充内容 (2024-12-14 10:48):
INFO-1.24.1213 移步27楼下载

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-7-20 18:18 | 显示全部楼层
感谢,INFO的其他函数就不单开贴了,继续分享。
INFO系列函数源于5月有位小伙伴求助,需要通过函数返回电脑的ip或电脑名,类似now函数,可以在报表打开自动更新了,具体是用户名称还是计算机名称(后来截图看应该是计算机名称)也没说,很多小伙伴也提供了方案,vba自定义函数,具体不了解我不做说明,可能这位小伙伴没有办法很好的去使用vba自定义函数吧,希望可以用内置函数这样式的。
第一阶段,当看到想获取电脑名称的时候,我想到了用INFO函数,怪我太菜知道有个INFO函数,可以实现什么?一概不知,自个儿赶紧试了下,发现INFO查询不了电脑名称啊,后来种种吧,就有了INFO函数的拓展:INFO函数插件,只有一个函数GETINFO,然后有了想法做个INFO系列吧,还有CELL嘛,接着就有了GETCELL,感觉名字一直看着不舒服,后来更新为INFO_EX、CELL_EX,意为对INFO、CELL函数的功能性拓展。
第二阶段,可以共用,那么在使用中一定存在各种各样的问题,也是我所注意不到的,个人的测试能力是非常有限的,所以现在是INFO系列函数插件,其中可能会带有各种bug的函数,以及一些还未实现的函数。欢迎大家测试,然后反馈bug,感谢!

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-7-21 10:58 | 显示全部楼层
INFO_EX函数



INFO_EX函数是对INFO函数的拓展,可以实现计算机名称、用户名称、IP、系统版本、开机运行时长、处理器、内存信息的查询

语法:INFO_EX(type_text,type)
参数:type_text   必填,指定所要获得的信息类型。
                                        COMPUTERNAME,
                                        USERNAME,
                                        IP,
                                        IPADDRESS,
                                        OSVERSION,
                                        SYSTEMUPTIME,
                                        PROCESSOR
                                        MEMORY
          type            可选,返回IP结果类型(仅type_text为IP时有效)。默认返回IP。
                                        1:洲;
                                        2:国家;
                                        3:省;
                                        4:市;
                                        5:经纬度
                                        6:返回所有IP信息


      

INFO_EX函数实现的信息类型

INFO_EX函数实现的信息类型


  如图,结果不演示了,大家可以自行测试,欢迎反馈问题或有的想法!感谢
  附上示例文件:
INFO_EX函数.rar (9.88 KB, 下载次数: 5)

  INFO系列函数插件详见1楼

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-7-21 17:56 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 胡汉三回来了 于 2024-7-21 20:56 编辑

CELL_EX函数解决【友友:念念鱼草】同一行相同颜色单元格求和

实现方案如图:

CELL_EX函数实现同一行相同颜色单元格求和

CELL_EX函数实现同一行相同颜色单元格求和

  1. H5单元格:
  2. =SUM(MAP($C5:$F5,LAMBDA(x,IF(CELL_EX(,x)=CELL_EX(,$L$1),x,0))))
  3. I5单元格:
  4. =SUM(MAP($C5:$F5,LAMBDA(x,IF(CELL_EX(,x)=CELL_EX(,$M$1),x,0))))
复制代码


详见帖子:同一行相同颜色单元格求和

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-7-23 13:32 | 显示全部楼层
本帖最后由 胡汉三回来了 于 2024-7-23 15:47 编辑

INFO系列函数插件更新如下:

INFO-1.24.723.rar (1.05 MB, 下载次数: 6)


使用方法移步1楼
欢迎大家测试反馈!


TA的精华主题

TA的得分主题

 楼主| 发表于 2024-7-23 15:02 | 显示全部楼层
本帖最后由 胡汉三回来了 于 2024-7-23 15:09 编辑

CELL_EX函数解决【友友:进击的猪人】让不同的颜色计算为不同的值

实现方案如图:

CELL_EX函数实现让不同的颜色计算为不同的值

CELL_EX函数实现让不同的颜色计算为不同的值

  1. C1单元格:
  2. =LET(a,$A$1:$A$36,b,MAP(a,LAMBDA(x,CELL_EX(,x))),c,UNIQUE(b),MAP(b,LAMBDA(y,IF(OR(y=16777215,y=13886716),0,MATCH(y,c,0)))))
复制代码
附件如下:
不同颜色对应不同的值.rar (8.86 KB, 下载次数: 2)

详见帖子:让不同的颜色计算为不同的值



TA的精华主题

TA的得分主题

 楼主| 发表于 2024-7-23 20:36 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
CELL_EX函数解决【友友:kenese】根据颜色自动变化状态

实现方案
如图:

CELL_EX函数实现根据颜色自动变化状态 方法一

CELL_EX函数实现根据颜色自动变化状态 方法一

  1. E2单元格:
  2. =MAP($D$2:$D$11,LAMBDA(x,TEXTJOIN("",1,BYROW($A$2:$A$4,LAMBDA(y,IF(CELL_EX(,x)=CELL_EX(,y),OFFSET(y,,1),""))))))
复制代码


实现方案二如图:

CELL_EX函数实现根据颜色自动变化状态 方法二

CELL_EX函数实现根据颜色自动变化状态 方法二

  1. F2单元格:
  2. =MAP($D$2:$D$11,LAMBDA(x,TEXTJOIN("",1,MAP({65535;255;5287936},{"In process";"Cancel";"Done"},LAMBDA(y,z,IF(CELL_EX(,x)=y,z,""))))))
复制代码

附件如下:
根据颜色自动变化状态.rar (8.44 KB, 下载次数: 3)

详见帖子:
根据颜色自动变化状态



TA的精华主题

TA的得分主题

 楼主| 发表于 2024-7-24 13:40 | 显示全部楼层
本帖最后由 胡汉三回来了 于 2024-7-24 13:51 编辑

CELL_EX函数解决【友友:叶子の秋】计算一定区域内背景色为红色的单元格个数

实现如图:

CELL_EX函数实现计算一定区域内背景色为红色的单元格个数

CELL_EX函数实现计算一定区域内背景色为红色的单元格个数

  1. =SUM(MAP($A1:$D6,LAMBDA(x,IF(CELL_EX(,x)=255,1,0))))
复制代码


详见帖子:如何计算一定区域内背景色为红色的单元格个数

其实1楼附件已经列举了如何通过CELL_EX函数实现单元格颜色求和、计数、数据提取,这里只是列出实际问题的解决方案与出处。

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-7-24 14:05 | 显示全部楼层
CELL_EX函数解决【友友:hello!world】若单元格有颜色则返回1,若没有颜色返回0


实现如图:

CELL_EX函数实现若单元格有颜色则返回1,若没有颜色返回0

CELL_EX函数实现若单元格有颜色则返回1,若没有颜色返回0

  1. =--AND(BYCOL($A1:$D1,LAMBDA(x,IF(CELL_EX(,x)<>16777215,1,0))))
复制代码


详见帖子:若单元格有颜色则返回1,若没有颜色返回0,可否用函数解决?

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-7-24 15:01 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
CELL_EX函数解决【友友:gxyun2010】如何按照单元格背景颜色统计员工数量


实现如图:

CELL_EX函数实现按照单元格背景颜色统计员工数量

CELL_EX函数实现按照单元格背景颜色统计员工数量

  1. E4单元格:
  2. =SUM(MAP($B$2:$B$21,LAMBDA(x,IF(CELL_EX(,x)=CELL_EX(,$D4),1,0))))
  3. F4单元格:
  4. =SUM(MAP($B$2:$B$21,LAMBDA(x,IF(CELL_EX(,x)=CELL_EX(,$D4),x,0))))
复制代码

附件如下:
统计(按照颜色).rar (8.91 KB, 下载次数: 11)

详见帖子:如何按照单元格背景颜色统计员工数量

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-12-26 03:20 , Processed in 0.043862 second(s), 9 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表