Quý Khách vẫn xem: Kiểm Tra Pmùi hương Trình Hồi Quy Tuyến Tính Bằng Excel, Hàm Xử Lý Thống Kê Bằng Excel Tại hoangdaokimgiap.vn

Excel mang lại hoangdaokimgiap.vn 365Excel mang lại hoangdaokimgiap.vn 365 dành riêng cho trang bị MacExcel cho webExcel 2019Excel 2016Excel 2019 for MacExcel 2013Excel 2010Excel 2007Excel năm nhâm thìn for MacExcel for Mac 2011Excel Starter 2010Thêm…Ít rộng

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 quý khách, rồi trả về một mảng mô 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ê mang đến các kiểu tế bào hình khác là đường tuyến tính trong các tđắm say 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 quý giá, cho nên 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.

Bạn đang xem: Phương trình hồi quy tuyến tính trong excel

Đang xem: Kiểm tra pmùi hương trình hồi quy tuyến tính bằng 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 quý giá x, Khi mà giá trị 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à quý giá 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 tất cả những đối số sau đây:

Cú pháp

known_y”s Bắt buộc. Tập quý giá y mà người dùng đã biết trong quan tiền 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 đọ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 chọi lẻ, thì mỗi hàng của known_x”s được gọi là một biến số riêng biệt rẽ.

known_x”s Tùy chọn. Tập quý hiếm x mà khách hàng có thể đã biết vào 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 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 thcầu 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à 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 những thống kê hồi quy té 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,mét vuông,…,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 quý giá y ước tính và thiết thật và nằm trong phạm vi quý hiếm 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 dị nào giữa quý giá y cầu tính và cực hiếm 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 trình hồi quy ko 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 xem mục “Ghi chú” tại đoạn sau bài viết này.

sey

Lỗi chuẩn mang lại cầu tính y.

F

Thống kê F, hoặc cực hiếm F quan tiền sát được. Dùng thống kê F để xác định xem quan liêu 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 ko.

df

Bậc tự bởi vì. Dùng bậc tự vày để giúp khách hàng tìm cực hiếm F tới hạn trong bảng thống kê. So sánh các cực hiếm quý khách 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 phát âm 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 phươ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 tiếp sau đây mang lại thấy thứ tự mà các thống kê hồi quy bổ sung được trả về.

