202206301510加總 按人员順序取全部值 (10163)

{=SUM(TEXT(SUM((RIGHT(SMALL(A$2:A$12/1%+IFERROR(MATCH(MID(B$2:B$12,{1,2,3},1),F:F,),9),A$2:A$12*3-2))-ROW(A2)=0)*C$2:C$12)-{0,5000},"0;!0")*{1,-0.03})

另解{=SUM(TEXT(SUM((CHAR(MOD(SMALL(ROW($1:$11)/1%+IFERROR(CODE(MID(B$2:B$12,{1,2,3},1)),99),ROW($1:$11)*3-2),100))=F2)*C$2:C$12)-{0,5000},"0;!0")*{1,-0.03})

另解{=SUM(TEXT(SUM(ISNUMBER(FIND(F2,$B$2:$B$12))*(MMULT(1-ISERR(FIND(TRANSPOSE($F$1:F1),$B$2:$B$12)),ROW($1:1)^0)=0)*$C$2:$C$12)-{0,5000},"[>]0;!0;0")*{1,-0.03})

另解{=SUM(TEXT(SUM(SUMIFS(C$2:C$12,A$2:A$12,IF(MMULT(COUNTIF(F$1:F1,MID(B$2:B$12,COLUMN(A:G),1)),T1:T7+1),,A$2:A$12),B$2:B$12,"*"&F2&"*"))-{0,5000},"0;�")*{1,-0.03})

另解{=SUM(TEXT(SUM((MMULT(1-ISERR(FIND(TRANSPOSE(F$2:F2),B$2:B12)),A3-A$2:A2)=1)*C$2:C12)%-{0,50},"0%;!0")*{100,-3})

另解{=SUM(TEXT(SUM((1-ISERR(FIND(F2,B$2:B12))-MMULT(COUNTIF(F$1:F1,MID(B$2:B12,{1,2,3},1)),{1;1;1})>0)*C$2:C12)%-{0,50},"0%;!0")*{100,-3})

另解{=SUM(TEXT(SUM((FIND(1,MMULT(10^-MATCH(MID($B$2:$B$12,{1,2,3},1),$F$2:$F$9&"",),{1;1;1}))-1=ROW())*$C$2:$C$12)%-{0,50},"0%;!0")*{100,-3})

另解{=SUM(TEXT(SUM((1-ISERR(FIND(F2,B$2:B$12)))*(0=MMULT(COUNTIF(F$1:F1,MID(B$2:B$12,COLUMN(A:G),1)),ROW(1:7)^0))*C$2:C$12)-{0,5000},{0,"[<]!0"})*{100,-3}%)

另解{=SUM(TEXT(SUM(ISNUMBER(FIND(F2,B$2:B$12))*(MMULT((MID(B$2:B$12&"ZZ",{1,2,3},1)>=F2)/1,{1;1;1})=3)*C$2:C$12)-{0,5000},"0;!0")*{1,-0.03})

另解{=SUM(TEXT(SUM(ISNUMBER(FIND(F2,B$2:B$12))*(MMULT((MID(B$2:B$12&"ZZ",{1,2,3},1)>=F2)/1,{1;1;1})=3)*C$2:C$12)-{0,5000},"0;!0")*{1,-0.03})

沒有上一則|日誌首頁|沒有下一則
回應
關鍵字
    沒有新回應!
    blank