Excel wie komme ich auf die beste Kombination von zwei Werten um auf einen Wert zu kommen
Hallo,
ich bräuchte eine Excel Tabelle mit einer Funktion die ich nicht weiss wie sie geht.
Also ich habe 20 Werte von ca.100-350. Ich möchte wissen welche der 20 Werte ergeben in Kombination 500 oder wenn keine Kombination 500 ergibt 501 bzw 502 usw. Eben welche ist die beste Kombination für genau 500 bzw. höher.
Hat einer eine Idee ob ich das mit Excel machen kann und wenn dann mit welcher Formel?
Danke
6 Antworten
Das geht m. E. nicht mit Excel
es geht auch mit (Matrix-)Formeln, komischerweise taucht das jetzt innerhalb von 5 Tagen zum 3. mal auf (nicht exakt gleich, aber doch nah verwandt):
In der einfachsten Form ergibt sich die Zeile so:
{=VERGLEICH(KKLEINSTE(ABS(A:A+B:B-15);ZEILE(1:1));ABS(A:A+B:B-15);0)}
das liefert aber für gleichlautende Differenzen mehrmals die oberste Zeile. Es ist deshalb sinnvoll, eine "individualisierungs-Variable" einzubauen:
{=VERGLEICH(KKLEINSTE(ABS(A:A+B:B-15)+1/10^7-ZEILE(A:A)/10^14;ZEILE(1:1));ABS(A:A+B:B-15)+1/10^7-ZEILE(A:A)/10^14;0)}
Das winzige Inkrement von 1/10^7-Zeile(A:A)/10^14 individualisiert die Wertepaare und beeinflusst die realen Werte >10^-7 nicht. Da die Zahl der Zeilen knapp über 10^6 liegt, reicht die Exponentendifferenz von 7 aus. bei gleichen Wertepaaren kommen die unteren zuoberst (wegen Minus Zeile(A:A)... , könnte man sicher auch umdrehen! (nicht ausprobiert, könnte sogar einfacher sein, weil dann der Addend 1/10^7 entfallen könnte? probier ich jetzt nicht mehr!)
will man nur begrenzte Zeilenzahlen auswerten, kann man natürlich auch so formulieren:
{=VERGLEICH(KKLEINSTE(ABS(A$1:A$999+B$1:B$999-15)+1/10^7-ZEILE(A$1:A$999)/10^14;ZEILE(1:1));ABS(A$1:A$999+B$1:B$999-15)+1/10^7-ZEILE(A$1:A$999)/10^14;0)}
Die Formeln kann man runter-, aber nicht hochkopieren (Zeile<1:1 gibts ja nicht), sehr wohl aber hochschieben, wenn zu weit unten platziert. Die Spalten können als ganze verschoben werden und müssen am Ende nicht nebeneinander liegen.
In den Spalten dürfen KEINE TEXTE stehen! (drauf "gepappte" Textfelder oder andere Objekte sowie Kommentare stören nicht,
Achtung: Dies ist eine Matrixformel!
Die geschweiften {Klammern} NICHT miteingeben, sondern die Formel anstatt mit einfachem Enter mit der Kombination Strg+Shift+Enter abschließen (nach jeder Neubearbeitung wieder!). Die {Klammern} entstehen dann automatisch.
Schreibtechnisch nimmst Du am besten die rechte Strg- und Umschalttaste, dann gehts mit einer Hand und lässt sich auch leichter merken!
so, und nach einem Tag Gehirntraining stelle ich fest, Dass mit
Kombination zweier Werte
nicht ein Wertepaar, sondern die Kombination aus einer Menge gemeint ist.
Ich war durch eine ähnliche Aufgabe so auf ein Paar fixiert, dass mir da gar keine Zweifel gekommen sind! Es geht also um Permutationen...
Shit happens, nur leider frustrierend häufig...
In welcher Form würdest du ein Ergebnis erwarten? In einer einzelnen Zelle könnte das gar nicht dargestellt werden, also käme allerhöchstens eine Matrixformel infrage.
Ich mache es aber kurz: mir ist keine solche Formel bekannt. Wenn du dich aber eini bisschen mit VBA auseinandersetzt, kannst du dir so eine Funktion selber basteln.
Ich habe einmal so etwas ähnliches programmiert. Eventuell kannst du dir das für dich Nötige davon abgucken:
Public Function FindeSummanden(oBereich As Range, iSumme As Integer) As String
'
' Findet die Summanden, die zu einer Summe führen
'
' 29.05.2012 TH
'
Dim iaOriginalwerte()
Dim i
Dim oZelle
ReDim iaOriginalwerte(1 To oBereich.Cells.Count)
' Ich habe Arrays lieber
i = 1
For Each oZelle In oBereich
iaOriginalwerte(i) = oZelle.Value
i = i + 1
Next
FindeSummanden = FindeSummandenIntern(iaOriginalwerte, iSumme)
End Function
Private Function FindeSummandenIntern(iaSummanden, iSumme) As String
Dim i
Dim iSumTemp
Dim iaTemp()
'debug'Call ArrayPrint(iaSummanden)
FindeSummandenIntern = "Nicht gefunden"
For i = LBound(iaSummanden) To UBound(iaSummanden)
If FindeSummandenIntern = "Nicht gefunden" Then
iSumTemp = ArraySum(iaSummanden)
If iSumTemp = iSumme Then
FindeSummandenIntern = ArrayPrint(iaSummanden)
Else
If iSumTemp > iSumme Then
iaTemp = iaSummanden
Call ArrayRemoveElement(iaTemp, i)
FindeSummandenIntern = FindeSummandenIntern(iaTemp, iSumme)
End If
End If
End If
Next
End Function
Private Sub ArrayRemoveElement(varArray, iPosToRemove)
' Löscht ein Arrayelement an einer bestimmten Position
Dim i, j
Dim varArrayNew()
If (iPosToRemove >= LBound(varArray)) And _
(iPosToRemove <= UBound(varArray)) Then
ReDim varArrayNew(LBound(varArray) To UBound(varArray) - 1)
j = LBound(varArrayNew)
For i = LBound(varArray) To UBound(varArray)
If i <> iPosToRemove Then
varArrayNew(j) = varArray(i)
j = j + 1
End If
Next
varArray = varArrayNew
End If
End Sub
Private Function ArraySum(nArray) As Variant
' Summiert die Werte eines numerischen Arrays
Dim i
ArraySum = 0
For i = LBound(nArray) To UBound(nArray)
ArraySum = ArraySum + nArray(i)
Next
End Function
Private Function ArrayPrint(varArray) As String
'Gibt den Arrayinhalt ins Debugfenster aus
Dim sDebug
Dim i
ArrayPrint = ""
For i = LBound(varArray) To UBound(varArray)
ArrayPrint = ArrayPrint & varArray(i) & ", "
Next
If Right(ArrayPrint, 2) = ", " Then _
ArrayPrint = Mid(ArrayPrint, 1, Len(ArrayPrint) - 2)
'debug' ArrayPrint = ArrayPrint & "Summe: " & ArraySum(varArray)
Debug.Print ArrayPrint
End Function
Anwendungsbeispiel:
A12: =PERSONAL.XLSB!FindeSummanden(A1:A9;500)
#----------#
| | A |
#----------#
| 1 | 100 |
| 2 | 120 |
| 3 | 50 |
| 4 | 80 |
| 5 | 150 |
| 6 | 90 |
| 7 | 65 |
| 8 | 78 |
| 9 | 112 |
| 10 | |
| 11 | |
| 12 | 100, 120, 90, 78, 112 |
#---------#ich hab für die Entwicklung meiner Formel wahrscheinlich genausolang gebraucht wie Du fürs Makro... ;-)
Das Makro war eigentlich eine Fingerübung für Rekursionen. Aber wenn man sich einmal die Mühe gemacht hat, dann kann man es immer wieder anbieten, denn die Frage nach Kombinationssuche tritt öfter mal auf.
offensichtlich Kopf- bzw. Fingerübung für den "Herrn Vergeblich"!
Neuer Anlauf:
Eine Einzelformel dafür hab ich nicht gefunden (war genau genommen auch nicht gefragt), aber ein xl-System:
Deine 20 Werte nach A2:A21, dann streigend sortierren.
in B1 die Formel:
=KGrösste($A2:$A21;Spalte(B1)-1)
B1 nach rechts kopieren bis U1. das kopiert Deine Werte in fallender Ordnung.
In B2 dann die Formel:
=WENN($A2+B$1>=500;$A2+B$1;999)
Du siehst jetzt schon die Grenzlinie aller Wertekombinationen, die >=500 ergeben. Weitere Auswertung: in zB B28 die Formel:
=Min(B2:B21)
rüberziehen bis U28.
nach V28 die Formel:
=Min(B28:U28)
Die Spalte des Minimums findert man mit
=VERGLEICH(Min(B28:U28);B28:U28;0) in zB W28
Geleiches Vorgehen für Zeilen:
W2: =Min(B2:U2) runterziehen bis W21
W24: = Min(W2:W21)
Zeile in W25, Fo:
=VERGLEICH(Min(W1:W21);W1:W21;0)
W27: =ADRESSE(W25;W28)
Achtung: Jede Zahl taucht zwangsläufig 2x auf, da zB 210+291=291+210. Eine Zahl auf der Diagonalen l.u.- r.o. könnte theoretisch das Minimum bilden , wäre aber dann kein Wertepaar, sondern die doppelte Verwendung desselben Werts (lt. Fragestellung nicht zulässig, da kein Wertepaar)
Was von diesem System nicht erfasst wird, sind dreier-Kombinationen. Es sind welche denkbar, die näher an 500 liegen oder =500 sind. Eine 3-D-Matric ist kaum darstellbar, evtl per Pivot-Tabelle, aber recht kompliziert.
ich hoffe, die AW nützt Dir nach so langer Zeit noch was!
späte Korrektur, nur damit nichts Falsches stehenbleibt für ggf spätere Interessenten:
Die Spaltennummer des Minimums findet man mit
=VERGLEICH(Min(B28:U28);A28:U28;0) in zB W28
den Spaltenbuchstaben könnte man erhalten mit
=Zeichen(VERGLEICH(Min(B28:U28);A28:U28;0)+64)
aber Adresse braucht nicht den Buchstaben, sondern Zeilen-/SpaltenNUMMER)
ich hab übrigens bei meinen mittels der Formel
=100+GANZZAHL(ZUFALLSZAHL()*250)
erzeugten Werten (funktioniert auch im alten xl, im neuen gäbe es auch Zufallsbereich()) noch zwei weitere Dreierkombinationen sowie eine Viererkombination mit genau 500 gefunden sowie etliche mit 501, und ich hab keine Garantie für Vollständigkeit!
Dies müsste man auch im Makro von Suboptimierer berücksichtigen durch Einführung weiterer Schleifen mit den Variablen k,l,(evtl noch m), was zu potenzierten Laufzeiten führen würde, aber ginge - im Ggs zu meinem Formelsystem.
Für eine Laufzeitreduzierung müsste dann wieder eine Prüfabfrage rein, dass nach der ersten Zahl über 500 die Schleife des jeweiligen Parameters verlassen wird und die nächsthöhere ihre Arbeit fortsetzt (prinzipiell so):
Werte steigend ordnen,
Schleifen verschachteln und dann
if Ergebnisvariable >500 then [Aktion: Ergebnisvar. festhalten]: Exit For
Klingt nach einer Aufgabe für den Solver von Excel.
Ist recht umfangreich zu erklären, daher als Empfehlung lieber gleich so:
https://www.google.de/#q=excel+solver+anleitung
Da sind einige brauchbare dabei.
wäre vllt einfacher gewesen als meine Formel zu entwickeln!
hat mich aber einen großen Schritt weiter gebracht!
ach so, ich vergaß zu erwähnen, dass das die Zeilennummer des Wertepaares liefert, die Werte selbst kriegst du mit
{=Index(A:A;obige Formel oder Verweis drauf)} bzw mit
{=index(B:B;obige Formel oder Verweis drauf)} oder mit
{=indirekt("A"&obige Formel oder Verweis drauf)} bzw mit
{=inirekt("B"&obige Formel oder Verweis drauf)}
indirekt ist unempfindlich gegen die Art der Formel, während index meckern könnte, wenn die Bereichsgrößen nicht zusammenpassen!
Habs grad noch ausprobiert, meckert nicht!