【第133期】人员录用（开帖）

误导帝

211

1. =IF(A2>SUM(I$2:I$4),"",IF(J1:O1=0,A2,INDEX(A$2:F75,MATCH(1,FREQUENCY(1,MMULT((COUNTIFS(M$1:M1,IF({1,0},C$2:C75,"*"),N$1:N1,D$2:D75)<SUMIF(H$2:H4,D$2:D75,I$2)/{2,1})-COUNTIF(O$1:O1,E$2:E75),{1;1})-F$2:F75%),),)))

复制代码

补充内容 (2021-11-10 13:18):
此楼请作废，新答案补充在14楼

jokulboy

混1分~~~

1. =IF($A2>SUM($I$2:$I$4),"",IF(J2=0,N(A2),LOOKUP(,0/FREQUENCY(-888,-IF(MMULT(N(COUNTIFS(OFFSET($M$1:$M1,,{0,1,2}),$C$2:$E$75,OFFSET($N$1:$N1,,{0,0,0}),$D$2:$D$75)<SUMIF($H$2:$H$4,$D$2:$D$75,$I$2:$I$4)/{2,1,99}),{1;1;1})=3,$F$2:$F$75)),A$2:A$75)))

复制代码

砍一刀~~~

1. =IF($A2>SUM($I$2:$I$4),"",IF(J2=0,A2,LOOKUP(,0/FREQUENCY(-888,-IF(MMULT(N(COUNTIFS(OFFSET($M$1:$M1,,{0,1,2}),$C$2:$E$75,$N$1:$N1,$D$2:$D$75)<SUMIF($H$2:$H$4,$D$2:$D$75,$I$2)/{2,1,99}),{1;1;1})=3,$F$2:$F$75)),A$2:A$75)))

复制代码

丢丢表格

K2:P2  区域数组

1. =INDEX(IF(A$2:A77,IF(J1:O1=0,A2,A$2:F77),""),MATCH(1,FREQUENCY(5,MMULT((COUNTIFS(M$1:M1,IF({1,0},C$2:C75,"*"),N$1:N1,D$2:D75)*{2,1}<SUMIF(H$1:H5,D$2:D75,I$1))-COUNTIF(O$1:O1,E$2:E75),{3;3})-F$2:F75%),))

复制代码

误导帝

200，实在砍不动了：

1. =IFERROR(INDEX(IF(J1:O1=0,A2,A$2:F75),MATCH(1,FREQUENCY(1,MMULT((COUNTIFS(M$1:M1,IF({1,0},C$2:C75,"*"),N$1:N1,D$2:D75)<SUMIF(H$2:H4,D$2:D75,I$2)/{2,1})-COUNTIF(O$1:O1,E$2:E75),{1;1})-F$2:F75%),),),"")

复制代码

delete_007

1. =IF(A2>I$2+I$3+I$4,"",IF(J1:O1=0,A2,INDEX(A$2:F75,MATCH(1,FREQUENCY(4,MMULT((COUNTIFS(M$1:M1,{"><",""}&C$2:C75,N$1:N1,D$2:D75)<SUMIF(H$2:H4,D$2:D75,I$2)/{1,2})-COUNTIF(O$1:O1,E$2:E75),{3;3})-F$2:F75%),),)))

复制代码

魚。。

额，来参与下东东的题目。根据需求，一个个条件输了一遍。就这样了，只能停留到能解决问题的技术水平，简化就算了

1. =IF(ROW()>SUM($I$2:$I$4)+1,"",IF(K$1="序号",N(K1)+1,OFFSET(A$1,100-MOD(MAX(IFERROR(ISNUMBER(MATCH($D$2:$D$75,IF(COUNTIF($N$1:$N1,$H$2:$H$4)<$I$2:$I$4,$H$2:$H$4),))*(COUNTIF($O$1:$O1,$E$2:$E$75)<1)*$F$2:$F$75/((COUNTIFS($M$1:$M1,$C$2:$C$75,$N$1:$N1,$D$2:$D$75)>=VLOOKUP(T(IF({1},$D$2:$D$75)),$H:$I,2,)/2)+1)%%-$A$2:$A$75,)),100),)))

复制代码

阿哥违法

完全不会，坐等答案

小|树

1. =IF(A2>SUM(I$2:I$4),"",IF(J1:O1>0,OFFSET(A$1:F$1,RIGHT(MAX(MMULT((COUNTIFS(N$1:N1,D$2:D75,M$1:M1,IF({1,0},"*",C$2:C75))<SUMIF(H$1:H4,D$2:D75,I$1)/{1,2})-COUNTIF(O$1:O1,E$2:E75),{1;1})*F$2:F75/1%%-A$2:A75*99),2),),A2))

复制代码

sayhi95

一行六列区域数组

1. =IFERROR(INDEX(IF(J1:O1=0,A2,A$1:F75),MOD(SMALL(IF(MMULT((COUNTIFS(M$1:M1,IF({1,0},C$2:C75,"*"),N$1:N1,D$2:D75)*{2,1}<SUMIF(H$2:H4,D$2:D75,I$2))-COUNTIF(O$1:O1,E$2:E75),{1;1})=2,ROW($2:75)-F$2:F75/1%%),1),100),),"")

复制代码

太阳之子

1. =IF(A2>I$2+I$3+I$4,"",IF(J1:O1=0,A2,OFFSET(A$1,MOD(-MAX(IF(MMULT(N(COUNTIFS(N$1:N1,D$2:D75,M$1:M1,{"",">1"}&C$2:C75)<SUMIF(H:H,D$2:D75,I$1)/{2,1}),{1;1})=2,(COUNTIF(O$1:O1,E$2:E75)=0)*F$2:F75/1%%-A$2:A75)),100),,,6)))

复制代码

再减1字符，217