Fórmula para retornar o valor de uma coluna quando a condição é atendida pela primeira vez em outra

Question

Exemplo:

Dada uma planilha com quatro colunas - A, B, C e D, e a linha 1 é uma linha de cabeçalho...

A2=1
B2=(Inteiro positivo aleatório)
C2=(Outro número inteiro positivo aleatório)

A3=A2+1
B3=B2-C2
A4=A3+1
B4=B3-C2

...

D2 é a célula problemática. Em D2, quero retornar o valor da coluna A que corresponde à primeira instância de um valor na coluna B que é <=0.

Answer 1

Na mesma linha da resposta de Paulo, eu usaria IndexeMatch

=INDEX($A:$A,MATCH(0,$B:$B,-1)+1)

1. Use colunas inteiras para que não precise ser ajustado à quantidade de dados na coluna.
2. Use Indexem vez de Offsetdesde que Offseté volátil e Indexnão é. (As funções voláteis são recalculadas em cada cálculo de planilha, enquanto as funções não voláteis são recalculadas apenas quando os dados referenciados são alterados. Muitas funções voláteis podem tornar o Excel lento.)

Answer 2

Você pode usar MATCH para obter o que precisa.

MATCH(0,B2:B6,-1)+1procurará B2:B6o primeiro valor que seja igual ou inferior ao número 0e retornará sua posição relativa. Ele é indexado a partir de zero, então adicionamos um para corresponder ao seu índice.

Indexado a partir de zero significa que a linha B2 é 0, B3 é 1, B4 é 2 e B5 é 3. Portanto, MATCH retornará “3”.

Você tem seu próprio índice na coluna A e, se ele não for sequencial, contiver outros valores ou não começar em 1, você poderá usar o deslocamento para acessá-lo:

=OFFSET(A2,MATCH(0,B2:B6,-1),0)

Portanto, isso está usando o resultado do MATCH para fazer a contagem regressiva de A2 para encontrar o valor naquela posição da linha.

Answer 3

você não poderia calcular isso usando apenas B2 e C2?

=CEILING(B2/C2,1)+1

De acordo com meu comentário na resposta de Chris -versão atualizada

=INDEX($A:$A,MATCH(TRUE,INDEX($B:$B<=0,0),0))

Isso deve evitar o problema inerente à sugestão de Chris, de que você obtém o valor errado quando o primeiro valor <=0 é o próprio 0.

A parte $B:$B<0retorna uma "matriz" de TRUE/FALSEvalores, o primeiro TRUEobviamente coincidindo com o valor da primeira coluna B <0, MATCH então encontra a posição dessa primeira instância e INDEX obtém o valor correspondente da coluna A.

O segundo INDEX existe apenas para evitar a "entrada no array" - funciona sem isso, ou seja

=INDEX($A:$A,MATCH(TRUE,$B:$B<=0,0))

....... mas essa versão precisaria ser "inserida na matriz" - ou seja, confirmada com CTRL+SHIFT+ENTER.

É um pouco mais ineficiente que as sugestões anteriores, usar a coluna inteira (e isso não funciona no Excel 2003 ou anterior - nessas versões é necessário usar um intervalo específico).

Observe que MATCH com "tipo de correspondência" de -1 conforme sugestão de Chris precisa ter valores decrescentes na coluna B - esta fórmula funciona independentemente da coluna B ser ordenada.