欢迎光临
我们一直在努力

excel:使满足条件的数据自动“跑到”其它工作表

问题来源

学习群里,有朋友提出的问题:

如下两个数据表,一个是“全部”工作表,一个是“已对”(已经核对)工作表,如何使得“全部”工作中,每核对一条,数据会自动从“全部”工作表“自动”跑到“已对”工作表,即:

INDEX+SMALL乾坤大挪移:满足条件的数据自动“跑到”其它工作表

 

结果如下动图:

INDEX+SMALL乾坤大挪移:满足条件的数据自动“跑到”其它工作表

 

 

公式实现

在A2单元格输入公式:

=INDEX(全部!A:A,SMALL(IF(全部!$F:$F="已对",ROW(全部!A:A),ROWS(A:A)),ROW(A1)))&"",以<Ctrl+Shift+Enter>三键组合结束;

公式向下填充,填充到和“全部”数据表中的行数一致。

INDEX+SMALL乾坤大挪移:满足条件的数据自动“跑到”其它工作表

 

 

公式解析

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部分是数组计算,所以公式以<Ctrl+Shift+Enter>三键组合结束。

公式向右填充,得到“已对”数据B列值……

INDEX(全部!A:A,SMALL(IF(全部!$F:$F="已对",ROW(全部!A:A),ROWS(A:A)),ROW(A1)))&"":

在最后加上&"",这一步是容错处理。用空单元格与空文本合并返回空文本的特性,将超出结果数量的部分不显示出来。

 收藏 (0) 打赏

您可以选择一种方式赞助本站

支付宝扫一扫赞助

微信钱包扫描赞助

未经允许不得转载:英协网 » excel:使满足条件的数据自动“跑到”其它工作表

分享到: 生成海报
avatar

热门文章

  • 评论 抢沙发

    • QQ号
    • 昵称 (必填)
    • 邮箱 (必填)
    • 网址

    登录

    忘记密码 ?

    切换登录

    注册

    我们将发送一封验证邮件至你的邮箱, 请正确填写以完成账号注册和激活