具有一行的AverageIF的数组公式 [英] Array Formula with AverageIF of a row

问题描述

我在 Google 表格中有一个公式，它正在对一行中的值求平均值...

I have a formula in a Google Sheet that is averaging the values in a row...

=AVERAGEIF(A2:L2,"<>0")

很简单吧?

但是，我不知道如何让 ARRAYFORMULA 为这个工作.

However, I cannot figure out how to get the ARRAYFORMULA to work for this.

=ARRAYFORMULA(AVERAGEIF(A2:L,"<>0")

该公式仅对范围内的所有单元格求平均值，而不是返回每一行的平均值.我错过了什么?

That formula only averages all of the cells in the range, instead of returning the average for each row. What am I missing?

推荐答案

你已经接近了.试试:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:L),
 "select "&TEXTJOIN(",", 1, IF(LEN(A2:A),
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")", ))&"")), 
 "select Col2"))

然后添加 <>0 你需要:

=ARRAYFORMULA(QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:L),
 "select "&TEXTJOIN(",", 1, IF(LEN(A2:A),
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")", ))&
 "where "&TEXTJOIN(" and ", 1, IF(LEN(A2:A), 
 "not Col"&ROW(A2:A)-ROW(A2)+1&"=0", ))&"")), 
 "select Col2"))

这篇关于具有一行的AverageIF的数组公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！