Voordeligste som maken met enkele voorwaarden

[vdbruben]

Hallo,

Ik ben opzoek naar een oplossing voor het volgende probleem;
Er zijn 11 percelen waarvoor er een bepaald aantal veiligheidscoördinatoren moeten aangesteld worden.
In totaal zijn er 11 bedrijven die een offerte voor de percelen hebben ingestuurd met telkens het aantal coördinatoren dat ze ter beschikking hebben.
Het is de bedoeling om voor elk perceel 1 bedrijf aan te stellen en uiteindelijk in totaal de meest voordelige combinatie te hebben (rekening houdend met het aantal coördinatoren).
Het is dus mogelijk dat een bedrijf meerdere percelen krijgt toegewezen.

In bijlage is de tabel te zien waarover het gaat. In kolom B is het aantal coördinatoren te zien dat elk bedrijf ter beschikking heeft. Rij 2 geeft het aantal coördinatoren weer dat op elk perceel aanwezig moet zijn.

Is er een manier om snel de meest voordelige combinatie te vinden? Bekijk bijlage Offertes.xlsx

 

[sylvester-ponte]

de bedragen in de tabel, daar zit een behoorlijk verschil in (per perceel) .
is dat het gevraagde bedrag per coördinator?

 

[vdbruben]

de bedragen in de tabel, daar zit een behoorlijk verschil in (per perceel) .
is dat het gevraagde bedrag per coördinator?

De bedragen die je per perceel ziet, zijn de totaalprijzen die ze vragen en dus niet per coördinator.

 

[popipipo]

Niet helemaal geautomatiseerd maar toch nog redelijk te doen denk ik.

 

[cow18]

even snel iets gemaakt met oplosser en gelukkig moest ik niet beginnen met binaire variabelen

 

[sylvester-ponte]

het is een voortborduursel op wat Willem aandroeg.

programma technisch een drama en de oplossing is niet perse de beste.

als je perse de beste wil moet er verder worden gepuzzeld . maar dan door de cracks.

 

[sylvester-ponte]

deze werkt met dictionary's en list objecten. dus is voor een MAC niet bruikbaar.
het geeft wel minder gewiebel in het beeld.
het werkt nog steeds volgens het principe van Willem.

er wordt dus niet echt door gerekend, maar vooral veel gesorteerd.

 

[cow18]

Oplosser (of Solver) is een gewone invoegtoepassing die je kan selecteren in het menu Bestand>OPties>Invoegtoepassingen.
Het duurt misschien eventjes tot je weet hoe 1 en ander werkt, maar daarna ben je vertrokken.
Ik was zo vrij om de vraagstelling eventjes anders te interpreteren om de flexibiliteit aan te tonen van deze oplosmethode.
De 2e regel "Inschrijver, aantal coordinatoren" bevat een aantal getallen, wel ik heb aangenomen dat dit het aantal is dat per perceel moet toebedeeld worden, dus 1 tot 4 coordinatoren per perceel ipv de gevraagde 1.
Eventjes de tabellen er onder aangepast en Oplosser laten lopen, zodoende regels 30-40 geeft je het aantal coordinatoren per perceel en per bedrijf en de regels 45-55 de daarbij behorende kosten.

Voor de duidelijkheid, dit is geen oplossing voor de originele vraag, het is een fantasietje.
Als je iets wenst te programmeren, dan moet je eerst eens googlen naar het "simplex algoritme" want zo worden dergelijke vraagstukken aangepakt.

 

[cow18]

en nog een fantasietje, ook 1 tot 4 coordinatoren per perceel, maar het mag telkens maar 1 van elk bedrijf zijn,
klein beetje knoeien met de tabellen, oplosser zijn werk laten doen en dit is het resultaat.

 

[sylvester-ponte]

cow, ik dacht dat er maar 1 bedrijf op 1 perceel moet komen.

vdbruben, deze loopt niet vast als er geen bedrijven voor een perceel aanwezig zijn,
of als er een onmogelijke oplossing gevraagd wordt. (zie voorbeeld)
het programma denkt niet door, het werkt heel kortzichtig: neem steeds de voordeligste oplossing (op korte termijn)
werkwijze van het programma:
sorteer per perceel de inschrijvingen.
neem de verschillen van de laagsten.
sorteer deze. de laagste geeft de oplossing
zo wordt de eerste oplossing gevonden.
verwijder het opgeloste perceel.
verlaag van het gekozen bedrijf het aantal coördinatoren met de gebruikten.
verwijder van dat bedrijf de inschrijvingen waarvoor te weinig coordinatoren aanwezig zijn.
herhaal dit tot alle percelen gehandeld zijn.

 

[cow18]

@ Sylvester Ponte,

cow, ik dacht dat er maar 1 bedrijf op 1 perceel moet komen.

