求稍微复杂的统计公式

409963457

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


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

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

绿色部分自己用了最原始的countif函数，但是感觉过于臃肿复杂，求大神！

mykitami

对于应出勤人数的解释以那个为准？

409963457

mykitami 发表于 2024-10-16 08:25
对于应出勤人数的解释以那个为准？

1.2为准，2.1只是举例

mykitami

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))

下拉

良良

班级数与“早读"表不匹配？
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}))

复制代码