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

问题描述

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

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.

我的意图是在列值共享相同ID时对其求平均值.

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.

推荐答案

因此公式应为

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

请注意，如果points列中有任何文本值，它仍将返回结果(因为count大于零)-您可以改用

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)),""))

如果对于任何ID，混合使用文本行和数字行，则返回的结果将小于平均或平均公式.这是此方法的局限性.您不能在其中添加额外条件(该B列必须包含一个数字)，因为您需要countifs，而countifs对数组不友好.似乎仍然很奇怪，AFAIK countif和sumif是该系列中唯一的数组友好函数，而countifs，sumifs和averageif等不是数组友好的.

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屋！