Desafio: a melhor maneira de selecionar os dois números mais semelhantes/mais próximos em um conjunto?

Se você tiver o Office 365, poderá usar o seguinte:

observe que, se houver vários pares mais próximos, isso retornará apenas o par com o valor absoluto mais baixo. Se isso for um problema, uma fórmula diferente pode ser criada

=LET(x,SORT(myRange),
          y,COUNT(x)-1,
          z,INDEX(x,SEQUENCE(y-1,,2)),
          a,INDEX(x,SEQUENCE(y-1)),
          b,z-a,
          c,MATCH(MIN(b),b,0),
          INDEX(x,SEQUENCE(2,,c)))

• Classifique o intervalo
• Subtraia cada par começando com linha2 - linha1
  • como o intervalo é classificado, o resultado sempre será positivo
• Determine a menor diferença
• Devolva o par correspondente

Se você não tiver o Office 365 com essas funções, poderá escrever uma fórmula muito complicada; use várias colunas auxiliares para obter os mesmos resultados; ou escreva uma UDF VBA.

Se você tiver vários pares atendendo aos critérios, para retornar TODOS eles, tente esta fórmula:

=LET(x,SORT(myRange),
          y,COUNT(x)-1,
          z,INDEX(x,SEQUENCE(y-1,,2)),
          a,INDEX(x,SEQUENCE(y-1)),
          b,z-a,
         arr1,FILTER(a,MIN(b)=b),
         arr2,FILTER(z,MIN(b)=b),
         xml,"<t><s>" &TEXTJOIN("</s><s>",TRUE, arr1 & "</s><s>" & arr2)&"</s></t>",
         FILTERXML(xml,"//s"))

Para versões anteriores do Excel, uma maneira de fazer isso, sem desenvolver uma fórmula extremamente longa e desajeitada, é usar fórmulas Name d.

Nos nomes abaixo, myRangerefere-se à fonte de dados, que deve estar em uma única coluna, e os outros nomes derivam dessa

| myRange | Refers to: | =Sheet1!$A$1:$A$16                                                              |
|---------|------------|---------------------------------------------------------------------------------|
| x       | Refers to: | =SMALL(myRange,ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,COUNT(myRange))))   |
| y       | Refers to: | =ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,COUNT(x)-1))                      |
| b       | Refers to: | =INDEX(x,y+1)-INDEX(x,y)                                                        |
| minB    | Refers to: | =MIN(b)                                                                         |
| rws     | Refers to: | =ROW(INDEX(Sheet1!$A:$A,MATCH(minB,b,0)):INDEX(Sheet1!$A:$A,MATCH(minB,b,0)+1)) |

Como os dados são numéricos, podemos SORTusar a SMALLfunção. As várias ROW(INDEX:...construções imitam a SEQUENCEfunção.

Dadas as fórmulas nomeadas acima, para retornar os mesmos resultados que a LETfunção acima, agora você pode usar:

=INDEX(x,rws)

Não tenho 100% de certeza se funcionará em versões mais antigas. Eu me deparei com uma estranheza em que não consegui obter a resposta adequada =MIN(b)quando digitada na planilha; mas ele retorna a resposta correta quando inserido como uma Namefórmula d (onde minBse refere a Min(b). Portanto, não tenho certeza sobre as versões mais antigas do Excel.

Também pode ser o caso em versões mais antigas do Excel que, para inserir/confirmar esta fórmula de matriz , talvez seja necessário manter pressionado ctrl+ shiftenquanto pressiona enter. Se você fizer isso corretamente, o Excel colocará chaves {...}ao redor da fórmula vista na barra de fórmulas.