Bài giảng Phân tích dữ liệu khách hàng bằng chương trình MS-Excel - Chương 4: Áp dụng MS-Excel trong phân tích tương quan và hồi quy - Nguyễn Đình Huy

Nội dung text: Bài giảng Phân tích dữ liệu khách hàng bằng chương trình MS-Excel - Chương 4: Áp dụng MS-Excel trong phân tích tương quan và hồi quy - Nguyễn Đình Huy

1. 36 Chương 6 Chương 4 ÁP DỤNG MS-EXCEL TRONG PHÂN TÍCH TƯƠNG QUAN VÀ HỒI QUY  Phân tích tương quan  Phân tích hồi quy Đơn giản Đa tham số BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
2. 38 Chương 6 6.2.1 Nhập dữ liệu vào bảng tính 6.2.3 Áp dụng “Correlation” a- Nhấp lần lượt đơn lệnh Tools và lệnh Data Analysic b- Chọn phương trình Correlation trong hợp thoại Data Analysic rồi nhấp nút OK. c- Trong hợp Correlation, lần lượt ấn định các chi tiết: Phạm vi đầu vào (Input Range), Cách xắp xếp theo hàng hay cột (Group By), Nhãn dữ liệu (Labels Fisrt Row/Column), Phạm vi đầu ra (Output Range) Hộp thoại Correlation Kết quả Các hệ số tương quan: R (ẩm/thời gian) = 0,97; R(nhiệt/thời gian) = 0,97 và R (ẩm / nhiệt) = 0,95 BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
3. 40 Chương 6 C- HỒI QUY TUYẾN TÍNH ĐƠN GIẢN 6.5 Phương trình tổng quát Y: biến số phụ thuộc Y|0X B BX (dependent / reponse variable) B0 Y BX X: là biến số độc lập XYXYNi i i i / (independent / predictor variable) B XXi B0 và B là các hệ số hồi quy (regression coefficients) Bảng ANOVA Nguồn Bậc Tổng số bình Bình phương Giá trị sai số tự do phương trung bình thống kê MSR Hồi quy 1 SSR () Y' Y ' 2 MSR = SSR F  i MSE '2 Sai số N − 2 SSE () Yii Y MSE = SSE/(N-2) 2 SST () Yi Y Tổng cộng N −1 = SSR + SSE Giá trị thống kê Giá trị R bình phương (R square): SSR R (100R2: % của biến đổi trên Y được giải thích bởi X) SST Độ lệch chuẩn (Standard Error): 1 (Sự phân tán của dữ liệu càng ít thì giá SYY ()'2 N 2  ii trị của S càng gần zero) Trắc nghiệm thống kê Đối với một phương trình hồi quy, , ý nghĩa thống kê của các hệ số Bi (B0 hay B) được đánh giá bằng trắc nghiệm t (phân phối Student) trong khi tính chất thích hợp của phương trình Y|X f() X được đánh giá bằng trắc nghiệm F (phân bố Fischer) Trắc nghiệm t - Giả thuyết: BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
4. 42 Chương 6 D- HỒI QUY TUYẾN TÍNH ĐA THAM SỐ Trong phương trình hồi quy tuyến tính đa tham số biến số phụ thuộc Y có liên quan đến k biến số độc lập Xi (i = 1,2, k) thay vì chỉ có một như trong hồi quy tuyến tính đơn giản. Phương trình tổng quát : YBBXBXBX |X01 , X , , Xk 0 1 1 2 2 k k Phương trình hồi quy đa tham số có thể được trình bày dưới dạng ma trận: Bảng ANOVA Nguồn Bậc Tổng số bình Bình phương Giá trị thống kê sai số tự do phương trung bình MSR Hồi quy k SSR MSR = SSR/k F MSE Sai số N – k – 1 SSE MSE = SSE/(N-k-1) Tổng cộng N – 1 SST= SSR + SSE Giá trị thống kê: Giá trị R bình phương: Giá trị R2 được hiệu chỉnh (Adjusted R Square) SSR kF R2 SST( N k 1) kF (R2 0,81 là khá tốt) Giá trị R2 được hiệu chỉnh (Adjusted R square): (N 1) R22 k k (1 R ) RR22 ii N k 1 ( N k 1) 2 2 ( Rii sẽ trở nên âm hay không xác định nếu R hay N nhỏ) BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
5. 44 Chương 6 Sử dụng “Regression” Nhấn lần lượt đơn lệnh Tools và lệnh Data Analysis. Chọn chương trình Regression trong hộp thoại Data Analysis rồi nhấp OK. Trong hộp thoại Regression, lần lượt ấn định các chi tiết: Phạm vi của biến số Y (Input Y Range). Phạm vi của biến số X (Input Y Range) Nhãn dữ liệu (Labels) Mức tin cậy (Confidence Level) Tọa độ đầu ra (Output Range) Và một số tùy chọn khác như đường hồi quy (Line Fit Plots), biểu thức sai số (Residuals Plots) Hộp thoại Regression BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
6. 46 Chương 6 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.84897293 X2 0.128555556 0.027023418 4.757191 0.002066 0.064655325 t0 = 3,418 < t0,05 = 2,365 (Hay PV 0,011 0,05) Bác bỏ giả thuyết H0. t2 = 4,757 < t0,05 = 2,365 (Hay PV 0,00206 0,05) Bác bỏ giả thuyết H0. FF 22,631 0,05 5,590 (Hay FS 0,00206 0,05) Bác bỏ giả thuyết H0. Vậy cả hai hệ số -11,14(B0) và 0,13(B2) của phương trình hồi quy YX 11,14 0,13 |2X2 đều có ý nghĩa thống kê. Nói một cách khác, phương trình hồi quy này thích hợp. Kết luận: Yếu tố nhiệt độ có liên quan tuyến tính với hiệu suất của phản ứng tổng hợp. Phương trình hồi quy Y f(,) X X |XX12 , 1 2 YXX 12,70 0,04 0,13 |XX12 , 1 2 (R2 = 0,97; S=0,33) BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
7. 48 Chương 6 Muốn dự đoán hiệu suất của phản ứng bằng phương trình hồi quy YXX 12,70 0,04 0,13 , bạn chỉ cần chọn một ô, thí dụ B21, sau đó nhập hàm và |XX12 , 1 2 được kết quả như sau: B21  = B17 + B18 * 50 + B19 * 115 A B C D 17 Interrcept -12,7 1,101638961 -11,52827782 18 X1 0,044539683 0,005873842 7.582717626 19 X2 0,128555556 0,008972441 14,32782351 20 21 Dự đoán 4,310873016 Ghi chú: B17 tọa độ của B0, B18 tọa độ của B1, B19 tọa độ của B2, 50 là giá trị của X1(thời gian) và 115 là giá trị của X2(nhiệt độ). PHỤ LỤC: Bảng giá trị tới hạn dùng trong trắc nghiệm loại giá trị bất thường: Giá trị thống kê Số trường hợp khảo Trị số tới hạn G1 sát GP (P=0,01) N N=37 3 0,976 4 0,846 YY21 5 0,729 G1 YYN 1 6 0,644 7 0,586 N=813 8 0,780 9 0,725 10 0,678 YY G 31 11 0,638 2 YY N 11 12 0,605 13 0,578 N=1424 14 0,602 15 0,579 16 0,559 17 0,542 18 0,527 YY31 19 0,514 G3 YYN 21 20 0,502 21 0,491 22 0,481 23 0,472 24 0,464 BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy