查找列中特定行的最大值

问题描述

我有一个像这样的excel表:

I have an excel table like this:

我想在剪切X"列中找到"story3"(行5,9,13和....)的最大值.我的意思是我应该写一个公式来查找故事3s"的行，然后在剪切X"列中找到它们的最大值.

I want to find the maximum value of "story3" (rows 5,9,13 and ....) in the column "shear X". I mean I should write a formula which finds the row of the "story 3s" and then find the max value of them in the column "shear X"

推荐答案

取决于Excel的年龄，

Depending on the age of your Excel,

Office 365 Excel及更高版本使用MAXIFS:

Office 365 Excel and later use MAXIFS:

=MAXIFS(C:C,A:A,"story3")

Office 2010及更高版本，我们汇总:

Office 2010 and Later us AGGREGATE:

=AGGREGATE(14,7,C1:C100/(A1:A100="story3"),1)

此数组公式的早期版本:

Earlier versions this array formula:

=MAX(IF(A1:A100="story3",C1:C100))

作为数组公式，必须通过Ctrl-Shift-Enter确认.

Being an array formula it must be confirmed with Ctrl-Shift-Enter.

如果列C并不总是Shear X列，而您需要找到它，我们可以使用INDEX(MATCH())将正确的列返回上述公式:

If column C is not always the Shear X column and you need to find it we can use INDEX(MATCH()) to return the correct Column to the above formulas:

INDEX(A:H,0,MATCH("shear X",A2:H2,0))

所以:

=MAXIFS(INDEX(A:H,0,MATCH("shear X",2:2,0)),A:A,"story3")

=AGGREGATE(14,7,INDEX(1:100,0,MATCH("shear X",2:2,0))/(A1:A100="story3"),1)

=MAX(IF(A1:A100="story3",INDEX(1:100,0,MATCH("shear X",2:2,0))))

这篇关于查找列中特定行的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！