Office: (Office 2016) Stärkste Kombination berechnen/ausgeben

Helfe beim Thema Stärkste Kombination berechnen/ausgeben in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo, ich bräuchte Hilfe um mir eine automatische Berechnung der stärksten Teamkombination nach ihren jeweiligen Fähigkeiten ausgeben zu lassen. Und... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von fitti811, 10. August 2021.

  1. fitti811 Erfahrener User

    Stärkste Kombination berechnen/ausgeben


    Hallo,

    ich bräuchte Hilfe um mir eine automatische Berechnung der stärksten Teamkombination nach ihren jeweiligen Fähigkeiten ausgeben zu lassen.

    Und zwar habe ich in den Zeilen die jeweiligen "Personen", und in den Spalten ihre jeweiligen Fähigkeiten verteilt.
    Bei den Fähigkeiten kann es natürlich Dopplungen usw. geben. Am Ende soll mir aber unten in den Spalten B14 bis C23 die entsprechend stärkste Kombination der Personen mit ihren Fähigkeiten ausgegeben werden.
    In der Datei habe ich versucht das stärkste Team entsprechend zu kennzeichnen (dicker Rahmen) und unten in die Auflistung zu schreiben. (Ich hoffe die 470 in Summe sind auch das höchste)

    Fragen versuche ich natürlich so gut ich kann zu beantworten. Vielen Dank schonmal an jeden der sich das hier überhaupt durchgelesen hat. :)
    Gruß
    Fitti
     
    fitti811, 10. August 2021
    #1
  2. Hajo_Zi
    Hajo_Zi Erfahrener User

    Tabelle1
     BCDEFGHIJK
    210203040505035402515
    315253045503530453020
    420203540304035353525
    525253540201510353525
    645251540353015154030
    750454035252020205050
    835251525402520354545
    925302530153025451525
    1015504525156025252530
    1130356050452015304035
    die bedingte Formatierung Fülleffekte, Symbole, Datenbalken
    werden in dieser Tabelle nicht dargestellt
    Schriftart wird in dieser Tabelle nicht dargestellt

    Bedingte Formatierung Haupttabelle 1  
    Wird angewendet auf   Nr. Bed. Regeltyp  Operator   Formel1   Formel2   Format Schrift Füll-farbe   Unterstrichen   Schrift- farbe Muster   Muster-farbe Typ Bereich
    $B$2:$K$1101.Bed.: Formel ist =B2=KGRÖSSTE($B2:$K2;1)  255         2$B$2:$K$11
    $B$2:$K$1102.Bed.: Formel ist =B2=MAX(WENN($B2:$K2<>MAX($B2:$K2);$B2:$K2))  12611584         2$B$2:$K$11
    Bedingte Formatierung Haupttabelle 2 oberere/unterer Bereich    
    Wird angewendet auf   Nr. Bed.  Format Zelle Anhalten Auswahl   Anzeige   Anzahl   Durchschnitt   Typ   Bereich
    $B$2:$K$1101.Bed.   Falsch          2$B$2:$K$11
    $B$2:$K$1102.Bed.   Falsch          2$B$2:$K$11
    Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.


    Füllfarben und Muster  
    Zelle Rot Grün Blau Color Muster Farbe
    B6:B7, D11, E4:F3, G2, G4, G10, I9, J7:K8 106168795220458   
    C7, C9:E9, D4:E3, E11, F8, G9, H4, I2:I4, I5:J6 2552171026740479   
    Zellen mit Füllfarbe automatisch werden nicht dargestellt

    Schriftformate  
    Zelle Rot Grün Blau Color Stil Unterstreichung Effekte Durchgestrichen Schriftart
    B2:K11        Arial 
    Zellen mit Schriftformatierung automatisch werden nicht dargestellt
    http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
    http://Hajo-Excel.de/tools.htm
    Add-In-Version 25.23 einschl. 64 Bit
    XHTML-Tabelle zur Darstellung in Foren, einschl. Funktion zum Zurücklesen in eine Excel-Tabelle

    Stärkste Kombination berechnen/ausgeben GrußformelStärkste Kombination berechnen/ausgeben Homepage
     
    Hajo_Zi, 10. August 2021
    #2
  3. fitti811 Erfahrener User
    Guten Morgen Hajo,

    erstmal Danke für deine Antwort, leider ist das nicht genau das was ich brauche, zumindest wenn ich es richtig verstehe. In deiner Lösung geht es glaub ich nur um die bedingte Formatierung oder?

    Was ich aber bräuchte wäre die Berechnung des stärksten möglichen Teams. Also unten in B14:B23 die Personen und in C14:C23 die werte der entsprechenden Fähigkeit.

    Als Beispiel vielleicht Fähigkeit 9 + 10
    Hier sind die Personen 6 u. 7 die stärksten mit den werten 50/45.
    Dann sollte die Berechnung für Fähigkeit 9 + 10 ergeben das dort bei Fähigkeit 9 einmal die Person 6 steht, und bei Fähigkeit 10 die Person 7 so dass für die Team-Summe schonmal 95 Punkte zusammen kommen.
    Und diese Berechnung müsste für jede Fähigkeit geschehen, so das am Schluss in C24 die höchst mögliche Summe erscheint, OHNE das eine Person doppelt genannt wird.

    Ich weiß leider nicht wie ich das besser beschreiben könnte, und hoffe das es weiterhilft.
    Auf Fragen werde ich natürlich versuchen so gut ich kann zu antworten. :)

    Gruß
    Fitti
     
    fitti811, 11. August 2021
    #3
  4. Hajo_Zi
    Hajo_Zi Erfahrener User

    Stärkste Kombination berechnen/ausgeben

    Hallo Fitti,

    Gut dan habe ich die Aufgabe falsch verstanden. Ich habe mich an Deine Markierung gehalten. Das war dann wohl falsch.
    Ich bin dann raus.

     
    Hajo_Zi, 11. August 2021
    #4
  5. fitti811 Erfahrener User
    Ok, Schade. Aber bist du jetzt raus wegen der Markierungen oder weil meine Anfrage nicht möglich ist?
    Vielen Dank trotzdem *für den* Lösungsansatz.
    Gruß
    Fitti

    Edit in *
     
    fitti811, 11. August 2021
    #5
  6. steve1da Super-Moderator
    Hola,
    ich verstehe es nicht wirklich. Warum ist bei Fähigkeit 1 Person5 aufgeführt, obwohl Person6 einen höheren Wert hat?
    Hängen die Fähigkeiten irgendwie zusammen? Du hast als einziges Beispiel nur Fähigkeit 9 und 10 erwähnt.
    Gruß,
    steve1da
     
    steve1da, 11. August 2021
    #6
  7. fitti811 Erfahrener User
    Hallo Steve,
    bei Fähigkeit 1 ist Person 5 aufgeführt weil diese Person hier (nach Person 6) den nächst höchsten Wert hat.
    Person 6 ist für Fähigkeit 9 eingetragen weil sie dort ebenfalls den höchsten Wert (50) hat.
    So ergeben die beiden Personen in Summe einen Wert von 95 für die beiden Fähigkeiten.

    Wenn hier bei Fähigkeit 1, Person 6 mit den 50 gewählt würde,
    dann wäre bei Fähigkeit 9, Person 5 mit dem Wert von 40 zu wählen,
    da Person 7 (welche bei Fähigkeit 9 den 2. höchsten Wert mit 45 hat)
    schon bei Fähigkeit 10 gewählt werden muss.
    Hier wäre dann für die 3 Fähigkeiten eine Summe von 135 statt wie im Beispiel 140.

    Also jede Person hat alle 10 Fähigkeiten in verschieden hoher Ausprägung, aber jede der 10 Fähigkeiten darf nur einmal besetzt werden. Die Krux ist es jetzt Excel ausrechnen zu lassen welche "Aufstellung" (ohne Doppelbesetzung) die beste ist.
    Es können natürlich (und müssen wahrscheinlich) gern diverse Hilfsspalten/Tabellen erstellt werden.

    Gruß
    Fitti
     
    fitti811, 11. August 2021
    #7
  8. steve1da Super-Moderator

    Stärkste Kombination berechnen/ausgeben

    Person 6 ist für Fähigkeit 9 eingetragen weil sie dort ebenfalls den höchsten Wert (50) hat.
    Und warum nicht Fähigkeit 10, dort ist der Wert doch auch 50?
     
    steve1da, 11. August 2021
    #8
  9. fitti811 Erfahrener User
    Das wäre in dem Fall tatsächlich egal.
    Fähigkeit 9 - Person 6 - Wert 50
    Fähigkeit 10 - Person 7 - Wert 45
    Summe 95

    Fähigkeit 9 - Person 7 - Wert 45
    Fähigkeit 10 - Person 6 - Wert 50
    Summe 95
     
    fitti811, 11. August 2021
    #9
  10. Exl121150 Erfahrener User
    Hallo,

    bei diesem Problem handelt es sich um ein Optimierungsproblem: alle 10 Personen sollen derart den 10 Fähigkeiten zugeordnet werden (anhand der Tabelle), sodass die Fähigkeitensumme dieser 10 Personen maximal wird.

    Das wäre an und für sich ein klassisches Problem für den Excel-Solver, der schon seit langer Zeit als Add-In in Excel aktivierbar ist. Mein Problem bei der Umsetzung im Excel-Solver ist, wie man die 10! = 3.628.800 Permutationen der 10 Personen (zu ihren Fähigkeitswerten) dort implementiert. Denn diejenige Permutation bzw. Permutationen, die die maximale Fähigkeitensumme liefert/liefern, stellt die Lösung(en) dar.
     
    Zuletzt bearbeitet: 11. August 2021
    Exl121150, 11. August 2021
    #10
    1 Person gefällt das.
  11. Exl121150 Erfahrener User
    Hallo,

    jetzt habe ich es geschafft, im Excel-Solver die permutierende Einstellung für die 10 Primärvariablen hinzubekommen, sodass der Solver ohne Protest mit korrektem Resultat die Optimierung durchgeführt hat:
    Stärkste Kombination berechnen/ausgeben upload_2021-8-11_17-14-12.png Stärkste Kombination berechnen/ausgeben upload_2021-8-11_17-15-3.png
     
    Exl121150, 11. August 2021
    #11
    1 Person gefällt das.
  12. fitti811 Erfahrener User
    Hallo Anton,
    deine Lösung sieht wirklich sehr gut aus.
    Ich habe jetzt versucht sie in meiner Tabelle entsprechen einzubauen.
    Den Solver habe ich "installiert", und nach anfänglichen Schwierigkeiten auch eingestellt bekommen.
    Funktioniert gut, vielen Dank dafür. :)

    Würde das mit diesem Solver auch funktionieren wenn sich die Personenanzahl erhöht?
    Also sprich, eine Berechnung der 10 besten Fähigkeiten/Personen aus 15 oder 20 Personen?

    Vielen Dank nochmal.
    Gruß
    Fitti
     
    fitti811, 11. August 2021
    #12
  13. Exl121150 Erfahrener User

    Stärkste Kombination berechnen/ausgeben

    Hallo,

    Natürlich! Es müssten mindestens die Bereichsangaben (für das Ziel, für die Variablenzellen bzw. für die Nebenbedingungen) angepasst werden und ebenso auch die Tabelle der Personen/Fähigkeiten-Aufschlüsselung.

    Man muss dabei aber bedenken, dass im schlimmsten Fall nicht bloß
    Permutationen 10! = 3.628.800 vorliegen, sondern
    Permutationen 15! = 1.307.674.368.000 bzw.
    Permutationen 20! = 2.432.902.008.176.640.000 (=2,43 Trillionen) anfallen.
    Das ist die Anzahl an unterschiedlichen Anordnungen der Personen 1, 2, 3, ..., 19, 20 in den Variablenzellen (=Primärvariablen), die theoretisch durchprobiert werden müssten, ob doch nicht ein noch höherer Zielwert in der Zielzelle gefunden werden kann.
     
    Exl121150, 12. August 2021
    #13
  14. fitti811 Erfahrener User
    Hallo,

    ich habe mir das mal mit 15 Personen eingestellt zum testen. Leider funktioniert die Berechnung nicht.
    Er zieht weiterhin nur die ersten 10 Personen in die Berechnung ein.

    Also ich habe die Tabelle um 5 Personen erweitert, und die INDEX Formel angepasst auf die jetzt 15 Personen. Außerdem auch auf Fakultät(15) geändert.
    Im Solver ist ja nichts zu ändern da sich die untere "Ausgabetabelle" ja nicht verändert.
    Es bleiben ja weiterhin 10 Fähigkeiten, denen die 10 stärksten Personen aus den jetzt 15 Personen zugeordnet werden sollen.
     
    fitti811, 12. August 2021
    #14
  15. Exl121150 Erfahrener User
    Hallo,

    genau da liegt höchstwahrscheinlich das Problem:

    1) Bisher hattest du eine 10x10-Person/Fähigkeits-Zuordnungstabelle, jetzt wird das mindestens eine 15x10 bzw. 15x15 Tabelle. Du wirst also auf alle Fälle mehr Zeilen benötigen wie bisher.

    2) Wegen 1) wird der Bereich für die Primärvariablen (bisher 10: B14:B23) um mindestens 5 Zeilen nach unten verschoben, wobei sich neben diesem Bereich der Bereich für die Zielfunktion (bisher C14:C23) zusammen mit der maximalen Zielwert-Zelle (bisher C24) befand. Dabei greifen die Index-Formeln der Zielfunktion auf die eigentliche Tabelle mit den Nebenbedingungen (bisher B2:K11) zu.

    3) Diese 4 fett geschriebenen Ausdrücke sind Fachausdrücke aus dem mathematischen Teilgebiet der "linearen Optimierung", das sich erst nach dem 2.Weltkrieg aufgrund militärischer Logistikprobleme entwickelt hat.
    Die Primärvariablen können dabei beliebige Werte annehmen, solange/soweit sie die Nebenbedingungen einhalten. Es wird dann diejenige Wertekombination unter den Primärvariablen gesucht, die die Zielwert-Vorgabe (=maximaler bzw. minimaler Zielwert) erfüllt. In unserem Fall ist das "maximaler Zielwert".
    In unserem (zuerst genannten) Fall handelt es sich ferner um ein diskretes Optimierungsproblem: die Primärvariablen dürfen nur ganzzahlige Werte annehmen (deshalb die Nebenbedingung: $B$14:$B$23=AllDifferent), wobei diese Nebenbedingung noch weiter einschränkt: Da es sich hier um 10 Zellen handelt, liegen somit 10 Primärvariablen vor, deren Werte aus der Menge {1, 2, 3, ..., 9, 10} genommen werden müssen und die alle unterschiedliche Werte haben müssen. Somit wird vermutlich das Add-In mit eben dieser Wertzuweisung starten, wenn man auf den "Lösen"-Button klickt. Durch Vertauschen (=Permutieren) dieser Werte untereinander innerhalb der Primärvariablen wird so diejenige Primärvariablen-Wertekombination ermittelt, die den maximalen Zielwert liefert.
    Welches Verfahren dabei Microsoft mit der Lösungsmethode "EA - Evolutionärer Algorithmus" implementiert hat, weiß ich nicht (weil dies vermutlich auch nicht öffentlich dokumentiert sein wird). Mit letzter Sicherheit wird man dabei vermutlich erst dann wissen, welche Primärvariablen-Kombination den maximalen Wert liefert, wenn alle Permutationen (10! = 3.628.800) durchprobiert sind. Diese Lösungsmethode bricht das Durchprobieren jedoch ab, wenn eine gewisse Zeit lang sich keine Veränderung beim (bisher maximalen) Zielwert ergeben hat.

    4) Dein zuerst präsentiertes Problem sah vor, 10 Personen genau 10 Fähigkeiten zuzuordnen. Durch Permutieren dieser 10 Personen war die maximale Zielfunktion/Zielwert zu ermitteln. Im Excel-Solver kann man dies erreichen über die AllDifferent-Nebenbedingung.
    Dein jetziges Problem ist dem gegenüber komplexer: Es bedeutet, dass 15 Personen den 10 Fähigkeiten zugeordnet werden müssen. 5 Personen sind bei jeder Zielfunktion/Zielwert-Ermittlung auszuscheiden und die restlichen 10 Personen dafür heranzuziehen, was insgesamt 15!/5! = 10.897.286.400 Kombinationen/Permutationen ergibt, die auf Optimalität zu testen wären. Ich weiß auch nicht, wie diese Konstellation einzugeben wäre in den vorliegenden Excel-Solver. Ich vermute, dass dies dessen Möglichkeiten überfordert.

    5) Der vorliegende Solver wurde offenbar nicht von Microsoft erstellt, sondern von der Firma Frontline Systems, Inc, wo detailierte Hilfe und Information zu beziehen ist unter folgendem Link:
    Solver-Hilfe unter www.solver.com.
    Der Excel-Solver stellt offenbar eine abgespeckte (Basis)-Version der dort vorhandenen Optimierer dar, um den Mund wässrig zu machen auf mehr.

    6) Die Angabe der Anzahl an Permutationen bewirkt im Excel-Solver nichts, ich habe diese Zahl nur angeführt, um den Umfang des Problems deutlich zu machen.
     
    Exl121150, 13. August 2021
    #15
