Daten in Bezug setzen und automatisch beste Kombination ausg

Moderator: ModerationP

Re: Daten in Bezug setzen und automatisch beste Kombination

Beitragvon 1Matthias » 05. Jun 2018, 12:43

Moin!
Um das Projekt mal voranzutreiben bin ich mal mutig und poste mal nen Code. Habe die Datei von thowe übernommen und den Code geändert / ergänzt.
Ablauf:
Im Blatt Schichtplan in Spalte A ein Datum anklicken und dann die Schaltfläche drücken. (kann man anlag auch in ein ChangeEvent übertragen). Im Blatt optimierte Besetzung wird dann die Zeile mit dem selben Datum ausgefüllt.
Priosierung:
Wie du geschrieben hast, wird immer die 1 gesetzt (wenn möglich) und davon ausgehend der Rest. Das kann dann ggf. dazu führen, das die Summe aller eingesetzen Qualifikationen größer ist als wenn man bei einem Mitarbeiter auf die 1 verzichtet hätte. Das kommt dann immer auf die Daten an. Alternativ müsste man dann wirklich alle Permutationen der Anordung durchgehen. (wenn ich mich nicht verrechnet habe wäre das: (Mitarbeiter!-(mitarbeiter-AnzahlMaschinen)!)*MItarbeiter - je nach dem wieviele Maschinen und Mitarbeiter du hast, wird das eine recht große Zahl :shock: - und die musst du erst jeweils zusammenstellen.)
Ermittlung:
Meine Variante geht deshalb so vor. Ausgehend vom Datum werden alle MItarbeiter die dort Schicht haben (im Beispiel F Schicht) ausgewählt und deren Qualifikationen übertragen. Das ganze stelle ich im einem Datenfeld / Matrix da. Hierbei ist in Zeile 0 das Feld zum vermerken, ob schon geprüft und in Spalte 0 das Feld mit dem Namen (evtl. mal auf Papier aufzeichnen). Diese Matrix gehe ich nun Zellweise durch (von links oben nach unten und dann die Spalten nach rechts). Wenn man auf eine 1 trifft, startet die Auswertung ebenfalls über alle Zellen (selbe Reihenfolge). Hierbei werden erst alle 1en gesetzt. Gesetzt heißt dabei, dass der Name zu der Maschine eingetragen wird, der Name in der Matrix Spalte 0 gelöscht wird und die Maschine in Zeile 0 ein x bekommt. Bei der Prüfung einer Spalte wird da dann geschaut, ob die ein x hat (schon gesetzt ist). Wenn ja, wird die Spalte übersprungen. Analog, bei Zeilen. Ist keine Name mehr da, wird die Zeile nicht mehr geprüft. So geht es durch den Code. Sind alle 1en gesetzt, läuft der Code wieder alle Zellen (eigentlich nur noch die verbliebenen) durch und setzt die 2. Zum Schluß das Ganze nochmal mit 3. Das Ergebnis (Zuordnung Name zu Maschine und die Qualifikationssumme) wird gespeichert. Dann wird die nächste 1 gesucht und der Spaß geht wieder los. Das Ergebnis wird dann mit dem gespeicherten verglichen. Ist die Summe gleich, wird dies genommen, sonst verworfen. Sind alle 1en mal geprüft, wird das zuletzt gespeicherte genommen.
Am besten mal mit einer kleinen Matrix auf dem Zettel nachvollziehen.
So zumindest mal die Theorie. Hoffe mal ich habe mich nicht vertan und falsch programmiert.
Ob das jetzt die optimalste Verteilung ist, weiß ich nicht - bin kein Mathematiker. :?
Habe die Daten mal aufgebläht um ein weniger mehr zum Testen zu haben. Kannst das zum Programmieren und schauen ob es wirklich optimal läuft auch wieder reduzieren.
Der Code kann gerne ergänzt oder geändert werden. Kommentare fehlen meist, können aber bei Bedarf noch ergänzt werden. Den Ablauf (wie es sein sollte :D ) habe ich ja eh mal dargestellt.
VG
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 413
Registriert: 15. Aug 2017, 18:36

Re: Daten in Bezug setzen und automatisch beste Kombination

Beitragvon Lukas_90 » 07. Jun 2018, 18:43

Hallo 1Matthias,

danke für deine Beispiel-Datei, scheint auch gut zu funktionieren, jedoch sollte das Programm nicht die erst beste 1 suchen und zuordnen, sondern die beste Kombination aus Einsen, Zweien, Dreien, zu suchen und zuzuordnen.

