对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区域。
猜你喜欢
- 我们在使用电脑的过程中,难免遇到故障蓝屏的情况,比如0x0000003b蓝屏,很多用户都不知道该如何解决?那么今天小编带来的就是0x0000
- 今天为大家分享Word自动编号到10以后间距与之前不一致的两种处理方法,对于碰到这类问题却不知道怎么解决的朋友可以参考本文,希望能对大家有所
- 我们经常会使用WPS进行办公,久而久之我们存储的文件就会越来越多,文件过多时我们就可能会忘记文件的保存位置。这个时候我们可以使用WPS中的历
- 最近有用户反映Win10 21H1系统在访问共享文件的时候突然被提示不能访问了,系统弹出的提示是“因为您组织的安全策略阻止未经身份验证的来宾
- excel直方图怎么做?直方图可以将数据更简单的呈现出来,并且制作简单。不过有很多朋友还不会做或者是做的不好,所以需要学习一下直方图的制作教
- 在OFFICE EXCEL 2003中,对于表格较大的文件,在多次调用的过程中,会出现以下问题“可用资源不足,EXCEL无法完成此任务,请少
- 1.打开Excel表格选中需要对齐的单元格内容,右键选择“设置单元格格式” 2.在设置单元格格式里选择“对齐”接着
- Excel怎么修改图表坐标轴的颜色?excel表格中的数据制作成了图表,想要修改图表中坐标轴的颜色,该怎么修改呢?下面我们就来看看详细的教程
- 人民币符号相信大家都见过,可能大家见到的是多种表现方式的人民币符号,如RMB、CNY、¥等均表示人民币符号,那么word人民币符号怎么打?下
- 我们在 word 排版中经常会遇到一些不常用到的特殊符号,而这些特殊符号用普通的方法是无法输入的,这样就使得很多人在 word 编辑排版时不
- 如何开启win10系统远程桌面协功能?自打win10系统首个年度更新(Build 10.1.14393)以来包含了一个非常有用的工具—微软快
- Excel表格怎么打印批注?1.打开需要打印批注的Excel工作簿,点击工具栏的“页面布局”→“打印标题”。Excel表格2.进入页面设置后
- 最近很多Win10专业版的用户发现电脑耳机麦克风打不开,而现如今无论是台式电脑还是笔记本电脑都会带有麦克风的功能,这也为我们提供了很多方便,
- 一说起做表,也许你会觉得EXCEL、ET等电子表格做着最好。其实这也是相对的,如简历表或审批表,那还是用WORD或WPS好些。那么下面就由小
- Win10电脑显卡驱动安装失败怎么办?显卡是所有电脑中必备的一个硬件设备,而显卡驱动就是帮助显卡正常运行的一款软件,而有的小伙伴最近在使用电
- word中的表格有时候提前做好了,但忘记在表格上方插入标题。而表格上方有没有段落标记,怎么办?步骤:第一步:在表格下方的段落标记处写好标题第
- 在Word2010文档中插入图片以后,如果原始图片发生了变化,用户需要向Word2010文档中重新插入该图片。借助Word2010提供的“插
- 说起word文档的安全性,人们想到的恐怕就是设置打开和修改权限密码。其实,在实际的应用中,我们需要更加周密的保护。例如禁止别人对原文档的格式
- 假设原数据在A列,见图一,1、选中A列原数据,转到“数据”工具栏,点击分列见图一2、在跳出的“文本分列向导”第一步中,选中:分隔符号,点击:
- Excel表格怎么利用数据有效性控制文字长度?excel表格中我们可以控制表格输入的字符数量,具体该怎么控制呢?下面我们就来看看详细的教程,