在与另一列匹配的列中找到最小/最大值。

I have a table with two columns, say A:B. I have a separate list (in column D) of all different values in column A. For each target value in column D, I want to find, among all rows whose col A matches the target, the minimum and maximum values in column B. For example, if data is as shown,

我有一个有两列的表，a:B。我在列a中有一个单独的列a(列D)，列a中的每个目标值，我想要找到，在所有的列中，其col a匹配目标，b列的最小值和最大值，如数据如所示，

    col A   col B        col D
 1  7.5     1.00         7.5      1.00   1.20
 2  7.5     1.04         8        1.08   1.45
 3  7.5     1.08         8.5      1.17   1.83
 4  7.5     1.15
 5  7.5     1.20
 6  8       1.08
 7  8       1.13
 8  8       1.20
 9  8       1.29
10  8       1.38
11  8       1.43
12  8       1.45
13  8.5     1.17
14  8.5     1.22
15  8.5     1.26
16  8.5     1.35
17  8.5     1.42
18  8.5     1.51
19  8.5     1.58
20  8.5     1.64
21  8.5     1.69
22  8.5     1.74
23  8.5     1.79
24  8.5     1.83

I want to have formulas that return the last two columns (min and max).

我希望得到返回后两列(min和max)的公式。

Notes:

注:

1. It would be convenient to have something that works even when referring to ranges going beyond the last row (e.g., using $A$8:$A$50 in formulas, not necessarily $A$8:$A$24), so that new data can be added at the bottom of columns A,B and everything gets updated automatically.

   即使是指在最后一行之外的范围(例如，使用$8:$ 50的公式，也不一定是$8:$ $24)，这样就很方便了，这样新的数据就可以在A、B列的底部添加，并且所有的东西都会自动更新。

2. Columns A,B will actually contain other data, headers, etc., so I guess some formulas may not work with references to whole columns like $A:$A.

   列A,B实际上包含其他数据，标题等等，所以我想一些公式可能不适用于对整个列的引用，比如$A:$A。

EDIT: I have just found a few similar/related posts

编辑:我刚刚发现了一些类似的/相关的帖子。

Find MIN/MAX date in a range if it matches criteria of other columns

如果它匹配其他列的标准，可以在一个范围内找到最小/最大日期。

Conditional Min and Max in Excel 2010

有条件的Min和Max在Excel 2010。

select min value in B column for same values in A columns excel?

在B列中为相同的值在列中选择最小值?

Given value in column A, find min/max in column B in EXCEL

在A列中给定值，在EXCEL中找到B列中的最小/最大值。

find max or min in excel, with conditions

在excel中找到max或min，有条件。

4 个解决方案

#1

It works for me.

它适合我。

for min:

最小值:

=MIN(IF(($A$1:$A$50=D1),($B$1:$B$50)))

for max:

马克斯:

=MAX(IF(($A$1:$A$50=D1),($B$1:$B$50)))

Note, that it is an array formulas, so you need to press CTRL+SHIFT+ENTER

注意，它是一个数组公式，所以需要按CTRL+SHIFT+ENTER。

#2

You can use array formulas to give you the answers you need.

您可以使用数组公式来给出所需的答案。

For the min you can use the formula in cell E1:

对于min，可以使用cell E1中的公式:

{=MIN(IF($A:$A=D1,$B:$B))}

and the max the formula for cell F1 is :

细胞F1的公式是:

{=MAX(IF($A:$A=D1,$B:$B))}

To enter an array formula, you should enter everything except the braces (the curly brackets) then press the Ctrl and Shift keys when you press the enter key... this will add the braces and the formula will be considered an array formula.

要输入一个数组公式，你应该输入所有的东西，除了括号(大括号)，然后按下Ctrl键和Shift键，然后按下enter键…这将增加括号，公式将被视为一个数组公式。

Once entered, you can copy the formula down for the other matched values

输入后，可以将公式复制到其他匹配的值。

Array formulas work by calculating every combination. It will calculate if the value in A1 is the same as D1, and if it is it will give the value of B1, then if the value of A2 is the same as D1 it will give the value of B2, and so on. This will give you a list (or array) of values from the B column where the value in A is a match. The MIN/MAX is then calculated as normal.

数组公式计算每一个组合。它会计算A1的值是否等于D1，如果它是B1，那么如果A2的值等于D1它会给出B2的值，等等。这将给出B列的值列表(或数组)，其中a的值是匹配的。然后将最小/最大值计算为正常。

#3

The INDEX function can help you avoid CSE by building a standard formula using some math to either zero or make astronomical any non-matching values depending upon whether you are looking for a MAX or MIN result.

索引函数可以通过构建一个标准公式来帮助您避免CSE，该公式使用一些数学方法为零，或者使天文数字不匹配，这取决于您是在寻找最大还是最小的结果。

The pseudo-MAXIF formula is a little easier so I'll start there.

伪maxif公式比较简单，我从这里开始。

=MAX(INDEX(B:B*(A:A=D1), , ))

Excel treats any boolean TRUE statement as 1 and any FALSE as 0 when used mathematically. Multiplying a value in column B by 1 leaves the value unchanged; multiplying by 0 will result in zero. The INDEX function passes an array of unchanged values and zeroes into the MAX function depending upon whether that match the criteria or not. The result will be the maximum value from column B where column A is equal to the criteria.

Excel将任何布尔TRUE语句作为1，并且在数学上使用任何FALSE作为0。将a列的值乘以1使其值不变;乘以0会得到0。索引函数根据是否符合标准，将一组未更改的值和零值传递给最大函数。结果将是B列的最大值，其中A列等于标准。

The pseudo-MINIF formula essentially flips the process around by mathematically excluding any non-matching value, leaving only matching values from which to choose a MIN from.

伪minif公式从数学上剔除了任何不匹配的值，从而将过程翻转过来，只留下匹配的值，从中选择最小值。

=MIN(INDEX(B:B+(A:A<>D1)*1E+99, , ))

Again, TRUE is 1 and FALSE is 0 but this time we are using it to add 1E+99 (a 1 followed by 99 zeroes which isn't going to be the MIN of anything) to any non-matching values. Matching values will have 0 × 1E+99 added to them which equates to zero and will not change their value.

同样，TRUE是1，而FALSE是0，但是这次我们使用它来添加1E+99(1后面是99个0，这不会是任何东西的最小值)，也就是任何非匹配的值。匹配的值将0×1 e + 99添加到相当于零,不会改变他们的价值。

             

             

The full column cell range references I've used do not negatively impact calculation lag any more than a similar array formula would.

我所使用的全列单元格范围引用不会比类似的数组公式更影响计算延迟。

#4

You can have the references calculate themselves, assuming there are no gaps in the data, using named ranges.
e.g.

您可以使用引用计算自己，假设数据中没有空白，使用命名范围。如。

ARange =OFFSET($A$2,0,0,COUNT($A:$A))
BRange =OFFSET($B$2,0,0,COUNT($A:$A))

不等=抵消($ 2美元,0,0,计数(A:A)美元)BRange =抵消(B 2美元,0,0,计数(答:美元))

(I use the same COUNT in both to ensure the areas are the same size)

(我用同样的计数来确保面积大小相同)

Now I can use an array formula =MAX((ARange=D2)*(BRange)) to get the max (and same for min).
Array Formulas are entered with CTRL+SHIFT+Enter
See @Simoco's answer for the correct formula

现在我可以使用一个数组公式=MAX((ARange=D2)*(BRange))得到最大值(和最小值)。按CTRL+SHIFT+Enter键输入数组公式，请参见@Simoco对正确公式的回答。