Weißt du was ich meine?

Das ist ja an dieser Aufgabe das schwerste.
Ich denke da müssen sehr viele Schleifen programmiert werden.

Liebe Grüße
Lukas
Lukas_90
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 27
Registriert: 03. Aug 2017, 20:17

Re: Daten in Bezug setzen und automatisch beste Kombination

Beitragvon 1Matthias » 10. Jun 2018, 21:50

Moin!
War mal kurz offline. :-)
Also es wird ja nicht die erstbeste 1 gesucht sondern der Code geht alle einsen durch und erstellt für jede die Zuordnung. Nach jeder 1 wird das beste Ergebnis mit dem letzten besten Ergebnis verglichen und das bessere gespeichert. Lediglich bei der 2 und 3 wird dann einfach gesetzt. Bei der 2 könnte man das auch noch durchprüfen lassen.
Das Problem ist, dass die beste Lösung etwas Rechenaufwand benötigt - insb. wenn das Personal mal gen 200 und die Maschinen gen 90 gehen sollen.
Wie schonmal geschrieben, bliebe für das wirklich beste Ergebnis nur die Prüfung aller ! Konstellationen. Du hattest aber mal geschrieben, wenn eine 1 da ist soll sie genommen werden. Das grenzt das Ganze dann aber ein (Rechenaufwand weniger - Genauigkeit aber auch).
Im letzten Code war noch ein Fehler drin. Den hätte ich behoben. Nun die Frage, wie weiter? Alle Konstellationen wird m.E. nix. Für die 2 (innerhalb der 1er Zuordnung) könnte man noch eine Schleife basteln - müsste dann mal schauen, wie sich die Laufzeit verhält.
Das Vorgehen wäre dann: Erst mal alle 1 prüfen und setzten. Dann alle 2 prüfen und setzen, den REst mit 3en auffüllen. Das für alle Konstellationen mit 1 und in jeder 1 auch alle Konstellationen für 2 - zumindest so erstmal der Gedanke.

VG
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 413
Registriert: 15. Aug 2017, 18:36

Re: Daten in Bezug setzen und automatisch beste Kombination

Beitragvon thowe » 13. Jun 2018, 16:35

Hallo Matthias.

Vorerst einmal danke! Ich bin über das Lokalfenster einige mal den Code durchgegangen. Nur mit Matrix 2 x 2....
Ein paar Verständnisfragen dazu:
Zeile 34: warum 2dimensionales Array für ergebnis?
Zeile 83: Warum ergebnissumme = anzmit * 3 'Da 3 Ausprägungen von Qualifikationen gegeben sind?
Zeile 85: die äußere Schleife (spalte) durchläuft die Maschinen pro Mitarbeiter (zeilenweise ->Arbeitsblatt: Qualifikation)?
Zeile 86: erste innere Schleife (zeile) Durchläuft die Mitarbeiter, (spaltenweise -> Arbeitsblatt: Qualifikation)?
Zeile 92: zweite innere Schleife (quali). Warum werden hier nochmals alle Maschinen und Mitarbeiter durchlaufen?
Zeile 104: Kann diese Bedingung jemals zutreffen: If ergebnissumme <= tempsumme?
Zeile 116: Wann kann diese Bedingung jemals zutreffen? If ergebnissumme > tempsumme And tempsumme > 0 And eingetragen = anzmit Then?

Bei zwei Mitarbeiter und zwei Maschinen komme ich im schlechtesten Fall auf 48 Durchläufe. Im Besten Fall trifft die Bedingung …Then GoTo weiter zu
LG & ciao....

thowe
<think happy thoughts>
Hast du Milch für Kaffe? Ja, ich habe "Fettarme". Das sehe ich. Bekomme ich nun eine Milch?
Benutzeravatar
thowe
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 334
Registriert: 09. Jul 2007, 12:20
Wohnort: Graz

Re: Daten in Bezug setzen und automatisch beste Kombination

Beitragvon 1Matthias » 13. Jun 2018, 20:07

Moin Thowe!
Hier mal noch meine aktuelle Variante. In der letzten waren doch noch etliche Fehler drin. Habe deshalb noch weiter gewerkelt. Die Version hier ist etwas besser (die alten Fehler sollten weg sein) aber noch nicht mein Wunsch vom Durchlauf aller Varianten mit 1. Wollte auf Rekursion verzichten und müsste innerhalb der Schleife nochmal eine Schleife durch alle verbliebenen Varianten eintragen. Da kam ich bisher nicht dazu. HIer in der Variante ist zudem noch ein Blatt Anzeige neu. Darin werden mal alle durchgegangen Kombinationen (bei denen auch alle Mitarbeier gesetzt wurden) gelistet und auch die Matrix der im Dienst befindlichen Mitarbeiter mit den Qualifikationen dargestellt.
Im groben ist der Code ähnlich wie vorher, deshalb kann ich die Fragen noch mit beantworten. Wobei im Code jetzt ein paar Erweiterungen sind, weshalb dort die Nummerierung anders ist.

