Konklusjon ved problemløsning i excel. Løse et transportproblem ved hjelp av løsningssøkeverktøyet

Bruk Microsoft Excelå løse problemer lineær programmering .

I Excel 2007, for å aktivere analysepakken, må du klikke gå til blokkering Excel-alternativer ved å trykke på knappen til venstre øverste hjørne, og deretter knappen Excel-alternativer"nederst i vinduet:

Deretter, i listen som åpnes, må du velge Tillegg, og plasser deretter markøren på elementet Å finne en løsning, trykk på knappen Gå og i neste vindu aktiver analysepakken.

For å løse LP-problemet i bordprosessor Microsoft Excel, må du gjøre følgende:

1. Angi problemtilstanden:

en)lage et skjermskjema for å legge inn oppgavebetingelser :

· variabler,

· objektiv funksjon(CF),

· begrensninger,

· grensebetingelser;

b) skriv inn de første dataene i skjermbildet :

· TF koeffisienter,

· koeffisienter for variabler i restriksjoner,

· høyre side av restriksjoner;

c) legge inn avhengigheter fra den matematiske modellen i skjermskjemaet :

formel for beregning av CF,

· formler for å beregne verdiene til venstre side av restriksjoner;

d) sett TF (i vinduet "Finne en løsning"):

målcelle

· retning av CF-optimalisering;

e) innføre restriksjoner og grensebetingelser (i vinduet "Finne en løsning"):

· celler med variable verdier,

· grensebetingelser for tillatte verdier av variabler,

· forhold mellom høyre og venstre side av begrensningene.

2. Løs problemet:

en) angi parametere for å løse problemet (i vinduet "Finne en løsning");

b) kjøre et problem å løse (i vinduet "Finne en løsning") ;

c) velg løsningsutdataformat (i vinduet "Løsningssøkeresultater").

La oss vurdere i detalj bruken av MS Excel ved å bruke eksemplet for å løse følgende problem.

Oppgave.

"GRM pic"-fabrikken produserer to typer frokostblandinger - "Crunchy" og "Chewy". Ingrediensene som brukes til å lage begge produktene er i hovedsak de samme og er generelt ikke mangelvare. Hovedbegrensningen på produksjonsvolumet er tilgjengeligheten av arbeidstimer i hvert av de tre verkstedene på fabrikken.

Produksjonssjef Joy Deason må utvikle en månedlig produksjonsplan. Tabellen nedenfor viser total arbeidstid og antall arbeidstimer som kreves for å produsere 1 tonn produkt.

*Butikk*	*Nødvendig arbeidstidsfond* *person-h/t*		*Generelt arbeidstidsfond* *person-time per måned*
*Butikk*	*"Knasende"*	*"seig"*	*Generelt arbeidstidsfond* *person-time per måned*
A. Produksjon	10	4	1000
B. Tilsetning av krydder	3	2	360
C. Emballasje	2	5	600

Inntekten fra produksjonen av 1 tonn "Crunchy" er 150 pund. Art., og fra produksjonen av "Chewy" - 75 f., art. På for tiden det er ingen begrensninger på mulige salgsvolum. Det er mulig å selge alle produserte produkter.

Påkrevd:

a) Formuler en lineær programmeringsmodell som maksimerer den totale månedlige inntekten til fabrikken.

b) Løs det ved hjelp av MS Excel.

Den formelle formuleringen av dette problemet har formen:

(1)
Oppgi innledende data
Opprette et skjermskjema og legge inn innledende data

Skjermskjema for løsningen i MS Excel er presentert i figur 1.

Bilde 1.

I skjermbildet i figur 1 er hver variabel og hver koeffisient av oppgaven tildelt en bestemt celle Excel ark. Cellenavnet består av en bokstav som angir en kolonne og et tall som angir en rad, i skjæringspunktet som er gjenstanden for LP-problemet. Så for eksempel svarer variablene til oppgave 1 til cellene B4 (), C4(), CF-koeffisientene tilsvarer cellene B6 (150), C6(75), tilsvarer høyresidene av restriksjonene celleneD18 (1000), D19 (360), D20 (600) osv.
Legge inn avhengigheter fra en formell problemstilling i et skjermskjema

For å angi avhengigheter som definerer uttrykket for målfunksjonen og restriksjoner, bruk MS Excel-funksjonen SUMPRODUKT, som beregner summen av parvise produkter av to eller flere matriser.

En av de mest enkle måter definere funksjoner i MS Excel er å bruke modusen "Sette inn funksjoner" , som kan kalles fra menyen "Sett inn" eller når du trykker på en knapp "

Figur 2

Så for eksempel er uttrykket for objektivfunksjonen fra oppgave 1 definert som følger:

· markør i feltet D6;

· ved å trykke på en knapp "

· i vinduet "Funksjon" velg funksjon SUMPRODUKT(Fig. 3) ;

Figur 3

i vinduet som vises "SUMPRODUKT"å linje "Array 1" angi uttrykk B$4: C$4 , og til linjen "Array 2"- uttrykk B6: C6 (fig. 4);

Figur 4

Venstre side av begrensningene for problem (1) er summen av produkter hver av cellene tildelt for verdier problemvariabler (B3, C3 ), til den tilsvarende cellen reservert for koeffisienter spesifikk begrensning (B13, C13 - 1. begrensning ; B14, C14- 2. begrensning og B15, C15- 3. begrensning). Formler som tilsvarer venstre side av restriksjonene er presentert i tabell 1.

Tabell 1.
Formler som beskriver begrensningene til modellen (1)

Venstre side av begrensningen	Formelutmerke
	=SUMPRODUKT(B4: C4; B13: C13))
	=SUMPRODUKT(B4: C4; B14: C14))
	=SUMPRODUKT(B4: C4; B15: C15)

DF oppgave

Ytterligere handlinger utføres i vinduet "Finne en løsning", som kalles fra menyen "Service"(Fig.5):

· Plasser markøren i feltet "Angi målcelle";

· skriv inn adressen til målcellen $ D$6 eller gjør ett klikk med venstre museknapp på målcellen i skjermbildet ¾ dette vil tilsvare å skrive inn adressen fra tastaturet;

· angi retningen for CF-optimalisering ved å klikke én gang med venstre museknapp på velgerknappen "maksimal verdi".

Figur 5
Angi begrensninger og grensebetingelser
Stille inn variable celler

Ut av vinduet "Finne en løsning" i felt "Endre celler" angi adresser $ B$4:$$4. De nødvendige adressene kan legges inn i feltet "Endre celler" og automatisk ved å velge de tilsvarende variabelcellene med musen direkte i skjermbildet.
Sette grensebetingelser for akseptable variabelverdier

I vårt tilfelle er bare grensebetingelsen for ikke-negativitet pålagt verdiene til variablene, det vil si at deres nedre grense må være lik null (se fig. 1).

· Klikk på knappen "Legg til", hvoretter et vindu vises "Legge til en begrensning"(Fig. 6).

· I felt "Cellereferanse" angi variable celleadresser $ B$4:$$4. Dette kan gjøres enten fra tastaturet eller ved å velge alle variable celler direkte i skjermskjemaet med musen.

· I skiltfeltet åpner du listen over foreslåtte skilt og velger .

· I felt "Begrensning" skriv inn 0.

Fig.6 - Legge til en betingelse for ikke-negativitet av problemvariabler (1)
Spesifisering av restriksjonsskilt , , =

· Klikk på knappen "Legg til" i vinduet "Legge til en begrensning".

· I felt "Cellereferanse" skriv inn celleadressen til venstre side av en bestemt begrensning, for eksempel $ B$18 . Dette kan gjøres enten fra tastaturet eller ved å velge ønsket celle direkte i skjermskjemaet med musen.

· I samsvar med betingelsene for oppgave (1), velg det nødvendige tegnet i skiltfeltet, for eksempel, .

· I felt "Begrensning" skriv inn celleadressen til høyre side av den aktuelle begrensningen, for eksempel $ D$18 .

· Skriv inn restriksjoner på samme måte: $ B$19<=$ D$19 , $ B$20<=$ D$20 .

· Bekreft inntastingen av alle de ovennevnte betingelsene ved å trykke på knappen OK.

Vindu "Finne en løsning" etter å ha lagt inn alle nødvendige data, er oppgave (1) vist i fig. 5.

Hvis det, når du legger inn en oppgavebetingelse, blir nødvendig å endre eller slette de angitte restriksjonene eller grensebetingelsene, kan dette gjøres ved å klikke på knappene "Endring" eller "Slett"(se fig. 5) .
Løsningen på problemet
Innstilling av parametere for å løse et problem

Oppgaven startes for å løses i vinduet "Å finne en løsning." Men først, for å angi spesifikke parametere for å løse optimaliseringsproblemer for en bestemt klasse, må du trykke på knappen "Alternativer" og fyll ut noen felt i vinduet "Alternativer for løsningssøk"(Fig. 7).

Ris. 7 - Løsningssøkeparametere som passer for de fleste LP-problemer

Parameter "Maksimal tid" tjener til å tildele tid (i sekunder) tildelt for å løse et problem. Du kan angi en tid i dette feltet som ikke overstiger 32 767 sekunder (mer enn 9 timer).

Parameter "Begrens antall iterasjoner" tjener til å kontrollere tiden som kreves for å løse et problem ved å begrense antall mellomliggende beregninger. I feltet kan du angi antall iterasjoner som ikke overstiger 32 767.

Parameter "Relativ feil" tjener til å spesifisere nøyaktigheten som cellens samsvar med målverdien eller dens tilnærming til de spesifiserte grensene bestemmes med. Feltet må inneholde et tall fra 0 til 1. Enn mindre antall desimaler i det angitte tallet, den under nøyaktighet. Høy nøyaktighet vil øke tiden det tar før optimaliseringsprosessen konvergerer.

Parameter "Toleranse" tjener til å sette toleransen for avvik fra den optimale løsningen i heltallsproblemer. Når du spesifiserer en større toleranse, avsluttes søket etter en løsning raskere.

Parameter "Konvergens" gjelder kun ved løsning av ikke-lineære problemer. Merk av i boksen "Lineær modell" gir akselerasjon av søket etter en løsning på et lineært problem gjennom bruk av simpleksmetoden.

Bekreft innstillingene ved å trykke på knappen " OK" .
Starter et problem å løse

Løsningsoppgaven startes fra vinduet "Finne en løsning" ved å trykke på en knapp "Løpe".

Etter å ha begynt å løse LP-problemet, vises et vindu på skjermen "Løsningssøkeresultater" med en melding om den vellykkede løsningen av problemet presentert i fig. 8.

Ris. 8 -. Melding om vellykket løsning av oppgaven

Utseendet til en annen melding indikerer ikke arten av den optimale løsningen på problemet, men snarere at det ble gjort feil ved å angi betingelsene for problemet i Excel. feil, hindrer Excel i å finne den optimale løsningen som faktisk eksisterer.

Hvis, når du fyller ut feltene i vinduet "Finne en løsning" Det ble gjort feil som ikke tillot Excel å bruke simpleksmetoden for å løse problemet eller fullføre løsningen, og etter å ha startet løsningsoppgaven, vil en tilsvarende melding vises på skjermen som indikerer årsaken til at løsningen ikke ble funnet. Noen ganger er parameterverdien for liten "Relativ feil" lar oss ikke finne den optimale løsningen. For å rette opp denne situasjonen, øk feilen bit for bit, for eksempel fra 0,000001 til 0,00001 osv.

I vinduet "Løsningssøkeresultater" Navnene på tre typer rapporter presenteres: «Resultater», «Bærekraftighet», «Begrensninger». De er nødvendige når man analyserer den resulterende løsningen for følsomhet. For å motta svaret (verdier av variabler, digitale funksjoner og venstre deler av restriksjoner) direkte på skjermen, trykk ganske enkelt på knappen " OK". Etter dette vises den optimale løsningen på problemet på skjermen (fig. 9).

Fig.9 - Skjermform av problemet (1) etter å ha oppnådd løsningen

Introduksjon

4.1. Innledende data

4.2. Formler for beregninger

4.3. Fylle ut dialogboksen Finn løsning

4.4. Løsningsresultater

Konklusjon

Referanser

Introduksjon

lineær programmering excel optimalisering problem

Løsningen på et bredt spekter av problemer i den elektriske kraftindustrien og andre sektorer av den nasjonale økonomien er basert på optimalisering av et komplekst sett av avhengigheter beskrevet matematisk ved hjelp av en viss "objektiv funksjon" (TF). Lignende funksjoner kan skrives for å bestemme kostnadene for drivstoff for kraftverk, tap av elektrisitet under transporten fra kraftverket til forbrukerne, og mange andre problematiske oppgaver. I slike tilfeller er det nødvendig å finne CF under visse begrensninger pålagt variablene. Hvis CF lineært avhenger av variablene som er inkludert i sammensetningen og alle restriksjoner danner et lineært system av ligninger og ulikheter, så en slik spesiell form optimaliseringsproblem kalt "lineære programmeringsproblemer."

Emnet for kursarbeidet er "Løse lineære programmeringsproblemer i MS Excel", ved å bruke eksempelet på et "transportproblem" hentet fra feltet generell energi, for å få praktiske ferdigheter i å bruke Microsoft Excel-regneark og løse optimaliseringsproblemer for lineær programmering .

1. Innledende data for å løse problemet

Opprinnelige data inkluderer - et layoutdiagram over kullbassenger (CB) og elektriske kraftstasjoner (PP), som indikerer transportforbindelser mellom dem, tabeller som inneholder informasjon om årlig produktivitet og spesifikk pris på CB-drivstoff, installert kapasitet, antall timer med bruk av installert kapasitet og spesifikt forbruk drivstoff på ES, avstander mellom UB og ES og enhetskostnaden for transport av drivstoff langs UB-ES-rutene.

Figur 1. Innledende data

2. Kort informasjon Om regneark MS Excel

Ris. 2. Programvindusvisning

Regnearkprosesser er programvarepakker utviklet for å lage regneark og manipulere dataene deres. Bruken av regneark forenkler arbeidet med data og lar deg automatisere beregninger uten bruk av spesiell programmering. Den mest utbredte bruken er i økonomiske og regnskapsmessige beregninger. MS Excel gir brukeren muligheten til å:

.Bruk komplekse formler som inneholder innebygde funksjoner.

2.Organiser forbindelser mellom celler og tabeller, mens endring av data i kildetabellene automatisk endrer resultatene i de resulterende tabellene.

.Lag pivottabeller.

.Bruk sortering og filtrering av data på tabeller.

.Utfør datakonsolidering (kombiner data fra flere tabeller til én).

.Bruk skript - navngitte matriser med kildedata, hvorfra de endelige totale verdiene dannes i samme tabell.

.Utfør automatisk søk etter feil i formler.

.Beskytt data.

.Bruk datastrukturering (skjul og vis deler av tabeller).

.Bruk autofullføring.

.Bruk makroer.

.Bygg diagrammer.

.Bruk autokorrektur og stavekontroll.

.Bruk stiler, maler, automatisk formatering.

.Utveksle data med andre applikasjoner.

Nøkkelkonsepter:

.Arbeidsbok - grunnleggende dokumenter, lagret i en fil.

2.Ark (volum: 256 kolonner, 65536 rader).

.En celle er den minste strukturelle enheten for dataplassering.

.Celleadresse - bestemmer plasseringen av cellen i tabellen.

.En formel er en matematisk notasjon av beregninger.

.Link - registrer celleadressen som en del av en formel.

.En funksjon er en matematisk notasjon som indikerer utførelsen av visse beregningsoperasjoner. Består av et navn og argumenter.

Datainput:

Dataene kan være av følgende typer -

· Tall.

· Tekst.

· Funksjoner.

· Formler.

Du kan gå inn -

· I celler.

· Til formellinjen.

Hvis ######## vises på skjermen i en celle etter inntasting, betyr det at tallet er langt og ikke passer inn i cellen, så må du øke bredden på cellen.

Formler- bestemme hvordan verdiene i cellene er relatert til hverandre. De. Dataene i cellen hentes ikke ved utfylling, men beregnes automatisk. Når du endrer innholdet i cellene det refereres til i en formel, endres også resultatet i den beregnede cellen. Alle formler begynner med =. Mer kan følge -

· Cellereferanse (for eksempel A6).

· Funksjon.

· Aritmetisk operator (+, -, /, *).

· Sammenligningsoperatorer (>,<, <=, =>, =).

Du kan legge inn formler direkte i en celle, men det er mer praktisk å skrive inn ved hjelp av formellinjen.

Funksjoner– Dette er standardformler for å utføre enkelte oppgaver. Funksjoner brukes bare i formler.

Vei: Sett inn - Funksjoneller klikk på i formellinjen = . En dialogboks vises med ti nylig brukte funksjoner. For å utvide listen, velg Andre funksjoner...,en annen dialogboks åpnes, hvor funksjoner er gruppert etter type (kategori), en beskrivelse av formålet med funksjonen og deres parametere er gitt.

En fullstendig beskrivelse av arbeid med MS Excel-regneark finnes i lærebøker og håndbøker (spesialiserte).

3. Matematisk formulering av oppgaven

Basert på kriteriet om minimum drivstoffkostnader for ES i den spesifiserte strømforsyningsregionen, er det nødvendig å bestemme deres optimale drivstoffforsyning fra tre kullbassenger, under hensyntagen til begrensningene for behovene til ES og produktiviteten til UB.

De første dataene for problemet og variablene som skal bestemmes under løsningen kan presenteres i form av tabell 3

Databetegnelse:

I 1 des , IN ub2 , IN ub3 - produktivitet av kullbassenger, tusen tonn;

MED 1 des , MED ub2 , MED ub3 - drivstoffkostnader i kullbassenger, c.u./tonn;

L på - lengde på jernbanesporet mellom UB til ES, km;

MED på - spesifikke kostnader for transport av drivstoff langs ruten fra UB til ES, c.u./tonn*km (C 11=C 12=C 13=C 21=C 22=C 23=C 31=C 32=C 33);

I på - volum drivstoff levert fra UB til kraftverket, tusen tonn;

I ES1 , IN ES2 , IN ES3 - årlig drivstoffbehov for henholdsvis første, andre og tredje kraftverk, tusen tonn;

I på - er parametrene til målfunksjonsvariablene som skal bestemmes i prosessen med å løse problemet;

Det er nødvendig å bestemme det optimale drivstoffvolumet (V på ), levert fra UB til hver av ES, hvor de totale drivstoffkostnadene for alle tre ES vil være minimale.

Den objektive funksjonen som skal optimaliseres i prosessen med å løse problemet vil være de totale drivstoffkostnadene for alle tre ES.

4. Løsning av problemet med lineær programmering

.1 Startdata

Ris. 4. Startdata

4.2 Formler for beregninger

Fig.5. Mellomberegninger

4.3 Fylle ut dialogboksen "Søk etter løsning".

Ris. 6. Optimaliseringsprosess.

Fig.6.1 Innstilling av begrensninger (drivstoff må være>0).

Fig.6.2 Sette restriksjoner (antall importer = forbrukt mengde drivstoff).

Fig.6.3 Innstilling av restriksjoner (årlig forsendelse, ikke overskrid produksjon UB1).

Fig. 6.4 Innstilling av restriksjoner (årlig forsendelse, ikke overskrid produksjon UB2).

Fig. 6.5 Innstilling av restriksjoner (årlig forsendelse, ikke overskrid produksjon UB3).

.4 Løsningsresultater

Fig.8. Resultater av å løse problemet

Svar: Mengde drivstoff (tusen tonn) levert til:

ES4 fra UB1 er 118,17 tonn;

ES6 fra UB1 er 545,66 tonn;

ES5 fra UB2 er 19,66 tonn;

ES6 fra UB2 er 180,34 tonn;

ES5 fra UB3 er 277,94 tonn;

ES6 fra UB3 er 526.00t;

ES4 totalt 118,17 tonn;

ES5 totalt 297,60 tonn;

ES6 totalt 1252.00t;

Drivstoffkostnadene beløp seg til (cu):

For ES4 - 496314.00.

For ES5 - 227064.75.

For ES6 - 23099064.78.

De totale kostnadene for alle ES er 23822443,53 USD;

Konklusjon

Kort informasjon om MS Excel-regneark. Løsning av et lineært programmeringsproblem. Løsning ved hjelp av Microsoft Excel-verktøy for et økonomisk optimaliseringsproblem, ved å bruke eksempelet " transportproblem". Funksjoner ved utformingen av et MS Word-dokument.

I kursarbeid viser hvordan man oppretter og arbeider når man designer et MS Word-dokument, innenfor rammen av hvilket løsningen på et økonomisk optimaliseringsproblem vurderes, ved å bruke eksemplet på et "transportproblem" hentet fra feltet generell energi, Microsoft betyr Utmerke.

La oss se på lineær programmering i Excel ved å bruke eksempelet på et tidligere løst problem.

Oppgave. Nikolai Kuznetsov driver et lite mekanisk anlegg. Neste måned planlegger han å produsere to produkter (A og B), for hvilke den spesifikke marginale fortjenesten er estimert til henholdsvis 2500 og 3500 rubler. Begge produktene krever maskinering, råvarer og arbeidskostnader å lage. Det tar 3 timer å produsere hver enhet av produkt A. maskinering, 16 enheter råvarer og 6 enheter arbeidskraft. De tilsvarende enhetskravene for produkt B er 10, 4 og 6. Nicholas spår at neste måned kan han levere 330 timer maskinering, 400 enheter råvarer og 240 enheter arbeidskraft. Teknologien i produksjonsprosessen er slik at det må produseres minst 12 enheter av produkt B i hver bestemt måned. Trenger å bestemme antall enheter av produkter A og B som Nikolay må produsere den neste måneden for å maksimere dekningsbidraget.

Last ned notatet i format, eksempel i format

1. La oss bruke den matematiske modellen som er konstruert. Dette er modellen:

Maksimer: Z = 2500 * x 1 + 3500 * x 2

Forutsatt at: 3 * x 1 + 10 * x 2 ≤ 330

16 * x 1 + 4 * x 2 ≤ 400

6 * x 1 + 6 * x 2 ≤ 240

2. La oss lage et skjermskjema og legge inn de første dataene i det (fig. 1).

Ris. 1. Skjermskjema for inntasting av data for et lineært programmeringsproblem

Vær oppmerksom på formelen i celle C7. Dette er objektivfunksjonsformelen. På samme måte legges formler for å beregne venstre side av restriksjonene inn i cellene C16:C18.

3. Sjekk om du har "Search for a solution"-tillegget installert (fig. 2), hopp over dette punktet.

Ris. 2. Søk etter løsning-tillegget er installert; Datafane, Analysegruppe

Hvis du ikke finner "Søk etter en løsning"-tillegget på Excel-båndet, klikker du på knappen Microsoft Office, og deretter Excel-alternativer (Figur 3).

Ris. 3. Excel-alternativer

Velg tilleggslinjen, og deretter helt nederst i "Administrer"-vinduet Microsoft-tillegg Excel" velg "Go" (fig. 4).

Ris. 4. Excel-tillegg

I vinduet "Tillegg" merker du av for "Søk etter en løsning" og klikker OK (fig. 5). (Hvis Solver ikke er oppført i tilleggsfeltet, klikker du på Bla gjennom for å finne tillegget. Hvis du mottar en melding om at Solver-tillegget ikke er installert på datamaskinen, klikker du Ja for å installere det.)

Ris. 5. Aktivering av tillegget "Søk etter en løsning".

Etter å ha lastet inn tillegget for å søke etter en løsning, blir kommandoen Søk etter en løsning tilgjengelig i Analyse-gruppen på fanen Data (fig. 2).

