Busca o número máis próximo

Na práctica, moitas veces hai casos nos que ti e eu necesitamos atopar o valor máis próximo nun conxunto (táboa) en relación a un número determinado. Podería ser, por exemplo:

• Cálculo do desconto en función do volume.
  
• Cálculo do importe das bonificacións en función da implantación do plan.
  
• Cálculo das tarifas de envío en función da distancia.
• Selección de envases axeitados para mercadorías, etc.

Ademais, pode ser necesario redondear tanto cara arriba como para abaixo, dependendo da situación.

Hai varias formas, obvias e non tan obvias, de resolver tal problema. Vexámolos secuencialmente.

Para comezar, imaxinemos un provedor que fai descontos no maior, e a porcentaxe do desconto depende da cantidade de bens comprados. Por exemplo, cando se compran máis de 5 pezas, ofrécese un desconto do 2%, e cando se compra a partir de 20 pezas, xa o 6%, etc.

Como calcular de xeito rápido e bonito a porcentaxe de desconto ao introducir a cantidade de bens comprados?

Método 1: IF anidados

Un método da serie "o que hai para pensar: tes que saltar!". Usando funcións aniñadas IF (SI) para comprobar secuencialmente se o valor da cela cae en cada un dos intervalos e mostrar un desconto para o intervalo correspondente. Pero a fórmula neste caso pode resultar moi complicada: 

Creo que é obvio que depurar tal "boneca monstruosa" ou tentar engadirlle un par de condicións novas despois dun tempo é divertido.

Ademais, Microsoft Excel ten un límite de anidamento para a función IF: 7 veces nas versións antigas e 64 veces nas versións máis recentes. E se necesitas máis?

Método 2. BUSCAR V con vista de intervalos

Este método é moito máis compacto. Para calcular a porcentaxe de desconto, usa a función lendaria VPR (BÚSQUEDA V) en modo de busca aproximada:

onde

• B4 – o valor da cantidade de mercadorías na primeira transacción para a que buscamos un desconto
  
• $G$4:$H$8 – unha ligazón á táboa de descontos – sen “cabeceira” e cos enderezos fixados co signo $.
  
