Hồi quy tuyến tính trong excel

Excel mang đến toasanguocmo.vn 365Excel đến toasanguocmo.vn 365 giành cho thiết bị MacExcel mang lại webExcel 2019Excel 2016Excel 2019 for MacExcel 2013Excel 2010Excel 2007Excel năm nhâm thìn for MacExcel for Mac 2011Excel Starter 2010Thêm...Ít hơn

Mô tả

Hàm LINEST tính toán các thống kê đến một đường thẳng bằng cách dùng pmùi hương pháp "bình phương thơm nhỏ nhất" để tính toán đường thẳng phù hợp nhất với dữ liệu của người dùng, rồi trả về một mảng tế bào tả đường thẳng đó. Bạn cũng có thể phối hợp hàm LINEST với các hàm khác để tính toán thống kê mang đến các kiểu mô hình khác là đường tuyến tính trong các tmê say số chưa biết, bao quát chuỗi nhiều thức, lô-ga-rit, hàm mũ và lũy thừa. Vì hàm này trả về một mảng cực hiếm, vì thế nó phải được nhập vào dưới dạng công thức mảng. Có các chỉ dẫn ở sau các ví dụ trong bài viết này.

You watching: Hồi quy tuyến tính trong 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, Lúc mà quý hiếm 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 cực hiếm x và b là cực hiếm 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 gồm các đối số sau đây:

Cú pháp

known_y"s Bắt buộc. Tập giá trị y mà bạn đã biết trong quan lại hệ y = mx + b.

Nếu phạm vi của known_y"s nằm vào một cột đối kháng lẻ, thì mỗi cột của known_x"s được đọc là một biến số riêng 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 gọi là một biến số riêng rẽ.

known_x"s Tùy chọn. Tập giá trị x mà bạn có thể đã biết vào quan lại hệ y = mx + b.

Phạm vi của known_x"s có thể tổng quan 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 thước như known_y"s.

const Tùy chọn. Một quý giá lô-gic chỉ rõ có bắt buộc hằng số b phải bằng 0 hay là 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ý hiếm 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 không.

Nếu stats là TRUE, thì quý hiếm linest trả về những thống kê lại hồi quy xẻ sung; cho nên 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 nlỗi sau.

Thống kê

Mô tả

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 quý hiếm y cầu tính và thiết thật và nằm trong phạm vi quý giá từ 0 tới 1. Nếu nó là 1, thì có một đối sánh tương quan hoàn hảo trong mẫu — ko có sự khác lạ nào giữa cực hiếm y ước tính và quý giá y thiết thật. Ở 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 cách tính toán2, hãy xem mục "Ghi chú" tại vị trí sau nội dung bài viết này.

sey

Lỗi chuẩn mang đến ước tính y.

F

Thống kê F, hoặc quý giá F quan liêu sát được. Dùng thống kê F để xác định coi quan hệ quan 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ự bởi vì. Dùng bậc tự bởi vì để giúp bạn tìm quý hiếm F tới hạn vào 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 mà hàm LINEST trả về để xác định mức độ tin cậy của tế bào 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 coi mục "Ghi chú" ở phần sau bài viết này.

Minc họa dưới đây đến thấy thứ tự mà các thống kê hồi quy bổ sung được trả về.

See more: 34 Món Cá Trích Làm Món Gì Ngon, Khô Cá Trích

*

Chú thích

Bạn có thể tế bào 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 mặt đường trực tiếp, thường xuyên được viết là m, mang nhị điểm trê tuyến phố trực tiếp kia, (x1,y1) với (x2,y2); độ dốc bởi (y2 - y1)/(x2 - x1).

Cắt Y (b): Giao cắt y của một mặt đường trực tiếp, thường được viết là b, là quý hiếm của y tại điểm nhưng mà mặt đường trực tiếp cắt trục y.

Pmùi hương trình của đường thẳng là y = mx + b. lúc đã biết giá trị của m và b, quý khách có thể tính toán bất kỳ điểm nào bên trên đường thẳng bằng cách nhập cực hiếm y hoặc y vào pmùi hương trình đó. Bạn cũng có thể dùng hàm TREND.

lúc bạn chỉ có một biến độc lập x, người tiêu dùng có thể nhận ra độ dốc và quý hiếm 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 vì hàm LINEST tính toán phụ thuộc vào độ phân tán vào dữ liệu của người tiêu dùng. 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 phương pháp bình phương nhỏ nhất để xác định sự phù hợp nhất của dữ liệu. khi người dùng chỉ có một biến số độc lập x, thì các phép tính đến m và b dựa vào công thức sau đây:

*

*

