Encontrando o número mais próximo

Na prática, muitas vezes há casos em que você e eu precisamos encontrar o valor mais próximo em um conjunto (tabela) em relação a um determinado número. Pode ser, por exemplo:

• Cálculo do desconto em função do volume.
  
• Cálculo do valor dos bônus dependendo da implementação do plano.
  
• Cálculo das taxas de envio em função da distância.
• Seleção de recipientes adequados para mercadorias, etc.

Além disso, o arredondamento pode ser necessário tanto para cima quanto para baixo, dependendo da situação.

Existem várias maneiras – óbvias e não tão óbvias – de resolver tal problema. Vamos olhar para eles sequencialmente.

Para começar, imaginemos um fornecedor que dá descontos no atacado, e o percentual do desconto depende da quantidade de mercadorias adquiridas. Por exemplo, na compra de mais de 5 peças, é dado um desconto de 2%, e na compra de 20 peças – já 6%, etc.

Como calcular de forma rápida e bonita a porcentagem de desconto ao inserir a quantidade dos produtos comprados?

Método 1: IFs aninhados

Um método da série “o que há para pensar – você precisa pular!”. Usando funções aninhadas IF (E SE) para verificar sequencialmente se o valor da célula se enquadra em cada um dos intervalos e exibir um desconto para o intervalo correspondente. Mas a fórmula neste caso pode ser muito complicada: 

Eu acho que é óbvio que depurar uma “boneca monstro” ou tentar adicionar algumas novas condições a ela depois de algum tempo é divertido.

Além disso, o Microsoft Excel tem um limite de aninhamento para a função SE – 7 vezes nas versões mais antigas e 64 vezes nas versões mais recentes. E se você precisar de mais?

Método 2. PROCV com visualização de intervalo

Este método é muito mais compacto. Para calcular a porcentagem de desconto, use a função lendária VPR (PROCV) no modo de busca aproximada:

onde

• B4 – o valor da quantidade de mercadorias na primeira transação para a qual procuramos um desconto
  
• $G$4:$H$8 – um link para a tabela de descontos – sem “cabeçalho” e com os endereços fixados com o símbolo $.
  
• 2 — o número ordinal da coluna na tabela de desconto da qual queremos obter o valor do desconto
• VERDADEIRO – é aqui que o “cachorro” está enterrado. Se como o último argumento da função VPR especificamos DEITADO (FALSO) ou 0, então a função irá procurar correspondência rigorosa na coluna quantidade (e no nosso caso dará um erro #N/A, pois não existe o valor 49 na tabela de descontos). Mas se em vez disso DEITADO escrever VERDADEIRO (VERDADE) ou 1, então a função não procurará o exato, mas mais próximo menor valor e nos dará a porcentagem de desconto que precisamos.

A desvantagem deste método é a necessidade de ordenar a tabela de descontos em ordem crescente pela primeira coluna. Se não houver essa classificação (ou for feita na ordem inversa), nossa fórmula não funcionará:

Assim, esta abordagem só pode ser usada para encontrar o menor valor mais próximo. Se você precisar encontrar o maior mais próximo, precisará usar uma abordagem diferente.

Método 3. Encontrando o maior mais próximo usando as funções INDEX e MATCH

Agora vamos olhar para o nosso problema do outro lado. Suponha que vendemos vários modelos de bombas industriais de várias capacidades. A tabela de vendas à esquerda mostra a potência exigida pelo cliente. Precisamos selecionar uma bomba de potência máxima ou igual mais próxima, mas não inferior à exigida pelo projeto.

A função VLOOKUP não ajudará aqui, então você terá que usar seu analógico - um monte de funções INDEX (ÍNDICE) e MAIS EXPOSTOS (COMBINE):

Aqui, a função MATCH com o último argumento -1 funciona no modo de encontrar o maior valor mais próximo, e a função INDEX então extrai o nome do modelo que precisamos da coluna adjacente.

Método 4. Nova função VIEW (XLOOKUP)

Se você tiver uma versão do Office 365 com todas as atualizações instaladas, em vez de VLOOKUP (PROCV) você pode usar seu analógico - a função VIEW (XLOOKUP), que já analisei em detalhes:

Aqui:

• B4 – o valor inicial da quantidade do produto para o qual procuramos um desconto
• $ G $ 4: $ G $ 8 – o intervalo onde procuramos correspondências
  
• $H$4:$H$8 – o intervalo de resultados do qual você deseja devolver o desconto
• quarto argumento (-1) inclui a pesquisa do menor número mais próximo que desejamos em vez de uma correspondência exata.
  

As vantagens deste método são que não há necessidade de ordenar a tabela de descontos e a possibilidade de pesquisar, se necessário, não apenas o menor valor mais próximo, mas também o maior valor mais próximo. O último argumento neste caso será 1.

Mas, infelizmente, nem todos têm esse recurso ainda – apenas proprietários felizes do Office 365.

Método 5. Consulta de energia

Se você ainda não está familiarizado com o suplemento Power Query poderoso e totalmente gratuito para Excel, então você está aqui. Se você já está familiarizado, vamos tentar usá-lo para resolver nosso problema.

Vamos fazer um trabalho preparatório primeiro:

1. Vamos converter nossas tabelas de origem para dinâmicas (inteligentes) usando um atalho de teclado Ctrl+T ou equipe Home – Formatar como uma tabela (Início — Formatar como Tabela).
2. Para maior clareza, vamos dar-lhes nomes. Vendas и Alugueres aba Construtor (Desenhar).
3. Carregue cada uma das tabelas no Power Query usando o botão Da tabela/intervalo aba Data (Dados — Da tabela/intervalo). Nas versões recentes do Excel, este botão foi renomeado para Com folhas (Da folha).
   
4. Se as tabelas tiverem nomes de colunas diferentes com quantidades, como em nosso exemplo (“Quantidade de mercadorias” e “Quantidade de …”), elas devem ser renomeadas no Power Query e nomeadas da mesma forma.
5. Depois disso, você pode retornar ao Excel selecionando o comando na janela do editor do Power Query Home — Fechar e carregar — Fechar e carregar em… (Início — Fechar e carregar — Fechar e carregar para…) e depois opção Basta criar uma conexão (Apenas criar conexão).

   
   

6. Então começa o mais interessante. Se você tem experiência em Power Query, suponho que a linha de pensamento adicional deve ser na direção de mesclar essas duas tabelas com uma consulta de junção (mesclar) a la VLOOKUP, como foi o caso do método anterior. Na verdade, precisaremos mesclar no modo de adição, o que não é nada óbvio à primeira vista. Selecione na guia Excel Dados – Obter Dados – Combinar Solicitações – Adicionar (Dados — Obter dados — Combinar consultas — Anexar) e então nossas mesas Vendas и Alugueres na janela que aparece:

   

7. Depois de clicar em OK nossas mesas serão coladas em um único todo – uma embaixo da outra. Observe que as colunas com a quantidade de mercadorias nessas tabelas caíram umas sobre as outras, porque. têm o mesmo nome:

   

8. Se a sequência original de linhas na tabela de vendas for importante para você, para que, após todas as transformações subsequentes, você possa restaurá-la, adicione uma coluna numerada à nossa tabela usando o comando Adicionando uma Coluna – Coluna de Índice (Adicionar coluna — coluna de índice). Se a sequência de linhas não for importante para você, pule esta etapa.
9. Agora, usando a lista suspensa no cabeçalho da tabela, classifique-a por coluna Qtd. Ascendente:

   

10. E o truque principal: clique com o botão direito do mouse no cabeçalho da coluna Desconto escolha um time Preencher (Preencher). Células vazias com nulo preenchido automaticamente com os valores de desconto anteriores:

    

11. Resta restaurar a sequência original de linhas classificando por coluna Índice (você pode excluí-lo com segurança mais tarde) e se livrar de linhas desnecessárias com um filtro nulo por coluna Código de transação:

    
    

• Usando a função PROCV para pesquisar e pesquisar dados
• O uso de VLOOKUP (VLOOKUP) diferencia maiúsculas de minúsculas
• XNUMXD PROCV (PROCV)