Xử lý thống kê bằng Excel

 được ghi nhận từ các thực nghiệm sau: 
Giống lúa 
Loại phân bón A B C 
1 
65 
68 
62 
69 
71 
67 
75 
75 
78 
2 
74 
79 
76 
72 
69 
69 
70 
69 
65 
3 
64 
72 
65 
68 
73 
75 
78 
82 
80 
4 
83 
82 
84 
78 
78 
75 
76 
77 
75 
 Hãy cho biết sự ảnh hưởng của loại phân bón, giống lúa trên năng suất, a = 0,01 
2) Điều tra mức tăng trưởng chiều cao của 1 loại cây trồng theo loại đất trồng và loại phân bón 
có kết quả: 
Loại đất 
Loại phân 1 2 3 
A 
5,5 
5,5 
6,0 
4,5 
4,5 
4,0 
3,5 
4,0 
3,0 
B 
5,6 
7,0 
7,0 
5,0 
5,5 
5,0 
4,0 
5,0 
4,5 
Hỏi có sự khác nhau của mức tăng trưởng chiều cao theo loại đất và loại phân bón ? 
a=0,05 
3) Nghiên cứu sản lượng bông (tạ/ha) theo mật độ trồng A và phân bón B thu được: 
Phân bón Mật độ trồng 
b1 b2 b3 b4 
a1 
16 
14 
21 
16 
19 
20 
23 
19 
19 
21 
22 
20 
20 
24 
21 
17 
a2 
17 
15 
17 
19 
19 
18 
18 
20 
21 
21 
22 
23 
20 
20 
22 
19 
a3 
18 
18 
19 
17 
20 
23 
21 
21 
22 
18 
21 
21 
25 
22 
21 
23 
Hỏi có sự khác nhau của sản lượng bông theo mật độ trồng, theo phân bón với mức a=0,05 
Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 
30 
V. TƯƠNG QUAN - HỒI QUY 
1) Tương quan (Correlation) 
§ Hệ số tương quan 
])([])([ 2222 åååå
ååå
--
-
=
iiii
iiii
yynxxn
yxyx
R 
§ Nếu R >0 thì X, Y tương quan thuận 
 Nếu R <0 thì X, Y tương quan nghịch 
§ Nếu R=0 thì X , Y không tương quan 
§ Nếu ïRï=1 thì X,Y có quan hệ hàm số bậc nhất. 
§ Nếu ïRï® 1 thì X, Y có tương quan chặt (tương quan mạnh) 
§ Nếu ïRï® 0 thì X, Y có tương quan không chặt (tương quan yếu) 
Ví dụ: Khảo sát mới quan hệ giữa nhiệt độ trung bình với doanh số bán kem theo bảng thống 
kê sau: 
Tháng Nhiệt độ trung bình Doanh số bán kem Doanh số bán đậu 
4 22 1250 3254 
5 27 3297 3072 
6 30 5576 3348 
7 34 8109 3118 
8 38 9645 3211 
9 32 7726 3276 
10 25 2958 3081 
§ Nhập và xử lý dữ liệu: chọn menu Tools/Data Analysis/Correlation 
§ Kết quả 
 Column 1 Column 2 Column 3 
Column 1 1 
Column 2 0.985572 1 
Column 3 0.127653 0.184818 1 
Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 
31 
Vì R12=0,9856 chứng tỏ giữa nhiệt độ (Column 1) và doanh số bán kem (Column 2) có mối 
quan hệ rất chặt chẽ với nhau và có tương quan thuận. 
2) Hồi quy (Regression) 
a) Hồi quy đơn tuyến tính 
§ Phương trình hồi quy tuyến tính: xaybrabxay
x
y
S
S
x -==+= ,, 
§ Kiểm định hệ số a,b 
 * Giả thiết H0: Hệ số hồi quy không có ý nghĩa (= 0 ) 
 H1: Hệ số hồi quy có ý nghĩa (¹ 0 ) 
 * Trắc nghiệm t < ta,n-2 : chấp nhận H0 
§ Kiểm định phương trình hồi quy 
 * Giả thiết H0:”Phương trình hồi quy tuyến tính không thích hợp” 
 H1: ”Phương trình hồi quy tuyến tính thích hợp” 
 * Trắc nghiệm F < Fa,1,n-2 : chấp nhận H0 
Ví dụ: Số liệu về doanh số bán hàng (Y) và chi phí chào hàng (X) của một số công ty, có kết 
quả sau: 
X (triệuđ/năm) 12 10 11 8 15 14 17 16 20 18 
Y (tỷ đ/năm) 2 1,8 1,8 1,5 2,2 2,6 3 3 3,5 3 
 Xác định phương trình hồi quy tuyến tính 
Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 
32 
SUMMARY OUTPUT 
Regression Statistics 
Multiple R 0.963150954 
R Square 0.927659761 
Adjusted R Square 0.918617231 
Standard Error 0.191227589 
Observations 10 
ANOVA 
 df SS MS F Significance F 
