谷歌表格中无限真实动态范围的平均数组公式 [英] 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的最佳方法是什么?
QUERY
级别 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(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(查询(转置(间接("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(TRANSPOSE))间接("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(间接(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(间接(C2:"&ROWS(C:C))* 1，序列(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),查询(拆分(平展(行(C2:C)&×"& C2:J)，×")，"选择 Col1,avg(Col2)其中 Col2 不为空group by Col1"), 2, )))
=INDEX(IFNA(VLOOKUP(ROW(C2:C),查询(拆分(平展(行(C2:C)&×"& C2:J)，×")，"选择 Col1,avg(Col2)其中 Col2 不为空group by Col1"), 2, )))排除零:
=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,,9^9, 9^9)), "×";),"选择平均值(Col2)其中 Col2 <>0按 Col1 分组标签 avg(Col2)''"))包括空单元格:
=INDEX(IFERROR(1/(1/QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×")&OFFSET(C2,,,,9^9, 9^9))*1), ×"),"选择平均值(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)''"))))
这篇关于谷歌表格中无限真实动态范围的平均数组公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
