KT的公式有误,可改为:- =SUM(LARGE(MOD(RIGHT(LARGE((--MID(10^3+LARGE(A1:J15,ROW(1:150)),{2,3,4},1)={1,9,5})*LARGE(A1:J15,ROW(1:150))+10^{3,4,5},{301,151,1}),3)*MMULT(COLUMN(A:ET)^0,4-LEN(SUBSTITUTE(LARGE(((MID(A1:J15+1000,2,1)="1")+(MID(A1:J15+1000,3,1)="9")+(MOD(A1:J15,10)=5)>0)*A1:J15+2000,ROW(1:150)),{1,9,5},))),100),{1,2,3})*10^{4,2,0})
复制代码 不降为1维硬整即为,- =SUM(LARGE(MOD(CHOOSE({1,2,3},SUM((LEN(A1:J15)-LEN(SUBSTITUTE(A1:J15,5,)))*((MOD(A1:J15,10)=5)+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,2,1)="1")>0))*MAX((MOD(A1:J15,10)=5)*A1:J15),SUM((LEN(A1:J15)-LEN(SUBSTITUTE(A1:J15,9,)))*((MOD(A1:J15,10)=5)+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,2,1)="1")>0))*MAX((MID(10^3+A1:J15,3,1)="9")*A1:J15),SUM((LEN(A1:J15)-LEN(SUBSTITUTE(A1:J15,1,)))*((MOD(A1:J15,10)=5)+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,2,1)="1")>0))*MAX((MID(10^3+A1:J15,2,1)="1")*A1:J15)),100),{1;2;3})*10^{4;2;0})
复制代码 |