Tarea de transporte en Excel. Encontrar el mejor método de transporte del vendedor al comprador

Excel es un programa muy funcional. Se puede usar para resolver una gran cantidad de problemas que uno tiene que enfrentar en los negocios. Uno de los más comunes es el transporte. Imagine que necesitamos comprender qué método de transporte desde el fabricante hasta el comprador final es el más óptimo en términos de tiempo, dinero y otros recursos. Este problema es bastante popular, sin importar en qué industria se encuentre el negocio. Por lo tanto, echemos un vistazo más de cerca a cómo implementarlo usando Excel.

Descripción de la tarea de transporte

Entonces, tenemos dos contrapartes que interactúan constantemente entre sí. En nuestro caso, se trata de un comprador y un vendedor. Necesitamos descubrir cómo transportar mercancías de tal manera que los costos sean mínimos. Para hacer esto, debe presentar todos los datos en forma esquemática o matricial. En Excel, usamos la última opción. En general, hay dos tipos de tareas de transporte:

1. Cerrado. En este caso, la oferta y la demanda están en equilibrio.
2. Abierto. Aquí no hay igualdad entre la oferta y la demanda. Para obtener una solución a este problema, primero debe llevarla al primer tipo, igualando la oferta y la demanda. Para hacer esto, debe introducir un indicador adicional: la presencia de un comprador o vendedor condicional. Además, debe realizar ciertos cambios en la tabla de costos.

Cómo habilitar la función Buscar solución en Excel

Para resolver problemas de transporte en Excel, existe una función especial llamada "Buscar una solución". No está habilitado de forma predeterminada, por lo que debe realizar los siguientes pasos:

1. Abra el menú "Archivo", que se encuentra en la esquina superior izquierda de la ventana del programa.
2. Después de eso, haga clic en el botón con los parámetros.
3. A continuación, encontramos la subsección "Configuración" y vamos al menú de administración de complementos. Estos son pequeños programas que se ejecutan en el entorno de Microsoft Excel. Vemos que primero hicimos clic en el menú "Complementos", y luego en la parte inferior derecha configuramos el elemento "Complementos de Excel" y hacemos clic en el botón "Ir". Todas las acciones necesarias están resaltadas con rectángulos rojos y flechas.
4. A continuación, active el complemento "Buscar una solución", después de lo cual confirmamos nuestras acciones presionando el botón Aceptar. Según la descripción de la configuración, podemos ver que está diseñada para analizar datos complejos, como científicos y financieros.
5. Después de eso, vaya a la pestaña "Datos", donde vemos un nuevo botón, que se llama igual que el complemento. Se puede encontrar en el grupo de herramientas de Análisis.

Solo queda hacer clic en este botón, y procedemos a la solución del problema del transporte. Pero antes de eso, deberíamos hablar un poco más sobre la herramienta Solver en Excel. Este es un complemento especial de Excel que permite encontrar la solución más rápida a un problema. Un rasgo característico es la consideración de restricciones que el usuario establece en la etapa de preparación. En términos simples, esta es una subrutina que permite determinar la mejor manera de lograr una determinada tarea. Tales tareas pueden incluir lo siguiente:

1. Invertir, cargar un almacén o cualquier otra actividad similar. Incluida la entrega de mercancías.
2. La mejor manera. Esto incluye objetivos como lograr el máximo beneficio al mínimo costo, cómo lograr la mejor calidad con los recursos disponibles, etc.

Además de las tareas de transporte, este complemento también se utiliza para los siguientes propósitos:

1. Desarrollo de un plan de producción. Es decir, cuántas unidades de un producto se deben producir para lograr el máximo ingreso.
2. Encuentre la distribución de la mano de obra para diferentes tipos de trabajo de manera que el costo total de producir un producto o servicio sea el más pequeño.
3. Establezca el tiempo mínimo que llevará completar todo el trabajo.

Como puede ver, las tareas son muy diferentes. La regla universal para aplicar este complemento es que antes de resolver el problema, es necesario crear un modelo que se corresponda con las características clave del problema planteado. Un modelo es una colección de funciones que usan variables como sus argumentos. Es decir, valores que pueden cambiar.

