|
|
本帖最后由 hjj0451 于 2012-3-23 23:28 编辑
关于模式化公式INDEX(区域，SMALL(IF(条件,行号数组，4^8),ROW(A1)))区域大小及IF第三参数的选择问题
如上，这个公式用来取得满足条件的多个值已成为模式化了，被各位板油广泛运用，但我在使用中以及看到各位新板友在使用该类型的过程中，发现如下问题，贴出来供大家思考，也请各位老师指导：
假设，有公式:- =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第三参数和&""，和上面的效果一样的话，不如写成下式更简洁：
- =INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20)),ROW(A1)))
但以上写法均难取得满意的效果，正确的写法是：
假设源数据放在$A$1:$A$20，那写成：
- =INDEX($A$1:$A$21,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20),21),ROW(A1)))&""
减少嵌套层数不用IF的写法是：- =INDEX($A$1:$A$21,SMALL(($B$1:$B$20<>C$1)*(21-ROW($1:$20))+ROW($1:$20),ROW(A1)))&""
- =INDEX($A$1:$A$21,SMALL(($B$1:$B$20=C$1)*(ROW($1:$20)-21)+21,ROW(A1)))&""
- =INDEX($A$1:$A$21,SMALL(--TEXT(($B$1:$B$20=C$1)*ROW($1:$20),"[=]21"),ROW(A1)))&""
- =INDEX($A:$A,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20),4^8),ROW(A1)))&""
- =INDEX($A:$A,SMALL(($B$1:$B$20<>C$1)/1%+ROW($1:$20),ROW(A1)))&""
上面的公式的ROW(A1)常用ROW(1:1)代替，前者得到常数1，后者得到常量数组{1}，下拉都能取得相同的效果。前者少1字符，后者多一字符。但后者安全性更高一点，前者在删除A1单元格或包含A1的单元格区域时都会发生错误，后者在删除第一行整行时发生错误。
总结上面的写法，也就是查找区域比源数据区域大，且IF第三参数的选择比数据区域所在的最大行号要大并且小于等于查找区域的行数时，加上&""，才能取得下拉N行后，当单元格数量超过实际查询得到的结果数量时，公式显示空文本而不是0零值的效果，且不会出现错误值。
关于&"",若INDEX需要返回的结果是文本，才适合加上&"".若返回的结果是数值类型，加上&""，将会导致你后面的运算要加上类似于减负的运算符号才能正常计算，因此在不加&""时在工具选项里取消勾选零值显示也不失为最好的方案。
举的简单例子在10楼。
|
评分
-
14
查看全部评分
-
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2011-9-14 00:10
楼主
发表于 2011-9-14 00:28
