如何在 Arrayformula 中使用 Averageifs 或 Sumifs [英] How to use Averageifs or Sumifs in an Arrayformula

问题描述

我会在这篇文章的底部发布一个指向电子表格的链接.

我正在尝试计算介于某个日期范围之间的一列数字的平均值.下面的公式有效，但我必须将它拖到列中，我希望它是一个数组，以便它自动更新.

=iferror(averageifs(B$2:B,A$2:A,">="&C2,A$2:A,"<="&D2),1)

所以我将它创建为一个数组，如下所示:

 =iferror(ArrayFormula(averageifs(B$2:B,A$2:A,">="&C2:C,A$2:A,"<="&D2:D)),1

但它在第一个单元格之后停止.所以我将平均函数分解为 sum &计数以划分.

计数数组使用以下公式工作:

=iferror(ArrayFormula(countifs(A$2:A,">="&C2:C,A$2:A,"<="&D2:D)),1)

但是 sum 数组甚至没有经过第一个单元格:

=iferror(ArrayFormula(SUMIFS(B$2:B,A$2:A,">="&C2:C,A$2:A,"<="&D2:D)),1)

组合越过第一个单元格，但计算结果都为零:

=ArrayFormula(iferror(SUMIFS(B$2:B,A$2:A,">="&C2:C,A$2:A,"<="&D2:D)/countifs(A$2:A,">="&C2:C,A$2:A,"<="&D2:D),1))

我想要达到的是上个月的平均值.

如果有人能帮助我，我将不胜感激！

<小时>

但如果你真的需要这样做:

=ARRAYFORMULA(IF(LEN(A2:A),IFERROR(VLOOKUP(TEXT(DATE(YEAR(A2:A), MONTH(A2:A)-1, 1), "yyyy-mmm"),查询({文本(A2:A，yyyy-mmm")，B2:B}，"选择 Col1,avg(Col2)其中 Col2 不为空按 Col1", 0), 2, 0), 0), )) 分组

I'll post a link to the spreadsheet at the bottom of this write-up.

I'm trying to work out the average of a column of numbers that fall between a date range. The formula below works but I have to drag it down the column, I want it to be an array so it auto updates.

=iferror(averageifs(B$2:B,A$2:A,">="&C2,A$2:A,"<="&D2),1)

So I created it as an array as follows:

 =iferror(ArrayFormula(averageifs(B$2:B,A$2:A,">="&C2:C,A$2:A,"<="&D2:D)),1

But it stops after the first cell. So I broke up the average function into sum & count in order to divide.

The count array works using this formula:

=iferror(ArrayFormula(countifs(A$2:A,">="&C2:C,A$2:A,"<="&D2:D)),1)

However the sum array does not even go past the first cell:

=iferror(ArrayFormula(SUMIFS(B$2:B,A$2:A,">="&C2:C,A$2:A,"<="&D2:D)),1)

And the combination gets past the first cell but the calculations all come to zero:

=ArrayFormula(iferror(SUMIFS(B$2:B,A$2:A,">="&C2:C,A$2:A,"<="&D2:D)/countifs(A$2:A,">="&C2:C,A$2:A,"<="&D2:D),1))

What I'm trying to achieve is an average for the previous month.

If anyone can help me I'd be very grateful!

https://docs.google.com/spreadsheets/d/1XhoLl5hB-MpXFz9VS2aLqJOWbXk1d_7apnwKWFdDUlg/edit?usp=sharing

解决方案

you are trying to apply a bit weird structural logic with lots of repeating values with no reason. basically, it looks like you need this:

=ARRAYFORMULA(QUERY({TEXT(A2:A, "yyyy-mmm"), B2:B}, 
 "select Col1,avg(Col2) 
  where Col2 is not null 
  group by Col1 
  order by avg(Col2) desc 
  label avg(Col2)''", 0))

---

but if you really really need so:

=ARRAYFORMULA(IF(LEN(A2:A), 
 IFERROR(VLOOKUP(TEXT(DATE(YEAR(A2:A), MONTH(A2:A)-1, 1), "yyyy-mmm"), 
 QUERY({TEXT(A2:A, "yyyy-mmm"), B2:B}, 
 "select Col1,avg(Col2) 
  where Col2 is not null 
  group by Col1", 0), 2, 0), 0), ))

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