vba 如何在excel中“分组”数据并找到组中的最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9012621/
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
How to 'group' data in excel and find the maximum in the group
提问by Sam
I have parent and child accounts that make a family. If the Parent_acct = Accnt_no then its parent. I need to find the maximum Term in the family and show as Family_term (this is to be calculated) for each account. What's the good way to do it? Sorry cannot post an image yet.
我有组成家庭的父子帐户。如果 Parent_acct = Accnt_no 那么它的父级。我需要找到家庭中的最大期限，并为每个帐户显示为 Family_term（这是要计算的）。有什么好办法呢？抱歉还不能发图。
Parent_acct_no Account_no Type Term Family_term(max)
565 565 Parent 45 78
565 256 Child 78 78
331 331 Parent 23 87
331 754 Child 87 87
331 823 Child 19 87
回答by vasek1
You can use an array formula of max combined with if. Assume the columns are A (Parent_Account_No) through E (Family term max), the formula in column E should be (starting with E2):
您可以将 max 的数组公式与 if 结合使用。假设列是 A（Parent_Account_No）到 E（Family term max），E 列中的公式应该是（从 E2 开始）：
{=max(if($A:$A=A2,$D:$D,FALSE))}
Make sure to press Ctrl+Shift+Enter when entering the formula to make it an array formula (see details here: http://www.cpearson.com/excel/ArrayFormulas.aspx)
确保在输入公式时按 Ctrl+Shift+Enter 使其成为数组公式（请参阅此处的详细信息：http: //www.cpearson.com/excel/ArrayFormulas.aspx）
回答by dgw
Lets assume the following columns:
让我们假设以下列：
A B C D E
Parent_acct_no Account_no Type Term Family_term(max)
And you want to sum up Family_termgrouped by each Type=Parentthen you could use the following formula in Column F
并且您想总结Family_term每个分组，Type=Parent然后您可以在 F 列中使用以下公式
(F2) =if(c2="Parent";sumif(A:A0;B2;E:E0);"")
If you name the column A (P_acc=A$2:A$500) and E (F_term=E$2:E$500) you can write
如果你命名列 A (P_acc=A$2:A$500) 和 E (F_term=E$2:E$500) 你可以写
(F2) =if(c2="Parent";sumif(P_acc;B2;F_term);"")
Since I don't have access to excel at home I could not test it tho.
由于我无法在家中使用 excel，因此我无法对其进行测试。
Hope that helps.
希望有帮助。
Edit: Corrected my denglish excel command (when->if) Thx.
编辑：更正了我的 denlish excel 命令 (when->if) Thx。
