ArrayFormula of Average on Infinite True Dynamic Range no Google Sheets

Dec 24 2020

como por exemplo:

     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 |           
  |======|=======|=====|=====|=====|=====|=====|=====
∞ |      |       |     |     |     |     |     |       

Qual é a maneira mais ideal de obter AVERAGEpara cada linha válida no sentido dinâmico dos termos (quantidade desconhecida de linhas e quantidade desconhecida de colunas)?

Respostas

4 player0 Dec 24 2020 at 14:34

INQUERIR

nível 1:

se todas as 5 células no intervalo C2: G tiverem valores:

=QUERY(QUERY(C2:G, "select (C+D+E+F+G)/5"), "offset 1", )

caso contrário, as linhas são ignoradas:

se as células vazias forem consideradas zeros:

=INDEX(QUERY(QUERY({C2:G*1}, "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))

para remover valores zero, usamos IFERROR(1/(1/...))agrupamento:

=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1}, 
 "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))))

para tornar as Colreferências dinâmicas, podemos fazer:

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


nível 2:

se as células vazias não forem consideradas zeros e não devem ser ignoradas:

=INDEX(TRANSPOSE(QUERY(TRANSPOSE(E2:I), 
 "select "&TEXTJOIN(",", 1, IF(A2:A="",,
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&")")))),, 2)

observe que esta é a coluna A dependente, portanto, os valores ausentes na coluna A compensarão os resultados

fato engraçado !! podemos trocar avgpara maxou min:

para libertá-lo do confinamento da coluna A e fazê-lo funcionar para qualquer linha 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)

se os 0s presentes no intervalo não devem ser calculados, podemos adicionar uma pequena declaração 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)

aqui usamos o chamado "quebra de consulta vertical" que pega todos os valores em um determinado intervalo e os concentra em uma única coluna, onde todas as células de cada linha são unidas com um espaço vazio como um subproduto:

=FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9))

além disso, há também "quebra de consulta horizontal" :

=QUERY(C2:G,,9^9)

e também "quebra de consulta dupla de 360 ​​° final", que coloca todas as células do intervalo em uma única célula:

=QUERY(FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9)),,9^9)

e, finalmente, "o infame quebra de consulta dupla reversa negativa 360 °", que prioriza colunas em vez de linhas:

=QUERY(FLATTEN(QUERY(C2:G,,9^9)),,9^9)

todos os nomes de quebra de consulta são protegidos por direitos autorais, é claro

de volta ao tópico ... como mencionado acima, todas as células por linha no intervalo são unidas com espaço vazio, mesmo aquelas vazias, então temos uma situação em que obtemos espaços duplos ou múltiplos entre os valores. para corrigir isso, usamos TRIMe apresentamos uma IFinstrução simples para atribuir 0 valores para linhas vazias em um determinado intervalo, por exemplo. para contrabalançar o deslocamento:


MMULT

nível 3:

MMULTé um tipo de fórmula de classe pesada que é capaz de realizar adição, subtração, multiplicação, divisão até mesmo executando o total em matrizes / matrizes ... no entanto, quanto maior o conjunto de dados = mais lento o cálculo da fórmula (porque MMULTmesmo em linhas vazias demoram tempo para realizar a + - × ÷operação ) ... a menos que usemos uma faixa verdadeiramente dinâmica infinita em ambas as direções ...

para obter a última linha com valores de um determinado intervalo:

=INDEX(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
 INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))))

para obter a última coluna com valores de um determinado intervalo:

=INDEX(MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))

agora podemos construí-lo de uma maneira simples:

=INDIRECT("C2:"&ADDRESS(9, 7))

que é o mesmo 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))))))

ou alternativa mais curta:

=INDEX(INDIRECT("C2:"&ADDRESS(
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)), 
 MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2)))))

portanto, a fórmula MMULT simplificada seria:

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

caso desejemos excluir valores zero do intervalo, a fórmula seria:

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

nível 4:

juntando tudo acima para torná-lo infinitamente dinâmico e ainda restrito a um conjunto de dados 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)))

novamente, não incluindo células com zeros no intervalo:


menções honrosas:

Nível @Erik Tyler :

o pólo oposto da fórmula anterior seria a de executar o MMULTsobre

  • área total de em vez deC2:? (all rows, all columns)
  • área válida que evita cálculos de massa deC2:? (excluding empty rows and columns)0 × 0 = 0

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

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

nível @kishkin :

para um intervalo fixo, C2:G9a MMULTmédia seria:

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

Nível @MattKing :

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

excluindo 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)''"))

incluindo células vazias:

=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

Você investiu muito tempo nisso. Espero que as pessoas apreciem isso, mais ainda porque você fez isso por todos os outros e não por si mesmo.

Olhando para suas fórmulas finais, elas devem produzir os mesmos resultados (fornecer dados em C2 :? como em seus exemplos):

Em B2 (incluir zeros):

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

Em B2 (excluir zeros):

=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

ATUALIZAÇÃO: Eu atualizei a fórmula do meu post original. O ROW () deve sempre vir primeiro para que os valores ausentes nos dados não prejudiquem a divisão.

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"|"&OFFSET(C2,,,9^9,9^9)),"|"),"select AVG(Col2) group by Col1 label AVG(Col2)''"))

Deve funcionar, a menos que eu esteja entendendo mal a pergunta.

Não há necessidade de vlookups ou mmults ou filtros ou qualquer coisa.

1 kishkin Dec 25 2020 at 20:43

Vou tentar fazer um pequeno acréscimo à resposta de @ player0. E eu realmente aprecio qualquer comentário sobre como otimizar isso.


No caso de haver muitas linhas e colunas vazias dentro do intervalo de dados, elas também podem ser excluídas MMULT.

