ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[求助] 日期排序后统计特定列乘积

[复制链接]

TA的精华主题

TA的得分主题

发表于 2019-6-15 21:26 | 显示全部楼层 |阅读模式
需求:
    做一个excel公式,要求在一个公式里实现、不要加辅助列:
    先选出B列=2018、G列>0 的行;
    然后在符合条件的行里、选出每日内J值最大的行;
    若这些行的E列不等于"请假"、K列不等于"1"、L列>0.01;
    则把他们的L值相乘。
    以前“micch ”给过一个公式:
=PRODUCT(IF((COUNTIFS(A:A,A2:A463,B:B,2018,G:G,">0",J:J,">"&IF((B2:B463=2018)*(G2:G463>0),J2:J463-ROW(2:463)%%%%%))=1)*(E2:E463<>"请假")*(K2:K463<>1)*
(L2:L463>1%),L2:L463,1))
    这公式很不错,不过这种算法每一个日期都要从头到尾把所有行计算过才得出最终结果。而我实际数据有几十万行,计算一次数据就久得可怕了。
    我想折中一下,我先把日期那行倒序排好,看哪个大神帮再弄一个公式。
    考虑到每个日期只有几行~几十行,而且全部排在一起,如果有合适的公式计算数度应该会快不少。
    拜托了。

公布数据.rar

29.97 KB, 下载次数: 3

TA的精华主题

TA的得分主题

发表于 2019-6-15 21:46 | 显示全部楼层
本帖最后由 micch 于 2019-6-15 21:47 编辑
  1. =PRODUCT(IF((A2:A463<>A1:A462)*(B2:B463=2018)*(G2:G463>0)*(E2:E463<>E1)*(K2:K463<>"1")*(L2:L463>1%),L2:L463,1))
复制代码


按日期,第二条件J列降序排序后公式ps。几十万行数据,其实可以透视表的,如果不能排序,其实代码自定义函数是个好办法。

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-6-15 22:24 | 显示全部楼层
micch 发表于 2019-6-15 21:46
按日期,第二条件J列降序排序后公式ps。几十万行数据,其实可以透视表的,如果不能排序,其实代码自定 ...

这个新公式算的值是10.80240105,用你的旧公式算是1.06
我看了一下,应该是漏了这个条件“选出每日内J值最大的行”
麻烦帮再该改好吗?

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-6-15 22:33 | 显示全部楼层
micch 发表于 2019-6-15 21:46
按日期,第二条件J列降序排序后公式ps。几十万行数据,其实可以透视表的,如果不能排序,其实代码自定 ...

J列在实际使用中是不能排序的,如果日期、和J列都排序的话,我已经有超快的解决方案了。
只能考虑日期排序而其他不排序的。

TA的精华主题

TA的得分主题

发表于 2019-6-16 09:32 | 显示全部楼层
本帖最后由 micch 于 2019-6-16 09:34 编辑
zggxlzhxg 发表于 2019-6-15 22:33
J列在实际使用中是不能排序的,如果日期、和J列都排序的话,我已经有超快的解决方案了。
只能考虑日期排 ...

J列是取值的唯一条件,其他都是筛选条件,所以J不排序,只能是countifs来取最大值,公式简化不了,日期排不排序都一样
几十万数据,筛选2018年的,如果2018年的数据只有几百个,那可以offset定位减少运算量,如果2018年的数据就几十万,那就没办法了

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-6-17 16:14 | 显示全部楼层
本帖最后由 zggxlzhxg 于 2019-6-18 23:57 编辑
micch 发表于 2019-6-15 21:46
按日期,第二条件J列降序排序后公式ps。几十万行数据,其实可以透视表的,如果不能排序,其实代码自定 ...

我的表述有误,在下面重改了

TA的精华主题

TA的得分主题

 楼主| 发表于 2019-6-18 23:59 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
micch 发表于 2019-6-15 21:46
按日期,第二条件J列降序排序后公式ps。几十万行数据,其实可以透视表的,如果不能排序,其实代码自定 ...


上面代码的逻辑是:时间和J列排好后,判断当日J列最高值的行是否符合那一长串条件
而我的要求是:时间和J列排好后,选出同时符合B列、G列条件的行中J值最高的,然后在看再是否符合E\K\L列的条件。也就是说“符合B列、G列条件的行中J值最高的行”并不一定就是“当日所有行中J值最高的行”。

还是麻烦您帮改一改公式吧。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-28 18:26 , Processed in 0.050157 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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