Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.04.2024 00:49:19
Excel Sheet in einen Excel Kalender einfügen
20.04.2024 00:08:11
In Ordner nur das Speichern von Dateien erlauben
19.04.2024 21:26:15
mehrfache Texte finden
19.04.2024 19:41:34
Bei VBA Ausführung Anzeige einfrieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1376to1380
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1376to1380
1376to1380
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Hiefe für eine bestimmte Formel

Hiefe für eine bestimmte Formel
21.08.2014 09:44:38
Rudi
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

23
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Hiefe für eine bestimmte Formel
21.08.2014 10:39:47
CitizenX
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)) =getMaxA(A1;B1)
in A1 befindet sich dein Zahlenstring
in B1 dein Max Wert
VG
Steffen

Ich glaube kaum, dass das die Lösung ist, ...
21.08.2014 11:27:00
Luc:-?
…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 :-?

Anzeige
AW: Hiefe für eine bestimmte Formel
21.08.2014 11:42:06
Rudi
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
Userbild

angepasst
21.08.2014 11:51:43
Erich
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)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: angepasst
21.08.2014 12:26:33
Rudi
Hallo Erich,
Danke, sieht auf den ersten Blick gut aus. Werde diese demnächst ausprobieren, wahrscheinlich erst morgen. Werde dann Rückmeldung geben.
Rudi

als Funktion
21.08.2014 13:02:10
Erich
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)
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

Anzeige
AW: als Funktion
21.08.2014 17:40:06
Rudi
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?
Userbild
Userbild
Rudi

...genau gelesen ?
21.08.2014 17:52:24
robert
...wobei die Zellen B5:C5 bei Eingabe der Matrixformel zusammen markiert sein müssen:
Gruß
robert

AW: ...genau gelesen ?
21.08.2014 18:06:10
Rudi
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

Anzeige
Matrixformel
21.08.2014 18:04:45
Erich
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

AW: Matrixformel
21.08.2014 18:51:30
Rudi
Hallo zusammen,
habe es jetzt hinbekommen und sieht wie folgt aus.
Userbild
Nochmals Danke an alle
Gruß Rudi

Anzeige
AW: Matrixformel
22.08.2014 07:36:06
Rudi
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.
Userbild
Gruß
Rudi

Rückfragen
22.08.2014 10:06:08
Erich
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)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Rückfragen
22.08.2014 10:33:09
Rudi
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.
Userbild
Gruß Rudi

noch zwei Tipps
22.08.2014 11:01:29
Erich
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

Anzeige
AW: noch zwei Tipps
22.08.2014 12:15:49
Rudi
Danke für die Info.
Bin zum ersten mal in einem Forum, daher bitte ich um Nachsicht.
Gruß
Rudi

AW: als Funktion
22.08.2014 09:10:46
Rudi
Was ich noch dazu sagen sollte. Es müssen immer 2 Zahlen dem Wert 100 am nächsten kommen.
Gruß
Rudi

AW: angepasst
21.08.2014 17:13:42
Rudi
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

...und hier meiner! ;-)
21.08.2014 11:59:34
Luc:-?
 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 :-?

Anzeige
...wobei ich es als unlogisch empfinde, falls ...
21.08.2014 12:16:41
Luc:-?
…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 :-?

AW: ...und hier meiner! ;-)
21.08.2014 12:20:56
Rudi
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

Anzeige
Tja, dann wirst du dich wohl entweder an ...
21.08.2014 12:34:35
Luc:-?
…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))
Achtung! In diesem Zusammenhang weise ich ausdrücklich darauf hin, dass hier von mir nur eine MinimalAnpassung eines FremdPgms vorgenommen wurde!
Luc :-?

noch eine VBA-Lösung
21.08.2014 11:29:58
Erich
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)
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige