Excel:从列中匹配的列中查找最小/最大值 [英] Excel: Find min/max values in a column among those matched from another column
问题描述
列A中的所有不同值都有一个单独的列表(列D)。
对于列D中的每个目标值,我想查找其列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
我想要有公式返回最后两列(最小和最大)。
注意:
-
即使在引用超出最后一行的范围(例如,在公式中使用
列A,B实际上将包含其他数据,标题等等,所以我想一些公式可能不适用于整个列的引用,如$ A $ 8:$ A $ 50
时,也可以使用某些功能,必须$ A $ 8:$ A $ 24
),以便在列A,B的底部添加新数据,并且所有内容都会自动更新。$ A: $ A
。
编辑一些类似/相关的帖子
它适用于我。
分钟:
= MIN(IF(($ A $ 1:$ A $ 50 = D1),($ B $ 1:$ B $ 50))
pre>
for max:
= MAX(IF(($ A $ 1:$ A $ 50 = D1),($ B $ 1:$ B $ 50))
注意,它是一个数组公式,所以你需要按 CTRL + SHIFT + ENTER
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,
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).
Notes:
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.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
.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
select min value in B column for same values in A columns excel?
Given value in column A, find min/max in column B in EXCEL
find max or min in excel, with conditions
解决方案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
这篇关于Excel:从列中匹配的列中查找最小/最大值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!