Rangorde mix

[rogierv]

Beste forumleden,

ik maak al een tijd gebruik van excel voor de administratie van mijn bedrijf en heb de basis dingen behoorlijk onder de knie denk ik. Maar nu zou ik graag een hulpsysteem op willen zetten wat echt boven mijn pet gaat. Zal uitleggen wat ik graag zou willen zien.

Ik heb meerdere oproepkrachten in dienst die allen dezelfde werkzaamheden verrichten bij verschillende klanten. De ene oproepkracht word door een opdrachtgever op een bepaalde manier gewaardeert, maar bij een andere opdrachtegever word die bijvoorbeeld minder gewaardeert. Nu zou ik excel willen laten berekenen wat de beste mix van mensen is om de klanten tevreden te houden. Zal het aangeven met een voorbeeld.

Klant 1 heeft op 1-1-2010 3 mensen nodig
klant 2 heeft op 1-1-2010 2 mensen nodig
klant 3 heeft op 1-1-2010 4 mensen nodig

Op 1-1-2010 zijn er 12 mensen tot mijn beschikking namelijk(waardering word bepaald tussen 1-10)

waardering bij: klant1 klant2 klant3
persoon1 9 8 8
persoon2 8 7 6
persoon3 9 8 7
persoon4 5 5 9
persoon5 6 6 7
persoon6 6 7 8
persoon7 4 4 8
persoon8 5 6 4
persoon9 5 5 7
persoon10 7 7 7
persoon11 7 9 5
persoon12 7 9 5

Nu wil ik dus dat excel berekent wat de beste mix is van mensen op 1-1-2010 bij de 3 verschillende klanten rekening houdend met het feit dat mensen niet bij 2 verschillende klanten tegelijk kunnen zijn. Er zullen dus ook mensen overblijven die niet zullen werken aangezien er maar 3+2+4= 9 mensen werkzaam zullen zijn op die dag

Hoop dat jullie mij hierbij kunnen helpen.

Alvast bedankt

 

[bandito bob]

Dit lijkt mij iets voor de oplosser/solver.
Op de site van Wim Gielis (www.wimgielis.be) vind je hier uitleg over.

Misschien dat ik ook (als ik ff tijd heb) een oplossing voor je kan aandragen.

 

[bandito bob]

Hier een oplossing.

Maak kolom A leeg (met DEL).
ga vervolgens naar de oplosser/solver >> oplossen.
Nu gaat hij even rekenen, naar het beste resultaat. Dit kan misschien enkele minuten duren bij een trage PC.
Als een oplossing is gevonden zie je in J9 t/m L20 met kleuren wie waar heen moet.

 

[bandito bob]

Ik heb hem nog wat aangepast, zodat hij het beter (sneller) zal doen.

p.s. in I3 t/m I5 zet je het aantal mensen dat een klant wil hebben.
p.s. 2: IN I9 t/m K20 houd je de punten bij die iemand bij een klant scoort.

 

[ExcelHulp]

Leuke oplossing!

 

[bandito bob]

Leuke oplossing!

Bedankt

 

[rogierv]

Heel erg bedankt bob, snap totaal niet wat je hebt gedaan maar hij doet het wel. Kan je dit een beetje uitleggen. Verder zie ik niet veel formules, staan die ergens verstopt ofzo. Is er ook een mogelijkheid dat wanneer ik die cijfers aanpas hij ook automatisch verandert?

 

[bandito bob]

Er staan ook niet veel formules in, het meeste wordt gedaan door de oplosser.
Je moet maar eens goed kijken naar de oplosser. Daar zie je staan:
cel bepalen: $E$5 >> Max. (oftewel: Ik wil cel E5 zo groot mogelijk).
Door verandering: $A$7:$A$42.
Hieronder zie je nog restricties staan. (Deze zijn heel belangrijk).

De oplosser gaat getallen invullen in A7 t/m A42, rekening houdend met de restricties die door mij zijn ingegeven. De getallen reeks waarbij E5 maximaal is en aan alle restrictie is voldaan is de uiteindelijke oplossing, die getoond wordt. De kleuren heb ik met een voorwaardelijke opmaak gedaan.

Als je een getal aanpast zal je de oplosser weer moeten laten zoeken naar een best oplossing. Dit kan door zelf kolom A te deleten en vervolgens de oplosser te activeren, of je kan dit automatisch door een macro laten doen (tenminste, volgens mij moet dat kunnen). Zelf zou ik het "gewoon" handmatig aansturen.

Ik zou de oplossingen wel altijd zelf nakijken, want ik zie wel eens rare uitkomsten van een oplosser. Je ziet vrij snel of een oplossing kan kloppen.
Als je in bestand oplosser2 ingeeft dat elke klant 2 mensen wilt zal je zien dat de oplosser het (net ff) fout heeft. Hij komt op een max van 52 punten, terwijl 53 mogelijk is.

 

[bandito bob]

Ik was zelf nog niet helemaal tevreden. Ik heb de restricties wat "ruimer" gezet, zodat de solver wat langer zal doorrekenen, en zodoende (vaker) tot de beste oplossing zal komen.
Als de oplosser een paar keer achter elkaar dezelfde uitkomst berekend stopt de oplosser (volgens mij) en kan zo wel eens vroegtijdig met een foute oplossing komen. Volgens mij gebeurt dat nu niet/nauwelijks meer. Ik heb het iig niet meer fout zien gaan.

Tevens heb ik er een keuze bij gezet, waarmee je werknemers op "VRIJ" kunt zetten. Deze mensen zullen in het rekenen tellen voor 0 punten (cel L6), en dus worden overgeslagen.
Als er teveel mensen worden gevraagd/te weinig mensen beschikbaar zijn dan komt er in cel B3 een waarschuwing.

Zo, ik heb weer ff lekker zitten puzzelen. Succes ermee.
Laat het maar weten als er wat is.