Fórmula para retornar o valor de uma coluna quando a condição é satisfeita 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 inteiro positivo aleatório)

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

...

D2 é a célula do problema. Em D2, desejo 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 Paul, eu usaria Index e Match

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

1. Use colunas inteiras para que não precise ser ajustado para a quantidade de dados na coluna.
2. Use Index em vez de Offset , pois Offset é volátil e Index não é. (Funções voláteis são recalculadas em cada cálculo de folha, enquanto funções não voláteis são recalculadas apenas quando os dados referenciados mudam. Muitas funções voláteis podem atrasar o Excel.)

Answer 2

Você pode usar MATCH para conseguir o que precisa.

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

Indexado 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 isso não for seqüencial, ou contiver outros valores, ou não começar em 1, você poderá usar o deslocamento para chegar até ele:

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

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

    

Answer 3

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

=CEILING(B2/C2,1)+1

Conforme 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, pois você obtém o valor errado quando o primeiro valor < = 0 é 0 em si.

A parte $B:$B<0 retorna um "array" de valores de TRUE/FALSE , o primeiro TRUE obviamente coincidindo com o valor da primeira coluna B < 0, MATCH então encontra a posição daquela primeira instância e INDEX então obtém o valor correspondente da coluna A.

O segundo INDEX está lá apenas para evitar "entrada de array" - funciona sem isso, ou seja,

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

....... mas essa versão precisaria ser "matriz inserida" - isto é, confirmada com CTRL+SHIFT+ENTER .

É um pouco mais ineficiente do que as sugestões anteriores, usando 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 a MATCH com "match type" de -1, conforme a sugestão de Chris, precisa ter valores descendentes na coluna B - essa fórmula funciona, mas a coluna B é ordenada.