Summenbildung mit Zielwert

Forumthread: Summenbildung mit Zielwert

10.10.2019 16:23:11

Daniel

Ich habe folgendes Excelproblem und suche eine möglichst elegante Lösung dafür.
Die Zellen A1:A100 enthalten unterschiedliche Zahlen, die Zellen B1 und B2 enthalten jeweils einen Sollwert.
Jetzt geht es darum, den Bereich A1:A100 in zwei Bereiche so aufzuteilen, dass die Summe der enthaltenen Zahlen für Bereich 1 möglichst nah an Zielwert 1 und für Bereich 2 möglichst nah an Zielwert 2 liegt. Dabei sind sowohl Abweichungen nach oben als auch nach unten erlaubt. Wichtig ist nur, dass der Betrag der Abweichung jeweils möglichst gering ist.
Die beiden Bereiche müssen dabei nicht aus untereinanderliegenden Zellen bestehen. Wichtig ist nur, dass alle Zellen jeweils einmal berücksichtigt werden (entweder im Bereich 1 oder 2).
Ob die beiden dabei ausgewählten Bereiche farblich markiert, die enthaltenen Zahlen in anderen Spalten ausgegeben, oder ob die Bereiche über eine andere Lösung identifiziert werden ist für die Lösung egal. Es geht lediglich um die Identifikation der Bereiche.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

per Formel geht das weder elegant noch überhaupt

10.10.2019 17:05:45

Hi,
wäre eigentlich eine SOLVER-Lösung; bei 100 Zahlen (schon weit früher) gibt der aber den Geist auf.
WF
vielleicht VBA ?

Die Aufgabenformulierung ist unsinnig, ...

10.10.2019 17:32:09

lupo1

... denn nach ihr soll (als dritte unausgesprochene Eigenschaft)
=SUMME(A1:A100) möglichst nah an =SUMME(B1:B2) sein.
Da jedoch alle 102 Zahlen Eingabewerte sind und keine Resultate oder mit sonstigen Eigenschaften, ist das weder gewollt, noch gekonnt.

AW: Summenbildung mit Zielwert

10.10.2019 17:33:52

Daniel

ich sage mal: das ist mit Excel nicht möglich.
im Prinzip müsstest du alle möglichen Gruppeneinteilungen durchtesten und das sind 2^100 Möglichkeiten.
du müsstest hier schon eine intelligente Suchstrategie, welche nicht auf das durchtesten aller Möglichkeiten angewiesen ist, anwenden.
Sowas wirst du nicht für lau bekommen.
Gruß Damiel

AW: Summenbildung mit Zielwert

10.10.2019 17:57:55

ChrisL

Hi Daniel
2^100 =
10715086071862673209484250490600018105614048117055336074437503883703510511249361224931983788156958581275946729175531468251871452856923140435984577574698574803934567774824230985421074605062371141877954182153046474983581941267398767559165543946077062914571196477686542167660429831652624386837205668069376
Zusätzliche Spalte =ZUFALLSZAHL()
SUMMEWENN die Zufallszahl kleiner 0.5 ist.
(quasi ein zufälliges WAHR/FALSCH)
Wenn das Ergebnis halbwegs zufriedenstellend ist, speicherst du dir die Lösung und sonst drückst du die F-Taste zur Aktualisierung. Was ich hier vorschlage ist ein kleiner Zufallsgenerator (den man ggf. per VBA auch noch automatisieren kann).
cu
Chris

AW: Summenbildung mit Zielwert

10.10.2019 18:33:24

Daniel

HI Chris
das wäre meiner Einschätzung nach der einzig mit Excel machbare Weg, sich einem brauchbaren Ergebnis anzunähern, auch wenn es vielleicht nicht zwingend das bestmögliche Ergebnis liefert.
da man jedoch eine Durchläufe benötigen wird, sollte man das ganze VBA unterstützen.
Gruß Daniel

AW: Summenbildung mit Zielwert

10.10.2019 18:41:19

Daniel

Hier mal ein Makro dazu (vollständige Markolösung)

