当前位置:首页 > 办公 > Excel教程 > 正文内容

excel图表怎么从列表中返回满足多个条件的数据

酷网3周前 (10-07)Excel教程19

在实际工作中,我们经常需要从某列返回数据,该数据对应于另一列满足一个或多个条件的数据中的最大值。

如下所示,需要返回指定序号(列A)的最新版本(列B)对应的日期(列C)。

解决方案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

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39457.html

分享给朋友:

相关文章

excel条件格式:高亮显示你要查看的商品

excel条件格式:高亮显示你要查看的商品

有朋友留言: “ 我的数据很多,里面有多种不同商品,核对数据费眼力,能不能实现我选哪种商品,哪种商品就高亮显示?” 示例结果如下: 其实这个方法很简单:条件格式。 方法实现 1、选中数据,【开始】——【条件格式】——【...

excel中怎么隐藏行?excel隐藏行,取消隐藏行

excel中怎么隐藏行?excel隐藏行,取消隐藏行

      隐藏你不需要的行和列可以使Excel表格更容易阅读,特别是当表格很大的时候。隐藏的行不会影响表格,但会影响公式。在日常的生活工作使用excel过程中,对于办公室小白来说,有时候对于excel表格中的...

excel表格跨表查询:查询指定顾客的购买记录

excel表格跨表查询:查询指定顾客的购买记录

今天一位朋友传来数据表,要求: 根据“购买记录表”中的记录,如下: 在“购买查询”表中实现查询指定顾客的购买记录,效果如下: 本问题,有两个关键点: 1、购买记录必须是依据指定的姓名展示的; 2、购买记录的序号必须是...

Excel中无法直接输入数字怎么办?

Excel中无法直接输入数字怎么办?

   对于Excel表格来说,最常用的就是存储数据了,无论是汉字还是数字都是主要数据。当我们Excel表格无法直接输入数字的时候,该怎么解决这个问题呢?下面就给大家介绍一下E...

使用SUMPRODUCT函数巧妙处理多条件判断

使用SUMPRODUCT函数巧妙处理多条件判断

下面是要使用公式解决的问题: 如果值处于0%-25%,则返回0;处于16%-50%,则返回0.1;处于51%-75%,则返回0.2;处于76%-100%,则返回0.3;大于100%则返回0.4。 如下所示,值为80%,返...

INDIRECT函数汇总各仓库的合计到销售总表

INDIRECT函数汇总各仓库的合计到销售总表

问题情境 如下样表: 分仓库数据表中,既有销售额明细,又有合计。 要求:只汇总各仓库的合计到“销售合计”表中。 公式实现 在销售合计”表B2单元格输入公式: =MAX(INDIRECT(A2&”!B...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。