Bestmögliche Kombination in Excel erstellen
Schritt-für-Schritt-Anleitung
Um die bestmögliche Kombination von Werten in Excel zu finden, kannst du folgende Schritte ausführen:
- Daten vorbereiten: Erstelle eine Tabelle mit den Werten in Spalte B. Setze das Ziel (z.B. 50) in Zelle G1.
- Makro erstellen: Öffne den VBA-Editor (ALT + F11) und füge ein neues Modul hinzu.
- Makro-Code einfügen: Nutze den folgenden VBA-Code, um die bestmögliche Kombination zu finden:
Sub Kombinatorik()
Dim x&, i&, z&
Dim a, b
Dim s#, bestS#
Dim AnzahlVariablen&, AnzahlKombinationen&
Dim ziel#, zDelta#
Dim schonDa As Boolean
a = Range("B2:B14") ' Werte anpassen
b = a
AnzahlVariablen = UBound(a)
AnzahlKombinationen = 2 ^ AnzahlVariablen
ziel = Range("G1")
zDelta = ziel * 0.95 ' 5% unter Ziel
Range("L2").CurrentRegion.ClearContents
z = 1
For i = 0 To AnzahlKombinationen - 1
s = 0
For x = 0 To AnzahlVariablen - 1
b(x + 1, 1) = (-((i And (2 ^ x)) > 0)) * a(x + 1, 1)
s = s + b(x + 1, 1)
Next
If (s > zDelta And s <= ziel) Or s = ziel Then
Range("L2").Offset(, z).Resize(AnzahlVariablen) = b
Range("L1").Offset(, z).Value = s
If s = ziel And (Not schonDa) Then
Range("D2").Resize(AnzahlVariablen) = b
schonDa = True
End If
bestS = s
z = z + 1
End If
Next
End Sub
- Makro ausführen: Schließe den VBA-Editor und führe das Makro aus, um die besten Kombinationen zu finden.
Häufige Fehler und Lösungen
- Fehler: "Typ nicht definiert": Stelle sicher, dass alle Variablen korrekt deklariert sind.
- Fehler: Keine Ergebnisse: Überprüfe, ob die Werte in der richtigen Spalte (B) und das Ziel in G1 steht.
- Fehler: Makro kann nicht ausgeführt werden: Aktiviere die Makros in den Excel-Optionen.
Alternative Methoden
Falls du kein Makro nutzen möchtest, kannst du auch folgende Methoden ausprobieren:
- Solver-Add-In: Excel bietet das Solver-Add-In, mit dem du Optimierungsprobleme lösen kannst. Du kannst es aktivieren und die Zielwerte und Einschränkungen definieren.
- Power Query: Mit Power Query kannst du Daten transformieren und analysieren, um die bestmöglichen Kombinationen zu finden.
Praktische Beispiele
Beispiel 1: Wenn du die Werte 5, 10, 15, 20 und 25 hast und 50 erreichen möchtest, wird der VBA-Code die Kombinationen wie (25, 20, 5) oder (15, 15, 20) ausgeben.
Beispiel 2: Mit dem Solver kannst du die gleiche Tabelle verwenden und die Zielzelle auf 50 setzen, um die Kombinationen zu optimieren.
Tipps für Profis
- Daten dynamisch gestalten: Verwende dynamische Bereiche in deinem Makro, um die Größe des Datenbereichs automatisch anzupassen.
- Ergebnisse formatieren: Nutze bedingte Formatierungen, um die besten Kombinationen hervorzuheben.
- Fehlerbehandlung einfügen: Implementiere eine Fehlerbehandlung im VBA-Code, um unerwartete Ergebnisse zu vermeiden.
FAQ: Häufige Fragen
1. Wie viele Werte kann ich in die Tabelle eingeben?
Du kannst eine beliebige Anzahl von Werten eingeben, solange dein Computer genügend Ressourcen hat.
2. Funktioniert das in allen Excel-Versionen?
Der VBA-Code sollte in Excel 2010 und späteren Versionen funktionieren. Achte darauf, dass Makros aktiviert sind.
3. Was ist, wenn die Summe nie 50 erreicht?
Der Code gibt die nächstgelegene Kombination aus, die unter 50 bleibt, falls kein exakter Treffer möglich ist.