对Excel表中数据一对多查询的方法
时间:2022-06-07 13:55:16
对Excel表中数据一对多查询的方法
举个例子,如下图,左侧A1:C10是一份学员名单表,现在需要根据F1单元格的“EH图班”这个指定的条件,在F2:F10单元格区域中,提取该班级全部学员名单。
今天说一个函数查询方面的方法:Index+Small。
F2单元格输入以下数组公式,按住Ctrl+Shift键不放,再按回车键,然后向下填充:
=INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1))),"")
公式讲解
IF(A$1:A$10=F$1,ROW($1:$10),4^8)
这部分,先判断A1:A10的值是否等于F1,如果相等,则返回A列班级相对应的行号,否则返回4^8,也就是65536,一般情况下,工作表到这个位置就没有数据了。
结果得到一个内存数组:
{65536;2;3;65536;65536;65536;65536;8;65536;10}
SMALL函数对IF函数的结果进行取数,随着公式的向下填充,依次提取第1、2、3……n个最小值,由此依次得到符合班级条件的行号。
随后使用INDEX函数,以SMALL函数返回的行号作为索引值,在B列中提取出对应的姓名结果。
当SMALL函数所得到的结果为65536时,意味着符合条件的行号已经被取之殆尽了,此时INDEX函数也随之返回B65536单元格的引用,结果是一个无意义的0,为了避免这个问题,可以在公式后面加上一个小尾巴 &""
利用&””的方法,很巧妙的规避了无意义0值的出现,只是当查找结果为数值或日期时,这个方法会把数值转变为文本值,并不利于数据的准确呈现以及再次统计分析。
练手题
最后留下一道练手题,如下图,根据A1:C10区域的数据,将E列相关班级的姓名,填充到F2:I5区域。
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Win10电脑怎么进入VGA模式?Win10进入VGA模式方法教程
![](https://img.aspxhome.com/file/2023/2/53012_0s.jpg)
如何对Word表格中的数据计算?
![](https://img.aspxhome.com/file/2023/8/33958_0s.gif)
如何在excel2013中添加文本型数据
![](https://img.aspxhome.com/file/2023/6/a141406_0s.jpg)
如何在word2007显示或隐藏格式标记
![](https://img.aspxhome.com/file/2023/1/29341_0s.jpg)
Word文档不能打印单击打印无反应怎么办?
![](https://img.aspxhome.com/file/2023/8/28178_0s.jpg)
Win10系统怎么删除更新提醒GWX.EXE?
![](https://img.aspxhome.com/file/2023/3/48693_0s.jpg)
Excel2016中设置默认工作表数量的方法
![](https://img.aspxhome.com/file/2023/7/40857_0s.jpg)
Excel如何搞定图片基本处理
![](https://img.aspxhome.com/file/2023/2/36252_0s.jpg)
excel表格怎么选中部分内容?excel表格选中部分内容方法
![](https://img.aspxhome.com/file/2023/8/39808_0s.jpg)
在excel中自动求和的方法步骤详解
如何使用Excel2013中推荐的数据透视表
![](https://img.aspxhome.com/file/2023/9/37599_0s.jpg)
罗技驱动无法定位程序输入点怎么解决?
![](https://img.aspxhome.com/file/2023/0/53240_0s.jpg)
Win10本地账户怎么改Microsoft账户?
![](https://img.aspxhome.com/file/2023/8/52308_0s.png)
如何使用office2010制作公章 office2010制作公章实例教程
![](https://img.aspxhome.com/file/2023/4/16184_0s.jpg)
Win10在不考虑更换硬件设备的前提下如何提升性能提升呢?
![](https://img.aspxhome.com/file/2023/8/50888_0s.jpg)
Publisher2010量度单位怎么更改为英寸?
![](https://img.aspxhome.com/file/2023/5/15115_0s.png)
Excel筛选多行多列不重复数据的正确方法
在Excel2003中如何设置页眉、页脚?
![](https://img.aspxhome.com/file/2023/4/40974_0s.jpg)
在 word 里打字带圈的数字怎么打?
Excel中两列数据对比如何找到相同的并自动填充
![](https://img.aspxhome.com/file/2023/6/36566_0s.jpg)