Ví dụ này hướng dẫn bạn cách chạy phân tích hồi quy tuyến tính trong Excel và cách diễn giải Kết quả Tóm tắt Show Dưới đây bạn có thể tìm thấy dữ liệu của chúng tôi. câu hỏi lớn là. có mối quan hệ giữa Số lượng bán (Đầu ra) và Giá cả và Quảng cáo (Đầu vào). Nói cách khác. chúng ta có thể dự đoán Số lượng bán nếu chúng ta biết Giá và Quảng cáo không?  1. Trên tab Dữ liệu, trong nhóm Phân tích, bấm vào Phân tích Dữ liệu Ghi chú. không thể tìm thấy nút Phân tích dữ liệu? 2. Chọn Hồi quy và bấm OK 3. Chọn Phạm vi Y (A1. A8). Đây là biến dự đoán (còn gọi là biến phụ thuộc) 4. Chọn Phạm vi X (B1. C8). Đây là các biến giải thích (còn gọi là biến độc lập). Các cột này phải liền kề nhau 5. Kiểm tra nhãn 6. Nhấp vào hộp Phạm vi đầu ra và chọn ô A11 7. Kiểm tra dư 8. Nhấp vào OK Excel tạo Kết quả Tóm tắt sau (làm tròn đến 3 chữ số thập phân) Quảng trường RR Vuông bằng 0. 962, rất phù hợp. 96% sự thay đổi về Số lượng bán được giải thích bởi các biến độc lập Giá và Quảng cáo. Càng gần 1, đường hồi quy (đọc tiếp) càng phù hợp với dữ liệu Ý nghĩa giá trị F và PĐể kiểm tra xem kết quả của bạn có đáng tin cậy (có ý nghĩa thống kê hay không), hãy xem Ý nghĩa F (0. 001). Nếu giá trị này nhỏ hơn 0. 05, bạn không sao đâu. Nếu Ý nghĩa F lớn hơn 0. 05, có lẽ tốt hơn là ngừng sử dụng tập hợp các biến độc lập này. Xóa một biến có giá trị P cao (lớn hơn 0. 05) và chạy lại hồi quy cho đến khi Ý nghĩa F giảm xuống dưới 0. 05 Hầu hết hoặc tất cả các giá trị P phải dưới 0. 05. Trong ví dụ của chúng tôi đây là trường hợp. (0. 000, 0. 001 và 0. 005) hệ sốĐường hồi quy là. y = Số lượng đã bán = 8536. 214 -835. 722 * Giá + 0. 592 * Quảng cáo. Nói cách khác, với mỗi đơn vị tăng giá, Lượng bán giảm 835. 722 đơn vị. Đối với mỗi đơn vị tăng trong Quảng cáo, Số lượng bán tăng bằng 0. 592 đơn vị. Đây là thông tin có giá trị Bạn cũng có thể sử dụng các hệ số này để dự báo. Ví dụ: nếu giá bằng $4 và Quảng cáo bằng $3000, bạn có thể đạt được Số lượng đã bán là 8536. 214 -835. 722 * 4 + 0. 592 * 3000 = 6970 dưPhần dư cho bạn biết khoảng cách giữa các điểm dữ liệu thực tế so với các điểm dữ liệu dự đoán (sử dụng phương trình). Ví dụ: điểm dữ liệu đầu tiên bằng 8500. Sử dụng phương trình, điểm dữ liệu dự đoán bằng 8536. 214 -835. 722 * 2 + 0. 592 * 2800 = 8523. 009, cho số dư 8500 - 8523. 009 = -23. 009 Trong Excel dành cho web, bạn có thể xem kết quả phân tích hồi quy (trong thống kê, một cách để dự đoán và dự đoán xu hướng), nhưng bạn không thể tạo kết quả vì công cụ Hồi quy không sẵn dùng Bạn cũng sẽ không thể sử dụng hàm trang tính thống kê chẳng hạn như LINEST để thực hiện phân tích có ý nghĩa vì hàm này yêu cầu bạn nhập dưới dạng công thức mảng, hàm này không được hỗ trợ trong Excel dành cho web Nếu bạn có ứng dụng Excel trên máy tính, bạn có thể sử dụng nút Mở trong Excel để mở sổ làm việc của mình và sử dụng công cụ Hồi quy của ToolPak hoặc các hàm thống kê để thực hiện phân tích hồi quy ở đó Bài viết này mô tả cú pháp công thức và cách sử dụng hàm LINEST trong Microsoft Excel. Tìm liên kết để biết thêm thông tin về biểu đồ và thực hiện phân tích hồi quy trong phần Xem thêm Sự miêu tảHàm LINEST tính toán số liệu thống kê cho một dòng bằng cách sử dụng phương pháp "bình phương nhỏ nhất" để tính toán một đường thẳng phù hợp nhất với dữ liệu của bạn, sau đó trả về một mảng mô tả dòng đó. Bạn cũng có thể kết hợp LINEST với các hàm khác để tính toán số liệu thống kê cho các loại mô hình tuyến tính khác với các tham số chưa biết, bao gồm chuỗi đa thức, logarit, hàm mũ và lũy thừa. Vì hàm này trả về một mảng các giá trị nên nó phải được nhập dưới dạng công thức mảng. Hướng dẫn làm theo các ví dụ trong bài viết này Phương trình của đường thẳng là y = mx + b -hoặc là- y = m1x1 + m2x2 +. + b nếu có nhiều phạm vi giá trị x, trong đó giá trị y phụ thuộc là một hàm của giá trị x độc lập. Các giá trị m là các hệ số tương ứng với từng giá trị x và b là một giá trị không đổi. Lưu ý rằng y, x và m có thể là các vectơ. Mảng mà hàm LINEST trả về là {mn,mn-1,. ,m1,b}. LINEST cũng có thể trả về số liệu thống kê hồi quy bổ sung cú phápLINEST(known_y's, [known_x's], [const], [stats]) Cú pháp hàm LINEST có các đối số sau cú pháp
thống kê Sự miêu tả se1, se2,. , sen Giá trị sai số chuẩn cho các hệ số m1,m2,. , mn seb Giá trị lỗi tiêu chuẩn cho hằng số b (seb = #N/A khi const là FALSE) r2 hệ số xác định. So sánh các giá trị y ước tính và thực tế, và nằm trong khoảng giá trị từ 0 đến 1. Nếu nó là 1, thì có một mối tương quan hoàn hảo trong mẫu — không có sự khác biệt giữa giá trị y ước tính và giá trị y thực tế. Ở một thái cực khác, nếu hệ số xác định bằng 0, phương trình hồi quy không hữu ích trong việc dự đoán giá trị y. Để biết thông tin về cách tính r2, hãy xem phần "Ghi chú" ở phần sau của chủ đề này sey Sai số chuẩn cho ước tính y F Thống kê F hoặc giá trị quan sát được của F. Sử dụng thống kê F để xác định xem mối quan hệ được quan sát giữa các biến phụ thuộc và biến độc lập có xảy ra tình cờ hay không df Bậc tự do. Sử dụng bậc tự do để giúp bạn tìm các giá trị tới hạn F trong bảng thống kê. So sánh các giá trị bạn tìm thấy trong bảng với thống kê F do LINEST trả về để xác định mức độ tin cậy cho mô hình. Để biết thông tin về cách tính toán df, hãy xem phần "Ghi chú" ở phần sau của chủ đề này. hiển thị việc sử dụng F và df ssreg Tổng bình phương hồi quy ssresid Tổng bình phương còn lại. Để biết thông tin về cách tính ssreg và ssresid, hãy xem phần "Ghi chú" ở phần sau của chủ đề này Hình minh họa sau đây cho thấy thứ tự trả về thống kê hồi quy bổ sung Nhận xét
ví dụVí dụ 1 - Độ dốc và Giao điểm YSao chép dữ liệu ví dụ trong bảng sau và dán vào ô A1 của trang tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2, rồi nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu đã biết y đã biết x 1 0 9 4 5 2 7 3 Kết quả (độ dốc) Kết quả (giá trị chặn y) 2 1 Công thức (công thức mảng trong ô A7. B7) =LINEST(A2. A5,B2. B5,,SAI) Ví dụ 2 - Hồi quy tuyến tính đơn giảnSao chép dữ liệu ví dụ trong bảng sau và dán vào ô A1 của trang tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2, rồi nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu Tháng Việc bán hàng 1 $3,100 2 $4,500 3 $4,400 4 $5,400 5 $7,500 6 $8,100 Công thức Kết quả =SUM(LINEST(B1. B6, A1. A6)*{9,1}) $11,000 Tính toán doanh số bán hàng ước tính trong tháng thứ chín, dựa trên doanh số bán hàng từ tháng 1 đến tháng 6 Ví dụ 3 - Hồi quy tuyến tính bộiSao chép dữ liệu ví dụ trong bảng sau và dán vào ô A1 của trang tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2, rồi nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu Diện tích sàn (x1) Văn phòng (x2) Lối vào (x3) Tuổi (x4) Giá trị thẩm định (y) 2310 2 2 20 $142,000 2333 2 2 12 $144,000 2356 3 1. 5 33 $151,000 2379 3 2 43 $150,000 2402 2 3 53 $139,000 2425 4 2 23 $169,000 2448 2 1. 5 99 $126,000 2471 2 2 34 $142,900 2494 3 3 23 $163,000 2517 4 4 55 $169,000 2540 2 3 22 $149,000 -234. 2371645 13. 26801148 0. 996747993 459. 7536742 1732393319 Công thức (công thức mảng động được nhập trong A19) =LINEST(E2. E12,A2. D12,TRUE,TRUE) Ví dụ 4 - Sử dụng Thống kê F và r2Trong ví dụ trước, hệ số xác định, hoặc r2, là 0. 99675 (xem ô A17 trong đầu ra cho LINEST), điều này sẽ biểu thị mối quan hệ chặt chẽ giữa các biến độc lập và giá bán. Bạn có thể sử dụng thống kê F để xác định xem những kết quả này, với giá trị r2 cao như vậy, có xảy ra tình cờ hay không Hiện tại, giả sử rằng trên thực tế không có mối quan hệ nào giữa các biến, nhưng bạn đã rút ra một mẫu hiếm hoi gồm 11 tòa nhà văn phòng khiến phân tích thống kê chứng minh mối quan hệ chặt chẽ. Thuật ngữ "Alpha" được sử dụng cho xác suất kết luận sai rằng có một mối quan hệ Các giá trị F và df trong đầu ra từ hàm LINEST có thể được sử dụng để đánh giá khả năng giá trị F cao hơn ngẫu nhiên xảy ra. Có thể so sánh F với các giá trị tới hạn trong các bảng phân phối F đã xuất bản hoặc có thể sử dụng hàm FDIST trong Excel để tính xác suất của một giá trị F lớn hơn tình cờ xảy ra. Phân phối F thích hợp có bậc tự do v1 và v2. Nếu n là số điểm dữ liệu và const = TRUE hoặc bị bỏ qua, thì v1 = n – df – 1 và v2 = df. (Nếu const = FALSE thì v1 = n – df và v2 = df. ) Hàm FDIST — với cú pháp FDIST(F,v1,v2) — sẽ trả về xác suất ngẫu nhiên xảy ra giá trị F cao hơn. Trong ví dụ này, df = 6 (ô B18) và F = 459. 753674 (ô A18) Giả sử giá trị Alpha là 0. 05, v1 = 11 – 6 – 1 = 4 và v2 = 6, mức tới hạn của F là 4. 53. Vì F = 459. 753674 cao hơn nhiều so với 4. 53, rất khó có khả năng giá trị F cao như thế này xảy ra một cách tình cờ. (Với Alpha = 0. 05, giả thuyết rằng không có mối quan hệ nào giữa known_y’s và known_x’s sẽ bị bác bỏ khi F vượt quá mức tới hạn, 4. 53. ) Bạn có thể sử dụng hàm FDIST trong Excel để tính xác suất giá trị F cao này tình cờ xảy ra. Ví dụ: FDIST(459. 753674, 4, 6) = 1. 37E-7, một xác suất cực kỳ nhỏ. Bạn có thể kết luận, bằng cách tìm mức tới hạn của F trong bảng hoặc bằng cách sử dụng hàm FDIST, rằng phương trình hồi quy hữu ích trong việc dự đoán giá trị được đánh giá của các tòa nhà văn phòng trong khu vực này. Hãy nhớ rằng điều quan trọng là sử dụng các giá trị chính xác của v1 và v2 đã được tính toán trong đoạn trước Ví dụ 5 - Tính thống kê tMột thử nghiệm giả thuyết khác sẽ xác định xem mỗi hệ số độ dốc có hữu ích trong việc ước tính giá trị được đánh giá của một tòa nhà văn phòng ở. Ví dụ: để kiểm tra ý nghĩa thống kê của hệ số tuổi, hãy chia -234. 24 (hệ số độ dốc tuổi) bằng 13. 268 (sai số chuẩn ước tính của hệ số tuổi trong ô A15). Sau đây là giá trị quan sát t t = m4 ÷ se4 = -234. 24 ÷ 13. 268 = -17. 7 Nếu giá trị tuyệt đối của t đủ cao, có thể kết luận rằng hệ số góc rất hữu ích trong việc ước tính giá trị được đánh giá của một tòa nhà văn phòng ở. Bảng sau đây cho thấy các giá trị tuyệt đối của 4 giá trị t-quan sát Nếu bạn tham khảo một bảng trong sách hướng dẫn thống kê, bạn sẽ thấy rằng t-critical, hai đuôi, với 6 bậc tự do và Alpha = 0. 05 là 2. 447. Giá trị tới hạn này cũng có thể được tìm thấy bằng cách sử dụng hàm TINV trong Excel. TINV(0. 05,6) = 2. 447. Vì giá trị tuyệt đối của t(17. 7) lớn hơn 2. 447, tuổi là một biến số quan trọng khi ước tính giá trị thẩm định của một tòa nhà văn phòng. Mỗi biến độc lập khác có thể được kiểm tra ý nghĩa thống kê theo cách tương tự. Sau đây là các giá trị t-quan sát cho từng biến độc lập Biến đổi giá trị quan sát t Không gian sàn 5. 1 Số văn phòng 31. 3 Số lối vào 4. 8 Tuổi 17. 7 Các giá trị này đều có giá trị tuyệt đối lớn hơn 2. 447; |
