本帖最后由 冻豆腐 于 2012-10-12 09:20 编辑
抛砖引玉,没有验证太多,可能还有错误。数组公式
步骤1
- =RIGHT(MAX((MID(10^3+A1:J15,2,1)="1")*A1:J15)*SUM((((MID(10^3+A1:J15,2,1)="1")+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,4,1)="5"))>0)*(3-LEN(SUBSTITUTE(TEXT(A1:J15,"000"),"1",)))),2)+100
复制代码 步骤2
- =RIGHT(MAX((MID(10^3+A1:J15,2,1)="1")*A1:J15)*SUM((((MID(10^3+A1:J15,2,1)="1")+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,4,1)="5"))>0)*(3-LEN(SUBSTITUTE(TEXT(A1:J15,"000"),"1",)))),2)+100&RIGHT(MAX((MID(10^3+A1:J15,3,1)="9")*A1:J15)*SUM((((MID(10^3+A1:J15,2,1)="1")+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,4,1)="5"))>0)*(3-LEN(SUBSTITUTE(TEXT(A1:J15,"000"),"9",)))),2)+100&RIGHT(MAX((MID(10^3+A1:J15,4,1)="5")*A1:J15)*SUM((((MID(10^3+A1:J15,2,1)="1")+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,4,1)="5"))>0)*(3-LEN(SUBSTITUTE(TEXT(A1:J15,"000"),"5",)))),2)+100
复制代码
最终合并的公式
- =SUM(LARGE(MID(RIGHT(MAX((MID(10^3+A1:J15,2,1)="1")*A1:J15)*SUM((((MID(10^3+A1:J15,2,1)="1")+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,4,1)="5"))>0)*(3-LEN(SUBSTITUTE(TEXT(A1:J15,"000"),"1",)))),2)+100&RIGHT(MAX((MID(10^3+A1:J15,3,1)="9")*A1:J15)*SUM((((MID(10^3+A1:J15,2,1)="1")+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,4,1)="5"))>0)*(3-LEN(SUBSTITUTE(TEXT(A1:J15,"000"),"9",)))),2)+100&RIGHT(MAX((MID(10^3+A1:J15,4,1)="5")*A1:J15)*SUM((((MID(10^3+A1:J15,2,1)="1")+(MID(10^3+A1:J15,3,1)="9")+(MID(10^3+A1:J15,4,1)="5"))>0)*(3-LEN(SUBSTITUTE(TEXT(A1:J15,"000"),"5",)))),2)+100,{2,5,8},2)+0,{1,2,3})*10^{4,2,0})
复制代码
|