[开贴]字符争霸第八期：拆分列标和行标

笨笨四

您还在抱怨论坛的竞赛之题太少吗？
你还在惆怅自己的功力不能展示吗？
来参加字符争霸吧！
秀出你的高端！秀出你的大气！
展示你的奢华！再现你的魅力！
Let’s go!

字符争霸第8期：拆分列标和行标
1.要求：见附件
2.赛题难度指数：中级。优秀答案额外+BB+JY

3.答题截止时间：2014年4月6日18:00
4.请勿重复占楼！！！！
5.更正：附件中说明“一个函数”是指“一个函数公式”

本期霸主：11楼：路边的记忆，112字符
      榜眼：4楼：vadera，119字符
      探花：15楼：huangcaiguang，145字符。
强中自有强中手，能人背后有能人。相信你，本期霸主就是你

字符争霸第7期链接：http://www.excelpx.com/thread-320329-1-1.html
总结贴：2楼！

笨笨四

各位老师、E友：

首先，因个人私事耽误了开贴，向各位老师、E友说声“对不起”，请大家谅解。
    字符争霸第八期拆分行标和列标，难度有所提高，但依然得到各位老师、E友的大力支持，奉献出精彩的公式和思路，衷心的表示感谢，并且也得到“路边的记忆”老师绝妙的公式，再次表示诚挚的感谢。

现就预设公式作简要解释：

    预设公式：G2：J2区域数组，下拉。

公式1：182字符

=TRIM(MID(SUBSTITUTE(REPLACE(REPLACE(A2,MIN(FIND(ROW($1:$9),A2&5/19,FIND(":",A2&":"))),,REPT(" ",15)),MIN(FIND(ROW($1:$9),A2&5/19)),,REPT(" ",15)),":",REPT(" ",15)),{1,15,30,45},15))

公式2：118字符

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(MID(CELL("address",(A:A,INDIRECT(A2))),7,99),"$",REPT(" ",15)),{1,15,30,45},15),":",))

公式1，普通思路，将字母与数字之间、替换“:”，使其有足够多的空字符，采用FIND(row(1:9)，A2)来查找最小的位置（因为单元格地址行号首位不可能为0，直接查1至9），为了保证能每个数都能查到，将A2连接5/19，在此位置用replace填充空字符，然后再用FIND来查到第二段字母与数字相邻的位置，这个时候就需要采用find的第三参数来定位（以“:”为起始点，开始查找）。当然，这两步先后顺序可以调换。第三步，将“:”替换成空字符。最后采用MID，分别提取。为了省字符，采用区域数组，使用了一个常量{1,15,30,45}。

公式2：将数据源所示单元格转换成文本地址，利用CELL（"address"，reference）的性质，但是直接使用INDIRECT(A2),CELL返回的只是第一个单元格地址，但我们要返回整个单元格地址，怎么办？这里有个小技巧，给他配上一个单元格cell("address" ,(A1,indirect(A1))),就可得到"$A$1,$FD$67:$ADV$156"这样一个文本地址字符串，然后接下来就普通的替换、提取思路，用substitute替换“$”成足够多的空字符，再用MID分别提取。当然，为了MID提取的方便，A1为绝对引用$A$1，也可用A$1,A:A等等。当然，有多位老师在indirect中采用其他精妙的方法，比如4楼老师，将“:”连接于A2之后和之前，虽然只是位置的变换，但却让字符更加精炼，每一字符是智慧的结晶，精彩无比！

霸主公式更值得学习，向“路边的记忆”老师致敬！

再次感谢大家的支持，字符争霸因你们而精彩！E路有你！

1091126096

用mid截取字符，花了几个小时写出一个公式，字符多得不好意思贴出来。

题目已经说的明白，这些字符串都表示一个单元格区域，可column和cell返回单元格地址的列标都是数字，不是字母，excel没有将十进制转换为二十六进制的函数。虽然应该有其它办法，可是想不出来。


公式1是一个G2:J2区域数组，228字符
公式2，225字符，数组公式，G2右拉下拉 ，有通用性，不限G2右拉下拉。  字符还可减少，但减少后没有通用性了。
公式3，167字符，由公式2优化而来，只能在G2右拉下拉，不能写在其他列。
公式4，公式3启用迭代可改写为公式4，G2右拉下拉 ，166字符。
公式5，另外一种思路，G2:J2区域数组，200字符。
公式6，G2:J2区域数组 ，162字符。
公式7，G2三键回车，160字符。

1. =IF(ISERR(FIND(":",A2))*COLUMN()>8,"",IF(MOD(COLUMN(),2),SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(TRIM(MID(SUBSTITUTE(A2,":",REPT(" ",9)),{1,1,9,9},9)))),4),1,),ROW(INDIRECT(TRIM(MID(SUBSTITUTE(A2,":",REPT(" ",9)),{1,1,9,9},11))))))
   
