Excel mang lại mercurytravel.com.vn 365 Excel đến mercurytravel.com.vn 365 dành cho máy Mac Excel cho web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel năm 2016 Excel năm 2016 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ê đến 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 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ê mang đến các kiểu tế bào 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.

Bạn đang xem: Vẽ đồ thị tuyến tính trong excel

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, lúc 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 giữ ý 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 có 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 trong một cột đối kháng lẻ, thì mỗi cột của known_x"s được hiểu là một biến số riêng rẽ rẽ.

Nếu phạm vi của known_y"s nằm trong một hàng đơn lẻ, thì mỗi hàng của known_x"s được hiểu là một biến số riêng biệt rẽ.

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

Phạm vi của known_x"s có thể bao gồ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 thước 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 giá trị lô-gic chỉ rõ có bắt buộc hằng số b phải bằng 0 tuyệt 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 đặt bằng 0 và giá trị 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 xuất xắc không.

Nếu stats là TRUE, thì quý giá linest trả về những thống kê hồi quy xẻ sung; do đó, 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ư 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 giá trị y ước tính và thực tế và nằm vào phạm vi giá trị từ 0 tới 1. Nếu nó là 1, thì có một đối sánh hoàn hảo trong mẫu — ko có sự khác biệt nào giữa giá trị y ước tính và giá trị y thực tế. Ở thái cực ngược lại, nếu hệ số xác định là 0, thì phương trình hồi quy ko còn hữu ích vào việc dự đoán giá trị y. Để biết phương pháp tính toán2, hãy xem mục "Ghi chú" ở phần sau nội dung bài viết này.

sey

Lỗi chuẩn mang lại ước tính y.

F

Thống kê F, hoặc giá trị F quan lại sát được. Dùng thống kê F để xác định xem quan tiền 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ự do. Dùng bậc tự bởi để giúp bạn tìm giá trị 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 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 phương hồi quy.

ssresid

Tổng bình phươ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.

Minh họa tiếp sau đây cho thấy thứ tự mà các thống kê hồi quy bổ sung được trả về.

Xem thêm: Công Ty Du Lịch Đường Sắt Mới : Trang Chủ, Công Ty Du Lịch Đường Sắt Mới

*

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): Để tra cứu độ dốc của một đường thẳng, thường xuyên được viết là m, đem hai điểm trên đường thẳng đó, (x1,y1) cùng (x2,y2); độ dốc bằng (y2 - y1)/(x2 - x1).

Cắt Y (b): giao nhau y của một đường thẳng, thường xuyên được viết là b, là quý giá của y trên điểm mà lại đường thẳng giảm trục y.

Phương trình của đường thẳng là y = mx + b. Khi đã biết giá trị của m và b, bạn 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.

Khi bạn chỉ có một biến độc lập x, bạn có thể tìm được độ 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 vì chưng hàm LINEST tính toán phụ thuộc vào độ phân tán trong dữ liệu của bạn. Dữ liệu càng tuyến tính, thì mô 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 bạn 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:

*

*

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 bạn. Mặc dù nhiên, bạn phải quyết định kết quả nào trong nhì 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) 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 giá trị 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 bạn. Sau đó, bạn có thể so sánh giá trị dự đoán với giá trị thực tế. Bạn có thể muốn vẽ đồ thị mang đến cả nhị để có được so sánh trực quan.

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 giá trị y ước tính mang đến điểm đó và giá trị y thực tế của điểm đó. Tổng của các bình phương hiệu này được gọi là tổng bình phươ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 là tổng của các bình phương hiệu giữa giá trị y thực tế và bình quân các giá trị y. Lúc đối số const = FALSE, thì tổng cộng bình phương là tổng các bình phương của các giá trị y thực tế (mà không trừ giá trị y trung bình ra khỏi mỗi giá trị 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ặng dư càng nhỏ so cùng với tổng cộng những bình phương, thì quý giá của thông số xác định, r2, cànglớn, mà đây là một chỉ báo cho biết thêm phương trình tác dụng của phân tích hồi quy diễn đạt rõ đến đâu mối quan hệ giữa những biến số. Cực hiếm của r2 bằng ssreg/sstotal.

Giá trị của df được tính toán như sau, khi không có cột X nào được loại bỏ khỏi tế bào 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ả 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 giá trị của df thêm 1.

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

Hãy lưu lại ý rằng các giá trị y mà phươ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 giá trị y mà bạn 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 vào các hàm SLOPE và INTERCEPT. Sự khác nhau 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 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ề giá trị 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, bạn 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 đối chọi các giá trị y và một cột đối kháng các giá trị 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, tuy thế trong một số trường hợp nó đòi hỏi 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 chép dữ liệu của ví dụ trong bảng tiếp sau đây và dán lại ô A1 của một trang tính Excel mới. Để phương pháp hiển thị kết quả, hãy chọn chúng, dìm F2 và tiếp nối nhấn Enter. Trường hợp cần, bạn cũng có thể điều chỉnh độ rộng cột để xem toàn bộ dữ 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 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ụ trong bảng tiếp sau đây và dán lại ô A1 của một trang tính Excel mới. Để công thức hiển thị kết quả, nên lựa chọn chúng, dấn F2 và tiếp đến nhấn Enter. Nếu như cần, bạn cũng có thể điều chỉnh độ rộng cột để xem toàn bộ 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 cầu tính doanh số bán hàng vào thời điểm tháng thứ chín, dựa trên doanh số các mon 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 lấy ví dụ trong bảng tiếp sau đây và ốp lại ô A1 của một trang tính Excel mới. Để công thức hiển thị kết quả, hãy lựa chọn chúng, dìm F2 và kế tiếp nhấn Enter. Nếu cần, bạn cũng có thể điều chỉnh độ rộng cột nhằm xem toàn bộ dữ liệu.

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

Văn 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ông thức mảng rượu cồn được nhập vào ô A19)

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

