Tarefa de transporte en Excel. Atopar o mellor método de transporte do vendedor ao comprador

Excel é un programa moi funcional. Pódese usar para resolver unha enorme capa de problemas aos que se ten que enfrontarse nos negocios. Un dos máis comúns é o transporte. Imaxina que necesitamos entender que método de transporte desde o fabricante ata o comprador final é o máis óptimo en termos de tempo, diñeiro e outros recursos. Este problema é bastante popular, independentemente do sector no que estea a empresa. Por iso, vexamos máis de cerca como implementalo mediante Excel.

Descrición da tarefa de transporte

Polo tanto, temos dúas contrapartes que interactúan constantemente entre si. No noso caso, este é un comprador e un vendedor. Necesitamos descubrir como transportar mercadorías de forma que os custos sexan mínimos. Para iso, cómpre presentar todos os datos en forma esquemática ou matricial. En Excel, usamos esta última opción. En xeral, hai dous tipos de tarefas de transporte:

1. Pechado. Neste caso, a oferta e a demanda están en equilibrio.
2. Aberto. Aquí non hai igualdade entre oferta e demanda. Para obter unha solución a este problema, primeiro debes traelo ao primeiro tipo, igualando a oferta e a demanda. Para iso, cómpre introducir un indicador adicional: a presenza dun comprador ou vendedor condicional. Ademais, cómpre facer certos cambios na táboa de custos.

Como activar a función Buscar solución en Excel

Para resolver problemas de transporte en Excel, hai unha función especial chamada "Buscar unha solución". Non está activado por defecto, polo que debes seguir os seguintes pasos:

1. Abre o menú "Ficheiro", que se atopa na esquina superior esquerda da xanela do programa.
2. Despois diso, fai clic no botón cos parámetros.
3. A continuación, atopamos a subsección "Configuración" e imos ao menú de xestión de complementos. Estes son pequenos programas que se executan no entorno Microsoft Excel. Vemos que nun primeiro momento prememos no menú "Complementos", e despois na parte inferior dereita configuramos o elemento "Complementos de Excel" e prememos no botón "Ir". Todas as accións necesarias resáltanse con rectángulos e frechas vermellas.
4. A continuación, active o complemento "Buscar unha solución", despois de que confirmamos as nosas accións premendo o botón Aceptar. A partir da descrición do escenario, podemos ver que está deseñado para analizar datos complexos, como científicos e financeiros.
5. Despois diso, vai á pestana "Datos", onde vemos un novo botón, que se chama o mesmo que o complemento. Pódese atopar no grupo de ferramentas de análise.

Só queda premer neste botón e procedemos á solución do problema de transporte. Pero antes diso, deberíamos falar un pouco máis sobre a ferramenta Solver en Excel. Este é un complemento especial de Excel que permite atopar a solución máis rápida a un problema. Un trazo característico é a consideración das restricións que o usuario establece na fase de preparación. En termos sinxelos, esta é unha subrutina que permite determinar a mellor forma de conseguir unha determinada tarefa. Tales tarefas poden incluír as seguintes:

1. Investir, cargar un almacén ou calquera outra actividade similar. Incluída a entrega de mercadorías.
2. O mellor xeito. Isto inclúe obxectivos como conseguir o máximo beneficio ao mínimo custo, como conseguir a mellor calidade cos recursos dispoñibles, etc.

Ademais das tarefas de transporte, este complemento tamén se usa para os seguintes propósitos:

1. Desenvolvemento dun plan de produción. É dicir, cantas unidades dun produto hai que producir para conseguir o máximo de ingresos.
2. Busca a distribución do traballo para os distintos tipos de traballo de forma que o custo total de producir un produto ou servizo sexa o menor.
3. Establece o tempo mínimo que tardará en completar todo o traballo.

Como podes ver, as tarefas son moi diferentes. A regra universal para aplicar este complemento é que antes de resolver o problema é necesario crear un modelo que se corresponda coas características clave do problema plantexado. Un modelo é unha colección de funcións que usan variables como argumentos. É dicir, valores que poden cambiar.

