Excel 2010 - Per VBA Kombinatorik auslesen lassen

[Surtia]

Aloha Community,

ist es Möglich per Excel alle Möglichen Kombinationen einer bestimmten Tabelle per VBA auslesen zu lassen?

Kategorie	A [Maßnahme]	B [min]
1	10	100
1	11	110
1	12	120
2	20	200
2	21	210
2	22	220
3	30	300
4	40	400
5	50	500
6	60	600

Als Ergebnis sollen alle Kombinationen ausgegeben werden.
Dabei ist zu beachten, dass die Kategorie 1 und 2 nicht in sich kombiniert werden dürfen (zb. 10 11).
Jedoch ist eine Kombination zwischen Kategorie 1 und 2 denkbar (z.b. 10 21)

Beipielhaftes Ergebnisse:
10 20 30 40 50 60 oder 11 60 oder 20 60 oder 30 40 oder 60 usw...

Ziel ist es, alle Kombinationen zu finden, deren Summen mindestens 450min betragen.

Alle Kombinationen sollen in Spalte C in Zellen untereinander geschrieben werden.
Wichtig ist, dass zu jeder Kombination in Spalte D die Summe der Minuten steht.
Alle Kombinationen deren Summen unter 450min betragen können verworfen werden.

Vielen Dank im Voraus!

 

[Alchemist]

Hallo,

eine Entscheidungsfrage ist sicher nicht, was du stellen wolltest. Die Antwort darauf wäre: ja.
Im Grunde ist es eher ein Logik Problem denn ein Programmierproblem.
Ich würde jede Maßnahme als einzelne Booleanvariable betrachten und dann die Regeln entsprechend formulieren. Das ist aber nicht ganz ohne und Grundkenntnisse der Aussagenlogik wären von Vorteil.

Bitte erklär mal genauer, was deine Frage ist.

 

[Surtia]

Ist es generell möglich diese Ausgabe im VBA zu programmieren?
Wie sieht der Quellcode aus?

Mir ist bewusst, dass das vermutlich sehr anspruchsvoll ist.

 

[Alchemist]

Also wie gesagt, gehen würde es.

Die Frage ist ein bisschen, wie viele Kombinationen es wirklich gibt. Im Beispiel hast du 10 Maßnahmen, wenn es aber in Wirklichkeit 100 sind, sieht die Vorgehensweise schon anders aus.

Ändert sich denn überhaupt etwas an den Daten oder ist es ein einmaliges Problem?

Ich würde sowas eigentlich in IBM ILOG CPLEX machen, dass ist aber noch mal eine ganz andere Baustelle.

 

[cumulonimbus8]

Ich sehe da kein richtiges Land.

Geht es da um alle theoretisch möglichen Kombinationen? Weil - das sind viele!
Wie sollen die denn in eine Spalte passen um leserlich zu sein? Um eine Kombination die zufällig in Zeile 25893 weilt zu erreichen müsstest du sie wahrhaftig suchen, also letztlich in ein Eingabesystem füttern und das abschicken was dir ein Resultat liefert.

Mir genügte eine stupide Eingabezeile [oder eine Text-Zelle] in VBA und das Ergebnis würde angezeigt oder käme in eine Zelle.
Ich könnte mir auch eine Batterie von Häkchen vorstellen [UserForm?!] die ein Code ausliest und auswertet. Aber eine Komplettaufstellung auf keinen Fall…

CN8

 

[Surtia]

Ich habe den ersten Beitrag auf das konkrete Praxisbeispiel umgeschrieben.

Ziel ist es, alle Kombinationen zu finden, deren Summen mindestens 450min betragen.

Das Auslesen der Kombinationen ist erforderlich, da nachfolgend zu jeder Kombi weitere Berechnungen notwendig sind und es als Nachweis einer Diplomarbeit dient (ohne VBA).

Es stimmt, die Kombinationen in nur eine Spalte (C) zu quetschen macht es unangenehm schwieriger.
Da eine maximale Länge von 6 Kombinationen (12 21 30 40 50 60 oder 11 22 30 40 50 60) möglich ist, könnten sich die Faktoren auch gern von Spalte C bis H erstrecken. Die zugehörigen Minutenwerte dann auf I bis N. Die Summe der Minuten in Spalte O, wobei O nicht ins VBA müsste.

Na wenn das mal keine Knobelei ist...

 

[Janush]

