現職各店 職務員工 (10118)

 

  E2:E22=IFERROR(IF(COUNTIF(B$2:B2,B2)=1,LOOKUP(1,0/(COUNTIFS(B$2:B$22,B$2:B$22,$A$2:$A$22,"离职")=0)/(B$2:B$22=B2),C$2:C$22),""),"")

H3:J5=IFERROR(LOOKUP(1,0/($D$2:$D$22=$G3)/($E$2:$E$22=H$2),$B$2:$B$22),"")

H9:J11=IFERROR(LOOKUP(1,0/(COUNTIFS($B$2:$B$29,$B$2:$B$29,$A$2:$A$29,"离职")=0)/(COUNTIF(OFFSET($B$2,,,ROW($1:$28)),$B$2:$B$29)=COUNTIF($B:$B,$B$2:$B$29))/($D$2:$D$29=$G3)/($C$2:$C$29=H$2),$B$2:$B$29),"")