2. =SUBSTITUTE(MID($A2,SMALL((1+MMULT(-ISERR(-MID(1&$A2,ROW($1:$98)+{0,1},1)),{1;1})<>0)*99+ROW($1:$98),COLUMN(A1)),SUM(SMALL((1+MMULT(-ISERR(-MID(1&$A2,ROW($1:$98)+{0,1},1)),{1;1})<>0)*99+ROW($1:$98),COLUMN(A:B))*{-1,1})),":",)
   
3. =MID($A2,SUM(LEN($F2:F2),2^(COLUMN()>8))-LEN($F2),SUM(SMALL((1+MMULT(-ISERR(-MID(1&$A2,ROW($1:18)+{0,1},1)),{1;1})<>0)*19+ROW($1:18),COLUMN(A:B))*{-1,1})-(COLUMN()=9))
   
4. =MID($A2,SUM(LEN($G2:G2),2^(COLUMN()>8))-LEN(G2),SUM(SMALL((1+MMULT(-ISERR(-MID(1&$A2,ROW($1:18)+{0,1},1)),{1;1})<>0)*19+ROW($1:18),COLUMN(A:B))*{-1,1})-(COLUMN()=9))
   
5. =IF(ISERR(FIND(":",A2))*COLUMN()>8,"",IF(MOD(COLUMN(),2),SUBSTITUTE(ADDRESS(1,COLUMN(INDIRECT(A2))+(COLUMNS(INDIRECT(A2))-3)*(COLUMN()=9),4),1,),(ROWS(INDIRECT(A2))-1)*(COLUMN()=10)+ROW(INDIRECT(A2))))
   
6. =TRIM(MID(SUBSTITUTE(REPLACE(A2,IF(COLUMN()<9,MIN(FIND(ROW($1:$10)-1,A2&(5/19))),LEN(A2)-COUNT(RIGHT(A2,ROW($1:11))*1)+1),,"     "),":","       "),{1,7,10,17},6))
   
7. =TRIM(MID(SUBSTITUTE(REPLACE($A2,MIN(FIND(ROW($1:$10)-1,$A2&(5/19),FIND(":",$A2&":")^(COLUMN()>8))),,"      "),":","      "),1+(COLUMN()>7)*6+(COLUMN()>9)*9,7))

复制代码

vadera

第一稿公式

1. =IFERROR(TRIM(MID(SUBSTITUTE(CELL("address",INDIRECT(MID($A2,FIND(":",$A2&":")*(COLUMN()>8)+1,99))),"$","         "),18-9*MOD(COLUMN(),2),9)),"")

复制代码

优化：

1. =TRIM(MID(SUBSTITUTE(IFERROR(CELL("address",INDIRECT(MID($A2,FIND(":",$A2&":")*(COLUMN()>8)+1,99))),),"$","   "),8-5*MOD(COLUMN(),2),5))

复制代码

常量数组G2:J2区域数组

1. =TRIM(MID(SUBSTITUTE(IFERROR(CELL("address",INDIRECT(MID(A2,FIND(":",A2&":")*{0,0,1,1}+1,99))),),"$","   "),{3,8,3,8},5))

复制代码

再优化
119

1. =TRIM(MID(SUBSTITUTE(IFERROR(CELL("address",INDIRECT(MID(A2,FIND(":",":"&A2,{1,1,2,2}),99))),),"$","   "),{3,8,3,8},5))

复制代码

砂海

=MID("0"&$A2,(COLUMN()=9)+1+SMALL(IF((ISERROR(--MID("0"&$A2,ROW(A$1:A$39),1))<>ISERROR(--MID("0"&$A2,ROW(A$2:A$40),1))),ROW(A$1:A$39),99),COLUMN()-6),SMALL(IF((ISERROR(--MID("0"&$A2,ROW(A$1:A$39),1))<>ISERROR(--MID("0"&$A2,ROW(A$2:A$40),1))),ROW(A$1:A$39),99),COLUMN()-5)-SMALL(IF((ISERROR(--MID("0"&$A2,ROW(A$1:A$39),1))<>ISERROR(--MID("0"&$A2,ROW(A$2:A$40),1))),ROW(A$1:A$39),99),COLUMN()-6)-(COLUMN()=9))

407字符
------------------------------------------
=MID($A2,SUM((IF(ISERROR(--MID($A2,ROW(OFFSET(A$1,,,LEN($A2))),1)),1,2)+IF(ROW(OFFSET(A$1,,,LEN($A2)))>=FIND(":",$A2&":"),2)<COLUMN()-6)*1)+1+(COLUMN()=9),MAX(SUM((IF(ISERROR(--MID($A2,ROW(OFFSET(A$1,,,LEN($A2))),1)),1,2)+IF(ROW(OFFSET(A$1,,,LEN($A2)))>=FIND(":",$A2&":"),2)=COLUMN()-6)*1)-(COLUMN()=9),1))

