Excel mang đến ipes.vn 365 Excel cho ipes.vn 365 dành cho máy Mac Excel cho website Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel năm 2016 Excel năm nhâm thìn for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 Xem thêm...Ít hơn

Mô tả

Hàm LINEST tính toán các thống kê cho 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 mua hàng, rồi trả về một mảng tế bào tả đường thẳng đó. Bạn cũng có thể dựa trên hàm LINEST với các hàm khác để tính toán thống kê cho các kiểu tế bào hình khác là đường tuyến tính vào các tsi số không biết, khái quát 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ý giá, cho nên 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ẽ đường 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 quý hiếm 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 quý giá x và b là quý 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 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à người mua đã biết trong quan hệ y = mx + b.

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

known_x"s Tùy chọn. Tập quý hiếm x mà khách hàng có thể đã biết trong quan hệ y = mx + b.

Phạm vi của known_x"s có thể bao hàm 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 thcầu 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 nlỗi known_y"s.

const Tùy chọn. Một cực hiếm 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à cực 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ề các thống kê lại hồi quy bổ sung; cho nên vì thế, 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.

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 lúc const là FALSE).

r2

Hệ số xác định. So sánh các cực hiếm y cầu tính và thực 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 — không có sự quái lạ nào giữa quý hiếm y ước tính và quý hiếm y thực 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 vào việc dự đoán giá trị y. Để biết cách tính toán2, hãy xem mục "Ghi chú" tại vị trí sau bài viết này.

sey

Lỗi chuẩn đến mong tính y.

F

Thống kê F, hoặc giá trị F quan liêu sát được. Dùng thống kê F để xác định xem quan liêu hệ quan lại 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 ko.

df

Bậc tự vày. Dùng bậc tự vày để giúp người mua tìm giá trị F tới hạn trong bảng thống kê. So sánh các cực hiếm người mua 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 đọc cách tính toán df, hãy xem 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 phươ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.

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

*

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 đường trực tiếp, thường xuyên được viết là m, lấy nhị điểm trên đường trực tiếp đó, (x1,y1) và (x2,y2); độ dốc bằng (y2 - y1)/(x2 - x1).

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

Phương trình của đường thẳng là y = mx + b. Lúc đã biết quý giá của m và b, quý 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 trình đó. Bạn cũng có thể dùng hàm TREND.

Lúc quý khách hàng chỉ có một biến độc lập x, người dùng có thể nhận được độ dốc và quý giá 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 trong 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 thơm 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. Lúc quý khách hàng chỉ có một biến số độc lập x, thì các phép tính cho 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)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 người dùng. Tuy nhiên, người mua hà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) cho một đường thẳng, hoặc GROWTH(known_y"s, known_x"s) mang đế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 người mua hàng. Sau đó, người mua có thể so sánh giá trị dự đoán với quý giá thiết thực. 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 của hiệu số giữa cực hiếm y ước tính đến điểm đó và quý giá y thiết thực của điểm đó. Tổng của các bình pmùi hương hiệu này được hiểu 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 pmùi hương, sstotal. lúc đối số const = TRUE hoặc được bỏ qua, thì tổng cộng bình pmùi hương là tổng của các bình phương hiệu giữa quý giá y thiết thực và bình quân các quý hiếm y. khi đối số const = FALSE, thì tổng cộng bình phương thơm là tổng các bình phương của các quý hiếm y thực tế (mà ko trừ cực hiếm y trung bình ra khỏi mỗi quý 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 bé dại so với tổng số những bình phương thơm, thì quý giá của hệ số xác định, r2, càngto, mà đây là một chỉ báo cho biết thêm phương thơm trình hiệu quả của so sánh hồi quy biểu hiện rõ đến đâu mối quan hệ thân các trở nên số. Giá trị của r2 bởi ssreg/sstotal.

Giá trị của df được tính toán nlỗi 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ả nhì trường hợp, cột X đã được loại bỏ bởi 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ố, khách hàng hãy dùng dấu phẩy để phân tách các quý giá chứa trong 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 mua hàng.

Hãy lưu ý rằng các quý hiếm 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 cực hiếm y mà người mua dùng để xác định phương 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 vào 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ả khác nhau Khi 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ề quý giá 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à vào 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, 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 tiêu dùng có một cột đối chọi các giá trị y và một cột 1-1 các quý 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 cực 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 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ách làm hiển thị tác dụng, nên lựa chọn bọn chúng, thừa nhận F2 với tiếp nối thừa nhận Enter. Nếu nên, bạn có thể điều chỉnh độ rộng cột giúp thấy tất cả tài liệu.

Y đã biết

X đã biết

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 trong ô A7:B7)

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

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

Sao chép dữ liệu của ví dụ trong bảng sau đây với dán vào ô A1 của một trang tính Excel new. Để bí quyết hiển thị tác dụng, hãy lựa chọn bọn chúng, dấn F2 cùng kế tiếp 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 lớn 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 thù ước tính doanh số bán hàng vào tháng thứ chín, dựa trên doanh số các tháng từ 1 đến 6.

Ví dụ 3: Hồi quy Tuyến tính Đa biến

Sao chép tài liệu của ví dụ vào bảng tiếp sau đây với dính vào ô A1 của một trang tính Excel bắt đầu. Để cách làm hiển thị kết quả, hãy chọn bọn chúng, nhấn F2 với sau đó dấn Enter. Nếu đề nghị, chúng ta cũng có thể kiểm soát và điều chỉnh độ rộng lớn cột giúp thấy 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)

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ách làm mảng động được nhtràn lên ô A19)

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

lấy ví dụ 4: Sử dụng Thống kê F và r2

Trong ví dụ trên trên đây, thông số xác định,tốt r2, là 0,99675 (xem ô A17 vào kết quả của đối số LINEST), trình bày một quan hệ mạnh mẽ thân các thay đổi số chủ quyền cùng giá thành. Bạn có thể dùng thống kê F để xác định coi những kết quả này, với cực hiếm r2 cao nlỗi 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 ko có quan hệ nào giữa các biến số, nhưng mà người tiêu dùng đã lấy một mẫu hiếm gặp về 11 tòa cao ốc vnạp năng lượng 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 không đúng lầm rằng có một quan liêu hệ.

Có thể cần sử dụng giá trị F cùng df trong cổng đầu ra trường đoản cú hàm LINEST nhằm reviews kỹ năng xẩy ra quý hiếm F cao hơn nữa. cũng có thể so sánh F với giá trị cho tới hạn vào bảng phân bổ F đang xuất bản hoặc hàm FDIST vào Excel để tính toán thù phần trăm của giá trị F to hơn xuất hiện vô tình. Phân tía F thích hợp gồm bậc tự do thoải mái v1 và v2. Nếu n là số điểm dữ liệu với const = TRUE hoặc được bỏ qua thì v1 = n – df – 1 cùng 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ề xác suất của cực hiếm F cao hơn nữa lộ diện tình cờ. Trong ví dụ này, df = 6 (ô B18) với F = 459,753674 (ô A18).

Giả sử cực hiếm Alpha là 0,05, v1 = 11 – 6 – 1 = 4 với v2 = 6, nút đặc trưng của F là 4,53. Vì F = 459,753674 cao hơn nữa những đối với 4,53, vô cùng cạnh tranh có công dụng xẩy ra quý giá F cao cho vậy. (Với Alpha = 0,05, đưa thiết rằng không tồn tại mối quan hệ nào thân nấc dục tình của known_y cùng của known_x là bị khước từ lúc F quá trên mức cần thiết giới hạn, 4,53.) Quý khách hàng hoàn toàn có thể cần sử dụng hàm FDIST trong Excel để có được Xác Suất quý giá F cao đến cả này bởi vì vô tình xảy ra. lấy ví dụ, FDIST(459,753674, 4, 6) = 1,37E-7, một Tỷ Lệ cực nhỏ tuổi. Quý khách hàng rất có thể tóm lại, bằng cách kiếm tìm nấc cho tới hạn F vào bảng hoặc bằng phương pháp dùng hàm FDIST, rằng phương trình hồi quy bổ ích trong bài toán dự đân oán giá trị định vị của những cao ốc vnạp năng lượng phòng vào khu vực này. Hãy nhớ là điều đặc trưng là sử dụng những giá trị đúng của v1 và v2 được tính toán trong khúc văn trước đó.

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 xem mỗi hệ số độ dốc có hữu ích không trong việc ước tính quý giá định giá của một cao ốc vnạp năng lượng phòng trong Ví dụ 3. Ví dụ, để kiểm tra 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 đến 13,268 (lỗi chuẩn cầu tính của hệ số tuổi thọ trong ô A15). Dưới đây là giá trị t-quan liêu sát:

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

Nếu cực hiếm tố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 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 tiếp sau đây thể hiện cực hiếm giỏi đối của 4 quý giá t-quan sát.

Nếu người mua hàng ttê mê khảo bảng trong sổ tay thống kê, người mua hàng sẽ thấy rằng t-tới hạn, hai phía, với 6 bậc tự vị và Alpha = 0,05 là 2,447. Cũng có thể nhận được giá trị 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ì cực hiếm giỏi đối của t (17,7) lớn hơn 2,447, cho nên vì vậy tuổi thọ là một biến số quan trọng khi ước tính cực hiếm đị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 kiểm tra ý nghĩa thống kê theo cách giống như. Dưới phía trên là các giá trị t-quan sát mang lại mỗi biến số độc lập.

Xem thêm: Cách Làm Thịt Ba Chỉ Ngâm Nước Mắm Ngon Ai Ăn Cũng Tấm Tắc Khen

Biến số

quý giá t-quan tiền sát

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 cực hiếm này đều có giá trị xuất xắc đối lớn rộng 2,447, vì vậy tổng số các biến số dùng vào 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 vnạp năng lượng phòng vào vùng này.