4. Neste trinn er å fylle ut Excel-vindu"Finne en løsning" (fig. 6)

Ris. 6. Fylle ut "Søk etter en løsning"-vinduet

I «Sett målcelle»-feltet velger du cellen med verdien av målfunksjonen – $C$7. Vi velger om vi skal maksimere eller minimere den objektive funksjonen. I feltet "Endre celler" velger du celler med verdiene til de ønskede variablene $C$4:$D$4 (så lenge de inneholder nuller eller tomme). I "Begrensninger"-området, ved å bruke "Legg til" -knappen, legger vi alle begrensningene til modellen vår. Klikk "Kjør". I vinduet "Solution Search Result" som vises, velg alle tre rapporttypene (fig. 7) og klikk OK. Disse rapportene er nødvendige for å analysere den resulterende løsningen. Du kan lese mer om dataene som presenteres i rapportene.

Ris. 7. Velge rapporttyper

Verdiene til den maksimerte objektivfunksjonen dukket opp på hovedarket - 130 000 rubler. og variable parametere x 1 = 10 og x 2 = 30. Derfor, for å maksimere marginalinntekten, bør Nicholas produsere 10 enheter av produkt A og 30 enheter av produkt B neste måned.

Hvis noe annet vises i stedet for "Solution Search Result"-vinduet, klarte ikke Excel å finne en løsning. Sjekk at "Søk etter en løsning"-vinduet er riktig fylt ut. Og ett lite triks til. Prøv å redusere presisjonen i løsningssøket. For å gjøre dette, i «Søk etter en løsning»-vinduet klikker du på Parameters (fig. 8.) og øker beregningsfeilen, for eksempel til 0,001. Noen ganger, på grunn av høy nøyaktighet, har ikke Excel tid til å finne en løsning i 100 iterasjoner. Du kan lese mer om parametrene for å finne en løsning.

Ris. 8. Økning i regnefeil

Det er nødvendig å bestemme i hvilken mengde det er nødvendig å produsere produkter av fire typer Prod1, Prod2, Prod3, Prod4, hvis produksjon krever tre typer ressurser: arbeidskraft, råvarer og økonomi. Mengden av hver type ressurs som kreves for å produsere en produksjonsenhet av denne typen, kalles forbruksraten. Forbruksrater, samt fortjeneste mottatt fra salg av en enhet av hver type produkt, er vist i fig. 1.

Ressurs	Forts1	Prod2	Prod3	Prod4	Skilt	Tilgjengelighet
Profitt
Arbeid
Råvarer
Finansiere

Bilde 1.

Matematisk modell oppgaven har formen:

hvor x j er mengden produserte produkter av den jte typen; F – målfunksjon; venstre side av begrensningsuttrykkene indikerer verdiene nødvendig ressurs, og høyresiden viser mengden tilgjengelig ressurs.

Legge inn oppgavebetingelser

For å løse problemet med ved hjelp av Excel Du bør lage et skjema for å legge inn de første dataene og skrive dem inn. Inndataskjemaet er vist i fig. 2.

I celle F6 introduseres et uttrykk for den objektive funksjonen som summen av produktene av profittverdiene fra utgivelsen av en produktenhet av hver type med antall produkter av den tilsvarende typen. For klarhets skyld, i fig. Figur 3 viser skjemaet for å legge inn startdata i formelutdatamodus.

De venstre delene av restriksjonene for ressurser av hver type legges inn i cellene F8:F10.

Figur 2.

Figur 3.

Løse et lineært programmeringsproblem

For å løse lineære programmeringsproblemer i Excel, bruk kraftig verktøy, kalt Å finne en løsning . Tilgang til Søk etter en løsning utføres fra menyen Service , vises dialogboksen Søk etter en løsning på skjermen (fig. 4).

Figur 4.

Å angi betingelsene for et problem for å finne løsningen består av følgende trinn:

1 Tilordne en målfunksjon ved å plassere markøren i feltet Angi målcelle vindu Søk etter en løsning og klikk i celle F6 i inndataskjemaet;

2 Slå på bryteren for verdien av objektivfunksjonen, dvs. angi det Lik Maksimal verdi ;

3 Skriv inn adressene til variablene som skal endres (x j): for å gjøre dette, plasser markøren i feltet Bytte celler vindu Søk etter en løsning, og velg deretter celleområdet B3:E3 i inndataskjemaet;

4 Trykk på knappen Legg til Løsningssøkevinduer for å angi begrensninger for et lineært programmeringsproblem; et vindu vises på skjermen Legger til en begrensning (Fig. 5) :

Skriv inn grensebetingelser for variablene x j (x j ³0), for dette i feltet Cellereferanse angi celle B3 som tilsvarer x 1, velg ønsket tegn (³) fra listen i feltet Begrensning angi cellen til inndataskjemaet der den tilsvarende verdien til grensebetingelsen er lagret (celle B4), klikk på knappen Legg til ; gjenta de beskrevne trinnene for variablene x 2, x 3 og x 4;

Angi begrensninger for hver type ressurs i feltet Cellereferanse vindu Legger til en begrensning angi celle F9 i inndataskjemaet, som inneholder uttrykket til venstre side av begrensningen pålagt arbeidsressurser i feltene Begrensning angi £-tegnet og H9-adressen på høyre side av begrensningen, trykk på knappen Legg til ; på samme måte innføre restriksjoner på andre typer ressurser;

Etter å ha lagt inn den siste begrensningen, i stedet for Legg til trykk OK og gå tilbake til vinduet Søk etter en løsning.

Figur 5.

Å løse et lineært programmeringsproblem begynner med å stille inn søkeparametrene:

I vinduet Å finne en løsning trykk på knappen Alternativer , vises et vindu på skjermen Løsningssøkealternativer (fig. 6);

Avmerkingsboks Lineær modell, som sikrer bruk av simpleksmetoden;

Angi maksimalt antall iterasjoner (standard er 100, som er egnet for å løse de fleste problemer);

Avmerkingsboks , hvis du trenger å gå gjennom alle stadier av å søke etter den optimale løsningen;

Klikk OK , gå tilbake til vinduet Å finne en løsning .

Figur 6.

For å løse problemet, trykk på knappen Henrette i vinduet Å finne en løsning , er det et vindu på skjermen Løsningssøkeresultater (Fig. 7), som inneholder meldingen Løsningen er funnet. Alle restriksjoner og optimalitetsbetingelser er oppfylt. Hvis betingelsene for problemet er inkonsekvente, vises en melding Søket finner ikke passende løsning . Hvis objektivfunksjonen ikke er begrenset, vises meldingen Målcelleverdier konvergerer ikke.

Figur 7.

For det aktuelle eksemplet er det funnet en løsning, og resultatet av den optimale løsningen på problemet vises i form av input: verdien av objektivfunksjonen som tilsvarer maksimal fortjeneste og lik 1320, angitt i celle F6 i inndataskjemaet, den optimale produksjonsplanen x 1 =10, x 2 =0, x 3 =6, x 4 =0 er angitt i cellene B3:C3 i inndataskjemaet (fig. 8).

Mengden ressurser som brukes til å produsere produkter vises i cellene F9:F11: arbeid - 16, råvarer - 84, økonomi - 100.

Figur 8.

Hvis, når du angir parametere i vinduet Løsningssøkealternativer (Fig. 6) avkrysningsboksen ble krysset av Vis iterasjonsresultater , så vil alle søketrinn vises sekvensielt. Et vindu vises på skjermen (Fig. 9). I dette tilfellet vil gjeldende verdier for variablene og målfunksjonene vises i inndataskjemaet. Dermed blir resultatene av den første iterasjonen av å søke etter en løsning på det opprinnelige problemet presentert i inndataskjemaet i figur 10.

Figur 9.

Figur 10.

For å fortsette å søke etter en løsning, klikk på knappen Fortsette i vinduet Nåværende situasjon leter etter en løsning .

Analyse av den optimale løsningen

Før vi går videre til analysen av løsningsresultatene, la oss presentere det opprinnelige problemet i skjemaet

ved å introdusere tilleggsvariabler for i, som representerer verdiene til ubrukte ressurser.

La oss lage et dobbeltproblem for det opprinnelige problemet og introdusere ytterligere doble variable v i .

Analyse av resultatene av søket etter en løsning vil tillate oss å koble dem med variablene til den innledende og doble problemer.

Ved hjelp av et vindu Løsningssøkeresultater Du kan hente frem tre typer rapporter som lar deg analysere den optimale løsningen som er funnet:

resultater,

Bærekraft,

Grenser.

Å ringe en rapport i et felt Rapport type fremheve tittelen riktig type og trykk OK .

1 Resultatrapport(Fig. 11) består av tre tabeller:

Tabell 1 inneholder informasjon om objektivfunksjonen; i kolonne Opprinnelig verdien av målfunksjonen er angitt før beregningene begynner;

Tabell 2 inneholder verdiene til de nødvendige variablene x j oppnådd som et resultat av å løse problemet (optimal produksjonsplan);

Tabell 3 viser resultatene av den optimale løsningen for begrensningene og for randbetingelsene.

Til Begrensninger i kolonnen Formel avhengighetene som ble angitt ved innstilling av begrensninger i vinduet vises Å finne en løsning ; i kolonnen Betydning verdiene til ressursen som brukes er angitt; i kolonnen Forskjell viser mengden ubrukt ressurs. Hvis ressursen er fullt brukt, så i kolonnen Stat meldingen vises i slekt ; hvis ressursen ikke er fullt brukt, indikerer denne kolonnen ikke tilkoblet. Til Grensebetingelser lignende verdier er gitt med den eneste forskjellen at i stedet for en ubrukt ressurs, vises forskjellen mellom verdien av variabelen x j i den funnet optimal løsning og grensebetingelsen spesifisert for den (x j ³0).

Det står i kolonnen Forskjell du kan se verdiene til tilleggsvariablene y i det opprinnelige problemet i formulering (2). Her y 1 =y 3 =0, dvs. mengden ubrukt arbeidskraft og økonomiske ressurser er null. Disse ressursene er fullt utnyttet. Samtidig er mengden ubrukte ressurser for råvarer y 2 = 26, som betyr at det er et overskudd av råvarer.

Figur 11.

2 Bærekraftsrapport(Fig. 12)består av to tabeller.

Tabell 1 viser følgende verdier:

Resultatet av å løse problemet (optimal utgivelsesplan);

- Normir. pris, dvs. verdier som viser hvor mye målfunksjonen vil endre seg når tvungen inkludering produksjonsenheter av tilsvarende type inn i den optimale planen;

Objektive funksjonskoeffisienter;

Grenseverdier for økningen av koeffisientene til målfunksjonen der den optimale produksjonsplanen opprettholdes.

Tabell 2 inneholder lignende data for restriksjoner:

Mengde ressurser brukt;

- Skyggepris, som viser hvordan målfunksjonen endres når verdien av den tilsvarende ressursen endres med én;

Gyldige verdierøkninger av ressurser der den optimale produksjonsplanen opprettholdes.

Figur 12.

Bærekraftsrapporten åpner for doble vurderinger.

Som kjent viser doble variable z i hvordan objektivfunksjonen endres når ressursen av den i-te typen endres med én. I en Excel-rapport kalles det doble estimatet Skyggepris.

I vårt eksempel er ikke råmaterialet fullt ut brukt og ressursen y 2 = 26. En økning i mengden råvarer, for eksempel til 111, vil selvsagt ikke medføre en økning i målfunksjonen. Derfor, for den andre begrensningen, er den doble variabelen z 2 =0. Altså hvis iht denne ressursen det er en reserve, da tilleggsvariabel vil være større enn null, og dobbel vurdering av denne begrensningen er null.

I eksemplet under vurdering ble arbeidsressurser og økonomi fullt ut brukt, så tilleggsvariablene deres er lik null (y 1 = y 3 = 0). Hvis en ressurs er fullt brukt, vil økningen eller reduksjonen påvirke volumet av produksjonen, og dermed verdien av målfunksjonen. Doble estimater av restriksjoner på arbeidskraft og økonomiske ressurser er forskjellige fra null, dvs. z 1 = 20, z 3 = 10.

