聚合函数(小)返回零而不是最小值 [英] Aggregate function (small) returns zeros rather than the smallest values

问题描述

我正在使用excel的聚合(小)函数来查找出现在列中的每个名称的最小值.问题在于，只要B中存在值，下面的公式就会简单地返回0.

I am using excel's aggregate (small) function to find the smallest value for each name that appears in a column. The issue is that the formula below simply returns 0s everywhere there is a value in B.

我正在使用的公式是

=IF($B2<>"", AGGREGATE(15,7, ($B:$B)*($A2=$A:$A)*($B2<>""), 1), "")

其中B包含我要从中获取最小值的数据，而A包含标识字符串.

where B contains the data I want the smallest value from and A contains identifying strings.

我感谢您可以提供的任何帮助！

I appreciate any help you can lend!

推荐答案

您要除以条件:

=IF($B2<>"", AGGREGATE(15,7, ($B:$B)/(($A2=$A:$A)*($B:$B<>"")), 1), "")

每当($ A2 = $ A:$ A)或($ B2<"")为 FALSE 时，它将返回 0 以及任何乘以 0 的值是 0 ，因此最小值是 0 .

Whenever ($A2=$A:$A) or ($B2<>"") is FALSE it will return 0 and anything multiplied by 0 is 0 and as such the smallest value is 0.

通过除以条件，我们抛出一个#DIV/0 错误，该错误在AGGREGATE的第二个条件中的 7 强制公式被忽略，因此，我们仅得到在两个布尔值中返回 TRUE 或 1 的最小值. 1 * 1 = 1 .

By dividing by the criteria we throw an #DIV/0 error which the 7 in the AGGREGATE's second criterion forces the formula to ignore and as such we only get the smallest of what returns TRUE or 1 in both Boolean. 1*1=1.

还有一件事.AGGREGATE是一个数组类型公式，因此将限制为仅数据将加快速度.

But one more thing. AGGREGATE is an array type formula so limiting the to only the data will speed it up.

=IF($B2<>"", AGGREGATE(15,7, ($B$1:INDEX($B:$B,MATCH("zzz",$A:$A)))/(($A2=$A$1:INDEX($A:$A,MATCH("zzz",$A:$A)))*($B$1:INDEX($B:$B,MATCH("zzz",$A:$A))<>"")), 1), "")

---

根据您的评论:

---

As per your comment:

=IF($B2 = AGGREGATE(15,7, ($B:$B)/(($A2=$A:$A)*($B:$B<>"")), 1),AGGREGATE(15,7, ($B:$B)/(($A2=$A:$A)*($B:$B<>"")), 1), "")

这篇关于聚合函数(小)返回零而不是最小值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！