对于合并单元格对应值的提取

wangg913

再来一个。

1. =IFERROR(INDEX(B:B,MATCH(D3,A:A,)+MATCH("*",A$48:INDEX(A:A,1+MATCH(D3,A:A,)),)-1),B$48)

复制代码

出错就返回最后一个。

cleverzhzhf

总结帖，汇总了一下各位老师的公式，让我顿感神清气爽，在这里斗胆汇总并进行简要的函数解读：
袁勇老师和Piny版主都是通过函数方式，通过LOOKUP的特性，构建一个内存数组，将空白的地方填上上方的非空单元格的值，然后通过与目标条件的比较得到最下面的值的行号，进一步得到结果。大头版主的思路则是完全新颖的。

袁勇老师<第一式>：

1. =INDIRECT("B"&MAX((LOOKUP(ROW($A$3:$A$48),IF($A$3:$A$48<>"",ROW($A$3:$A$48)),$A$3:$A$48)=D3)*(ROW($A$3:$A$48))))

复制代码

数组公式，112个字符。
通过用IF($A$3:$A$48<>"",ROW($A$3:$A$48))，返回一串数字与FALSE的数组，通过LOOKUP的特性，构建成需要的内存数组，即：
{"A Zone";"A Zone";"A Zone";"A Zone";"A Zone";"B Zone";"B Zone";"B Zone";"B Zone";"B Zone";"B Zone";"B Zone";"C Zone";"C Zone";"C Zone";"C Zone";"C Zone";"D Zone";"D Zone";"D Zone";"D Zone";"D Zone";"D Zone";"E Zone";"E Zone";"E Zone";"F Zone";"F Zone";"F Zone";"F Zone";"F Zone";"F Zone";"F Zone";"F Zone";"F Zone";"G Zone";"G Zone";"G Zone";"G Zone";"G Zone";"H zone";"H zone";"H zone";"H zone";"H zone";"H zone"}
然后通过“{内存数组}=目标条件”的比较，返回符合条件的最大行号值，最后得到结果。
袁勇老师<第二式>：

1. =INDEX(B:B,MAX((LOOKUP(ROW($A$3:$A$48),IF($A$3:$A$48<>"",ROW($A$3:$A$48)),$A$3:$A$48)=D3)*(ROW($A$3:$A$48))))

复制代码

数组公式，109个字符。
和上面方法的思路同样，只是我的实际工作中需要跨工作簿引用，所以INDIRECT不是最好的选择，更改成了INDEX，同理，还可以换成OFFSET。
袁勇老师的这两个式子也还都有缩减12个字符的空间。

-----------------------------------------------------------------------------------------------------------

Piny版主：

1. =INDEX(B:B,MAX(IF(D3=LOOKUP(ROW($3:$48),ROW($3:$48)/(A$3:A$48<>""),A$3:A$48),ROW($3:$48))))

复制代码

数组公式，91个字符。
与袁勇老师不同之处在于，ROW($3:$48)/(A$3:A$48<>"")，利用BOOL函数的思想，返回一串数字与#DIV/0!的数组，然后通过LOOKUP构建需要的内存数组。

--------------------------------------------------------------------------------------------------------------

大头版主<第一式>：

1. =LOOKUP(COUNTA(A$3:INDEX(A:A,MATCH(D3,A:A,))),SUBTOTAL(3,OFFSET(A$3,,,ROW($1:$46),)),B$3:B$48)

复制代码

普通公式，94个字符。两处让我称妙：
第一处，以前一直没有深刻理解过，其实INDEX返回的结果是“引用”，可以直接加“:”和其他单元格形成区域；
第二处，妙用SUBTOTAL，COUNTA的参数是无法通过多维引用而得到一组数据，但是SUBTOTAL可以。
大头版主<第二式>：

1. =IFERROR(INDEX(B:B,MATCH(D3,A:A,)+MATCH("*",A$48:INDEX(A:A,1+MATCH(D3,A:A,)),)-1),B$48)

复制代码

普通函数，87字符。
MATCH("*",Reference,0)的妙用，这个是返回区域中查找到的第一个文本，忽略数字。之后靠单元格位置间的数字关系进行巧妙运算。
缺点，2003版本无法使用IFERROR函数，可以用ISERROR或ISNA代替，那样公式就会比较长。

-------------------------------------------------------------------------------------------------------------------

站在巨人的肩膀上，将老师们的思路优化一点点，我写成如下的式子：

1. =LOOKUP(,0/(LOOKUP(ROW($3:$48),ROW($3:$48)/(A$3:A$48<>""),A$3:A$48)=D3),B$3:B$48)

复制代码

数组公式，81个字符。目前看到的最短的式子哦~~{:soso_e182:}

piny

cleverzhzhf 发表于 2013-1-10 17:26
总结帖，汇总了一下各位老师的公式，让我顿感神清气爽，在这里斗胆汇总并进行简要的函数解读：
袁勇老师和 ...

ROW的$都可省略 因僅需比較相對大小即可

1. =LOOKUP(,0/(LOOKUP(ROW(3:48),ROW(3:48)/(A$3:A$48<>""),A$3:A$48)=D3),B$3:B$48)

复制代码

wangg913

看来还是传统公式最靠谱。
就本题目而言，可简化为：

1. =LOOKUP(D3,LOOKUP(ROW(3:48),ROW(3:48)/(A$3:A$48>0),A$3:A48),B$3:B48)

复制代码

cleverzhzhf

wangg913 发表于 2013-1-11 11:11
看来还是传统公式最靠谱。
就本题目而言，可简化为：

强！没有最短只有更短。
在这里，所有的文本都是大于数字的，所以可以用
A$3:A$48>0代替了A$3:A$48<>""，节省两个字符。
再次佩服版主的活用。

zengxp

太牛了，这些函数

七夕、

好题，感谢LZ总结，感谢老师们的解答。

sunshine00

cleverzhzhf 发表于 2013-1-10 15:35
高！！！
COUNTA(A$3:INDEX(A:A,MATCH(D3,A:A,)))这个用的妙，才明白过来，INDEX先是返回的Refence

原来如此，刚刚开始还一直没想明白

豆果劳子

学习了,谢谢

cleverzhzhf

最近又再看lookup，总是理解不透，把老帖子拿出来翻一翻。然后把各位老师的公式汇总一下。看到了越来越短。
突然有种看到人类进化过程的感觉。{:soso_e113:}