Regression 1 3.751456073 3.751456 102.5885 7.71522E-06 
Residual 8 0.292543927 0.036568 
Total 9 4.044 
 Coefficients Standard Error t Stat P-value Lower 95% 
Intercept 0.053017571 0.243302295 0.217908 0.832956 -0.50803889 
X 0.169289534 0.016714013 10.1286 7.72E-06 0.130746927 
Þ xy x 1693,0053,0 += 
· Hệ số hồi quy: 0.832956 > 0,05 : hệ số tự do có ý nghĩa. 
 7.72E-06 < 0,05 : hệ số của x không có ý nghĩa. 
· Phương trình hồi quy tuyến tính này không thích hợp vì 7.71522E-06 < 0,05. 
b) Hồi quy đa tuyến tính 
§ Phương trình hồi quy đa tuyến tính: nnx xbxbby +++= ...110 
§ Kiểm định hệ số bj 
 * Giả thiết H0: Các hệ số hồi quy không có ý nghĩa (bj= 0 ) 
 H1: Có ít nhất vài hệ số hồi quy có ý nghĩa (bj ¹ 0 ) 
 * Trắc nghiệm t < ta,n-2 : chấp nhận H0 
§ Kiểm định phương trình hồi quy 
 * Giả thiết H0:”Phương trình hồi quy không thích hợp” 
 H1: ”Phương trình hồi quy thích hợp với ít nhất vài bj” 
 * Trắc nghiệm F < Fa,1,n-2 : chấp nhận H0 
Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 
33 
Ví dụ: Người ta đã dùng ba mức nhiệt độ gồm 105 , 120 và 135 0C kết hợp với ba khoảng 
thời gian là 15 , 30 và 60 phút để thực hiện một phản ứng tổng hợp. các hiệu suất 
của phản ứng (%)được trình bày trong bảng sau đây: 
Thời gian (ph) 
X1 
Nhiệt độ (0C) 
X2 
Hiệu suất (%) 
Y 
15 105 1,87 
30 105 2,02 
60 105 3,28 
15 120 3,05 
30 120 4,07 
60 120 5,54 
15 135 5,03 
30 135 6,45 
60 135 7,26 
Hãy cho biết yếu tố nhiệt độ và hoặc yếu tố thời gian có liên quan tuyến tính với hiệu suất 
của phản ứng tổng hợp? Nếu có thì ở điều kiện nhiệt độ 115 0C trong 50 phút thì hiệu suất 
phản ứng sẽ là bao nhiêu? 
· Nhập dữ liệu: 
· 1101 XbbY X += 
Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 
34 
SUMMARY OUTPUT 
Regression Statistics 
Multiple R 0.462512069 
R Square 0.213917414 
Adjusted R Square 0.101619901 
Standard Error 1.811191587 
Observations 9 
ANOVA 
 df SS MS F Significance F 
Regression 1 6.24891746 6.248917 1.904917 0.209994918 
Residual 7 22.96290476 3.280415 
Total 8 29.21182222 
 Coefficients Standard Error t Stat P-value Lower 95% 
Intercept 2.726666667 1.280705853 2.129034 0.070771 -0.301719287 
X1 0.044539683 0.032270754 1.380187 0.209995 -0.031768471 
Phương trình hồi quy: 104454,07267,21 XY X += không thích hợp vì 0.209994918 > 0,05 
Nghĩa là : Hiệu suất Y không có liên quan tuyến tính với yếu tố thời gian X1 
· 2202 XbbY X += 
Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 
35 
SUMMARY OUTPUT 
Regression Statistics 
Multiple R 0.873933544 
R Square 0.76375984 
Adjusted R Square 0.730011246 
Standard Error 0.99290379 
Observations 9 
ANOVA 
 df SS MS F Significance F 
Regression 1 22.31081667 22.31082 22.63086 0.002066188 
Residual 7 6.901005556 0.985858 
Total 8 29.21182222 
 Coefficients Standard Error t Stat P-value Lower 95% 
Intercept -11.14111111 3.25965608 -3.41788 0.011168 -18.84896742 
X2 0.128555556 0.027023418 4.757191 0.002066 0.064655371 
Phương trình hồi quy: 11286,01411,111 XY X +-= này thích hợp vì 0.002066188 < 0,05 
Nghĩa là: Hiệu suất Y có liên quan tuyến tính với yếu tố nhiệt độ X2. 
· 21 2102,1 XbXbbY XX ++= 
Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 
36 
SUMMARY OUTPUT 
Regression Statistics 
Multiple R 0.988776 
R Square 0.977677 
Adjusted R Square 0.970236 
Standard Error 0.329669 
Observations 9 
ANOVA 
 df SS MS F Significance F 
