Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Mehrfachberechnung linearesGleichungssystem-Solver

Betrifft: Mehrfachberechnung linearesGleichungssystem-Solver von: Joana
Geschrieben am: 29.06.2007 22:28:40

Hallo,
ich habe ein Problem, was ich bisher nur mit einem riesigen Arbeitsaufwand zu loesen weiss: Ich habe eine Gleichung y=a+b*x, wobei x und y als Variable gegeben sind. Nun moechte ich, dass Excel mir (z.B. mittels Solver) fuer vorgegebene xy die Gleichung loest - allerdings moechte ich ALLE moeglichen Loesungen fuer die angegebenen xy (insgesamt 92), und daraus dann den Durchschnitt... heisst also die Loesung des GS fuer Zeile 1 mit 2 bis Zeile 1 mit 92, das gleiche fuer Zeile 2 mit 3 bis Zeile 2 bis 92, usw. Ich weiss nicht, ob ich mich klar genug ausgedrueckt habe?
Das ganze manuell zu erledigen waer ein Riesenaufwand, ein Makro fuer jeden der einzelnen Solver auch... Gibt es eine Formel (bzw. Kombination von Formeln), die mir helfen kann???????????
Fuer einen Tipp waer ich riesig dankbar!!
Schoene Gruesse,
Joana

  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: ingUR
Geschrieben am: 29.06.2007 23:03:47

Hallo, Joana,

mehrmals habe ich Deine Aufgabe gelesen, jedoch genausoft auch nicht verstanden.

Die Gleichung y=a+b*x beschreibt eine Menge von Punkten, die auf einer Geraden liegen, die einen Anstieg von b aufweist und die y-Achse bei einem Wert von a schneidet.

Ein Punkt {x,y} allein kann jedoch keine Gerade beschreiben oder richtiger gesagt, durch ihn können beliebig viel Geraden der Form y=a+b*x gelegt werden. Damit kann für einen Punkt {x,y} keine eindeutige Lösung für das Parameterpaar {a,b} gefunden werden.

Vielleicht kannst Du mit einem Beispiel den Weg zu den ersten Lösungspaaren demonstrieren.

Gruß,
Uwe




  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: OttoH
Geschrieben am: 29.06.2007 23:05:28

Hallo Joana,

soll das heißen, Du hast 92 Punkte und jeder soll mit jedem eine Gerade definieren, für die dann a und b zu berechnen sind?


fragt OttoH


  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: Joana
Geschrieben am: 29.06.2007 23:15:35

Hallo Otto,
ja genau. Ich benoetige im Endeffekt aber nicht die einzelnen Geraden, sondern ein Durchschnitts-a und ein Durchschnitts-b, entweder als Median oder Mittelwert. Um den Durchschnitt zu erhalten, muss ich jedoch erst einmal die einzelnen a und b ausrechnen, was mir das ganze so schwierig macht...
Gibt es da eine Moeglichkeit?

Danke schon einmal!


  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: ingUR
Geschrieben am: 30.06.2007 11:26:47

Hallo, Joana,

nach der Beantwortung von Ottos Rückfrage, scheint mir Deine Aufgabe am übersichtlichsten mit VBA zu lösen zu sein, denn Du benötigst für jedes Koeffizentenpaar, das Du berechnen willst, die zwei Punkte aus Deiner Liste.

Für fünf Punkte habe ich einmal die Berechnung exemplarisch durchgeführt



Der Aufbau der Spalten für die Punkte A und B wird zwar über Indexberechnung möglich sein, so dass im Anschluß für jedes Punktpaar der Antiegs- und Absolutkoeffizient bestimmt werden kann (Wiederholungen, also Strecke P1-P2 und Strecke P2-P1, können, wenn sie vollständig eingeführt werden, hingenommen werden, da dadurch das Endergebnis sich nicht verändert), doch fehlt mir im Moment noch etwas die Einsicht, warum man derartige Aufgaben überhaupt lösen möchte.

Die allgemeine Formel zur Ermittluung von m und y0 bei gegeben Punkten P1 und P2 ist bekannt:

m = (y2-y1)/(x2-x1)
y0 = y1-m*x1

