Question

在EXCEL 中 =INDEX(A:A,LOOKUP(9^9,SMALL((A$1:A$30="")/1%%+ROW($1:$30),ROW()))) 表示什么

能不能来个大神~帮我解释一下

尤其是LOOKUP(9^9,SMALL((A$1:A$30="")/1%%+ROW($1:$30),ROW() 这块。

Answer 1

这个公式的作用是提取A1到A30不为空的数据。
1、(A$1:A$30="")/1%%+ROW($1:$30)：其中1%%就是万分之1，(A$1:A$30="")/1%%就是将空的单元格用10000代替，不空的为0，组成一个数组，加上1到30的自然数数组生成一个新的数组；
2、SMALL((A$1:A$30="")/1%%+ROW($1:$30),ROW())：在上述数组中，用SMALL得到第ROW（）小的数；（实际上就是返回不为空的单元格对应的序号）
3、LOOKUP：返回不大于9^9的最后一个数，此公式中，它的作用是将一维数组转换为单一值，可以不用它，用SUMPRODUCT代替（SUMPRODUCT(SMALL((A$1:A$30="")/1%%+ROW($1:$30),ROW()))）；
4、最后用INDEX返回A1到A30不为空，第ROW（）个数据。

追问

那我如果想提取A一整列不为空的数据，这个公式能实现吗？ 应该修改哪里？

追答

需要修改一下公式：
=INDEX(A:A,LOOKUP(9^9,SMALL((A:A="")/1%%%%+ROW(A:A),ROW())))

追问

这个公式可以实现跨表提取吗？ 我是小白..谢谢大神。。

追答

在SHEET1中提取SHEET2中A列不为空的数据：
=INDEX(sheet2!A:A,LOOKUP(9^9,SMALL((sheet2!A:A="")/1%%%%+ROW(A:A),ROW())))

Answer 2

mark下，mark mark

Answer 3

　　公式的意义：提取出A1:A30区域中的非空数据。
　　但此公式最后的ROW()用得不好，不限定具体行号，公式输入位置不同，返回的值也不会相同，也就是说此公式必须输入在第一行中，才能提取出所有非空数据。所以此处，最好用ROW(A1)或ROW(1:1)，这样公式可输入在任何起始位置。
　　SMALL是取第几小函数，/1%%是除以万分之一，也就是乘以10000，这样用是个人习惯，事实上这里只有30行数据，直接*100即可，目的是如果单元格是空值，行号+100，非空，直接返回行号。不管公式中是用ROW()、还是ROW(A1)或ROW(1:1)，下拉时行号会增加的，SMALL函数根据增加的行号取出第几小的值。
　　LOOKUP函数是向后兼容性，返回最后一个小于等于查找值的值，9^9，是9的9次方，就是一个很大的数，因为SMALL取出的行号，就算加上10000，也只有1万多，远小于9^9，肯定小于它，返回的就是SMALL取出的值。
因为公式中用了(A$1:A$30="")这种数组数据，加LOOKUP就是为了取出单一值，不按数组公式的方式输入。等同于公式：

　　=INDEX(A:A,SMALL((A$1:A$30="")/1%%+ROW($1:$30),ROW()))，但此公式是数组公式，即同时按住Ctrl+Shift+Enter三键结束公式编辑，会自动生成一对｛｝，下拉。
　　或者直接用IF条件判断是为空值的公式：
　　=INDEX(A:A,SMALL(IF(A$1:A$30<>"",ROW($1:$30),4^8),ROW(A1)))，这也是数组公式，即同时按住Ctrl+Shift+Enter三键结束公式编辑，会自动生成一对｛｝，下拉。

追问

我想实现从表1里提取非空数据到表2里，顺序不变，可以实现吗？公式是怎样的。谢谢大神。

追答

=INDEX(Sheet1!A:A,SMALL(IF(Sheet1!A$1:A$1000"",ROW($1:$1000),4^8),ROW(1:1)))，数组公式，即同时按住Ctrl+Shift+Enter三键结束公式编辑，会自动生成一对｛｝，下拉。