Transportuppgift i Excel. Att hitta den bästa transportmetoden från säljaren till köparen

Excel är ett mycket funktionellt program. Det kan användas för att lösa ett enormt lager av problem som man måste möta i näringslivet. En av de vanligaste är transporter. Föreställ dig att vi måste förstå vilken metod för transport från tillverkaren till den slutliga köparen som är den mest optimala när det gäller tid, pengar och andra resurser. Det här problemet är ganska populärt, oavsett vilken bransch företaget är i. Låt oss därför titta närmare på hur man implementerar det med Excel.

Beskrivning av transportuppgiften

Så vi har två motparter som ständigt interagerar med varandra. I vårt fall är detta en köpare och en säljare. Vi måste ta reda på hur man transporterar varor på ett sådant sätt att kostnaderna blir minimala. För att göra detta måste du presentera all data i en schematisk eller matrisform. I Excel använder vi det senare alternativet. Generellt finns det två typer av transportuppgifter:

1. Stängd. I det här fallet är utbud och efterfrågan i balans.
2. Öppna. Här finns ingen jämställdhet mellan utbud och efterfrågan. För att få en lösning på detta problem måste du först ta det till den första typen, utjämna utbud och efterfrågan. För att göra detta måste du införa en ytterligare indikator - närvaron av en villkorad köpare eller säljare. Dessutom måste du göra vissa ändringar i kostnadstabellen.

Så här aktiverar du funktionen Hitta lösning i Excel

För att lösa transportproblem i Excel finns en speciell funktion som heter "Sök efter en lösning". Det är inte aktiverat som standard, så du måste göra följande steg:

1. Öppna "Arkiv"-menyn, som finns i det övre vänstra hörnet av programfönstret.
2. Därefter klickar du på knappen med parametrarna.
3. Därefter hittar vi undersektionen "Inställningar" och går till menyn för hantering av tillägg. Dessa är små program som körs i Microsoft Excel-miljön. Vi ser att vi först klickade på menyn "Tillägg", och sedan i den nedre högra delen ställde vi in ​​"Excel-tillägg" och klickade på knappen "Kör". Alla nödvändiga åtgärder markeras med röda rektanglar och pilar.
4. Slå sedan på tillägget "Sök efter en lösning", varefter vi bekräftar våra åtgärder genom att trycka på OK-knappen. Baserat på beskrivningen av miljön kan vi se att den är utformad för att analysera komplexa data, såsom vetenskapliga och ekonomiska.
5. Efter det, gå till fliken "Data", där vi ser en ny knapp, som kallas samma som tillägget. Den finns i analysverktygsgruppen.

Det återstår bara att klicka på den här knappen, och vi fortsätter till lösningen av transportproblemet. Men innan dess bör vi prata lite mer om verktyget Solver i Excel. Detta är ett speciellt Excel-tillägg som gör det möjligt att hitta den snabbaste lösningen på ett problem. Ett karakteristiskt drag är hänsynen till begränsningar som användaren sätter i förberedelsestadiet. Enkelt uttryckt är detta en subrutin som gör det möjligt att bestämma det bästa sättet att uppnå en viss uppgift. Sådana uppgifter kan omfatta följande:

1. Investera, ladda ett lager eller någon annan liknande aktivitet. Inklusive leverans av varor.
2. Det bästa sättet. Detta inkluderar mål som att uppnå maximal vinst till lägsta kostnad, hur man uppnår bästa kvalitet med tillgängliga resurser och så vidare.

Förutom transportuppgifter används detta tillägg även för följande ändamål:

1. Utveckling av en produktionsplan. Det vill säga hur många enheter av en produkt som behöver produceras för att uppnå maximal inkomst.
2. Hitta arbetsfördelningen för olika typer av arbete så att den totala kostnaden för att producera en produkt eller tjänst blir som lägst.
3. Ställ in den minsta tid det tar att slutföra allt arbete.

Som du kan se är uppgifterna väldigt olika. Den universella regeln för att tillämpa detta tillägg är att innan du löser problemet är det nödvändigt att skapa en modell som motsvarar de viktigaste egenskaperna hos problemet. En modell är en samling funktioner som använder variabler som sina argument. Det vill säga värderingar som kan förändras.