Na klar geht das. Im Prinzip muss es doch nur wie eine "Uhr" von oben nach unten "durchzählen". Erst alle einzeln, dann alle einzeln nochmal mit der 10 fest...usw. Irgendwann hat man alle Kombinationen durch denke ich.

Ich habe gerade keine Zeit das zu programmieren, aber vielleicht später, wenn es vorher noch keiner gemacht hat.

 

[Surtia]

Das wäre super!
Meine VBA-Kenntnisse reichen leider nicht aus um solche Schleifen oder Ausgaben in entsprechende Zeilen zu programmieren.

 

[Surtia]

Ich möchte nochmal nachhaken und fragen ob es jemanden möglich ist diese Problematik ins VBA umzusetzen?

 

[Alchemist]

Warum VBA? Das kannst du super bequem per Formel lösen... 2^10 ergibt 1024 Möglichkeiten. Eine einfache Variante um alle Kombinationen zu bekommen, ist die Umwandlung in Binärzahlen.
Eine 1 an Position 1 (von rechts gezählt) heißt, dass Maßnahme 1 gewählt ist. So zieht sich das dann durch und du hast alle Kombinationen.
Zur Prüfung kann ich dann einfach die Summen verwenden. Die Länge ist eine Matrixmultiplikation. In der 2. Tabelle hab ich das kurz gemacht, da Excel leider die Zahlen in Tabelle 1 nicht als solche erkennt. In Tabelle 1 sind aber die Formeln noch drin.

Excel erlaubt bei Binärzahlen nur 10 Bit und das linkeste Bit ist das Vorzeichen. Daher hab ich einfach bei 511 die Richtung gewechselt und bei -1 (1111111111) angefangen. So hat man alle möglichen Kombinationen.

Was ich vor allem noch sagen will, es sind selten Probleme die daran scheitern, dass man nicht programmieren kann. Die Formeln sind super einfach, nur die Idee muss man eben entwickeln können.

 

[cumulonimbus8]

Wenn du mitgelesen hättet wüsstest du, dass die Tabelle im 1. Post nur ein fragmentarisches Beispiel ist.
CN8

 

[Alchemist]

Wirklich? In #6 steht aber was anderes.

 

[Surtia]

@Alchemist
Du hast den Nagel auf den Kopf getroffen.
An erster Stelle erstmal vielen Dank für deine Mühe bzw. Lösung!

Leider besaß ich bis eben noch nicht die Kenntniss über die Formel DEZINBIN.
Wie ich finde ein geniales Werkzeug, da mir gleich andere Beispiele einfallen an denen dies verwenden kann.

Sehe ich das richtig, dass die 1024te Variante die Kombination 0000000000 wäre?
In Tabelle 2 hast du die nicht in Frage kommenden Zeilen bereits gelöscht, oder?

 

[Alchemist]

Sehe ich das richtig, dass die 1024te Variante die Kombination 0000000000 wäre?

Ja, das stimmt. Dies ist ja ohnehin keine Lösung für das Problem, daher habe ich sie einfach weg gelassen.

In Tabelle 2 hast du die nicht in Frage kommenden Zeilen bereits gelöscht, oder?

Ja, dazu habe ich nur kurz den Filter drüber gelegt und die entsprechenden Einträge gelöscht. Die Originaldaten sind aber alle in Tabelle 1. Da fehlt nichts.

 

[Surtia]

Subba.

Ähnliches habe ich vermutet - war unschlüssig, da kein gesetzter Filter zu erkennen war.
Jedenfalls eine grandiose Lösung zu einem nicht alltäglichen Problem.
Vielen Dank.

Rein hypothetisch würde mich interessieren, was zu tun ist wenn Kombinationen größer 1024 zu erwarten sind.
Für das hier konkret beschriebene Beispiel passte es glücklicherweise auf die Anzahl genau.

 

[Alchemist]

Da wäre es dann zumindest mit der Formel in Excel nicht mehr möglich.
Ein einfaches Makro, dass binär hochzählen kann, ist aber schnell geschrieben. Das Beispiel schreibt alle Binärzahlen in die 1. Spalte. Anstatt die Zahl zusammenzusetzen, kann man die Schleife auch einfach anpassen um jede Stelle in eine separate Spalte zu schreiben. Binär hochzählen ist zum Glück extrem einfach.
Die Grundidee ist ein Array zu erstellen. Die Länge des Arrays ist dabei die Anzahl an Einträgen, die kombiniert werden sollen; im Endeffekt die Länge der Binärzahl. (im Code als "anzahl" geschrieben).

Aktuell unterstütz Excel (Seit Version 2007) 1.048.576 Zeilen (2^20). Die Variable "anzahl" darf daher nicht größer als 20 sein, wenn du das Ergebnis ausschreiben willst.

Sub Kombis()
Dim binCode() As Integer
Dim anzahl As Double
Dim output As String
Dim weiter As Boolean
Dim kombinationen As Double

anzahl = 4
weiter = True

ReDim binCode(anzahl - 1)

kombinationen = 2 ^ anzahl

j = 1
output = "'"
Do While weiter

    For k = 0 To UBound(binCode)
        output = output & binCode(k)
    Next k
    
    Cells(j, 1) = (output)
    If j = kombinationen Then
        weiter = False
        Exit Do
    End If
        
        
    j = j + 1
    output = "'"

    For i = UBound(binCode) To 0 Step -1
        If binCode(i) = 0 Then
            binCode(i) = 1
            Exit For
        Else
            binCode(i) = 0
        End If
    Next i
    
Loop

End Sub

 

[Surtia]

Wow, super!

Ok, ich bin gestern mathematisch rangegangen und habe quasi für Kombinationsmöglichkeiten größer 1024 zwei seperate Spalten genutzt. Die Zweite habe ich jeweils um eine Stelle "rotieren" lassen. Am Ende werden die zwei Spalten per =VERKETTEN in einer dritten Spalte zusammengefasst. Dies ist natürlich deutlich zeitaufwendiger, komplizierter und fehleranfälliger.

Was bedeutet folgendes Schnipsel? Ist das eine Formatierung für eine Textausgabe?

output = "'"

Und den Teil verstehe ich nicht wirklich:

For i = UBound(binCode) To 0 Step -1

 

[Alchemist]

Hallo,

ja, für ein oder zwei weitere Stellen ist es kein Problem das so zu machen. Ab vier oder 5 wird es aber wieder kompliziert dann.

Der erste Schnipsel ist dafür da, dass am Anfang der Ausgabe ein Hochkomma steht (') sonst werden die führenden Nullen verworfen.
Der zweite Codeausschnitt ist dafür da, um von rechts hochzuzählen. Im Grunde geht das auch anders herum und ändert nichts am Ergebnis. UBound() liefert die obere Grenze des Arrays, also wie viele Einträge enthalten sind. Step -1 bedeutet, dass nicht hochgezählt wird, sondern in jedem Durchlauf i um 1 verringert wird.

 

[Surtia]

Ok, dann habe ich es richtig interpretiert, nur falsch beschrieben.
Letztlich selbiges als würde ich ein Hochkomma vor ein - oder = stellen.
Beide Codeschnipsel verstanden - danke Dir!

D.h. du hast quasi 1 Schleife in die 2 Bedingungen gepackt sind.
Eine Bedingung zum Zählen der zu füllenden Zellen und eine für den Text, der in die gezählte Zelle verfasst werden soll.

Wozu dient dann der output = "'" vor der Schleife? Ist dieser der beiden überflüssig?
Ich mache mir so intensiv Gedanken darüber, da ich so zum Teil lerne besser mit VBA umzugehen.
Es ist mein Versuch den Code so kompakt wie möglich zu verfassen.
Schaffe ich dies oder denke ich es durch, habe ich es letztlich verstanden.
Daher die ganzen doofen Fragen. Sry

 

[Alchemist]

Wozu dient dann der output = "'" vor der Schleife? Ist dieser der beiden überflüssig?

In der Tat, kann man das output = "'" (aus Zeile 31) auch an den Beginn der Schleife ziehen. Dann kann es vor der Schleife auch weg (Zeile 16).
Eine Zeile, die die Ausführung des Codes merklich beschleunigen kann ist:

Application.ScreenUpdating = False

Am Ende der Prozedur dann wieder auf True stellen. Dies bewirkt genau wonach es klingt. Sonst wird die Anzeige immer aktualisiert beim Schreiben von Werten.

Schreibt man beispielsweise Werte in ein Tabellenblatt in dem auch Formel sind, kann es sich lohnen dies ebenfalls in die Sub zu schreiben:

Application.Calculation = xlCalculationManual

Dadurch wird die Berechnung auf Manuell gesetzt. Auch hier kann man dann am Ende wieder auf xlCalculationAutomatic setzen.