Regression - Excel by dm3RYS2w

VIEWS: 22 PAGES: 15

									           Ausgangsdaten
                Xi           Yi     xi-xquer       yi-yquer                           60000
            KW         Preis
Audi                74        38000          -32.1       -8666.7                      55000
Audi                92        41600          -14.1       -5066.7
Audi               110        48100            3.9        1433.3                      50000
Audi               142        53600           35.9        6933.3
                                                                                      45000
BMW                 75        38600          -31.1       -8066.7
BMW                 85        42100          -21.1       -4566.7                      40000
BMW                110        48600            3.9        1933.3
BMW                125        52200           18.9        5533.3                      35000
BMW                142        57200           35.9      10533.3
                                                                                      30000
                                                                                              70        80        90



1. Bestimmung der Modellparameter a und b

Gesamtmittelwerte              xquer                     106.1
                               yquer                   46666.7


Steigung                                        oder                                                           257.12



Achsenabschnitt                                                                                               19383.6


                    Xi                 Yi       xi-xquer           yi-yquer       yi schätz        yi schätz - y quer
            KW                 Preis
Audi                      74            38000              -32.1        -8666.7          38410                -8256.4
Audi                      92            41600              -14.1        -5066.7          43038                -3628.2
Audi                     110            48100                3.9         1433.3          47667                  999.9
Audi                     142            53600               35.9         6933.3          55894                 9227.7
BMW                       75            38600              -31.1        -8066.7          38667                -7999.2
BMW                       85            42100              -21.1        -4566.7          41239                -5428.1
BMW                      110            48600                3.9         1933.3          47667                  999.9
BMW                      125            52200               18.9         5533.3          51523                 4856.7
BMW                      142            57200               35.9        10533.3          55894                 9227.7

                                                (xi-xquer)^2 (yi-yquer)^2                          (yischätz-yquer)^2
            Quadratesummen                        5606.88889 382140000                                   370670207.5

2. Bestimmung der Modellgüte: Bestimmtheitsmass B

                           yi  ymittel
                                                 2
              
Gesamtvariation
                                                                     382140000
                          i

                           yi  schätz  ymittel
                                                           2
Erklärte_Variation
                                                                     370670207
                          i

                      yi  schätz  yi
                                                2
Restvariation                                                       11469793
                     i
                         yi  schätz  yi
                                                         2
Restvariation
                        i

            erklärte_Variation
B                                                                                       B=                0.97
            gesamte_Variation                                    Korrelationskoeffizient r =                0.98

Das Bestimmtheitsmass lässt sich wie folgt testen:
     Erklärte_Variation ( n  2) Mit B eingesetzt ergibt sich:                     B( n  2)      Prüfgrösse F=
F                                                                        F                           `--->p=
      Rest_Variation        1
                                                                                    1B
Die Erklärte und die Gesamtvarianz kann man für den F-Wert nicht nehmen, da diese beiden Varianzen nicht unabhängig voneinan
Das Ergebnis       1.38E-06 bedeutet:    Wenn die n=9 y-Werte rein zufällig wären (y also von x nicht abhängen würde), dann wü
                                         geringen Wahrscheinlichkeit durch eine Gerade derart gut (B= 0,97) genähert werden kö

3. Bestimmung Vertrauensintervalle der Modellparameter a und b.

Die Varianz der Residuen wird benötigt zur Berechnung der Varianz der beiden Modellparameter und dann für die Berechn


                               yi  yi  schätz
                                                     2


                              i
        VarianzResiduen 
                                     n 2                                     n=                                9

Varianz der Residuen                                                                                    1638542
Standardabweichung der Residuen                                                                            1280


Berechnung der Standardfehler der Modellparameter




                                                                                               Varianz(a)
                                                                                               Standardabw(a)


Anmerkung:dies ist das Quadrat des Standardfehlers des y-Schätzwertes an der Stelle x=0.
Das Quadrat des Standardfehlers des y-Schätzwertes an beliebigen Stellen x siehe unten.



                                                                                               Varianz(b)
                                                                                               Standardabw(b)




Berechnung der 2-seitigen Vertrauensintervalle der Modellparameter zum gegebenen Alpharisiko.

n=                          9
alpha                    0.05
b0=                   19383.6
b1=                    257.12
                                                                                                  14977



                                                                                                 216.70



Die beiden Modellparameter lassen sich mit voriger Information einfach gegen bestimmte Werte (z.B. gegen Null, dann beta_k=0))


                                                      b entsprechend


Für die Berechnung der Vertrauensintervalle der geschätzten Einzelwerte, von prognostizierten Einzelwerten und der gesa
benötigt man die Berechnung der jeweils zugehörigen Standardfehler.

4. Bestimmung der Vertrauensintervalle ursprünglicher Messwerte

Quadrat des Standardfehlers des y-Schätzwertes an einer bereits vorhandenen Stelle xk

                  1      xk  xmittel 2   yi  yi  schätz  2           Daraus ergibt sich das Vertrauensintervall (des Erwartungsw
                                          2 
sy  schätz                            
                  n
                           xi  xmittel i        n 2
  k
                                           
                         i                 


     85000

     75000                                                                                          x
                                                                                                            40
                                                                                                            50
     65000                                                                                                  60
                                                                                                            70
     55000                                                                                                  80
                                                                                                            90
                                                                                                           100
     45000                                                                                                 110
                                                                                                           120
                                                                                                           130
     35000                                                                                                 140
                                                                                                           150
                                                                                                           160
     25000                                                                                                 170
             40     60        80      100       120      140    160    180   200                           180
                                                                                                           190
                                                                                                           200


5. Bestimmung der Vertrauensintervalle Prognostizierter Werte
Prognosegenauigkeit des y-Wertes an einem noch nicht gemessenen x-Wert
--> Quadrat des Standardfehlers des (unbekannten) "wahren" y-Wertes an einer beliebigen Stelle x.
Dieser Standardfehler ist grösser als der Standardfehler für den y-Schätzwert, da letzterer ja auf der Geraden liegt und für ersteren


                                                                                           Daraus ergibt sich das Vertrauensintervall (de
  85000


                                                                                                x
  75000                                                                                                 40
                                                                                                        50
                                                                                                        60
  65000
                                                                                                        70
                                                                                                        80
  55000                                                                                                 90
                                                                                                       100
                                                                                                       110
  45000                                                                                                120
                                                                                                       130
                                                                                                       140
  35000
                                                                                                       150
                                                                                                       160
                                                                                                       170
  25000
          40             90               140             190                                          180
                                                                                                       190
                                                                                                       200


6. Bestimmung des Vertrauensintervalles der gesamten Regressionsgeraden
Dieses ist weiter als alle einzelnen Vertrauensintervalle der Schätzwerte zusammengenommen.
Dies ist sozusagen das Vertrauensintervall für die Geradengleichung.
Das folgend angegebene Vertrauensintervall ist also als Gleichung für alle Punkte zusammen zu sehen und sagt nichts aus über di
Diese wurden ja schon zuvor angegeben.




               Der Ausdruck unter der Wurzel ist die F-Verteilung. Die F-Verteilung ist Prüfverteilung für den Quotienten aus 2 Varia
               Die beiden Varianzen sind die Residuenvarianz und die durch das Modell erklärte Varianz.

                                                                                                x
                                                                                                        40
  80000
                                                                                                        50
                                                                                                        60
  70000                                                                                                 70
                                                                                                        80
                                                                                                        90
  60000                                                                                                100
                                                                                                       110
  50000                                                                                                120
                                                                                                       130
                                                                                                       140
  40000                                                                                                150
                                                                                                       160
  30000                                                                                                170
                                                                                                       180
                              190
20000                         200
        30   80   130   180
    100         110    120       130       140   150




                              60000


yi -yi schätz                 55000

           -410               50000
          -1438       Preis
            433
                              45000
          -2294
            -67
            861               40000
            933
            677               35000
           1306                       70         90    110   130   150
                                                       KW
(yi-yischätz)^2
   11469792.52




       Kontrolle
      11469793
                                226.2
                             1.38E-06 Die Residuenvarianz hat n-2 Freiheitsgrade,
                                      die erklärte Varianz nur einen.
se beiden Varianzen nicht unabhängig voneinander sind ( die eine enthält ja die andere)
 n (y also von x nicht abhängen würde), dann würden y-Werte nur mit dieser
Gerade derart gut (B= 0,97) genähert werden können.




n Modellparameter und dann für die Berechnung deren Vertrauensintervalle.




                                      Hier steht n-2 deshalb, weil a) Zur Varianzberechnung die
                                      Kenntnis des Mittelwertes erforderlich ist (also n-1 FG), b)
                                      genau eine erklärende Variable (x) herangezogen wurde.
                                      Also n-1-1 = n-2




                            3472525
                               1863




                        292.2372497
                               17.1




ebenen Alpharisiko.
                        < a_wahr <        23790



                        < b_wahr <        297.54



mmte Werte (z.B. gegen Null, dann beta_k=0)) wie folgt testen:




 prognostizierten Einzelwerten und der gesamten Regressionsgeraden




bt sich das Vertrauensintervall (des Erwartungswertes) für den Schätzwert an der Stelle x:

                                                                     alpha                   0.05       a    19383.6
                                                                     n                          9       b     257.12
                                                                     t[(n-2); 0,975]        2.365
                                                                     xquer                  106.1
                                                                                            1
                                                                     Standardfehler[Residuen] 280
                                                                     Summe[xi-xquer]^25606.88889

                          yi,schätz    yi,schätz un   yi,schätz ob
                           29668        26811.75897    32524.83094
                           32239        29757.00852    34721.94594
                           34811        32691.15254    36930.16647
                           37382        35607.36311    39156.32045
                           39953        38492.87161    41413.17651
                           42524        41323.32263    43725.09004
                           45095        44056.63742    46134.1398
                           47667        46645.44784    48687.69393
                           50238        49083.11626    51392.39007
                           52809        51412.3373     54205.53357
                           55380        53678.7676     57081.46782
                           57951        55910.34337    59992.2566
                           60522        58121.81121    62923.15331
                           63094        60320.98624    65866.34284
                           65665        62512.21708    68817.47655
                           68236        64698.06224    71773.99594
                           70807        66880.10677    74734.31596




zterer ja auf der Geraden liegt und für ersteren noch die Varianz der Residuen hinzukommt.


  Daraus ergibt sich das Vertrauensintervall (des Erwartungswertes) für den "wahren" Wert an der Stelle x:
                         yi,schätz     yi,wahr un       yi,wahr ob
                           29668       25506.37006      33830.21985
                           32239       28324.82886      36154.1256
                           34811       31115.50988      38505.80913
                           37382       33873.19596      40890.4876
                           39953       36592.38928      43313.65884
                           42524       39267.83679      45780.57588
                           45095       41895.25971      48295.51751
                           47667        44472.1205      50861.02127
                           50238       46998.15329      53477.35303
                           52809       49475.42268      56142.4482
                           55380       51907.88311      58852.35232
                           57951       54300.63911      61601.96087
                           60522       56659.18802      64385.77651
                           63094       58988.84227      67198.48681
                           65665       61294.38972      70035.30391
                           68236       63579.95452      72892.10366
                           70807       65848.98876      75765.43398


Regressionsgeraden

usammen zu sehen und sagt nichts aus über die Vertrauensintervalle der einzelnen Werte.




st Prüfverteilung für den Quotienten aus 2 Varianzen.                 F[2;7; 0,95]        4.737


                         yi,schätz    unten          oben
                           29668          25950           33387
                           32239          29008           35471
                           34811          32052           37570
                           37382          35072           39692
                           39953          38052           41854
                           42524          40961           44087
                           45095          43743           46448
                           47667          46337           48996
                           50238          48735           51741
                           52809          50991           54627
                           55380          53165           57595
                           57951          55295           60608
                           60522          57397           63648
                           63094          59484           66703
                           65665          61561           69769
68236   63631   72842
70807   65695   75919
150
       Ausgangsdaten
            Xi           Yi
        KW         Preis                                             Für eine nähere Erklärung der hier vorkommenden Be
Audi            74        38000                                      siehe das Exceltabellenblatt "Regression" in
Audi            92        41600
Audi           110        48100                                      Tiefergehende Ausführungen, mit der Excelfunktion R
Audi           142        53600                                      (anstelle Analyse Toolpak) befinden sich in
BMW             75        38600
BMW             85        42100
BMW            110        48600
BMW            125        52200
BMW            142        57200


        AUSGABE: ZUSAMMENFASSUNG

           Regressions-Statistik
                        0.98487835
        Multipler Korrelationskoeffizient
        Bestimmtheitsmaß0.96998537
                        0.96569756
        Adjustiertes Bestimmtheitsmaß
        Standardfehler 1280.05538
        Beobachtungen              9

        ANOVA
                     Freiheitsgrade (df)    Quadratsummen (SS)    Mittlere Quadratsumme (MS)
        Regression                  1                 370670207.5                 370670207.5
        Residue                     7                 11469792.52                 1638541.788
        Gesamt                      8                  382140000

                       Koeffizienten           Standardfehler                t-Statistik
        Schnittpunkt    19383.5659                     1863.471318                  10.40185897
        X Variable 1    257.118228                     17.09494807                  15.04059717
e nähere Erklärung der hier vorkommenden Begriffe
as Exceltabellenblatt "Regression" in  dieser Datei.

ehende Ausführungen, mit der Excelfunktion RGP
e Analyse Toolpak) befinden sich in     dieser Datei




                   Prüfgröße (F) F krit
                    226.219563 1E-06




                      P-Wert Untere 95% Obere 95% Untere 95,0% Obere 95,0%
                     1.6491E-05 14977 23789.97217 14977.15953 23789.9722
                     1.3795E-06 216.7 297.5413275 216.6951277 297.541327
                                                  Auch das ist ein Fehler:
                                                  Doppelte Ausgabe der Daten
Beispiel Quadratische Regression mit Excel Toolpak.


                Ausgangsdaten
             X1i           X2i                    Ylinear,i Yquadrat,i           100000
          KW         KW^2                       Preis                             90000
Audi              80                   6400           38000      37449
Audi              92                   8464           40000    37072.2            80000
Audi             110                  12100           46000      45309            70000
Audi             142                  20164           85000    86032.2
BMW               75                   5625           37000    38991.5            60000
BMW               85                   7225           38000    36721.5            50000
BMW              110                  12100           45000      45309
                                                                                  40000
BMW              125                  15625           58000    60241.5
BMW              142                  20164           90000    86032.2            30000
                                                                                          60



          AUSGABE: ZUSAMMENFASSUNG

                Regressions-Statistik
                               0.994861005
          Multipler Korrelationskoeffizient
          Bestimmtheitsmaß 0.989748418
                               0.986331225
          Adjustiertes Bestimmtheitsmaß
          Standardfehler       2412.360073
          Beobachtungen                     9

          ANOVA
                                        Quadratsummen (SS)
                         Freiheitsgrade (df)                         Prüfgröße (F)
                                                 Mittlere Quadratsumme (MS)            F krit
          Regression                       2 3371083113 1685541557 289.637774        1.0774E-06
          Residue                          6 34916886.7 5819481.12
          Gesamt                           8 3406000000

                            Koeffizienten Standardfehler t-Statistik      P-Wert    Untere 95%
          Schnittpunkt        159929.4785 20977.8674 7.62372434          0.00026556 108598.449
          X Variable 1       -2835.054251 398.272061 -7.11838597          0.0003866 -3809.59159
          X Variable 2        16.33606041 1.81213541      9.0148122       0.0001043 11.9019216
                                            Series1
                                            Series2




80        100        120       140    160




Obere 95% Untere 95,0% Obere 95,0%
 211260.508 108598.449 211260.508
-1860.51691 -3809.59159 -1860.51691
 20.7701993 11.9019216 20.7701993

								
To top