Summe errechnen die möglichst nah am Zielwert liegt

[XBU Philippe]

Hallo,

ich suche Hilfe bei einer Formel, zu der ich nichts Passendes im Internet finden konnte, vielleicht könnt ihr mir ja helfen.

Ich suche ein Formel, bei der ich aus einer Reihe von Werten (ich habe 6 Werte in Zellen horizontal nebeneinander stehen) nur DIEJENIGEN Zellen zusammenrechnen will, bei denen die Summe am nächsten bei 60 liegt. Ich habe es anfangs mit =MIN(SUMME(D2:H2);60) versucht, allerdings kommt er dann immer auf 60 Punkte.

Mein Beispiel zeigt, dass das beste Ergebnis aus den 6 Zellen 59 ist (10+48+1), allerdings rechnet er mit meiner Formel immer 60 aus.

Wie kann ich das denn richtig programmieren?

[Sigi.21]

Hallo Phillippe,

das hier "SUMME(D2:H2)" ergibt doch 67 (wenn dein Foto stimmt). Min sind halt 60.

Gruß
Sigi

[Mase]

Hi,

bei Deinem Beispiel gibt es 3^3 mögliche Kombinationen; 27 Ergebnisse + die drei Zahlen selbst. (Könnte ja sein Du hast schon 59)
Von jedem Ergebnis 60 abgezogen, dort dann den Kleinsten Wert nehmen.

Hinweis:
Zahlen Kleiner 0 mit -1 Multiplizieren.

[Ego]

Hallo X...,

A. zu

Wie kann ich das denn richtig programmieren?

Das ist die richtige Frage. Ich würde es als UDF (=user defined function; Funktion als Makro geschrieben) programmieren.
Ist ein Makro für dich akzeptabel?

B. Wenn ja, noch zwei Fragen:

1. Wenn es Kombinationen mit der Summe 59 und Kombinationen mit der Summe 61 gibt sollte dann eine bevorzugt angezeigt werden?
Bitte mit Begründung.

2. Hast du eine Idee wie du die Funktion nennen willst?

[schauan]

Hi,

auf excelformeln.de gibt es eine Formellösung,

Kombinationen von Zweiern, Dreiern und Vierern auflisten

die Dir z.B. die 15 möglichen Zweier, 20 Dreier und 15 Vierer aus Deinen 6 Zahlen auflistet. Das kann man für die Fünfer-Kombinationen erweitern. Einer und Sechser sind es ja eher nur wenige, die muss man nicht unbedingt errechnen
Allerdings würde ich auch eher auf eine VBA-Funktion gehen, Du willst sicher keine ellenlange Liste

[Sulprobil]

Hallo,

Da hatte Michael Schwimmer einmal eine gute Lösung programmiert, die auf einem Programmierwettbewerb von MrExcel basierte.

Sie ist auf meiner Webseite frei herunterladbar, ohne jede Gewähr (aber ich verwende einen aktuellen Virenscanner):
http://sulprobil.com/Get_it_done/IT/Exce...oblem.html

Viele Grüße,
Bernd P

[Ego]

Hallo X...,

ind der Anlage einmal mit der UDF "NaheBei".

Achtung! Da das Makro gnadenlos alle Kombinationen der Zellen auswertet, sollte man nicht eine zu grosse Anzahl von Zellen nutzen.
Ansonsten kann man Jahrzehnte warten bis der Wert ermittelt wird.

Und hier der Code:

Code:

Function NaheBei(Summe As Double, Zellen As Range) As Double
Dim AnzZ As Long
Dim AktZ As Long
Dim ArrZ As Variant
Dim AktO As Double
Dim ArrW() As Double
Dim Wert As Range
ArrZ = Zellen.Value
AnzZ = Zellen.Cells.Count
ReDim ArrW(1 To AnzZ)
AktZ = 0
AktO = 2 ^ 10
For Each Wert In Zellen
    AktZ = AktZ + 1
    ArrW(AktZ) = Wert.Value
Next Wert
Call Kombi(ArrW, AktO, Summe, 1, 0)
NaheBei = AktO
End Function

Sub Kombi(ByRef ArrW, ByRef AktO, ByRef Summe, ByVal PPos As Long, ByVal PSum As Double)
Dim IPos As Long
For IPos = PPos To UBound(ArrW, 1)
    PSum = PSum + ArrW(IPos)
        If Abs(PSum - Summe) < Abs(AktO - Summe) Then
            AktO = PSum
        End If
        If IPos < UBound(ArrW, 1) Then
            Call Kombi(ArrW, AktO, Summe, IPos + 1, PSum)
        End If
    PSum = PSum - ArrW(IPos)
Next IPos
End Sub

[Ego]

Hallo X...,

wenn du im Code die Zeile

If IPos < UBound(ArrW, 1) Then

durch

If IPos < UBound(ArrW, 1) And PSum < Summe Then

ersetzt, dürfen es einige Zellen mehr sein, da jetzt nicht mehr alle Kombinationen ausgewertet werden.

[schauan]

Hallöchen,

so wie es ausschaut soll "nahe bei 60" die 60 auch einschließen. Müsste da nicht auch

If Abs(PSum - Summe) < Abs(AktO - Summe) Then

dann

If Abs(PSum - Summe) <= Abs(AktO - Summe) Then

sein?

[Ego]

Hallo Andre,

nein, da 0 < 1 ist.