社会财经
社会杂谈 政治军事 科学探索 商业财经
居家生活
百科大全 手工制作 亲子育儿 美食菜谱 居家装修 驾车宝典 安全知识 智能手机 保险理财
电脑办公
电脑设计 系统/上网/安全 优化/故障/维护 办公软硬件 电脑教程 图片素材 其它
健康养生
健康常识 健康饮食 养生保健 亚健康 两性健康 医药大全 疾病防治 母婴常识
学习教育
小学课堂 初中/中考 高中/高考 大学/成教 教师/教学 公务员考试 英语学习 作文大全 论文 范文大全 其它
资讯八卦
热点资讯 娱乐资讯 科技资讯 体育资讯 军事资讯 房产资讯 职场资讯 教育资讯 灵异事件 爆笑段子 奇闻趣事
兴趣爱好
影视曲 解梦 佛教 钓鱼 园艺 宠物 星座 游戏 风水 摄影 收藏 旅游/汽车 乐器 茶艺 书画 其它
时尚爱美
发型发艺 瘦身减肥 健身运动 美容护肤 化妆技巧 香水精油 整容整形 美体塑身 穿衣搭配 时尚资讯
情感心理
两性心理 解读男人 女性课堂 心理知识 情绪管理 情感文章 爱情攻略 励志成长
历史文化
文化杂谈 美文欣赏 中国历史 世界历史 野史秘闻 诗词古文 短篇小说 故事大全 原创文化 其它
首页电脑办公办公软硬件
      

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

时间:2018-01-11  热:0℃  分享:yoyo520

每天清晨六点,准时与您相约






问题来源

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

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

结果如下动图:


公式实现

在A2单元格输入公式:

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

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


公式解析

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)))&''

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


公式虽然麻烦,但用于核对数据,绝对是一绝,试试吧!


百度搜索“就爱阅读”,专业资料,生活学习,尽在就爱阅读网92to.com,您的在线图书馆!
标签: 乾坤大挪移
分享此文
猜你喜欢