Det är viktigt att notera att optimeringen av en uppsättning värden utförs uteslutande på en indikator, som kallas den objektiva funktionen.

Solver-tillägget räknar upp de olika värdena för variablerna som skickas till objektivfunktionen på ett sådant sätt att det är maximum, minimum eller lika med ett visst värde (detta är just begränsningen). Det finns en annan funktion som är något liknande i sin funktionsprincip och som ofta förväxlas med "Sök efter en lösning". Det kallas "Alternativval". Men om du gräver djupare är skillnaden mellan dem enorm:

1. Målsökningsfunktionen fungerar inte med mer än en variabel.
2. Den ger inte möjlighet att sätta gränser för variabler.
3. Den kan endast bestämma den objektiva funktionens likhet till ett visst värde, men gör det inte möjligt att hitta maximum och minimum. Därför är det inte lämpligt för vår uppgift.
4. Kunna effektivt beräkna endast om modell linjär typ. Om modellen är icke-linjär, så hittar den det värde som ligger närmast det ursprungliga värdet.

Transportuppgiften är mycket mer komplicerad i sin struktur, så tillägget "Parameterval" är inte tillräckligt för detta. Låt oss titta närmare på hur man implementerar funktionen "Sök efter en lösning" i praktiken med hjälp av exemplet på ett transportproblem.

Ett exempel på att lösa ett transportproblem i Excel

För att tydligt visa hur man löser transportproblem i praktiken i Excel, låt oss ge ett exempel.

Villkor uppgifter

Anta att vi har 6 säljare och 7 köpare. Efterfrågan och utbud mellan dem fördelar sig på följande sätt: 36, 51, 32, 44, 35 och 38 enheter är säljare och 33, 48, 30, 36, 33, 24 och 32 enheter är köpare. Om du summerar alla dessa värden kommer du att upptäcka att utbud och efterfrågan är i balans. Därför är detta problem av en sluten typ, vilket löses mycket enkelt.

Dessutom har vi information om hur mycket du behöver lägga på transport från punkt A till punkt B (de är markerade i gula celler i exemplet).

Lösning – steg för steg algoritm

Nu, efter att vi har bekantat oss med tabellerna med initialdata, kan vi använda följande algoritm för att lösa detta problem:

1. Först gör vi en tabell som består av 6 rader och 7 kolumner.
2. Efter det går vi till valfri cell som inte innehåller några värden och samtidigt ligger utanför den nyskapade tabellen och infogar funktionen. För att göra detta, klicka på fx-knappen, som finns till vänster om funktionsinmatningsraden.
3. Vi har ett fönster där vi måste välja kategorin "Matte". Vilken funktion är vi intresserade av? Den som är markerad i den här skärmdumpen. Fungera SUMPRODUCT multiplicerar intervall eller arrayer sinsemellan och summerar dem. Precis vad vi behöver. Efter det, tryck på OK-tangenten.
4. Därefter visas ett fönster på skärmen där du måste ange funktionsparametrarna. De är följande:
   1. Array 1. Detta är det första argumentet där vi skriver intervallet som är markerat med gult. Du kan ställa in funktionsparametrarna antingen med hjälp av tangentbordet eller genom att välja lämpligt område med vänster musknapp.
   2. Array 2. Detta är det andra argumentet, som är den nyskapade tabellen. Åtgärder utförs på samma sätt.

Bekräfta din åtgärd genom att trycka på OK-knappen.

