ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
楼主: cleverzhzhf

[分享] 对于合并单元格对应值的提取

[复制链接]

TA的精华主题

TA的得分主题

发表于 2013-1-10 16:02 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
再来一个。
  1. =IFERROR(INDEX(B:B,MATCH(D3,A:A,)+MATCH("*",A$48:INDEX(A:A,1+MATCH(D3,A:A,)),)-1),B$48)
复制代码
出错就返回最后一个。

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-1-10 17:26 | 显示全部楼层
本帖最后由 cleverzhzhf 于 2015-5-21 11:03 编辑

总结帖,汇总了一下各位老师的公式,让我顿感神清气爽,在这里斗胆汇总并进行简要的函数解读:
袁勇老师和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:}


评分

3

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-1-10 17:44 | 显示全部楼层
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)
复制代码

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2013-1-11 11:11 | 显示全部楼层
看来还是传统公式最靠谱。
就本题目而言,可简化为:
  1. =LOOKUP(D3,LOOKUP(ROW(3:48),ROW(3:48)/(A$3:A$48>0),A$3:A48),B$3:B48)
复制代码

评分

1

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-1-11 13:06 | 显示全部楼层
wangg913 发表于 2013-1-11 11:11
看来还是传统公式最靠谱。
就本题目而言,可简化为:

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

TA的精华主题

TA的得分主题

发表于 2013-1-11 13:21 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-1-11 13:41 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-1-11 14:09 | 显示全部楼层
cleverzhzhf 发表于 2013-1-10 15:35
高!!!
COUNTA(A$3:INDEX(A:A,MATCH(D3,A:A,)))这个用的妙,才明白过来,INDEX先是返回的Refence

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

TA的精华主题

TA的得分主题

发表于 2013-1-12 09:31 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-4-3 11:10 | 显示全部楼层
最近又再看lookup,总是理解不透,把老帖子拿出来翻一翻。然后把各位老师的公式汇总一下。看到了越来越短。
突然有种看到人类进化过程的感觉。{:soso_e113:}
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 18:12 , Processed in 0.051622 second(s), 8 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表