Precisa de uma fórmula do Excel que subtraia o mais recente dos 5 valores em uma coluna do mais antigo

Question

Sou um treinador de força e condicionamento físico e preciso de ajuda para analisar os resultados da avaliação dos meus atletas. Preciso de ajuda para escrever uma fórmula que subtraia o mais antigo dos 5 resultados de teste do mais recente. A ficha tem quase 3.000 linhas, sendo que cada atleta tem 5 linhas cada (semestre 1, 2, 3, 4 e verão), independentemente da sua participação efetiva nas referidas provas. Portanto, a fórmula basicamente precisa descer na coluna e pegar o primeiro valor disponível das 5 células e subtraí-lo do último valor disponível das 5 células. A planilha está configurada assim:

Por favor ajude! Lamento se isso não fornece informações suficientes, sou novo no site.

Answer 1

Eu tenho uma fórmula que funciona quando você a coloca em qualquer uma daquelas linhas cinza intitulada MUDAR

É um doozie, fica assim:

=INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), ROWS(Values)) - ROW(StartValue) + 1,1)- INDEX(Values, SMALL(INDEX((Values<>0)*ROW(Values), 0), COUNTIF(Values, "")+1) - ROW(StartValue)+1,1)

Onde

• Valuesé o intervalo de valores, no meu exemplo éH5:H9
• StartValueé a primeira célula no intervalo de valores, para mim isso éH5

Como funciona

• A instrução INDEX((Values<>0)*ROW(Values), 0)retorna uma matriz que é 0quando a célula está em branco e o número da linha da célula quando não está em branco
• A SMALL()função classifica-os em ordem crescente e retorna o valor na matriz correspondente ao seu segundo argumento

Portanto, na planilha de exemplo abaixo, INDEX((Values<>0)*ROW(Values), 0)retorna

0
6
7
8
0

SMALL()classifica para ficar assim:

0
0
6  <- Row of first nonzero value, second argument to SMALL(): =COUNTIF(Values, "")+1 = 3
7
8  <- Row of last nonzero value, second argument to SMALL(): ROWS(Values) = 5

• Agora que conhecemos a linha, usamos INDEX()para obter o valor. A linha - ROW(StartValue) + 1traduz a linha retornada por SMALL()em um índice
• Então ficamos com o valor diferente de zero mais recente da primeira chamada para INDEX()e subtraímos dele o valor diferente de zero mais distante da segunda chamada paraINDEX()

Captura de tela

Link para planilha

Aquié um link para baixar esta planilha de exemplo. Normalmente uso FormulaChop para explicar essas fórmulas, mas não funciona com a função ROW(). (Divulgação completa: escrevi FormulaChop). Então incluí uma versão da fórmula que funciona, e você pode vê-la dividida na guia FormulaChop.

Answer 2

Com as datas na coluna A (A2:A3001), os nomes na coluna B e os valores nas colunas C, coloque o nome para procurar em F2 e use esta fórmula em G2.

=SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(14, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))-
 SUMIFS(C:C, B:B, F2, A:A, AGGREGATE(15, 7, (A$2:A$3001)/(B$2:B$3001=F2), 1))

Answer 3

Espero que seja isso que você está procurando?

[ ]

Na foto, temos dois usuários. dados amarelos são o que você tem em sua planilha (acho que com muitos usuários), e outras colunas existem para avaliá-los usando fórmulas simples.

Answer 4

Isso pode ser o que você está procurando Imagem mostrando como são os resultados

É assim que fica a fórmula, é muito simples de usar Imagem mostrando fórmulas

Como criar fórmula? Pressione Alt+F11 e copie e cole o seguinte

Função pública Diff2 (n1 como duplo, n2 como duplo, n3 como duplo, n4 como duplo, n5 como duplo)

índice1 = 0 índice2 = 0

'encontre o primeiro número Se n1 > 0 Então PrimeiroNum = n1 índice1 = 1 ElseIf n2 > 0 Então PrimeiroNum = n2 índice1 = 1 ElseIf n3 > 0 Então PrimeiroNum = n3 índice1 = 3 ElseIf n4 > 0 Então PrimeiroNum = n4 índice1 = 4 Fim se

'encontre o segundo número Se n5 > 0 Então SecNum = n5 índice2 = 5 ElseIf n4 > 0 Então SecNum = n4 índice2 = 4 ElseIf n3 > 0 Então SecNum = n3 índice2 = 3 ElseIf n2 > 0 Então SecNum = n2 índice2 = 2 End If

'Verificação de erros If (index1 = 0) Ou (index2 = 0) Ou (index1 - index2 = 0) Then Diff2 = "ERROR" Else Diff2 = SecNum - FirstNum End If

Função final

Imagem mostrando Excel VBA