ArrayFormula of Average on Infinite True Dynamic Range no Google Sheets
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 AVERAGE
para cada linha válida no sentido dinâmico dos termos (quantidade desconhecida de linhas e quantidade desconhecida de colunas)?
Respostas
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 Col
referê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 avg
para max
ou 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 TRIM
e apresentamos uma IF
instruçã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 MMULT
mesmo 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 MMULT
sobre
- área total de em vez de
C2:?
(all rows, all columns)
- área válida que evita cálculos de massa de
C2:?
(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:G9
a MMULT
mé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)''"))))
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))))
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.
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 C2
a ú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 0
se 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 FILTER
que 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 FILTER
com MATCH
condiçã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 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
)
)
)
O intervalo de dados original se tornará internamente:

Etapa 3 - Faça o MMULT
Agora podemos usar MMULT
esse 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 VLOOKUP
assim:
=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 aquele0
filtrado 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
INDEX
poderia ser usado em vez deARRAYFORMULA
por brevidade (obrigado @ player0, me ensinou isso alguns meses atrás), mas eu gosto da clareza deARRAYFORMULA
.- Eu costumo
SEQUENCE
construir uma coluna ou linha de1
s 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 SEQUENCE
a implementação interna de deve ser mais simples do que a operação de elevação a uma potência.
- Eu uso intervalo,
C2:K
que é uma coluna a mais do que realmente existe na planilha. Não só fornece um intervalo de todas as colunas à direitaC2
e 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. IssoC2:K
pode quase perfeitamente (haverá um problema no caso de haver realmente umaZZZ
coluna 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.
Acho que há uma resposta simples para a média de linha usando VLOOKUP
e 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
QUERY
instruçã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 como0
s;FLATTEN(IFERROR(1/(1/C2:J)))
para excluir0
s da média.
- Se não houver linhas vazias intermediárias,
VLOOKUP
pode ser removido da fórmula, bem comoCol1
daSELECT
instrução. - Há uma versão mais curta (obrigado @MattKing!) Sem
VLOOKUP
eWHERE 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:J
intervalo com colunas até I:I
, alguns detalhes sobre isso:
- Intervalo
C2:J
que é uma coluna a mais do que realmente existe na planilha. Não só fornece um intervalo de todas as colunas à direitaC2
e 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. IssoC2:J
pode quase perfeitamente (haverá um problema no caso de haver realmente umaZZZ
coluna 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 por0
), portanto, precisamos usar em seu=SEQUENCE(1, COLUMNS(C2:J),,)
lugar.
@ player0, alguma opinião sobre isso?