索引/匹配，如果大于 [英] Index/match if greater than

问题描述

希望有人能帮助我解决索引/匹配公式的问题.

Hoping someone can help me with my trouble with an index/match formula.

B 列包含名称列表，C 列包含采取行动(销售)的频率，而 D 列包含平均销售额.

Column B has a list of names, column C has the frequency of an action taken (sales made) whilst column D has the average sales value.

我在 G 列中创建了 6 个表现出色的最高销售额的轨迹:

I've created in column G a track of the 6 highest sales which has worked great:

=大($D$8:$D$13, 2)

=LARGE($D$8:$D$13, 2)

然后我使用 F 列来确定与每个销售平均值匹配的名称:

Then I've used column F to determine the name that matches each sales average:

=INDEX($B$8:$B$13, MATCH(G4, $D$8:$D$13, 0))

=INDEX($B$8:$B$13, MATCH(G4, $D$8:$D$13, 0))

到目前为止一切顺利！但是，如果该个人的销售额超过 3 次，我只想包括平均销售额.IE;C 列中的值 >3.

So far so good! However, I'd like to only include the sales average if that individual has had more than 3 sales. IE; the value in column C is >3.

有人可以提供帮助或建议吗?

Can any one provide help or suggestions please?

推荐答案

你可以使用 一个数组公式:

=LARGE(IF(range=criteria,values),n)

对于您的情况:

=LARGE(IF($C$8:$C$13>3,$D$8:$D$13), 2)

按 CTRL+SHIFT+ENTER 输入数组公式.

press CTRL+SHIFT+ENTER to enter an array formula.

这篇关于索引/匹配，如果大于的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！