Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1484to1488
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
Inhaltsverzeichnis

Minima berechnen

Minima berechnen
05.04.2016 20:25:53
Heiko
Hallo,
ich kämpfe seit Stunden mit einem Problem.
Ich habe eine Wertetabelle mit Messwerten.
WertX AnzeigeA AnzeigeB
Nun muss ich aus dem Produkt von AnzeigeA und AnzeigeB das absolute Minimum berechnen. Dieser Wert muss nicht unbedingt in den Wertepaaren enthalten sein. Und für dieses Minimum benötige ich den dazugehörigen WertX.
Kann man das überhaupt mit Excel lösen?
Vielen Dank

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Minima berechnen
05.04.2016 21:02:20
Fennek
Hallo Heiko,
der Code ist mit integer-Zahlen getestet:

Sub Heiko()
Dim rng as range
MinA = worksheetfunction.min(columns(2))
MinB = worksheetfunction.min(columns(3))
Set rng = columns(2).find(MinA)
Msgbox "MinA in Zeile " & rng.row
Set rng = columns(3).find(MinB)
Msgbox "MinB in Zeile " & rng.row
End sub
Mfg

AW: Minima berechnen
05.04.2016 21:11:41
Heiko
Danke,
Da habe ich mich nicht konkret ausgedrückt.
Das Produkt ist immer als Produkt der gleichen Zeile gemeint.
Ich habe vorhin eine Tabelle als Anhang (?) hochgeladen. Nur kann ich sie nicht mehr finden ;(

Anzeige
Hochgeladene Datei nicht auffindbar?
06.04.2016 01:51:51
Luc:-?
Warum wohl, Heiko?
Sicher, weil du dir nicht die Mühe gemacht hast, die Hinweise zu lesen und auch zu befolgen!
Wenn du das schon nicht kannst, wie willst du dann dein Problem lösen…?!
Morrn, Luc :-?

AW: Minima berechnen
06.04.2016 19:09:40
Heiko
Guten Abend,
vielen Dank für die freundliche Unterstützung eines newbies ;)
Anbei der aktuelle Sheet inkl. Diagramm. Ich hoffe, jetzt kann man erkennen, was ich suche (orange als Hintergrund). Der jetzt dort stehende Wert sollte die Lösung sein, welche ich durch händische Annäherung ermittelt habe. Dieser Wert ist auch plausibel.
Eine Automatik wäre halt schön, weil ich den ganzen Spass mit verschiedenen Messgrößen und Messbereichen für verschiedene Messgeräte betrachten darf....
Vielen Dank nochmals.
https://www.herber.de/bbs/user/104809.xlsx

Anzeige
AW: Minima berechnen
07.04.2016 14:54:02
Michael
Hi Heiko,
jetzt hab ich noch an Deiner Geschichte gebastelt.
Eine "Interpolation" muß ja gar nicht durchgeführt werden, da Deine Berechnungen (mit Ausnahme der in E7) ja immer gleich aufgebaut sind.
Wenn man das Ganze zusammenfaßt, ist die Berechnung Deiner Spalte E mit der Formel
=((B10*coeff_E2+coeff_F2*coeff_B27)/WURZEL(3))^2*10/B10

in Spalte S erledigt; die beiden Werte E10 und S10 habe ich zum direkten Vergleich blau markiert.
Das läßt sich natürlich in VBA auch schreiben:
Function polynom(x As Double, coE2#, coF2#, coB27#) As Double
polynom = ((x * coE2 + coF2 * coB27) ^ 2) / 3 * 10 / x
End Function

aber noch deutlich (um die 30%) optimieren.
Ich habe zwei Makrovarianten eingebaut:
a) die höchst simple, einfach in einer Schleife Werte in B28 zu schreiben und das Ergebnis zum Vergleich in E28 "abzuholen": dauert natürlich vergleichsweise "ewig", kommt aber schon im ersten Durchlauf (mit /1000-Schritten) auf ein besseres Ergebnis: 37,5 und 0,8 glatt.
b) die optimierte Version, bei der VOR der eigentlichen Berechnung alle Werte, die nach Ausmultiplikation konstant sind, in Variablen verpackt werden, so daß der Rechenaufwand minimal ist.
Das sieht dann so aus:
Function MinNumerischFormel(von#, bis#, schritt#) As Double
Dim y#, ymin#, xmin#, x#, y2#
Dim c_E2#, c_F2#, c_B27#, cProd#, cEP#, cPQ#, cEQ#, ZweiProdMalc_E2#
Dim iterationen&, iSchritt&
Dim t0 As Single
c_E2 = Range("coeff_E2"): cEQ = c_E2 ^ 2
c_F2 = Range("coeff_F2")
c_B27 = Range("coeff_B27")
cProd = c_F2 * c_B27: cPQ = cProd ^ 2
cEP = cProd * c_E2
ZweiProdMalc_E2 = 2 * cProd * c_E2
If bis  0!"
MinNumerischFormel = 2 ^ 32
Exit Function
End If
ymin = 2 ^ 32
x = von
iSchritt = Int(1 / schritt)
t0 = Timer
While Round(x, 14) 
und rechnet mit 1/10000er Schritten in ein paar hundert Millisekunden.
Beide Varianten rechnen nicht ALLES durch, sondern ausgehend von einem via Formeln ermittelten Startwert in Zelle B38.
Ich habe einige NAMEN definiert, für E2, F2 und B27, so nach dem Motto "coeff_E2", weil das übersichtlicher weiterzurechnen war.
Die erste Variante kannst Du einsetzen, falls sich Deine Formeln häufig ändern, die zweite, wenn nicht.
Die Datei: https://www.herber.de/bbs/user/104823.xlsm
Die Spalten O-V kannst Du löschen, die braucht das Makro nicht, die waren nur zur Verifikation der Formeln.
Schöne Grüße,
Michael

Anzeige
AW: Minima berechnen
07.04.2016 16:25:47
Heiko
Hallo Frank,
vielen Dank für Deine Mühe. Ich bin begeistert.
Der Ansatz gefällt mir, aber (leider) kommen da noch bis zu 8 weitere Parameter hinzu, welche in die Berechnung, genauso aufwändig, eingehen (ab Zeile Q). Ich wollte mich nur zuerst mit der "Allgemeingültigkeit" beschäftigen, bevor ich die anderen Parameter berücksichtige. Die Formel, welche jetzt benutzt wird,wird dann sehr sehr sehr lang.
"Makro simpel" gefällt mir da schon ganz gut ;)
Vielen Dank nochmal.

AW: Minima berechnen
07.04.2016 16:57:48
Heiko
Hallo Michael,
sorry, für die Anrede mit Frank ;(
Aber Frank stand da gerade neben mir :D

Anzeige
alles klar
08.04.2016 14:56:54
Michael
Hi Heiko,
no problem.
Du siehst ja es ja selbst: wenn es OFT mit gleichen Werten/Formeln berechnet werden muß, rentiert es sich schon, die zu zerpflücken und "VBA-gerecht" zu formulieren.
Was aber evtl. wichtig ist: meine Ermittlung des Startwerts ist eine sehr vorläufige Krücke, an der Du noch etwas basteln solltest.
Einen etwas besseren, aber immer noch nicht optimalen Vorschlag findest Du links unten in gelb:
https://www.herber.de/bbs/user/104852.xlsm
Happy Exceling,
Michael

AW: Minima berechnen
05.04.2016 22:03:16
Fennek
Hallo,
das 'Produkt' hatte ich überlesen. Ich nehme an, dass Spalte 'D' leer ist.

Sub Heiko2()
Dim rng as range
lr = cells(rows.count, "A").end(xlup).row
Range(cells(2, "D"), cells(lr, "D")).formulaR1C1 = "rc[-2] * rc[-1]"
iMin = worksheetfunction.min(columns(4))
Set rng = columns(4).find(iMin, lookin:=xlvalues)
Msgbox "Min in Zeile " & rng.row
End sub
Mfg

Anzeige
AW: Minima berechnen
05.04.2016 23:06:21
Heiko
Dankeschön, für die Mühe, aber wir reden leider aneinander vorbei.
Leider finde ich nicht heraus, wie ich hier den Sheet anhängen kann...
Spalte A. Spalte B. Spalte C. Spalte D.
10. 1,7. 0,21. B * C
15. 1,9. 0,17. B * C
20. 2,3. 0,15. B * C
25. 2,8. 0,11. B * C
Dies sind fiktive Werte.
B ist die absolute Unsicherheit. C ist die relative Unsicherheit bezogen auf A (B in Prozent von A).
Das Minimum des Produktes ist der Indikator zur Ableitung der allgemeinen Aussage. Der entstehende Graph ist irgendwie eine flache Parabel.
Ich habe jetzt das Minimum aus der Tabelle ermittelt. Das dumme ist nur, dass das wirkliche Minimum der Funktion zwischen meinen Zeilen liegt.
Ich muss also irgendwie Zwischenwerte "interpolieren", das wirkliche Minimum und den resultierenden Wert für A ermitteln.
Mit diesem Wert von A kann ich dann die erforderlichen Ableitungswerte berechnen.
Vielen Dank

Anzeige
AW: Minima berechnen
05.04.2016 23:33:09
Fennek
Hallo,
Ok, aber für diese Fragestellung halte ich das für einen falschen Ansatz. Auch wenn meine Uni-Zeit lange zurückliegt, es gab da mehrere Ansätze für die Bestimmung von Extrema, z.b. das Newton-Verfahren, wenn man die Funktion nicht kennt.
Mit den Standard-Excel-Formeln ist das aber nicht zu machen, aber sieh dir mal den 'solver' an.
Ich bin dann raus.
Mfg

Datei hochladen
06.04.2016 03:18:07
Michael
Hi Heiko,
wer lesen kann, ist klar im Vorteil ...
Userbild
und was die Zwischenablage ist, dürftest Du mit "Excel gut" auch wissen:
Userbild
Wenn wir dann ne Datei haben, können wir Dir helfen.
Schöne Grüße,
Michael

Anzeige
...und die Mathematik - SEHR seltsam
06.04.2016 05:43:42
Michael
Hi Heiko,
noch zur Mathematik: eine "flache Parabel" ist schön und gut, aber eine Interpolation zu n Wertepaaren bzw. Punkten ergibt ein Polynom max. (n-1)-ter Ordnung, und wenn mich nicht alles täuscht (bin da auch nicht sooo fit), kann man nicht auf einen Streich das absolute Minimum errechnen, sondern muß alle Minima errechnen und dann das kleinste aussuchen.
Excel kann grafisch u.a. eine polynomische Trendlinie (im Diagramm) anzeigen, deren Polynom man auslesen kann.
Die Maxima/Minima sind da, wo die 1. Ableitung = 0 ist, und da wird's happig, weil Du aus einem Polynom höherer als 4. Ordnung keine Nullstellen mehr (formelmäßig) errechnen kannst.
Zunächst kommt es darauf an, wie viele Punkte Du hast, und dann, wie gut die Interpolation ist.
Schlimmstenfalls muß man das Polynom halt mit VBA "abfahren" und jeweils zwei sehr nah nebeneinander liegende Punkte miteinander vergleichen: wenn die Steigung (die 1. Ableitung) Ergebnisse mit unterschiedlichem Vorzeichen hat, muß ein Min/Max dazwischen liegen.
Obwohl: wenn man es *so* macht, kann man gleich den kleinsten y-Wert errechnen, ohne Umweg über die Ableitungen, wobei Du die Function Polynom mit den interpolierten Berechnungen bestückst:
Option Explicit
Function polynom(x As Double) As Double
polynom = x
End Function
Function MinNumerisch(von#, bis#, schritt#) As Double
Dim y#, ymin#, x#, xmin#
If bis  0!"
MinNumerisch = 2 ^ 32
Exit Function
End If
x = von
'While x  bis)
y = polynom(x)
Debug.Print x & ":" & y
If y  1.9 Then Stop
Wend
MinNumerisch = xmin
End Function
Sub minAufrufen()
MsgBox MinNumerisch(-2, 2, 0.1)
End Sub
SEHR seltsam ist, wie man dabei von Excel immer gefoppt wird, denn die Ausgabe im Debug-Fenster hört bei 1,9 auf, nicht wie gewünscht bei 2.
Zu allem Überfluß ist auch 1,9 größer als 1,9:
Userbild
Das nur Illustration, daß # (=Double)-Werte IMMER mit Vorsicht zu genießen bzw. zu runden sind, denn so geht es:
Function MinNumerisch(von#, bis#, schritt#) As Double
Dim y#, ymin#, x#, xmin#
If bis  0!"
MinNumerisch = 2 ^ 32
Exit Function
End If
x = von
While Round(x, 14)  1.9 Then Stop
Wend
MinNumerisch = xmin
End Function
Gruß,
Michael
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige