Transportoppgave i Excel. Finne den beste metoden for transport fra selger til kjøper

Excel er et veldig funksjonelt program. Det kan brukes til å løse et stort lag med problemer som man må møte i virksomheten. En av de vanligste er transport. Tenk deg at vi trenger å forstå hvilken transportmetode fra produsenten til den endelige kjøperen som er den mest optimale når det gjelder tid, penger og andre ressurser. Dette problemet er ganske populært, uansett hvilken bransje virksomheten er i. La oss derfor se nærmere på hvordan du implementerer det ved hjelp av Excel.

Beskrivelse av transportoppgaven

Så vi har to motparter som hele tiden samhandler med hverandre. I vårt tilfelle er dette en kjøper og en selger. Vi må finne ut hvordan vi skal transportere varer på en slik måte at kostnadene er minimale. For å gjøre dette må du presentere alle dataene i en skjematisk eller matriseform. I Excel bruker vi det siste alternativet. Generelt er det to typer transportoppgaver:

1. Lukket. I dette tilfellet er tilbud og etterspørsel i balanse.
2. Åpen. Her er det ingen likhet mellom tilbud og etterspørsel. For å få en løsning på dette problemet, må du først bringe det til den første typen, utjevne tilbud og etterspørsel. For å gjøre dette, må du introdusere en ekstra indikator - tilstedeværelsen av en betinget kjøper eller selger. I tillegg må du gjøre visse endringer i kostnadstabellen.

Slik aktiverer du funksjonen Finn løsning i Excel

For å løse transportproblemer i Excel, er det en spesiell funksjon kalt "Søk etter en løsning". Det er ikke aktivert som standard, så du må gjøre følgende:

1. Åpne "Fil"-menyen, som er plassert i øvre venstre hjørne av programvinduet.
2. Klikk deretter på knappen med parameterne.
3. Deretter finner vi underseksjonen "Innstillinger" og går til administrasjonsmenyen for tillegg. Dette er små programmer som kjører i Microsoft Excel-miljøet. Vi ser at vi først klikket på "Add-ins"-menyen, og deretter i nedre høyre del satte vi elementet "Excel Add-ins" og klikket på "Go"-knappen. Alle nødvendige handlinger er uthevet med røde rektangler og piler.
4. Deretter slår du på tillegget "Søk etter en løsning", hvoretter vi bekrefter handlingene våre ved å trykke på OK-knappen. Basert på beskrivelsen av innstillingen kan vi se at den er designet for å analysere komplekse data, som vitenskapelige og økonomiske.
5. Etter det, gå til "Data" -fanen, hvor vi ser en ny knapp, som kalles det samme som tillegget. Den finnes i Analyseverktøygruppen.

Det gjenstår bare å klikke på denne knappen, og vi fortsetter til løsningen av transportproblemet. Men før det bør vi snakke litt mer om Solver-verktøyet i Excel. Dette er et spesielt Excel-tillegg som gjør det mulig å finne den raskeste løsningen på et problem. Et karakteristisk trekk er hensynet til begrensninger som brukeren setter på forberedelsesstadiet. Enkelt sagt er dette en subrutine som gjør det mulig å bestemme den beste måten å oppnå en bestemt oppgave på. Slike oppgaver kan omfatte følgende:

1. Investering, lasting av et lager eller annen lignende aktivitet. Inkludert levering av varer.
2. Den beste måten. Dette inkluderer mål som å oppnå maksimal fortjeneste til lavest mulig kostnad, hvordan oppnå best kvalitet med tilgjengelige ressurser, og så videre.

I tillegg til transportoppgaver, brukes dette tillegget også til følgende formål:

1. Utvikling av produksjonsplan. Det vil si hvor mange enheter av et produkt som må produseres for å oppnå maksimal inntekt.
2. Finn fordelingen av arbeidskraft for ulike typer arbeid slik at den totale kostnaden ved å produsere et produkt eller en tjeneste blir den minste.
3. Angi minimumstiden det vil ta å fullføre alt arbeidet.

Som du ser er oppgavene veldig forskjellige. Den universelle regelen for å bruke dette tillegget er at før du løser problemet, er det nødvendig å lage en modell som svarer til nøkkelkarakteristikkene til problemet. En modell er en samling funksjoner som bruker variabler som argumenter. Det vil si verdier som kan endre seg.

