Excel-根据条件查找第n个最大值 [英] Excel - find nth largest value based on criteria

问题描述

这是对该问题的一种扩展:

解决方案

首先，输入值:

这两个公式都是数组公式，请在仍位于菜单栏中的情况下，按 Ctrl + Shift + Enter 确认公式.公式栏

= MAX(SUMIFS($ F $ 2:$ F $ 6，$ A $ 2:$ A $ 6，$ I2，$ D $ 2:$ D $ 6，$ D $ 2:$ D $ 6))

这将在单元格 $ I2 中为国家/地区构建一个 SUMIFS()结果数组，然后每个产品名称将获取 MAX()结果.

以及产品名称:

= INDEX($ D $ 2:$ D $ 6，SMALL(IF(SUMIFS($ F $ 2:$ F $ 6，$ A $ 2:$ A $ 6，$ I2，$ D $ 2:$ D $ 6，$ D $ 2:$ D $ 6)= $ K2，ROW($ D $ 2:$ D $ 6)-1)，1)))

现在使用最大的 SUMIFS()结果，我们引用 SUMIFS()结果的列表并获取乘积行(偏移量为 INDEX())并检索最小的行号.

您可以将第一个公式中的 MAX()调整为 LARGE(，n)，其中n是第n大结果.

This is a kind of an extension to this problem: Excel - Sum values from the data set based on criteria

I have a table like this:

Country Region    Code  Name of product Year   Value
Sweden  Stockholm 52    Apple           1995   1000
Sweden  Malmö     25    Pancake         1991   1500
Sweden  Malmö     52    Apple           1992   2470
Finland Helsinki  21    Candy           1987   2500
Denmark Copenhagen 52   Apple           1987   2571

What I want to do is to make a code that can give me the sum of the nth largest value of products that have been sold in a specific country.

That is, if I want to get the highest value for products sold in Sweden it should return Apple and the sum of sold apples, 3470.

edit: The solution of Glitch_Doctor:

解决方案

Firstly, for the value:

Both formulas are array formulas, please confirm the formula with Ctrl+Shift+Enter while still in the formula bar

=MAX(SUMIFS($F$2:$F$6,$A$2:$A$6,$I2,$D$2:$D$6,$D$2:$D$6))

This builds an array of SUMIFS() results for the country in cell $I2 and each product name then grabs the MAX() result.

And the product name:

=INDEX($D$2:$D$6,SMALL(IF(SUMIFS($F$2:$F$6,$A$2:$A$6,$I2,$D$2:$D$6,$D$2:$D$6)=$K2,ROW($D$2:$D$6)-1),1))

Now using the max SUMIFS() result, we reference the list of SUMIFS() results and get the row of the product (offset to the start of the INDEX()) and retrieve the smallest row number.

You can adjust MAX() in the first formula to be LARGE(,n) where n is the nth largest result.

这篇关于Excel-根据条件查找第n个最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！