Encontrando um valor zero em uma coluna e o valor correspondente na quinta coluna da linha anterior

Question

Eu tenho uma tabela com 5 colunas. As linhas depois de algum ponto terão um valor zero. Preciso obter o valor da linha anterior da coluna 5.
Por exemplo; aqui depois da quarta linha, todo o valor se torna zero. Eu então preciso que o valor 89 apareça em alguma célula designada fora desta tabela.

Year    Amt1    Amt 2   Amt 3    Amt 5
2012         5       6      17      48
2013        15       0      72      83
2014        25      63       0      12
2015         0      56      75      89
   0         0       0       0       0
   0         0       0       0       0
   0         0       0       0       0

    

Answer 1

Uma fórmula adequada ao que você está procurando seria:

=OFFSET(E2,MATCH(0,E:E,0)-3,0)

E2 é o primeiro valor na coluna 5 (48)

OFFSET com a célula no início da coluna e abaixo de MATCH(0,E:E,0)-3 .

MATCH(0,E:E,0) retornará a linha na qual poderá encontrar a primeira ocorrência de 0 (os parâmetros são: procure 0 na coluna E:E e use 0 para indicar a correspondência exata).

O -3 é responsável pelo cabeçalho, pela primeira célula e sobe uma célula porque MATCH retornará a posição de 0 , não a da célula logo acima dela.

O último parâmetro em OFFSET é o número de colunas que o intervalo deve mover e, como já estamos na coluna E, não há movimento a esse respeito.

EDIT: Como a coluna Year é zeros perfeitos (a coluna 5 contém números muito pequenos arredondados para 1), você pode usar isto:

=OFFSET(E2,MATCH(0,A:A,0)-3,0)

Entendendo o OFFSET

Digamos que você use a fórmula:

=OFFSET(A1, 1, 1)

OFFSET retornará a célula que é encontrada 1 linha abaixo e 1 coluna após a célula A1 , significando célula B2 .

Da mesma forma, =OFFSET(A1, 3, 1) fornecerá a célula B4 .

Na verdade, existem mais opções que você pode ter em OFFSET , ou seja, a altura e a largura do intervalo resultante.

=OFFSET(A1, 1, 1, 2, 3)

OFFSET retornará a célula que é encontrada 1 linha abaixo e 1 coluna após a célula A1 , significando célula B2 . Em seguida, a altura do resultado será 2 e a largura 3, o que significa que retornará o intervalo B2:D3 (se você selecionar esse intervalo com o mouse, verá duas células de altura e três de largura). No entanto, como este é um array, você receberá um #N/A com essa fórmula. Você ainda pode realizar operações com esse array, como VLOOKUP ou INDEX , SUM , etc.