本帖最后由 doggie 于 2012-6-1 22:39 编辑
练习一下
一开始想出来的:
- =MID(INDIRECT("A"&MATCH(ROW()*10+COLUMN()-20,COUNTIF(OFFSET($A$1,,,ROW($1:$17)-1),"")*10+IF($A$1:$A$17="",COLUMN(),MATCH(LEFT($A$1:$A$17,2),$A$1:$H$1,)),)),4,9)
复制代码
优化了一下的:
- =MID(INDIRECT("A"&MATCH(1,IF($A$1:$A$17="",1,LEFT($A$1:$A$17,2)=C$1)*(COUNTIF(OFFSET($A$1,,,ROW($1:$17)-1),"")=(ROW()-2)),)),4,9)
复制代码 对点评修改一下:
=SUBSTITUTE(INDIRECT("A"&MATCH(1,IF($A$1:$A$17="",1,FIND(T(INDIRECT("R1C"&COLUMN(),)),$A$1:$A$17))*(COUNTIF(INDIRECT("A1:A"&ROW($1:$17)-1),"")=(ROW()-2)),)),T(INDIRECT("R1C"&COLUMN(),))&":",) 再优化一下:
=IFERROR(SUBSTITUTE(LOOKUP(2,FIND(T(INDIRECT("R1C"&COLUMN(),)),INDIRECT("A1:A"&SMALL(IF($A$1:$A$18=0,ROW($1:$18)),SUM(ROW()-1)))),$A$1:$A$18),T(INDIRECT("R1C"&COLUMN(),))&":",),"")
如不加公式字符不可变这一点,公式最少可以为129字符。
|