2楼
biaotiger1 |
史上最长的公式了 呵呵
等着看高手精彩作品
- =TEXT(COUNTA(B$2:B2),"00")&TEXT(COUNTA(OFFSET(INDIRECT("c"&MATCH(LOOKUP("座",B$2:B2),B$2:B2,)),1,,ROW()-MATCH(LOOKUP("座",B$2:B2),B$2:B2,),)),"00;;")&TEXT((LEN(D2&E2)>0)*COUNTA(OFFSET(INDIRECT("d"&MATCH(LOOKUP("座",C$2:C2),C$2:C2,)),1,,ROW()-MATCH(LOOKUP("座",C$2:C2),C$2:C2,),)),"00;;")&TEXT((E2<>"")*COUNTA(OFFSET(INDIRECT("e"&MATCH(LOOKUP("座",D$2:D2),D$2:D2,)),1,,ROW()-MATCH(LOOKUP("座",D$2:D2),D$2:D2,),)),"00;;")
- =SUBSTITUTE(TRIM(COUNTA(B$2:B2)&" "&TEXT(COUNTA(OFFSET(INDIRECT("c"&MATCH(LOOKUP("座",B$2:B2),B$2:B2,)),1,,ROW()-MATCH(LOOKUP("座",B$2:B2),B$2:B2,),)),"#;;")&" "&TEXT((LEN(D2&E2)>0)*(COUNTA(D$2:D2)>0)*COUNTA(OFFSET(INDIRECT("d"&MATCH(LOOKUP("座",C$2:C2),C$2:C2,)),1,,ROW()-MATCH(LOOKUP("座",C$2:C2),C$2:C2,),)),"#;;")&" "&TEXT((E2<>"")*COUNTA(OFFSET(INDIRECT("e"&MATCH(LOOKUP("座",D$2:D2),D$2:D2,)),1,,ROW()-MATCH(LOOKUP("座",D$2:D2),D$2:D2,),)),"#;;"))," ","-")
 多级自动编码-biaotiger1.rar
|
6楼
gouweicao78 |
我的答案：
题目1：
A2公式
- =LEFT(A1,2*MATCH("*",B2:E2,)-2)&TEXT((0&MID(A1,2*MATCH("*",B2:E2,)-1,2))+1,"00")
水星钓鱼给出3个答案，都比我的答案好，呵呵：
- =LEFT(F1,2*MATCH("座",B2:E2)-2)&TEXT((0&MID(F1,2*MATCH("座",B2:E2)-1,2))+1,"00")
- =LEFT(G1,2*MATCH("座",B2:E2)-2)&RIGHT(0&(0&MID(G1,2*MATCH("座",B2:E2)-1,2))+1,2)
- =RIGHT(0&LEFT(H1&0&0,2*MATCH("座",B2:E2))+1,2*MATCH("座",B2:E2))
rongjun兄的答案：
- =RIGHT(10^8+LEFT(A1&REPT(0,8),2*MATCH("*",B2:E2,))+1,2*MATCH("*",B2:E2,))
题目2：
我的答案1，借用题目1的结果（相当于题目1作为辅助列）：
- =MID(SUBSTITUTE(TEXT(A2,REPT("-00",LEN(A2)/2)),"-0","-"),2,9)
不借用答案1的公式：
- =MID(SUBSTITUTE(TEXT(SUM(TRIM(MID(SUBSTITUTE(0&A1&"-0-0-0","-",REPT(" ",50)),COLUMN(A:D)*50-49,50))*100^(MATCH("*",B2:E2,)-COLUMN(A:D)))+1,REPT("-00",MATCH("*",B2:E2,0))),"-0","-"),2,11)
水星钓鱼给出2个答案（和我想法比较接近，不过比我的简洁）：
- =MID(SUBSTITUTE(SUBSTITUTE(TEXT(SUM(MID(SUBSTITUTE(0&F1&"-0-0-0","-",REPT(" ",9)),{1,9,21,31},9)*100^(MATCH("座",B2:E2)-{1,2,3,4}))+1,REPT("-00",4)),"-00",),"-0","-"),2,11)
- =LEFT(G1&"-",IF(B2="",FIND(" ",SUBSTITUTE(G1&"-","-"," ",MATCH("座",C2:E2)))))&1+MID(SUBSTITUTE(0&G1&"-0","-",REPT(" ",9)),9*MATCH("座",B2:E2)-8,9)
rongjun兄的答案：
- =IF(MATCH("*",B2:E2,)=1,COUNTA($B$2:B2),LOOKUP("座",OFFSET($A$1:A1,,MATCH("*",B2:E2,)-1),$A$1:A1)&"-"&COUNTA(OFFSET(B2,,MATCH("*",B2:E2,)-1,LOOKUP("座",OFFSET($A$1:A1,,MATCH("*",B2:E2,)-1),ROW($1:1))-ROW())))
简单点评：总体上说，toby08的答案是本题的最佳答案。我一直想用SUBTOTAL+多维引用构建一下的，不过没成功，当然题目2如果使用题目1的结果作辅助列的话，问题就简单得多。
自动定额编码答案.rar
|
