大神求助！计算满足件数和天数要求时的最早日期

悦。

求大神指教，想计算，每个人员，达成每日要求（每日达成件数>=15，工作时长>=8小时）时，当天达成的件数才可以计入150件的要求件数里，想算出满足每日要求时，完成150件所在的最早日期

624134745

=INDEX($A$2:$A$21,MATCH(1=1,MMULT(N(ROW(1:20)>=COLUMN(A:T)),($B$2:$B$21=G4)*($C$2:$C$21>=15)*($D$2:$D$21>=8)*$C$2:$C$21)>=150,))

E学子

1. =INDEX($A$2:$A$21,MATCH(1,N(SUMIFS($C$2:$C$21,$A$2:$A$21,"<="&$A$2:$A$21,$B$2:$B$21,G4,$C$2:$C$21,">="&I4,$D$2:$D$21,">="&J4)>=H4),))

复制代码

条件引用的HIJ列的内容，如果你的条件固定的话，可以将单元格地址替换成等价常量。

无关岁月

1. =LET(_a,FILTER(A$2:D$21,(B$2:B$21=G4)*(C$2:C$21>=I4)*(D$2:D$21>=J4)),_b,SCAN(0,INDEX(_a,,3),SUM),INDEX(_a,MATCH(1=1,_b>=H4,),1))

复制代码

ww87725244

=MIN(LET(x,FILTER($A$2:$D$21,($B$2:$B$21=G4)*($C$2:$C$21>=I4)*($D$2:$D$21>=J4)),IF(SCAN(,CHOOSECOLS(x,3),SUM)>=H4,CHOOSECOLS(x,1),"")))

jisijie630

=INDEX(A:A,MATCH(149,MMULT(MOD(IFERROR(SMALL(ROW($2:$21)/1%+(B$2:B$21=G4)*(C$2:C$21>14)*(D$2:D$21>=8)*C$2:C$21,(COLUMN(A:Z)<=ROW($1:$20))*COLUMN(A:Z)),),100),Z1:Z26+1))+2)

逐渐陌生

jivy1212

1. =LOOKUP(,0/FREQUENCY(H4,SUMIFS(C:C,B:B,G4,D:D,">="&J4,C:C,">="&I4,A:A,"<="&$A$2:$A$21)*($B$2:$B$21=G4)),$A$2:$A$21)

复制代码

mykitami

=LET(a,FILTER(CHOOSECOLS($A$2:$D$21,1,3),($B$2:$B$21=G11)*($C$2:$C$21>=I4)*($D$2:$D$21>=8)),XLOOKUP(H4,SCAN(0,TAKE(a,,-1),SUM),TAKE(a,,1),,1))

悦。

感谢各位大神相助，已解决