Z34: Der Code war dahin ausgelegt nur immer eine Zeile auszuwerten. Der muss also am Ende immer nur eine Zeile schreiben. Die beste Variante wird in ergebnis immer übertragen. Man braucht also nur eine Zeile. Das sie 2Dimensional ist, habe ich mir als Tick immer angewöhnt. Würde auch als 1Dimensionales Array gehen - kann man genauso gut ins Blatt schreiben. Hat also keine wirkliche Bedeutung.

Z83: Im Code prüfe ich für jede Konstellation die ich grad durchlaufe die Summe der Qulifikationen. Die wird dann am Ende mit der derzeit besten abgeglichen. Ist sie kleiner wird die Konstellation zwichengespeichert, ansonsten verworfen. Zum Vergleich brauchte ich am Anfang einen möglichst hohen Wert. Da die maximale Qualifikation 3 ist und ich maximal die anzmit (= ANzahl Mitarbeiter) setzen kann, wird also im schlimmsten Fall 3 mal anzmit als Summe herauskommen. Alle anderen wären kleiner. Damit hätte ich die obere Schranke.

Z85: Genau die vorher erstellte Matrix (da war noch ein Fehler) von Maschinenqualifikation zu Mitarbeiter wird zeilenweise vom Maschine 1 beginnend durchgegangen. Die Daten habe ich vorher aus den Blättern Schichtplan und Qualifikation ausgelesen. Auf die Blätter beziehe ich mich später gar nicht mehr. Ist so schneller.
Idee/Ziel war er erst mal alle Kombinationen für die 1 zu suchen (die sollten ja wenn möglich immer gesetzt werden). Deshalb gehe ich so durch die Spalten und bei einer 1 beginne ich die Suche nach einer Kombination.
Die Matrix wird jetzt im Blatt Anzeige auch mal mit ausgegeben - kann man es besser nachvollziehen. Wie aber an den Kombinationen links zu erkennen, fehlt wie eingangs beschrieben noch die innere Schleife. Deshalb werden (noch :? ) nicht alle Kombinationen für die 1 angezeigt sondern immer nur folgende. Habe zum Testen mal alle Qual. auf 1 gesetzt. Um nicht alle Komninationen zu testen (das wird m.E. zu viel bei der beabsichtigten Menge) hätte ich nur für 1 alle Möglichkeiten gesucht und die Qual. 2 und 3 sozusagen wenn möglich gesetzt.

Z86: Hängt mit der Erklärung von Z85 zusammen. In jeder Spalte wird in jeder Zeile nach einer 1 gesucht und dann gestartet. Deshalb 1.Schleife über die Spalten und in jeder Spalte eine Schleife über die Zeilen. Wobei Zeilen und Spalte sich auf meine MAtrix beziehen.

Z92: Hier gehe ich jetzt im worst case dreimal durch die ("kleiner werdende") Matrix (wieder meine erstellte). Bisher haben wir an dieser Stelle ja nur die erste aufgetretene 1 gefunden und die Kombinationssuche gestartet.
Dabei werden wieder erstmal alle 1en gesetzt. Um das zu machen, muss ich erstmal durch alle Spalten und Zeilen um die 1en zu finden. Sind alle vorhandenen 1en gefunden und gesetzt starte ich die suche mit der 2 und zum Schluß mit der 3. Das bei der 3 könnte man noch optimieren, in dem man nach 2 abbricht und die ganzen überprüfungen drum herum weg lässt. Alles was dann noch nicht gesetzt wurde erhälte der Reihe nach eine 3. Ist aber erstmal nur eine Idee.
Die erste Schleife dient also nur dazu, die verschiedenen 1en zu finden - nacheinander. Das ist also der Startpunkt um dann für jede gefundene 1 über die innere Schleife die Kombination zu suchen. Kann das grad nicht besser erklären. Hoffe das ist verständlich. Ansonsten vllt. mal an der Matrix im Blatt nachvolziehen.

