数据源的表格数据行增加，公式正常；减少行，公式就错误了。

feiaoli

数据源的表格数据行增加，公式正常；减少行，公式就错误了。

数组公式：=INDEX(科目汇总!B:B,SMALL(IF(科目汇总!$F$1:$F$243=1,ROW($1:$243),999),ROW(B1)))&""

我不懂公式一位老师给我做的，通过我反复试验上面公式中绿色数字可以增减变化，而红色的数字只可以增加不可以减少。


求老师，如何把红色的数字做成可以增减变化？或者其他公式达到这个公式的效果？




非常感谢sqrall900 老师的帮助，完全达到了我的目的。收藏了，
新年快乐！！！！！！！！！

sqrall900

=INDEX(科目汇总!B:B,SMALL(IF(indirect("科目汇总!$F$1:$F$1000")=1,ROW(indirect("1:1000")),999),ROW(B1)))&""

wangjguo44

问题的症结不是红色绿色字体数字的增加或减少，关键是理解公式的含义，这里更要理解其中IF(科目汇总!$F$1:$F$243=1,ROW($1:$243),999)这一“节”的含义——
译成啰嗦点文字，这一段意思就是，如果科目汇总!$F$1:$F$243这一区域里有等于1的单元格，那么就取（或说保留）它们所在的行号（ROW($1:$243)），否则的话（不等于1的哪些单元格）就赋以一个（比区域所有行号都大的）行号999。
这样一来，配合SMALL 可以逐行提取符合条件（I科目汇总!$F$1:$F$243=1）的行号，并由 INDEX 引用返回科目汇总!B:B对应行号（单元格）里的数据，（提到最后，符合条件的全提完了，就提999行的数据了）

对上面公式意义能理解了，就可以得知：绿色数字最好是跟红色数字匹配（相等），若绿色数字比红色数字小，条件数据范围里符合条件的行号仍尚能全部匹配出来，但若绿色数字比红色数字大，条件范围超出红色数字，那超出部分有符合条件的行号还能提取得到吗？

wangjguo44

除了红绿两色的数字外，还有个正常颜色的数字999，在公式中取值也要注意，这个公式的思路往往是这样：不符合条件的单元格就赋给它一个空单元格的行号，让符合条件的数据提取完后，去提取那空单元格的数据（空），这时公式自动把空视作0（并显示为0），所以公式最后加个&""，把IMDEX引用结果（若是数值型）转为文本型外，还将那个空单元格的0，转成空（""）显示！
因此，这个数字，一般是远离条件区域范围，习惯上用的很多的是4^8（4的8次方=65536），是03版工作表最大行号（因为一般表格不会那么大），不应该用跟条件区域很近的行号（避免数据增加，发生干扰），更不用在条件区域范围内的行号，否则不符合条件时，却返回了区域里该行的数据（除非确认该行号的单元格是空单元格）

feiaoli

sqrall900 发表于 2017-1-23 19:46
=INDEX(科目汇总!B:B,SMALL(IF(indirect("科目汇总!$F$1:$F$1000")=1,ROW(indirect("1:1000")),999),ROW(B1 ...

非常感谢sqrall900 老师的帮助，完全达到了我的目的。收藏了，
新年快乐！！！！！！！！！

1. =INDEX(科目汇总!B:B,SMALL(IF(indirect("科目汇总!$F$1:$F$1000")=1,ROW(indirect("1:1000")),999),ROW(B1)))&""

复制代码

feiaoli

wangjguo44 发表于 2017-1-23 19:52
问题的症结不是红色绿色字体数字的增加或减少，关键是理解公式的含义，这里更要理解其中IF(科目汇总!$F$1:$ ...

谢谢老师的教诲，学到很多，最起码我是公式小白理解了这个公式的最基本的意思，再次感谢，新年快乐！