如何在 ARRAYFORMULA 中使用 AVERAGEIFS [英] How to use AVERAGEIFS within ARRAYFORMULA

问题描述

我试图在 ARRAYFORMULA 中使用 AVERAGEIFS.查看其他问题，我得出的结论是，不使用 QUERY 函数是不可能的.

我的目的是对共享相同 ID 的列的值求平均值.

我认为

请注意，如果点列中有任何文本值，这仍会返回结果(因为计数将大于零) - 您可以改为使用

=ArrayFormula(if(isnumber(B2:B),(sumif(A2:A,A2:A,B2:B)/countif(A2:A,A2:A)),""))

如果您将任何 ID 的行与文本和数字的行混合在一起，这将返回比平均值或平均公式更小的结果.这是这种方法的局限性.您不能添加额外的条件(B 列必须包含一个数字)，因为您需要 countifs，而 countifs 不是数组友好的.AFAIK countif 和 sumif 是这个家族中唯一对数组友好的函数，而 countifs、sumifs、averageif 等不是.

I am trying to use AVERAGEIFS inside ARRAYFORMULA. Looking at other questions, I have come to the conclusion that it is not possible without using QUERY function.

My intention is to average the values of a column whenever they share the same ID.

I think this question comes pretty close to what I need, but I haven't been able to replicate and adapt its solution on my own sheet.

In this sheet I show the result I expect (I got it by dragging the formula). I've also reviewed the Query Language Reference, unsuccessfully.

Thanks a lot for your time and effort.

解决方案

So the formula should be

=ArrayFormula(iferror(sumif(A2:A,A2:A,B2:B)/countif(A2:A,A2:A)))

Note that if there were any text values in the points column, this would still return a result (because count would be greater than zero) - you could instead use

=ArrayFormula(if(isnumber(B2:B),(sumif(A2:A,A2:A,B2:B)/countif(A2:A,A2:A)),""))

If you had a mixture of rows with text and rows with numbers for any ID, this would return a smaller result than the avg or average formula. This is a limitation of this method. You can't put an extra condition in (that column B has to contain a number) because you would need countifs and countifs isn't array-friendly. It still seems strange that AFAIK countif and sumif are the only functions out of this family that are array-friendly while countifs, sumifs, averageif etc. are not.

这篇关于如何在 ARRAYFORMULA 中使用 AVERAGEIFS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！