Z104: Ja. Ergebnissumme ist die aktuelle gespeicherte Summe der bisher besten Kombination. tempsumme ist die Summe der grad geprüften Kombination. Da ja nicht alle Kombinationen gleich gut sind, gibt es welche bei dem ich andere Quali. nutzten musste (bsp. eine andere 1 genutzt und kann dann nur noch eine 2 setzten und nicht mehr eine 1 wie vorher) und die Summe gößter ist. An der Stelle breche ich die Schleife ab. Jetzt kann es nur noch größer und damit schlechter werden. Weiter zu prüfen würde nur noch Rechenzeit kosten aber nix mehr bringen.

Z116: Immer wenn eine bessere Kombination gefunden wurde. :) Ich fange mal hinten an. mit eingetragen = anzmit stelle ich sicher, dass auch alle verfügbaren Mitarbeiter gesetzt wurden. Falls die Schleife mal abbricht und noch nicht alle gesetzt sind ( ggf. eine 4 im der Qual. eingetragen etc) wird die Kombin. verworfen. Dabei kam mit grad der Gedanke das man da auch nach der Maschinenanzahl prüfen müsste. ISt jetzt im Code geändert. Tempsumme > 0 ist eigentlich nur für den Start von Bedeutung. Da liegt ja noch kein Wert vor. Und ergebnissumme > tempsumme ist das eigentlich wichtige Kriterium. Wenn die SUmme der Qual. kleiner ist und dann noch alle Mitarbeiter eingetragen wurden, wird diese Kombination übernommen.

Hoffe mal das war verständlich.
Wie kommst du aber auf 48 Durchläufe bei einer 2x2 Matrix. Das sollten eigentlich weniger werden. Die Spalten=Maschinen die schon gesetzt wurden "fliegen ja eigentlich raus". DA läuft dann keine Schleife mehr durch die Zeilen. Dafür setzte ich bei besetzten Maschinen ein x in die 0 Zeile der MAtrix und beim Namen lösche ich den raus. Die braucht man dann ja nicht mehr prüfen.

Bei Fragen, Kritik, Verbesserungsvorschlägen etc. immer melden. Bzw. wenn es zu kompliziert oder Nonsens ist auch melden. Kein Problem.
SChönen Abend noch.
VG
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 413
Registriert: 15. Aug 2017, 18:36

Re: Daten in Bezug setzen und automatisch beste Kombination

Beitragvon thowe » 14. Jun 2018, 06:51

Hallo Matthias,

Ganz viele Dankeschön!!!!, für die Beantwortung und deine Unterstützung.

Das ist mir schon klar das du deine eigene Matrix/dein Array "mitarbeiter" abarbeitest, und nicht die Spalten/Zeilen von einem Excel-Arbeitsblatt...

Ich werde mal die "neuere" Version durchgucken.

Ich möchte nur wenn auf Basis deiner Lösung "richtig gerechnet" wird, einfach das rundherum bauen, damit es "dynamischer" ist.
Jedenfalls hast du hier mir persönlich (und wahrscheinlich auch anderen) mit folgendem geholfen.

Arrays, iterieren durch Arrays, Schleifenabbruch, Addressierung von Arrays (das mit der IIF Bedingung zu machen ist für mich "genial").

Jedenfalls nochmals Danke.
LG & ciao....

thowe
<think happy thoughts>
Hast du Milch für Kaffe? Ja, ich habe "Fettarme". Das sehe ich. Bekomme ich nun eine Milch?
Benutzeravatar
thowe
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 334
Registriert: 09. Jul 2007, 12:20
Wohnort: Graz

Re: Daten in Bezug setzen und automatisch beste Kombination

Beitragvon 1Matthias » 14. Jun 2018, 19:14

Moin!
Also so richtig wie gewollt listet er die Kombinationen nicht auf. Habe nochmal geschaut. Nur mit Schleifen wird es nicht gehen. DAmit kann man nicht so richtig alle Durchläufe erstellen. Muss wohl doch eine Rekursion einbauen. Da komme ich aber grad nicht dazu. Wenn ich es gebastelt habe (und es auch passt :) ) würde ich die mal hochladen.
VG
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 413
Registriert: 15. Aug 2017, 18:36

Re: Daten in Bezug setzen und automatisch beste Kombination

Beitragvon thowe » 15. Jun 2018, 10:06

Hallo Matthias,

nein rekursiv ist nicht notwendig.

Wir können zumindest bis 170! die Falkutät ( eigentlich Permutationen...) in Excel iterativ (also in Schleifen) durchlaufen. Das lässt - soweit meine Recherche - der (Stapel)Speicher (korrekter Ausdruck?) zu...

