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
880to884
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
880to884
880to884
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Mehrfachberechnung linearesGleichungssystem-Solver

Mehrfachberechnung linearesGleichungssystem-Solver
29.06.2007 22:28:40
Joana
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

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Mehrfachberechnung linearesGleichungssystem-Solver
29.06.2007 23:03:00
ingUR
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

Anzeige
AW: Mehrfachberechnung linearesGleichungssystem-Solver
29.06.2007 23:05:28
OttoH
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

AW: Mehrfachberechnung linearesGleichungssystem-Solver
29.06.2007 23:15:00
Joana
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!

AW: Mehrfachberechnung linearesGleichungssystem-Solver
30.06.2007 11:26:00
ingUR
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

Anzeige
AW: Mehrfachberechnung linearesGleichungssystem-Solver
30.06.2007 13:29:00
OttoH
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




		

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Mehrfachberechnung linearesGleichungssystem-Solver
                                            

                    

                                                    30.06.2007 13:35:15
                                            

                                

                                            OttoH
                                    

            

        

        

                            
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




		

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Mehrfachberechnung linearesGleichungssystem-Solver
                                            

                    

                                                    02.07.2007 16:53:08
                                            

                                

                                            Joana
                                    

            

        

        

                            
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




		

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Mehrfachberechnung linearesGleichungssystem-Solver
                                            

                    

                                                    02.07.2007 17:48:30
                                            

                                

                                            ingUR
                                    

            

        

        

                            

 
  
 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) 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




		

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Mehrfachberechnung linearesGleichungssystem-Solver
                                            

                    

                                                    02.07.2007 20:59:54
                                            

                                

                                            Joana
                                    

            

        

        

                            
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 
Einigermassen verstaendlich? Ansonsten lad ich auch gern meine Tabelle hoch, muesste nur wissen, wo..

Danke!




		

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Mehrfachberechnung linearesGleichungssystem-Solver
                                            

                    

                                                    02.07.2007 22:32:07
                                            

                                

                                            Joana
                                    

            

        

        

                            
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!!!!!




		

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: Mehrfachberechnung linearesGleichungssystem-Solver
                                            

                    

                                                    02.07.2007 18:29:00
                                            

                                

                                            OttoH
                                    

            

        

        

                            
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

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


            
    
    
    
    
    
    
    
    
    
            


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

    
    
    


    
Anzeige

    

        
                    
                
    




    
    

    

        

            

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

            

                

                
            

        

    




    
    

    
    
            
 

            
            

                
                
                                    

                        


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

                    

                                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige