Ich habe ein etwas umfangreicheres Anliegen an euch und bin mir tatsächlich nicht mal ganz sicher, ob
sich mein Problem mit Excel lösen lässt. In der Überschrift habe ich versucht, es mal mit dem Begriff
Gruppenplanung zu umschreiben, das klingt aber leider etwas einfacher als es ist:
Ich arbeite in einer Berufsvorbereitungsmaßnahme. Bei Maßnahmebeginn haben wir immer eine Teilnehmerzahl
von ca. 40-60, für die wir für einen Zeitraum von 2 Wochen einen Verteilungsplan erstellen müssen, der
eine ganze Reihe Vorgaben berücksichtigen muss.
- In der Maßnahme haben wir fünf Gewerke im Angebot: Metall, Bau, Lager, HoGa, Farbe
- Jeder Teilnehmer darf sich vorab drei der fünf Gewerke aussuchen, die er sich anschauen will
- Jeder Teilnehmer hat 1-2 feste Berufsschultage, die aber von Teilnehmer zu Teilnehmer
unterschiedlich sind (Anton hat montags und dienstags Schule, Ben nur donnerstags, Carola
dienstags und freitags und so weiter)
- nach Möglichkeit sollte jeder TN jedes der drei Gewerke mindestens 2 Tage anschauen können
- nach Möglichkeit sollte jeder TN zusätzlich an einem Tag eine Kompetenztestung absolvieren.
- Die einzelnen Gewerke haben eine unterschiedliche Anzahl an maximalen Teilnehmern
(Bau: 5, Metall: 17, HoGa: 8, Lager: 6, Farbe: 8, Kompetenzanalyse: 10)
Bisher wurde die entsprechende Planung immer analog - sprich: mit Stift und Papier - und damit
sehr zeit- und nervenaufwändig durchgeführt. Ich hätte gerne eine Möglichkeit, die Teilnehmer
in eine Liste einzutragen und mit folgenden Daten zu "füttern":
- Name des TN
- gewünschte drei Gewerke, die sich der TN ansehen soll
- individuelle Berufsschultage
Dann soll das Programm alle TN so verteilen, dass - unter Berücksichtigung der Berufsschultage und
der jeweiligen maximalen Teilnehmerzahl - alle TN ihre drei Gewerke erproben können und idealerweise
auch noch die Kompetenztestung durchführen können.
Ist so etwas mit Excel prinzipiell machbar? Wenn ja, hat jemand von euch eine Vorlage, die zumindest
in die Richtung geht und die ich für uns anpassen könnte? Oder habt ihr zumindest ein paar Tips, wie
ich eine Vorlage selbst erstellen könnte?
Ich bin in EDV-Dingen einigermaßen bewandert, habe aber mit Excel bisher einfach sehr wenig gearbeitet,
so dass ich hier nur wenig Vorkenntnisse habe. Ich kann mich mit Unterstützung hier aber voraussichtlich
sehr schnell einfinden.
Über Hilfe eurerseits würde ich mich sehr freuen, danke schonmal!!
A) Verständnisfragen
1. Ist es richtig, dass die Gewerke mit der beschriebenen maximalen Teilnehmerzahl an 10 Tagen jeweils einmal angeboten werden?
2. Ist es richtig, dass die Kompetenztestung unabhängig von dem vorherigen Besuch eines Gewerkes ist? Also auch am ersten Tag angeboten werden kann?
B) Excel
1) Formeln
Ich glaube nicht, dass man durch Formeln einen Vorschlag erhalten kann. Man kann aber den bisherigen analogen Prozess mit Excel durchführen und über Formeln ermitteln, welche Teilnehmer im aktuellen Prozessschritt noch nicht den gewünschten Gewerken zugeordnet ist und, wenn man die Ergebnisse gewichtet, sehen wie gut der aktuelle Stand ist.
2) Solver (Optimierer in Excel)
Vorschläge für ähnliche Aufgabenstellungen werden in Excel häufig mit dem vorhandenen Solver erarbeitet. Dieser kann aber nur für sehr viel kleinere Projekte eingesetzt werden. Das ist also auch keine Möglichkeit.
3) Programmierung
Für einen Teilnehmer, der nur einen Tag in der Woche Berufsschule hat, gibt es 5040 Varianten die drei gewünschten Gewerke und den Kompetenztest zuzuordnen. Bei 60 Teilnehmern macht das ca 10^222 Variationen (mehr als Atome im Universum). Da macht es auch nicht viel, das Excel-Makros weit mehr als 10 mal langsamer sind als Programme in einer "richtigen" Programiersprache.
Man könnte über Zufallsprozesse (zig-)tausende Variationen gewichten und das beste Ergebnis ausweisen. Wenn man hierbei geschickt vorgeht und zuerst die Engpässe bearbeitet kann es vorkommen, dass man häufig einen guten Vorschlag erhält.
Für ein ähnliches (kann nicht für deine Anforderung genutzt werden) Projekt habe ich hier ein Beispiel:
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
danke dir schonmal für die ausführliche Antwort :)
Zu A)
1. Ja, genau. Die Gewerke werden an den Erprobungstagen jeweils einmal angeboten (dieses Jahr sind es tatsächlich sogar 11 Tage, vom 25.08. - 08.09, das variiert ein wenig von Jahr zu Jahr).
2. Ja, das ist unabhängig, die Testung kann auch am ersten Tag sein.
Zu B)
1) Wenn ich dich hier richtig verstehe, nimmt mir diese Möglichkeit aber keine Arbeit ab, richtig? Ich mache alles wie immer, habe aber anschließend noch eine Kontrollmöglichkeit zusätzlich?
2) Den Solver kannte ich noch gar nicht, schaue ich mir mal an, danke (Zur Neugierbefriedigung, für mein Problem kann ich ihn ja leider nicht nutzen sagtest du).
3) Dein Atom-Beispiel macht mich gerade etwas sprachlos...ich wusste ja, dass es sehr viele Varianten gibt, aber diese Größe hätte ich nun doch nicht erwartet. Ich benötige ja nur ein einzelnes
Ergebnis, dass den Anforderungen entspricht. Aber ich verstehe das Grundproblem. Ich schaue mir mal das Beispiel an, vielleicht kriege ich ja ein paar Inspirationen :)
Vielen Dank auf jeden Fall schonmal!!
in der Anlage eine erste Version und hier erste Bemerkungen zum Testen:
A) Eingaben
In den grünen Zellen im Bereich A1:R75 werden die Eingangsparameter eingetragen. a) Max Sek
Die Zeit in Sekunden, die das Programm versuchen soll eine bessere Lösung zu finden. b) Gewerke
für jeden Tag kann eingetragen werden wieviel Plätze in dem Gewek zur Verfügung stehen. Die Werte können an den einzelnen Tagen unterschiedlich sein (zB kann ein Gewerk dass in Summe sehr viele Plätze hat an einzelnen Tagen ganz ausgeschlossen werden). c) Teilnehmer
Hier werden für die Teilnehmer
1. die Schultage mit -1 vorbelegt (alle anderen Tage erhalten zur Zeit eine 0) und
2. die Positonsnummern der Wünsche eingetragen.
B) Ausgaben a) Belegungsplan
(Position der Gewerke) der Teilnehmer für alle Tage (T16:AC75) b) Zusatzinformationen
1. Anzeige der freien Plätze pro Gewerk und Tag (T5:AC10)
2. Anzeige der nicht erfüllten Wünsche (AJ16:AO75)
C) Button a) Tuwat
Mit dem Makro wird versucht in der vorgegeben Zeit einen möglichst hoch gewichteten Belegungsplan zu ermitteln.
Jede Verbesserung wird direkt angezeigt.
Falls ein Plan mit maximal möglicher Gewichtung gefunden wurde wird das Programm vorzeitig beendt.
(In allen meinen Tests wurde das Programm vorzeitig beendet.) b) Testdaten
Hiermit werden für die Teilnehmereingaben Testdaten erzeugt. Achtung!!!!!! Die vorhandenen Teilnehmerdaten werden gnadenlos überschrieben und sollten, wenn noch benötigt, rechtzeitig gesichert werden.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
das ist ja der Wahnsinn, vielen vielen Dank für deine Mühe!!! Nach der ersten Antwort hätte ich jetzt nicht damit
gerechnet, dass du doch eine Lösung findest, und dann auch noch so schnell!!! Wie lange hast du denn an der
Programmierung gesessen?
Ich habe jetzt auf jeden Fall mal festgestellt, wie wenig Excel-Ahnung ich tatsächlich habe: Von der VBA-Schnittstelle
wusste ich gar nichts. Habe bisher leider auch nur sehr eingeschränkte Vorerfahrungen mit Programmiersprachen.
Ich habe die Tabelle natürlich direkt mal mit unseren Daten gefüttert und getestet. Bisher gab es leider noch keine
perfekte Lösung. Wie kann ich die Tabelle denn bei Bedarf um einen Tag 11 etc. erweitern? Dazu muss ich ja sicherlich
auch in der VBA-Umgebung Ergänzungen/Änderungen vornehmen oder? Kannst du mir das kurz beschreiben?
20.08.2020, 11:55 (Dieser Beitrag wurde zuletzt bearbeitet: 20.08.2020, 11:55 von Ego.)
Hallo Bruno,
A) zu
Zitat:Wie lange hast du denn an der Programmierung gesessen?
Einen längeren Spaziergang für die Überlegung der Struktur und dann 2-3 Stunden für Dateneingabe, Programmierung und Tests.
B) zu
Zitat:Ich habe die Tabelle natürlich direkt mal mit unseren Daten gefüttert und getestet. Bisher gab es leider noch keine perfekte Lösung.
Wenn du willst, kannst du -gern auch mehrere- Echtdaten (natürlich anonymisiert) zur Verfügung stellen. Vielleicht kann ich anhand dieser Daten erkennen ob Programmänderungen eine Verbesserung bringen. Vielleicht ist auch ein anderer Ansatz (zB EA-Optimierung) besser geeignet.
Hier einige Bemerkungen:
1. Wenn für ein Gewerk mehr Wünsche als Plätze vorhanden sind, können natürlich nicht alle Wünsche erfüllt werden.
2. Zur Zeit sollten für einen Teilnehmer nicht mehr Wünsche eingetragen werden als er Anwesenheitstage hat, da das Programm ansonsten die möglichen Punkte falsch berechnet und nicht vorzeitig abgebrochen werden kann.
3. Die Zusammenstellung der Variationen erfolgt im Programm über Zufallsprozesse. Bei mehrmaligen Aufrufen mit gleichen Eingangsdaten werden sehr wahrscheinlich immer unterschiedlich Ergebisse ausgewiesen. Vielleicht kannst du das Programm ja auch einmal übe die Mittagspause (mehr als 60 Sekunden einstellen) laufen lassen.
C) zu
Zitat:Wie kann ich die Tabelle denn bei Bedarf um einen Tag 11 etc. erweitern? Dazu muss ich ja sicherlich auch in der VBA-Umgebung Ergänzungen/Änderungen vornehmen oder?
Natürlich nicht.
Die Anzahl der Gewerke, Tage, Teilnehmer und Wünsche werden allein aus den Daten der beiden Erfassungsblöcke ermittelt und können erweitert werden.
Anzahl Gewerke = Zeilen im Bereich Gewerke - 1 (Überschrift)
Anzahl Tage = Spalten im Bereich Gewerke - 2 (erste Spalte Bezeichnung und letzte Spalte für die Gewichtung)
Anzahl Teilnehmer = Zeilen im Bereich Teilnehmer - 1 (Überschrift)
Anzahl Wünsche = Spalten im Bereich Teilnehmer - 1 - Anzahl Tage (erst Spalte Namen und x Spalten für die Anwesenheitskennungen an den Tagen)
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Hallo Helmut, entschuldige die lange Pause, ich war im verlängerten Wochenende...
Auf dein Angebot, dir die Daten mal zuzuschicken komme ich hiermit sehr gerne zurück! Habe die Datei angefügt.
Das mit der längeren Laufzeit habe ich ein paar mal probiert, es war immer knapp vor dem perfekten Ergebnis, aber
eben immer nur knapp davor.
Das mit der Erweiterung habe ich somit auch kapiert, vielen Dank :)
Ich bin begeistert, was du da eben mal in 2-3 Stunden hinbekommst!!
A) Vorab
Ich hatte in meinem letzten Beitrag geschrieben, dass die Berechnung der maximalen Punktzahl noch falsch ist, wenn es für einen Teilnehmer mehr Wünsche als Anwesenheitstage gibt (werde ich ändern).
Wenn ich in deinen Beispieldaten den 7. Wusch auf 0 setzte wenn der Teilnehmer nur 6 Anwesenheitstage hat, habe ich in drei Versuchen zweimal ein optimales Ergebnis nach fast 60 Sekunden.
B) Änderungen
Ich werde noch einige Ideen umsetzen um die Wahrscheinlichkeit eines guten Ergebnisses zu erhöhen. Falls du zusätzliche Wünsche hast, solltest du sie so früh wie möglich anfragen, damit ich nicht in die falsche Richtung entwickle.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
in der Anlage eine neue Version und hier noch einige Bemerkungen:
A) 1. Schritt
Im ersten Schritt wird versucht möglichst viele der Wünsche zu erfüllen. Berücksichtigt werden hierbei nur so viele Wünsche, wie der Teilnehmer Anwesenheitstage hat.
Nach jeweils 100 Versuche über Zufallsprozesse versuche ich jetzt zusätzlich, das bisher beste Ergebnis über einfache Vertauschung zu verbessern. (Aus diesem Grund sind die Anzahl der Versuche für den ersten Schritt häufig durch 100 teilbar.)
B) 2. Schritt
Falls ein Teilnehmer, der mehr Wünsche als Anwesenheitstage hat, im ersten Schritt nicht an allen Tagen ein Gewerk zugeordnet wurde (zB weil ein gewünschtes Gewerk überbelegt ist), wird versucht diese Tage mit den zusätzlichen Wünschen zu füllen.
C) Vorgegebene Belegungen
Wenn du in der Teilnehmerliste an einem Tag durch den Eintrag einer Gewerkenummer ein Belegung vorgibst, wird sie auch in der Ausgabe übernommen.
Du solltest aber dann
1. an diesem Tag die Anzahl der noch zu vergebenen Gewerkeplätze anpassen und
2. diesen Eintrag nicht zusätzlich als Wunsch eintragen.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
ich bin absolut begeistert, vielen vielen Dank, das nimmt uns wirklich enorm Arbeit ab!
Ich habe versuchsweise auch mal die Daten aus dem letzten Jahr eingetragen und hatte
in weniger als 60 Sekunden ein optimales Ergebnis.
Excel ist wirklich ein Programm, dass ich mir künftig wohl besser anschauen sollte/werde.
Wenn du mal Fortbildungen anbietest oder so, bin ich auf jeden Fall dabei :)
