Clusteranalyse und automatische Sortierung

[BimHennemann]

Guten Tag zusammen, ich hoffe die Überschrift verrät in etwa was ich aktuell ausprobiere.

ich habe eine Matrix mit Bauteilen erstellt welche darstellt welche Wechselwirkungen die Bauteile aufeinander ausüben.
9=sehr starke Wechselwirkung
6=starke Wechselwirkung
3=geringe Wechselwirkung
0=keine Wechselwirkung (in diesem Fall der Übersicht zur Liebe weggelassen).

In der Bilddatei habe ich es beispielhaft mal mit Bauteilen von einem Auto gemacht.
Die obere Tabelle zeigt quasi wie ich die Wechselwirkungen bewerte.
Die untere Tabelle zeigt was ich gerne raus bekäme (nämlich eine Gruppierung der Bauteile welche sich stark beeinflussen)
   


Auf Grund der dort geringen Anzahl von Bauteilen, ist mir das Clustern "per Hand" noch recht gut gelungen.

Wenn ich jetzt aber noch mehr Bauteile habe, wird es sehr unübersichtlich. Gibt es in Excel eine Funktion welche die Clusterung von alleine vornimmt? Oder ist jemandem eine passende Software bekannt (am liebsten Freeware)?
Vielen Dank und viele Grüße
Robin

[schauan]

Hallöchen,

ich denke, wenn Du das einmal per Hand gemacht hast kannst Du das dann auf verschiedene Autos anwenden.
Ist auch schwer einschätzbar. Warum stehen z.B. Motor und Getriebe im Ziel ganz oben, wo doch Karosserie und Scheiben ebenfalls 2x die 9 haben und darüber hinaus noch auf andere Bauteile wirken - also deutlich mehr Einfluss haben als Motor und Getriebe? Ansonsten könnte ich mir ja vorstellen, dass Du die Summe der Einflüsse nimmst und danach sortierst. Oder mach es wie bei den olympischen Spielen. Zähle erst alle 9en und sortiere danach. Bei Gleichstand nimmst Du die 6en dazu. usw.

[Ego]

Hallo Robin,

A) Wie groß wird denn die Anzahl der Bauteile maximal und wie groß die Anzahl der Gruppen?
Abhängig von diesen Größen könnte man eventuell alle Kombinationen durchprobieren oder ein Optimierungsproblem für den Excelsolver erstellen.

B) Für beide Verfahren benötigt man ein Maß für die Güte der Clusterung. Ich nehme an, dass es der Mittelwert der grau hinterlegten Felder ist. Ist das richtig?
(Wenn ja reicht es den Mittelwert der Werte unterhalb der Diagonalen zu berücksichtigen, da die Matrix ja symetrisch ist.

[Ego]

Hallo Robin,

In der Anlage einmal ein Lösungvorschlag mit Nutzung des Excelsolvers und hier einige Bemerkungen:

A) Grenzen/Optimum
Da der Excelsolver "nur" 100 Variablen verarbeiten kann, ist diese Lösung nur für maximal 101 Bauteile möglich.
Da das Optimierungsproblem nicht linear ist, ist es nicht absolut sicher, dass der Algorithmus immer die optimale Lösung findet, aber mit Sicherheit eine gute Lösung.
Wenn man alle Kombinationen durchrechnet könnte man auf jeden Fall die optimale Lösung finden. Da aber die Anzahl der zu prüfenden Kombinationen exponentiell mit der Anzahl der Bauteile steigt (zB. ca 10^24 Kombinationen bei 30 Bauteilen und 7 Gruppen), ist hierfür die mögliche Anzahl der Bauteile für die man in akzeptabler Zeit alle Kombinationen in Excel berechnen kann aber kleiner (abhängig von der Anzahl der Gruppenwohl kleiner als 30).
Bei 30 Bauteilen findet aber auch der Solver (insbesondere wenn man ihm Zeit läst) wohl die optimale Lösung.
Bei mehr als 101 Bauteilen ist meines Erachtens Excel nicht mehr das richtige Werkzeug.


B) Ablauf
1. Eintragen der Parameter (hellgrüne Felder im Bereich "Liste").
2. Starten des Solver über "Daten">"Analyse">"Solver"
3. Anzeige der Gruppenzuordnung im Bereich "Ausgabe"
4. zur Zeit wenn gewünscht Sortierung der Zeilen im Bereich "Ausgabe" (im Beispiel A14:I20) nach Gruppe.

C) Solver
Der Solver variiert die Gruppen im Bereich "Gruppen" und prüft für jede Variation, ob sich der Wert in der Zielzelle (im Beispiel N2) gegenüber dem bisherigen Optimum verbessert hat und merkt sich dann diese Variation für die Ausgabe des optimalen Ergebnisses. Wenn für die in den Optionen vorgegebene Zeit keine bessere Lösung gefunden wird, bricht der Algorithmus mit der Ausgabe der bisherigen optimalen Variation ab.

1. Falls der Solver in dem oben angegebenen Menü noch nicht vorhanden ist, muss er noch als Add-In eingerichtet werden (Anleitungen hierfür  gibt es bei Tante Google).
2. Für dieses Problem muss der "Evolutionäre Algorithmus" genutzt werden.
3. Bei einer kleinen Anzahl von Bauteilen kann unter den Optionen die "Höchstzeit ohne Verbesserung" reduziert werden, da schnell die optimale Lösung gefunden wird.
Bei einer grossen Anzahl von Bauteilen kann man die "Höchstzeit ohne Verbesserung" erhöhen, um auch die Wahrscheinlichkeit die optimale Lösung zu finden zu erhöhen.
4.  Falls sich die Anzahl der Bauteile ändert, müssen die Parameterbereiche sowohl in den Variablen, als auch in den Nebenbedingungen angepasst werden (Die Gruppe des ersten Bauteiles muss hierbei nicht mit berücksichtigt werden).
5. Falls sich die Anzahl der Gruppen ändert, muss die Obergrenze in den Nebenbedingungen angepasst werden.

D) UDF "Machs"
Diese Funktion ermittelt für die aktuell eingetragene Variation der Gruppen den Mittelwert der Zuordnungen.  Aus dem Bereich Liste wird nur die linke untere Dreiecksmatrix genutzt.
1. Falls sich die Anzahl der Bauteile ändert, müssen die Bereiche in den Parametern der Funktion "Machs" angepasst werden (hier mit Berücksichtigung des ersten Bauteiles).

E) Anzeige
Zur Zeit werden über den Button "anzeige" nur die Bauteile und die Gruppenzuordnungen im Bereich Ausgabe eingetragen. Die Wertematrix wird dann über Formeln angepasst und die Farbgebung über bedingte Formatierung durchgeführt.
Falls sich aber die Anzahl von Bauteilen häufiger ändert würde ich auch die Ausgabe und Formatierung der Wertematrix programmieren.

[Ego]

Hallo Robi,

zu

Bei 30 Bauteilen findet aber auch der Solver (insbesondere wenn man ihm Zeit läst) wohl die optimale Lösung.

Diese Aussage muss ich zurücknehme. Ich habe die Optimierung einmal für 34 Bauteile und 5 Gruppen durchgeführt. Ich erhalte bei einem festen Datenbestand bei mehreren Versuchen zwar meistens ähnlich gute aber immer verschiedene Ergebnisse.