Microsoft Excel

Herbers Excel/VBA-Archiv

Hiefe für eine bestimmte Formel

Betrifft: Hiefe für eine bestimmte Formel von: Rudi
Geschrieben am: 21.08.2014 09:44:38

Hallo,

benötige Hilfe ob so etwas überhaupt geht?

Ich habe in einer Zeile mehrere Zahlen. Von diesen Zahlen sollen davon
zwei Zahlen einer bestimmten vorgegeben Zahl am nächsten kommen.

Z.B. 10 30 15 5 1 usw.
die vorgegebene Zahl ist 50

die Zahlen 30 + 15 in Summe = 45 kommen der Zahl 50 am nächsten.

Hat hier jemand einen Lösungsvorschlag mit welcher Formel man dies lösen kann

  

Betrifft: AW: Hiefe für eine bestimmte Formel von: CitizenX
Geschrieben am: 21.08.2014 10:39:47

Moin,

füge in ein allgemeines Modul folgedes script ein:

Option Explicit

Public Function getMaxA(Bereich As Range, Wert As Long) As Long
    Dim oWert, myArr, n&
    myArr = Split(Bereich)
    For Each oWert In myArr
        For n = 0 To UBound(myArr)
        If CLng(oWert) + CLng(myArr(n)) <= Wert Then
        If getMaxA < CLng(oWert) + CLng(myArr(n)) Then getMaxA = CLng(oWert) + CLng(myArr(n))
        End If
        Next
    Next
End Function
Die Funktion rufst du dann in deiner Tabelle auf:
=getMaxA(A1;B1)

in A1 befindet sich dein Zahlenstring
in B1 dein Max Wert

VG
Steffen


  

Betrifft: Ich glaube kaum, dass das die Lösung ist, ... von: Luc:-?
Geschrieben am: 21.08.2014 11:27:00

Steffen,
denn …
1. stand in der Anfrage Zeile nicht Zelle und …
2. wurden die 2 Zahlen gesucht, deren Summe der Vorgabe am nächsten kommt, und wohl eher nicht diese Summe.
Ich schlage deshalb (vorerst und) auf die Schnelle Folgendes vor, Rudi:

 ABCDE
110301551
2Hilfsmatrix    
300000
400100
501000
600000
700000
8Ergebnisvektor
9 3015  
10Formeln    
11A3:E7: {=--(ABS(50-MTRANS(A1:E1)-A1:E1)=MIN(ABS(50-MTRANS(A1:E1)-A1:E1)))}
12A9:E9: {=WENN(A1:E1*TEILERGEBNIS(9;INDIREKT(WECHSELN("Z3S#:Z7S#";"#";SPALTE(A1:E1));0))=
13                 0;"";A1:E1*TEILERGEBNIS(9;INDIREKT(WECHSELN("Z3S#:Z7S#";"#";SPALTE(A1:E1));0)))}

Gruß, Luc :-?


  

Betrifft: AW: Hiefe für eine bestimmte Formel von: Rudi
Geschrieben am: 21.08.2014 11:42:06

Danke für die schnelle Antwort,

bin hier zum ersten mal in einem Forum.
Steffen Dein Vorschlag ist nicht das was ich wollte, hier kommt nicht das Ergebnis raus was benötigt wird.
Hier ein Beispiel ohne Formel



  

Betrifft: angepasst von: Erich G.
Geschrieben am: 21.08.2014 11:51:43

Hi Rudi,
hier mein Vorschlag mal nach deiner neuen Vorgabe umgebaut:

 ABCDEF
1Zielwert29    
2      
3Zahlen10301551
4      
5Paar301   
6Kontrollsumme31    

Formeln der Tabelle
ZelleFormel
B6=B5+C5
Sub PaarZiel2()
   Dim arZ, dZ As Double, ii As Long, jj As Long, im As Long, jm As Long, dm As Double

   dZ = Cells(1, 2)
   arZ = Cells(3, 2).Resize(, Cells(3, Columns.Count).End(xlToLeft).Column - 1)
   dm = 9 ^ 99
   
   For ii = 1 To UBound(arZ, 2) - 1
      For jj = ii + 1 To UBound(arZ, 2)
         If Abs(arZ(1, ii) + arZ(1, jj) - dZ) < dm Then
            dm = Abs(arZ(1, ii) + arZ(1, jj) - dZ)
            im = ii
            jm = jj
         End If
      Next jj
   Next ii
   Cells(5, 2) = arZ(1, im)
   Cells(5, 3) = arZ(1, jm)
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: angepasst von: Rudi
Geschrieben am: 21.08.2014 12:26:33