1. Efter det gör vi ett vänsterklick på cellen som fungerar som den övre vänstra i den nyskapade tabellen. Klicka nu på knappen infoga funktion igen.
2. Vi väljer samma kategori som i föregående fall. Men den här gången är vi intresserade av funktionen SUMMA.
3. Nu kommer steget att fylla i argumenten. Som första argument skriver vi den översta raden i tabellen som vi skapade i början. På samma sätt som tidigare kan detta göras genom att markera dessa celler på arket, eller manuellt. Vi bekräftar våra åtgärder genom att trycka på OK-knappen.
4. Vi kommer att se resultaten i cellen med funktionen. I det här fallet är det noll. Flytta sedan markören till det nedre högra hörnet, varefter en markör för autoslutförande visas. Det ser ut som en liten svart plysch. Om det visas, håll nere vänster musknapp och flytta markören till den sista cellen i vår tabell.
5. Detta ger oss möjlighet att överföra formeln till alla andra celler och få rätt resultat utan att behöva utföra ytterligare beräkningar.
6. Nästa steg är att välja den övre vänstra cellen och klistra in funktionen SUMMA in i henne. Efter det anger vi argumenten och använder autokompletteringsmarkören för att fylla i alla återstående celler.
7. Efter det fortsätter vi direkt för att lösa problemet. För att göra detta kommer vi att använda tillägget som vi inkluderade tidigare. Gå till fliken "Data" och där hittar vi verktyget "Sök efter en lösning". Vi klickar på den här knappen.
8. Nu har ett fönster dykt upp framför våra ögon, genom vilket du kan konfigurera parametrarna för vårt tillägg. Låt oss ta en titt på vart och ett av dessa alternativ:
   1. Optimera målfunktionen. Här måste vi välja cellen som innehåller funktionen SUMPRODUCT. Vi ser att detta alternativ gör det möjligt att välja en funktion som en lösning ska sökas för.
   2. Innan. Här ställer vi in ​​alternativet "Minimum".
   3. Genom att ändra cellerna i variablerna. Här anger vi intervallet som motsvarar tabellen som vi skapade i början (med undantag för den sammanfattande raden och kolumnen).
   4. Med förbehåll för restriktioner. Här måste vi lägga till begränsningar genom att klicka på knappen Lägg till.
   5. Vi kommer ihåg vilken typ av begränsning vi behöver skapa – summan av värdena av köpares krav och säljares erbjudanden måste vara densamma.
9. Uppgiften med begränsningar utförs enligt följande:
   1. Länk till celler. Här anger vi tabellens intervall för beräkningar.
   2. Villkor. Detta är en matematisk operation mot vilken intervallet som anges i det första inmatningsfältet kontrolleras.
   3. Värdet av villkoret eller begränsningen. Här anger vi lämplig kolumn i källtabellen.
   4. När alla steg är slutförda klickar du på OK-knappen och bekräftar därmed våra åtgärder.

Vi utför exakt samma operationer för de översta raderna och ställer in följande villkor: de måste vara lika.

Nästa steg är att sätta villkoren. Vi måste ställa in följande kriterier för summan av cellerna i tabellen – större än eller lika med noll, ett heltal. Som ett resultat har vi en sådan lista över förhållanden under vilka problemet löses. Här måste du se till att kryssrutan bredvid alternativet "Gör variabler utan gränser till icke-negativa" är markerad. I vår situation krävs det också att metoden för att lösa problemet väljs - "Söker efter en lösning på olinjära problem med OPG-metoder". Nu kan vi lugnt säga att inställningen är klar. Därför återstår det bara att utföra beräkningarna. För att göra detta, klicka på knappen "Hitta en lösning".

Efter det kommer all data att beräknas automatiskt, och sedan visar Excel ett fönster med resultaten. Det är nödvändigt för att dubbelkontrollera datorns funktion, eftersom fel är möjliga om villkoren tidigare var felaktigt inställda. Om allt är korrekt, klicka sedan på "OK"-knappen och se den färdiga tabellen.

Om det visar sig att vår uppgift har blivit en öppen typ, så är detta dåligt, eftersom du måste redigera källtabellen så att uppgiften förvandlas till en stängd. Men när detta är gjort kommer den återstående algoritmen att vara densamma.

Slutsats

Som du ser kan Excel även användas för mycket komplexa beräkningar, som vid en första anblick inte är tillgängliga för ett enkelt datorprogram som är installerat i nästan alla. Men det är det. Idag har vi redan täckt den avancerade användningsnivån. Det här ämnet är inte så enkelt, men som de säger, vägen kommer att bemästras av den som går. Det viktigaste är att följa handlingsplanen och exakt utföra alla åtgärder som anges ovan. Då blir det inga fel, och programmet kommer självständigt att utföra alla nödvändiga beräkningar. Du behöver inte tänka på vilken funktion du ska använda och så vidare.