Hiển phương trình hồi quy trong Excel
Excel cho Microsoft 365 Excel cho Microsoft 365 dành cho máy Mac Excel cho web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 Xem thêm...Ít hơn 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. Hãy tìm các liên kết để xem thêm thông tin về việc lập biểu đồ và thực hiện phân tích hồi quy trong mục Xem Thêm. Hàm LINEST tính toán các thống kê cho một đường thẳng bằng cách dùng phương pháp "bình phương nhỏ nhất" để tính toán đường thẳng phù hợp nhất với dữ liệu của bạn, rồi trả về một mảng mô tả đường thẳng đó. Bạn cũng có thể kết hợp hàm LINEST với các hàm khác để tính toán thống kê cho các kiểu mô hình khác là đường tuyến tính trong các tham số chưa biết, bao gồm chuỗi đa thức, lô-ga-rit, hàm mũ và lũy thừa. Vì hàm này trả về một mảng giá trị, cho nên nó phải được nhập vào dưới dạng công thức mảng. Có các hướng dẫn ở sau 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– y = m1x1 + m2x2 + ... + b nếu có nhiều phạm vi giá trị x, khi mà giá trị y phụ thuộc là một hàm của các giá trị x độc lập. Giá trị m là các hệ số tương ứng với mỗi giá trị x và b là giá trị hằng số. Lưu ý rằng y, x và m có thể là các véc-tơ. Mảng mà hàm LINEST trả về là {mn,mn-1,...,m1,b}. Hàm LINEST cũng có thể trả về các thống kế hồi quy bổ sung. LINEST(known_y's, [known_x's], [const], [stats]) Cú pháp hàm LINEST có các đối số sau đây:
Minh họa sau đây cho thấy thứ tự mà các thống kê hồi quy bổ sung được trả về.
Sao chép dữ liệu của ví dụ trong bảng sau đây và dán vào ô A1 của một trang tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó 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.
Sao chép dữ liệu của ví dụ trong bảng sau đây và dán vào ô A1 của một trang tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó 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.
Sao chép dữ liệu của ví dụ trong bảng sau đây và dán vào ô A1 của một trang tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó 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.
Trong ví dụ trên đây, hệ số xác định,hay r2, là 0,99675 (xem ô A17 trong kết quả của đối số LINEST), thể hiện một quan hệ mạnh mẽ giữa các biến số độc lập và giá bán. Bạn có thể 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ó ngẫu nhiên xảy ra hay không. Giả sử rằng trên thực tế không có quan hệ nào giữa các biến số, nhưng bạn đã lấy một mẫu hiếm gặp về 11 tòa cao ốc văn phòng, khiến cho phân tích thống kê thể hiện một quan hệ mạnh mẽ. Thuật ngữ "Alpha" được dùng để chỉ xác xuất của kết luận sai lầm rằng có một quan hệ. Có thể dùng giá trị F và df trong đầu ra từ hàm LINEST để đánh giá khả năng xảy ra giá trị F cao hơn. Có thể so sánh F với giá trị tới hạn trong bảng phân bố F đã phát hành hoặc hàm FDIST trong Excel để tính toán xác suất của giá trị F lớn hơn xuất hiện tình cờ. Phân bố 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 được 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 của giá trị F cao hơn xuất hiện tình cờ. 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 quan trọng 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 xảy ra giá trị F cao đến vậy. (Với Alpha = 0,05, giả thiết rằng không có mối quan hệ nào giữa mức quan hệ của known_y và của known_x là bị từ chối khi F vượt quá mức giới hạn, 4,53.) Bạn có thể dùng hàm FDIST trong Excel để có được xác suất giá trị F cao đến mức này do vô tình xảy ra. Ví dụ, FDIST(459,753674, 4, 6) = 1,37E-7, một xác suất cực nhỏ. Bạn có thể kết luận, bằng cách tìm mức tới hạn F trong bảng hoặc bằng cách 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ị định giá của các cao ốc 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ị đúng của v1 và v2 được tính toán trong đoạn văn trước đó. Một kiểm tra giả thuyết khác sẽ xác định xem mỗi hệ số độ dốc có hữu ích không trong việc ước tính giá trị định giá của một cao ốc văn phòng trong Ví dụ 3. Ví dụ, để kiểm tra hệ số tuổi thọ cho ý nghĩa thống kê, hãy chia -234,24 (hệ số độ dốc tuổi thọ) cho 13,268 (lỗi chuẩn ước tính của hệ số tuổi thọ trong ô A15). Dưới đây là giá trị t-quan sát: t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7 Nếu giá trị tuyệt đối của t đủ lớn, thì có thể kết luận rằng hệ số độ dốc là hữu ích trong việc ước tính giá trị định giá của một cao ốc văn phòng trong Ví dụ 3. Bảng sau đây thể hiện giá trị tuyệt đối của 4 giá trị t-quan sát. Nếu bạn tham khảo bảng trong sổ tay thống kê, bạn sẽ thấy rằng t-tới hạn, hai phía, với 6 bậc tự do và Alpha = 0,05 là 2,447. Cũng có thể tìm được giá trị tới hạn này bằng cách 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, cho nên tuổi thọ là một biến số quan trọng khi ước tính giá trị định giá của một cao ốc văn phòng. Mỗi trong số các biến số độc lập khác có thể được kiểm tra ý nghĩa thống kê theo cách tương tự. Dưới đây là các giá trị t-quan sát cho mỗi biến số độc lập.
Tất cả những giá trị này đều có giá trị tuyệt đối lớn hơn 2,447, vì vậy tất cả các biến số dùng trong phương trình hồi quy đều hữu ích trong việc dự đoán giá trị định giá của các cao ốc văn phòng trong vùng này.
|