ACHTUNG: Sonderbearbeitung für x1=x2 ist erforderlich - Gerade steht senkrecht und schneidet die y-Achse in keinem Punkt oder ist mit ihr deckungsgleich - Einführung der trigonometrischen Funktionen wird in diesem Fall erforderlich), was eine Lösung mit Zellenformeln zusätzlich erschwert, wenn diese Fälle zu berücksichtigen sind.

Der Mittelwert aller Anstiege der Punktverbindungsgeraden beträgt im Beispiel m = -1. Der mittlere Achsenabschnittswert y0, wird zu -2,78 ermittelt. Im Vergelich ist die lineare Regressionsferade mit Formel eingetragen (y = m*x + y0, mit y0=1,4 und m = -1,1)

Gruß,
Uwe


  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: OttoH
Geschrieben am: 30.06.2007 13:29:34

Hallo Joana, hallo Uwe,

ich habe auch mal ein wenig gebastelt, nachdem ich gestern in der Nacht keine Lust mehr hatte, die Antwort auf meine Frage abzuwarten.

Herausgekommen ist folgendes:

 
 ABCDEFGHIJK
1           
2 Achsenabschnitt a         
3           
4 x -2,00-1,000,001,002,00   
5  y9,00-3,004,00-10,007,00   
6 -2,009,00------15,004,00-3,678,00   
7 -1,00-3,00-15,00-----4,00-6,500,33   
8 0,004,004,004,00-----4,004,00   
9 1,00-10,00-3,67-6,504,00------27,00 Mittl. Achsenabschnitt 
10 2,007,008,000,334,00-27,00----- -2,78 
11           
12           
13           
14           
15           
16           
17 Steigung b         
18           
19 x -2,00-1,000,001,002,00   
20  y9,00-3,004,00-10,007,00   
21 -2,009,00------12,00-2,50-6,33-0,50   
22 -1,00-3,00-12,00-----7,00-3,503,33   
23 0,004,00-2,507,00------14,001,50   
24 1,00-10,00-6,33-3,50-14,00-----17,00 Mittl. Steigung 
25 2,007,00-0,503,331,5017,00----- -1,00 
26           
27           
28           
Formeln der Tabelle
D6 : =a(D$5;$C6;D$4;$B6)
E6 : =a(E$5;$C6;E$4;$B6)
F6 : =a(F$5;$C6;F$4;$B6)
G6 : =a(G$5;$C6;G$4;$B6)
H6 : =a(H$5;$C6;H$4;$B6)
D7 : =a(D$5;$C7;D$4;$B7)
E7 : =a(E$5;$C7;E$4;$B7)
F7 : =a(F$5;$C7;F$4;$B7)
G7 : =a(G$5;$C7;G$4;$B7)
H7 : =a(H$5;$C7;H$4;$B7)
D8 : =a(D$5;$C8;D$4;$B8)
E8 : =a(E$5;$C8;E$4;$B8)
F8 : =a(F$5;$C8;F$4;$B8)
G8 : =a(G$5;$C8;G$4;$B8)
H8 : =a(H$5;$C8;H$4;$B8)
D9 : =a(D$5;$C9;D$4;$B9)
E9 : =a(E$5;$C9;E$4;$B9)
F9 : =a(F$5;$C9;F$4;$B9)
G9 : =a(G$5;$C9;G$4;$B9)
H9 : =a(H$5;$C9;H$4;$B9)
D10 : =a(D$5;$C10;D$4;$B10)
E10 : =a(E$5;$C10;E$4;$B10)
F10 : =a(F$5;$C10;F$4;$B10)
G10 : =a(G$5;$C10;G$4;$B10)
H10 : =a(H$5;$C10;H$4;$B10)
J10 : =MITTELWERT(D6:H10)
D21 : =b(D$20;$C21;D$19;$B21)
E21 : =b(E$20;$C21;E$19;$B21)
F21 : =b(F$20;$C21;F$19;$B21)
G21 : =b(G$20;$C21;G$19;$B21)
H21 : =b(H$20;$C21;H$19;$B21)
D22 : =b(D$20;$C22;D$19;$B22)
E22 : =b(E$20;$C22;E$19;$B22)
F22 : =b(F$20;$C22;F$19;$B22)
G22 : =b(G$20;$C22;G$19;$B22)
H22 : =b(H$20;$C22;H$19;$B22)
D23 : =b(D$20;$C23;D$19;$B23)
E23 : =b(E$20;$C23;E$19;$B23)
F23 : =b(F$20;$C23;F$19;$B23)
G23 : =b(G$20;$C23;G$19;$B23)
H23 : =b(H$20;$C23;H$19;$B23)
D24 : =b(D$20;$C24;D$19;$B24)
E24 : =b(E$20;$C24;E$19;$B24)
F24 : =b(F$20;$C24;F$19;$B24)
G24 : =b(G$20;$C24;G$19;$B24)
H24 : =b(H$20;$C24;H$19;$B24)
D25 : =b(D$20;$C25;D$19;$B25)
E25 : =b(E$20;$C25;E$19;$B25)
F25 : =b(F$20;$C25;F$19;$B25)
G25 : =b(G$20;$C25;G$19;$B25)
H25 : =b(H$20;$C25;H$19;$B25)
J25 : =MITTELWERT(D21:H25)
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  



Dazu braucht man auch noch 2 selbstgeschriebene Funktionen:

Option Explicit

Public Function a(y1, y2, x1, x2) As Variant  'Achsenabschnitt

If x2 = x1 Then
    a = "-----"
Else
    a = y1 - b(y1, y2, x1, x2) * x1
End If

End Function



Public Function b(y1, y2, x1, x2) As Variant  'Steigung

If x2 = x1 Then
    b = "-----"
Else
    b = (y2 - y1) / (x2 - x1)
End If

End Function




Die komplette Mappe findet Ihr hier:
https://www.herber.de/bbs/user/43711.xls


Bei Rückfragen bitte melden; kann aber sein, dass ich heute nicht mehr vorbeischaue.

Gruß & nice weekend OttoH


  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: OttoH
Geschrieben am: 30.06.2007 13:35:15

Hallo nochmal,

die Aufgabe ist zwar ganz interessant zu lösen, aber die Zentralfrage: Was will ich mit der mittleren Steigung und dem mittleren Achsenabschnitt????

Gruß OttoH


  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: Joana
Geschrieben am: 02.07.2007 16:53:08

Hallo Otto,
hallo Uwe,

hab vorhin schon einmal geantwortet, aber irgendwie ist mein Beitrag net angekommen. Erstmal vielen vielen Dank fuer Eure schnelle Hilfe, habe das gerade umgesetzt und es klappt auch (fast) alles bestens..

Es handelt sich uebrigens bei mir nicht um eine mathematische Anschaung, sondern um eine betriebswirtschaftliche Berechnung, naemlich der Berechnung von Kommissionen.. Daher konnt ich mich auch im mathematischen Sinne (Geradenschnittpunkte) eher schlecht ausdruecken :). Sinn meiner Berechnung ist es, eine Beziehung zwischen variablen und fixen Bestandteilen von Gesamtumsaetzen verschiedener Lokale herzustellen.

Otto, ich habe nun Deine Loesung angewandt: Mein Problem ist noch, dass x1 schon = x2 sein kann, ebenso y. Die Eineindeutigkeit habe ich mittels Lokalcodes sichergestellt, und die x und y per s-verweis zugewiesen. Heisst also, ich habe in Spalte A und Zeile 1 den Code, y in S'B' und Z'2' und x in S'C' und Zeile'3. Kann ich in den VBA-Code, der die Formeln definiert, anstatt eines x2=x1, etc. relative Zellbezuege integrieren, die mir bei Gleichheit einer Zelle in Spalte A mit einer Zelle in Zeile 1 die Berechnung auslaesst?? (Hoffe, ich habe mich klar genug ausgedrueckt)

Vielen herzlichen Dank schonmal! Ihr seid mir eine riesen Hilfe!

Gruss,

Joana


  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: ingUR
Geschrieben am: 02.07.2007 17:48:30

 
 ABCDE
1xymittleres m-0,019 
2020mitlleres y09,644 
3112mittlere Gerade aller Punkpaartverbindungen
4214X1m-1,0009,663
5316X2m92,0007,916
6415   
754   
8612   
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  


Hallo, Joana,

ausgehend von den Wertepaaren {x;y} in den Spalten A und B der aktiven Tabelle (hier nur bis zur Spalte 8 dargestellt), werden mit der unten angegebenen VBA-Prozedur, die in einem Standardmodul einzufügen ist, die Werte der Zellen D1, D2, {D4, E4} und {D5;E5} berechnet.

Das Makro muß hier über einem Makroaufruf (z.B. via F8) aktiviert werden.

Option Explicit
Const EPSI As Double = 1 / 100000000#

Sub MittlereGerade()
    Dim maxR As Long, r As Long
    Dim x1 As Double, y1 As Double
    Dim x2 As Double, y2 As Double
    Dim rngC1 As Range, rngC2 As Range
    Dim dx As Double, dy As Double
    Dim m As Double, SumM As Double
    Dim y0 As Double, SumY0 As Double
    Dim s As Long
    
    r = Cells(Rows.Count, 1).End(xlUp).Row
    
    For Each rngC1 In Range("A2:A" & r - 1)
        x1 = rngC1
        y1 = rngC1.Offset(0, 1)
        For Each rngC2 In Range("A" & rngC1.Row + 1 & ":A" & r)
            x2 = rngC2
            y2 = rngC2.Offset(0, 1)
            
            dx = x2 - x1
            If Abs(dx) < EPSI Then
                MsgBox "Punkt " & rngC1.Row & " und Punkt " & rngC2.Row & " haben einen  _
gleichen x-Wert" & vbLf & "das Wertepaar wird nicht  berücksichtigt!"
            Else
                dy = y2 - y1
            
                m = dy / dx
                y0 = y1 - x1 * m
            
                SumM = SumM + m
                SumY0 = SumY0 + y0
                s = s + 1
            End If
        Next rngC2
    Next rngC1
    
    m = SumM / s
    y0 = SumY0 / s
    Cells(1, "D") = m
    Cells(2, "D") = y0
    
    Cells(4, "D") = Application.Min(Range("A2:A" & r)) - 1
    Cells(4, "E") = y0 + Cells(4, "D") * m
    
    Cells(5, "D") = Application.Max(Range("A2:A" & r)) + 1
    Cells(5, "E") = y0 + Cells(5, "D") * m
    
End Sub

Die Werte der {x;y}-Punkte können in einem Punktdiagramm dargestellt werde. die mittlere Gerade kann als zusätzliche Datenreihe im selben Diagramm eingefügt werden:



Vielleicht hilft das Modul für die weitere Ausarbeitung, denn natürlich kann, wenn das Nachrichtenfenster nicht gebraucht wird, dieser IF-Zweig entfallen und die Abfrage nur nach IF abs(dx)>EPSI durchgeführt werden.


      ...
      If Abs(dx) > EPSI Then
             dy = y2 - y1
            
              m = dy / dx
              y0 = y1 - x1 * m
              ....
      End if
     ...


Gruß,
Uwe


  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: Joana
Geschrieben am: 02.07.2007 20:59:54

Danke fuer Eure Muehen, leider hab ich immer noch ein paar Probleme:

@Uwe: die Loesung geht ja schon in die hoehere VBA-Kunst; leider hab ich erst angefangen, mich damit zu beschaeftigen und bin ueber einfache Codes noch nicht hinausgekommen.. heisst, mir stellen sich einige Huerden in den Weg, wenn ich versuche, den Code an meine tabelle anzupassen:(.. Msg Box brauch ich uebrigens nicht, ich wollt das Tabellenblatt eh verstecken und nur die Loesungen rauskopieren. Hinzkommt, dass mir aufgefallen ist, dass ich nicht einfach den 'Gesamtmittelwert' bilden kann, sondern unter bestimmten Kriterien klassifizieren muss, um gruppenweise Mittelwerte zu bilden. Womit sich ein grossartiger VBA-Code schon erledigt hat, denn wenn ich selbst Bedingungen hinzufuegen oder abaendern muss, bin ich verloren...

@ Otto: mit Deiner Loesung hab ich das eigtl gut hinbekommen, also auch das Klassifizieren, vermutlich etwas umstaendlicher als mit Visual Basic, aber fktioniert... Mein einziges Problem ist das bereits angesprochene, dass meine Xe sich ruhig ueberschneiden duerfen, da mit x die Anzahl an Tischen in einem Lokal ausgedrueckt wird, nur die Schnittmenge ein und desselben Lokals sollte net ausgerechnet werden, und in diesem Fall haett ich gern das "----". Jedem Lokal ist ein Code zugewiesen, ueber welchen es eindeutig zu identifizieren ist, weshalb ich gern x1 = x2, durch den jeweiligen relativen Zellbezug ersetzen wuerde, also evtl. Spalte A ungleich Zeile 1...

Wuerde auch gern meine Tabelle anhaengen, denn ich habe das Gefuehl, meine Versuche, mich exakt auszudruecken, misslingen irgendwie:).. Wie ist denn das moeglich?

Hier schon mal ein Einblick:

CÓDIGO 40265 40410 40120 40335 40300 40320 40315 40365
VENTAS $ 3,93 $ 7,30 $ 9,59 $ 10,53 $ 10,97 $ 12,21 $ 12,68 $ 13,06
N° DE MESAS 100 95 50 120 30 40 90 140
40265 $ 3,93 100 ----- $ 71,20 $ 15,24 $ -29,07 $ 13,98 $ 17,73 $ 91,38 $ -18,88
40410 $ 7,30 95 $ 71,20 ----- $ 12,13 $ -5,00 $ 12,66 $ 15,78 $ 109,54 $ -4,87
40120 $ 9,59 50 $ 15,24 $ 12,13 ----- $ 8,91 $ 13,04 $ 22,70 $ 5,72 $ 7,66
40335 $ 10,53 120 $ -29,07 $ -5,00 $ 8,91 ----- $ 11,11 $ 13,05 $ 19,11 $ -4,62
40300 $ 10,97 30 $ 13,98 $ 12,66 $ 13,04 $ 11,11 ----- $ 7,25 $ 10,11 $ 10,40
40320 $ 12,21 40 $ 17,73 $ 15,78 $ 22,70 $ 13,05 $ 7,25 ----- $ 11,83 $ 11,87
40315 $ 12,68 90 $ 91,38 $ 109,54 $ 5,72 $ 19,11 $ 10,11 $ 11,83 ----- $ 11,99
40365 $ 13,06 140 $ -18,88 $ -4,87 $ 7,66 $ -4,62 $ 10,40 $ 11,87 $ 11,99 -----

Unte jeder Spalte wollte ich nun einen Mittelwert mit den Kriterien >0 und <=D2:D (jeweilige Umsatzzahl)bilden...

Einigermassen verstaendlich?? Ansonsten lad ich auch gern meine Tabelle hoch, muesste nur wissen, wo..

Danke!


  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: Joana
Geschrieben am: 02.07.2007 22:32:07

Hat sich schon erledigt, danke! Hab einfach in den VBA-Code zwei weitere Variable eingebaut und miteinander abgeglichen..

Trotzdem vielen vielen Dank fuer die Hilfe!!!!!


  

Betrifft: AW: Mehrfachberechnung linearesGleichungssystem-Solver von: OttoH
Geschrieben am: 02.07.2007 18:29:06

Hallo Joana,

die Gleichheit von X1 und x2 hatte ich in der Funktion abgefangen. Insofern muss diese Bedingung nicht nochmals abgefangen werden. Wenn der Fall eintritt, setze ich "-----" in die Zelle ein und dieser Wert wird bei der Mittelwertberechnung auch nicht berücksichtigt.
Sie festen Zellbezüge müssen auch nicht unbedingt sein und können grundsätzlich durch die Z1S1-Schreibweise ersetzt werden. Dann wird die Formel auch noch ein wenig unansehnlicher. Über die Sinngebung habe ich mir allerdings noch keine Gedanken gemacht.
Ich habe dise relativ einfache DArstellung der Werte per $-Adressierung gewählt, um die Formel leicht kopieren zu können. Es geht immerhin um 90*90*2 = 16200 Formeln, die dort abgefackelt werden; ich hätte keine Lust, die alle per Hand zu editieren.

Wenn noch Unklarheiten bestehen, setze doch mal Deine TAbelle hier herein; dann kann man besser fachsimpeln.

Gruß OttoH