Microsoft Excel

Herbers Excel/VBA-Archiv

Summenbildung mit Zielwert


Betrifft: Summenbildung mit Zielwert von: Daniel
Geschrieben am: 10.10.2019 16:23:11

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.

  

Betrifft: per Formel geht das weder elegant noch überhaupt von: WF
Geschrieben am: 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 ?


  

Betrifft: Die Aufgabenformulierung ist unsinnig, ... von: lupo1
Geschrieben am: 10.10.2019 17:32:09

... 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.


  

Betrifft: AW: Summenbildung mit Zielwert von: Daniel
Geschrieben am: 10.10.2019 17:33:52

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


  

Betrifft: AW: Summenbildung mit Zielwert von: ChrisL
Geschrieben am: 10.10.2019 17:57:55

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


  

Betrifft: AW: Summenbildung mit Zielwert von: Daniel
Geschrieben am: 10.10.2019 18:33:24

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


  

Betrifft: AW: Summenbildung mit Zielwert von: Daniel
Geschrieben am: 10.10.2019 18:41:19

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 < AbwMin Then
            AbwMin = Abw
            Erg = arrB
        End If
    End With
Next

Cells(1, 3).Resize(UBound(Erg)).Value = WorksheetFunction.Transpose(Erg)

End Sub



Beiträge aus dem Excel-Forum zum Thema "Summenbildung mit Zielwert"