Encontrando o próximo valor mais alto em uma coluna

Question

Eu tenho duas colunas, A e B . A coluna B tem valores que preciso procurar na coluna A . No entanto, não preciso encontrar o valor exato exato, preciso do próximo valor mais alto.

Por exemplo:

Column A    Column B   

   2           3
   4           4
   5           5
   7           6
   8           8
   9           9

Portanto, para o valor 5 na coluna B , desejo retornar 7 da coluna A .

Eu acho que provavelmente preciso de alguma forma de função lookup / index-match, mas eu não fui capaz de escrever a fórmula sozinho.

    

Answer 1

Ordenado

A fórmula mais simples é para o caso em que a coluna A é classificada em ordem crescente:

InsiraaseguintefórmulaemC1ectrl-enter/copy-paste/fill-down/preenchimentoautomáticonorestantedacolunadatabela:

=INDEX(A:A,1+MATCH(B1,A:A,1))

Explicação:

O 1 como o terceiro argumento de MATCH() significa que ele encontra o maior valor que é menor ou igual ao primeiro argumento. Adicionar 1 a esse índice resulta no índice do próximo número mais alto. A função INDEX() , em seguida, extrai o número.

Observe que adicionei um valor extra no final da coluna A . Isto é para o caso especial em que não há um valor maior seguinte.

Não sorteado

Para o caso em que a coluna A não está classificada (também funciona se classificada), a fórmula é um pouco mais complicada:

Matriz entra ( Ctrl + Desloca + Enter ) a seguinte fórmula em C1 e copia-cola / fill-down no resto da coluna da tabela (não se esqueça de remover o { e } ):

{=SMALL(IF($A$1:$A$6>B1,$A$1:$A$6),1)}

Explicação:

A função SMALL(array,n) retorna o enésimo menor valor da matriz, ignorando valores booleanos . Como o padrão para o terceiro argumento da função IF() é FALSE , somente os valores maiores que o valor na coluna B são verificados, resultando no próximo valor mais alto.

Observe que um valor especial de término para a coluna A não é obrigatório, pois um erro #NUM! é o resultado se não houver valores na coluna A maior que o valor na coluna B .

Finalmente, como a aventurina apontou, existe uma fórmula alternativa e similar que funciona independentemente da classificação (mas com uma advertência importante).

Para o Excel 2016 +:

=MINIFS($A$1:$A$6,$A$1:$A$6,">"&B1)

Isso funciona porque a função MINIFS() filtra os valores que não correspondem ao (s) critério (s) antes de extrair o valor mínimo.

Para versões anteriores do Excel:

{=MIN(IF($A$1:$A$6>B1,$A$1:$A$6))}

Isso funciona pelo mesmo motivo que a função SMALL() - ignora valores booleanos gerados pela função IF() .

Advertência:

As fórmulas =MINIFS() e {=MIN(IF())} não funcionarão corretamente se um zero puder ser o próximo valor mais alto correto, pois o zero também será retornado quando não houver nenhum próximo valor mais alto. (Esta é a mesma razão para adicionar um valor extra no final da coluna A para a primeira fórmula - essa fórmula também retorna um zero se não houver valores mais altos.)

    

Answer 2

Você pode usar, por exemplo, a função de matriz {=MIN(IF(A1:A6 > B1; A1:A6))} ou {=MIN(IF(A1:A6 > B1; A1:A6; 1000))} (com 1000 como valor de fallback).

Leva o mínimo de todos os valores da coluna A que são maiores que o valor da célula atual da coluna B (aqui B1 ). Portanto, nenhuma das colunas deve ser classificada.

Com o Excel > = 2016, você também pode usar a função MINIFS .

Note que as funções do array devem ser inseridas pressionando Ctrl+Shift+Enter .