I have an Excel spreadsheet of the form:
我有一个表格的Excel电子表格:
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?
我想要得到A列每一个不同值的B列的最大值—我如何通过纯Excel公式来实现它?
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
换句话说,我想要Excel版本的SQL查询
SELECT A,max(B)
FROM myTable
GROUP BY A
Any version of Excel is acceptable, but I use 365 one.
任何版本的Excel都可以接受,但是我用的是365 1。
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
数据透视表是一种可以接受的方法(我目前使用的是数据透视表),但我强烈希望使用真正的公式——问题的主要目标是增强我对Excel中公式编程的理解。没有VBA
4 个解决方案
#1
5
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/
另外,该方法的链接:http://blog.context . archives/2011/07/27/finding- minf -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
忽略重复,在Excel中创建唯一值的新列表
Getting unique values in Excel by using formulas only
只使用公式在Excel中获取唯一值
#2
2
With data in columns A and B use the Array Formula:
A和B列的数据使用数组公式:
=MAX(IF(A1:A6="x",B1:B6))
Same for "y" and "z"
y和z也是一样
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.
数组公式必须与Ctrl + Shift + Enter一起输入,而不仅仅是Enter键。
Notice the braces in the Formula Bar
注意公式栏中的括号
EDIT#1:
编辑# 1:
To generate the formulas automatically, first copy column A to column C.
要自动生成公式,首先将A列复制到C列。
Then use the Remove Duplicate feature in the Data tab:
然后在Data选项卡中使用Remove Duplicate特性:
Then enter the Array Formula in cell D1:
然后在单元格D1中输入数组公式:
=MAX(IF(A$1:A$14=C1,B$1:B$14))
and copy down:
和复制:
The formula is only entered once and then copied down!
公式只输入一次,然后复制下来!
#3
1
Pivot table is the correct answer.
数据透视表是正确的答案。
- Select column A's title and drag it to the Row Labels section.
- 选择列A的标题并将其拖到行标签部分。
- Select column B's title and drag it to the Values section.
- 选择列B的标题并将其拖到Values部分。
- Click the arrow on column B's title in the values section and choose Value Field Settings.
- 单击“值”部分中的“B”列的标题,并选择Value字段设置。
- Select Max
- 选择马克斯
#4
1
Try below. Note I moved your desired output table to Columns D and E. You will not need to hard-code any values into formulas.
试试下面。注意,我将您希望的输出表移动到列D和e。您不需要将任何值硬编码到公式中。
Data
数据
D E
Y 11
X 7
Z 1
Formulas
公式
E2 Formula: ={MAX(--($D1=A1:A6)*B1:B6)}
E3 Formula: ={MAX(--($D2=A2:A7)*B2:B7)}
E4 Formula: ={MAX(--($D3=A3:A8)*B3:B8)}
#1
5
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/
另外,该方法的链接:http://blog.context . archives/2011/07/27/finding- minf -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
忽略重复,在Excel中创建唯一值的新列表
Getting unique values in Excel by using formulas only
只使用公式在Excel中获取唯一值
#2
2
With data in columns A and B use the Array Formula:
A和B列的数据使用数组公式:
=MAX(IF(A1:A6="x",B1:B6))
Same for "y" and "z"
y和z也是一样
Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.
数组公式必须与Ctrl + Shift + Enter一起输入,而不仅仅是Enter键。
Notice the braces in the Formula Bar
注意公式栏中的括号
EDIT#1:
编辑# 1:
To generate the formulas automatically, first copy column A to column C.
要自动生成公式,首先将A列复制到C列。
Then use the Remove Duplicate feature in the Data tab:
然后在Data选项卡中使用Remove Duplicate特性:
Then enter the Array Formula in cell D1:
然后在单元格D1中输入数组公式:
=MAX(IF(A$1:A$14=C1,B$1:B$14))
and copy down:
和复制:
The formula is only entered once and then copied down!
公式只输入一次,然后复制下来!
#3
1
Pivot table is the correct answer.
数据透视表是正确的答案。
- Select column A's title and drag it to the Row Labels section.
- 选择列A的标题并将其拖到行标签部分。
- Select column B's title and drag it to the Values section.
- 选择列B的标题并将其拖到Values部分。
- Click the arrow on column B's title in the values section and choose Value Field Settings.
- 单击“值”部分中的“B”列的标题,并选择Value字段设置。
- Select Max
- 选择马克斯
#4
1
Try below. Note I moved your desired output table to Columns D and E. You will not need to hard-code any values into formulas.
试试下面。注意,我将您希望的输出表移动到列D和e。您不需要将任何值硬编码到公式中。
Data
数据
D E
Y 11
X 7
Z 1
Formulas
公式
E2 Formula: ={MAX(--($D1=A1:A6)*B1:B6)}
E3 Formula: ={MAX(--($D2=A2:A7)*B2:B7)}
E4 Formula: ={MAX(--($D3=A3:A8)*B3:B8)}