Ladda tilläggsprogrammet Solver | Formulera modellen | Test och fel | Lös modellen
Excel innehåller ett verktyg som kallas lösare som använder tekniker från operationsforskningen för att hitta optimala lösningar för alla typer av beslutsproblem.
Ladda tilläggsprogrammet Solver
För att ladda tilläggsprogrammet för lösare, utför följande steg.
1. Klicka på Alternativ på fliken Arkiv.
2. Under Tillägg, välj Solver-tillägg och klicka på knappen Kör.
3. Markera Solver-tillägg och klicka på OK.
4. Du hittar lösaren på fliken Data i gruppen Analysera.
Formulera modellen
De modell vi ska lösa ser ut så här i Excel.
1. För att formulera denna linjära programmeringsmodell, svara på följande tre frågor.
a. Vilka beslut ska fattas? För detta problem behöver vi Excel för att ta reda på hur mycket som ska beställas för varje produkt (cyklar, mopeder och barnstolar).
b. Vilka är begränsningarna för dessa beslut? Begränsningarna här är att mängden kapital och lagring som används av produkterna inte kan överstiga den begränsade mängd kapital och lagring (tillgängliga resurser). Till exempel använder varje cykel 300 enheter kapital och 0,5 enheter lagring.
c. Vad är det övergripande måttet på prestanda för dessa beslut? Det övergripande måttet på prestanda är den totala vinsten för de tre produkterna, så målet är att maximera denna mängd.
2. För att göra modellen lättare att förstå, skapa följande namngivna intervall.
| Intervallnamn | Celler |
|---|---|
| UnitProfit | C4: E4 |
| OrderSize | C12: E12 |
| Resurser Används | G7: G8 |
| Resurser Tillgängliga | I7: I8 |
| Total vinst | I12 |
3. Sätt i följande tre SUMPRODUCT -funktioner.
Förklaring: Mängden kapital som används är lika med sumprodukten av intervallet C7: E7 och OrderSize. Mängden lagring som används motsvarar sumprodukten av intervallet C8: E8 och OrderSize. Total vinst motsvarar sumprodukten för UnitProfit och OrderSize.
Test och fel
Med denna formulering blir det enkelt att analysera vilken testlösning som helst.
Till exempel, om vi beställer 20 cyklar, 40 mopeder och 100 barnstolar, överstiger inte den totala mängden resurser som används. Denna lösning har en total vinst på 19000.
Det är inte nödvändigt att använda trial and error. Vi ska beskriva nästa hur Excel Solver kan användas för att snabbt hitta den optimala lösningen.
Lös modellen
För att hitta optimal lösning, utför följande steg.
1. Klicka på på fliken Data i gruppen Analysera Lösare.
Ange lösningsparametrarna (läs vidare). Resultatet ska överensstämma med bilden nedan.
Du kan välja att skriva intervallnamnen eller klicka på cellerna i kalkylarket.
2. Ange TotalProfit för målet.
3. Klicka på Max.
4. Ange OrderSize för de ändrade variabelcellerna.
5. Klicka på Lägg till för att ange följande begränsning.
6. Markera "Gör obegränsade variabler icke-negativa" och välj "Simplex LP".
7. Klicka slutligen på Lös.
Resultat:
Den optimala lösningen:
Slutsats: det är optimalt att beställa 94 cyklar och 54 mopeder. Denna lösning ger maximal vinst på 25600. Denna lösning använder alla tillgängliga resurser.