Det er viktig å merke seg at optimaliseringen av et sett med verdier utelukkende utføres på en indikator, som kalles den objektive funksjonen.

Solver-tillegget teller opp de forskjellige verdiene til variablene som sendes til objektivfunksjonen på en slik måte at det er maksimum, minimum eller lik en viss verdi (dette er nettopp begrensningen). Det er en annen funksjon som er noe lik i sitt virkeprinsipp, og som ofte forveksles med "Søk etter en løsning". Det kalles "Alternativvalg". Men hvis du graver dypere, er forskjellen mellom dem enorm:

1. Målsøk-funksjonen fungerer ikke med mer enn én variabel.
2. Den gir ikke mulighet til å sette grenser for variabler.
3. Den er i stand til å bestemme bare likheten til den objektive funksjonen til en viss verdi, men gjør det ikke mulig å finne maksimum og minimum. Derfor er det ikke egnet for vår oppgave.
4. Kunne effektivt beregne bare hvis modellen er lineær type. Hvis modellen er ikke-lineær, finner den verdien som er nærmest den opprinnelige verdien.

Transportoppgaven er mye mer komplisert i sin struktur, så tillegget "Parametervalg" er ikke nok for dette. La oss se nærmere på hvordan du implementerer «Søk etter en løsning»-funksjonen i praksis ved å bruke eksemplet på et transportproblem.

Et eksempel på løsning av et transportproblem i Excel

For å tydelig vise hvordan man løser transportproblemer i praksis i Excel, la oss gi et eksempel.

Betingelsesoppgaver

Anta at vi har 6 selgere og 7 kjøpere. Etterspørsel og tilbud mellom dem fordeles henholdsvis på følgende måte: 36, 51, 32, 44, 35 og 38 enheter er selgere og 33, 48, 30, 36, 33, 24 og 32 enheter er kjøpere. Hvis du summerer alle disse verdiene, vil du finne at tilbud og etterspørsel er i balanse. Derfor er dette problemet av en lukket type, som løses veldig enkelt.

I tillegg har vi informasjon om hvor mye du må bruke på transport fra punkt A til punkt B (de er uthevet i gule celler i eksempelet).

Løsning – trinnvis algoritme

Nå, etter at vi har gjort oss kjent med tabellene med de første dataene, kan vi bruke følgende algoritme for å løse dette problemet:

1. Først lager vi en tabell som består av 6 rader og 7 kolonner.
2. Etter det går vi til en hvilken som helst celle som ikke inneholder noen verdier og som samtidig ligger utenfor den nyopprettede tabellen og setter inn funksjonen. For å gjøre dette, klikk på fx-knappen, som er plassert til venstre for funksjonsinntastingslinjen.
3. Vi har et vindu der vi må velge kategorien "Matematikk". Hvilken funksjon er vi interessert i? Den som er uthevet i dette skjermbildet. Funksjon SUMPRODUCT multipliserer områder eller matriser seg imellom og summerer dem. Akkurat det vi trenger. Trykk deretter på OK-tasten.
4. Deretter vises et vindu på skjermen der du må spesifisere funksjonsparametrene. De er følgende:
   1. Matrise 1. Dette er det første argumentet der vi skriver området som er uthevet med gult. Du kan stille inn funksjonsparametrene enten ved å bruke tastaturet eller ved å velge det aktuelle området med venstre museknapp.
   2. Array 2. Dette er det andre argumentet, som er den nyopprettede tabellen. Handlinger utføres på samme måte.

Bekreft handlingen ved å trykke på OK-knappen.

1. Etter det gjør vi et venstre museklikk på cellen som fungerer som øverst til venstre i den nyopprettede tabellen. Klikk nå på sette inn funksjonsknappen igjen.
2. Vi velger samme kategori som i forrige tilfelle. Men denne gangen er vi interessert i funksjonen SUM.
3. Nå kommer stadiet med å fylle ut argumentene. Som det første argumentet skriver vi den øverste raden i tabellen som vi opprettet i begynnelsen. På samme måte som før kan dette gjøres ved å velge disse cellene på arket, eller manuelt. Vi bekrefter handlingene våre ved å trykke på OK-knappen.
4. Vi vil se resultatene i cellen med funksjonen. I dette tilfellet er det null. Deretter flytter du markøren til nedre høyre hjørne, hvoretter en autofullføringsmarkør vises. Det ser ut som en liten svart plysj. Hvis det vises, hold nede venstre museknapp og flytt markøren til den siste cellen i tabellen vår.
5. Dette gir oss muligheten til å overføre formelen til alle andre celler og få riktige resultater uten å måtte utføre ytterligere beregninger.
6. Det neste trinnet er å velge cellen øverst til venstre og lime inn funksjonen SUM inn i henne. Etter det legger vi inn argumentene og bruker autofullføringsmarkøren for å fylle ut alle de gjenværende cellene.
7. Etter det fortsetter vi direkte for å løse problemet. For å gjøre dette, vil vi bruke tillegget som vi inkluderte tidligere. Gå til fanen "Data", og der finner vi verktøyet "Søk etter en løsning". Vi klikker på denne knappen.
8. Nå har et vindu dukket opp foran øynene våre, der du kan konfigurere parametrene til tillegget vårt. La oss ta en titt på hvert av disse alternativene:
   1. Optimaliser objektivfunksjonen. Her må vi velge cellen som inneholder funksjonen SUMPRODUCT. Vi ser at dette alternativet gjør det mulig å velge en funksjon som det skal søkes løsning for.
   2. Før. Her setter vi alternativet "Minimum".
   3. Ved å endre cellene til variablene. Her angir vi området som tilsvarer tabellen som vi opprettet helt i begynnelsen (med unntak av den oppsummerende raden og kolonnen).
   4. Med forbehold om restriksjoner. Her må vi legge til begrensninger ved å klikke på Legg til-knappen.
   5. Vi husker hva slags begrensning vi må skape – summen av verdiene av kjøpers krav og selgers tilbud må være den samme.
9. Oppgaven med restriksjoner utføres som følger:
   1. Link til celler. Her legger vi inn rekkevidden til tabellen for beregninger.
   2. Vilkår. Dette er en matematisk operasjon som området som er spesifisert i det første inntastingsfeltet kontrolleres mot.
   3. Verdien av betingelsen eller begrensningen. Her legger vi inn riktig kolonne i kildetabellen.
   4. Etter at alle trinnene er fullført, klikker du på OK-knappen, og bekrefter dermed handlingene våre.

Vi utfører nøyaktig de samme operasjonene for de øverste radene, og setter følgende betingelse: de må være like.

Det neste trinnet er å sette betingelsene. Vi må sette følgende kriterier for summen av cellene i tabellen – større enn eller lik null, et heltall. Som et resultat har vi en slik liste over forhold som problemet løses under. Her må du sørge for at avkrysningsboksen ved siden av alternativet "Gjør variabler uten grenser til ikke-negative" er merket av. I vår situasjon kreves det også at metoden for å løse problemet velges - "Søker etter en løsning på ikke-lineære problemer med OPG-metoder". Nå kan vi trygt si at innstillingen er ferdig. Derfor gjenstår det bare å utføre beregningene. For å gjøre dette, klikk på "Finn en løsning"-knappen.

Etter det vil alle data beregnes automatisk, og deretter vil Excel vise et vindu med resultatene. Det er nødvendig for å dobbeltsjekke driften av datamaskinen, siden feil er mulige hvis forholdene tidligere ble satt feil. Hvis alt er riktig, klikk på "OK"-knappen og se den ferdige tabellen.

Hvis det viser seg at oppgaven vår har blitt en åpen type, så er dette dårlig, fordi du må redigere kildetabellen slik at oppgaven blir til en lukket. Men når dette er gjort, vil den gjenværende algoritmen være den samme.

konklusjonen

Som du ser kan Excel også brukes til svært komplekse beregninger, som ved første øyekast ikke er tilgjengelige for et enkelt dataprogram som er installert i nesten alle. Men det er det. I dag har vi allerede dekket det avanserte bruksnivået. Dette emnet er ikke så enkelt, men som de sier, veien vil bli mestret av den gående. Det viktigste er å følge handlingsplanen, og nøyaktig utføre alle handlingene som er angitt ovenfor. Da vil det ikke være noen feil, og programmet vil uavhengig utføre alle nødvendige beregninger. Det vil ikke være nødvendig å tenke på hvilken funksjon du skal bruke og så videre.