excel如何从列表中返回满足多个条件的数据
时间:2023-04-12 04:41:52
在实际工作中,我们经常需要从某列返回数据,该数据对应于另一列满足一个或多个条件的数据中的最大值。
如下图1所示,需要返回指定序号(列A)的最新版本(列B)对应的日期(列C)。
![excel如何从列表中返回满足多个条件的数据](https://img.aspxhome.com/file/2023/6/a142756_0.jpg)
图1
解决方案1:
在单元格F2中输入数组公式:
=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=F1,B2:B10)),IF(A2:A10=F1,B2:B10),0))
注意这里有两个IF子句,不仅在生成参数lookup_value的值的构造中,也在生成参数lookup_array的值的构造中。千万不能忽略了这一要点,即如果采用以下简单方法:
=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=F1,B2:B10)),B2:B10,0))
尽管此公式构造仍可以返回正确的值,但完全不能保证所有情况下都正确。原因是与条件对应的最大值不是在B2:B10中,而是针对不同的序号。而且,如果该情况发生在希望返回的值之前行中,则MATCH函数显然不会返回我们想要的值。
可以将上面的公式解析来验证:
=INDEX(C2:C10,MATCH(MAX(IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE},{4;2;5;3;1;3;4;1;2})),B2:B10,0))
转换为:
=INDEX(C2:C10,MATCH(MAX({FALSE;FALSE;FALSE;FALSE;FALSE;3;4;1;2}),B2:B10,0))
转换为:
=INDEX(C2:C10,MATCH(4,B2:B10,0))
转换为:
=INDEX(C2:C10,MATCH(4,{4;2;5;3;1;3;4;1;2},0))
很显示,数组中的第一个满足条件的值并不是我们想要查找的值所在的位置:
=INDEX(C2:C10,1)
得到:
2013-2-21
这并不是满足我们的条件对应的值。
回到正确的公式:
=INDEX(C2:C10,MATCH(MAX(IF(A2:A10=F1,B2:B10)),IF(A2:A10=F1,B2:B10),0))
转换为:
=INDEX(C2:C10,MATCH(4,IF(A2:A10=F1,B2:B10),0))
转换为:
=INDEX(C2:C10,MATCH(4,IF({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE},{4;2;5;3;1;3;4;1;2}),0))
转换为:
=INDEX(C2:C10,MATCH(4,{FALSE;FALSE;FALSE;FALSE;FALSE;3;4;1;2},0))
这次,参数lookup_array的数组中出现的最大值4与条件指定的序号相关,公式转换为:
=INDEX(C2:C10,7)
结果为:
2014-10-7
解决方案2:
下面的公式更优雅:
=LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A10=F1)*B2:B10)),C2:C10)
先看看公式中的:
(A2:A10=F1)*B2:B10
转换为:
({12345;12345;12345;12345;12345;54321;54321;54321;54321}=54321)*B2:B10
转换为:
({FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE})*B2:B10
得到:
{0;0;0;0;0;3;4;1;2}
现在,来到公式中巧妙的部分。为了找到最大值在此数组中的位置(而不是像方案1一样使用MATCH(MAX,…等)组合,那需要重复生成上述数组的子句),进行如下操作:
我们首先给上面数组中的每个值添加一个小值。这是必需的,因为接下来将会对该数组中的值求倒数,如果不执行此操作,则数组中的零将导致#DIV / 0!错误,这会在将数组传递给FREQUENCY函数时使事情更复杂。
这样:
1/(1+(A2:A10=F1)*B2:B10)
转换为:
1/(1+{0;0;0;0;0;3;4;1;2})
得到:
{1;1;1;1;1;0.25;0.2;0.5;0.333333333333333}
这里的关键在于,将此数组作为bins_array参数的值传递给FREQUENCY函数,将零作为参数data_array的值。此时,公式构造:
FREQUENCY(0,1/(1+(A2:A10=F1)*B2:B10))
转换为:
FREQUENCY(0,{1;1;1;1;1;0.25;0.2;0.5;0.333333333333333})
求倒数之前数组中的最大值(即我们关注的值)为求倒数之后数组中的最小值。而且,如果我们传递一个所有值都在0到1之间的值数组作为FREQUENCY函数的参数bins_array的值,将0作为其参数data_array的值,那么零将被分配给参数bins_array中的最小值;其余的为空或为零。
由于数组中的最小值为0.2,在数组中的第7个位置,因此上述公式构造的结果为:
{0;0;0;0;0;0;1;0;0;0}
获得此数组后,我们只需要从列C中与该数组出现的非零条目(即1)相对应的位置返回数据即可。因此,公式:
=LOOKUP(1,0/FREQUENCY(0,1/(1+(A2:A10=F1)*B2:B10)),C2:C10)
转换为:
=LOOKUP(1,0/{0;0;0;0;0;0;1;0;0;0},C2:C10)
转换为:
=LOOKUP(1,{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!},C2:C10)
得到:
2014-10-7
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Win10版微软Cortana新增直接打开网站功能
![](https://img.aspxhome.com/file/2023/4/46604_0s.jpg)
Word文档中快速插入分隔线的技巧
![](https://img.aspxhome.com/file/2023/3/21893_0s.jpg)
word2010新建空白文档
![](https://img.aspxhome.com/file/2023/0/21380_0s.jpg)
excel表格中怎么生成二维码?
![](https://img.aspxhome.com/file/2023/8/41358_0s.jpg)
去除wrod水印的三种方法
Win10打印机提示0x000006b9错误代码怎么办?
![](https://img.aspxhome.com/file/2023/1/52341_0s.jpg)
excel如何设置固定表头?excel表头固定方法介绍
![](https://img.aspxhome.com/file/2023/5/42425_0s.jpg)
如何设置SmartArt图形文本等高字符和字符间距
![](https://img.aspxhome.com/file/2023/3/30083_0s.jpg)
联想电脑怎么把Win10系统改为win7系统?
![](https://img.aspxhome.com/file/2023/2/51762_0s.png)
利用word如何使彩色照片快速变为黑白照片?
![](https://img.aspxhome.com/file/2023/3/35863_0s.png)
Win10怎么关闭右下角的资讯和新闻功能?
![](https://img.aspxhome.com/file/2023/9/51269_0s.png)
excel减法运算函数使用方法
![](https://img.aspxhome.com/file/2023/2/39172_0s.gif)
理解VLOOKUP函数第4个参数的真实含义
![](https://img.aspxhome.com/file/2023/8/a142548_0s.png)
office 格式刷到底是刷什么
![](https://img.aspxhome.com/file/2023/1/16661_0s.jpg)
Excel2016图表底部怎么添加数据表格?
![](https://img.aspxhome.com/file/2023/9/a141569_0s.jpg)
Word2013怎么设置页码
在word中如何设置起始页码
如何在word2007显示或隐藏格式标记
![](https://img.aspxhome.com/file/2023/1/29341_0s.jpg)
怎样批量修改word文档名字(批量命名)
![](https://img.aspxhome.com/file/2023/6/19936_0s.png)
Word文档如何转成WPS文字
![](https://img.aspxhome.com/file/2023/0/31370_0s.jpg)