Příklad: Jednoduchá/Vícenásobná lineární regrese: Excel Statistická terminologie Y X1 X2 X3 X4 Prodejna Roční tržby tis. Kč Poč. kolemjdoucích/hod. Velikost prodejny m2 Prům. plat prodavačů/měs. Přítomnost konkurence v místě Násobné R = R - koeficient korelace 1 7800 12 90 10.0 1 Hodnota spolehlivosti R = R2 - koeficient determinace 2 10500 20 150 17.1 0 Nastavená hodnota spolehlivosti R = R2adj - upravený koeficient determinace 3 5700 11 100 10.5 1 Chyba stř. hodnoty = s2 - směrodatná chyba (odhad směrodatné odchylky náhodné složky) 4 12000 30 180 20.8 0 5 8100 15 120 12.4 1 6 9600 17 90 15.7 1 7 12900 27 200 23.2 1 8 6600 13 100 12.1 1 9 19500 55 320 26.3 0 10 15600 45 220 24.8 0 11 11400 29 170 20.5 0 12 9000 15 145 13.8 1 13 10800 24 170 16.2 0 14 9900 22 130 15.4 1 15 7200 11 120 13.1 1 16 10560 16 140 14.6 1 17 11280 18 150 15.9 0 18 11700 20 190 20.5 0 19 12300 23 190 21.3 1 20 10320 31 170 14.3 1 21 8040 16 130 12.6 1 22 8760 19 140 14.2 1 23 10920 21 170 17.4 1 24 11940 24 160 21.1 1 25 12360 29 170 22.1 1 Úkoly: "•Vyjádřete (lineární) závislost tržeb na 4 prediktorech (poč. kolemjdoucích, velikost prodejny, průměrný plat prodavačů, přítomnost konkurence) " "•Na základě modelu prognózujte tržby nové prodejny s těmito parametry: počet kolemjdoucích = 20, velikost prodejny = 200, průměrný plat prodavačů = 15, přítomnost konkurence = 1. " ##### Sheet/List 2 ##### Jednoduchá lineární regrese Zjistěte závislost ročních tržeb na počtu kolemjdoucích (!?) Zjistěte závislost ročních tržeb na velikosti prodejny (!?) Zjistěte závislost velikosti prodejny na počtu kolemjdoucích (!?) Zjistěte závislost průměrného platu prodavačů na velikosti tržeb (!?) x y Poč. kolemjdoucích/hod. Roční tržby tis. Kč Velikost prodejny m2 12 7800 90 20 10500 150 11 5700 100 30 12000 180 15 8100 120 17 9600 90 27 12900 200 13 6600 100 55 19500 320 45 15600 220 29 11400 170 15 9000 145 24 10800 170 22 9900 130 11 7200 120 16 10560 140 18 11280 150 20 11700 190 23 12300 190 31 10320 170 16 8040 130 19 8760 140 21 10920 170 24 11940 160 29 12360 170 xi yi xi.yi xi^2 Yi ST Sy 12 7800 20 10500 11 5700 30 12000 15 8100 17 9600 27 12900 13 6600 55 19500 45 15600 29 11400 15 9000 24 10800 22 9900 11 7200 16 10560 18 11280 20 11700 23 12300 31 10320 16 8040 19 8760 21 10920 24 11940 29 12360 součet průměr ##### Sheet/List 3 ##### Maticové násobení: Funkce: SOUČIN.MATIC 3 sloupce 2 sloupce 2 sloupce 1 2 3 3 4 4 řádky 4 5 6 3 řádky 2 5 = 4 řádky 7 8 9 1 6 10 11 12 A = (4x3) """krát""" B =(3x2) = (4x2) AB BA nelze 10 2 3 A= 4 5 6 A-1= A.A-1 = 8 10 11.99999 ##### Sheet/List 4 ##### 1 12 90 10.0 1 7800 1 20 150 17.1 0 10500 1 11 100 10.5 1 FTF = 5700 1 30 180 20.8 0 12000 1 15 120 12.4 1 8100 1 17 90 15.7 1 9600 1 27 200 23.2 1 12900 1 13 100 12.1 1 6600 1 55 320 26.3 0 19500 1 45 220 24.8 0 (FT.F)-1 = 15600 1 29 170 20.5 0 11400 1 15 145 13.8 1 9000 1 24 170 16.2 0 = F 10800 = y 1 22 130 15.4 1 9900 1 11 120 13.1 1 7200 1 16 140 14.6 1 10560 1 18 150 15.9 0 11280 1 20 190 20.5 0 11700 1 23 190 21.3 1 12300 1 31 170 14.3 1 10320 1 16 130 12.6 1 absolut. Člen 8040 1 19 140 14.2 1 Poč. kolemjdoucích/hod. 8760 1 21 170 17.4 1 (FTF)-1.FTy = Velikost prodejny m2 10920 1 24 160 21.1 1 Prům. plat prodavačů/měs. 11940 1 29 170 22.1 1 Přítomnost konkurence v místě 12360 (FTF).FT = VÝSLEDEK Regresní statistika Násobné R 0.969282179 Hodnota spolehlivosti R 0.939507942 Nastavená hodnota spolehlivosti R 0.927409531 Chyba stř. hodnoty 780.5521634 Pozorování 25 ANOVA Rozdíl SS MS F Významnost F Regrese 4 189250030.4 47312507.6 77.65547903 6.82042E-12 Rezidua 20 12185233.6 609261.6798 Celkem 24 201435264 Koeficienty Chyba stř. hodnoty t stat Hodnota P Dolní 95% Horní 95% "Dolní 95,0%" "Horní 95,0%" Hranice 1642.641452 932.470667 1.761601207 0.093417207 -302.4573713 3587.740276 -302.4573713 3587.740276 Soubor X 1 81.89852789 36.85457485 2.222207914 0.037967472 5.0212676 158.7757882 5.0212676 158.7757882 Soubor X 2 19.89343475 8.495773929 2.34156828 0.0296686 2.171569106 37.61530039 2.171569106 37.61530039 Soubor X 3 241.0024633 70.48227096 3.419334537 0.002717405 93.97909072 388.0258359 93.97909072 388.0258359 Soubor X 4 -171.8031569 399.2127029 -0.430354935 0.671539317 -1004.545876 660.9395622 -1004.545876 660.9395622 ##### Sheet/List 5 ##### VÝSLEDEK Regresní statistika Násobné R 0.969282179 Hodnota spolehlivosti R 0.939507942 Nastavená hodnota spolehlivosti R 0.927409531 Chyba stř. hodnoty 780.5521634 Pozorování 25 ANOVA Rozdíl SS MS F Významnost F Regrese 4 189250030.4 47312507.6 77.65547903 6.82042E-12 Rezidua 20 12185233.6 609261.6798 Celkem 24 201435264 Koeficienty Chyba stř. hodnoty t stat Hodnota P Dolní 95% Horní 95% "Dolní 95,0%" "Horní 95,0%" Hranice 1642.6 932.470667 1.761601207 0.0934 -302.4573713 3587.740276 -302.4573713 3587.740276 Poč. kolemjdoucích/hod. 81.9 36.85457485 2.222207914 0.0380 5.0212676 158.7757882 5.0212676 158.7757882 Velikost prodejny m2 19.9 8.495773929 2.34156828 0.0297 2.171569106 37.61530039 2.171569106 37.61530039 Prům. plat prodavačů/měs. 241.0 70.48227096 3.419334537 0.0027 93.97909072 388.0258359 93.97909072 388.0258359 Přítomnost konkurence v místě -171.8 399.2127029 -0.430354935 0.6715 -1004.545876 660.9395622 -1004.545876 660.9395622 "Y = 1642,6 + 81,9*X1 + 19,9*X2 + 241*X3 " 9231.694457 REZIDUA Pozorování Očekávané Roční tržby tis. Kč Rezidua 1 6654.05439 1145.94561 2 10385.76934 114.2306553 3 6891.591442 -1191.591442 4 12693.26678 -693.2667803 5 8074.958928 25.04107161 6 8437.261071 1162.738929 7 13252.04265 -352.0426467 8 7440.992439 -840.9924387 9 18851.32439 648.6756098 10 15681.49194 -81.49194176 11 12340.13317 -940.133166 12 8909.698246 90.3017543 13 10894.32993 -94.3299342 14 9570.190361 329.8096389 15 7916.066541 -716.0665412 16 9084.931571 1475.068429 17 9932.769333 1347.230667 18 12000.91511 -300.9151099 19 12267.60951 32.39049266 20 10837.91179 -517.9117922 21 8403.992296 -363.9922964 22 9234.226169 -474.2261689 23 10766.03415 153.9658503 24 11704.5045 235.4954998 25 12553.93395 -193.9339504 ##### Sheet/List 6 ##### Roční tržby tis. Kč Poč. kolemjdoucích/hod. Velikost prodejny m2 Prům. plat prodavačů/měs. Přítomnost konkurence v místě VÝSLEDEK 7800 12 90 10.0 1 10500 20 150 17.1 0 Regresní statistika 5700 11 100 10.5 1 Násobné R 0.969282179 = R - koeficient korelace 12000 30 180 20.8 0 Hodnota spolehlivosti R 0.939507942 = R2 - koeficient determinace 8100 15 120 12.4 1 Nastavená hodnota spolehlivosti R 0.927409531 = R2adj - upravený koeficient determinace 9600 17 90 15.7 1 Chyba stř. hodnoty 780.5521634 = s2 - směrodatná chyba (odhad směrodatné odchylky náhodné složky) 12900 27 200 23.2 1 Pozorování 25 6600 13 100 12.1 1 19500 55 320 26.3 0 ANOVA 15600 45 220 24.8 0 Rozdíl SS MS F Významnost F 11400 29 170 20.5 0 Regrese 4 189250030.4 47312507.6 77.65547903 6.82042E-12 9000 15 145 13.8 1 Rezidua 20 12185233.6 609261.6798 10800 24 170 16.2 0 Celkem 24 201435264 9900 22 130 15.4 1 7200 11 120 13.1 1 Koeficienty Chyba stř. hodnoty t stat Hodnota P Dolní 95% Horní 95% "Dolní 95,0%" "Horní 95,0%" 10560 16 140 14.6 1 Hranice 1642.641452 932.470667 1.761601207 0.093417207 -302.4573713 3587.740276 -302.4573713 3587.740276 11280 18 150 15.9 0 Poč. kolemjdoucích/hod. 81.89852789 36.85457485 2.222207914 0.037967472 5.0212676 158.7757882 5.0212676 158.7757882 11700 20 190 20.5 0 Velikost prodejny m2 19.89343475 8.495773929 2.34156828 0.0296686 2.171569106 37.61530039 2.171569106 37.61530039 12300 23 190 21.3 1 Prům. plat prodavačů/měs. 241.0024633 70.48227096 3.419334537 0.002717405 93.97909072 388.0258359 93.97909072 388.0258359 10320 31 170 14.3 1 Přítomnost konkurence v místě -171.8031569 399.2127029 -0.430354935 0.671539317 -1004.545876 660.9395622 -1004.545876 660.9395622 8040 16 130 12.6 1 8760 19 140 14.2 1 10920 21 170 17.4 1 11940 24 160 21.1 1 REZIDUA 12360 29 170 22.1 1 Pozorování Očekávané Roční tržby tis. Kč Rezidua 1 6654.05439 1145.94561 2 10385.76934 114.2306553 3 6891.591442 -1191.591442 4 12693.26678 -693.2667803 5 8074.958928 25.04107161 6 8437.261071 1162.738929 7 13252.04265 -352.0426467 8 7440.992439 -840.9924387 9 18851.32439 648.6756098 10 15681.49194 -81.49194176 11 12340.13317 -940.133166 12 8909.698246 90.3017543 13 10894.32993 -94.3299342 14 9570.190361 329.8096389 15 7916.066541 -716.0665412 16 9084.931571 1475.068429 17 9932.769333 1347.230667 18 12000.91511 -300.9151099 19 12267.60951 32.39049266 20 10837.91179 -517.9117922 21 8403.992296 -363.9922964 22 9234.226169 -474.2261689 23 10766.03415 153.9658503 24 11704.5045 235.4954998 25 12553.93395 -193.9339504