根据 A 列的每个不同值获取 B 列的最大值 [英] Get the maximum values of column B per each distinct value of column A
问题描述
我有一个表格形式的 Excel 电子表格:
A,BX,1X,5Y,4Y,11X,7Z,1
我想为 A 列的每个不同值获取 B 列的最大值 - 如何通过纯 Excel 公式来实现?
想要的结果(顺序无关):
A,BY,11X,7Z,1
换句话说,我想要 SQL 查询的 Excel 版本
SELECT A,max(B)从我的表按 A 分组
任何版本的 Excel 都可以接受,但我使用的是 365 版本.
数据透视表是一种可以接受的方法(我目前自己用数据透视表做了)但是我非常喜欢一个真正的公式 - 这个问题的主要目标是增强我对公式编程的理解优秀.没有 VBA
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屋!