315字符

jio1ye

1. =IF(COLUMN()=7,IF(CODE(MID($A2,2,1))>57,LEFT($A2,2),LEFT($A2,1)),IF(COLUMN()=8,MID($A2,LEN($G2)+1,IFERROR(FIND(":",$A2),LEN($A2)+1)-LEN($G2)-1),IF(ISERR(FIND(":",$A2)),"",IF(COLUMN()=9,IF(CODE(MID($A2,FIND(":",$A2)+3,1))>57,MID($A2,FIND(":",$A2)+1,3),MID($A2,FIND(":",$A2)+1,2)),RIGHT($A2,LEN($A2)-1-SUMPRODUCT(LEN($G2:$I2)))))))

复制代码

前排占楼。。。在G2单元格输入，下拉右拉。329字符。。。

1. =IF(COLUMN()=7,MID($A2,1,SUM(CODE(MID($A2,2,1))>57,1)),IF(COLUMN()=8,MID($A2,LEN($G2)+1,IFERROR(FIND(":",$A2),LEN($A2)+1)-LEN($G2)-1),IFERROR(IF(COLUMN()=9,MID($A2,FIND(":",$A2)+1,SUM(CODE(MID($A2,FIND(":",$A2)+3,1))>57,2)),MID($A2,SUMPRODUCT(LEN($G2:$I2))+2,3)),"")))

复制代码

前排占楼。。。在G2单元格输入，下拉右拉。268字符。。。

1. =IFERROR(CHOOSE(COLUMN()-6,LEFT($A2,SUM(1,MID($A2,2,1)>"9")),MID($A2,LEN($G2)+1,IFERROR(FIND(":",$A2)-LEN($G2)-1,3)),MID($A2,FIND(":",$A2)+1,SUM(MID($A2,FIND(":",$A2)+3,1)>"9",2)),MID($A2,SUMPRODUCT(LEN($G2:$I2))+2,3)),"")

复制代码

前排占楼。。。在G2单元格输入，下拉右拉。222字符。。。够二的。真的难以压缩啊

1. =IFERROR(MID($A2,CHOOSE(COLUMN()-6,1,LEN(F2)+1,FIND(":",$A2)+1,SUMPRODUCT(LEN(D2:F2))+2),CHOOSE(COLUMN()-6,SUM(MID($A2,2,1)>"9",1),IFERROR(FIND(":",$A2)-LEN(F2)-1,3),SUM(MID($A2,FIND(":",$A2)+3,1)>"9",2),3)),"")

复制代码

再接再厉，字符210。。。怎么就进不去180呢。。。

（不玩了）

liziyuliziyu7

=CHOOSE(IF(AND(ISERROR(FIND(":",$A2)),COLUMN(A1)>2),5,COLUMN(A1)),LEFT($A2,MATCH(,0/MID($A2,ROW($1:$9),1),)-1),ROW(INDIRECT($A2)),SUBSTITUTE(ADDRESS(1,MAX(COLUMN(INDIRECT($A2))),4),1,""),MAX(ROW(INDIRECT($A2))),"")

baksy

1.G2公式，横拉、下拉
=CHOOSE(COLUMN()-6,LEFT($A2,FIND($H2,$A2)-1),ROW(INDIRECT($A2)),SUBSTITUTE(MID($A2,LEN($G2&$H2)+2,6),$J2,),RIGHT(MID($A2,6,6),3))

2.分段公式,下拉
G2=LEFT(A2,FIND(H2,A2)-1)
H2=ROW(INDIRECT(A2))
I2=SUBSTITUTE(MID(A2,LEN(G2&H2)+2,6),J2,)
J2=RIGHT(MID(A2,6,6),3)

小福贵

先占个位： 155个字符
=IFERROR(TRIM(MID(SUBSTITUTE(CELL("address",INDIRECT(MID($A2,FIND(":",$A2&":")*(COLUMN(A1)>2)+1,99))),"$",REPT(" ",20)),(MOD(COLUMN(A1)+1,2)+1)*20,20)),"")

ddhhyy16

1. =TRIM(MID(REPLACE(TRIM(MID(SUBSTITUTE($A2,":",REPT(" ",9)),10^(INT(COLUMN()/9)),10)),MIN(FIND(ROW($1:$10)-1,TRIM(MID(SUBSTITUTE($A2,":",REPT(" ",9)),10^(INT(COLUMN()/9)),10))&"0123456789")),,REPT(" ",9)),1+MOD(COLUMN()-1,2)*9,9))

复制代码