当前所在位置: 首页 > 儿童教育 > 正文

excel如何从列表中返回满足多个条件的数据

2023-01-26 餐饮美食网 【 字体:

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

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

图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

阅读全文
相关推荐

如何自制蛋挞

如何自制蛋挞
1、倒入低筋面、黄油和水,揉成面团状,藏一小时后取出,抹上黄油卷起,包上保鲜膜冷藏半小时。2、取出,将面团切成一厘米的面团,放入模具内,用力按压,中间稍薄,外缘要比模具高,将蛋挞液倒入蛋挞皮中,放入烤箱里烤30分钟即可。

肉火烧面怎么和面

肉火烧面怎么和面
1、首先用温水把酵母融开,加入面粉,用手把面粉揉成非常软的面团,可以放一点熬好的猪油在面团里面。2、然后放在温暖的地方,盖上保鲜膜发酵至2倍大,发好的面团用手插入不回缩,就说明面团发酵好了。

奶茶几分糖好喝

奶茶几分糖好喝
1、不同的人对奶茶的口感要求是不一样的,一般来说,奶茶有三分糖、五分糖、七分糖三种不同的口味。2、女生一般都喜欢喝甜一点的,而男生则喜欢喝不那么甜的,七分糖的奶茶最好喝。

过桥米线是哪里的?

过桥米线是哪里的?
过桥米线是来自云南省滇南地区的一种有名特色小吃。过桥米线最早是在清朝就已经出现,距离现在至少有一百多年的历史啦!起源于建水县东城外锁龙桥西侧的鸡市街头处,有建水的特产草芽、地椒作配料,风味独特而远近闻名。深受广大美食爱好者的喜欢,不少人为此专门长途跋涉,只为尝

面霜和乳液的区别

面霜和乳液的区别
面霜和乳液的区别,相较而言乳液的水分含量要比面霜高,乳液质地要轻薄一些,乳液主要作用是保湿,滋润可以隔离外界干燥的气候,面霜既可保湿,又可美白,还能抗衰老,乳液的吸收快一些,而面霜吸收比较慢一些,因为液体的吸收速度都比较快。

怎么摘隐形眼镜

怎么摘隐形眼镜
在摘隐形眼镜之前,先用洗手液将手清洗干净,以免将细菌带入眼睛内,对着镜子,用右手中指轻拉眼睛下眼睑,左手中指轻拉眼睛上眼睑,让黑色眼球暴露在空气中,用右手食指和拇指轻触镜片的两边缘部分,向中间推使镜片拱起,再用两手指轻轻捏出镜片即可。

粉饼和散粉的区别

粉饼和散粉的区别
粉饼是呈压缩固体状态,多呈圆形或者方形,散粉则是细腻的粉末状,粉饼遮瑕力会比较强一些,可以湿用做粉底,或者用来补妆,而散粉则是定妆的效果,粉饼通常用在底妆的第一步,而散粉通常用在底妆最后一步。

冷烫和热烫的区别

冷烫和热烫的区别
冷烫和热烫的区别:冷烫对头发的要求是要在保温状态下才能给卷有个好的效果,而热烫是在干和湿的情况下都行,热烫烫出来的头发比较自然明显而有弹力,冷烫的头发风干后基本看不出来,并且发质会有点干。

高品质香水如何鉴别?用三步就可以解决

高品质香水如何鉴别?用三步就可以解决
1、看色泽以天然香料调制而成的高级香水,都有它本来的颜色,且大都是琥珀色或褐色,看起来很像宝石,比如,从茉莉、玫瑰或水仙等天然鲜花中所萃取的精油都呈黄色、褐白或绿褐色;此外,香水中所添加的魅惑香气物性香料也是褐色,苔类中的橡树苔是绿色,从树根或树根脂类中萃取的

如何去除黑眼圈

如何去除黑眼圈
去除黑眼圈首先可以用热毛巾敷眼,然后再用冷毛巾敷,十分钟就能让黑眼圈淡化的不那么严重,其次将去壳后的鸡蛋用无菌纱布包裹住,敷于眼部轻轻转动,可以急救去除黑眼圈,另外抹完眼霜后,用双手顺时针按摩,可以促进血液循环消除黑眼圈。
本文Tag