获取列A的每个不同值的列B的最大值 [英] Get the maximum values of column B per each distinct value of column A
问题描述
我有一张Excel电子表格:
A,B
X,1
X,5
Y,4
Y,11
X,7
Z,1
我想为列A的每个不同值获取列B的最大值 - 如何通过纯Excel公式执行?
期望的结果(订单无关):
A,B
Y,11
X,7
Z,1
换句话说,我想要Excel的SQL查询版本
SELECT A,max(B)
FROM myTable
GROUP BY A
任何版本的Excel都可以接受,但我使用365。
数据透视表是一种可以接受的方法(我目前使用的是自己的一个枢轴),但是,我强烈希望使用真正的公式 - 主要目标的问题是增强我对Excel中公式编程的理解。 >>
Gary的学生答案是正确的。我添加了一些东西。
此外,还有一个指向该方法的链接:
I have an Excel spreadsheet of the form:
A,B
X,1
X,5
Y,4
Y,11
X,7
Z,1
I would like to get the maximum value of column B for each distinct value of column A - how can I do it via pure Excel formula?
Desired result (order is irrelevant):
A,B
Y,11
X,7
Z,1
In other words, I would like Excel's version of an SQL query
SELECT A,max(B)
FROM myTable
GROUP BY A
Any version of Excel is acceptable, but I use 365 one.
Pivot tables are an acceptable approach (I currently did it with a pivot myself) but I would strongly prefer a real formula - the main goal of the question is to enhance my understanding of formula programming in Excel. No VBA
Gary's Student's answer is correct. I added a couple things.
Also, a link to the method: http://blog.contextures.com/archives/2011/07/27/finding-min-if-or-max-if-in-excel/
A distinct list of values can be generated in a couple ways, here's one:
And here's links to a method or two for distinct lists. All with array formulas:
Ignore Duplicates and Create New List of Unique Values in Excel
Getting unique values in Excel by using formulas only
这篇关于获取列A的每个不同值的列B的最大值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!