Hallo Erich,

Danke, sieht auf den ersten Blick gut aus. Werde diese demnächst ausprobieren, wahrscheinlich erst morgen. Werde dann Rückmeldung geben.

Rudi


  

Betrifft: als Funktion von: Erich G.
Geschrieben am: 21.08.2014 13:02:10

Hi Rudi,
wenn du das lieber als Funktion (im Tabellenblatt als UDF nutzbar) haben möchtest:

Function PaarZiel(rngBer As Range, dVglW As Double)
   Dim arZ, ii As Long, jj As Long, im As Long, jm As Long, dm As Double

   arZ = rngBer.Value
   dm = 9 ^ 99
   
   For ii = 1 To UBound(arZ, 2) - 1
      For jj = ii + 1 To UBound(arZ, 2)
         If Abs(arZ(1, ii) + arZ(1, jj) - dVglW) < dm Then
            dm = Abs(arZ(1, ii) + arZ(1, jj) - dVglW)
            im = ii
            jm = jj
         End If
      Next jj
   Next ii
   PaarZiel = Array(arZ(1, im), arZ(1, jm))
End Function
Die Fkt. gibt zwei Werte zurück - eben das Zahlenpaar. In der Tebelle sieht das dann so aus,
wobei die Zellen B5:C5 bei Eingabe der Matrixformel zusammen markiert sein müssen:

 ABCDEF
1Zielwert32,9    
2      
3Zahlen10301551
4      
5Paar301   
6Kontrollsumme31    

Formeln der Tabelle
ZelleFormel
B5{=PaarZiel(B3:F3;B1)}
C5{=PaarZiel(B3:F3;B1)}
B6=B5+C5
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: als Funktion von: Rudi
Geschrieben am: 21.08.2014 17:40:06

Hallo Erich,

habe jetzt alles eingebaut. Allerdings ist bei mir noch ein Fehler.
Habe in B5 und C5 jeweils den gleichen Wert.
Kannst Du mir sagen was ich hier falsch gemacht habe?



Rudi


  

Betrifft: ...genau gelesen ? von: robert
Geschrieben am: 21.08.2014 17:52:24

...wobei die Zellen B5:C5 bei Eingabe der Matrixformel zusammen markiert sein müssen:

Gruß
robert


  

Betrifft: AW: ...genau gelesen ? von: Rudi
Geschrieben am: 21.08.2014 18:06:10

DANKE an ALLE.
Jetzt klappt es. War aber wirklich knifflig.
Werde jetzt Versuchen die ganze Datei zu erstellen
Bewundere Euch wo ihr dieses Wissen herhabt.

Gruß und bis zum nächsten mal
RUDI


  

Betrifft: Matrixformel von: Erich G.
Geschrieben am: 21.08.2014 18:04:45

Hi Rudi,
der Begriff "Matrixformel" kann mancherlei beinhalten...

Hier ist es so, dass in der Formel der Aufruf einer Funktion steht, die mehrere (zwei) Werte liefert.
Wo sollen die nun hin?

Du musst zunächst B5 und C5 zusammen markieren (mit Maus oder Tastatur), wobei B5 die aktive Zelle sein muss.
Dann kannst du F2 drücken die Formel eingeben bzw. hineinkopieren, dann mit STRG+SHIFT+RETURN abschicken - fertig.
Alles klar?

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Matrixformel von: Rudi
Geschrieben am: 21.08.2014 18:51:30

Hallo zusammen,

habe es jetzt hinbekommen und sieht wie folgt aus.



Nochmals Danke an alle

Gruß Rudi


  

Betrifft: AW: Matrixformel von: Rudi
Geschrieben am: 22.08.2014 07:36:06

Nach weiterem Test hat sich ein weiteres Problem herausgestellt.
Liegt eine Zahl über 100 so wird nur noch eine Zahl die dem Wert 100 kommt genommen.
Siehe Bild
Was kann ich jetzt machen? So hat meine Liste nicht das gewünscht Ergebnis.

Gruß
Rudi


  

Betrifft: Rückfragen von: Erich G.
Geschrieben am: 22.08.2014 10:06:08

Hi Rudi,
dieses Verhalten ist duechaus erklärlich. :-)

a) Wie genau lautet die Formel, die bei dir in Q9:R9 steht? Das kann man der Grafik(!) leider nicht entnehmen.

b) Beinhaltet der Bereich, den PaarZiel auswerten soll, mindestens eine leere Zelle?

c) Hast du hier schon etwas gesagt darüber, wie leere Zellen behandelt werden sollen?
(Eine Möglichkeit: als 0 verwenden - das passierte bisher) Mit der 100 hat das nichts zu tun.

d) Wenn bei jeder Zelle des auszuwertenden Bereichs geprüft werden soll, ob sie eine Zahl enthält,
kann es sein, dass in dem Bereich keine oder nur eine Zahl steht. Was soll dann das Ergebnis von PaarZiel sein?

Ein neuer Codevorschlag:

Function PaarZiel(rngBer As Range, dVglW As Double)
   Dim arZ, ii As Long, jj As Long, im As Long, jm As Long, dm As Double

   arZ = rngBer.Value
   dm = 9 ^ 99
   
   For ii = 1 To UBound(arZ, 2) - 1
      If Application.IsNumber(arZ(1, ii)) Then
         For jj = ii + 1 To UBound(arZ, 2)
            If Application.IsNumber(arZ(1, jj)) Then
               If Abs(arZ(1, ii) + arZ(1, jj) - dVglW) < dm Then
                  dm = Abs(arZ(1, ii) + arZ(1, jj) - dVglW)
                  im = ii
                  jm = jj
               End If
            End If
         Next jj
      End If
   Next ii
   If im * jm Then
      PaarZiel = Array(arZ(1, im), arZ(1, jm))
   Else
      PaarZiel = Array("nix", "los")                  ' ???
   End If
End Function
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: Rückfragen von: Rudi
Geschrieben am: 22.08.2014 10:33:09

Guten Morgen Erich,
Danke für die schnelle Antwort. So wie es mit dem neuen Code aussieht,
dürfte es jetzt passen. Werde noch weiter Tests durchführen.



Gruß Rudi


  

Betrifft: noch zwei Tipps von: Erich G.
Geschrieben am: 22.08.2014 11:01:29

Hi Rudi,
meine Fragen a) und b) hast du - mit der Grafik(!) - beantwortet, die weiteren nicht. :-(

Tipp 1:
Auch Fragesteller dürfen (und sollten) hier mal Fragen beantworten.

Tipp 2:
Excel ist kein Grafikprogramm. Wer hier eine Grafik veröffentlicht, zwingt andere machmal dazu, Daten abzutippen. :-(
Es gibt etliche Tools, mit denen man Tabellenausschnitte sehr schön als HTML-Tabellen in Foren stellen kann,
z. B. die Excel Jeanie. Links dazu findest du in den meisten Threads hier.
Mit der Nutzung der Tools lassen sich Daten einfach vom Browser nach Excel kopieren.

Das soll jetzt kein Gemecker sein, aber vielleicht eine Anregung für die Zukunft.

Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


  

Betrifft: AW: noch zwei Tipps von: Rudi
Geschrieben am: 22.08.2014 12:15:49

Danke für die Info.
Bin zum ersten mal in einem Forum, daher bitte ich um Nachsicht.

Gruß
Rudi


  

Betrifft: AW: als Funktion von: Rudi
Geschrieben am: 22.08.2014 09:10:46

Was ich noch dazu sagen sollte. Es müssen immer 2 Zahlen dem Wert 100 am nächsten kommen.

Gruß
Rudi


  

Betrifft: AW: angepasst von: Rudi
Geschrieben am: 21.08.2014 17:13:42

Hallo Erich,

bekomme keine Werte in B5 und C6.
Muss ich in B5 und C5 eine bestimmte Formel eintragen?
oder stelle ich mich dumm an?

Rudi


  

Betrifft: ...und hier meiner! ;-) von: Luc:-?
Geschrieben am: 21.08.2014 11:59:34

 ABCDEF
110204060550
2Hilfsmatrix     
300100 
400000 
510000 
600000 
700000 
8Ergebnisvektor
910 40  50
10Formeln     
11A3:E7: {=--(ABS(F1-MTRANS(A1:E1)-A1:E1)=MIN(ABS(F1-MTRANS(A1:E1)-A1:E1)))}
12A9:E9: {=WENN(A1:E1*TEILERGEBNIS(9;INDIREKT(WECHSELN("Z3S#:Z7S#";"#";SPALTE(A1:E1));
13                 0))=0;"";A1:E1*TEILERGEBNIS(9;INDIREKT(WECHSELN("Z3S#:Z7S#";"#";SPALTE(A1:E1));0)))}
14F9:=SUMME(A9:E9)     

Luc :-?


  

Betrifft: ...wobei ich es als unlogisch empfinde, falls ... von: Luc:-?
Geschrieben am: 21.08.2014 12:16:41

…bei den neuen Werten nun plötzlich ebenfalls 40 und 5 herauskommen soll, Rudi,
denn das entspricht nicht dem, was man normalerweise unter „am nächsten kommen“ verstehen würde. Darin wären sowohl der kleinste Negativ- als auch Positiv-Differenz-Fall (inkl 0) eingeschlossen.
Aber, falls die Differenz immer ≠0 oder >0 sein soll, lässt sich das auch mit der gezeigten Methode darstellen, nur wird die Fml dann länger.
Luc :-?


  

Betrifft: AW: ...und hier meiner! ;-) von: Rudi
Geschrieben am: 21.08.2014 12:20:56

Danke für den Vorschlag,

mit der Formel wird die Datei aber richtig aufwendig.
Grund - die Zahlen können z.B. bis Spalte Z gehen. Hier muss ich ja dann die Hilfsmatrix bis Spalte Z
ausbauen. Zudem kommem weitere 30 Zeilen dazu mit Zahlen die hier ausgewertet werden müssen.

Gruß Rudi


  

Betrifft: Tja, dann wirst du dich wohl entweder an ... von: Luc:-?
Geschrieben am: 21.08.2014 12:34:35

…Erichs SubProzedur-Lösung orientieren, Rudi,
oder Steffens UDF für deine Zwecke gangbar machen müssen, zB so:

Function GetMaxA(Bereich As Range, VglWert As Long)
    Dim oWert, myArr, n&
    With WorksheetFunction
        myArr = .Transpose(.Transpose(Bereich))
    End With
    For Each oWert In myArr
        For n = LBound(myArr) To UBound(myArr)
            If CLng(oWert) + CLng(myArr(n)) <= VglWert Then
                If GetMaxA < CLng(oWert) + CLng(myArr(n)) Then _
                    GetMaxA = CLng(oWert) + CLng(myArr(n))
            End If
        Next
    Next
End Function
Achtung! In diesem Zusammenhang weise ich ausdrücklich darauf hin, dass hier von mir nur eine MinimalAnpassung eines FremdPgms vorgenommen wurde!
Luc :-?


  

Betrifft: noch eine VBA-Lösung von: Erich G.
Geschrieben am: 21.08.2014 11:29:58

Hi Rudi,
so könnte es auch aussehen:

 ABC
1ZahlenZielwertPaar
2105030
330 15
415Summe45
55  
61  

Formeln der Tabelle
ZelleFormel
C4=C2+C3

Die Zellen C2:C3 werden in VBA berechnet:
Option Explicit

Sub PaarZiel()
   Dim arZ, dZ As Double, ii As Long, jj As Long, im As Long, jm As Long, dm As Double

   arZ = Application.Transpose(Cells(2, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1))
   dZ = Cells(2, 2)
   dm = 9 ^ 99

   For ii = 1 To UBound(arZ) - 1
      For jj = ii + 1 To UBound(arZ)
         If Abs(arZ(ii) + arZ(jj) - dZ) < dm Then
            dm = Abs(arZ(ii) + arZ(jj) - dZ)
            im = ii
            jm = jj
         End If
      Next jj
   Next ii
   Cells(2, 3) = arZ(im)
   Cells(3, 3) = arZ(jm)
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich


 

Beiträge aus den Excel-Beispielen zum Thema "Hiefe für eine bestimmte Formel"