• 2 — o número ordinal da columna da táboa de descontos da que queremos obter o valor de desconto
• TRUE – aquí é onde está enterrado o “can”. Se como último argumento da función VPR especificar MENTIRO (FALSO) ou 0, entón a función buscará coincidencia estrita na columna da cantidade (e no noso caso dará un erro #N/A, xa que non existe o valor 49 na táboa de descontos). Pero se en cambio MENTIRO escribir TRUE (VERDADEIRO) ou 1, entón a función non buscará o exacto, pero máis próximo máis pequeno valor e daranos a porcentaxe de desconto que necesitamos.

A desvantaxe deste método é a necesidade de ordenar a táboa de descontos en orde ascendente pola primeira columna. Se non hai tal clasificación (ou se fai en orde inversa), a nosa fórmula non funcionará:

En consecuencia, este enfoque só se pode usar para atopar o valor máis pequeno máis próximo. Se necesitas atopar o máis próximo máis grande, tes que usar un enfoque diferente.

Método 3. Busca o maior máis próximo mediante as funcións INDEX e MATCH

Agora vexamos o noso problema dende o outro lado. Supoñamos que vendemos varios modelos de bombas industriais de varias capacidades. A táboa de vendas da esquerda mostra a potencia requirida polo cliente. Necesitamos seleccionar unha bomba da potencia máxima ou igual máis próxima, pero non inferior á esixida polo proxecto.

A función VLOOKUP non axudará aquí, polo que terás que usar o seu analóxico: unha morea de funcións INDEX (ÍNDICE) e MÁIS EXPOSTA (Coincidir):

Aquí, a función MATCH co último argumento -1 funciona no modo de atopar o maior valor máis próximo e, a continuación, a función INDEX extrae o nome do modelo que necesitamos da columna adxacente.

Método 4. Nova función VER (XLOOKUP)

Se tes unha versión de Office 365 con todas as actualizacións instaladas, en lugar de BUSCAR V (BÚSQUEDA V) pode usar o seu analóxico: a función VER (BUSCAR XL), que xa analizei polo miúdo:

Aquí:

• B4 – o valor inicial da cantidade do produto para o que buscamos un desconto
• $G$4:$G$8 – o rango onde buscamos partidos
  
• $H$4:$H$8 – o intervalo de resultados desde o que quere devolver o desconto
• cuarto argumento (-1) inclúe a busca do número máis pequeno máis próximo que queremos en lugar dunha coincidencia exacta.
  

As vantaxes deste método son que non hai necesidade de ordenar a táboa de descontos e a posibilidade de buscar, se é necesario, non só o valor máis próximo máis pequeno, senón tamén o valor máis próximo. O último argumento neste caso será 1.

Pero, desafortunadamente, aínda non todos teñen esta función: só os propietarios felices de Office 365.

Método 5. Power Query

Se aínda non estás familiarizado co poderoso e totalmente gratuíto complemento Power Query para Excel, estás aquí. Se xa estás familiarizado, intentemos usalo para resolver o noso problema.

Fagamos primeiro un traballo preparatorio:

1. Imos converter as nosas táboas de orixe en dinámicas (intelixentes) usando un atallo de teclado Ctrl+T ou equipo Inicio - Formato como unha táboa (Inicio — Formato como táboa).
2. Para máis claridade, poñémoslles nomes. De vendas и Descontos aba Construtor (Deseño).
3. Cargue cada unha das táboas á súa vez en Power Query usando o botón De Táboa/Range aba Data (Datos: da táboa/rango). Nas versións recentes de Excel, este botón cambiou o nome a Con follas (Da folla).
   
4. Se as táboas teñen diferentes nomes de columnas con cantidades, como no noso exemplo ("Cantidade de mercadorías" e "Cantidade de..."), entón hai que renomearlles en Power Query e denominarse igual.
5. Despois diso, pode volver a Excel seleccionando o comando na xanela do editor de Power Query Inicio — Pechar e cargar — Pechar e cargar en… (Inicio — Pechar e cargar — Pechar e cargar en…) e despois opción Só ten que crear unha conexión (Só crear conexión).

   
   

6. Despois comeza o máis interesante. Se tes experiencia en Power Query, supoño que a outra liña de pensamento debería estar na dirección de fusionar estas dúas táboas cunha consulta de unión (fusionar) á BUSCAR V, como foi o caso no método anterior. De feito, necesitaremos combinarnos no modo de engadir, o que non é nada obvio a primeira vista. Seleccione na pestana de Excel Datos - Obter datos - Combinar solicitudes - Engadir (Datos — Obter datos — Combinar consultas — Anexar) e despois as nosas mesas De vendas и Descontos na xanela que aparece:

   

7. Despois de premer OK as nosas táboas estarán pegadas nun único todo, unha debaixo das outras. Teña en conta que as columnas coa cantidade de mercadorías nestas táboas caían unhas debaixo das outras, porque. teñen o mesmo nome:

   

8. Se a secuencia orixinal de filas da táboa de vendas é importante para ti, para que despois de todas as transformacións posteriores poidas restaurala, engade unha columna numerada á nosa táboa usando o comando Engadir unha columna - Columna de índice (Engadir columna — Columna de índice). Se a secuencia de liñas non che importa, podes omitir este paso.
9. Agora, usando a lista despregable na cabeceira da táboa, ordénaa por columna cantidade Ascendente:

   

10. E o truco principal: fai clic co botón dereito na cabeceira da columna desconto escolle un equipo Encher - Abaixo (Encher - Abaixo). Celas baleiras con nulo enchéranse automaticamente cos valores de desconto anteriores:

    

11. Queda por restaurar a secuencia orixinal de filas ordenando por columna Índice (pode eliminalo con seguridade máis tarde) e desfacerse das liñas innecesarias cun filtro nulo por columna Código de transacción:

    
    

• Usando a función BUSCAR V para buscar e buscar datos
• Usar BUSCARV (BUSCARV) distingue entre maiúsculas e minúsculas
• XNUMXD BUSCAR V (BUSCAR V)