Encontrar el número más cercano

En la práctica, muy a menudo hay casos en los que tú y yo necesitamos encontrar el valor más cercano en un conjunto (tabla) en relación con un número dado. Podría ser, por ejemplo:

• Cálculo de descuento según volumen.
  
• Cálculo del importe de las bonificaciones en función de la ejecución del plan.
  
• Cálculo de tarifas de envío en función de la distancia.
• Selección de contenedores adecuados para mercancías, etc.

Además, puede ser necesario redondear tanto hacia arriba como hacia abajo, según la situación.

Hay varias formas, obvias y no tan obvias, de resolver este problema. Veámoslos secuencialmente.

Para empezar, imaginemos un proveedor que ofrece descuentos al por mayor, y el porcentaje de descuento depende de la cantidad de bienes comprados. Por ejemplo, al comprar más de 5 piezas, se otorga un descuento del 2%, y al comprar a partir de 20 piezas, ya el 6%, etc.

¿Cómo calcular rápida y bellamente el porcentaje de descuento al ingresar la cantidad de los bienes comprados?

Método 1: IF anidados

Un método de la serie "¿Qué hay para pensar? ¡Tienes que saltar!". Uso de funciones anidadas IF (SI) para verificar secuencialmente si el valor de la celda cae en cada uno de los intervalos y mostrar un descuento para el rango correspondiente. Pero la fórmula en este caso puede resultar muy engorrosa: 

Creo que es obvio que depurar una "muñeca monstruosa" o tratar de agregarle un par de nuevas condiciones después de un tiempo es divertido.

Además, Microsoft Excel tiene un límite de anidamiento para la función SI: 7 veces en versiones anteriores y 64 veces en versiones más nuevas. ¿Qué pasa si necesitas más?

Método 2. BUSCARV con vista de intervalo

Este método es mucho más compacto. Para calcular el porcentaje de descuento, use la función legendaria VPR (VLOOKUP) en modo de búsqueda aproximada:

donde

• B4 – el valor de la cantidad de bienes en la primera transacción para la que buscamos un descuento
  
• $G$4:$H$8 – un enlace a la tabla de descuentos – sin “encabezado” y con las direcciones fijadas con el signo $.
  
• 2 — el número ordinal de la columna en la tabla de descuentos de la que queremos obtener el valor del descuento
• VERDADERO – aquí es donde está enterrado el “perro”. Si como último argumento de la función VPR especificar MENTIRA (FALSO) o 0, entonces la función buscará coincidencia estricta en la columna de cantidad (y en nuestro caso dará error #N/A, ya que no hay valor 49 en la tabla de descuento). Pero si en cambio MENTIRA escribir VERDADERO (CIERTO) o 1, entonces la función buscará no el exacto, sino más pequeño más cercano valor y nos dará el porcentaje de descuento que necesitamos.

La desventaja de este método es la necesidad de ordenar la tabla de descuentos en orden ascendente por la primera columna. Si no existe tal clasificación (o se realiza en orden inverso), entonces nuestra fórmula no funcionará:

En consecuencia, este enfoque solo se puede utilizar para encontrar el valor más pequeño más cercano. Si necesita encontrar el más grande más cercano, entonces debe usar un enfoque diferente.

Método 3. Encontrar el mayor más cercano usando las funciones ÍNDICE y COINCIDIR

Ahora veamos nuestro problema desde el otro lado. Supongamos que vendemos varios modelos de bombas industriales de varias capacidades. La tabla de ventas de la izquierda muestra la potencia requerida por el cliente. Necesitamos seleccionar una bomba de la potencia máxima o igual más cercana, pero no menos de lo que requiere el proyecto.

La función BUSCARV no ayudará aquí, por lo que tendrá que usar su análogo: un montón de funciones ÍNDICE (ÍNDICE) y MÁS EXPUESTO (JUEGO):

Aquí, la función COINCIDIR con el último argumento -1 funciona en el modo de encontrar el valor más grande más cercano, y la función ÍNDICE luego extrae el nombre del modelo que necesitamos de la columna adyacente.

Método 4. Nueva función VER (BUSCARX)

Si tiene una versión de Office 365 con todas las actualizaciones instaladas, en lugar de BUSCARV (VLOOKUP) puede usar su análogo: la función VIEW (BUSCARX), que ya he analizado en detalle:

Aquí:

• B4 – el valor inicial de la cantidad del producto para el que buscamos un descuento
• $G$4:$G$8 – el rango donde estamos buscando coincidencias
  
• $H$4:$H$8 – el rango de resultados a partir del cual desea devolver el descuento
• cuarto argumento (-1) incluye la búsqueda del número más pequeño más cercano que queramos en lugar de una coincidencia exacta.
  

Las ventajas de este método son que no hay necesidad de ordenar la tabla de descuento y la capacidad de buscar, si es necesario, no solo el valor más cercano más pequeño, sino también el más cercano. El último argumento en este caso será 1.

Pero, desafortunadamente, no todos tienen esta función todavía, solo los felices propietarios de Office 365.

Método 5. Consulta de poder

Si aún no está familiarizado con el poderoso y completamente gratuito complemento Power Query para Excel, entonces está aquí. Si ya está familiarizado, intentemos usarlo para resolver nuestro problema.

Hagamos un poco de trabajo preparatorio primero:

1. Convirtamos nuestras tablas de origen en dinámicas (inteligentes) usando un atajo de teclado Ctrl+T o equipo Inicio – Formato como tabla (Inicio — Formatear como tabla).
2. Para mayor claridad, vamos a darles nombres. Ventas и Descuentos de la pestaña. Constructor (Diseño).
3. Cargue cada una de las tablas a su vez en Power Query usando el botón Desde tabla/rango de la pestaña. Datos (Datos — De la tabla/rango). En versiones recientes de Excel, este botón ha cambiado de nombre a con hojas (De la hoja).
   
4. Si las tablas tienen diferentes nombres de columna con cantidades, como en nuestro ejemplo ("Cantidad de bienes" y "Cantidad de ..."), entonces deben cambiarse de nombre en Power Query y tener el mismo nombre.
5. Después de eso, puede volver a Excel seleccionando el comando en la ventana del editor de Power Query Inicio — Cerrar y cargar — Cerrar y cargar en… (Inicio — Cerrar y cargar — Cerrar y cargar a…) y luego opcion Solo crea una conexión (Solo crear conexión).

   
   

6. Entonces comienza lo más interesante. Si tiene experiencia en Power Query, entonces asumo que la línea de pensamiento adicional debe estar en la dirección de fusionar estas dos tablas con una consulta conjunta (fusionar) a la VLOOKUP, como fue el caso en el método anterior. De hecho, necesitaremos fusionarnos en modo agregar, lo cual no es nada obvio a primera vista. Seleccionar en la pestaña de Excel Datos – Obtener datos – Combinar solicitudes – Agregar (Datos — Obtener datos — Combinar consultas — Agregar) y luego nuestras mesas Ventas и Descuentos en la ventana que aparece:

   

7. Después de hacer clic en OK nuestras mesas se pegarán en un solo conjunto, una debajo de la otra. Tenga en cuenta que las columnas con la cantidad de bienes en estas tablas se encuentran una debajo de la otra, porque. tienen el mismo nombre:

   

8. Si la secuencia original de filas en la tabla de ventas es importante para usted, para que después de todas las transformaciones posteriores pueda restaurarla, agregue una columna numerada a nuestra tabla usando el comando Agregar una columna: columna de índice (Agregar columna — Columna de índice). Si la secuencia de líneas no le importa, puede omitir este paso.
9. Ahora, usando la lista desplegable en el encabezado de la tabla, ordénela por columna Cantidad Ascendente:

   

10. Y el truco principal: haga clic derecho en el encabezado de la columna El descuento elige un equipo Llenar hacia abajo (Llenar hacia abajo). Celdas vacías con nulo rellenado automáticamente con los valores de descuento anteriores:

    

11. Queda por restaurar la secuencia original de filas ordenando por columna Home (puede eliminarlo de forma segura más tarde) y deshágase de las líneas innecesarias con un filtro nulo por columna Código de transacción:

    
    

• Uso de la función BUSCARV para buscar y buscar datos
• El uso de BUSCARV (BUSCARV) distingue entre mayúsculas y minúsculas
• XNUMXD BUSCARV (BUSCARV)