Etapa 1 - Filtre as linhas vazias

Temos um intervalo de dados: desde C2a última linha até a última coluna (que é J:J). Vou usar C2:K, veja os detalhes abaixo para explicação.

Esta fórmula nos dará uma matriz de números de linha onde há pelo menos uma célula não vazia. Também terá um 0se houver linhas vazias, mas não importa para pesquisar neste array, ou vamos filtrá-lo quando for importante:

=ARRAYFORMULA(
  UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K)))
)

Então, para filtrar linhas vazias do intervalo de dados, usamos o FILTERque verificará se uma linha está em nosso array de cima e deixará se for nesse caso:

=ARRAYFORMULA(
  FILTER(
    C2:K*1,
    MATCH(
      ROW(C2:K),
      UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
      0
    )
  )
)

Etapa 2 - Filtrar colunas vazias

Para obter uma matriz de apenas números de coluna não vazios, podemos usar quase a mesma fórmula:

=ARRAYFORMULA(
  UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))))
)

Por que SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))é usado em vez de COLUMN(C2:K)ver os detalhes no final.

Para filtrar colunas vazias, também usamos FILTERcom MATCHcondição para pesquisar números de coluna em nossa 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
    )
  )
)

E para filtrar linhas e colunas vazias, usamos apenas dois 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
    )
  )
)

O intervalo de dados original se tornará internamente:

Etapa 3 - Faça o MMULT

Agora podemos usar MMULTesse conjunto de dados para calcular a média:

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

É um pouco estranho em relação às linhas de dados originais.

Etapa 4 - Preencher a coluna MÉDIA

Para tornar as médias consistentes com as linhas de dados originais, podemos usar VLOOKUPassim:

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

Onde

  • SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))é uma matriz de números de linha do segundo ao último nenhum vazio. Não preencheremos todas as linhas com strings vazias.
  • QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")é uma matriz de números de linha não vazios com aquele 0filtrado usado como chaves para pesquisa.
  • IFNA retornará uma string vazia para colocar ao lado de uma linha de dados vazia.

FÓRMULA FINAL

Juntando tudo:

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


Alguns detalhes

  • INDEXpoderia ser usado em vez de ARRAYFORMULApor brevidade (obrigado @ player0, me ensinou isso alguns meses atrás), mas eu gosto da clareza de ARRAYFORMULA.
  • Eu costumo SEQUENCEconstruir uma coluna ou linha de 1s para ser explícito, para maior clareza. Por exemplo, este aqui
SEQUENCE(
  ROWS(
    QUERY(
      UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
      "WHERE Col1 <> 0"
    )
  ),
  1,
  1,
  0
)

poderia ser substituído por

SIGN(
  QUERY(
    UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
    "WHERE Col1 <> 0"
  )
)

que é um pouco mais curto. Também existe uma maneira demonstrada aqui por @ player0 de elevar à potência de 0:

QUERY(
  UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
  "WHERE Col1 <> 0"
)^0

mas (é apenas especulação minha) acho que SEQUENCEa implementação interna de deve ser mais simples do que a operação de elevação a uma potência.

  • Eu uso intervalo, C2:Kque é uma coluna a mais do que realmente existe na planilha. Não só fornece um intervalo de todas as colunas à direita C2e todas as linhas abaixo dela, mas também atualiza no caso de adicionar outra coluna à direita da planilha: uma demonstração . Embora não chegue a ser destacado. Isso C2:Kpode quase perfeitamente (haverá um problema no caso de haver realmente uma ZZZcoluna presente em uma folha) substituir essas abordagens:
INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
  • Há uma pequena desvantagem em usar C2:K: =ARRAYFORMULA(COLUMN(C2:K))retornará uma matriz de números de coluna, mesmo para as não existentes, portanto, precisamos usar em seu =SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))lugar.
1 kishkin Jan 04 2021 at 19:17

Acho que há uma resposta simples para a média de linha usando VLOOKUPe QUERY.

Este está em 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
    )
  )
)

  • Isso pode ser facilmente alterado para max, min, sum, count - basta alterar a função de agregação dentro da QUERYinstrução.
  • A mesma abordagem pode ser usada para agregação por coluna.
  • FLATTEN(C2:J) pode ser alterado para:
    • FLATTEN(--C2:J)para tratar células vazias como 0s;
    • FLATTEN(IFERROR(1/(1/C2:J)))para excluir 0s da média.
  • Se não houver linhas vazias intermediárias, VLOOKUPpode ser removido da fórmula, bem como Col1da SELECTinstrução.
  • Há uma versão mais curta (obrigado @MattKing!) Sem VLOOKUPe 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) ''"
  )
)

Eu uso C2:Jintervalo com colunas até I:I, alguns detalhes sobre isso:

  • Intervalo C2:Jque é uma coluna a mais do que realmente existe na planilha. Não só fornece um intervalo de todas as colunas à direita C2e todas as linhas abaixo dela, mas também atualiza no caso de adicionar outra coluna à direita da planilha: uma demonstração . Embora não chegue a ser destacado. Isso C2:Jpode quase perfeitamente (haverá um problema no caso de haver realmente uma ZZZcoluna presente em uma folha) substituir essas abordagens:
INDIRECT("C2:" & ROWS(C:C))

OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
  • Há uma pequena desvantagem em usar C2:J: =ARRAYFORMULA(0 * COLUMN(C2:J))retornará uma matriz de números de coluna, mesmo para os não existentes (multiplicado por 0), portanto, precisamos usar em seu =SEQUENCE(1, COLUMNS(C2:J),,)lugar.

@ player0, alguma opinião sobre isso?