Verdiene til de doble estimatene finnes i Bærekraftsrapport, i tabell 2, i kolonnen Skyggepris.

Med en økning (reduksjon) i arbeidsressursene med en enhet vil målfunksjonen øke (minske) med 20 enheter og være lik

F=1320+20×1=1340 (med forstørrelse).

Tilsvarende, når volumet av finans øker med én enhet, vil målfunksjonen være

F=1320+10×1=1330.

Her, i grafene Tillatt økning Og Tillatt reduksjon Tabell 2 viser tillatte grenser for endring av ressursmengden av typen j. For eksempel, når økningen i verdien av arbeidsressursene endres fra –6 til 3,55, som vist i tabellen, bevares strukturen til den optimale løsningen, det vil si at den største fortjenesten leveres av produksjonen til Prod1 og Prod3, men i forskjellige mengder.

Ytterligere doble variabler reflekteres også i Bærekraftsrapport i kolonnen Normir. pris tabell 1.

Dersom hovedvariablene ikke er inkludert i den optimale løsningen, dvs. er lik null (i eksemplet x 2 =x 4 =0), så har de tilsvarende tilleggsvariablene positive verdier (v 2 =10, v 4 =20). Hvis hovedvariablene er inkludert i den optimale løsningen (x 1 =10, x 3 =6), så er deres ekstra dualvariabler lik null (v 1 =0, v 3 =0).

Disse verdiene viser hvor mye objektivfunksjonen vil avta (derfor minustegnet i verdiene til variablene v 2 og v 4) med tvungen utløsning av en enhet av dette produktet. Derfor, hvis vi ønsker å tvangsfrigjøre en produktenhet av typen Prod3, vil objektivfunksjonen reduseres med 10 enheter og vil være lik 1320 -10×1 = 1310.

La oss betegne med Dс j endringen i koeffisientene til objektivfunksjonen i den opprinnelige modellen (1). Disse koeffisientene bestemmer fortjenesten mottatt ved salg av en produktenhet av den jte typen.

I grafer Tillatt økning Og Tillatt reduksjon tabell 1 Bærekraftsrapport grensene for endring Dс j er vist der strukturen er bevart optimal plan, dvs. Det vil være lønnsomt å fortsette å produsere produkter av typen Prodj. For eksempel, hvis Dc 1 endres innenfor -12 £ Dc 1 £ 40, som vist i rapporten, vil det fortsatt være lønnsomt å produsere produkter av typen Prod1. I dette tilfellet vil verdien av objektivfunksjonen være F=1320+x 1 ×Dс j =1320+10×Dс j .

3 Begrensningsrapport vist i fig. 13. Den viser innenfor hvilke grenser verdiene x j inkludert i den optimale løsningen kan endres samtidig som strukturen til den optimale løsningen opprettholdes. I tillegg, for hver type produkt, er verdiene til den objektive funksjonen gitt, oppnådd ved å erstatte verdien av den nedre grensen for produksjon av produkter av tilsvarende type i den optimale løsningen med konstante verdier for produksjon av andre typer. For eksempel, hvis vi for den optimale løsningen x 1 =10, x 2 =0, x 3 =6, x 4 =0 setter x 1 =0 (nedre grense) med x 2, x 3 og x 4 uendret, så verdien av objektivfunksjonen vil være lik 60×0+70×0+120×6+130×0=720.

La oss se på et eksempel på et lineært programmeringsproblem.

Det er nødvendig å bestemme i hvilken mengde det er nødvendig å produsere produkter av fire typer Prod1, Prod2, Prod3, Prod4, hvis produksjon krever tre typer ressurser: arbeidskraft, råvarer og økonomi. Mengden av hver type ressurs som kreves for å produsere en produktenhet av en gitt type kalles forbruksraten. Forbruksrater, samt fortjeneste mottatt fra salg av en enhet av hver type produkt, er vist i fig. 1.

Ressurs	Forts1	Prod2	Prod3	Prod4	Skilt	Tilgjengelighet
Profitt
Arbeid
Råvarer
Finansiere

Bilde 1.

Den matematiske modellen av problemet har formen:

Legge inn oppgavebetingelser

For å løse problemet ved hjelp av Excel, bør du lage et skjema for å legge inn innledende data og skrive det inn. Inndataskjemaet er vist i fig. 2.

De venstre delene av restriksjonene for ressurser av hver type legges inn i cellene F8:F10.

Figur 2.

Figur 3.

Løse et lineært programmeringsproblem

For å løse lineære programmeringsproblemer i Excel bruker du et kraftig verktøy kalt Å finne en løsning . Tilgang til Søk etter en løsning utføres fra menyen Service , vises dialogboksen Søk etter en løsning på skjermen (fig. 4).

Figur 4.

Å angi betingelsene for et problem for å finne løsningen består av følgende trinn:

1 Tilordne en målfunksjon ved å plassere markøren i feltet Angi målcelle vindu Søk etter en løsning og klikk i celle F6 i inndataskjemaet;

2 Slå på bryteren for verdien av objektivfunksjonen, dvs. angi det Lik maksimalverdien ;

4 Trykk på knappen Legg til Løsningssøkevinduer for å angi begrensninger for et lineært programmeringsproblem; et vindu vises på skjermen Legger til en begrensning (Fig. 5) :

Etter å ha lagt inn den siste begrensningen, i stedet for Legg til trykk OK og gå tilbake til vinduet Søk etter en løsning.

Figur 5.

Å løse et lineært programmeringsproblem begynner med å stille inn søkeparametrene:

I vinduet Å finne en løsning trykk på knappen Alternativer , vises et vindu på skjermen Løsningssøkealternativer (fig. 6);

Avmerkingsboks Lineær modell, som sikrer bruk av simpleksmetoden;

Angi maksimalt antall iterasjoner (standard er 100, som er egnet for å løse de fleste problemer);

Avmerkingsboks , hvis du trenger å gå gjennom alle stadier av å søke etter den optimale løsningen;