Regression 2 28.55973413 14.27987 131.3921 1.11235E-05 
Residual 6 0.652088095 0.108681 
Total 8 29.21182222 
 Coefficients Standard Error t Stat P-value Lower 95% 
Intercept -12.7 1.101638961 -11.5283 2.56E-05 -15.3956154 
X1 0.04454 0.005873842 7.582718 0.000274 0.030166899 
X2 0.128556 0.008972441 14.32782 7.23E-06 0.106600767 
Phương trình hồi quy: 21286,0104454,07,122,1 XXY XX ++-= này thích hợp 
vì 1.11235E-05 < 0,05 
Nghĩa là:Hiệu suất Y có liên quan tuyến tính với thời gian X1 và nhiệt độ X2. 
· khi X1=50 , X2=115 ta dự đoán: 
Intercept -12.7 Dự đoaùn hiệu suất Y: 
X1 0.04454 4.31094 
X2 0.128556 
Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 
37 
Bài tập 
1. Cho Y là nhu cầu thịt bò (đơn vị 100 tấn) của 12 tháng liên tiếp (X) trong một khu dân cư : 
X: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12 
Y: 15, 18, 18, 16, 14, 18, 20, 21, 19, 20, 24, 26. 
Hãy ước lượng hàm hồi quy tuyến tính đơn, dự báo nhu cầu thịt bò cho 3 tháng tiếp theo. 
Đáp số : y = 0.793706 x + 13.92424. 
2. Trong 10 tháng liên tiếp lượng hàng bán ra của một công ty rất thấp, sau đó công ty tung ra thị 
trường một sản phẩm mới và nhận thấy lượng hàng bán ra tăng theo hàm mũ. Số đơn vị hàng 
bán ra (Y) trong 6 tháng tiếp theo (X) cho trong bảng sau: 
Hãy ước lượng hàm hồi quy mũ và dự báo lượng hàng bán ra trong các tháng 17, 18, 19, 20 
(dùng hàm Growth). Đáp số : y = 495.3048 +1.463276x . 
3. Tính hàm hồi quy tuyến tính bội với số liệu cho trong bảng duới 
trong đó Y là thu nhập quốc dân, X1 là sản lượng điện, X2 là sản lượng than, X3 là sản lượng 
lương thực, X4 là sản lượng thép. Dùng hai phương pháp: dùng hàm Linest và lệnh Tools / Data 
Analysis. Dự báo Y với X = (5.2, 65.1, 275.3, 37.8). 
Đáp số: dự báo Y =751.79289. 
4. Bảng bên cho số liệu về doanh thu (Y), chi phí cho quảng cáo (X1), tiền lương của nhân viên tiếp 
thị (X2) của 12 công ty tư nhân, đơn vị là 1 triệu đồng. Xây dựng hàm hồi quy tuyến tính bội Y 
phụ thuộc vào X1, X2. 
Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 
38 
Để ước lượng hàm hồi quy ta dùng hàm mảng Linest như sau: đánh dấu khối vùng ô B19: D23, 
nhập công thức =LINEST(A2 : A13, B2 : C13, True, True), ấn Ctrl + Shift +Enter, kết quả ta 
được 12 số: 
Tiếp theo, cho các bộ giá trị mới x1, x2 trong khối ô B15 : C17, cần dự báo các giá trị y được 
tính theo (2) trong khối ô D15 :D17. Thao tác tính: đánh dấu khối vùng ô D15:D17, nhập công 
thức = Trend(a2: a13,b2: c13, b15: c17, True), ấn Ctrl + Shift +Enter 
5. Tính hàm hồi quy của y (sản lựơng nông nghiệp) phụ thuộc vào x (lựơng phân bón). 
Công thức trong ô D2 là = Slope(a2:a6, b2:b6), công thức trong ô E2 là =Intercept(a2:a6, 
b2:b6), công thức trong ô E5 là =Forecast(d5, a2:a6, b2:b6) để dự báo y với x = 1612. 
 y = mx + b 
Do đó tất cả các hàm và lệnh đã trình bày với hồi quy tuyến tính bội cũng đúng với hồi quy 
tuyến tính đơn. Song đối với hồi quy tuyến tính đơn có thêm ba hàm mới. 
− Hàm Slope(known_y's, known_x's) ước lượng giá trị m của phương trình (3). 
− Hàm Intercept(known_y's, known_x's) ước lượng giá trị b của (3). 
− Hàm Forecast( x, known_y's, known_x's ): dự đoán y theo phương trình (3) với giá trị x 
biết trước.