Formulering van het probleem
Stel dat het bedrijf waar u werkt drie magazijnen heeft, van waaruit de goederen naar vijf van uw winkels gaan, verspreid over Moskou.
Elke winkel kan een bepaalde hoeveelheid goederen verkopen die bij ons bekend zijn. Elk van de magazijnen heeft een beperkte capaciteit. De taak is om rationeel te kiezen uit welk magazijn naar welke winkels de goederen moeten worden geleverd om de totale transportkosten te minimaliseren.
Voordat met de optimalisatie wordt begonnen, is het nodig om een eenvoudige tabel op een Excel-blad samen te stellen - ons wiskundig model dat de situatie beschrijft:
Het is wel verstaan dat:
- De lichtgele tabel (C4:G6) beschrijft de kosten voor het verzenden van één artikel van elk magazijn naar elke winkel.
- Paarse cellen (C15:G14) beschrijven de hoeveelheid goederen die elke winkel moet verkopen.
- Rode cellen (J10:J13) geven de capaciteit van elk magazijn weer - de maximale hoeveelheid goederen die het magazijn kan bevatten.
- Gele (C13:G13) en blauwe (H10:H13) cellen zijn respectievelijk de rij- en kolomsommen voor groene cellen.
- De totale verzendkosten (J18) worden berekend als de som van de producten van het aantal goederen en de bijbehorende verzendkosten - voor de berekening wordt de functie hier gebruikt SUMPRODUCT (SOMPRODUCT).
Onze taak is dus beperkt tot de selectie van optimale waarden van groene cellen. En zodat het totale bedrag voor de lijn (blauwe cellen) de capaciteit van het magazijn (rode cellen) niet overschrijdt, en tegelijkertijd elke winkel de hoeveelheid goederen ontvangt die hij nodig heeft om te verkopen (het bedrag voor elke winkel in de gele cellen moeten zo dicht mogelijk bij de vereisten liggen – paarse cellen).
Oplossing
In de wiskunde zijn dergelijke problemen met het kiezen van de optimale verdeling van hulpbronnen al lang geformuleerd en beschreven. En natuurlijk zijn manieren om ze op te lossen al lang ontwikkeld, niet door botte opsomming (wat erg lang is), maar in een zeer klein aantal iteraties. Excel biedt de gebruiker dergelijke functionaliteit met behulp van een invoegtoepassing. Zoekoplossingen (Oplosser) van het tabblad Data (Datum):
Als op het tabblad Data je Excel heeft zo'n commando niet - het is goed - het betekent dat de invoegtoepassing gewoon nog niet is aangesloten. Om het te activeren open Dien in, Selecteer dan parameters - Add-ons - Over (Opties — Invoegtoepassingen — Ga naar). Vink in het geopende venster het vakje aan naast de regel die we nodig hebben Zoekoplossingen (Oplosser).
Laten we de add-on uitvoeren:
In dit venster moet u de volgende parameters instellen:
- Doelfunctie optimaliseren (Stel in opgeld cel) – hier is het noodzakelijk om het uiteindelijke hoofddoel van onze optimalisatie aan te geven, namelijk roze doos met de totale verzendkosten (J18). De doelcel kan worden geminimaliseerd (als het kosten zijn, zoals in ons geval), gemaximaliseerd (als het bijvoorbeeld winst is) of proberen deze op een bepaalde waarde te brengen (bijvoorbeeld precies passen in het toegewezen budget).
- Variabele cellen wijzigen (By veranderende cellen) - hier geven we de groene cellen aan (C10: G12), door de waarden te variëren waarvan we ons resultaat willen bereiken - de minimale leveringskosten.
- In overeenstemming met beperkingen (Onderwerp naar de Beperkingen) – een lijst met beperkingen waarmee rekening moet worden gehouden bij het optimaliseren. Om beperkingen aan de lijst toe te voegen, klikt u op de knop Toevoegen (Toevoegen) en voer de voorwaarde in het venster dat verschijnt in. In ons geval is dit de vraagbeperking:
en limiet op het maximale volume van magazijnen:
Naast de voor de hand liggende beperkingen die samenhangen met fysieke factoren (capaciteit van magazijnen en transportmiddelen, budget en tijdsdruk, enz.), is het soms nodig om beperkingen "speciaal voor Excel" toe te voegen. Excel kan er bijvoorbeeld voor zorgen dat u de bezorgkosten eenvoudig kunt 'optimaliseren' door goederen van de winkels terug naar het magazijn te vervoeren - de kosten worden negatief, dat wil zeggen dat we winst maken!
Om dit te voorkomen, kunt u het selectievakje het beste ingeschakeld laten. Maak onbeperkte variabelen niet-negatief of soms zelfs expliciet registreren van dergelijke momenten in de lijst met beperkingen.
Na het instellen van alle benodigde parameters, zou het venster er als volgt uit moeten zien:
In de vervolgkeuzelijst Selecteer een oplossingsmethode moet u bovendien de juiste wiskundige methode selecteren voor het oplossen van een keuze uit drie opties:
- Simplex methode is een eenvoudige en snelle methode voor het oplossen van lineaire problemen, dwz problemen waarbij de output lineair afhankelijk is van de input.
- Algemene gedegradeerde gradiëntmethode (OGG) – voor niet-lineaire problemen, waarbij er complexe niet-lineaire afhankelijkheden zijn tussen invoer- en uitvoergegevens (bijvoorbeeld de afhankelijkheid van verkoop van advertentiekosten).
- Evolutionaire zoektocht naar een oplossing – een relatief nieuwe optimalisatiemethode gebaseerd op de principes van biologische evolutie (hallo Darwin). Deze methode werkt vele malen langer dan de eerste twee, maar kan bijna elk probleem oplossen (niet-lineair, discreet).
Onze taak is duidelijk lineair: 1 stuk geleverd - 40 roebel uitgegeven, 2 stuks geleverd - 80 roebel uitgegeven. enz., dus de simplex-methode is de beste keuze.
Nu de gegevens voor de berekening zijn ingevoerd, drukt u op de knop Een oplossing vinden (Oplossen)optimalisatie te starten. In ernstige gevallen met veel veranderende cellen en beperkingen, kan het vinden van een oplossing lang duren (vooral met de evolutionaire methode), maar onze taak voor Excel zal geen probleem zijn - binnen enkele ogenblikken zullen we de volgende resultaten krijgen :
Let op hoe interessant de bevoorradingsvolumes over de winkels werden verdeeld, zonder de capaciteit van onze magazijnen te overschrijden en aan alle verzoeken om het vereiste aantal goederen voor elke winkel te voldoen.
Als de gevonden oplossing bij ons past, kunnen we deze opslaan, of teruggaan naar de oorspronkelijke waarden en het opnieuw proberen met andere parameters. U kunt de geselecteerde combinatie van parameters ook opslaan als: Scenario. Op verzoek van de gebruiker kan Excel drie typen bouwen Rapporten over het probleem dat wordt opgelost op aparte bladen: een rapport over de resultaten, een rapport over de wiskundige stabiliteit van de oplossing en een rapport over de limieten (beperkingen) van de oplossing, maar in de meeste gevallen zijn ze alleen van belang voor specialisten .
Er zijn echter situaties waarin Excel geen passende oplossing kan vinden. Het is mogelijk om een dergelijk geval te simuleren als we in ons voorbeeld de behoeften van de winkels aangeven in een hoeveelheid die groter is dan de totale capaciteit van de magazijnen. Bij het uitvoeren van een optimalisatie probeert Excel vervolgens zo dicht mogelijk bij de oplossing te komen en geeft vervolgens een bericht weer dat de oplossing niet kan worden gevonden. Desalniettemin hebben we zelfs in dit geval veel nuttige informatie - we kunnen met name de "zwakke schakels" van onze bedrijfsprocessen zien en de gebieden voor verbetering begrijpen.
Het weloverwogen voorbeeld is natuurlijk relatief eenvoudig, maar kan gemakkelijk worden geschaald om veel complexere problemen op te lossen. Bijvoorbeeld:
- Optimalisatie van de verdeling van financiële middelen per uitgavenpost in het businessplan of de begroting van het project. De beperkingen zijn in dit geval de hoeveelheid financiering en de timing van het project, en het doel van optimalisatie is om de winst te maximaliseren en de projectkosten te minimaliseren.
- Optimalisatie van personeelsplanning om het loonfonds van de onderneming te minimaliseren. Beperkingen zijn in dit geval de wensen van elke werknemer volgens het arbeidsschema en de vereisten van de personeelstafel.
- Optimalisatie van investeringsinvesteringen – de noodzaak om middelen correct te verdelen tussen verschillende banken, effecten of aandelen van ondernemingen om, nogmaals, winst te maximaliseren of (indien belangrijker) risico's te minimaliseren.
In ieder geval, add-on Zoekoplossingen (oplosser) is een zeer krachtige en mooie Excel-tool en verdient uw aandacht, omdat het kan helpen in veel moeilijke situaties waarmee u in het moderne bedrijfsleven te maken krijgt.