INDIRECT练手题
本帖最后由 飞雨飘 于 2012-6-5 10:03 编辑公式需用到INDIRECT来返回
公式长度140字数加10经验,以上加8经验。
一个老题,别告我侵权.
另外没注意冒号前（:）项目非定长,即不一定是两个字符.
新加一点要求，一、公式可右拉下拉，公式内字符不变。二、如果不用IFERROR,空格内可出现错误返回。
本帖最后由 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字符。
好长哦=TEXT(SUBSTITUTE(IF(COUNTIF(OFFSET($A$1,SMALL(ISERR(FIND("汇总",$A$1:$A$99))*99+ROW($1:$99),ROW()-1),,5),C$1&"*"),INDIRECT("R"&SMALL(ISERR(FIND("汇总",$A$1:$A$99))*99+ROW($1:$99),ROW()-1)+1+COUNTIF($B2:B2,">""")&"C1",),""),C$1&":",),IF(COLUMN()=3,"m/d/yyyy",)) =SUBSTITUTE(INDIRECT("a"&N(MATCH("*"&C$1&"*",INDIRECT("a"&SMALL(IF($A$1:$A999=$A$1,ROW(A$1:A999),4^8),ROW(A1))&":a"&SMALL(IF($A$1:$A999=$A$1,ROW(A$1:A999),4^8),ROW(A2))),))+SMALL(IF($A$1:$A999=$A$1,ROW(A$1:A999),4^8),ROW(A1))-1),C$1&":","") 本帖最后由 rebornxldeng 于 2012-6-1 12:53 编辑
做不出来......{:251:}
什么时候能公布答案啊?最好配个讲解... ...
1.=SUBSTITUTE(INDIRECT("a"&SMALL(IF($A$1:$A$22="金算盘汇总",ROW($1:$22)),ROWS($1:1))+MATCH(C$1&"*",INDIRECT("a"&SMALL(IF($A$1:$A$22="金算盘汇总",ROW($1:$22)),ROWS($1:1))&":a"&SMALL(IF($A$1:$A$22=0,ROW($1:$22)),ROWS($1:1))),)-1),C$1&":","")
=SUBSTITUTE(INDIRECT("A"&MIN(IF(ISNUMBER(FIND(C$1,$A$1:$A$18))*(COUNTIF(OFFSET($A$1,,,ROW($1:$18)),$A$1)=ROW(A1)),ROW($1:$18),4^8))),C$1&":",)&"" 本帖最后由 魔魔 于 2012-6-3 10:18 编辑
不容错版，长度129：=MID(VLOOKUP(C$1&"*",INDIRECT(TEXT(SUM(SMALL(($A$1:$A19<>$A$1)*50+ROW($1:19),ROW()-{1,0})*{100,1}),"a0!:a00")),1,),LEN(C$1)+2,99)用iferror容错之后长度是139=MID(IFERROR(VLOOKUP(C$1&"*",INDIRECT(TEXT(SUM(SMALL(($A$1:$A19<>$A$1)*50+ROW($1:19),ROW()-{1,0})*{100,1}),"a0!:a00")),1,),),LEN(C$1)+2,99) 不容错129字符
=MID(VLOOKUP((ROW()-1)&INDIRECT("r1c",)&"*",COUNTIF(INDIRECT("a1:a"&ROW($1:$99)),$A$1)&$A$1:$A$99,1,),LEN(INDIRECT("r1c",))+3,99)
另一种VLOOKUP131字符
=MID(VLOOKUP(INDIRECT("r1c",)&"*",IF(COUNTIF(INDIRECT("a1:a"&ROW($1:$99)),$A$1)=ROW()-1,$A$1:$A$104),1,),LEN(INDIRECT("r1c",))+2,99)
容错后
=MID(IFERROR(VLOOKUP((ROW()-1)&INDIRECT("r1c",)&"*",COUNTIF(INDIRECT("a1:a"&ROW($1:$99)),$A$1)&$A$1:$A$99,1,),""),LEN(INDIRECT("r1c",))+3,99)
=MID(IFERROR(VLOOKUP(INDIRECT("r1c",)&"*",IF(COUNTIF(INDIRECT("a1:a"&ROW($1:$99)),$A$1)=ROW()-1,$A$1:$A$99),1,),""),LEN(INDIRECT("r1c",))+2,99)
2003容错可通过ISNUMBER解决，因出题主要是练习INDIRECT("r1c",)和COUNTIF(INDIRECT())所以不详述了。
另：外套不用VLOOKUP也可能用其它定位函数如NDEX($A:$A,MATCH()),INDIRECT("a"&MATCH())返回。也不是练题目的同时字数多了十几了字符，也不详述了。