excel图表怎么从列表中返回满足多个条件的数据
发布时间:2023-07-08 01:01:11
在实际工作中,我们经常需要从某列返回数据,该数据对应于另一列满足一个或多个条件的数据中的最大值。
如下所示,需要返回指定序号(列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
猜你喜欢
- 看过前面一系列文章的朋友,一定会熟悉“重新定义数组维度”的概念。这是一项非常有用且非常重要的技术,使我们可以接受二维数组并将其转换为一维数组
- 想要改win10系统的账户名称本应是轻松愉快中进行完,但是有些用户的账户改名却是异常坎坷。让小编都忍不住想去帮他解决了,那来看看到底要怎么修
- ①打开Excel表格,在功能选区中选择“审阅”—“保护工作表”。 ②在弹出的“保护工作表”对话框中进行取消保护所需
- 在公司、学校,经常会遇到需要在Excel录入身份证号的场景,但录入完毕会发现身份证号成了科学统计法,再次点击发现最后三位成了“0”,这个如何
- Word图表怎么设置自动生成目录?Word文章中图片很多,想要给图片排序,该怎么办呢?下面我们就来看看Word图表中生成目录的教程,需要的朋
- wps 邮件合并在软件上边工具栏后面,具体操作方法是:1、打开WPS表格,创建原始数据;2、打开WPS,切换至“引用”选项卡,点击“邮件合并
- 不少小伙伴在使用Win10系统玩游戏的时候总会弹出打字输入框,尤其是FPS游戏中的Shift键常是加速功能,但是经常会跳出输入法打字,那么碰
- 4月下旬,微软发布了更新win10补丁KB4493436。更新内容包括:通过HTTPS协议托管页面,解决Internetexplorer阻止
- 在我们平时使用Excel表格办公时,经常会碰到一些数字加汉字的组合,为了使用方便我们往往会选择拆分这些组合字符串。那么这个功能应该如何实现呢
- 如果不再需要在Word文档中显示智能标记,用户可以将其删除,操作步骤如下所述:第1步,打开Word2007文档窗口,依次单击Office按钮
- 由于有时会会在window服务器上部署服务什么的。这里会记录一下常见的疑难杂症修改hosts配置文件不起作用有时会需要做一些假域名的映射,在
- 电脑自带的大多是word2003版。那么如何用word2003打开docx格式文档呢?对于新手来说熟练运用Word软件还是有一定难度,怎么办
- 在Word中使用表格的功能,有时候简直就是一种折磨。相对Excel,毕竟Word更专业于图文混排。在Word中使用表格的时候,经常遇见的问题
- 升级Windows 10后,每次开机都会有弹窗提示SQL软件错误?别担心,这只是因为SQL服务器软件在Windows 10尚存在兼容问题:当
- 在写论文的过程中,编辑、打印文档等,正文一般都需要页眉和页脚,那么大家知道怎么在word中设置页眉页脚吗?下面给大家介绍如何从第二页设置页眉
- Word2007具有多个文档窗口并排查看的功能,通过多窗口并排查看,可以对不同窗口中的内容进行比较。Word2007具有多个文档窗口并排查看
- 为让利消费者,提供更优惠的服务,某大型收费停车场规划调整收费标准,拟从原来“不足15分钟按15分钟收费”调整为“不足15分钟部分不收费”的收
- 在使用电脑工作时常会发生一些异常情况,导致文件无法响应、死机等,如果你正在编辑文件却没能及时保存怎么办,今天小编为大家介绍一下如何在exce
- 不少人平时使用的是办公电脑,经常会有其他人借用,为了防止不必要的错误,会想要对开机问候语上进行备注说明,那么要如何更改系统的开机问候语呢?其
- Excel表格怎么快速计算公司员工的累积加班工时?很多都是都会根据需要加班,当然加班会有加班费用,该怎么计算员工累计的加班时长呢?下面我们就