*
*

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 bạn. Tuy nhiên, khách hàng 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) đế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ý giá 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 tiêu dùng. Sau đó, người mua có thể so sánh quý hiếm dự đoán với giá trị thiết thực. Bạn có thể muốn vẽ đồ thị cho cả nhị để có được so sánh trực quan lại.

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ầu tính mang đến điểm đó và cực hiếm y thực tế 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 phương thặng dư, ssresid. Sau đó, Excel tính toán tổng cộng bình phương, sstotal. khi đố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 quý giá y thực tế và bình quân các giá trị y. Khi đối số const = FALSE, thì tổng cộng bình phương thơm là tổng các bình pmùi hương của các quý hiếm y thiết thật (mà ko trừ giá trị 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 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ỏ tuổi so với tổng số những bình pmùi hương, thì giá trị của hệ số khẳng định, r2, cànglớn, mà đó là một chỉ báo cho thấy pmùi hương trình hiệu quả của so sánh hồi quy biểu lộ rõ mang lại đâu quan hệ thân các biến đổi số. Giá trị của r2 bởi 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 do 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ỏ vày tính cộng tuyến sẽ làm tăng cực hiếm của df thêm 1.

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

Hãy giữ ý rằng các cực hiếm y mà pmùi hươ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à người mua hàng dùng để xác định phương thơm 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ự 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 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ý 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ề quý giá 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à trong 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, 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 mang đến hàm LINEST. Ví dụ, công thức sau đây:

=LINEST(yvalues, xvalues^COLUMN($A:$C))

hoạt động lúc người mua có một cột đối kháng các quý giá 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 (nhiều 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ế vào 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 tài liệu của ví dụ trong bảng sau đây cùng ốp lại ô A1 của một trang tính Excel bắt đầu. Để bí quyết hiển thị tác dụng, nên chọn lựa chúng, dìm F2 với tiếp nối dìm Enter. Nếu nên, bạn có thể kiểm soát và điều chỉnh độ rộng cột để thấy toàn bộ dữ 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 chxay tài liệu của ví dụ trong bảng tiếp sau đây với ốp lại ô A1 của một trang tính Excel bắt đầu. Để bí quyết hiển thị hiệu quả, nên chọn bọn chúng, thừa nhận F2 cùng kế tiếp nhấn Enter. Nếu buộc phải, bạn có thể điều chỉnh độ rộng cột giúp thấy 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 mặt hàng trong thời điểm 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 chnghiền dữ liệu của ví dụ trong bảng dưới đây với dán lại ô A1 của một trang tính Excel mới. Để công thức hiển thị hiệu quả, hãy lựa chọn chúng, dìm F2 và sau đó nhận 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 để thấy toàn bộ tài liệu.

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

Văn uố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 đụng đượ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 và r2

Trong ví dụ trên trên đây, thông số khẳng định,xuất xắc r2, là 0,99675 (xem ô A17 vào hiệu quả của đối số LINEST), thể hiện một dục tình mạnh bạo giữa những biến số chủ quyền và giá thành. 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ư 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 liêu hệ nào giữa các biến số, dẫu vậy khách hà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 mang đến 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 không đúng lầm rằng có một quan hệ.

cũng có thể dùng cực hiếm F và df vào đầu ra từ bỏ hàm LINEST nhằm review khả năng xẩy ra cực hiếm F cao hơn. Có thể so sánh F với giá trị cho tới hạn vào bảng phân bổ F sẽ tạo ra hoặc hàm FDIST trong Excel để tính tân oán Tỷ Lệ của cực hiếm F lớn hơn xuất hiện thêm vô tình. Phân bố F thích hợp có bậc tự do v1 cùng 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) — đang trả về xác suất của cực hiếm F cao hơn mở ra vô tình. Trong 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à v2 = 6, nút đặc biệt của F là 4,53. Vì F = 459,753674 cao hơn những đối với 4,53, hết sức nặng nề có công dụng xẩy ra giá trị F cao mang đến vậy. (Với Altrộn = 0,05, giả thiết rằng không có mối quan hệ như thế nào giữa mức quan hệ tình dục của known_y cùng của known_x là bị phủ nhận khi F vượt quá mức cho phép giới hạn, 4,53.) quý khách hàng có thể sử dụng hàm FDIST vào Excel để sở hữu được Phần Trăm quý giá F cao đến mứ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 tỷ lệ cực bé dại. Quý Khách rất có thể kết luận, bằng phương pháp tra cứu nấc tới hạn F vào bảng hoặc bằng cách sử dụng hàm FDIST, rằng phương thơm trình hồi quy có lợi trong việc dự đoán quý giá định vị của những chung cư văn uống phòng trong khoanh vùng này. Hãy đừng quên điều đặc trưng là thực hiện các quý giá đúng của v1 và v2 được xem toán thù trong khúc vnạp năng lượng trước kia.

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 vào 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. Ví dụ, để kiểm tra hệ số tuổi thọ đến ý nghĩa thống kê, hãy phân 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 trên đây là giá trị t-quan tiền sát:

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

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

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

Xem thêm: Top 7 Phần Mềm Test Hiệu Năng Máy Tính, Các Cách Kiểm Tra Hiệu Suất Máy Tính

Biến số

cực hiếm t-quan lại 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 cực hiếm này đều có giá trị tốt đối lớn hơn 2,447, vì vậy tổng cộng 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 quý hiếm định giá của các cao ốc văn uống phòng trong vùng này.