虽然Excel不是专用的日历程序，但可以使用Excel创建和管理日历。你可以根据自己的喜好自定义各种日历模板，这比自己从头开始创建日历格式要快得多。还可以从电子表格中获取日历事件列表，并将其导入Out......
2023-05-29 0 excel公式,excel技巧,excel教程,Excel表格,Excel教程
excel图表怎么从列表中返回满足多个条件的数据
Excel教程
2023-05-29 22:26:24
在实际工作中，我们经常需要从某列返回数据，该数据对应于另一列满足一个或多个条件的数据中的最大值。
如下所示，需要返回指定序号（列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
标签： Excel函数 excel函数公式 excel表格制作 Excel教程 excel 图表 数据 ppt中制作统计图表 在ppt中制作统计图表的方法 条件 返回
相关文章
- 详细阅读
-
INDEX函数，给公式提供数组详细阅读
INDEX函数虽然可以生成数组，但如果不用数组公式，似乎只能返回其生成的数组中的第一个元素。然而，可以使用INDEX函数来给公式提供数组。例如： =SUM(INDEX(A1:A10,N(IF(1,{......
2023-05-29 0 Excel函数,excel函数公式,excel表格制作,Excel教程
-
Excel求2018年母亲节的日期详细阅读
如何使用公式求出2018年母亲节的日期？ 先不看答案，自已动手试一试。 公式思路 母亲节(Mother's Day)，是一个感谢母亲的节日，这个节日最早出现在古希腊；而现代的母亲节起源于美国，是......
2023-05-29 0 excel公式,excel技巧,excel教程,Excel表格,Excel教程
-
如何在Excel中比较数据详细阅读
本文将教你如何在Excel中比较不同的数据。这些数据既可以是同一电子表格中的两列数据，也可以是两个不同的Excel文件中的数据。 步骤 方法 1比较两列数据 选中空白列的第一个单元格。要比较同一......
2023-05-29 0 excel公式,Excel函数教程,excel技巧,excel教程,Excel表格,Excel教程
-
如何在 Excel 中显示隐藏的列详细阅读
用户有时候会隐藏 Microsoft Excel 中的列来使工作表显示的内容更加简洁，或者用来防止特定数据被更改或删除。但是这些隐藏的行和列都可以随时显示出来。按照下列步骤操作，你就可以学会在所有 E......
2023-05-29 0 excel公式,Excel函数教程,excel技巧,excel教程,Excel表格,Excel教程