INDEX SMALL完成大动作:符合条件的数据自动“运行”到其他工作表

问题来源

在学习小组中，有朋友提出的问题：

下面两个数据表，一个是“全部”工作表另一个是“已检查”(checked)工作表，如何使数据自动从“全部”工作表运行到“已检查”工作表，即：

结果如下：

公式实现

在单元格A2中输入公式：

=INDEX (all！A:A，SMALL(IF (all！$F:$F="选中"，ROW(全部！A:A)、ROW(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;……}

如果“全部”工作表第五行数据已经勾选(表头行为第一行)，则此部分返回的数组为单元格A3的公式，此部分返回的数组为：

{1048576;1048576;1048576;1048576;5;1048576;1048576;1048576;1048576;1048576;……}

小(如果(全部！$F:$F="选中"，ROW(全部！A:A)、ROWS(A:A))、ROW(A1)):

上一步中从数组中获取的ROW(A1)的小值。

行(A1)是一个动态数值。公式向下填充一行，行数增加一，即公式在单元格A3时为ROW(A2)，公式在单元格A4填充时为row (a3)，到达单元格A5时为ROW(A3).

这样，在A2、A3、A4、A5的数组中找到第一个、第二个、第三个、第四个小值，即第一条、第二条、第三条、第四条的勾选值。

$F:$F，这在F列中是绝对引用的，因为公式是否被向下或向右填充是在F列中检查的。

INDEX(全部！A:A，SMALL(IF (all！$F:$F="选中"，ROW(全部！A:A)、ROWS(A:A))、ROW(A1))):

当公式在单元格A2中时，返回第一个“选中”数据的A列的值，当公式在单元格A3中时，返回第二个“选中”数据的A列的值.

因为公式的IF部分是数组计算，所以公式以三键组合结束。

将公式填充到右边，得到“匹配”数据的b列值.

INDEX(全部！A:A，SMALL(IF (all！$F:$F="选中"，ROW(全部！A:A)、ROWS(A:A))、ROW(A1)))”；

在最后加上“”，这一步就是容错处理。将空单元格与空文本相结合以返回空文本的功能不会显示超出结果数量的部分。

虽然公式比较麻烦，但是查数据绝对是必须的。试试看！