INDEX+SMALL完成乾坤大挪移：满足条件的数据自动“跑到”其它工作表 _知识经验

问题来源
学习群里，有朋友提出的问题：
如下两个数据表，一个是“全部”工作表，一个是“已对”（已经核对）工作表，如何使得“全部”工作中，每核对一条，数据会自动从“全部”工作表“自动”跑到“已对”工作表，即：

文章插图
结果如下动图：

文章插图
公式实现
在A2单元格输入公式：
=INDEX(全部!A:A,SMALL(IF(全部!$F:$F=”已对”,ROW(全部!A:A),ROWS(A:A)),ROW(A1)))&””，以三键组合结束；
公式向下填充，填充到和“全部”数据表中的行数一致。

文章插图
公式解析
ROWS(A:A)：
整个工作表的行数，EXCEL2016默认工作表行数为1048576 。此处用工作表最大行数，使得不管数据有多少行，公式都能使用。
IF(全部!$F:$F=”已对”,ROW(全部!A:A),ROWS(A:A))：
用IF函数，建立一新的数组，这一新的数组建立的规则是：
如果“全部”工作表中F列单元格内容等于“已对”，则返回该单元格所在的行，否则返回整个工作表的行数。
所以：
如果“全部”工作表中第二行数据已经核对（标题行为第一行），“已对”工作表中A2单元格的公式，此部分返回的数组是：
{1048576;2;1048576;1048576;1048576;1048576;1048576;1048576;1048576;1048576;……}
如果“全部”工作表中第五行数据已经核对（标题行为第一行），“已对”工作表中A2单元格的公式，此部分返回的数组是：A3单元格的公式，此部分返回的数组是：
{1048576;1048576;1048576;1048576;5;1048576;1048576;1048576;1048576;1048576;……}
SMALL(IF(全部!$F:$F=”已对”,ROW(全部!A:A),ROWS(A:A)),ROW(A1))：
从上一步数组中取出的ROW(A1）小的数值。
ROW(A1)是一个动态的数值，公式往下填充一行，行数加1，即当公式在A3单元格时，是ROW(A2)，当公式填充到A4单元格是，是ROW(A3)，当到A5单元格时，是ROW(A3)……
这样，就在A2、A3、A4、A5……的数组中找到了第1、2、3、4小的值，即第一条、第二条、第三条、第四条……已经核对的数值。
$F:$F，F列绝对引用，因为不管公式向下、向右填充，是否核对都在F列。
INDEX(全部!A:A,SMALL(IF(全部!$F:$F=”已对”,ROW(全部!A:A),ROWS(A:A)),ROW(A1)))：
当公式在A2单元格时，返回第一条“已对”数据的A列的值，当公式在A3单元格时，返回第二条“已对”数据的A列的值……
因为公式中IF部分是数组计算，所以公式以三键组合结束。
公式向右填充，得到“已对”数据B列值……
INDEX(全部!A:A,SMALL(IF(全部!$F:$F=”已对”,ROW(全部!A:A),ROWS(A:A)),ROW(A1)))&””：
在最后加上&””，这一步是容错处理。用空单元格与空文本合并返回空文本的特性，将超出结果数量的部分不显示出来。
【INDEX+SMALL完成乾坤大挪移：满足条件的数据自动“跑到”其它工作表】公式虽然麻烦，但用于核对数据，绝对是一绝，试试吧！