É importante ter en conta que a optimización dun conxunto de valores realízase exclusivamente nun indicador, que se chama función obxectivo.

O complemento Solver enumera os diferentes valores das variables que se pasan á función obxectivo de forma que sexa o máximo, mínimo ou igual a un determinado valor (esta é precisamente a restrición). Hai outra función que é algo semellante no seu principio de funcionamento, e que moitas veces se confunde co "Buscar unha solución". Chámase "Selección de opcións". Pero se afondas, a diferenza entre eles é enorme:

1. A función de busca de obxectivos non funciona con máis dunha variable.
2. Non prevé a posibilidade de establecer límites ás variables.
3. É capaz de determinar só a igualdade da función obxectivo a un determinado valor, pero non permite atopar o máximo e o mínimo. Polo tanto, non é axeitado para a nosa tarefa.
4. Capaz de calcular de forma eficiente só se modelo tipo lineal. Se o modelo non é lineal, entón atopa o valor máis próximo ao valor orixinal.

A tarefa de transporte é moito máis complicada na súa estrutura, polo que o complemento "Selección de parámetros" non é suficiente para iso. Vexamos máis de cerca como implementar a función "Buscar unha solución" na práctica usando o exemplo dun problema de transporte.

Un exemplo de resolución dun problema de transporte en Excel

Para demostrar claramente como resolver problemas de transporte na práctica en Excel, imos dar un exemplo.

Condicións tarefas

Supoñamos que temos 6 vendedores e 7 compradores. A demanda e a oferta entre elas distribúense respectivamente da seguinte forma: 36, 51, 32, 44, 35 e 38 unidades son vendedoras e 33, 48, 30, 36, 33, 24 e 32 unidades son compradores. Se sumas todos estes valores, descubrirás que a oferta e a demanda están en equilibrio. Polo tanto, este problema é de tipo pechado, que se resolve de xeito moi sinxelo.

Ademais, dispoñemos de información sobre canto debe gastar no transporte do punto A ao punto B (destacanse en celas amarelas no exemplo).

Solución: algoritmo paso a paso

Agora, despois de familiarizarnos coas táboas cos datos iniciais, podemos utilizar o seguinte algoritmo para resolver este problema:

1. En primeiro lugar, elaboramos unha táboa formada por 6 filas e 7 columnas.
2. Despois diso, imos a calquera cela que non conteña ningún valor e ao mesmo tempo quede fóra da táboa recén creada e inserimos a función. Para iso, fai clic no botón fx, que está situado á esquerda da liña de entrada da función.
3. Temos unha xanela na que debemos seleccionar a categoría "Matemáticas". Que función nos interesa? O destacado nesta captura de pantalla. Función SUMPRODUCT multiplica intervalos ou matrices entre si e sumalos. Xusto o que necesitamos. Despois diso, prema a tecla Aceptar.
4. A continuación, aparecerá unha xanela na pantalla na que precisa especificar os parámetros da función. Son os seguintes:
   1. Matriz 1. Este é o primeiro argumento no que escribimos o intervalo que está resaltado en amarelo. Pode axustar os parámetros da función usando o teclado ou seleccionando a área apropiada co botón esquerdo do rato.
   2. Matriz 2. Este é o segundo argumento, que é a táboa recén creada. As accións realízanse do mesmo xeito.

Confirme a súa acción premendo o botón Aceptar.

