Obtenha os últimos 5 valores não zero seguidos

Question

Eu tenho uma planilha grande com muitas linhas em que os dados podem ter zeros e valores positivos / negativos, mas todos os valores numéricos. Eu gostaria de escrever uma fórmula (ou cinco fórmulas, se necessário) para me dizer quais são os últimos cinco valores diferentes de zero para cada linha.

Exemplo (uma linha de dados): 1 - 5 - 0 - 8 - 10 - 0 - 7 - 0 - 3 - 4

Nos dados de exemplo acima, idealmente a fórmula (ou 5 fórmulas) retornaria: 4 - 3 - 7 - 10 - 8

Qualquer ajuda é muito apreciada, foi atormentando meu cérebro neste.

-Obrigado

    

Answer 1

Você pode fazer isso com fórmulas e algumas matrizes auxiliares. Digamos que seus dados estejam nas colunas A a J, começando na linha 1. Use as colunas L a P para os cinco resultados. As colunas R a AA são a primeira matriz auxiliar e as colunas AC a AL são a segunda matriz auxiliar. Você pode ocultar as matrizes auxiliares ou movê-las para outro lugar (basta ajustar os cálculos de referência da coluna).

Vamos começar com o primeiro array de ajuda. O Excel tem todos os tipos de funções que funcionam da esquerda para a direita, mas nenhuma que funciona da direita para a esquerda (a menos que você esteja usando a edição israelense :-)). O primeiro array auxiliar apenas inverte a ordem dos dados. A célula R1 conteria:

    =OFFSET($A1,0,27-COLUMN(), )

Copie isso para as células S1 a AA1.

A segunda matriz auxiliar determina as localizações e os números de sequência dos valores diferentes de zero. A célula AC1 contém:

    =IF(R1=0,"",COLUMN()-28-COUNTIF($R1:R1,0))

Copie isso para as colunas AD a AL.

Agora, os valores dos resultados. Digite esta fórmula em L1 e copie para M1 até P1:

    =INDEX($R1:$AA1, ,MATCH(1,$AC1:$AL1,0))

Edite as fórmulas M a P, substituindo o parâmetro de pesquisa na função MATCH de 2 a 5, de modo que as fórmulas de M a P sejam:

    =INDEX($R1:$AA1, ,MATCH(2,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(3,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(4,$AC1:$AL1,0))
    =INDEX($R1:$AA1, ,MATCH(5,$AC1:$AL1,0))

Agora, todas as fórmulas podem ser copiadas na planilha conforme necessário.

    

Answer 2

Supondo que seus valores estejam em A1: J1, esta fórmula de matriz ** em, por exemplo, L1:

=INDEX($A1:$J1,LARGE(IF($A1:$J1<>0,COLUMN($A1:$J1)-MIN(COLUMN($A1:$J1))+1),COLUMNS($A:A)))

Copie para a direita mais 4 colunas.

Isso também pode ser copiado para dar resultados semelhantes aos dados em A2: J2, A3: J3, etc.

Atenciosamente

** As fórmulas das matrizes não são inseridas da mesma maneira que as fórmulas 'padrão'. Em vez de pressionar apenas ENTER, você primeiro pressiona CTRL e SHIFT, e só então pressiona ENTER. Se você fez isso corretamente, você notará que o Excel coloca as chaves {} ao redor da fórmula (embora não tente inseri-las manualmente).