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

Nội dung text: Xử lý thống kê bằng Excel

1. NHÓM HÀM VỀ PHÂN PHỐI XÁC SUẤT BETADIST (x, alpha, beta, A, B) Trả về giá trị của hàm tính mật độ phân phối xác suất tích lũy beta. BETAINV (probability, alpha, beta, A, B) Trả về nghịch đảo của hàm tính mật độ phân phối xác suất tích lũy beta BINOMDIST (number_s, trials, probability_s, Trả về xác suất của những lần thử thành công của cumulative) phân phối nhị phân. CHIDIST (x, degrees_freedom) Trả về xác xuất một phía của phân phối chi-squared. CHIINV (probability, degrees_freedom) Trả về nghịch đảo của xác xuất một phía của phân phối chi-squared. CHITEST (actual_range, expected_range) Trả về giá trị của xác xuất từ phân phối chi-squared và số bậc tự do tương ứng. CONFIDENCE (alpha, standard_dev, size) Tính khoảng tin cậy cho một kỳ vọng lý thuyết CRITBINOM (trials, probability_s, alpha) Trả về giá trị nhỏ nhất sao cho phân phối nhị thức tích lũy lớn hơn hay bằng giá trị tiêu chuẩn. Thường dùng để bảo đảm các ứng dụng đạt chất lượng EXPONDIST (x, lambda, cumulative) : Tính phân phối mũ. Thường dùng để mô phỏng thời gian giữa các biến cố FDIST (x, degrees_freedom1, degrees_freedom2) Tính phân phối xác suất F. Thường dùng để tìm xem hai tập số liệu có nhiều mức độ khác nhau hay không FINV (probability, degrees_freedom1, Tính nghịch đảo của phân phối xác suất F. Thường degrees_freedom2) dùng để so sánh độ biến thiên trong hai tập số liệu. FTEST (array1, array2) : Trả về kết quả của một phép thử F. Thường dùng để xác định xem hai mẫu có các phương sai khác nhau hay không FISHER (x) Trả về phép biến đổi Fisher tại x. Thường dùng để kiểm tra giả thuyết dựa trên hệ số tương quan FISHERINV (y) Tính nghịch đảo phép biến đổi Fisher. Thường dùng để phân tích mối tương quan giữa các mảng số liệu GAMMADIST (x, alpha, beta, cumulative) Trả về phân phối tích lũy gamma. Có thể dùng để nghiên cứu có phân bố lệch. GAMMAINV (probability, alpha, beta) Trả về nghịch đảo của phân phối tích lũy gamma. GAMMLN (x) Tính logarit tự nhiên của hàm gamma. HYPGEOMDIST (number1, number2, ) Trả về phân phối siêu bội (xác suất của một số lần thành công nào đó ) Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 3
2. NHÓM HÀM VỀ TƯƠNG QUAN VÀ HỒI QUY TUYẾN TÍNH CORREL (array1, array2) Tính hệ số tương quan giữa hai mảng để xác định mối quan hệ của hai đặc tính. COVAR (array1, array2) Tính tích số các độ lệch của mỗi cặp điểm dữ liệu, rồi tính trung bình các tích số đó. FORECAST (x, known_y's, known_x's) Tính toán hay dự đoán một giá trị tương lai bằng cách sử dụng các giá trị hiện có, bằng phương pháp hồi quy tuyến tính. GROWTH (known_y's, known_x's, new_x's, Tính toán sự tăng trưởng dự kiến theo hàm mũ, bằng const) cách sử dụng các dữ kiện hiện có. INTERCEPT (known_y's, known_x's) Tìm điểm giao nhau của một đường thẳng với trục y bằng cách sử dụng các trị x và y cho trước LINEST (known_y's, known_x's, const, stats) Tính thống kê cho một đường bằng cách dùng phương pháp bình phương tối thiểu (least squares) để tính đường thẳng thích hợp nhất với dữ liệu, rồi trả về mảng mô tả đường thẳng đó. Luôn dùng hàm này ở dạng công thức mảng. LOGEST (known_y's, known_x's, const, stats) Dùng trong phân tích hồi quy. Hàm sẽ tính đường cong hàm mũ phù hợp với dữ liệu được cung cấp, rồi trả về mảng gía trị mô tả đường cong đó. Luôn dùng hàm này ở dạng công thức mảng. PEARSON (array1, array2) Tính hệ số tương quan momen tích pearson (r), một chỉ mục không thứ nguyên, trong khoảng từ -1 đến 1, phản ánh sự mở rộng quan hệ tuyến tính giữa hai tập số liệu. RSQ (known_y's, known_x's) Tính bình phương hệ số tương quan momen tích Pearson (r), thông qua các điểm dữ liệu trong known_y's và known_x's. SLOPE (known_y's, known_x's) Tính hệ số góc của đường hồi quy tuyến tính thông qua các điềm dữ liệu. STEYX (known_y's, known_x's) Trả về sai số chuẩn của trị dự đoán y đối với mỗi trị x trong hồi quy. TREND (known_y's, known_x's, new_x's, const) Trả về các trị theo xu thế tuyến tính Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 5
3. Ví dụ : Lập bảng và vẽ biểu đồ dữ liệu sau: 12 13 11 13 15 12 11 10 14 13 12 15 § Lập bảng phân phối tần số: o Nhập cột giá trị khác nhau vào C3:C8 o Đánh dấu khối cột tần số ở D3:D8 , nhấn F2 nhập công thức = frequency(A2: A13 , C3:C8) và ấn CTRL+SHIFT +ENTER § Lập bảng phân phối tần suất:nhập vào G2 công thức =D3/$D$9 ,copy các ô còn lại. § Vẽ biểu đồ o Chọn menu: Insert/ Chart / Line/ Next o Nhập vào Data Range : $G$3:$G$8 và chọn mục Column o Chọn Tab Series , nhập địa chỉ cột giá trị: $F$3:$F$8 vào Category (X) axis labels o Chọn Next , Finish Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 7
4. · Kết quả bao gồm: Kỳ vọng (trung bình), phương sai, trung vị, mode, độ lệch chuẩn, độ nhọn, độ nghiêng (hệ số bất đối xứng so với phân phối chuẩn), khoảng biến thiên, max, min, sum, số mẫu (count), khoảng tin cậy của trung bình ở mức 95% . Column1 Tính theo các hàm Mean x = 12.58333 Giá trị trung bình AVERAGE(A1:A12) Sx Standard Error = 0.451569 Sai số mẫu n Median 12.5 Trung vị MEDIAN(A1:A12) Mode 12 Mode MODE(A1:A12) Standard Deviation sx= 1.564279 Độ lệch chuẩn STDEV(A1:A12) Sample Variance 2.44697 Phương sai mẫu VAR(A1:A12) Kurtosis -0.61768 Độ nhọn của đỉnh KURT(A1:A12) Skewness 0.157146 Độ nghiêng SKEW(A1:A12) Range 5 Khoảng biến thiên MAX()-MIN() Minimum 10 Tối thiểu MIN(A1:A12) Maximum 15 Tối đa MAX(A1:A12) Sum 151 Tổng SUM(A1:A12) Count n= 12 Số lượng mẫu COUNT(A1:A12) Sx Confidence Level(95.0%) t = 0.993896 Độ chính xác CONFIDENCE(0,05;S ;n) a n x Sx Sx Chú ý : Khi mẫu lớn (n ³ 30) ta thay t bằng z trong ñoù: Za = NORMSINV(1- a/2) a n a n Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 9
5. III. KIỂM ĐỊNH GIẢ THIẾT 1) So sánh 2 trung bình với phương sai đã biết hay mẫu lớn (n³30) v Dùng menu: Tools/ Data Analysis / z-test:Two Sample for Means x - x v Tiêu chuẩn kiểm định: z= 1 2 2 2 s1 + s 2 n1 n2 v Phân vị 2 phía za/2 là: z Critical two-tail v Nếu ïzï > za/2 thì bác bỏ H0 , chấp nhận H1 Nếu ïzï £ za/2 thì chấp nhận H0 , bác bỏ H1 Ví dụ: Người ta chọn 2 mẫu, mỗi mẫu 10 máy, từ hai lô (I và II được sản xuất với phương sai biết trước tương ứng là 1 và 0,98) để khảo sát thời gian hoàn thành công việc (phút) của chúng: I 6 8 9 10 6 15 9 7 13 11 II 5 5 4 3 9 9 6 13 17 12 Hỏi khả năng hoàn thành công việc của hai máy có khác nhau hay không? a=0,05 Nhập và xử lý dữ liệu § Variable 1 Range , Variable 2 Range: địa chỉ tuyệt đối của vùng dữ liệu của I, II § Variable 1 Variance(known), Variable 2 Variance(known): phương sai của I,II § Labels: chọn khi có tên biến ở đầu cột hoặc hàng § Alpha : mức ý nghĩa a § Output options: chọn cách xuất kết quả Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 11
6. § Nhập và xử lý dữ liệu § Kết quả H0: a1=a2 “Thuốc ngủ trên không có tác dụng đến số giờ ngủ” H1: a1¹a2 “Thuốc ngủ trên có tác dụng đến số giờ ngủ” t-Test: Paired Two Sample for Means Số giờ ngủ có thuốc Số giờ ngủ với thuốc giả Mean 7.06 5.28 Variance 0.720444444 1.577333333 Observations 10 10 Pearson Correlation -0.388571913 Hypothesized Mean Difference 0 df 9 t Stat 3.183538302 P(T ta/2= 2,2622 nên chấp nhận H1 Vậy loại thuốc ngủ trên có ảnh hưởng làm tăng số giờ ngủ trung bình. Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 13
7. t-Test: Two-Sample Assuming Equal Variances Thuốc Giả dược Mean 1.047 1.223 Variance 0.002401111 0.002001111 Observations 10 10 Pooled Variance 0.002201111 Hypothesized Mean Difference 0 df 18 t Stat -8.388352782 P(T ta/2 thì bác bỏ H0 , chấp nhận H1 Nếu ïtï £ ta/2 thì chấp nhận H0 , bác bỏ H1 Ví dụ: Thời gian tan rã (phút) của một loại viên bao từ 2 xí nghiệp dược phẩm (XNDP) khác nhau được kiểm nghiệm như sau: XNDP I 61 71 68 73 71 70 69 74 XNDP II 62 69 65 65 70 71 68 73 Thời gian tan rã của viên bao thuộc hai XNDP có giống nhau không? § Nhập, xử lý dữ liệu và kết quả H0 : a1=a2 “Thời gian tan rã của viên bao 2 XNDP như nhau” H1 : a1 ¹ a2 “Thời gian tan rã của viên bao 2 XNDP khác nhau” Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 15
8. § Kết quả 2 Þ P(X>c )= 0,17295 > a = 0,05 , nên chấp nhận H0 Vậy tỷ lệ khỏi bệnh do thuốc và do giả dược không khác nhau. 6. So sánh 2 phương sai v So sánh 2 phương sai được áp dụng để so sánh độ chính xác của 2 phương pháp định lượng khác nhau. v Chọn menu:Tools/Data Analysis /F-Test Two-Samplefor Variances S 2 v Tính tiêu chuẩn kiểm định F= 1 2 S2 s 2 = s 2 v Nếu F < Fa thì chấp nhận H0: 1 2 và ngược lại. Ví dụ: Một được phân tích bởi hai phương pháp A và B với kết quả sau: A 6,4 5,2 4,8 5,2 4,3 4,4 5,1 5,8 B 2,6 3,5 3,4 3,2 3,4 2,8 2,9 2,8 Cho biết phương pháp nào chính xác hơn? § Nhập và xử lý dữ liệu Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 17
9. IV. PHÂN TÍCH PHƯƠNG SAI (ANOVA) 1. Phân tích phương sai 1 nhân tố 2 Giả sử nhân tố A có k mức X1, X2 , , Xk với Xj có phân phối chuẩn N(a,s ) có mẫu điều tra X1 X2 Xk x11 x12 x1k x21 x22 x2k : : : : : : : x xn k n11 k x n2 2 Với mức ý nghĩa a , hãy kiểm định giả thiết : H0 : a1 = a2 = = ak H1 : “Tồn tại j1¹j2 sao cho aj1≠aj2 “ · Đặt: k § Tổng số quan sát: n = å n j j =1 n j n j 1 T j § Trung bình mẫu nhóm j ( j =1, , k ): x j = å xij = với T j = å xij n j i=1 n j i=1 n 1 k ni T k j k § Trung bình mẫu chung: x = åå xij = v ới T = åå xij = åT j n j=1 i=1 n j=1 i=1 j=1 n j 2 1 2 § Phương sai hiệu chỉnh nhóm j: S j = å(xij - x j ) n j -1 i=1 k n j 2 § SST = åå (xij - x ) Tổng bình phương các độ lệch. j=1 i=1 k 2 § SSA = ån j (x j - x) Tổng bình phương độ lệch riêng của các nhóm so với x j=1 2 k n j 2 k 2 2 T T j T SST = åå xij - SSA= å - SSE = SST - SSA n n n j=1 i=1 j=1 j SSA SSE MSA= MSE = k -1 n - k MSA · Nếu H0 đúng thì F = có phân phối Fisher bậc tự do k-1; n-k MSE · Miền Ba : F > Fk-1; n-k ; 1-a Bảng ANOVA Nguồn sai số Tổng bình Bậc tự do Bình phương trung bình Giá trị thống kê phương SS df MS F Yếu t ố SSA MSA SSA k-1 MSA = F = (Between Group) k -1 MSE Sai số SSE SSE = SST - SSA n-k MSE = (Within Group) n - k Tổng cộng SST n-1 Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 19
10. 4. Kết quả Anova: Single Factor SUMMARY Groups Count Sum Average Variance Vùng 1 7 50.1 7.157143 0.202857 Vùng 2 5 29.2 5.84 0.043 Vùng 3 6 38.1 6.35 0.023 ANOVA Source of Variation SS df MS F P-value F crit Between Groups 5.326968 2 2.663484 26.56148 1.17756E-05 3.682316674 Within Groups 1.504143 15 0.100276 Total 6.831111 17 Þ F= 26,5615 > Fk-1; n-k ; 1-a =3,6823 nên bác bỏ H0 chấp nhận H1. Vậy hàm lượng Alcaloid có sai khác theo vùng. Bài tập 1. So sánh 3 loại thuốc bổ A, B, C trên 3 nhóm, người ta được kết quả tăng trọng(kg) như sau: A: 1,0 1,2 1,4 1,1 0,8 0,6 B: 2,0 1,8 1,9 1,2 1,4 1,0 1,5 1,8 C: 0,4 0,6 0,7 0,2 0,3 0,1 0,2 Hãy so sánh kết quả tăng trọng của 3 loại thuốc bổ trên với a = 0,01 2. Một nghiên cứu được thực hiện nhằm xem xét năng suất lúa trung bình của 3 giống lúa. Kết quả thu thập qua 4 năm như sau: Năm A B C 1 65 69 75 2 74 72 70 3 64 68 78 4 83 78 76 Hãy cho biết năng suất lúa trung bình của 3 giống lúa có khác nhau hay không? a=0,01 3. So sánh hiệu quả giảm đau của 4 loại thuốc A, B, C, D bằng cách chia 20 bệnh nhân thành 4 nhóm, mỗi nhóm dùng một loại thuốc giảm đau trên. Kết quả mức độ giảm đau là: A: 82 89 77 72 92 B: 80 70 72 90 68 C: 77 69 67 65 57 D: 65 75 67 55 63 Hỏi hiệu quả giảm đau của 4 loại thuốc có khác nhau không? Nếu hiệu quả giảm đau của 4 loại thuốc A, B, C, D khác nhau có ý nghĩa, hãy so sánh từng cặp thuốc với a = 0,05 Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 21
11. * Bảng ANOVA Nguồn SS df MS F 2 SSA SSA åTi* 2 Yếu tố A T n-1 MS ( A = FA = SSA= i - n - 1 SSE m m.n T 2 å * j 2 SSB SSB Yếu tố B j T m-1 MSB = F = SSB= - m -1 B SSE n m.n SSE Sai số SSE=SST-SSA-SSB (n-1)(m- MSE = 1) (n -1)(m -1) 2 2 T Tổng SST= å xij - nm-1 i, j m.n * Kết luận: · Nếu FA > F n-1 ; (n-1)(m-1) ; 1-a thì bác bỏ yếu tố A (hàng) · Nếu FB > F m-1 ; (n-1)(m-1) ; 1-a thì bác bỏ yếu tố B (cột) Ví dụ: Chiết suất chất X từ 1 loại dược liệu bằng 3 phương pháp và 5 loại dung môi, ta có kết quả: PP Chiết suất (B) b b b Dung môi (A) 1 2 3 a1 120 60 60 a2 120 70 50 a3 130 60 50 a4 150 70 60 a5 110 75 54 Hãy xét ảnh hưởng của phương pháp chiết suất và dung môi đến kết quả chiết suất chất X với a=0,01. · Giả thiết H0 : * Trung bình của 3 phương pháp chiết suất bằng nhau * Trung bình của 5 dung môi bằng nhau * Không có sự tương tác giữa phương pháp chiế suất và dung môi · Chọn Tools\Data Analysis \Anova: Two-Factor without replication · Chọn các mục như hình Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 23
12. 3) Để khảo sát ảnh hưởng của 4 loại thuốc trừ sâu (1, 2, 3 và 4) và ba loại giống (B1, B2 và B3) đến sản lượng của cam, các nhà nghiên cứu tiến hành một thí nghiệm loại giai thừa. Trong thí nghiệm này, mỗi giống cam có 4 cây cam được chọn một cách ngẫu nhiên, và 4 loại thuốc trừ sâu áp dụng (cũng ngẫu nhiên) cho mỗi cây cam. Kết quả nghiên cứu (sản lượng cam) cho từng giống và thuốc trừ sâu như sau: Thuốc trừ sâu 1 2 3 4 Giống Cam B1 29 50 43 53 B2 41 58 42 73 B3 66 85 63 85 Hãy cho biết thuốc trừ sâu, giống cam có ảnh h ưởng đến sản lượng cam không? a = 0,05 4) 4 chuyên gia tài chính được yêu cầu dự đoán về tốc độ tăng trưởng (%) trong năm tới của 5 công ty trong ngành nhựa. Dự đoán được ghi nhận như sau: Chuyên gia Công ty A B C D 1 8 12 8,5 13 2 14 10 9 11 3 11 9 12 10 4 9 13 10 13 5 12 10 10 10 Hãy lập bảng ANOVA. Có thể nói rằng dự đoán tốc độ tăng trưởng trung bình là như nhau cho cả 5 công ty nhựa được không? 3. Phân tích phương sai 2 nhân tố có lặp Tương tự như bài toán phân tích phương sai 2 nhân tố không lặp, chỉ khác mỗi mức ((ai , bj) đều có sự lặp lại r lần thí nghiệm và ta cần khảo sát thêm sự tương tác (interaction term) FAB giữa 2 nhân tố A và B. * Mẫu điều tra: B b b ¼ b A 1 2 m x111 x121 x1m1 x112 x122 x1m2 a1 : : ¼ : : : : x11r x12r x1mr x211 x221 ¼ x2m1 x212 x222 x2m2 a2 : : : : : : x21r x22r x2mr : : : : : : : : xn11 xn21 ¼ xnm1 xn12 xn22 xnm2 an : : : : : : xn1r xn2r xnmr Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 25
13. * Bảng ANOVA Nguồn SS df MS F SSA MSA MSA = F = Yếu tố A SSA n-1 n -1 A MSE SSB MSB Yếu tố B SSB m-1 MSB = F = m -1 B MSE SSAB MSAB Tương tác AB SSAB (n-1)(m-1) MSAB = F = (n -1)(m -1) AB MSE SSE Sai số SSE nm(r-1) MSE = nm(r -1) Tổng SST nmr-1 * Kết luận: · Nếu FA > F n-1 ; nm(r-1) ; 1-a thì bác bỏ yếu tố A (h àng) · Nếu FB > F m-1 ; nm(r-1) ; 1-a thì bác bỏ yếu tố B (cột) · Nếu FAB > F (n-1)(m-1) ; nm(r-1) ; 1-a thì có sự tương tác giữa A và B Ví dụ: Hàm lượng saponin (mg) của cùng một loại dược liệu được thu hái trong 2 mùa (khô và mưa: trong mỗi mùa lấy mẫu 3 lần - đầu mùa, giữa mùa, cuối mùa) và từ 3 miền (Nam, Trung, Bắc) thu được kết quả sau: Miền Mùa Thời điểm Nam Trung Bắc Đầu mùa 2,4 2,1 3,2 Khô Giữa mùa 2,4 2,2 3,2 Cuối mùa 2,5 2,2 3,4 Đầu mùa 2,5 2,2 3,4 Mưa Giữa mùa 2,5 2,3 3,5 Cuối mùa 2,6 2,3 3,5 Hãy cho biết hàm lượng saponin có khác nhau theo mùa hay miền không? Nếu có thì 2 yếu tố mùa và miền có sự tương tác với nhau hay không? a = 0,05 Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 27
14. Bài tập 1) Một nghiên cứu được thực hiện nhằm xem xét sự liên hệ giữa loại phân bón, giống lúa và năng suất. Năng suất lúa được ghi nhận từ các thực nghiệm sau: Giống lúa A B C Loại phân bón 65 69 75 1 68 71 75 62 67 78 74 72 70 2 79 69 69 76 69 65 64 68 78 3 72 73 82 65 75 80 83 78 76 4 82 78 77 84 75 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 1 2 3 Loại phân 5,5 4,5 3,5 A 5,5 4,5 4,0 6,0 4,0 3,0 5,6 5,0 4,0 B 7,0 5,5 5,0 7,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 16 19 19 20 a1 14 20 21 24 21 23 22 21 16 19 20 17 17 19 21 20 a2 15 18 21 20 17 18 22 22 19 20 23 19 18 20 22 25 a3 18 23 18 22 19 21 21 21 17 21 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) 29
15. 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 S y § Phương trình hồi quy tuyến tính: y x = a + bx , a = r , b = y - ax Sx § 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) 31
16. 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) Nhiệt độ (0C) Hiệu suất (%) X1 X2 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: · Y X1 = b0 + b1 X1 Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 33
17. 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: Y X1 = -11,1411+ 0,1286X1 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. · Y X1,X 2 = b0 + b1 X1+ b2 X 2 Giải toán XSTK bằng EXCEL (ĐaTaDa – ĐHNL 10/10/2009) 35
18. 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) 37