vào đó x và y là các trung độ mẫu, tức là x = AVERAGE(known x"s)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 khách hàng. Tuy nhiên, bạn phải quyết định kết quả nào trong 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) đến một đường thẳng, hoặc GROWTH(known_y"s, known_x"s) cho một đường cong hàm mũ. Những hàm này, không có đối số new_x"s, trả về một mảng quý 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 người mua. Sau đó, bạn có thể so sánh giá trị dự đoán với cực hiếm thực tế. Bạn có thể muốn vẽ đồ thị cho cả hai để 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 giá trị y cầu 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 phương thơm hiệu này được phát âm là tổng bình phương thơm thặng dư, ssresid. Sau đó, Excel tính toán tổng cộng bình pmùi hương, sstotal. Khi đối số const = TRUE hoặc được bỏ qua, thì tổng cộng bình phương là tổng của các bình phương hiệu giữa cực hiếm y thiết thực và bình quân các giá trị y. Khi đối số const = FALSE, thì tổng cộng bình pmùi hương là tổng các bình phương thơm của các giá trị y thiết thật (mà ko trừ quý hiếm y trung bình ra khỏi mỗi quý giá y). Sau đó có thể tìm thấy tổng bình phương hồi quy, ssreg từ công thức ssreg = sstotal - ssresid. Tổng bình phương thơm thặng dư càng nhỏ dại đối với tổng số những bình pmùi hương, thì quý giá của thông số khẳng định, r2, càngKhủng, nhưng mà đấy là một chỉ báo cho thấy pmùi hương trình công dụng của so sánh hồi quy biểu đạt rõ đến đâu quan hệ thân những biến hóa số. Giá trị của r2 bởi ssreg/sstotal.

Giá trị của df được tính toán nhỏng sau, lúc không có cột X nào được loại bỏ khỏi mô hình vì chưng 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ả nhị trường hợp, cột X đã được loại bỏ do tính cộng tuyến sẽ làm tăng quý giá của df thêm một.

Khi nhập một hằng số mảng (chẳng hạn như known_x"s) làm đối số, quý khách hãy dùng dấu phẩy để phân tách các cực hiếm 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ể sự so sánh tùy thuộc vào thiết đặt vùng của người dùng.

Hãy lưu lại ý rằng các quý giá y mà phương trình hồi quy dự đoán có thể không hợp lệ nếu chúng nằm ngoài phạm vi các giá trị y mà khách hàng dùng để xác định phương trình.

Thuật toán ẩn dưới dùng trong 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ự so sánh giữa các thuật toán này có thể dẫn đến các kết quả khác nhau Lúc dữ liệu không đượ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ề cực 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ề giá trị 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, người mua 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 khi người mua hàng có một cột đối kháng 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, mà lại trong một số trường hợp nó lời xin phải điều chỉnh quý hiếm đầu ra và các thống kê khác.

Ví dụ

Ví dụ 1 - Độ dốc và giao cắt Y

Sao chép dữ liệu của ví dụ trong bảng sau đây và dán 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 lựa bọn chúng, nhấn F2 cùng tiếp nối dìm Enter. Nếu yêu cầu, bạn cũng có thể kiểm soát và điều chỉnh độ rộng cột để thấy tất cả tài liệu.

Y đã biết

X đã biết

Công thức (công thức mảng trong ô A7:B7)

1

0

9

4

5

2

7

3

Kết quả (độ dốc)

Kết quả (giao cắt y)

2

1

=LINEST(A2:A5,B2:B5,,FALSE)

Ví dụ 2: Hồi quy Tuyến tính Đơn giản

Sao chnghiền tài liệu của ví dụ vào bảng sau đây cùng dính vào ô A1 của một trang tính Excel new. Để công thức hiển thị công dụng, nên chọn bọn chúng, dìm F2 cùng tiếp nối nhấn Enter. Nếu cần, bạn có thể kiểm soát và điều chỉnh độ rộng cột để xem tất cả dữ liệu.

Tháng

Doanh số

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 dự tính doanh số bán hàng hồi 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ụ vào bảng dưới đây với dán vào ô A1 của một trang tính Excel new. Để phương pháp hiển thị hiệu quả, hãy chọn bọn chúng, nhấn F2 và tiếp đến nhấn Enter. Nếu nên, chúng ta 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.

Diện tích mặt sàn (x1)

Vnạp năng lượng phòng (x2)

Cửa vào (x3)

Tuổi thọ (x4)

Giá trị định giá (y)

-234,2371645

13,26801148

0,996747993

459,7536742

1732393319

Công thức (phương pháp mảng hễ được nhtràn lên ô A19)

=LINEST(E2:E12,A2:D12,TRUE,TRUE)

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

ví dụ như 4: Sử dụng Thống kê F cùng r2

Trong ví dụ trên đây, hệ số khẳng định,tuyệt r2, là 0,99675 (coi ô A17 vào công dụng của đối số LINEST), miêu tả một dục tình mạnh bạo thân các biến chuyển số chủ quyền với giá bán. Bạn có thể dùng thống kê F để xác định coi những kết quả này, với giá trị r2 cao nhỏng vậy, có ngẫu nhiên xảy ra hay không.

Giả sử rằng bên trên thiết thực không có quan hệ nào giữa các biến số, nhưng mà người mua hàng đã rước một mẫu hiếm gặp về 11 tòa cao ốc văn uống phòng, khiến mang lại 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 sai lầm rằng có một quan tiền hệ.

Có thể cần sử dụng cực hiếm F và df vào cổng đầu ra trường đoản cú hàm LINEST để đánh giá tài năng xảy ra cực hiếm F cao hơn nữa. cũng có thể đối chiếu F với cái giá trị cho tới hạn trong bảng phân bố F đang xây đắp hoặc hàm FDIST trong Excel để tính tân oán Xác Suất của quý giá F to hơn xuất hiện tình cờ. Phân ba F tương thích bao gồm bậc tự do v1 với v2. Nếu n là số điểm tài liệu với 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 với 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ử quý hiếm Altrộn là 0,05, v1 = 11 – 6 – 1 = 4 với v2 = 6, nấc quan trọng của F là 4,53. Vì F = 459,753674 cao hơn các đối với 4,53, vô cùng nặng nề có khả năng xảy ra quý hiếm F cao cho vậy. (Với Alpha = 0,05, giả thiết rằng không tồn tại mối quan hệ làm sao giữa mức quan hệ của known_y cùng của known_x là bị phủ nhận lúc F vượt quá mức giới hạn, 4,53.) Quý khách hàng rất có thể sử dụng hàm FDIST trong Excel để có được Tỷ Lệ giá trị F cao tới cả này vì chưng vô tình xảy ra. lấy ví dụ, FDIST(459,753674, 4, 6) = 1,37E-7, một Phần Trăm cực nhỏ. Quý khách hàng có thể Tóm lại, bằng cách kiếm tìm nút cho tới hạn F trong bảng hoặc bằng phương pháp dùng hàm FDIST, rằng pmùi hương trình hồi quy có lợi trong việc dự đoán cực hiếm định vị của các chung cư văn uống phòng vào Khu Vực này. Hãy hãy nhờ rằng điều quan trọng đặc biệt là sử dụng các giá trị đúng của v1 với v2 được xem toán thù trong khúc vnạp năng lượng trước đó.

Ví dụ 5: Tính toán thống kê t-Statistics

Một kiểm tra giả thuyết khác sẽ xác định coi 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 vào Ví dụ 3. Ví dụ, để xác định hệ số tuổi thọ mang lại ý nghĩa thống kê, hãy phân tách -234,24 (hệ số độ dốc tuổi thọ) đến 13,268 (lỗi chuẩn mong tính của hệ số tuổi thọ trong ô A15). Dưới phía trên là giá trị t-quan lại sát:

t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7

Nếu giá trị hay đố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 quý hiếm định giá của một cao ốc vnạp năng lượng phòng vào Ví dụ 3. Bảng sau đây thể hiện giá trị hay đối của 4 quý hiếm t-quan sát.

Nếu người mua tsi mê khảo bảng trong sổ tay thống kê, người mua sẽ thấy rằng t-tới hạn, hai phía, với 6 bậc tự vị và Altrộn = 0,05 là 2,447. Cũng có thể tìm thấy quý giá 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ì quý giá 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 tiền trọng khi mong tính giá trị định giá của một cao ốc văn uống phòng. Mỗi vào 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 giống như. Dưới phía trên là các cực hiếm t-quan liêu sát đến mỗi biến số độc lập.

See more: (Pdf) Phương Pháp Luận Nghiên Cứu Khoa Học Phổ Biến, Bài Giảng Về Phương Pháp Luận Nghiên Cứu Khoa Học

Biến số

quý hiếm t-quan sát

Diện tích mặt sàn

5,1

Số lượng văn uống phòng

31,3

Số lượng cửa vào

4,8

Tuổi thọ

17,7

Tất cả những cực hiếm này đều có cực hiếm hay đối lớn hơn 2,447, vì vậy tổng hợp các biến số dùng trong pmùi hương trình hồi quy đều hữu ích vào việc dự đoán giá trị định giá của các cao ốc vnạp năng lượng phòng trong vùng này.