Sub test()
Dim arrA
Dim arrB
Dim GruppenSumme(1 To 2)
Dim Ziel(1 To 2) As Double
Dim Grp As Long
Dim Erg
Dim Abw As Double
Dim AbwMin As Double
Dim z As Long, i As Long
arrA = Range("A1:A100").Value
ReDim arrB(1 To UBound(arrA))
AbwMin = 10 ^ 300
Ziel(1) = Range("B1").Value
Ziel(2) = Range("B2").Value
For i = 1 To 5000
With WorksheetFunction
GruppenSumme(1) = 0
GruppenSumme(2) = 0
For z = 1 To UBound(arrA, 1)
Grp = .RandBetween(1, 2)
arrB(z) = Grp
GruppenSumme(Grp) = GruppenSumme(Grp) + arrA(z, 1)
Next
Abw = Abs(.Min(GruppenSumme) - .Min(Ziel)) + Abs(.Max(GruppenSumme) - .Max(Ziel))
If Abw

Infobox / Tutorial

Zielwerte in Excel erreichen: Summenbildung optimieren

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um die Summenbildung in Excel so zu optimieren, dass die Werte in den Zellen A1:A100 möglichst nah an den Zielwerten in B1 und B2 liegen, kannst du folgende Schritte befolgen:

Zahlen eingeben: Füge deine Zahlen in den Bereich A1:A100 ein und definiere die Zielwerte in den Zellen B1 und B2.
Zufallszahlen generieren: In einer neuen Spalte (z.B. C1) kannst du die Formel =ZUFALLSZAHL() verwenden, um eine Zufallszahl zwischen 0 und 1 zu generieren. Ziehe die Formel bis zur Zelle C100.
Gruppierung mittels SUMMEWENN: Verwende die Funktion =SUMMEWENN(C1:C100; "<0,5"; A1:A100) in einer Zelle (z.B. D1), um die Summe der Zahlen für eine Gruppe zu berechnen, deren Zufallszahl kleiner als 0,5 ist.
Iterativer Ansatz: Drücke F9, um die Zufallszahlen zu aktualisieren und beobachte, wie sich die Summen ändern.
VBA-Makro (optional): Wenn du eine automatisierte Lösung bevorzugst, kannst du das bereitgestellte VBA-Makro verwenden, um mehrere Durchläufe zu machen und die besten Ergebnisse zu speichern.

Häufige Fehler und Lösungen

Fehler: Die Summen sind nicht nah genug an den Zielwerten.
- Lösung: Überprüfe die Zufallszahlen oder erhöhe die Anzahl der Iterationen im VBA-Makro.
Fehler: Das Makro funktioniert nicht.
- Lösung: Stelle sicher, dass du in der Entwicklertools-Registerkarte den Makroschutz deaktiviert hast und die richtige Excel-Version verwendest.

Alternative Methoden

Eine weitere Methode zur Erreichung der Zielwerte wäre die Verwendung des Solver-Add-Ins. Hierbei kannst du die Summe der ausgewählten Zellen optimieren, um die Zielwerte zu erreichen. Beachte jedoch, dass der Solver bei sehr großen Datenmengen (z.B. über 100 Werte) an seine Grenzen stößt und möglicherweise nicht die besten Ergebnisse liefert.

Praktische Beispiele

Angenommen, du hast folgende Zahlen in A1:A10:

|   A   |
|-------|
|  10   |
|  20   |
|  30   |
|  40   |
|  50   |
|  60   |
|  70   |
|  80   |
|  90   |
|  100  |

Setze die Zielwerte in B1 (160) und B2 (200).

Mit der oben beschriebenen Methode kannst du schnell die besten Gruppierungen finden, die den Zielwerten am nächsten kommen.

Tipps für Profis

Verwende VBA: Automatisiere den Prozess mit VBA, um mehrere Kombinationen effizienter zu testen.
Optimiere die Zufallszahlen: Du kannst die Wahrscheinlichkeit der Gruppeneinteilung durch Anpassung der Zufallszahl oder durch gezielte Gewichtung beeinflussen.
Visualisierung: Nutze bedingte Formatierungen, um die Zellen farblich hervorzuheben, die Teil der optimalen Lösung sind.

FAQ: Häufige Fragen

1. Kann ich die Lösung ohne VBA umsetzen? Ja, du kannst die Lösung auch nur mit Excel-Formeln umsetzen, aber VBA macht den Prozess deutlich effizienter.

2. Funktioniert dies in allen Excel-Versionen? Ja, die beschriebenen Methoden funktionieren in Excel 2010 und späteren Versionen, wobei VBA einige zusätzliche Anforderungen haben kann.

Beliebteste Forumthreads (12 Monate)

Forumthread: Summenbildung mit Zielwert

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Zielwerte in Excel erreichen: Summenbildung optimieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Summenbildung mit Zielwert

Forumthread: Summenbildung mit Zielwert

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Zielwerte in Excel erreichen: Summenbildung optimieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread