☰

Encontrando o próximo valor mais alto em uma coluna

tag-icon • tag-icon microsoft-excel worksheet-function lookup
Encontrando o próximo valor mais alto em uma coluna

Eu tenho duas colunas Ae B. Column Btem valores que preciso procurar em Column A. No entanto, não preciso encontrar o valor correspondente 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

Então, para o valor 5em column B, quero retornar 7de column A.

Acho que provavelmente preciso de alguma forma de função de pesquisa/correspondência de índice, mas não consegui escrever a fórmula sozinho.

Responder1

Classificado

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

Captura de tela da planilha

Insira a seguinte fórmula C1e pressione Ctrl-enter/copiar-colar/preencher/preencher automaticamente no restante da coluna da tabela:

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

Explicação:

O 1terceiro argumento MATCH()significa que encontra o maior valor menor ou igual ao primeiro argumento. Adicionar 1a esse índice resulta no índice do próximo número mais alto. A INDEX()função então 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á valor imediatamente superior.

não triados

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

Array insira ( Ctrl+ Shift+ Enter) a seguinte fórmula C1e copie e cole/preencha no restante da coluna da tabela (não se esqueça de remover o {and }):

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

Explicação:

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

Observe que um valor final especial para column Anão é necessário, pois #NUM!ocorrerá um erro se não houver valores em column Amaiores que o valor em column B.

Finalmente, como salientou aventurina, existe uma fórmula alternativa e semelhante que funciona independentemente da classificação (mas com uma ressalva importante).

Para Excel 2016+:

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

Isso funciona porque a MINIFS()função filtra os valores que não correspondem aos critérios 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 SMALL()função - ignora os valores booleanos gerados pela IF()função.

Embargo:

Ambas 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 zero também é retornado quando háénenhum próximo valor mais alto. (Este é o mesmo motivo para adicionar um valor extra no final da coluna Apara a primeira fórmula - essa fórmula também retorna zero se não houver valores mais altos.)

Responder2

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

Ele pega 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 Excel >= 2016 você também pode usar a MINIFSfunção.

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

informação relacionada