Klikk OK , gå tilbake til vinduet Å finne en løsning .

Figur 6.

For å løse problemet, trykk på knappen Henrette i vinduet Å finne en løsning , er det et vindu på skjermen Løsningssøkeresultater (Fig. 7), som inneholder meldingen Løsningen er funnet. Alle restriksjoner og optimalitetsbetingelser er oppfylt. Hvis betingelsene for problemet er inkonsekvente, vises en melding Søk kan ikke finne en passende løsning. Hvis objektivfunksjonen ikke er begrenset, vises meldingen Målcelleverdier konvergerer ikke.

Figur 7.

For eksempelet som vurderes er det funnet en løsning, og resultatet av den optimale løsningen på problemet vises i inndataskjemaet: verdien av målfunksjonen som tilsvarer maksimal fortjeneste og lik 1320 er indikert i celle F6 i inndataskjema, den optimale produksjonsplanen x 1 =10, x 2 =0, x 3 =6, x 4 =0 er angitt i cellene B3:C3 i inndataskjemaet (fig. 8).

Mengden ressurser som brukes til å produsere produkter vises i cellene F9:F11: arbeid - 16, råvarer - 84, økonomi - 100.

Figur 8.

Figur 9.

Figur 10.

For å fortsette å søke etter en løsning, klikk på knappen Fortsette i vinduet Nåværende status for søket etter en løsning .

Analyse av den optimale løsningen

Før vi går videre til analysen av løsningsresultatene, la oss presentere det opprinnelige problemet i skjemaet

ved å introdusere tilleggsvariabler for i, som representerer verdiene til ubrukte ressurser.

La oss lage et dobbeltproblem for det opprinnelige problemet og introdusere ytterligere doble variable v i .

Analyse av resultatene av søket etter en løsning vil tillate oss å koble dem med variablene til de opprinnelige og doble problemene.

Ved hjelp av et vindu Løsningssøkeresultater Du kan hente frem tre typer rapporter som lar deg analysere den optimale løsningen som er funnet:

resultater,

Bærekraft,

Grenser.

Å ringe en rapport i et felt Rapport type uthev navnet på ønsket type og trykk OK .

1 Resultatrapport(Fig. 11) består av tre tabeller:

Tabell 1 inneholder informasjon om objektivfunksjonen; i kolonne Opprinnelig verdien av målfunksjonen er angitt før beregningene begynner;

Tabell 2 inneholder verdiene til de nødvendige variablene x j oppnådd som et resultat av å løse problemet (optimal produksjonsplan);

Tabell 3 viser resultatene av den optimale løsningen for begrensningene og for randbetingelsene.

Til Begrensninger i kolonnen Formel avhengighetene som ble angitt ved innstilling av begrensninger i vinduet vises Å finne en løsning ; i kolonnen Betydning verdiene til ressursen som brukes er angitt; i kolonnen Forskjell viser mengden ubrukt ressurs. Hvis ressursen er fullt brukt, så i kolonnen Stat meldingen vises i slekt ; hvis ressursen ikke er fullt brukt, indikerer denne kolonnen ikke tilkoblet. Til Grensebetingelser lignende verdier er gitt med den eneste forskjellen at i stedet for en ubrukt ressurs, vises forskjellen mellom verdien av variabelen x j i den funnet optimale løsningen og grensebetingelsen spesifisert for den (x j ³0).

Figur 11.

2 Bærekraftsrapport(Fig. 12)består av to tabeller.

Tabell 1 viser følgende verdier:

Resultatet av å løse problemet (optimal utgivelsesplan);

- Normir. pris, dvs. verdier som viser hvor mye den objektive funksjonen vil endre seg når en produksjonsenhet av tilsvarende type blir tvunget til å inkluderes i den optimale planen;

Objektive funksjonskoeffisienter;

Grenseverdier for økningen av koeffisientene til målfunksjonen der den optimale produksjonsplanen opprettholdes.

Tabell 2 inneholder lignende data for restriksjoner:

Mengde ressurser brukt;

- Skyggepris, som viser hvordan målfunksjonen endres når verdien av den tilsvarende ressursen endres med én;

Akseptable verdier av ressursøkninger der den optimale produksjonsplanen opprettholdes.

Figur 12.

Bærekraftsrapporten åpner for doble vurderinger.

Som kjent viser doble variable z i hvordan objektivfunksjonen endres når ressursen av den i-te typen endres med én. I en Excel-rapport kalles det doble estimatet Skyggepris.

I vårt eksempel er ikke råmaterialet fullt ut brukt og ressursen y 2 = 26. En økning i mengden råvarer, for eksempel til 111, vil selvsagt ikke medføre en økning i målfunksjonen. Derfor, for den andre begrensningen, er den doble variabelen z 2 =0. Altså, hvis det er en reserve for denne ressursen, da tilleggsvariabel vil være større enn null, og dobbel vurdering av denne begrensningen er null.

Verdiene til de doble estimatene finnes i Bærekraftsrapport, i tabell 2, i kolonnen Skyggepris.

Med en økning (reduksjon) i arbeidsressursene med en enhet vil målfunksjonen øke (minske) med 20 enheter og være lik

F=1320+20×1=1340 (med forstørrelse).

Tilsvarende, når volumet av finans øker med én enhet, vil målfunksjonen være

F=1320+10×1=1330.

Ytterligere doble variabler reflekteres også i Bærekraftsrapport i kolonnen Normir. pris tabell 1.

I grafer Tillatt økning Og Tillatt reduksjon tabell 1 Bærekraftsrapport grensene for endring i Dc j er vist hvor strukturen til den optimale planen er bevart, dvs. Det vil være lønnsomt å fortsette å produsere produkter av typen Prodj. For eksempel, hvis Dc 1 endres innenfor -12 £ Dc 1 £ 40, som vist i rapporten, vil det fortsatt være lønnsomt å produsere produkter av typen Prod1. I dette tilfellet vil verdien av objektivfunksjonen være F=1320+x 1 ×Dс j =1320+10×Dс j .