ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[已解决] 求稍微复杂的统计公式

[复制链接]

TA的精华主题

TA的得分主题

发表于 2024-10-15 19:51 | 显示全部楼层 |阅读模式
本帖最后由 409963457 于 2024-10-18 13:33 编辑

学校出勤人数次数统计,由于比较复杂,所以求助,感谢大神
在统计表中,黄色的教学周,绿色的是要求统计数据出现的区域,统计规则如下:
一、被统计表介绍:


1、被统计表是“早读”表,左侧列有周次、院系、班级。
2、接下来是人数列,分有:班级人数,执勤人数,请假人数。注:应出勤人数=班级人数-执勤人数-请假人数!
3、然后是接下来5天的量化数据。
二、统计表

1、应出勤人次。是指在黄色教学周内,班级周一至周五,发生了早读检查的应出勤人数之和。例如:1班应出勤50人,有3天早读,应出勤150人次。
2、实际出勤人次。统计班级周一至周五,发生了早读检查的实际出勤人数之和。
3、有部分班级因为课程安排,不同周次发生早读的次数不同。例如黄色举例标识班班级,在当周分别发生了2,5,3次早读。
4、当天有无发生早读,主要看当天是否有“得分”。

绿色部分自己用了最原始的countif函数,但是感觉过于臃肿复杂,求大神!
早读.png
统计.png

班级常规量化系统.zip

125.56 KB, 下载次数: 16

TA的精华主题

TA的得分主题

发表于 2024-10-16 08:25 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
image.jpg
对于应出勤人数的解释以那个为准?

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-10-16 09:16 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
mykitami 发表于 2024-10-16 08:25
对于应出勤人数的解释以那个为准?

1.2为准,2.1只是举例

TA的精华主题

TA的得分主题

发表于 2024-10-16 12:48 | 显示全部楼层
D6公式
=LET(pp,LAMBDA(x,SUM(BYCOL(SUMIFS(OFFSET(早读!$D:$D,,{1,2,3}),早读!$A:$A,TOCOL(统计表!$C$1:$G$1),x,">0",早读!$B:$B,$B6),SUM)*{1,-1,-1})),REDUCE(0,OFFSET(早读!$H:$H,,SEQUENCE(,5,6,6)),LAMBDA(m,n,m+pp(n))))
E6公式
=SUM(SUMIFS(OFFSET(早读!$H:$H,,SEQUENCE(,5,1,6)),早读!$A:$A,TOCOL(统计表!$C$1:$G$1),OFFSET(早读!$H:$H,,SEQUENCE(,5,6,6)),">0",早读!$B:$B,$B6))

下拉

TA的精华主题

TA的得分主题

发表于 2024-10-16 13:03 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 良良 于 2024-10-16 16:18 编辑

班级数与“早读"表不匹配?
C6若改为:3,
D6
  1. =NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$N:$N,"<>"))+NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$T:$T,"<>"))+NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$Z:$Z,"<>"))+NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$AF:$AF,"<>"))+NPV(-2,SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),早读!$AL:$AL,"<>"))
复制代码


简化一下:
  1. =SUM(MMULT(SUMIFS(OFFSET(早读!$E:$E,,{1,0,2}),早读!$B:$B,$B6,早读!$C:$C,"*"&$B6&$C6&"*",早读!$A:$A,">="&MIN($C$1:$G$1),早读!$A:$A,"<="&MAX($C$1:$G$1),OFFSET(早读!$N:$N,,ROW($1:$5)*6-6),"<>"),{-1;1;-1}))
复制代码

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-10-16 17:00 | 显示全部楼层
本帖最后由 409963457 于 2024-10-17 13:20 编辑
mykitami 发表于 2024-10-16 12:48
D6公式
=LET(pp,LAMBDA(x,SUM(BYCOL(SUMIFS(OFFSET(早读!$D:$D,,{1,2,3}),早读!$A:$A,TOCOL(统计表!$C$1:$ ...

公式能够跨文件引用吗?我垮了文件就显示错误

TA的精华主题

TA的得分主题

 楼主| 发表于 2024-10-17 12:24 | 显示全部楼层
良良 发表于 2024-10-16 13:03
班级数与“早读"表不匹配?
C6若改为:3,
D6

班级数不参与运算,只是单纯显示。所以不知道改c6目地。
并且计算值不正确。

TA的精华主题

TA的得分主题

发表于 2024-10-17 16:10 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
409963457 发表于 2024-10-17 12:24
班级数不参与运算,只是单纯显示。所以不知道改c6目地。
并且计算值不正确。

正确的值是多少?请模拟一下。
另外,“早读”表的工作表保护密码请告知一下,便于测试。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-11-22 21:10 , Processed in 0.045851 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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