excel函数获取与满足多个查找条件的所有值
发布时间:2022-10-20 20:01:45
excel函数获取与满足多个查找条件的所有值?如下图1所示,单元格区域A1:E25中存放着数据,列D中是要查找的值需满足的条件,列I和列J中显示查找到的结果,示例中显示的是1月份南区超市销售的蔬菜及其数量。
图1
要求在I2中输入公式,向右向下拖拉以获取全部满足条件的数据。
先不看答案,自已动手试一试。
公式
在单元格I2中输入数组公式:
=IF(COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2),””,INDEX(D:D,SMALL(IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(C1))))
向右向下拖拉至出现空单元格。
公式解析
公式中的:
COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2)
用来计算符合条件的结果数(本例中为5),并与已放置值的单元格数(已返回的值)相比较,以确定在单元格中输入相应的值还是输入空。
公式中的:
($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3
转换为:
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3
转换为:
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}+{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}+($C$2:$C$25=$G$3)=3
转换为:
{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}+{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}+{FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}=3
转换为:
{1;1;2;2;1;2;0;0;1;1;0;1;2;2;3;3;2;3;1;1;3;3;1;2}=3
数组中有5个3,表明有5条数据满足条件。得到:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}
代入IF语句中:
IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25))
转换为:
IF({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE},ROW($C$2:$C$25))
得到:
{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;16;17;FALSE;19;FALSE;FALSE;22;23;FALSE;FALSE}
代入SMALL语句中:
SMALL(IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(C1))
转换为:
SMALL({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;16;17;FALSE;19;FALSE;FALSE;22;23;FALSE;FALSE},1)
得到:
16
注意,当公式向下拖拉时,ROW(C1)将更新为ROW(C2)、ROW(C3)……,得到值2、3……等,从而可以获取相应位置的值。
代入INDEX语句中:
INDEX(D:D,SMALL(IF(($A$2:$A$25=$G$6)+($B$2:$B$25=$G$9)+($C$2:$C$25=$G$3)=3,ROW($C$2:$C$25)),ROW(C1)))
转换为:
INDEX(D:D,16)
得到单元格D16中的值:
土豆
由于COUNTIFS($A:$A,$G$6,$B:$B,$G$9,$C:$C,$G$3)<ROWS($I$2:I2)转换为:
5<1
结果为:
False
因此,该单元格中的公式返回的结果为:土豆
其余单元格公式转换原理可依此类推。
猜你喜欢
- EXCEL怎么制作作动图展示不同月份的费用占比?公司一个月份的不同费用作图很简单,那么,对于多个月份的费用变化,如何在一个图表中,通过控制月
- 剪贴板是Windows系统中自带一个非常好用的工具,通过这个小小的剪贴板,我们可以进行复制、粘贴、剪切操作,轻松做到传递和共享信息。那么,W
- 我们有时候使用word或者Excel的时候,会发现文件上面的功能区的菜单栏不见了,那么我们该如何解决这种问题呢?解决方法如下:1、在电脑上打
- 这篇教程是向脚本之家的朋友分享在excel中使用vba实现单元格间数据移动方法,教程比较基础,适合新手来学习,有需要的朋友可以参考本文,希望
- VLOOKUP函数的区间是按照给定的数值区间,查找某个数在哪个区间,并返回这个区间对应的值。等级评定是一种特殊的区间查找。字符的模糊查找,是
- 在Word2007中如何添加并改变封面样式操作步骤1、首先打开Word2007软件,单击“插入”按钮,在“页”区域选择“封面”选
- excel怎么使用vba创建一个预设格式的工作表?想要通过vba预设一个固定格式的工作表,该怎么设置呢?下面我们就来看看详细的设置教程,需要
- Designer2007怎么定位到背景图片的像素点?网页中的背景图片想要定位到指定的像素点,该怎么定位呢?下面我们就来分享三种教程,需要的朋
- word使用自动编号,需要在段落设置中,设置“编号”。方法步骤如下:1、打开需要操作的word文档,鼠标选中需要自动编号的文本段落。2、在“
- Word怎么制作公司出入的工作证?一般公司都有工作证,今天我们就来看看使用word机会只工作证的教程,过程很简单,感兴趣的朋友可以进来参考一
- 不少网友在使用极速Word这款办公软件制作表格的时候,都需要使用到划斜线这个操作,不过,不少网友都不知道怎么完成这个动作!那么,极速Word
- excel如何制作饼图?使用饼图来表现各个项目的占比情况最直观,之前我们也介绍过整个饼图的制作方法,今天我们来看看半个饼图怎么做,下面分享e
- 粘滞键是专为同时按下两个或多个键有困难的人设计的。连续按五次shift可以启用或禁用粘滞键。但是,如果我们不需要粘滞键,我们可以关闭粘滞键。
- 在Word2010文档中,用户可以根据文档需要为文本框设置纯颜色填充、渐变颜色填充、图片填充或纹理填充,使文本框更具表现力。在Word201
- win10系统是最主流的计算机使用系统。用户可以使用win10系统进行大量的工作或娱乐。强大的性能也使win10系统成为计算机系统中最主流的
- 为了更好地保护个人隐私,很多windows10系统用户都会给帐户设置密码。而如果想要更加保险的话,我们还可以在win10系统下设置BIOS密
- 在excel2010中怎么按行填充序列的步骤:新建一excel表格,在第一个单元格中输入1,选中要填充的行。 然后
- 很多朋友不是太清楚Word2016怎么添加目录?下面小编给大家带来Word2016添加目录教程,需要的朋友可以参考下如果你的文档有很多类别,
- 经常使用Excel 的朋友会遇到Excel 2003打开发送错误报告这一问题,这时该怎么办,不用急,今天小编就来给大家解决这个问题,下面就是
- 相信有很多用户在使用Win10系统的时候都接触过PIN码,这个PIN码是独立于WINDOWS账户密码的本地凭据,在一些特殊的情况就需要用户通