Mô tả
Hàm LINEST tính toán các thống kê mang lại 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 quý khách hàng, rồi trả về một mảng tế bào 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ê đến các kiểu mô hình khác là đường tuyến tính vào các tđam mê số không 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 quý hiếm, vì vậy 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.
Bạn đang xem: Vẽ phương trình hồi quy bằng excel
Phương thơm 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à quý giá y phụ thuộc là một hàm của các cực hiếm x độc lập. Giá trị m là các hệ số tương ứng với mỗi quý hiếm 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.
Cú pháp
LINEST(known_y"s,
Cú pháp hàm LINEST bao gồm những đối số sau đây:
Cú pháp
known_y"s Bắt buộc. Tập cực hiếm y mà quý khách đã biết vào quan liêu hệ y = mx + b.
Nếu phạm vi của known_y"s nằm vào một cột solo lẻ, thì mỗi cột của known_x"s được phát âm là một biến số riêng biệt rẽ.
Nếu phạm vi của known_y"s nằm trong một hàng đối kháng lẻ, thì mỗi hàng của known_x"s được phát âm là một biến số riêng rẽ.
known_x"s Tùy chọn. Tập quý hiếm x mà người tiêu dùng có thể đã biết trong quan liêu hệ y = mx + b.
Phạm vi của known_x"s có thể khái quát một hoặc nhiều tập biến số. Nếu chỉ dùng một biến số, thì known_y"s và known_x"s có thể là các phạm vi với bất kỳ hình dạng nào, miễn là chúng có các kích thmong bằng nhau. Nếu dùng nhiều biến số, thì known_y"s phải là một véc-tơ (có nghĩa là một phạm vi cao một hàng và rộng một cột).
Nếu known_x"s được bỏ qua, thì nó được giả định là một mảng 1,2,3,... có cùng kích thcầu như known_y"s.
const Tùy chọn. Một quý hiếm lô-gic chỉ rõ có bắt buộc hằng số b phải bằng 0 hay không.
Nếu const là TRUE hoặc được bỏ qua, thì b được tính toán bình thường.
Nếu const là FALSE, thì b được để bằng 0 và quý giá m được điều chỉnh để phù hợp với y = mx.
stats Tùy chọn. Giá trị lô-gic chỉ rõ có trả về các thống kê hồi quy bổ sung hay là không.
Nếu stats là TRUE, thì quý hiếm linest trả về các thống kê hồi quy bổ sung; vì vậy, mảng được trả về là mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey; F,df;ssreg,ssresid.
Nếu stats là FALSE hoặc được bỏ qua, thì hàm LINEST chỉ trả về hệ số m và hằng số b.
Các thống kê hồi quy bổ sung nhỏng sau.
| se1,se2,...,sen | Giá trị lỗi chuẩn chủa các hệ số m1,m2,...,mn. |
| seb | Giá trị lỗi chuẩn của hằng số b (seb = #N/A Khi const là FALSE). |
| r2 | Hệ số xác định. So sánh các cực hiếm y ước tính và thiết thực và nằm vào phạm vi quý hiếm từ 0 tới 1. Nếu nó là 1, thì có một đối sánh hoàn hảo vào mẫu — ko có sự lạ mắt nào giữa cực hiếm y cầu tính và cực hiếm y thiết thực. Ở thái cực ngược lại, nếu hệ số xác định là 0, thì phương thơm trình hồi quy không còn hữu ích trong việc dự đoán cực hiếm y. Để biết phương pháp tính toán2, hãy coi mục "Ghi chú" ở vị trí sau bài viết này. |
| sey | Lỗi chuẩn đến ước tính y. |
| F | Thống kê F, hoặc cực hiếm F quan sát được. Dùng thống kê F để xác định xem quan lại hệ quan liêu sát được giữa các biến số độc lập và phụ thuộc có ngẫu nhiên xảy ra không. |
| df | Bậc tự vị. Dùng bậc tự vày để giúp khách hàng tìm quý hiếm F tới hạn trong bảng thống kê. So sánh các quý hiếm bạn tìm thấy trong bảng với thống kê F mà hàm LINEST trả về để xác định mức độ tin cậy của mô hình. Để tìm hiểu cách tính toán df, hãy coi mục "Ghi chú" ở phần sau bài viết này. Ví dụ 4 nói về cách dùng F và df. |
| ssreg | Tổng bình pmùi hương hồi quy. |
| ssresid | Tổng bình pmùi hương thặng dư. Để biết cách tính toán ssreg và ssresid, hãy xem mục "Ghi chú" ở phần sau bài viết này. |
Minc họa tiếp sau đây đến thấy thứ tự mà các thống kê hồi quy bổ sung được trả về.
Xem thêm: Lớp Học Vẽ Tranh Phong Cảnh Online Miễn Phí, Học Vẽ Tranh Phong Cảnh Online Miễn Phí
Chú thích
Bạn có thể mô tả bất kỳ đường thẳng nào bằng độ dốc và giao cắt y:
Độ dốc (m): Để tìm độ dốc của một con đường trực tiếp, thường được viết là m, lấy nhị điểm trên tuyến đường trực tiếp kia, (x1,y1) với (x2,y2); độ dốc bởi (y2 - y1)/(x2 - x1).
Cắt Y (b): Giao giảm y của một mặt đường trực tiếp, thường được viết là b, là giá trị của y tại điểm nhưng mặt đường trực tiếp cắt trục y.
Phương trình của đường thẳng là y = mx + b. Khi đã biết quý hiếm của m và b, khách hàng có thể tính toán bất kỳ điểm nào trên đường thẳng bằng cách nhập giá trị y hoặc y vào phương thơm trình đó. Bạn cũng có thể dùng hàm TREND.
Khi quý khách hàng chỉ có một biến độc lập x, khách hàng có thể nhận ra độ dốc và giá trị giao cắt y trực tiếp bằng cách dùng công thức sau đây:
Độ dốc: =INDEX(LINEST(known_y"s,known_x"s),1)
Cắt Y: =INDEX(LINEST(known_y"s,known_x"s),2)
Độ chính xác của đường thẳng bởi hàm LINEST tính toán phụ thuộc vào độ phân tán vào dữ liệu của bạn. Dữ liệu càng tuyến tính, thì tế bào hình LINEST càng chính xác. Hàm LINEST dùng pmùi hương pháp bình pmùi hương nhỏ nhất để xác định sự phù hợp nhất của dữ liệu. khi bạn chỉ có một biến số độc lập x, thì các phép tính mang đến m và b dựa vào công thức sau đây:
trong đó x và y là các trung độ mẫu, tức là x = AVERAGE(known x"s) với y = AVERAGE(known_y"s).
Các hàm phù hợp với đường thẳng và đường cong LINEST và LOGEST có thể tính toán đường thẳng hoặc đường cong hàm mũ phù hợp nhất với dữ liệu của quý khách hàng. Tuy nhiên, người dùng phải quyết định kết quả nào vào hai kết quả là phù hợp nhất với dữ liệu của mình. Bạn có thể tính toán TREND(known_y"s,known_x"s) mang lại một đường thẳng, hoặc GROWTH(known_y"s, known_x"s) đến một đường cong hàm mũ. Những hàm này, ko có đối số new_x"s, trả về một mảng cực hiếm y được dự đoán dọc theo đường thẳng hoặc đường cong tại điểm dữ liệu thực của quý khách hàng. Sau đó, người dùng có thể so sánh quý giá dự đoán với giá trị thiết thật. Bạn có thể muốn vẽ đồ thị đến cả nhị để có được so sánh trực quan tiền.
Trong phân tích hồi quy, Excel tính toán tại mỗi điểm bình phương thơm của hiệu số giữa quý hiếm y ước tính cho điểm đó và cực hiếm y thiết thật của điểm đó. Tổng của các bình pmùi hương hiệu này được đọc là tổng bình pmùi hương thặng dư, ssresid. Sau đó, Excel tính toán tổng cộng bình phương, sstotal. lúc đối số const = TRUE hoặc được bỏ qua, thì tổng cộng bình phương thơm là tổng của các bình phương hiệu giữa giá trị y thiết thật và bình quân các quý giá y. lúc đối số const = FALSE, thì tổng cộng bình pmùi hương là tổng các bình pmùi hương của các cực hiếm y thực tế (mà không trừ giá trị y trung bình ra khỏi mỗi cực hiếm y). Sau đó có thể tìm thấy tổng bình phương thơm hồi quy, ssreg từ công thức ssreg = sstotal - ssresid. Tổng bình pmùi hương thặng dư càng nhỏ tuổi so với tổng cộng những bình phương thơm, thì cực hiếm của hệ số khẳng định, r2, càngbéo, mà lại đây là một chỉ báo cho biết thêm phương thơm trình kết quả của phân tích hồi quy biểu thị rõ mang đến đâu quan hệ thân những đổi mới số. Giá trị của r2 bằng ssreg/sstotal.
Giá trị của df được tính toán như sau, lúc không có cột X nào được loại bỏ khỏi mô hình vì tính cộng tuyến: nếu có các cột k chứa known_x’s và const = TRUE hoặc được bỏ qua, thì df = n – k – 1. Nếu const = FALSE, thì df = n - k. Trong cả hai trường hợp, cột X đã được loại bỏ vì chưng tính cộng tuyến sẽ làm tăng cực hiếm của df thêm một.
lúc nhập một hằng số mảng (chẳng hạn nhỏng known_x"s) làm đối số, bạn hãy dùng dấu phẩy để phân tách các quý giá chứa vào cùng một hàng và dùng dấu chấm phẩy để phân tách hàng. Ký tự phân tách có thể khác nhau tùy thuộc vào thiết để vùng của quý khách.
Hãy lưu lại ý rằng các giá trị y mà pmùi hương trình hồi quy dự đoán có thể ko hợp lệ nếu chúng nằm ngoài phạm vi các quý hiếm y mà khách hàng dùng để xác định phương thơm trình.
Thuật toán ẩn dưới dùng vào hàm LINEST khác với thuật toán ẩn dưới dùng trong các hàm SLOPE và INTERCEPT. Sự sự khác biệt giữa các thuật toán này có thể dẫn đến các kết quả sự so sánh Khi dữ liệu chưa được xác định và cộng tuyến. Ví dụ, nếu các điểm dữ liệu của đối số known_y"s là 0 và các điểm dữ liệu của đối số known_x"s là 1:
Hàm LINEST trả về quý hiếm 0. Thuật toán của hàm LINEST được thiết kế để trả về kết quả hợp lý của dữ liệu cộng tuyến và trong trường hợp này, có thể tìm thấy ít nhất một câu trả lời.
Hàm SLOPE và INTERCEPT trả về cực hiếm lỗi #DIV/0! lỗi. Thuật toán của hàm SLOPE và INTERCEPT được thiết kế để chỉ tìm kiếm một câu trả lời và vào trường hợp này có thể có nhiều câu trả lời.
Ngoài việc dùng hàm LOGEST để tính toán các thống kê hoặc các kiểu hồi quy khác, quý khách hàng có thể dùng hàm LINEST để tính toán một phạm vi các kiểu hồi quy khác bằng cách nhập các hàm của các biến số x làm các chuỗi x và y đến hàm LINEST. Ví dụ, công thức sau đây:
=LINEST(yvalues, xvalues^COLUMN($A:$C))
hoạt động lúc bạn có một cột đơn các quý giá y và một cột solo các cực hiếm x cần tính toán phép xấp xỉ lập phương (đa thức lũy thừa bậc 3) của biểu mẫu:
y = m1*x + m2*x^2 + m3*x^3 + b
Bạn có thể điều chỉnh công thức này để tính toán các kiểu hồi quy khác, nhưng vào một số trường hợp nó nhu cầu phải điều chỉnh giá trị đầu ra và các thống kê khác.
Ví dụ
Ví dụ 1 - Độ dốc và giao cắt Y
Sao chnghiền dữ liệu của ví dụ trong bảng dưới đây và ốp lại ô A1 của một trang tính Excel new. Để cách làm hiển thị hiệu quả, nên chọn bọn chúng, dấn F2 với tiếp nối dìm Enter. Nếu bắt buộc, bạn có thể kiểm soát và điều chỉnh độ rộng lớn cột giúp xem tất cả tài liệu.
| 1 | 0 |
| 9 | 4 |
| 5 | 2 |
| 7 | 3 |
| Kết quả (độ dốc) | Kết quả (giao cắt y) |
| 2 | 1 |
| Công thức (công thức mảng vào ô A7:B7) | |
| =LINEST(A2:A5,B2:B5,,FALSE) |
Ví dụ 2: Hồi quy Tuyến tính Đơn giản
Sao chép tài liệu của ví dụ vào bảng dưới đây với ốp lại ô A1 của một trang tính Excel bắt đầu. Để phương pháp hiển thị hiệu quả, nên chọn chúng, thừa nhận F2 và tiếp nối nhận Enter. Nếu buộc phải, chúng ta có thể điều chỉnh độ rộng lớn cột giúp thấy toàn bộ dữ liệu.
| 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 tân oán dự trù doanh số bán hàng trong tháng thứ chín, phối hợp doanh số các mon từ 1 đến 6. |
Ví dụ 3: Hồi quy Tuyến tính Đa biến
Sao chnghiền tài liệu của ví dụ trong bảng sau đây với dán lại ô A1 của một trang tính Excel mới. Để cách làm hiển thị công dụng, nên lựa chọn bọn chúng, thừa nhận F2 và sau đó dìm Enter. Nếu phải, chúng ta có thể điều chỉnh độ rộng cột để xem tất cả tài liệu.
| 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 (phương pháp mảng rượu cồn được nhập lệ ô A19) | ||||
| =LINEST(E2:E12,A2:D12,TRUE,TRUE) |
lấy ví dụ 4: Sử dụng Thống kê F cùng r2
Trong ví dụ bên trên trên đây, hệ số khẳng định,tuyệt r2, là 0,99675 (xem ô A17 trong kết quả của đối số LINEST), miêu tả một quan hệ nam nữ trẻ khỏe thân các biến chuyển số hòa bình với giá cả. Bạn có thể dùng thống kê F để xác định xem những kết quả này, với quý giá r2 cao nhỏng vậy, có ngẫu nhiên xảy ra hay là không.
Giả sử rằng trên thiết thực ko có quan liêu hệ nào giữa các biến số, mà lại người dùng đã mang một mẫu hiếm gặp về 11 tòa cao ốc vnạp năng lượng phòng, khiến mang đến phân tích thống kê thể hiện một quan liêu hệ mạnh mẽ. Thuật ngữ "Alpha" được dùng để chỉ xác xuất của kết luận không đúng lầm rằng có một quan liêu hệ.
Có thể dùng quý hiếm F cùng df trong đầu ra output từ hàm LINEST nhằm Đánh Giá năng lực xẩy ra quý hiếm F cao hơn. cũng có thể đối chiếu F với giá trị cho tới hạn vào bảng phân bố F đang kiến thiết hoặc hàm FDIST vào Excel để tính toán thù Xác Suất của cực hiếm F to hơn xuất hiện vô tình. Phân cha F phù hợp gồm 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 mất thì v1 = n – df – 1 với v2 = df. (Nếu const = FALSE thì v1 = n – df cùng v2 = df.) Hàm FDIST — cùng với cú pháp FDIST(F,v1,v2) — sẽ trả về Phần Trăm của giá trị F cao hơn nữa xuất hiện thêm tình cờ. Trong ví dụ này, df = 6 (ô B18) cùng F = 459,753674 (ô A18).
Giả sử giá trị Altrộn là 0,05, v1 = 11 – 6 – 1 = 4 cùng v2 = 6, mức đặc biệt quan trọng của F là 4,53. Vì F = 459,753674 cao hơn những đối với 4,53, rất khó khăn có tác dụng xảy ra cực hiếm F cao đến vậy. (Với Altrộn = 0,05, giả thiết rằng không có quan hệ nào giữa nút tình dục của known_y và của known_x là bị lắc đầu Khi F vượt quá mức số lượng giới hạn, 4,53.) quý khách hàng có thể dùng hàm FDIST vào Excel để có được tỷ lệ giá trị F cao tới mức này bởi vô tình xảy ra. ví dụ như, FDIST(459,753674, 4, 6) = 1,37E-7, một Phần Trăm cực bé dại. Quý khách hàng rất có thể kết luận, bằng cách tìm nút cho tới hạn F trong bảng hoặc bằng cách cần sử dụng hàm FDIST, rằng phương trình hồi quy hữu ích vào vấn đề dự đân oán quý hiếm định vị của những cao ốc văn uống chống trong khoanh vùng này. Hãy hãy nhớ là điều quan trọng là áp dụng các quý hiếm đúng của v1 và v2 được tính toán thù trong đoạn văn uống trước kia.
Ví dụ 5: Tính toán thống kê t-Statistics
Một xác định giả thuyết khác sẽ xác định coi mỗi hệ số độ dốc có hữu ích ko vào việc mong tính giá trị định giá của một cao ốc văn uống phòng trong Ví dụ 3. Ví dụ, để chứng thực hệ số tuổi thọ đến ý nghĩa thống kê, hãy phân tách -234,24 (hệ số độ dốc tuổi thọ) mang lại 13,268 (lỗi chuẩn ước tính của hệ số tuổi thọ vào ô A15). Dưới đây là giá trị t-quan sát:
t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7
Nếu quý hiếm hay đối của t đủ lớn, thì có thể kết luận rằng hệ số độ dốc là hữu ích vào việc ước tính quý giá định giá của một cao ốc văn phòng vào Ví dụ 3. Bảng sau đây thể hiện quý hiếm tốt đối của 4 quý hiếm t-quan tiền sát.
Nếu quý khách tđê mê khảo bảng vào sổ tay thống kê, quý khách sẽ thấy rằng t-tới hạn, hai phía, với 6 bậc tự bởi và Alpha = 0,05 là 2,447. Cũng có thể tìm được cực hiếm tới hạn này bằng cách dùng hàm TINV vào Excel. TINV(0,05,6) = 2,447. Vì giá trị giỏi đối của t (17,7) lớn rộng 2,447, cho nên vì thế tuổi thọ là một biến số quan liêu trọng lúc mong tính quý hiếm định giá của một cao ốc vnạp năng lượng phòng. Mỗi trong số các biến số độc lập khác có thể được xác định ý nghĩa thống kê theo cách kiểu như. Dưới phía trên là các giá trị t-quan liêu sát đến mỗi biến số độc lập.
| Diện tích mặt sàn | 5,1 |
| Số lượng vnạp năng lượng phòng | 31,3 |
| Số lượng cửa vào | 4,8 |
| Tuổi thọ | 17,7 |
Tất cả những giá trị này đều có cực hiếm hay đối lớn hơn 2,447, vì vậy toàn bộ các biến số dùng vào phương thơm trình hồi quy đều hữu ích trong việc dự đoán cực hiếm định giá của các cao ốc vnạp năng lượng phòng vào vùng này.