Es importante señalar que la optimización de un conjunto de valores se lleva a cabo exclusivamente en un indicador, que se denomina función objetivo.

El complemento Solver enumera los diferentes valores de las variables que se pasan a la función objetivo de tal forma que sea el máximo, mínimo o igual a un valor determinado (esa es precisamente la restricción). Existe otra función que es algo similar en su principio de funcionamiento, y que suele confundirse con la “Búsqueda de solución”. Se llama "Selección de opciones". Pero si profundizas más, la diferencia entre ellos es enorme:

1. La función Goal Seek no funciona con más de una variable.
2. No prevé la capacidad de establecer límites en las variables.
3. Es capaz de determinar solo la igualdad de la función objetivo a un cierto valor, pero no permite encontrar el máximo y el mínimo. Por lo tanto, no es adecuado para nuestra tarea.
4. Capaz de calcular eficientemente solo si el modelo es de tipo lineal. Si el modelo no es lineal, busca el valor más cercano al valor original.

La tarea de transporte es mucho más complicada en su estructura, por lo que el complemento "Selección de parámetros" no es suficiente para esto. Echemos un vistazo más de cerca a cómo implementar la función "Buscar una solución" en la práctica utilizando el ejemplo de un problema de transporte.

Un ejemplo de resolución de un problema de transporte en Excel

Para demostrar claramente cómo resolver problemas de transporte en la práctica en Excel, demos un ejemplo.

Tareas de condiciones

Supongamos que tenemos 6 vendedores y 7 compradores. La demanda y la oferta entre ellos se distribuyen respectivamente de la siguiente manera: 36, 51, 32, 44, 35 y 38 unidades son vendedoras y 33, 48, 30, 36, 33, 24 y 32 unidades son compradoras. Si suma todos estos valores, encontrará que la oferta y la demanda están en equilibrio. Por tanto, este problema es de tipo cerrado, el cual se resuelve de forma muy sencilla.

Además, tenemos información sobre cuánto necesita gastar en transporte del punto A al punto B (están resaltados en celdas amarillas en el ejemplo).

Solución: algoritmo paso a paso

Ahora, después de habernos familiarizado con las tablas con los datos iniciales, podemos usar el siguiente algoritmo para resolver este problema:

1. Primero, hacemos una tabla que consta de 6 filas y 7 columnas.
2. Después de eso, vamos a cualquier celda que no contenga ningún valor y al mismo tiempo se encuentre fuera de la tabla recién creada e insertemos la función. Para hacer esto, haga clic en el botón fx, que se encuentra a la izquierda de la línea de entrada de la función.
3. Tenemos una ventana en la que debemos seleccionar la categoría "Matemáticas". ¿Qué función nos interesa? El resaltado en esta captura de pantalla. Función SUMPRODUCT multiplica rangos o arreglos entre ellos y los suma. Justo lo que necesitamos. Después de eso, presione la tecla OK.
4. A continuación, aparecerá una ventana en la pantalla en la que debe especificar los parámetros de la función. Ellos son los siguientes:
   1. Matriz 1. Este es el primer argumento en el que escribimos el rango que está resaltado en amarillo. Puede configurar los parámetros de la función usando el teclado o seleccionando el área apropiada con el botón izquierdo del mouse.
   2. Matriz 2. Este es el segundo argumento, que es la tabla recién creada. Las acciones se realizan de la misma manera.

Confirme su acción presionando el botón OK.