1. Despois diso, facemos un clic esquerdo do rato na cela que serve como parte superior esquerda da táboa recén creada. Agora fai clic de novo no botón de función de inserción.
2. Seleccionamos a mesma categoría que no caso anterior. Pero esta vez interésanos a función SUM.
3. Agora chega a etapa de encher os argumentos. Como primeiro argumento, escribimos a fila superior da táboa que creamos ao principio. Do mesmo xeito que antes, pódese facer seleccionando estas celas na folla, ou manualmente. Confirmamos as nosas accións premendo o botón Aceptar.
4. Veremos os resultados na cela coa función. Neste caso, é cero. A continuación, move o cursor á esquina inferior dereita, despois de que aparecerá un marcador de autocompletar. Parece un pequeno peluche negro. Se aparece, manteña premido o botón esquerdo do rato e move o cursor ata a última cela da nosa táboa.
5. Isto dános a oportunidade de transferir a fórmula a todas as outras celas e obter os resultados correctos sen ter que realizar cálculos adicionais.
6. O seguinte paso é seleccionar a cela superior esquerda e pegar a función SUM nela. Despois diso, introducimos os argumentos e usamos o marcador de autocompletar para encher todas as celas restantes.
7. Despois diso, procedemos directamente á resolución do problema. Para iso, utilizaremos o complemento que incluímos anteriormente. Vaia á pestana "Datos" e alí atopamos a ferramenta "Buscar unha solución". Facemos clic neste botón.
8. Agora apareceu ante os nosos ollos unha xanela a través da cal podedes configurar os parámetros do noso complemento. Vexamos cada unha destas opcións:
   1. Optimizar a función obxectivo. Aquí temos que seleccionar a cela que contén a función SUMPRODUCT. Vemos que esta opción permite seleccionar unha función para a que se buscará unha solución.
   2. Antes. Aquí configuramos a opción "Mínimo".
   3. Ao cambiar as celas das variables. Aquí indicamos o rango correspondente á táboa que creamos ao principio (a excepción da fila e columna de resumo).
   4. Suxeito a restricións. Aquí necesitamos engadir restricións facendo clic no botón Engadir.
   5. Lembramos que tipo de limitación debemos crear: a suma dos valores das demandas dos compradores e das ofertas dos vendedores debe ser a mesma.
9. A tarefa de restricións realízase do seguinte xeito:
   1. Ligazón a celas. Aquí introducimos o rango da táboa para os cálculos.
   2. Termos. Esta é unha operación matemática coa que se verifica o intervalo especificado no primeiro campo de entrada.
   3. O valor da condición ou restrición. Aquí introducimos a columna apropiada na táboa de orixe.
   4. Despois de completar todos os pasos, fai clic no botón Aceptar, confirmando así as nosas accións.

Realizamos exactamente as mesmas operacións para as filas superiores, establecendo a seguinte condición: deben ser iguais.

O seguinte paso é establecer as condicións. Necesitamos establecer os seguintes criterios para a suma das celas da táboa: maior ou igual a cero, un número enteiro. Como resultado, temos unha lista de condicións nas que se resolve o problema. Aquí cómpre asegurarse de que a caixa de verificación situada xunto á opción "Facer variables sen límites non negativas" estea marcada. Ademais, na nosa situación, é necesario que se elixa o método para resolver o problema: "Buscando unha solución a problemas non lineais dos métodos OPG". Agora podemos dicir con seguridade que a configuración está feita. Polo tanto, só queda realizar os cálculos. Para iso, fai clic no botón "Buscar unha solución".

Despois diso, todos os datos calcularanse automaticamente e, a continuación, Excel mostrará unha xanela cos resultados. É necesario para comprobar o funcionamento do ordenador, xa que os erros son posibles se as condicións estaban previamente configuradas incorrectamente. Se todo é correcto, fai clic no botón "Aceptar" e mira a táboa rematada.

Se resulta que a nosa tarefa se converteu nun tipo aberto, isto é malo, porque cómpre editar a táboa de orixe para que a tarefa se converta nunha pechada. Non obstante, cando se faga isto, o algoritmo restante será o mesmo.

Conclusión

Como podes ver, Excel tamén se pode utilizar para cálculos moi complexos, que a primeira vista non están dispoñibles para un programa informático sinxelo que está instalado en case todos. Con todo, é. Hoxe xa cubrimos o nivel avanzado de uso. Este tema non é tan sinxelo, pero como din, o camiño será dominado polo camiñante. O principal é seguir o plan de acción e realizar con precisión todas as accións indicadas anteriormente. Entón non haberá erros e o programa realizará de forma independente todos os cálculos necesarios. Non haberá que pensar en que función usar e así por diante.