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 3: Áp dụng MS-Excel trong quy hoạch thực nghiệm và phân tích phương sai - 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 3: Áp dụng MS-Excel trong quy hoạch thực nghiệm và phân tích phương sai - Nguyễn Đình Huy

1. 23 Chương 5 Chương 3 ÁP DỤNG MS-EXCEL TRONG QUY HOẠCH THỰC NGHIỆM VÀ PHÂN TÍCH PHƯƠNG SAI  Phân tích phương sai một yếu tố  Phân tích phương sai hai yếu tố Không lặp Có lặp  Phân tích phương sai ba yếu tố BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
2. 25 Chương 5 MSF * Giá trị thống kê: F MSE * Biện luận: Nếu F F (k 1;N k) Chấp nhận giả thuyết H0. 5.2 Áp dụng MS-EXCEL Thí dụ 12: Hàm lượng alcaloid (mg) trong một loại dược liệu được thu hái từ ba vùng khác nhau được trình bày trong bảng sau: Vùng I Vùng II Vùng III 7,5 5,8 6,1 6,8 5,6 6,3 7,1 6,1 6,5 7,5 6,0 6,4 6,8 5,7 6,5 6,6 6,3 7,8 Hàm lượng alcaloid có khác nhau theo vùng? Nhập dữ liệu vào bảng tính A B C 1 Vùng I Vùng II Vùng III 2 7.5 5.8 6.1 3 6.8 5.6 6.3 4 7.1 6.1 6.5 5 7.5 6.0 6.4 6 6.8 5.7 6.5 7 6.6 6.3 8 7.8 Áp dụng “Anova: Single Factor” a. Nhấp lần lượt đơn lệnh Tools và lệnh Data Analysis. b. Chọn chương trình Anova: Single Factor trong hộp thoại Data Analysis rồi nhấp nút OK. c. Trong hộp thoại Anova: Single Factor. lần lượt ấn định: - Phạm vi đầu vào (Input Range) - Cách sắp xếp theo hàng hay cột (Group By) - Nhãn dữ liệu (Labels in Fisrt Row/Column) BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
3. 27 Chương 5 B- PHÂN TÍCH PHƯƠNG SAI HAI YẾU TỐ (KHÔNG LẶP) 5.3 Khái niệm thống kê Sự phân tích này nhằm đánh giá sự ảnh hưởng của hai yếu tố trên các giá trị quan sát Yij (i = 1. 2. r: yếu tố A; j = 1. 2. . c: yếu tố B). Mô hình Yếu tố B Tổng Trung Yếu tố A 1 2 c cộng bình 1 Y11 Y12 Y1c Y1. Y1 2 Y21 Y22 Y2c Y2. Y2 r Yr1 Yr2 Yrc Yr Yr Tổng cộng T.1 T.2 Tc T Trung bình Y.1 Y.2 Y.c Y Bảng ANOVA Nguồn Bậc tự Tổng số bình Bình phương Giá trị sai số do phương trung bình thống kê (r-1) r T2 T2 SSB MSB Yếu tố A SSB i MSB FR  c (r 1) MSE (Hàng) i 1 (c-1) c 2 2 SSF MSF Tj T Yếu tố B SSB MSF FC  r rc (c 1) MSE (Cột) j 1 SSE = SST – (SSF + (r-1)(c-1) Sai số SSB) (rc-1) r c 2 Tổng 2 T SST  Yij cộng i 1 j 1 r Trắc nghiệm * Giả thuyết: H0: 1 2 k “Các giá trị trung bình bằng nhau” H1: i  j “Ít nhất có hai giá trị trung bình khác nhau” MSB MSF * Giá trị thống kê: F và F R MSE C MSE BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
4. 29 Chương 5 Hình 5.2: Hộp thoại Anova: Two-Factor Without Replicatio Anova: Two-Factor Without Replication SUMMARY Count Sum Average Variance A 5 82.6 16.52 21.537 B 5 90 18 10.495 C 5 88.9 17.78 5.107 D 5 90.6 18.12 11.842 E 5 92 18.4 12.29 I 5 68.1 13.62 1.337 II 5 89.2 17.84 0.908 III 5 88 17.6 13.885 IV 5 111.1 22.22 1.157 V 5 87.7 17.54 0.263 ANOVA Source of Variation SS df MS F P-value F crit Rows 10.6736 4 2.6684 0.717234706 0.59240848 3.00691728 Columns 185.5576 4 46.3894 12.46892807 8.5462E-05 3.00691728 Error 59.5264 16 3.7204 Total 255.7576 24 Kết quả và biện luận FR = 0.717 F0.05 = 3.007 Bác bỏ giả thuyết H0 (Dung môi) Vậy chỉ có dung môi ảnh hưởng đến kết quả chiết xuất. BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
5. 31 Chương 5 Anova: Two-Factor With Replication SUMMARY Nam Trung Bắc Total Mùa khô Count 3 3 3 9 Sum 7.3 6.5 9.8 23.6 Average 2.433333 2.166667 3.266667 2.622222 Variance 0.003333 0.003333 0.013333 0.251944 Mùa mưa Count 3 3 3 9 Sum 7.6 6.8 10.4 24.8 Average 2.533333 2.266667 3.466667 2.755556 Variance 0.003333 0.003333 0.003333 0.300278 Total Count 6 6 6 Sum 14.9 13.3 20.2 Average 2.483333 2.216667 3.366667 Variance 0.005667 0.005667 0.018667 ANOVA Source of Variation SS df MS F P-value F crit Sample 0.08 1 0.08 16 0.001762 4.747225 Columns 4.347778 2 2.173889 434.7778 6.36E-12 3.885294 Interaction 0.01 2 0.005 1 0.396569 3.885294 Within 0.06 12 0.005 Total 4.497778 17 Kết quả và biện luận FR = 16 > F0.05 = 4.747 Bác bỏ giả thuyết H0 (Mùa). FC = 434.778 > F0.05 = 3.385 Bác bỏ giả thuyết H0 (Miền) F1 = 1 < F0.05 = 3.385 Chấp nhận giả thuyết H0 (Mùa x miền). Vậy hàm lượng Saponin trong dược liệu được khảo sát khác nhau không những theo mùa mà còn theo miền. Tuy nhiên. không có sự tương tác giữa hai yếu tố mùa và miền trên hàm lượng ấy. BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
6. 33 Chương 5 H0 : 1 2 k « Các giá trị trung bình bằng nhau » H1 : i  j «Có ít nhất hai giá trị trung bình khác nhau» * Giá trị thống kê: Ġ và Ġ * Biện luận: - NếuĠ ( Chấp nhận H0 (Yếu tố A) - NếuĠ ( Chấp nhận H0 (Yếu tố B) - NếuĠ ( Chấp nhận H0 (Yếu tố C). 5.8 Áp dụng MS-EXCEL Thí dụ 15: Hiệu suất phần trăm (%) của một phản ứng hóa học được nghiên cứu theo ba yếu tố: pH (A). nhiệt độ (B) và chất xúc tác (C) được trình bày trong bảng sau: Yếu Yếu tố B tố A B1 B2 B3 B4 A1 C1 9 C2 14 C3 16 C4 12 A2 C2 12 C3 15 C4 12 C1 10 A3 C3 13 C4 14 C1 11 C2 14 A4 C4 10 C1 11 C2 13 C3 13 Hãy đánh giá về ảnh hưởng của các yếu tố trên hiệu suất phản ứng? Nhập dữ liệu vào bảng tính 1 A B C D E F G H I J K L M 2 B1 B2 B3 B4 3 A1 9 14 16 12 4 A2 12 15 12 10 5 A3 13 14 11 14 6 A4 10 11 13 13 7 8 Ti 51 49 52 47 SUMSQTi 9915 SSR 3.69 MSR 1.2292 FR 3.106 9 T.j. 44 54 52 49 SUMSQT.j. 9957 SSC 14.2 MSC 4.7292 FC 11.95 10 T k 41 53 57 48 SUMSQT k 10043 SSF 35.7 MSF 11.896 F 30.06 11 T 199 SQT 39601 SSE 2.38 MSE 0.3958 12 SUMSQ 2531 STT 55.9 Yijk Thiết lập các biểu thức và tính các giá trị thống kê * Tính các giá trị Ti T.j T k và T - Các giá trị Ti Chọn ô B7 và nhập biểu thức =SUM(B2:E2) BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy
7. 35 Chương 5 - Giá trị SST Chọn ô K7 và nhập biểu thức =I7/(4-1) Dùng con trỏ kéo kí hiệu tự điền từ ô K7 đến ô K9. - Giá trị MSE Chọn ô K10 và nhập biểu thức =I10/((4-1)*(4-2)). * Tính các giá trịĠ và F Chọn ô M7 và nhập biểu thức = K7/0.3958 Dùng con trỏ kéo kí hiệu tự điền từ ô M7 đến ô M9. Kết quả và biện luận FR 3,10 F0,05 (3,6) 4,76 chấp nhận H0 (pH). FC 11,95 F0,05 (3,6) 4,76 Bác bỏ H0 (nhiệt độ) F 30,05 F0,05 (3,6) 4,76 bác bỏ H0 (chất xúc tác) Vậy chỉ có nhiệt độ và chất xúc tác gây ảnh hưởng đến hiệu suất. BỘ MÔN TOÁN GVGD: Nguyễn Đình Huy