1. Después de eso, hacemos clic con el botón izquierdo del mouse en la celda que sirve como la parte superior izquierda en la tabla recién creada. Ahora haga clic en el botón Insertar función de nuevo.
2. Seleccionamos la misma categoría que en el caso anterior. Pero esta vez nos interesa la función. SUM.
3. Ahora viene la etapa de completar los argumentos. Como primer argumento, escribimos la fila superior de la tabla que creamos al principio. De la misma manera que antes, esto se puede hacer seleccionando estas celdas en la hoja o manualmente. Confirmamos nuestras acciones presionando el botón OK.
4. Veremos los resultados en la celda con la función. En este caso, es cero. A continuación, mueva el cursor a la esquina inferior derecha, después de lo cual aparecerá un marcador de autocompletar. Parece un pequeño peluche negro. Si aparece, mantenga presionado el botón izquierdo del mouse y mueva el cursor a la última celda de nuestra tabla.
5. Esto nos da la oportunidad de transferir la fórmula a todas las demás celdas y obtener los resultados correctos sin tener que realizar cálculos adicionales.
6. El siguiente paso es seleccionar la celda superior izquierda y pegar la función SUM en ella Después de eso, ingresamos los argumentos y usamos el marcador de autocompletar para completar todas las celdas restantes.
7. Después de eso, procedemos directamente a resolver el problema. Para hacer esto, usaremos el complemento que incluimos anteriormente. Vaya a la pestaña "Datos", y allí encontramos la herramienta "Buscar una solución". Hacemos clic en este botón.
8. Ahora ha aparecido una ventana ante nuestros ojos, a través de la cual puede configurar los parámetros de nuestro complemento. Echemos un vistazo a cada una de estas opciones:
   1. Optimizar la función objetivo. Aquí tenemos que seleccionar la celda que contiene la función SUMPRODUCT. Vemos que esta opción permite seleccionar una función para la cual se buscará una solución.
   2. Antes. Aquí configuramos la opción “Mínimo”.
   3. Cambiando las celdas de las variables. Aquí indicamos el rango correspondiente a la tabla que creamos al principio (con la excepción de la fila y la columna de resumen).
   4. Sujeto a restricciones. Aquí necesitamos agregar restricciones haciendo clic en el botón Agregar.
   5. Recordamos qué tipo de restricción necesitamos crear: la suma de los valores de las demandas de los compradores y las ofertas de los vendedores debe ser la misma.
9. La tarea de restricciones se lleva a cabo de la siguiente manera:
   1. Enlace a celdas. Aquí ingresamos el rango de la tabla para los cálculos.
   2. Términos. Esta es una operación matemática contra la cual se verifica el rango especificado en el primer campo de entrada.
   3. El valor de la condición o restricción. Aquí ingresamos la columna apropiada en la tabla de origen.
   4. Después de completar todos los pasos, haga clic en el botón Aceptar, confirmando así nuestras acciones.

Realizamos exactamente las mismas operaciones para las filas superiores, estableciendo la siguiente condición: deben ser iguales.

El siguiente paso es establecer las condiciones. Necesitamos establecer los siguientes criterios para la suma de las celdas de la tabla: mayor o igual a cero, un número entero. Como resultado, tenemos tal lista de condiciones bajo las cuales se resuelve el problema. Aquí debe asegurarse de que la casilla de verificación junto a la opción "Hacer que las variables sin límites no sean negativas" esté marcada. Además, en nuestra situación, se requiere que se elija el método para resolver el problema: "Búsqueda de una solución a problemas no lineales de métodos OPG". Ahora podemos decir con seguridad que la configuración está lista. Por lo tanto, solo queda realizar los cálculos. Para hacer esto, haga clic en el botón "Buscar una solución".

Después de eso, todos los datos se calcularán automáticamente y luego Excel mostrará una ventana con los resultados. Es necesario para verificar el funcionamiento de la computadora, ya que es posible que se produzcan errores si las condiciones se configuraron incorrectamente anteriormente. Si todo es correcto, haga clic en el botón "Aceptar" y vea la tabla terminada.

Si resulta que nuestra tarea se ha convertido en un tipo abierto, entonces esto es malo, porque necesita editar la tabla de origen para que la tarea se convierta en una cerrada. Sin embargo, cuando se hace esto, el algoritmo restante será el mismo.

Conclusión

Como ves, Excel también se puede utilizar para cálculos muy complejos, que a primera vista no están al alcance de un simple programa informático que está instalado en casi todo el mundo. Sin embargo lo és. Hoy ya hemos cubierto el nivel de uso avanzado. Este tema no es tan simple, pero como dicen, el camino será dominado por el que camina. Lo principal es seguir el plan de acción y realizar con precisión todas las acciones indicadas anteriormente. Entonces no habrá errores y el programa realizará de forma independiente todos los cálculos necesarios. No habrá necesidad de pensar en qué función utilizar y así sucesivamente.