INDEX+SMALL完成乾坤大挪移:满足条件的数据自动“跑到”其它工作表
时间:2023-04-20 17:31:52
学习群里,有朋友提出的问题:
如下两个数据表,一个是“全部”工作表,一个是“已对”(已经核对)工作表,如何使得“全部”工作中,每核对一条,数据会自动从“全部”工作表“自动”跑到“已对”工作表,即:
结果如下动图:
公式实现
在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)))&””:
在最后加上&””,这一步是容错处理。用空单元格与空文本合并返回空文本的特性,将超出结果数量的部分不显示出来。
公式虽然麻烦,但用于核对数据,绝对是一绝,试试吧!
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
笔记本电脑找不到WiFi网络怎么回事?
![](https://img.aspxhome.com/file/2023/1/a280813_0s.jpg)
飞飞一键重装怎么装xp系统_飞飞一键重装xp系统图文教程
![](https://img.aspxhome.com/file/2023/2/a288936_0s.jpg)
咚漫漫画忘记密码怎么解决?咚漫漫画忘记密码解决方法
![](https://img.aspxhome.com/file/2023/8/a336700_0s.jpg)
如何使用Paw Mac构建并发送您的请求
![](https://img.aspxhome.com/file/2023/0/a213230_0s.png)
Win11热点连接成功但没网?Win11移动热点和网络冲突的解决方法
![](https://img.aspxhome.com/file/2023/28/a239600_0s.jpg)
wps office批注怎么删除?
![](https://img.aspxhome.com/file/2023/6/a168226_0s.jpg)
WPS制作自定义模板方法教程
苹果双系统Win10键盘灯不亮怎么办 苹果双系统Win10键盘灯不亮解决方法
![](https://img.aspxhome.com/file/2023/25/a218339_0s.png)
如何打开Mac电脑资源库?打开Mac电脑资源库的方法
![](https://img.aspxhome.com/file/2023/5/a203085_0s.jpeg)
电脑中如何设置WPS取消拼写检查去掉文字下方的波浪线
![](https://img.aspxhome.com/file/2023/5/a163905_0s.jpg)
WPS2007拼音指南的应用
英雄联盟手游和端游数据互通吗?英雄联盟手游和端游可以联机吗?
![](https://img.aspxhome.com/file/2023/6/a324020_0s.jpg)
PRISMSTA.EXE - PRISMSTA是什么进程 有什么用
如何查看Win10专业版的版本号?查看Win10专业版版本号的方法
![](https://img.aspxhome.com/file/2023/26/a223629_0s.png)
Win7旗舰版桌面文件名变蓝色怎么解决?
![](https://img.aspxhome.com/file/2023/26/a228931_0s.png)
按键精灵如何设置循环?按键精灵设置循环的方法步骤
![](https://img.aspxhome.com/file/2023/7/a332510_0s.jpg)
ppt怎么设计圆形立体的按钮?
![](https://img.aspxhome.com/file/2023/10/a345967_0s.jpg)
我的电脑图标不见了怎么找回来?
![](https://img.aspxhome.com/file/2023/2/a291674_0s.png)
Excel表格的35招必学秘技
![](https://img.aspxhome.com/file/2023/6/39326_0s.jpg)
Win7旗舰版怎么删除开机启动项?
![](https://img.aspxhome.com/file/2023/26/a229119_0s.png)