ArrayFormula of Average on Infinite Truly Dynamic Range en Google Sheets

Dec 24 2020

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 AVERAGEcada fila válida en el sentido dinámico de los términos (cantidad desconocida de filas y cantidad desconocida de columnas)?

Respuestas

4 player0 Dec 24 2020 at 14:34

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 Coldinamizar 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 avga maxo 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 TRIMe introducimos una IFdeclaración simple para asignar valores 0 para filas vacías en un rango dado, por ejemplo. para contrarrestar el desplazamiento:


MMULT

nivel 3:

MMULTes 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 MMULTincluso 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 MMULTen

  • área total de en lugar deC2:? (all rows, all columns)
  • área válida que evita cálculos de masa deC2:? (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:G9el MMULTpromedio 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)''"))))
2 ErikTyler Dec 24 2020 at 18:27

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))))

2 MattKing Feb 16 2021 at 23:04

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.

1 kishkin Dec 25 2020 at 20:43

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 C2abajo 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 0si 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 FILTERcon MATCHcondició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 FILTERs:

=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 MMULTcon 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 VLOOKUPasí:

=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 se 0filtran 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

  • INDEXpodría usarse en lugar de ARRAYFORMULApor brevedad (gracias @ player0, me lo enseñaron hace unos meses), pero me gusta la falta de ambigüedad de ARRAYFORMULA.
  • Utilizo SEQUENCEpara construir una columna o una fila de 1s 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 SEQUENCEla implementación interna debería ser más simple que la operación de elevarse a un poder.

  • Utilizo un rango C2:Kque es una columna más de lo que realmente existe en la hoja. No solo proporciona un rango de todas las columnas a la derecha C2y 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. Esto C2:Kpuede casi perfectamente (habrá un problema en caso de que haya una ZZZcolumna 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.
1 kishkin Jan 04 2021 at 19:17

Creo que hay una respuesta simple para el promedio de filas usando VLOOKUPy 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 QUERYdeclaració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 como 0s;
    • FLATTEN(IFERROR(1/(1/C2:J)))para excluir 0s del promedio.
  • Si no hay filas vacías intermedias, VLOOKUPpodría eliminarse de la fórmula, así como Col1de la SELECTdeclaración.
  • Hay una versión más corta (¡gracias @MattKing!) Sin VLOOKUPy WHERE 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:Jrango con columnas hasta I:I, algunos detalles sobre eso:

  • Rango C2:Jque es una columna más de lo que realmente existe en la hoja. No solo proporciona un rango de todas las columnas a la derecha C2y 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. Esto C2:Jpuede casi perfectamente (habrá un problema en caso de que haya una ZZZcolumna 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 por 0), por lo que debemos usar =SEQUENCE(1, COLUMNS(C2:J),,)en su lugar.

@ player0, ¿alguna idea sobre esto?