Thema:

Stärkste Kombination berechnen/ausgeben

Die Seite wird geladen...
  1. Stärkste Kombination berechnen/ausgeben - Similar Threads - Stärkste Kombination berechnen

  2. Ändern der Farbe, Art oder Stärke einer Linie in Word für Mac

    in Microsoft Excel Tutorials
    Ändern der Farbe, Art oder Stärke einer Linie in Word für Mac: Ändern der Farbe, Art oder Stärke einer Linie in Word für Mac Excel für Microsoft 365 für Mac Word für Microsoft 365 für Mac PowerPoint für Microsoft 365 für Mac Word...
  3. Ändern der Farbe, Art oder Stärke einer Linie

    in Microsoft Excel Tutorials
    Ändern der Farbe, Art oder Stärke einer Linie: Ändern der Farbe, Art oder Stärke einer Linie Excel für Microsoft 365 Word für Microsoft 365 Outlook für Microsoft 365 PowerPoint für Microsoft 365 Publisher...
  4. Starker Mehrbenutzerbetrieb in Access

    in Microsoft Access Hilfe
    Starker Mehrbenutzerbetrieb in Access: Hallo liebes Forum, wir arbeiten zur Zeitzu Zweit an einer Access-DB mit sehr vielen Tabellen-Daten. So langsam sind wir auch Fertig nur gibt es ein Problem und das ist sind unsere Nutzer. Denn...
  5. Teilen von CatiaV5-Inhalten (CAD) nicht möglich wegen starken Flackerns

    in Microsoft Teams Hilfe
    Teilen von CatiaV5-Inhalten (CAD) nicht möglich wegen starken Flackerns: Hallo Zusammen, beim Teilen von Bildschirminhalten des CAD-Programms Catia V5 (Dassault) tritt in Teams ein relativ niederfrequentes Flackern des geteilten Fensters auf. Offensichtlich wird der...
  6. MacBook-Lüfter dreht stark auf bei MS Teams Video Konferenz

    in Microsoft Teams Hilfe
    MacBook-Lüfter dreht stark auf bei MS Teams Video Konferenz: Hallo beim Benutzen von MS Teams in einer Videokonferenz dreht der Lüfter meines neuen MacBook Air 2020 derart auf, dass ich die Konferenzteilnehmer kaum verstehen kann. Die Software ist auf dem...
  7. Audiodatei verliert bei Einbettung in PP 2019 stark an Qualität - Autokomprimierung?

    in Microsoft PowerPoint Hilfe
    Audiodatei verliert bei Einbettung in PP 2019 stark an Qualität - Autokomprimierung?: Hallo liebe Leute, ich habe folgendes Problem und würde mich sehr freuen falls ihr helfen könnt. In der Suche habe ich nichts passendes finden können. Wenn ich eine von mir aufgenommene...
  8. Text Kalendereintrag vergrößern, Kalenderlinien stärker

    in Microsoft Outlook Hilfe
    Text Kalendereintrag vergrößern, Kalenderlinien stärker: [ATTACH][ATTACH] Hallo liebe Forumsmitglieder, ich brauche Ihre Hilfe. Ich habe auf meinem PC Windows 1o und Office 2019 Professional Plus. 1. Ich möchte gerne in Outlook, Kalendereintrag, den...
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden