Crie uma fórmula que tenha referências de célula específicas alteradas a cada n'ésima linha no Excel

Question

Eu tenho um grande DataSet que é estruturado de forma que sempre 16 linhas pertençam ao mesmo grupo. (por exemplo: A1, A2, A3, A4, A5, A6,… A16 - > Grupo1; A17, A18, A19, A20,…, A32 - > Grupo2..etc.)

Eu gostaria de comparar todas as pessoas de um grupo (ex: Grupo 1) com todas as outras do mesmo grupo (por exemplo: Grupo 1) e recuperar a soma de cada um dos números menores das duas células comparadas outra célula (sem a própria comparação).

Por isso, eu tenho uma fórmula que eu criei, que me dá a informação que eu preciso & até aqui funciona! A fórmula que uso no Excel é a seguinte:

=SUM(MIN(A1,A$1),MIN(A1,A$2),MIN(A1,A$3),MIN(A1,A$4),MIN(A1,A$5),MIN(A1,A$6),MIN(A1,A$7),MIN(A1,A$8),MIN(A1,A$9),MIN(A1,A$10),MIN(A1,A$11),MIN(A1,A$12),MIN(A1,A$13),MIN(A1,A$14),MIN(A1,A$15),MIN(A1,A$16))-A1

Mas agora o problema é que essa fórmula funciona apenas para as primeiras 16 linhas (A1 - A16). Existe a possibilidade de alterar a fórmula a cada 16 linhas?

Ou como posso indicar a fórmula de tal forma que, quando atinge a linha A17, faça referência às 16 células seguintes (A17 a A32) e não às 16 células anteriores (A1 a A16)?

Fazer isso manualmente não é uma opção nesse caso, pois tenho milhares de linhas de dados e a fórmula deve alternar sua referência a cada 16 linhas do conjunto de dados e fazer referência às 16 linhas que pertencem ao mesmo grupo.

    

Answer 1

Primeiramente eu simplifiquei sua fórmula original usando uma fórmula de matriz (precisa ser inserida pressionando ctrl + shift + enter

=SUM(IF(A1<$A$1:$A$16,A1,$A$1:$A$16))-A1

Então precisa ser dinâmico

Esta fórmula retorna 1 para as primeiras 16 linhas da planilha

=(INT((ROW()-1)/16)*16+1)

Esta fórmula retorna 16 para as primeiras 16 linhas da planilha

=(INT((ROW()-1)/16)+1)*16

Estes foram inseridos em uma função de índice para retornar as células A1 (abaixo) e A16

INDEX($A$1:$A$32,(INT((ROW()-1)/16)*16+1))

Todas essas fórmulas foram colocadas juntas para criar a fórmula final que ainda precisa ser digitada usando ctrl + shift + enter .

Ele pressupõe que seu intervalo total seja A1: A32, que precisará ser estendido para o conjunto de dados. Além disso, se os dados não começarem na linha 1, você precisará ajustar as fórmulas Int.

=SUM(IF(A1<INDEX($A$1:$A$32,(INT((ROW()-1)/16)*16+1)):INDEX($A$1:$A$32,((INT((ROW()-1)/16)+1)*16)),A1,INDEX($A$1:$A$32,(INT((ROW()-1)/16)*16+1)):INDEX($A$1:$A$32,((INT((ROW()-1)/16)+1)*16))))-A1

Aqui está uma imagem da primeira fórmula de matriz funcionando. O resto da resposta apenas faz com que ela mude a cada 16 células.