Ví dụ 4: áp dụng Thống kê F và r2

Trong ví dụ như trên đây, hệ số xác định,hay r2, là 0,99675 (xem ô A17 trong tác dụng của đối số LINEST), bộc lộ một quan liêu hệ mạnh khỏe giữa những biến số hòa bình và giá chỉ 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ế ko có quan liêu 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 mang lại phân tích thống kê thể hiện một quan lại 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 lại hệ.

Có thể sử dụng giá trị F và df trong áp ra output từ hàm LINEST để nhận xét khả năng xảy ra giá trị F cao hơn. Có thể so sánh F với cái giá trị cho tới hạn trong bảng phân bổ F đã chế tạo hoặc hàm FDIST vào Excel để giám sát xác suất của cực hiếm F mập hơn xuất hiện thêm tình cờ. Phân bố F tương thích có bậc tự do thoải mái v1 với v2. Nếu như n là số điểm dữ liệu và const = TRUE hoặc được bỏ lỡ thì v1 = n – df – 1 với v2 = df. (Nếu const = FALSE thì v1 = n – df với v2 = df.) Hàm FDIST — với cú pháp FDIST(F,v1,v2) — đã trả về xác suất của quý hiếm F cao hơn mở ra tình cờ. Trong lấy một ví dụ này, df = 6 (ô B18) và F = 459,753674 (ô A18).

Giả sử cực hiếm Alpha là 0,05, v1 = 11 – 6 – 1 = 4 với v2 = 6, mức quan trọng đặc biệt của F là 4,53. Vì chưng F = 459,753674 cao hơn nữa nhiều so với 4,53, vô cùng khó có khả năng xảy ra giá trị F cao mang lại vậy. (Với Alpha = 0,05, trả thiết rằng không có mối dục tình nào giữa mức dục tình của known_ycủa known_x là bị không đồng ý khi F vượt quá mức cần thiết giới hạn, 4,53.) chúng ta cũng có thể dùng hàm FDIST vào Excel để sở hữu được tỷ lệ giá trị F cao đến cả này bởi vô tình xảy ra. Ví dụ, FDIST(459,753674, 4, 6) = 1,37E-7, một tỷ lệ cực nhỏ. Bạn cũng 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 phương pháp dùng hàm FDIST, rằng phương trình hồi quy hữu ích trong việc dự kiến giá trị định giá của những cao ốc văn phòng trong quanh vùng này. Hãy hãy nhờ rằng điều quan trọng là sử dụng các giá trị đúng của v1 cùng v2 được đo lường trong đoạn văn 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 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 vào Ví dụ 3. Ví dụ, để kiểm tra hệ số tuổi thọ mang lại ý nghĩa thống kê, hãy phân chia -234,24 (hệ số độ dốc tuổi thọ) mang đến 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 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 vào việc ước tính giá trị định giá của một cao ốc văn phòng vào 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 vào 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ự vày 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 rộng 2,447, cho nên vì vậy tuổi thọ là một biến số quan liêu trọng khi ước tính giá trị định giá của một cao ốc văn 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 tương tự. Dưới phía trên là các giá trị t-quan sát cho mỗi biến số độc lập.

Biến số

giá trị t-quan sát

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

5,1

Số lượng văn 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ó giá trị tuyệt đối lớn rộng 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 vào việc dự đoán giá trị định giá của các cao ốc văn phòng trong vùng này.