Optimización de Rutas de Reparto con Excel

En el ámbito de la logística y la gestión de la cadena de suministro, la eficiencia es clave. Tomar decisiones rápidas y basadas en datos puede significar un ahorro considerable en tiempo y dinero. Microsoft Excel, con sus potentes funciones de cálculo matricial, ofrece herramientas excepcionales para analizar y resolver problemas complejos, como la optimización de rutas de reparto.

La fórmula que exploraremos aquí combina varias funciones para encontrar el coste mínimo para una ruta específica, siempre que dicho coste no supere un umbral predefinido. Es una solución elegante que filtra datos bajo múltiples criterios para devolver un valor único y óptimo. Aunque la sintaxis presentada en la consulta puede ser una representación conceptual, la implementaremos usando las funciones modernas y más eficientes de Excel, como FILTRAR y MIN.

Sintaxis

Para encontrar el coste mínimo de una ruta específica que a su vez sea inferior a un valor máximo, utilizaremos una combinación de las funciones MIN y FILTRAR. Esta fórmula es dinámica y requiere una versión de Excel compatible con matrices dinámicas (Microsoft 365 o Excel 2021 y posteriores).

=MIN(FILTRAR(rango_costos, (rango_rutas = valor_deseado) * (rango_costos < valor_maximo)))

Esta fórmula funciona creando una matriz filtrada de costes que cumplen dos condiciones simultáneamente y luego extrae el valor más bajo de esa matriz resultante. A continuación, se detalla cada componente:

• MIN(…): Es la función principal que envuelve la lógica. Se encarga de calcular el valor mínimo del conjunto de datos que le proporciona la función FILTRAR.
• FILTRAR(rango_costos, …): Esta función genera una nueva matriz que contiene únicamente los valores de rango_costos que cumplen con los criterios especificados.
• rango_costos: Es el rango de celdas que contiene los costes de todas las rutas. De aquí se extraerá el resultado final.
• rango_rutas: Es el rango de celdas que contiene los nombres o identificadores de las rutas (ej: «Ruta Norte», «Ruta Sur»). Debe tener la misma dimensión que rango_costos.
• (rango_rutas = valor_deseado): Esta es la primera condición lógica. Compara cada celda del rango_rutas con el valor_deseado (ej: «Ruta Central»). El resultado es una matriz de valores VERDADERO (si coincide) y FALSO (si no coincide).
• (rango_costos < valor_maximo): Esta es la segunda condición lógica. Compara cada celda del rango_costos con el valor_maximo. El resultado es otra matriz de VERDADERO (si el coste es menor) y FALSO (si es mayor o igual).
• * (Operador de multiplicación): En el contexto de matrices lógicas, el asterisco actúa como un operador «Y» (AND). Multiplica las dos matrices de VERDADERO/FALSO. El resultado es una matriz de 1 (cuando ambas condiciones son VERDADERO) y 0 (en cualquier otro caso), que FILTRAR utiliza para incluir solo los valores correspondientes.

Ejemplos

Para ilustrar el uso de esta fórmula, imaginemos que gestionamos una pequeña empresa de mensajería y tenemos la siguiente tabla de datos con las diferentes opciones de envío para varias rutas.

Ejemplo 1: Encontrar el coste mínimo para la «Ruta Norte» por debajo de 120€

Queremos identificar la opción más económica para la «Ruta Norte», pero nuestro presupuesto máximo para esta operación es de 120€. La fórmula buscará en la tabla todas las entradas de «Ruta Norte», filtrará aquellas cuyo coste sea estrictamente menor a 120€ y nos devolverá el más bajo.

=MIN(FILTRAR(C2:C7; (A2:A7="Ruta Norte") * (C2:C7<120))) -> Resultado esperado: 110

La fórmula primero identifica las filas 2, 4 y 6 como «Ruta Norte». Luego, comprueba la condición de coste: 120 no es menor que 120, 110 sí lo es, y 115 también. De los costes que cumplen (110 y 115), devuelve el mínimo: 110.

Ejemplo 2: Gestionar casos sin resultados

¿Qué ocurre si buscamos el coste mínimo para la «Ruta Central» por debajo de 80€? Ninguna de las opciones para esta ruta cumple la condición. En este caso, la función FILTRAR devolvería un error #¡CALC! porque no encuentra resultados. Para evitar este error y mostrar un mensaje más amigable, podemos anidar la fórmula dentro de SI.ERROR.

=SI.ERROR(MIN(FILTRAR(C2:C7; (A2:A7="Ruta Central") * (C2:C7<80))); "No hay opciones") -> Resultado esperado: No hay opciones

Aplicaciones Prácticas

• LogísticaEncontrar el transportista más barato para una ruta específica, excluyendo opciones que excedan el tiempo de entrega máximo.
• Gestión de ComprasSeleccionar el proveedor con el precio más bajo para un material, siempre que su calificación de calidad supere un mínimo.
• Recursos HumanosIdentificar al candidato con el salario solicitado más bajo para un puesto, filtrando por aquellos que tengan más de un número determinado de años de experiencia.
• Análisis FinancieroEncontrar la acción con el precio más bajo dentro de un sector industrial específico que también tenga una capitalización de mercado por encima de un umbral.

Observaciones

Es fundamental que los rangos utilizados en la fórmula (rango_costos y rango_rutas) tengan exactamente las mismas dimensiones (mismo número de filas y columnas). De lo contrario, la fórmula arrojará un error #¡VALOR!.

Esta fórmula se basa en la tecnología de matrices dinámicas. Si está utilizando una versión de Excel anterior a 2021, la función FILTRAR no estará disponible y deberá usar una alternativa (ver más abajo).

Errores comunes

• #¡CALC!: Este error aparece cuando la función FILTRAR no encuentra ningún valor que cumpla con todos los criterios establecidos. Se puede gestionar elegantemente con la función SI.ERROR.
• #¡VALOR!: Ocurre si los rangos proporcionados a la fórmula no tienen las mismas dimensiones. Por ejemplo, si rango_rutas es A2:A7 y rango_costos es C2:C6.
• #¿NOMBRE?: Aparece si está intentando usar la función FILTRAR en una versión de Excel que no la soporta (Excel 2019 o anterior).

Alternativas

Si no dispone de una versión de Excel con matrices dinámicas, existen otras formas de lograr el mismo resultado:

• Función MIN.SI.CONJUNTO:

  =MIN.SI.CONJUNTO(C2:C7; A2:A7; "Ruta Norte"; C2:C7; "<120")

  Esta es la alternativa más directa y sencilla si no se pueden usar matrices dinámicas. La función MIN.SI.CONJUNTO está diseñada específicamente para encontrar el valor mínimo en un rango que cumple múltiples criterios.

• Fórmula matricial (heredada):

  {=MIN(SI((A2:A7="Ruta Norte")*(C2:C7<120); C2:C7))}

  Esta es una fórmula matricial tradicional. Funciona de manera muy similar a la versión con FILTRAR, pero debe ser introducida pulsando Ctrl + Shift + Enter en lugar de solo Enter. Excel la rodeará automáticamente con llaves { } para indicar que es una fórmula matricial. Es potente pero menos intuitiva que las soluciones modernas.