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.
28.09.2019, 14:21 (Dieser Beitrag wurde zuletzt bearbeitet: 28.09.2019, 14:21 von 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.
28.09.2019, 17:05 (Dieser Beitrag wurde zuletzt bearbeitet: 28.09.2019, 17:05 von Ego.)
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?
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
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
. \\\|/// Hoffe, geholfen zu haben. ( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
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
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
ersetzt, dürfen es einige Zellen mehr sein, da jetzt nicht mehr alle Kombinationen ausgewertet werden.
helmut
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität. Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.
