ArrayFormula of Average on Infinite Truly Dynamic Range en Google Sheets
como por ejemplo:
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 |
|======|=======|=====|=====|=====|=====|=====|=====
∞ | | | | | | | |
¿Cuál es la forma más óptima de obtener AVERAGE
cada fila válida en el sentido dinámico de los términos (cantidad desconocida de filas y cantidad desconocida de columnas)?
Respuestas
CONSULTA
nivel 1:
si las 5 celdas en el rango C2: G tienen valores:
=QUERY(QUERY(C2:G, "select (C+D+E+F+G)/5"), "offset 1", )

si no, las filas se saltan:

si las celdas vacías se consideran ceros:
=INDEX(QUERY(QUERY({C2:G*1}, "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))

para eliminar los valores cero usamos IFERROR(1/(1/...))
envoltura:
=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1},
"select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))))

para Col
dinamizar las referencias podemos hacer:
=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1},
"select "&
"("&JOIN("+", "Col"&ROW(INDIRECT("1:"&COLUMNS(C:G))))&")/"&COLUMNS(C:G)),
"offset 1", ))))

nivel 2:
si las celdas vacías no se consideran ceros y no se deben omitir:
=INDEX(TRANSPOSE(QUERY(TRANSPOSE(E2:I),
"select "&TEXTJOIN(",", 1, IF(A2:A="",,
"avg(Col"&ROW(A2:A)-ROW(A2)+1&")")))),, 2)

tenga en cuenta que esto es dependiente de la columna A, por lo que los valores faltantes en la columna A compensarán los resultados
hecho de la diversión !! podemos cambiar avg
a max
o min
:

para liberarlo del confinamiento de la columna A y hacer que funcione para cualquier fila válida:
=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)

Si los ceros presentes en el rango no deben promediarse, podemos agregar una pequeña declaración IF:
=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)

aquí usamos el llamado "smash de consulta vertical", que toma todos los valores en un rango dado y los concentra en una sola columna, donde todas las celdas de cada fila se unen con un espacio vacío como subproducto:
=FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9))

Aparte de esto, también hay "aplastamiento de consulta horizontal" :
=QUERY(C2:G,,9^9)

y también "rotura definitiva de doble consulta de 360 °" que coloca todas las celdas del rango en una sola celda:
=QUERY(FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9)),,9^9)

y finalmente "el infame error negativo de doble consulta inversa de 360 °" que prioriza las columnas sobre las filas:
=QUERY(FLATTEN(QUERY(C2:G,,9^9)),,9^9)

Por supuesto, todos los nombres de consultas rotas tienen derechos de autor.
volviendo al tema ... como se mencionó anteriormente, todas las celdas por fila en el rango se unen con espacios vacíos, incluso aquellos vacíos, por lo que tenemos una situación en la que obtenemos espacios dobles o múltiples entre valores. para solucionar esto usamos TRIM
e introducimos una IF
declaración simple para asignar valores 0 para filas vacías en un rango dado, por ejemplo. para contrarrestar el desplazamiento:

MMULT
nivel 3:
MMULT
es un tipo de fórmula de clase pesada que es capaz de realizar sumas, restas, multiplicaciones, divisiones incluso ejecutando el total en matrices / matrices ... sin embargo, más grande es el conjunto de datos = más lento el cálculo de la fórmula (porque MMULT
incluso en filas vacías se necesita tiempo para realizar la + - × ÷
operación ) ... a menos que usemos un rango verdaderamente dinámico infinito en ambas direcciones ...
para obtener la última fila con valores de un rango dado:
=INDEX(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))))

para obtener la última columna con valores de un rango dado:
=INDEX(MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))

ahora podemos construirlo de forma sencilla:
=INDIRECT("C2:"&ADDRESS(9, 7))
que es lo mismo que:
=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))))))

o alternativa más corta:
=INDEX(INDIRECT("C2:"&ADDRESS(
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)),
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2)))))

por lo tanto, la fórmula de MMULT simplificada sería:
=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)))

en caso de que queramos excluir valores cero del rango, la fórmula sería:
=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)))

nivel 4:
reuniendo todo lo anterior para hacerlo infinitamente dinámico y aún restringido a un conjunto de datos válido:
=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)))

de nuevo, sin incluir celdas con ceros en el rango:

Menciones honoríficas:
@Erik Tyler nivel:
el polo opuesto de la fórmula anterior sería correr el MMULT
en
- área total de en lugar de
C2:?
(all rows, all columns)
- área válida que evita cálculos de masa de
C2:?
(excluding empty rows and columns)
0 × 0 = 0
incluyendo ceros:
=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)))

excluyendo ceros:
=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)))

nivel @kishkin :
para un rango fijo, C2:G9
el MMULT
promedio sería:
=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, )))

@ Nivel MattKing :
=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
"select avg(Col2)
group by Col1
label avg(Col2)''"))

excluyendo ceros:
=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)''"))
incluyendo celdas vacías:
=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)''"))))
Pusiste mucho tiempo en esto. Espero que la gente lo aprecie, más para que lo hicieras por todos los demás y no por ti mismo.
Mirando sus fórmulas finales, estas deberían producir los mismos resultados (proporcione datos en C2 :? como en sus ejemplos):
En B2 (incluir ceros):
=ArrayFormula(IFERROR(MMULT(INDIRECT("C2:"&ROWS(C:C))*1,SEQUENCE(COLUMNS(C1:1),1,1,0))/ MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>"",1,0),SEQUENCE(COLUMNS(C1:1),1,1,0))))
En B2 (excluir ceros):
=ArrayFormula(IFERROR(MMULT(INDIRECT("C2:"&ROWS(C:C))*1,SEQUENCE(COLUMNS(C1:1),1,1,0))/ MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>0,1,0),SEQUENCE(COLUMNS(C1:1),1,1,0))))
ACTUALIZACIÓN: He actualizado la fórmula de mi publicación original. La FILA () siempre debe ir primero para que los valores faltantes en los datos no eliminen la división.
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"|"&OFFSET(C2,,,9^9,9^9)),"|"),"select AVG(Col2) group by Col1 label AVG(Col2)''"))
Debería funcionar a menos que no haya entendido mal la pregunta.
No hay necesidad de vlookups o mmults o filtros ni nada.
Intentaré hacer una pequeña adición a la respuesta de @ player0. Y realmente agradeceré cualquier comentario sobre cómo optimizar esto.
En caso de que haya muchas filas y columnas vacías dentro del rango de datos, esas también podrían excluirse MMULT
.
Paso 1: filtrar filas vacías
Tenemos un rango de datos: desde C2
abajo hasta la última fila y hasta la última columna (que es J:J
). Usaré C2:K
, vea los detalles a continuación para obtener una explicación.
Esta fórmula nos dará una matriz de números de fila donde hay al menos una celda no vacía. También tendrá un 0
si hay filas vacías, pero no importará buscar en esta matriz, o lo filtraremos cuando sea importante:
=ARRAYFORMULA(
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K)))
)

Entonces, para filtrar filas vacías del rango de datos que usamos FILTER
, verificará si una fila está en nuestra matriz desde arriba y dejará si está en ese caso:
=ARRAYFORMULA(
FILTER(
C2:K*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
)
)
Paso 2: filtrar las columnas vacías
Para obtener una matriz de solo números de columna no vacíos, podemos usar casi la misma fórmula:
=ARRAYFORMULA(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))))
)

Por qué SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))
se usa en lugar de COLUMN(C2:K)
ver los detalles al final.
Para filtrar columnas vacías también usamos FILTER
con MATCH
condición para buscar números de columna en nuestra matriz:
=ARRAYFORMULA(
FILTER(
C2:K*1,
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
)
)
Y para filtrar filas vacías y columnas vacías, solo usamos dos FILTER
s:
=ARRAYFORMULA(
FILTER(
FILTER(
C2:K*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
)
)
El rango de datos original se convertirá internamente en:

Paso 3: haz el MMULT
Ahora podemos usar MMULT
con ese conjunto de datos para calcular el promedio:
=ARRAYFORMULA(
MMULT(
FILTER(
FILTER(
C2:K*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
),
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
) /
MMULT(
FILTER(
FILTER(
(C2:K <> "")*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
),
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
)
)

Está un poco desviado con respecto a las filas de datos originales.
Paso 4: complete la columna PROMEDIO
Para hacer promedios consistentes con las filas de datos originales, podemos usar VLOOKUP
así:
=ARRAYFORMULA(
IFNA(VLOOKUP(
SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2)),
{
QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0"),
MMULT(
...
) /
MMULT(
...
)
},
2,
0
))
)
Dónde
SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))
es una matriz de números de fila desde el segundo hasta el último que no está vacío. No llenaremos todas las filas con cadenas vacías.QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")
es una matriz de números de fila no vacíos que se0
filtran y se utilizan como claves para la búsqueda.IFNA
devolverá una cadena vacía para poner junto a una fila de datos vacía.
FÓRMULA FINAL
Poniendolo todo junto:
=ARRAYFORMULA(
IFNA(VLOOKUP(
SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2)),
{
QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0"),
MMULT(
FILTER(
FILTER(
C2:K*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
),
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
) /
MMULT(
FILTER(
FILTER(
(C2:K <> "")*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
),
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
)
},
2,
0
))
)

Algunos detalles
INDEX
podría usarse en lugar deARRAYFORMULA
por brevedad (gracias @ player0, me lo enseñaron hace unos meses), pero me gusta la falta de ambigüedad deARRAYFORMULA
.- Utilizo
SEQUENCE
para construir una columna o una fila de1
s para ser explícitos, para mayor claridad. Por ejemplo, este
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
podría ser reemplazado con
SIGN(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
)
que es un poco más corto. También hay una forma demostrada aquí por @ player0 de elevar al poder de 0
:
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)^0
pero (es solo mi especulación) creo que SEQUENCE
la implementación interna debería ser más simple que la operación de elevarse a un poder.
- Utilizo un rango
C2:K
que es una columna más de lo que realmente existe en la hoja. No solo proporciona un rango de todas las columnas a la derechaC2
y todas las filas hacia abajo, sino que también se actualiza en caso de agregar otra columna a la derecha de la hoja: una demostración . Aunque no se destaca. EstoC2:K
puede casi perfectamente (habrá un problema en caso de que haya unaZZZ
columna presente en una hoja) reemplazar esos enfoques:
INDIRECT("C2:" & ROWS(C:C))
OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
- Hay un pequeño inconveniente en el uso
C2:K
:=ARRAYFORMULA(COLUMN(C2:K))
devolverá una matriz de números de columna incluso para las que no existen, por lo que debemos usar=SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))
en su lugar.
Creo que hay una respuesta simple para el promedio de filas usando VLOOKUP
y QUERY
.
Este está en B2
:
=ARRAYFORMULA(
IFNA(
VLOOKUP(
ROW(B2:B),
QUERY(
{
FLATTEN(ROW(C2:J) + SEQUENCE(1, COLUMNS(C2:J),,)),
FLATTEN(C2:J)
},
"SELECT Col1, AVG(Col2)
WHERE Col2 IS NOT NULL
GROUP BY Col1"
),
2,
0
)
)
)

- Esto podría cambiarse fácilmente para max, min, sum, count, solo cambie la función de agregación dentro de la
QUERY
declaración. - Se podría utilizar el mismo enfoque para la agregación por columnas.
FLATTEN(C2:J)
podría cambiarse a:FLATTEN(--C2:J)
tratar las celdas vacías como0
s;FLATTEN(IFERROR(1/(1/C2:J)))
para excluir0
s del promedio.
- Si no hay filas vacías intermedias,
VLOOKUP
podría eliminarse de la fórmula, así comoCol1
de laSELECT
declaración. - Hay una versión más corta (¡gracias @MattKing!) Sin
VLOOKUP
yWHERE Col...
:
=ARRAYFORMULA(
QUERY(
{
FLATTEN(ROW(C2:J) + SEQUENCE(1, COLUMNS(C2:J),,)),
FLATTEN(IFERROR(1/(1/C2:J)))
},
"SELECT AVG(Col2)
GROUP BY Col1
LABEL AVG(Col2) ''"
)
)

Utilizo el C2:J
rango con columnas hasta I:I
, algunos detalles sobre eso:
- Rango
C2:J
que es una columna más de lo que realmente existe en la hoja. No solo proporciona un rango de todas las columnas a la derechaC2
y todas las filas hacia abajo, sino que también se actualiza en caso de agregar otra columna a la derecha de la hoja: una demostración . Aunque no se destaca. EstoC2:J
puede casi perfectamente (habrá un problema en caso de que haya unaZZZ
columna presente en una hoja) reemplazar esos enfoques:
INDIRECT("C2:" & ROWS(C:C))
OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
- Hay un pequeño inconveniente en el uso
C2:J
:=ARRAYFORMULA(0 * COLUMN(C2:J))
devolverá una matriz de números de columna incluso para los que no existen (multiplicados por0
), por lo que debemos usar=SEQUENCE(1, COLUMNS(C2:J),,)
en su lugar.
@ player0, ¿alguna idea sobre esto?