如何在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 

但是它在第一个单元格之后停止.因此，我将平均功能分解为求和&为了划分而计数.

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

  = 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")，QUERY({TEXT(A2:A，"yyyy-mmm")，B2:B}，选择Col1，avg(Col2)其中Col2不为null按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屋！