Google表格中无限真实动态范围上的平均值的ArrayFormula [英] ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
问题描述
根据示例:
A B C D E F G∞| ====== | ====== | ================ | ========== | =====1 ||平均||||||| ====== | ====== | ================ | ========== | =====2 |xx 1 ||1 |2 |0.5 |10 ||| ====== | ====== | ================ | ========== | =====3 |xx 2 ||7 |1 ||||| ====== | ====== | ================ | ========== | =====4 |||0 |||||| ====== | ====== | ================ | ========== | =====5 |xx 3 ||9 |8 |7 |6 ||| ====== | ====== | ================ | ========== | =====6 |xx 4 ||0 |1 |2 |1 ||| ====== | ====== | ================ | ========== | =====7 |||1 ||4 |||| ====== | ====== | ================ | ========== | =====8 |xx 5 |||||||| ====== | ====== | ================ | ========== | =====9 |||||||5 || ====== | ====== | ================ | ========== | =====∞|||||||| 以动态术语(未知数量的行& 未知数量的列)为每个有效行获取 AVERAGE 的最佳方法是什么?
查询
级别1:
如果范围 C2:G 中的所有5个单元格都具有值:
= QUERY(QUERY(C2:G，"select(C + D + E + F + G)/5")，"offset 1"，)
如果没有，则跳过行:
如果将空单元格视为零:
= INDEX(QUERY(QUERY({C2:G * 1}，"select(Col1 + Col2 + Col3 + Col4 + Col5)/5"，"offset 1"，))
要删除零值，我们使用 IFERROR(1/(1/...))包装:
= INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G * 1}，选择(Col1 + Col2 + Col3 + Col4 + Col5)/5")，偏移1"，))))
要使 Col 引用动态化，我们可以做到:
= INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G * 1}，"选择"&"((& JOIN("+""，"Col")& ROW(INDIRECT("1:"& COLUMNS(C:G))))&"/"& COLUMNS(C:G))，偏移量1"，))))
级别2:
如果空单元格不被视为零，则不应跳过:
= INDEX(TRANSPOSE(QUERY(TRANSPOSE(E2:I)，选择"& TEXTJOIN(，"，1，IF(A2:A =" ,,"avg(Col"& ROW(A2:A)-ROW(A2)+ 1&)'))))))，)2)
请注意，这取决于A列，因此A列中缺少的值将抵消结果
有趣的事实！我们可以将 avg 交换为 max 或 min :
将其从A列的限制中释放出来，并使其可用于任何有效行:
= INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(C2:G),, 9 ^ 9)))="，C2:G * 0，C2:G))，"选择"& TEXTJOIN(，"，1，"avg(Col"& ROW(A2:A)-ROW(A2)+ 1&)'))))))))，)2)
如果不应平均取0范围内的平均值，则可以添加一个小的IF语句:
= INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(IF(修剪(TRANSPOSE(查询(TRANSPOSE(IF(C2:G> 0，C2:G，)),, 9 ^ 9)))=""，C2:G * 0，IF(C2:G> 0，C2:G，)))，"选择"& TEXTJOIN(，"，1，"avg(Col"& ROW(A2:A)-ROW(A2)+ 1&)'))))))))，)2)
这里我们使用了所谓的垂直查询粉碎" ，它将所有值都在给定范围内并将其集中到一个单列中，其中每行的所有单元格都用空格隔开副产品:
= FLATTEN(QUERY(TRANSPOSE(C2:G),, 9 ^ 9))
除此之外，还水平查询粉碎" :
= QUERY(C2:G ,, 9 ^ 9)
以及最终360°双查询粉碎" ，它将范围内的所有单元格都放入一个单元格中:
= QUERY(FLATTEN(QUERY(TRANSPOSE(C2:G),, 9 ^ 9)),, 9 ^ 9)
，最后臭名昭著的负360°反向双重查询粉碎" ，该列将行优先于行:
= QUERY(FLATTEN(QUERY(C2:G ,, 9 ^ 9)),, 9 ^ 9)
所有查询smash名称均受版权保护
回到主题...如上所述，范围中每行的所有单元格都与空白空间(甚至是那些空白空间)连接在一起，因此，我们遇到了这样的情况，即值之间会出现双倍或多个空格.为了解决这个问题，我们使用 TRIM 并引入一个简单的 IF 语句为给定范围内的空行分配0值.抵消偏移量:
MMULT
级别3:
MMULT 是一种重类公式，能够对数组/矩阵进行加，减，乘，除甚至总计运算...但是，数据集越大=公式计算就越慢(因为在 MMULT 中，即使是空行也需要时间来执行 +-×÷操作)...除非我们在两者中均使用真正的动态范围，方向...
获得具有给定范围值的最后一行:
= INDEX(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(INDIRECT("C2:"& ROWS(A:A))),, 9 ^ 9)))="" ,, ROW(A2:A)))))
获得具有给定范围值的最后一列:
= INDEX(MAX(IF(TRIM(QUERY(INDIRECT("C2:"& ROWS(A:A)),, 9 ^ 9))=",,, COLUMN(C2:2))))
现在我们可以用一种简单的方式构造它:
= INDIRECT("C2:"& ADDRESS(9，7)) 与以下相同:
= INDEX(INDIRECT("C2:"& ADDRESS(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(INDIRECT("C2:"& ROWS(A:A))),, 9 ^ 9)))="" ,, ROW(A2:A)))，MAX(IF(TRIM(QUERY(INDIRECT("C2:"& ROWS(A:A)),, 9 ^ 9))=" ,, COLUMN(C2:2)))))))))
或更短的选择:
= INDEX(INDIRECT("C2:"& ADDRESS(MAX((INDIRECT("C2:"& ROWS(A:A))"")* ROW(A2:A))，MAX((INDIRECT("C2:"& ROWS(A:A))"")* COLUMN(C2:2))))))
因此简化的MMULT公式为:
= ARRAYFORMULA(IFERROR(MMULT(N(C2:G9)，ROW(INDIRECT("C1:C"& COLUMNS(C:G)))^ 0)/MMULT(N(IF(C2:G9″，1，)))，ROW(INDIRECT("C1:C"& COLUMNS(C:G))^ 0))))
如果我们要从范围中排除零值，则公式为:
= ARRAYFORMULA(IFERROR(MMULT(N(C2:G9)，ROW(INDIRECT("C1:C"& COLUMNS(C:G)))^ 0)/MMULT(N(IF(C2:G9> 0，1，))，ROW(INDIRECT("C1:C"& COLUMNS(C:G)))^ 0))))
4级:
将以上所有内容放在一起以使其无限动态，并且仍然限于有效数据集:
= INDEX(IFERROR(MMULT(N(INDIRECT("C2:"& ADDRESS(MAX((INDIRECT("C2:"& ROWS(A:A))<")* ROW(A2:A))，MAX((INDIRECT("C2:"& ROWS(A:A))"" ** COLUMN(C2:2))))))，ROW(INDIRECT("C1:C";&MAX((INDIRECT("C2:"& ROWS(A:A))*""*" COLUMN(C2:2))-(COLUMN(C2)-1)))^ 0)/MMULT(N(IF(INDIRECT("C2:"& ADDRESS(MAX((INDIRECT("C2:"& ROWS(A:A))<")* ROW(A2:A))，MAX((INDIRECT("C2:"& ROWS(A:A))"")* COLUMN(C2:2)))))"，1，))，ROW(INDIRECT("C1:C"&MAX((INDIRECT("C2:"& ROWS(A:A))*""*" COLUMN(C2:2))-(COLUMN(C2)-1)))^ 0))) 再次
，不包括范围为零的单元格:
荣誉提及:
不包括零:
= INDEX(IFERROR(MMULT(INDIRECT("C2:"& ROWS(C:C))* 1，SEQUENCE(COLUMNS(C2:2))^ 0)/MMULT(IF(INDIRECT("C2:"& ROWS(C:C))> 0，1)* 1，SEQUENCE(COLUMNS(C2:2))^ 0))))
= INDEX(IFNA(VLOOKUP(ROW(C2:C)，QUERY(SPLIT(FLATTEN(ROW(C2:C)&×"& C2:J)，×"))，选择Col1，avg(Col2)其中Col2不为null按Col1)，2，)))分组
= INDEX(IFNA(VLOOKUP(ROW(C2:C)，QUERY(SPLIT(FLATTEN(ROW(C2:C)&×"& C2:J)，×"))，选择Col1，avg(Col2)其中Col2不为null按Col1)，2，)))分组
不包括零:
= INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&``×''& OFFSET(C2 ,,, 9 ^ 9，9 ^ 9))，×'';)，"选择avg(Col2)其中Col2<>0按Col1分组标签avg(Col2)'''')) 包括空单元格
= INDEX(IFERROR(1/(1/QUERY(SPLIT(FLATTEN(ROW(C2:C)&``×'')& OFFSET(C2 ,,, 9 ^ 9，9 ^ 9)* 1)，×")，"选择avg(Col2)按Col1分组标签avg(Col2)'''')))) as per example:
A B C D E F G ∞
|======|=======|=====|=====|=====|=====|=====|=====
1 | |AVERAGE| | | | | |
|======|=======|=====|=====|=====|=====|=====|=====
2 | xx 1 | | 1 | 2 | 0.5 | 10 | |
|======|=======|=====|=====|=====|=====|=====|=====
3 | xx 2 | | 7 | 1 | | | |
|======|=======|=====|=====|=====|=====|=====|=====
4 | | | 0 | | | | |
|======|=======|=====|=====|=====|=====|=====|=====
5 | xx 3 | | 9 | 8 | 7 | 6 | |
|======|=======|=====|=====|=====|=====|=====|=====
6 | xx 4 | | 0 | 1 | 2 | 1 | |
|======|=======|=====|=====|=====|=====|=====|=====
7 | | | 1 | | 4 | | |
|======|=======|=====|=====|=====|=====|=====|=====
8 | xx 5 | | | | | | |
|======|=======|=====|=====|=====|=====|=====|=====
9 | | | | | | | 5 |
|======|=======|=====|=====|=====|=====|=====|=====
∞ | | | | | | | |
what's the most optimal way of getting AVERAGE for every valid row in the dynamic sense of terms (unknown quantity of rows & unknown quantity of columns) ?
QUERY
level 1:
if all 5 cells in range C2:G have values:
=QUERY(QUERY(C2:G, "select (C+D+E+F+G)/5"), "offset 1", )
if not, then rows are skipped:
if empty cells are considered as zeros:
=INDEX(QUERY(QUERY({C2:G*1}, "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))
to remove zero values we use IFERROR(1/(1/...)) wrapping:
=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1},
"select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))))
to make Col references dynamic we can do:
=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1},
"select "&
"("&JOIN("+", "Col"&ROW(INDIRECT("1:"&COLUMNS(C:G))))&")/"&COLUMNS(C:G)),
"offset 1", ))))
level 2:
if empty cells are not considered as zeros and shouldn't be skipped:
=INDEX(TRANSPOSE(QUERY(TRANSPOSE(E2:I),
"select "&TEXTJOIN(",", 1, IF(A2:A="",,
"avg(Col"&ROW(A2:A)-ROW(A2)+1&")")))),, 2)
note that this is column A dependant, so missing values in column A will offset the results
fun fact !! we can swap avg to max or min:
to free it from confinement of column A and make it work for any valid row:
=INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(
IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(C2:G),,9^9)))="", C2:G*0, C2:G)),
"select "&TEXTJOIN(",", 1,
"avg(Col"&ROW(A2:A)-ROW(A2)+1&")"))))),, 2)
if present 0's in range shouldn't be averaged we can add a small IF statement:
=INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(
IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
IF(C2:G>0, C2:G, )),,9^9)))="", C2:G*0,
IF(C2:G>0, C2:G, ))),
"select "&TEXTJOIN(",", 1,
"avg(Col"&ROW(A2:A)-ROW(A2)+1&")"))))),, 2)
here we used so-called "vertical query smash" which takes all values in a given range and concentrates it to one single column, where all cells per each row are joined with empty space as a byproduct:
=FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9))
apart from this, there is also "horizontal query smash":
=QUERY(C2:G,,9^9)
and also "ultimate 360° double query smash" which puts all cells from range into one single cell:
=QUERY(FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9)),,9^9)
and finally "the infamous negative 360° reverse double query smash" which prioritizes columns over rows:
=QUERY(FLATTEN(QUERY(C2:G,,9^9)),,9^9)
all query smash names are copyrighted of course
back to the topic... as mentioned above all cells per row in range are joined with empty space even those empty ones, so we got a situation where we getting double or multiple spaces between values. to fix this we use TRIM and introduce a simple IF statement to assign 0 values for empty rows in a given range eg. to counter the offset:
MMULT
level 3:
MMULT is a kind of heavy class formula that is able to perform addition, subtraction, multiplication, division even running total on arrays/matrixes... however, bigger the dataset = slower the formula calculation (because in MMULT even empty rows take time to perform + - × ÷ operation) ...unless we use truly dynamic range infinite in both directions...
to get the last row with values of a given range:
=INDEX(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))))
to get the last column with values of a given range:
=INDEX(MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))
now we can construct it in a simple way:
=INDIRECT("C2:"&ADDRESS(9, 7))
which is the same as:
=INDEX(INDIRECT("C2:"&ADDRESS(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))),
MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))))
or shorter alternative:
=INDEX(INDIRECT("C2:"&ADDRESS(
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)),
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2)))))
therefore simplified MMULT formula would be:
=ARRAYFORMULA(IFERROR(
MMULT(N( C2:G9), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)/
MMULT(N(IF(C2:G9<>"", 1, )), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)))
in case we want to exclude zero values from range, the formula would be:
=ARRAYFORMULA(IFERROR(
MMULT(N( C2:G9), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)/
MMULT(N(IF(C2:G9>0, 1, )), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)))
level 4:
putting together all above to make it infinitely dynamic and still restricted to valid dataset:
=INDEX(IFERROR(
MMULT(N( INDIRECT("C2:"&ADDRESS(
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)),
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))))), ROW(INDIRECT("C1:C"&
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))-(COLUMN(C2)-1)))^0)/
MMULT(N(IF(INDIRECT("C2:"&ADDRESS(
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)),
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))))<>"", 1, )), ROW(INDIRECT("C1:C"&
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))-(COLUMN(C2)-1)))^0)))
again, not including cells with zeros in range:
honorable mentions:
@Erik Tyler level:
the polar opposite of the previous formula would be to run the MMULT on
- total area of
C2:?(all rows, all columns)instead of - valid area
C2:?(excluding empty rows and columns)which avoids mass-calculations of0 × 0 = 0
including zeros:
=INDEX(IFERROR(
MMULT( INDIRECT("C2:"&ROWS(C:C))*1, SEQUENCE(COLUMNS(C2:2))^0)/
MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>"", 1)*1, SEQUENCE(COLUMNS(C2:2))^0)))
excluding zeros:
=INDEX(IFERROR(
MMULT( INDIRECT("C2:"&ROWS(C:C))*1, SEQUENCE(COLUMNS(C2:2))^0)/
MMULT(IF(INDIRECT("C2:"&ROWS(C:C))>0, 1)*1, SEQUENCE(COLUMNS(C2:2))^0)))
@kishkin level:
for a fixed range C2:G9 the MMULT average would be:
=INDEX(IFERROR(
MMULT( C2:G9*1, FLATTEN(COLUMN(C:G))^0)/
MMULT((C2:G9>0)*1, FLATTEN(COLUMN(C:G))^0)))
=INDEX(IFNA(VLOOKUP(ROW(C2:C),
QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&C2:J), "×"),
"select Col1,avg(Col2)
where Col2 is not null
group by Col1"), 2, )))
@MattKing level:
=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
"select avg(Col2)
group by Col1
label avg(Col2)''"))
excluding zeros:
=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
"select avg(Col2)
where Col2 <> 0
group by Col1
label avg(Col2)''"))
including empty cells:
=INDEX(IFERROR(1/(1/QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)*1), "×"),
"select avg(Col2)
group by Col1
label avg(Col2)''"))))
这篇关于Google表格中无限真实动态范围上的平均值的ArrayFormula的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
