ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[分享] INDEX(区域,SMALL(IF(条件,行号数组,4^8),ROW(A1)))区域大小及IF第三参数的选择问题

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2011-9-14 00:10 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:数组公式
本帖最后由 hjj0451 于 2012-3-23 23:28 编辑


关于模式化公式INDEX(区域,SMALL(IF(条件,行号数组,4^8),ROW(A1)))区域大小及IF第三参数的选择问题


如上,这个公式用来取得满足条件的多个值已成为模式化了,被各位板油广泛运用,但我在使用中以及看到各位新板友在使用该类型的过程中,发现如下问题,贴出来供大家思考,也请各位老师指导:

假设,有公式:
  1. =INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20),4^8),ROW(A1)))&""
复制代码
,各位板友发现什么问题没有,就我看来有如下问题:
此公式的两个地方没有发挥应有的作用:
1.IF第三个参数4^8
2.&""
此公式没有容错,还要加上IF(ISERROR来取得不显示错误值的效果,不如省略IF第三参数和&"",和上面的效果一样的话,不如写成下式更简洁:

  1. =INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20)),ROW(A1)))
复制代码

但以上写法均难取得满意的效果,正确的写法是:
假设源数据放在$A$1:$A$20,那写成:
  1. =INDEX($A$1:$A$21,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20),21),ROW(A1)))&""
复制代码

减少嵌套层数不用IF的写法是:
  1. =INDEX($A$1:$A$21,SMALL(($B$1:$B$20<>C$1)*(21-ROW($1:$20))+ROW($1:$20),ROW(A1)))&""
复制代码
  1. =INDEX($A$1:$A$21,SMALL(($B$1:$B$20=C$1)*(ROW($1:$20)-21)+21,ROW(A1)))&""
复制代码
用TEXT来代替IF的写法是:
  1. =INDEX($A$1:$A$21,SMALL(--TEXT(($B$1:$B$20=C$1)*ROW($1:$20),"[=]21"),ROW(A1)))&""
复制代码
套用4^8的写法那就是:
  1. =INDEX($A:$A,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20),4^8),ROW(A1)))&""
复制代码
或者:
  1. =INDEX($A:$A,SMALL(($B$1:$B$20<>C$1)/1%+ROW($1:$20),ROW(A1)))&""
复制代码
4^8的用法在2003里也就整列引用时才能使用,否则就是多此一举了。

上面的公式的ROW(A1)常用ROW(1:1)代替,前者得到常数1,后者得到常量数组{1},下拉都能取得相同的效果。前者少1字符,后者多一字符。但后者安全性更高一点,前者在删除A1单元格或包含A1的单元格区域时都会发生错误,后者在删除第一行整行时发生错误。

总结上面的写法,也就是查找区域比源数据区域大,且IF第三参数的选择比数据区域所在的最大行号要大并且小于等于查找区域的行数时,加上&"",才能取得下拉N行后,当单元格数量超过实际查询得到的结果数量时,公式显示空文本而不是0零值的效果,且不会出现错误值。

关于&"",若INDEX需要返回的结果是文本,才适合加上&"".若返回的结果是数值类型,加上&"",将会导致你后面的运算要加上类似于减负的运算符号才能正常计算,因此在不加&""时在工具选项里取消勾选零值显示也不失为最好的方案。

举的简单例子在10楼。


评分

14

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-9-14 00:11 | 显示全部楼层
占个楼,关于SMALL第二参数ROW(A1)有点东西还要补充:

TA的精华主题

TA的得分主题

发表于 2011-9-14 00:28 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
当年第一次见到4^8及&""的用法后,的确眼前一亮。呵呵。。。

另:那个$B$1:$B$20=C1中的C1应该写成C$1吧?在行方向上使用绝对引用,这样才能得到正确的答案。

关于ROW(A1),楼上要补充什么呢?好期待。

我觉得Row(A1)换成Row(1:1)比较好,避免删除单元格后引起错误。

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-9-14 00:31 | 显示全部楼层
ggsmart 发表于 2011-9-14 00:28
当年第一次见到4^8及&""的用法后,的确眼前一亮。呵呵。。。

另:那个$B$1:$B$20=C1中的C1应该写成C$1吧 ...

谢谢SMART老师,你的建议很好,关于ROW(A1)的想法我明天准备补充。晚安

点评

晚安,我是向你学习  发表于 2011-9-14 00:43

TA的精华主题

TA的得分主题

发表于 2011-9-14 04:47 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-9-30 09:40 | 显示全部楼层
谢谢,楼上各位老师,让我学习了很多,终于明白:index+small+if

TA的精华主题

TA的得分主题

发表于 2011-9-30 09:48 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
楼主辛苦了。

TA的精华主题

TA的得分主题

发表于 2011-10-2 18:49 | 显示全部楼层
第二次又搜到此贴,对INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20)),ROW(A1)))
深有同感

TA的精华主题

TA的得分主题

发表于 2011-10-2 20:16 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2011-10-2 20:33 | 显示全部楼层
陈王宇翔 发表于 2011-10-2 19:16
老师能否举例说明,谢谢。

例子.rar (3.36 KB, 下载次数: 5229)
临时写的简单例子,希望对你有启发。

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-6-19 08:35 , Processed in 0.041077 second(s), 16 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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