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?
Hallo Phillippe,
das hier "SUMME(D2:H2)" ergibt doch 67 (wenn dein Foto stimmt). Min sind halt 60.
Gruß
Sigi
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.
Hallo X...,
A. zu
Zitat: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?
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
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
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
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.
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?
Hallo Andre,
nein, da 0 < 1 ist.