vba Excel - 如何在组中获得最小值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17114443/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel - how to get min value in a group
提问by Raymond
I've simplified my example to be something like this:
我已将我的示例简化为如下所示：
ID Value MAX
Group1 2 6
Group1 4 6
Group1 6 6
Group2 1 3
Group2 3 3
Group3 7 8
Group3 4 8
Group3 2 8
Group3 8 8
Group4 1 3
Group4 2 3
Group4 3 3
Group5 7 7
The column 'MAX' has the results that I want
'MAX' 列有我想要的结果
My two part question is
我的两部分问题是
(1) What are some of the ways I can get the values for the 'Max' column?
(1) 有哪些方法可以获得“Max”列的值？
I am currently using a pivot table to help support this functionality, but users are complaining it is too slow and can make Excel non-responsive.
我目前正在使用数据透视表来帮助支持此功能，但用户抱怨它太慢并且会使 Excel 无响应。
I then tried to use array functions with the formula like this:
然后我尝试将数组函数与这样的公式一起使用：
=MAX(IF($A:$A=A12,$B:$B))
This works, but unfortunately, I learned that it doesn't stay current and I need some mechanisms to refresh the data... and users have said they don't want yet-another-button to refresh the data.
这是有效的，但不幸的是，我了解到它不会保持最新状态，我需要一些机制来刷新数据......并且用户表示他们不想要另一个按钮来刷新数据。
Are there any other functions I could use?
我可以使用其他任何功能吗？
Would you use VBA and some 'on-open, on-close' event? Or maybe do this all in VBA?
你会使用 VBA 和一些“开盘、关盘”事件吗？或者也许这一切都在 VBA 中完成？
(2) Assuming there is a nice formula to solve the above, I also have the requirement that my Value column is actually a date which could be empty, and my actual requirement is to get the minimum date in the group, ignoring any blanks. Any tips on the equation?!
(2) 假设有一个很好的公式来解决上述问题，我还要求我的值列实际上是一个可以为空的日期，而我的实际要求是获取组中的最小日期，忽略任何空格。关于等式的任何提示？！
Thanks.
谢谢。
采纳答案by Raymond
A few things... my first issue was that my existing spreadsheet was set to 'manual calculation' instead of 'automatic calculation'. (under menu Formulas | Calculation Options).
一些事情......我的第一个问题是我现有的电子表格被设置为“手动计算”而不是“自动计算”。（在菜单公式 | 计算选项下）。
Here is some sample code I use to add calculate the min date based on a 'grouping' from another column. (NOTE: my spreadsheet has about 1500 rows, and I do notice a slowdown when making changes to cells and having the formulas get updated)
这是我用来添加基于另一列“分组”计算最小日期的一些示例代码。（注意：我的电子表格大约有 1500 行，我确实注意到在更改单元格和更新公式时速度变慢了）
Sub AddFormulaToCalculateEarliestRevisedDate()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim identifierColumn As String
Dim identierRow As String
Dim identifierRange As String
Dim valueRange As String
Dim formulaColumn As String
Dim formulaRange As String
Dim myIdentifierRange As Range
Dim myFormulaRange As Range
Dim lastRow As String
lastRow = ActiveSheet.Range("C5000").End(xlUp).Row
identifierColumn = "B"
identifierRange = "B6:B" & lastRow
valueRange = "AP6:AP" & lastRow
formulaColumn = "CZ"
formulaRange = "CZ6:CZ" & lastRow
Set myIdentifierRange = ActiveSheet.Range(identifierRange)
Set myFormulaRange = ActiveSheet.Range(formulaRange)
' delete any existing any array formulas first! otherwise, get error
myFormulaRange.ClearContents
myFormulaRange.NumberFormat = "m/d/yyyy;;" ' notice the ;; to handle zero dates 1/0/1900 to be blank
' loop through each row and set the array formula
Dim identifierCell As String
Dim arrayFormula As String
Dim r As Range
For Each r In myIdentifierRange.Rows
' example: arrayFormula = {=MIN(IF($B:$B00=B6,$AP:$AP00))}
identifierCell = identifierColumn & r.Row
arrayFormula = "MAX(IF(" & identifierRange & "=" & identifierCell & "," & valueRange & "))"
Range(formulaColumn & r.Row).FormulaArray = "=" & arrayFormula
Next
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
回答by Gary's Student
In C2 enter the array formula:
在 C2 中输入数组公式：
=MAX(IF(A:A=A2,B:B))
and copy down.
并抄下来。
Array formulasmust be entered with Ctrl+ Shift+ Enterrather than just the Enterkey. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.
数组公式必须输入Ctrl+ Shift+ Enter，而不是仅仅的Enter关键。如果正确完成此操作，公式将显示在公式栏中，并带有花括号。