oplossing in reactie 5 is de ideale oplossing, dus die richting moet je programma uitgaan. Prijskaartje 868.534 met overal een coordinator
Mijn andere reacties waren, zoals ik in die posten zelf aangaf, wat fantasietjes, moest TS plots nog wat extra voorwaarden stellen bv.

 

[vdbruben]

Allemaal al bedankt voor de reacties. Ik ben zelf eens verder proberen in te gaan op de solver toepassing. De tool slaagt er helaas nog niet in om tot een oplossing te komen. Heb ik nog iets over het hoofd gezien? Bekijk bijlage Narekening veiligheidscoördinatie2.xlsx

 

[sylvester-ponte]

cow18, in post5 wordt geen rekening gehouden met het aantal beschikbare coördinatoren per bedrijf

 

[sylvester-ponte]

vdbruben , om te testen had ik even dat data overhoop gegooid.
hier mijn oplossing met de originele data.

 

[cow18]

dan komen die grapjes die ik in reactie 8 en 9 gaf in beeld.
Blijkbaar hadden we de vraag niet goed begrepen en hadden we maar 1 coordinator per perceel voorzien.

Even in het kort uitleggen, als je je layout netjes maakt, dan kan je vrij snel je voorwaarden schrijven, dat is leuker.
Het gebruik van binaire of integers is een beetje af te raden, daar zijn er niet genoeg van (ik dacht maximaal 30 in de oudere excels, hoeveel het nu is weet ik niet.
Dus had ik nog geen zulke voorwaarden gesteld, moest het probleem zich voordoen, dan zou ik pas die richting uitkijken.

Dus je 1e tabel vond ik goed en daar zette ik die 2e tabel (rijen 17-27) onder, met per bedrijf en per perceel het maximale aantal coordinatoren dat een bedrijf ter beschikking heeft, als dat bedrijf voor een bepaald perceel een prijs bepaald heeft.

De volgende tabel (in het grijs) zijn dan alle variabelen, die straks door de Oplosser ingevuld zullen worden en die aan een aantal voorwaarden moeten voldoen.
Dus door een goeie layout gaat dat hier vrij snel, zie straks

De volgende kolom vermenigvuldigt de prijs van een coordinator met het aantal coordinatoren en dan daaronder maak ik het totaalplaatje in cel M57.
Deze cel zal straks moeten geminimaliseerd worden.

1e voorwaarde :het aantal toegekende coordinatoren per bedrijf en per perceel is kleiner of gelijk aan het maximale aantal coordinatoren van dat bedrijf, als dat bedrijf voor dat perceel een prijs bepaald had.
2e voorwaarde : het aantal toegekende coordinatoren voor een perceel (rij 41) is gelijk aan het aantal gewenste coordinatoren voor dat perceel (rij 3)
3e voorwaarde : het aantal toegekende coordinatoren per bedrijf (kolom N) is kleiner of gelijk aan het maximaal aantal coordinatoren van dat bedrijf (kolom O)

Verder gebruik ik het Simplex LP algoritme

Klaar

Ik vrees nu dat de volgende vraag is dat perceel 4 en 10 iet mogen toegekend worden aan 2 bedrijven maar dat hoor ik dan wel

 

[cow18]

joepie, 121 binairen en geen opmerking daarover door Oplosser

 

[vdbruben]

joepie, 121 binairen en geen opmerking daarover door Oplosser

Super, hiernaar was ik opzoek. Bedankt!

 

[sylvester-ponte]

cow18, volgens post3 zijn de bedragen in de start tabel de kosten die de bedrijven berekenen voor een heel perceel.

maar in jouw oplossing gebruik je voor bvb perceel 10 zowel bedrijf 10 als bedrijf 3. waarom is dat?

bedrijf 10 ( 131.290,00 ) zou al voldoende zijn.


vdbruben, is het de bedoeling dat er eventueel meerdere bedrijven werkzaam zijn op 1 perceel?

 

[cow18]

@Sylverster, in post 16 zijn aan al je opmerkingen voldaan, dus het bedrag gaat over het ganse perceel met x coordinatoren en een perceel wordt slechts aan 1 bedrijf toegekend.

@vbruben, door de invoer van die binairen mag de 1e regel voorwaarden weg, die met de grijze cellen <= 1, dat was eigenlijk 2 keer hetzelfde.
Als een bedrijf geen bedrag opgegeven heeft voor een bepaald perceel, dan zet ik in de 2e tabel daarvoor 99 coordinatoren, beduidend meer dan het maximum dat ieder bedrijf kan leveren.
Dat kwam in de plaats van een overdreven groot bedrag uit mijn vorige versie.
Eigenlijk 2 manieren om dat probleem van een niet ingevuld bedrag te omzeilen, ik weet niet welke versie je voorkeur heeft.

 

[sylvester-ponte]

cow18, dan snap ik je oplossing niet, wand in jouw oplossing staan bij bvb perceel 10 twee bedrijven ingedeeld. of zie ik dat verkeerd?"

sorry ik had post 16 nog niet gezien.

super , heel mooie oplossing !