Dh zB bis zu 56 Mitarbeiter bei 3 Maschinen oder
Dh zB bis zu 42 Mitarbeiter bei 4 Maschinen

also von der Architektur ungefähr so (exemplarisch):

Code: Alles auswählen
Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer, f As Integer
Dim  n As Long
n = 0

For a = 1 To 1
  For b = a + 1 To 2
    For c = b + 1 To 3
      For d = c + 1 To 4
        For e = d + 1 To 5
            n = n + 1
          Next e
        Next d
      Next c
    Next b
  Next a


Das heißt das LösungsArray (dzt. "mitarbeiter") verdoppelt sich mit jedem n.
[*klugscheiß an*]
Aber das Problem ist an sich NP-vollständig (lässt sich also vermutlich nicht vollständig effizient lösen)
[*klugscheiß aus*]
Was das für den Speicher bedeutet weiß ich nicht.
Jedenfalls kann man dann aus dem Array jene Lösung mit Subsum(min) heraussuchen, und als optimale Lösung festsetzen.
Wie denkst du darüber?
LG & ciao....

thowe
<think happy thoughts>
Hast du Milch für Kaffe? Ja, ich habe "Fettarme". Das sehe ich. Bekomme ich nun eine Milch?
Benutzeravatar
thowe
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 334
Registriert: 09. Jul 2007, 12:20
Wohnort: Graz

Re: Daten in Bezug setzen und automatisch beste Kombination

Beitragvon 1Matthias » 19. Jun 2018, 17:59

Moin!
Also ganz so einfach dürfte der Schleifenaufbau nicht sein. :) Und ob das nun NP endlich ist - damit habe ich mich nie gerne beschäftigt.
Wenn ich deine Idee richtige Interpretiere, willst du ein Array aufbauen in dem alle Kombinationen (=Permutationen) drin sind. Anschließend dann zeilenweise die Summen bilden und die kleinste Summe finden. ODer? Wenn ja, würde ich das Prozedere dahingehend ändern, dass in einer Schleife über alle Mitarbeiter jeweils die Kombinationen zu diesem gelistet und ausgewertet werden. Ansonsten kommst du auf eine zu große Zahl an Zeilen. Dafür gibt es auf meinem Rechner keinen Datentyp, der die Zählvariable beinhalten könnte. Long geht nur bis 2 Mrd. Das wäre grad mal 12! .
Hätte mal anbei noch was gebastelt. Die bunten Schaltflächen sind neu. Der alte Code hatte doch noch Schwächen und einige Fehler.
In der roten Variante habe ich mal den Schleifenaufbau für bis zu 6 Maschinen gebastelt. Könnte man noch erweitern auf mehr Maschinen (Personal ist egal). Habe dafür den zu kopierenden Bereich bzw. wo er einzufügen ist und was dann geändert werden muss markiert. Der Code liegt in Variante 2.
Der grüne Versuch sollte unabhängig von Personal- und Maschinengröße sein. Um alles abzuarbeiten habe ich mal auf Rekursion verzichtet und manipuliere die Schleifenvariable (ich weiß soll man eigentlich nicht machen, anders wäre es aber m.E. nicht gegangen). Der Code liegt in Variante 3.
Bin mir fast sicher :oops: das keine Fehler drin sind. Finde aber ab und an doch noch welche bzw. schrecke im Schlaf hoch und hab nen Fehler. :) Bin aber soweit alles mal im EInzelschritt und lokalen Fenster durchgegangen und sollte passen.
Der Ablauf ist bei beiden Varianten in etwa identisch. Es werden alle Permutationen durchgegangen und die beste wird sich gemerkt. Habe dabei noch eine Schranke / Abschätzung drin, damit er offensichtlich nicht erfolgversprechende Varianten abbricht.
Bei beiden Codes kann die Maschinenzahl größer als die Mitarbeiterzahl sein. Dann werden an nicht besetzten Stellen "x" eingetragen. Sollte ja nach TE vorkommen.
Kannst es dir ja mal anschauen und testen.
VG
Du hast keine ausreichende Berechtigung, um die Dateianhänge dieses Beitrags anzusehen.
1Matthias
Im Profil kannst Du frei den Rang ändern
 
Beiträge: 413
Registriert: 15. Aug 2017, 18:36

Vorherige

Zurück zu Excel Forum (provisorisch)

Wer ist online?

Mitglieder in diesem Forum: Hajo_Zi, Pearli123 und 17 Gäste