Bạn hiện đang ngoại tuyến, hãy chờ internet để kết nối lại

Mô tả về các chức năng LINEST trong Excel 2003 và các phiên bản sau của Excel

Hỗ trợ dành cho Office 2003 đã kết thúc

Microsoft đã kết thúc hỗ trợ dành cho Office 2003 vào ngày 8 tháng 4 năm 2014. Thay đổi này đã ảnh hưởng đến các bản cập nhật phần mềm và các tùy chọn bảo mật của bạn. Tìm hiểu ý nghĩa của điều này với bạn và cách thực hiện để luôn được bảo vệ.

QUAN TRỌNG: Bài viết này được dịch bằng phần mềm dịch máy của Microsoft chứ không phải do con người dịch. Microsoft cung cấp các bài viết do con người dịch và cả các bài viết do máy dịch để bạn có thể truy cập vào tất cả các bài viết trong Cơ sở Kiến thức của chúng tôi bằng ngôn ngữ của bạn. Tuy nhiên, bài viết do máy dịch không phải lúc nào cũng hoàn hảo. Loại bài viết này có thể chứa các sai sót về từ vựng, cú pháp hoặc ngữ pháp, giống như một người nước ngoài có thể mắc sai sót khi nói ngôn ngữ của bạn. Microsoft không chịu trách nhiệm về bất kỳ sự thiếu chính xác, sai sót hoặc thiệt hại nào do việc dịch sai nội dung hoặc do hoạt động sử dụng của khách hàng gây ra. Microsoft cũng thường xuyên cập nhật phần mềm dịch máy này.

Nhấp chuột vào đây để xem bản tiếng Anh của bài viết này:828533
TÓM TẮT
Mục đích của bài viết này là để mô tả LINEST chức năng trong Microsoft Office Excel 2003 và các phiên bản sau của Excel, để minh họa cách chức năng LINEST là được sử dụng, và so sánh kết quả của các chức năng LINEST trong Excel 2003 và các phiên bản sau của Excel với các hiển thị của các chức năng LINEST trong phiên bản trước của Excel.

Microsoft đã có những thay đổi rộng rãi cho các chức năng LINEST để sửa công thức không chính xác được được sử dụng khi dòng hồi qui phải đi qua nguồn gốc. Những thay đổi cũng phải trả tiền quan tâm hơn đến các vấn đề liên quan đến các biến dự báo chốt. Vì các cải tiến này rộng lớn, bài viết này tập trung hơn vào những cải tiến và ít hơn vào hướng dẫn người sử dụng về cách sử dụng LINEST.

Microsoft Excel 2004 cho Mac thông tin

Các chức năng thống kê trong Excel 2004 cho Mac đã được cập nhật bằng cách sử dụng các thuật toán tương tự đã được sử dụng để cập nhật các chức năng thống kê trong Microsoft Office Excel 2003 và các phiên bản sau của Excel. Bất kỳ thông tin nào trong bài viết này mô tả cách thức một chức năng hoạt động hoặc làm thế nào một hàm được lần cho Excel 2003 và các phiên bản sau của Excel cũng áp dụng cho Excel 2004 cho Mac.
THÔNG TIN THÊM
LINEST (của known_y, known_x của, đánh chặn, thống kê) chức năng được sử dụng để thực hiện các hồi quy tuyến tính. Một tiêu chí tối thiểu là được sử dụng, và LINEST cố gắng để tìm thấy phù hợp nhất theo tiêu chuẩn đó. Của Known_y đại diện cho dữ liệu vào phụ thuộc vào biến và known_x của đại diện cho dữ liệu trên một trong những hoặc biến độc lập hơn. Đối số thứ hai là tùy chọn. Nếu thứ hai đối số bỏ qua, nó được giả định là một mảng của cùng kích thước của known_y có chứa các giá trị {1, 2, 3, và do đó trên}.

Các đối số cuối cùng là đặt thành TRUE nếu bạn muốn có số liệu thống kê bổ sung (khoản tiền khác nhau của hình vuông, r-bình phương, f-số liệu thống kê, hoặc lỗi chuẩn của hệ số hồi qui, cho Ví dụ). Trong trường hợp này, LINEST phải được nhập như là một công thức mảng. Cuối cùng đối số là tuỳ chọn; Nếu nó bỏ qua, nó được coi là là sai. Các mảng Kích thước là hàng năm bởi một số cột bằng số lượng các biến độc lập cộng một nếu đối số thứ ba được đặt thành TRUE (nếu các đối số thứ ba không được đặt thành TRUE, số lượng các cột là bằng với số các biến độc lập). Thiết lập đối số thứ ba để sai trong Microsoft Excel 2002 và trong phiên bản trước của Excel đòi hỏi một workaround. Workaround workaround này thảo luận sau này trong điều này bài viết.

Trong những ứng dụng phổ biến nhất của LINEST, đánh chặn đối số là đặt thành TRUE. Thiết đặt này có nghĩa rằng bạn muốn mô hình tuyến tính hồi qui để bao gồm khả năng xảy ra một yếu tố khác không đánh chặn trong mô hình của nó. Nếu known_x của được đại diện trong dữ liệu cột, đánh chặn thiết đặt True nói với LINEST thêm một cột dữ liệu là đầy 1s như dữ liệu vào bổ sung biến độc lập. Đối số đánh chặn nên được đặt thành FALSE chỉ khi bạn bạn muốn ép buộc dây hồi qui để đi qua nguồn gốc. Cho Excel 2002 và cho phiên bản trước của Excel, thiết lập đối số này để sai luôn trả về kết quả không đúng, ít nhất trong thống kê chi tiết có sẵn từ LINEST. Bài viết này thảo luận về vấn đề này và cung cấp một workaround. Vấn đề này có được sửa chữa trong Excel 2003 và các phiên bản sau của Excel. Đối số thứ ba là tùy chọn; Nếu nó bỏ qua, nó được coi là là đúng sự thật.

Để dễ dàng triển lãm phần còn lại của bài viết này, giả sử rằng dữ liệu được sắp xếp theo cột, do đó của known_y là một cột của y dữ liệu và known_x của là một hoặc nhiều cột của x dữ liệu. Các Kích thước (hoặc độ dài) của mỗi của các cột phải được bình đẳng. Tất cả các sau những quan sát được bình đẳng với đúng nếu dữ liệu không được sắp xếp theo cột, nhưng nó dễ dàng hơn để thảo luận về đĩa đơn này (thường xuyên nhất được sử dụng) trường hợp.

Một lý do để thiết lập các đối số đánh chặn false là nếu bạn đã rõ ràng đã mô phỏng đánh chặn trong dữ liệu bằng cách bao gồm một cột của 1s. Trong Excel 2002 và trong phiên bản trước của Excel, giải pháp tốt nhất là để bỏ qua cột của 1s và để gọi LINEST với cột này thiếu từ known_x của và với đối số đánh chặn đặt thành TRUE. Excel 2002 và trước đó các phiên bản Excel luôn luôn trở lại kết quả là không đúng khi các đối số đánh chặn được đặt thành FALSE. Cho Excel 2003 và các phiên bản sau của Excel, cách tiếp cận này là cũng ưa thích, mặc dù các công thức đã được sửa chữa cho Excel 2003 và các phiên bản sau của Excel.

Hiệu suất của LINEST trong phiên bản trước của Excel (hay chính xác hơn, hiệu suất của các Phân tích ToolPak tuyến tính hồi qui công cụ gọi là LINEST) đã justifiably chỉ trích (xem phần "Tham khảo", bài viết này để biết thêm thông tin). Mối quan tâm chính về Excel của hồi quy tuyến tính công cụ là một thiếu sự chú ý đến các vấn đề của các biến dự báo chốt (hoặc gần như chốt). Bằng cách sử dụng datasets đã được cung cấp bởi viện quốc gia cho các tiêu chuẩn và Công nghệ (NIST, trước đây là Cục tiêu chuẩn quốc gia) được thiết kế để kiểm tra hiệu quả của phần mềm thống kê, số không chính xác đã loài này có ở các khu vực của hồi quy tuyến tính, phân tích của phương sai, và phi tuyến tính hồi quy. Trong Excel 2003 và trong phiên bản sau của Excel, những vấn đề đã được giải quyết, ngoại trừ phi tuyến tính hồi qui, gây ra bởi một vấn đề với người giải quyết add-in thay vì với chức năng thống kê hoặc phân tích ToolPak. Chức năng RAND trong Excel là cũng đặt thông qua các bài kiểm tra tiêu chuẩn ngẫu nhiên và báo cáo subpar kết quả. Chức năng RAND cũng được được sửa đổi trong Excel 2003 và các phiên bản sau của Excel.

LINEST đã sử dụng các phương trình bình thường"" cho việc tìm kiếm hồi qui hệ số. Phương pháp này là ít ổn định đạo hơn số ít giá trị Phân hủy hoặc phân hủy QR. Excel 2003 và các phiên bản sau của Excel đã triển khai QR phân hủy. Trong khi đây là một kỹ thuật tiêu chuẩn được mô tả trong nhiều văn bản, một nhỏ Ví dụ được thảo luận trong bài viết này. QR phân hủy một cách hiệu quả phân tích collinearity các vấn đề và không bao gồm bất kỳ cột dữ liệu từ các mô hình cuối cùng nếu đó cột có thể được thể hiện như một khoản bội số của cột bao gồm. Gần collinearity được xử lý như vậy; một tập hợp các cột là gần chốt Nếu, khi bạn cố gắng để thể hiện một dữ liệu cột như một khoản bội số của những người khác, phù hợp với kết quả là rất gần gũi. Ví dụ, tổng bình phương sự khác nhau giữa các cột dữ liệu và các giá trị được trang bị là ít hơn 10^(-12).

Tệp trợ giúp LINEST đã được Cập Nhật trong Excel 2003 và trong phiên bản sau của Excel.

Nói tóm lại, những thay đổi chính là như sau:
  • Công thức tính toán thống kê bổ sung (như vậy như r bình phương và khoản tiền khác nhau của hình vuông) mà được sử dụng khi đánh chặn được thiết lập sai đã được sửa chữa.
  • QR phân hủy đã được thực hiện để giải quyết tất cả các trường hợp, bất kể các thiết lập của các đối số thứ ba và thứ tư.

Cú pháp

LINEST(known_y's, known_x's, intercept, statistics)
Đối số, của known_y và known_x, phải là mảng hoặc các dãy tế bào đó có liên quan đến kích thước. Nếu known_y của là một trong những cột bởi m hàng, known_x nên là c cột bởi m hàng nơi c là lớn hơn hoặc bằng 1; c là các số lượng dự báo biến; và m là số lượng các điểm dữ liệu. (Tương tự như các mối quan hệ phải giữ nếu known_y của là đặt ra trong một đơn hàng; của known_x phải ở trong r hàng nơi r là lớn hơn hoặc bằng với một hàng, và của known_y và known_x của nên có cùng một số cột.) Đánh chặn và thống kê đối số phải được đặt là TRUE hay FALSE (hoặc 0 hoặc 1, mà Excel diễn giải như là sai hoặc đúng, tương ứng). Các đối số cuối ba LINEST là tất cả các tùy chọn. Nếu bạn bỏ qua đối số thứ hai, LINEST giả định một đĩa đơn dự báo có chứa các mục {1, 2, 3,...}. Nếu đối số thứ ba là bỏ qua, nó được coi là là đúng sự thật. Nếu đối số thứ tư bỏ qua, đó là giải thích là sai.

Việc sử dụng phổ biến nhất LINEST bao gồm hai phạm vi của các tế bào có chứa dữ liệu, chẳng hạn như LINEST (A1:A100, B1:F100, TRUE» SỰ THẬT). Bởi vì thường là nhiều hơn một dự báo biến, thứ hai đối số trong ví dụ này chứa nhiều cột. Trong ví dụ này, có những một trăm đối tượng, giá trị một phụ thuộc vào biến (known_y) cho mỗi chủ đề và năm giá trị biến độc lập (known_x) cho mỗi chủ đề.

Ví dụ về cách sử dụng

Ví dụ bảng tính Excel riêng biệt được cung cấp để minh họa khái niệm quan trọng khác nhau.

Để minh họa cho một khoản tiền tiêu cực của hình vuông trong Excel với đối số thứ ba tập sai, làm theo các bước sau:
  1. Tạo ra một bảng tính Excel trống, và sau đó sao chép dưới đây bảng.
  2. Trong Excel 2003 và trong phiên bản trước của Excel, bấm vào ô A1 trong của bạn bảng tính Excel trống, và sau đó nhấp vào Dán ngày các Chỉnh sửa trình đơn để cho các mục trong bảng điền vào các tế bào A1:H19 trong bảng tính của bạn.

    Trong Excel 2007, bấm vào ô A1 trong của bạn bảng tính Excel trống, và sau đó nhấp vào Dán trong các Bảng tạm nhóm vào các Trang chủ Tab để cho các mục trong bảng điền vào các tế bào A1:H19 trong bảng tính của bạn.
  3. Sau khi bạn dán bảng vào của bạn bảng tính Excel mới, Nhấp vào Tùy chọn dán, sau đó bấm Điểm đến trận đấu Định dạng.
  4. Trong Excel 2003 và trong phiên bản trước của Excel, trong khi vẫn chọn phạm vi dán, điểm đến Cột trên cácĐịnh dạng trình đơn, và sau đó nhấp vàoLựa chọn AutoFit.

    Trong Excel 2007, trong khi vẫn chọn phạm vi dán, nhấp vào Định dạng trong các Các tế bào nhóm vào các Trang chủ tab, và sau đó nhấp vào AutoFit Column Width.
XY
111
212
313
Excel 2002 và Phiên bản trước của ExcelExcel 2003 và các phiên bản sau của Excel
LINEST ĐẦU RA:LINEST RA:LINEST ĐẦU RA:
5.28571428605.28571428605.2857142860
1.237179148# N/A1.237179148# N/A1.237179148# N/A
0.9012508234.629100499-20.428571434.6291004990.9012508234.629100499
18.253333332-1.906666667218.253333332
391.142857142.85714286-40.8571428642.85714286391.142857142.85714286
2<--linest's total="" sum="" of=""></--linest's>
42.85714286<--linest's correct="" residual="" sum="" of=""></--linest's>
-40.85714286<-- difference,="" linest's="" regression="" sum="" of=""></-->
434<--correct total="" sum="" of=""></--correct>
42.85714286<--linest's correct="" residual="" sum="" of=""></--linest's>
391.1428571<-- difference,="" correct="" regression="" sum="" of=""></-->
Mục có trong các tế bào A7:B11 tương ứng với sản lượng trong Excel 2003 và trong phiên bản sau của Excel. Để tạo ra sản lượng thích hợp cho các phiên bản của Excel, bấm tế bào A7, chọn phạm vi di động A7:B11 và sau đó nhập sau mảng công thức:
= LINEST(B2:B4, A2:A4, FALSE, TRUE)
Ví dụ này tập trung vào một mô hình LINEST mà có đối số thứ ba set false. LINEST trong Excel 2002 và trong phiên bản trước của Excel sử dụng một công thức cho số tiền của hình vuông là không đúng trong trường hợp này. Công thức này underestimates các thực tế số tiền của hình vuông và luôn luôn dẫn đến giá trị của hồi quy tổng của Quảng trường mà không phải là chính xác. Công thức này đôi khi sản lượng tiêu cực hồi qui tổng của hình vuông và các giá trị r bình phương tiêu cực.

Các tế bào D6:E11 hiển thị LINEST đầu ra trong Excel 2002 và trong phiên bản trước của Excel. Trong các phiên bản Excel, LINEST tính số tiền vuông cho các mô hình có đối số thứ ba đặt thành FALSE theo tổng bình phương sai của các giá trị y về cột y có nghĩa là. Điều này giá trị được hiển thị trong ô A13 và là một tính toán thích hợp khi thứ ba đối số được thiết lập đúng sự thật. Tuy nhiên, khi đối số thứ ba được đặt thành FALSE, các chính xác số tiền của hình vuông là tổng bình phương của các giá trị y và được hiển thị trong tế bào A17. Sử dụng công thức sai cho số tiền của hình vuông dẫn đến các phủ định hồi quy tổng của hình vuông trong tế bào A15. Đầu ra chính xác trong Excel 2003 được hiển thị trong các tế bào G6:H11.

Nếu bạn sử dụng một phiên bản cũ của Excel và nếu bạn muốn ép buộc tốt nhất phù hợp với hồi quy tuyến tính thông qua các nguồn gốc, bạn phải tính toán một số mục nhập trong cuối ba hàng đầu ra xe một lần nữa. Để làm điều này, sử dụng các workaround sau đây.

Chú ý Bạn có thể tham khảo bảng tính trước.
  1. Gọi Excel với đối số thứ tư đặt True để tạo ra mảng sản lượng chi tiết. Bởi vì bạn sử dụng Excel 2002 hoặc phiên bản trước của Excel, cho rằng sản lượng này là trong các tế bào D7:E11.

    Lưu ý rằng chỉ có các mục sau đây yêu cầu sửa đổi: r bình phương, số liệu thống kê f và hồi quy tổng của hình vuông. Những khoản mục xuất hiện trong các tế bào Q9, D10 và D11.
  2. Tính toán số tiền quảng trường một lần nữa như SUMSQ(known_y's). Trong ví dụ này, SUMSQ(B2:B4).

    Hồi quy tổng của hình vuông (các giá trị thay thế mục trong tế bào D11) là SUMSQ(B2:B4)-E11. Giá trị này là số tiền của hình vuông trừ đi tổng dư của hình vuông (như tính toán một cách chính xác bởi LINEST).
  3. R bình phương (giá trị thay thế mục trong tế bào Q9) là sau đó hồi quy tổng của hình vuông chia cho số tiền của hình vuông.
  4. Thống kê f là f số liệu thống kê cho LINEST (trong tế bào D10) nhân với số tiền chính xác hồi quy của hình vuông, và sau đó chia cho các LINEST hồi quy tổng của hình vuông (trong tế bào D11).
Thủ tục này sửa chữa công thức trong Excel 2002 và trước đó các phiên bản Excel, nhưng không không địa chỉ collinearity. Vì vậy, các thủ tục hoạt động tốt chỉ mà không có collinearity (trường hợp điển hình trong thực tế). Vấn đề số có thể được phóng đại khi collinearity hoặc gần collinearity tồn tại, tương tự như những gì xuất hiện trong NIST datasets. Trường hợp thậm chí đơn giản có thể tạo ra vấn đề, như minh họa trong các ví dụ sau.

Dự báo cột (known_x) đang chốt nếu ít nhất một cột, c, có thể được thể hiện như một khoản bội số của những người khác (c1, c2, và có lẽ thêm cột). Cột c thường xuyên được gọi là dư thừa, vì các thông tin mà nó chứa có thể được xây dựng từ các cột c1, c2 và cột khác. Nguyên tắc cơ bản trong sự hiện diện của collinearity là rằng kết quả sẽ không bị ảnh hưởng bởi cho dù một dự phòng cột bao gồm trong dữ liệu gốc hoặc gỡ bỏ từ dữ liệu gốc. Bởi vì LINEST trong Excel 2002 và trong phiên bản trước của Excel không nhìn collinearity, nguyên tắc này một cách dễ dàng bị vi phạm. Dự báo cột gần chốt nếu ít nhất một cột, c, có thể được thể hiện như là gần như tương đương với một khoản tiền của bội số của những người khác (c1, c2, và những người khác). Trong trường hợp này, "gần như bằng nhau" có nghĩa một khoản tiền rất nhỏ của bình phương sai của mục nhập trong c từ tương ứng mục trong tổng trọng số của c1, c2 và cột khác; "rất nhỏ" có thể là ít hơn 10^(-12), ví dụ.

Để minh họa cho collinearity, hãy làm theo các bước sau:
  1. Tạo ra một bảng tính Excel trống, và sau đó sao chép dưới đây bảng.
  2. Trong Excel 2003 và trong phiên bản trước của Excel, bấm vào ô A1 trong của bạn bảng tính Excel trống, và sau đó nhấp vào Dán ngày các Chỉnh sửa trình đơn, để cho các mục trong bảng điền vào các tế bào A1:N27 trong bảng tính của bạn.

    Trong Excel 2007, bấm vào ô A1 trong của bạn bảng tính Excel trống, và sau đó nhấp vào Dán trong các Bảng tạm nhóm vào các Trang chủ Tab để cho các mục trong bảng điền các tế bào A1:N27 trong bảng tính của bạn.
  3. Sau khi bạn dán bảng vào của bạn bảng tính Excel mới, Nhấp vào Tùy chọn dán, sau đó bấm Điểm đến trận đấu Định dạng.
  4. Trong Excel 2003 và trong phiên bản trước của Excel, trong khi vẫn chọn phạm vi dán, điểm đến Cột trên cácĐịnh dạng trình đơn, và sau đó nhấp vàoLựa chọn AutoFit.

    Trong Excel 2007, trong khi vẫn chọn phạm vi dán, nhấp vào Định dạng trong các Các tế bào nhóm vào các Trang chủ tab, và sau đó nhấp vào AutoFit Column Width.
y's:x:
1121
2341
3451
4671
5781
LINEST sử dụng cột B, C:Các giá trị trong Excel 2002 và trước đó các phiên bản của Excel:Các giá trị trong Excel 2003 và các phiên bản sau của Excel:
# NUM!# NUM!# NUM!00.6578950.236842
# NUM!# NUM!# NUM!00.043860.206653
# NUM!# NUM!# NUM!0.9868420.209427# N/A
# NUM!# NUM!# NUM!2253# N/A
# NUM!# NUM!# NUM!9.8684210.131579# N/A
LINEST sử dụng cột B, C, D với sai 3rd arg:
0.403646-0.16680.824698000.2368420.4210530
248449124844912484491# N/A00.2066530.246552# N/A
0.9868420.256495# N/A# N/A0.9976080.209427# N/A# N/A
502# N/A# N/A625.53# N/A# N/A
9.8684210.131579# N/A# N/A54.868420.131579# N/A# N/A
LINEST sử dụng cột b chỉ
0.6578950.2368420.6578950.236842
0.043860.2066530.043860.206653
0.9868420.2094270.9868420.209427
22532253
9.8684210.1315799.8684210.131579
Dữ liệu được bao gồm trong các tế bào A1:D6. Kết quả của ba khác nhau cho các cuộc gọi LINEST sẽ được hiển thị cho Excel 2002 và cho phiên bản trước của Excel trong các tế bào F8:I27, và kết quả cho Excel 2003 và các phiên bản sau của Excel trong các tế bào K8:N27.

Để xác minh rằng các kết quả trong phiên bản của bạn trùng với kết quả trong các tế bào F8:I27 hoặc trong các tế bào K8:N27, bạn có thể nhập công thức ba mảng sau đây:
  • Chọn cell A9 và phạm vi di động A9:C13, và sau đó nhập công thức sau đây như là một công thức mảng:
    =LINEST(A2:A6,B2:C6,TRUE,TRUE)
  • Chọn cell A16 và phạm vi di động A16:D20, và sau đó nhập công thức sau đây như là một công thức mảng:
    =LINEST(A2:A6,B2:D6,FALSE,TRUE)
  • Chọn cell A23 và phạm vi di động A23:B27, và sau đó nhập công thức sau đây như là một công thức mảng:
    =LINEST(A2:A6,B2:B6,TRUE,TRUE)
Kiểu đầu tiên, ở hàng 8-13, sử dụng cột b và c là dự đoán. Bởi bỏ qua đối số thứ ba, các mô hình đầu tiên yêu cầu Excel để mô hình đánh chặn. Excel sau đó có hiệu quả chèn một dự báo bổ sung cột đó trông giống như các tế bào D2:D6. Mục trong cột c trong hàng 2-6 chính xác bằng với tổng các mục tương ứng trong cột b và mất Vì vậy, collinearity là hiện nay bởi vì cột c là một khoản bội số của cột b và một cột bổ sung của 1s chèn vào bởi LINEST bởi vì thứ ba đối số LINEST được bỏ qua hoặc đặt thành TRUE (trường hợp "bình thường"). Collinearity vấn đề số nguyên nhân, Excel 2002 và phiên bản trước của Excel không thể tính toán kết quả, và các LINEST đầu ra bảng là đầy #NUM!.

Mô hình mô hình của thứ hai, các hàng 15 đến 20, sử dụng cột B, C và d dự đoán nhưng bộ đối số thứ ba của LINEST thành FALSE. Bởi vì đánh chặn được mô phỏng một cách rõ ràng qua cột D, bạn không muốn Excel để riêng rẽ mẫu đánh chặn bằng cách xây dựng một lần thứ hai cột của 1s. Một lần nữa, collinearity được trình bày bởi vì mục có trong cột c trong hàng 2-6 chính xác bằng với tổng các mục tương ứng trong cột b và mất phân tích sự hiện diện của collinearity không bị ảnh hưởng bởi thực tế rằng cột d được sử dụng một cách rõ ràng trong mô hình này và một cột tương tự như của 1s được tạo ra trong nội bộ của Excel trong mô hình đầu tiên. Trong trường hợp này, các giá trị được tính cho LINEST ra bàn, nhưng một số các giá trị không thích hợp.

Bất kỳ phiên bản nào của Excel có thể xử lý các mô hình thứ ba (trong hàng 22 27). Không có collinearity, và Excel mô hình đánh chặn, do đó tránh các mô hình với các đối số thứ ba đặt thành FALSE (có sử dụng các không đúng công thức để tính toán một số thống kê trong các phiên bản Excel sớm hơn Excel 2003). Ví dụ này được bao gồm trong bài viết này cho sau đây lý do:
  • Ví dụ này có lẽ nhất tiêu biểu của trường hợp thực tế: không có collinearity hiện diện và đối số thứ ba để LINEST là đúng một trong hai hoặc bỏ qua. Tất cả các phiên bản Excel có thể xử lý các trường hợp này. Nếu bạn sử dụng Excel 2002 hoặc một phiên bản cũ của Excel, số vấn đề là không có khả năng xảy ra trong những trường hợp này.
  • Ví dụ này được sử dụng để so sánh các hành vi của Excel 2003 và các phiên bản sau của Excel trong ba mô hình. Hầu hết người gói chính thống kê phân tích collinearity, loại bỏ một cột mà là một khoản bội số của những người khác từ các mô hình, và cảnh báo bạn với một tin nhắn như"cột c linearly phụ thuộc vào cột dự báo khác "và đã được gỡ bỏ từ các phân tích."
Trong Excel 2003 và trong phiên bản sau của Excel, tin nhắn được chuyển tải trong đầu ra LINEST Bảng thay vì trong một chuỗi văn bản. Một yếu tố hồi qui bằng 0 và có lỗi chuẩn cũng là zero tương ứng với một hệ số cho một cột mà đã được gỡ bỏ từ các mô hình. Các mục trong các tế bào K9:K10 hiển thị này. Trong trường hợp này, LINEST tháo bỏ cột C (hệ số trong các tế bào K9, L9, M9 tương ứng để cột C, B, và Excel đánh chặn cột, tương ứng). Khi collinearity hiện nay, bất kỳ một trong các cột có liên quan có thể gỡ bỏ.

Mô hình thứ hai ở hàng around 15 bộ đối số thứ ba của LINEST thành FALSE. Các mục trong các tế bào N16:N17 là cách tiêu chuẩn của Excel của truyền đạt thông tin này. Mục có trong các tế bào K16:K17 cho thấy rằng LINEST gỡ bỏ một cột (cột D) từ các mô hình. Hệ số trong cột l và m là cho dữ liệu cột c và B, tương ứng.

Trong ba mẫu, trong hàng 22 để 27, không có collinearity là hiện tại và không có cột được gỡ bỏ. Dự đoán y giá trị là giống nhau trong tất cả các mô hình ba vì một cách rõ ràng mô hình hóa một đánh chặn (như trong các mô hình thứ hai) cung cấp chính xác cùng một mô hình hóa khả năng như ngầm làm mẫu nó trong Excel trong nội bộ (giống như trong lần đầu tiên mô hình và các mô hình thứ ba). Ngoài ra, loại bỏ một cột dự phòng mà là một khoản bội số của những người khác (như ở kiểu đầu tiên và thứ hai mẫu) thì không giảm tốt đẹp của sự phù hợp của các mô hình kết quả. Các cột được gỡ bỏ chính xác vì họ đại diện không có giá trị gia tăng trong cố gắng để tìm tốt nhất tối thiểu phù hợp.

Ví dụ sau là một ví dụ cuối cùng của collinearity. Các dữ liệu trong ví dụ này cũng được sử dụng trong sự phân hủy QR Ví dụ trong bài viết này. Để minh họa cho các ví dụ cuối cùng của collinearity, thực hiện theo các bước sau:
  1. Tạo ra một bảng tính Excel trống, và sau đó sao chép dưới đây bảng.
  2. Trong Excel 2003 và trong phiên bản trước của Excel, bấm vào ô A1 trong của bạn bảng tính Excel trống, và sau đó trên các Chỉnh sửa trình đơn, nhấp vào Dán do đó các mục trong bảng điền vào các tế bào A1:D25 trong bảng tính của bạn.

    Trong Excel 2007, bấm vào ô A1 trong của bạn bảng tính Excel trống, và sau đó nhấp vào Dán trong các Bảng tạm nhóm vào các Trang chủ Tab để cho các mục trong bảng điền các tế bào A1:D25 trong bảng tính.
  3. Sau khi bạn dán bảng vào của bạn bảng tính Excel mới, Nhấp vào Tùy chọn dán, sau đó bấm Điểm đến trận đấu Định dạng.
  4. Trong Excel 2003 và trong phiên bản trước của Excel, trong khi phạm vi dán vẫn chọn, trên cácĐịnh dạng trình đơn, điểm đến Cột, sau đó bấmLựa chọn AutoFit.

    Trong Excel 2007, trong khi vẫn chọn phạm vi dán, nhấp vào Định dạng trong các Các tế bào nhóm vào các Trang chủ tab, và sau đó nhấp vào AutoFit Column Width.
  5. Chọn cell A7 và phạm vi di động A7:C11. Công thức chỉnh sửa thanh nên hiển thị các thông tin sau:
    =LINEST(A2:A5,C2:D5,,TRUE)
  6. Nhập thông tin từ thanh chỉnh sửa công thức như là một mảng công thức bằng cách nhấn CTRL + SHIFT + ENTER.

    Các tế bào A7:C11 hiển thị LINEST kết quả phù hợp với các giá trị trong các tế bào A13:C18 hoặc các tế bào A20:C25, tùy thuộc vào Phiên bản Excel mà bạn sử dụng.
YX 0X 1
10111
20420
30832
40729
=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)
=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)
=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)
=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)
=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)=LINEST(A2:A5,C2:D5,,TRUE)
Các giá trị Excel 2002:
-3.514.166666666666734.6666666666666
000
0.8066666666666679.83192080250175# N/A
2.086206896551721# N/A
403.33333333333396.6666666666666# N/A
Excel 2003 giá trị:
1.222222222222220-3.11111111111111
0.423098505881328010.3334826751454
0.8066666666666676.95221787153807# N/A
8.34482758620692# N/A
403.33333333333396.6666666666667# N/A
Mô hình này minh hoạ rằng sự hiện diện của collinearity có thể không dễ dàng để xác định. Kiểm tra các tế bào C2:D5 đòi hỏi rằng bạn đang nhận thức được rằng LINEST mô hình hóa đánh chặn bằng cách cung cấp được xây dựng trong cột của 1s. Nếu bạn gọi cột này, X 2, bạn có thể nhận thấy rằng X 1 có thể được biểu diễn như 3 * X 0 + 8 * X 2.

Tất cả các phiên bản Excel cung cấp các cùng một tốt đẹp của sự phù hợp được đo bằng các tế bào B18 và tế bào B25. Tuy nhiên, Excel 2002 cung cấp tất cả các chiếc Zero như các giá trị cho lỗi chuẩn hồi qui hệ số.

Mục cho df trong tế bào B17 và tế bào B24 khác nhau. Các f-số liệu thống kê trong tế bào A17 và tế bào A24 cũng khác nhau. Df cho Excel 2003 là đúng cho một mô hình với hai cột dự báo, chính xác những gì các mô hình sử dụng (Excel đánh chặn được xây dựng trong cột và X 1). Df cho Excel 2002 là thích hợp cho ba dự báo cột. Tuy nhiên, vì của collinearity, có những chỉ có hai dự báo cột. Có những chỉ có hai dự báo cột bởi vì sau khi bạn đã sử dụng bất kỳ hai trong ba cột, mở rộng các mô hình sử dụng thứ ba cột không có giá trị thêm. Vì vậy, vì của collinearity, mục nhập trong tế bào B17 không phải là chính xác, và các mục nhập trong tế bào B24 là chính xác. Các giá trị không chính xác của df ảnh hưởng đến số liệu thống kê mà phụ thuộc vào df: tỉ lệ f trong tế bào A17 và tế bào A24 và lỗi chuẩn y trong tế bào B16 và tế bào B23. Mục có trong tế bào A17 và tế bào B16 là không chính xác; các mục trong tế bào A24 và tế bào B23 là chính xác.

Ví dụ sau minh hoạ QR Thuật toán phân hủy. Đô thị này có hai lợi thế chính trên các thuật toán mà sử dụng các phương trình bình thường"." Trước tiên, kết quả là ổn định hơn numerically. Khi collinearity không phải là một vấn đề, kết quả là điển hình chính xác đến hơn thập phân những nơi có QR phân hủy. Thứ hai, QR phân hủy một cách thích hợp xử lý collinearity. Nó có thể được dùng như "chế biến" cột một lúc một thời gian, và nó không xử lý cột linearly phụ thuộc vào từng chế biến cột. Các thuật toán trước không đúng cách xử lý collinearity. Nếu collinearity là hiện nay, các kết quả từ các thuật toán trước đó là thường xuyên méo, đôi khi đến khi trở về #NUM!.
YX 0X 1
10111
20420
30832
40729
Đại tá có nghĩa là:
=AVERAGE(A2:A5) =AVERAGE(C2:C5)=AVERAGE(D2:D5)
Trung tâm dữ liệu với thêm col của 1, X 2:
YX 0X 1X 2
= A2-A$ 7= C2-C$ 7= D2-D$ 71
= A3-A$ 7= C3-C$ 7= D3-D$ 71
= A4-A$ 7= C4-C$ 7= D4-D$ 71
= A5-A$ 7= C5-C$ 7= D5-D$ 71
TotalSS:
=SUMSQ(A11:A14)
Đại tá x bình phương độ dài:
=SUMSQ(C11:C14)=SUMSQ(D11:D14)=SUMSQ(E11:E14)
sau khi trao đổi cols:
Y X 1X 0X 2
= A11= D11= C111
= A12= ĐƯỜNG D12= C121
= A13= D13= C131
= A14= D14.= EXR1
tính toán V:Vvà VTV:và v lần v transpose:
= C23=SQRT(D19)1= A29 + B$ 29 * C29=SUMSQ(E29:E32)=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))
= C240= A30 + B$ 29 * C30=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))
= C250= TRÚ + B$ 29 * C31=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))
= C260= A32 + B$ 29 * C32=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))=MMULT(E29:E32,TRANSPOSE(E29:E32))
tính toán P = I - (2/VTV) * VVtransposepremultiply X bởi P:và y bởi P:
X 1X 0X 2Y
=-(2 /$ G$ 29) * I29 + 1=-(2 /$ G$ 29) * J29=-(2 /$ G$ 29) * K29=-(2 /$ G$ 29) * L29=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,A23:A26)
=-(2 /$ G$ 29) * TUCSON=-(2 /$ G$ 29) * 19XA + 1=-(2 /$ G$ 29) * K30=-(2 /$ G$ 29) * L30=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,A23:A26)
=-(2 /$ G$ 29) * I31=-(2 /$ G$ 29) * J31=-(2 /$ G$ 29) * K31 + 1=-(2 /$ G$ 29) * L31=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,A23:A26)
=-(2 /$ G$ 29) * I32=-(2 /$ G$ 29) * J32=-(2 /$ G$ 29) * K32=-(2 /$ G$ 29) * L32 + 1=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,C23:E26)=MMULT(A35:D38,A23:A26)
Bình phương độ dài của X, Y cols là không thay đổi sau khi bạn premultiply bởi P:=SUMSQ(G35:G38)=SUMSQ(H35:H38)=SUMSQ(I35:I38)=SUMSQ(L35:L38)
Thuật toán tiếp tục với chỉ đậm phần của các sửa đổi x ma trận và y cột
độ dài bình phương của các hàng cuối 3 X vectơ:=SUMSQ(H36:H38)=SUMSQ(I36:I38)
sau khi trao đổi cols:
YX 1X 2X 0
= L35= G35= I35= H35
= L36= G36= I36= H36
= L37= G37= I37= H37
= L38= G38= I38= H38
tính toán V:Vvà VTV:và v lần v transpose:
= D47=SQRT(I42)1= A52 + B$ 52 * C52=SUMSQ(E52:E54)=MMULT(E52:E54,TRANSPOSE(E52:E54))=MMULT(E52:E54,TRANSPOSE(E52:E54))=MMULT(E52:E54,TRANSPOSE(E52:E54))
= D480= A53 + B$ 52 * C53=MMULT(E52:E54,TRANSPOSE(E52:E54))=MMULT(E52:E54,TRANSPOSE(E52:E54))=MMULT(E52:E54,TRANSPOSE(E52:E54))
= D490= A54 + B$ 52 * C54=MMULT(E52:E54,TRANSPOSE(E52:E54))=MMULT(E52:E54,TRANSPOSE(E52:E54))=MMULT(E52:E54,TRANSPOSE(E52:E54))
tính toán P = I - (2/VTV) * VVtransposepremultiply X bởi P:và y bởi P:
X 1X 2X 0Y
=-(2 /$ G$ 52) * I52 + 1=-(2 /$ G$ 52) * J52=-(2 /$ G$ 52) * K52= C46= D46= E46= L35
=-(2 /$ G$ 52) * I53=-(2 /$ G$ 52) * J53 + 1=-(2 /$ G$ 52) * K53= G36=MMULT(A57:C59,D47:E49)=MMULT(A57:C59,D47:E49)=MMULT(A57:C59,A47:A49)
=-(2 /$ G$ 52) * I54=-(2 /$ G$ 52) * J54=-(2 /$ G$ 52) * K54 + 1= G37=MMULT(A57:C59,D47:E49)=MMULT(A57:C59,D47:E49)=MMULT(A57:C59,A47:A49)
= G38=MMULT(A57:C59,D47:E49)=MMULT(A57:C59,D47:E49)=MMULT(A57:C59,A47:A49)
Viết lại: hiệu quả 0--> 0:X 1X 2X 0Y
= G350= I57= L35
0= H5800
000= L59
000= L60
Vòng lặp chính phân hủy QR chấm dứt vì lâu nhất còn lại sub-vector có chiều dài 0
hồi qui coeffts bởi backsubstitution:=(L64- H64 * H71) / G64= L65/H650
dư SS từ cuối 2 hàng của Y:=SUMSQ(L66:L67)
Excel 2003 LINEST:
SSRegression = SSTotal - SSResidual:= A17-G721.222222222222220-3.11111111111111
Bình phương R = SSRegression / SSTotal= E74/A170.423098505881328010.3334826751454
DF = 2 (xem bài viết)0.8066666666666676.95221787153807# N/A
stdErrorY = sqrt(SSResidual/DF)=SQRT(G72/2)8.34482758620692# N/A
FStatistic = (SSRegression / (DF Regression)) / (SSResidual/DF):=(E74/(0+1))/(G72/2)403.33333333333396.6666666666667# N/A
Đánh chặn:= A7 - I71 * C7 - G71 * D7
Bảng tính này sử dụng dữ liệu tương tự như trước đó bảng tính. QR phân hủy thực hiện một chuỗi trực giao tuyến tính biến đổi. Dữ liệu gốc là trong các tế bào A1:D5. Bước đầu tiên là "giữa" dữ liệu cho các cột gốc, và sau đó một cách rõ ràng thêm một cột của 1s (nó giả định rằng đối số thứ ba để LINEST là đúng hoặc bỏ qua). Các Sửa đổi dữ liệu được hiển thị trong các tế bào A10:D14. Cột X 2 là cột 1s, được thêm vào. Để Trung tâm dữ liệu, tìm thấy bình trong mỗi cột (thể hiện trong các tế bào A7:D7) và sau đó trừ từ mỗi quan sát trong cột tương ứng. Vì vậy, cho các Y, X 0, và X 1 cột, dữ liệu gốc đã được thay thế bởi độ lệch về các cột có nghĩa là. Tập trung rất hữu ích trong việc giảm thiểu vòng ra lỗi. Giá trị trong tế bào A17 là tổng bình phương của các giá trị y trung tâm, số tiền Vuông cho phân tích hồi qui. Giá trị trong các tế bào C19:E19 là một khoản tiền ô vuông của các trung tâm X 0 và X 1 cột và cột (không trung tâm) của 1s đó tên X 2. Bạn có thể trao đổi cột X 0, X 1 và X 2 vì vậy mà một trong những với tiền lớn nhất của hình vuông đến trước. Các kết quả này đang ở trong các tế bào A21:D26. Khi bạn trao đổi cột, bạn phải theo dõi các vị trí của mỗi cột gốc.

Sau những thay đổi sơ bộ, bạn có thể sử dụng các vòng lặp chính của thuật toán phân hủy QR. Bạn muốn tìm một ma trận 4 x 4 (vì có 4 của các hàng dữ liệu) mà bạn có thể sử dụng để premultiply mỗi cột. Chuyển đổi này không thay đổi độ dài bình phương của mỗi cột. Bạn đầu tiên tìm thấy cột vectơ v bằng việc cột đầu tiên và thêm quảng trường thư mục gốc của các cột tổng của hình vuông (tính trong tế bào B29) cho các mục nhập đầu tiên. Other mục có trong cột đầu tiên không được thay đổi. Hành động này sản lượng các vector trong các tế bào E29:E32. Tổng của hình vuông trong V (như VTV) là trong tế bào G29. (Chú ý T phải là một viết bên trên.) Ma trận 4 x 4 VVT là trong các tế bào I29:L32. Sử dụng thông tin này để tính ma trận biến đổi 4 x 4, P, bởi bằng cách sử dụng công thức sau đây.
P = I – (2/ VTV)* VVT
Chú ý Tất cả các Ts phải là viết bên trên.

Ma trận kết quả p là hiển thị trong các tế bào A35:D38. Nếu bạn premultiply các sửa đổi x cột trong các tế bào C23:E26 p, bạn nhận được các kết quả trong các tế bào G35:I38. Tương tự, nếu bạn premultiply sửa đổi y cột trong các tế bào A23:A26 p, bạn nhận được các kết quả trong các tế bào L35:L38. X 1 cột đã được chuyển đổi để cho nó vẫn có tổng cùng một hình vuông như trước, nhưng tất cả các mục trừ các mục nhập đầu trang trong cột là 0. Chính xác hơn, mục trong các tế bào G36:G38 là "hiệu quả 0" bởi vì họ là số không đến mười lăm những nơi thập phân. Trong hàng 40, vuông cho một khoản tiền tất cả các cột được tính và không được thay đổi bởi các chuyển đổi.

Các thuật toán vẫn tiếp tục cho một lặp thứ hai của các vòng lặp chính và sử dụng chỉ các X 0 và X 2 dữ liệu trong các tế bào H36:I38 và các dữ liệu y trong các tế bào L36:38. Bởi vì bạn có liên quan với chỉ ba hàng, bạn có thể tính toán khoản vuông cho chỉ cuối ba hàng cột X 0 và X 2. Các giá trị này được hiển thị trong các tế bào H42:H43. Tổng bình phương của X 0 là về cơ bản 0. X 0 và X 2 cột được đổi chỗ vì X 2 đã lớn hơn có liên quan tổng của hình vuông. Sau khi các cột được đổi chỗ, sửa đổi cột hiển thị trong các tế bào A45:E49. V tính toán chính xác như trong sự lặp đầu tiên ngoại trừ việc bây giờ v đã chỉ ba hàng. Tính toán VTV, VVT và p tiếp tục chính xác như trước và được thể hiện trong hàng 51-54 và các tế bào A57:C59. Bạn có thể sau đó premultiply chỉ cuối ba hàng cột X 2, X 0 và y bởi p để sản lượng các cột sửa đổi trong các tế bào G56:L60. Thực hiện điều này dễ đọc hơn, các cột được viết lại trong các tế bào G63:L67 bằng cách thiết lập giá trị là rất có hiệu quả không để chính xác zero.

Lặp tiếp theo chỉ liên quan đến X 0 cột và của nó cuối hai hàng. Bởi vì tổng squares của mục nhập trong các hàng là zero, các vòng lặp chính của thuật toán chấm dứt.

Tổng dư của hình vuông là tổng bình phương của sửa đổi y vector mục dưới đây hàng thứ hai. Tất cả các hàng không được xử lý lúc thời gian kết thúc vòng lặp chính của thuật toán phân hủy QR bao gồm ở đây. Trong trường hợp này, chế biến bị dừng vì cuối cùng hai hàng năm X 0 cột chứa chỉ zeros. Tổng dư của hình vuông được tính trong tế bào G74. Bạn có thể nhìn thấy từ các mục trong các tế bào G63:L67 những gì bất kỳ giá trị cho hệ số các Xs để lại một giá trị được trang bị của zero cho mỗi người trong số cuối cùng hai hàng. Các giá trị của hệ số cho X 1 và 2 X đã được tìm thấy năng suất một chính xác phù hợp với giá trị y ở các hàng đầu tiên hai. Vì vậy, Y đã chuyển do đó của nó số tiền của hình vuông là không thay đổi, Tổng dư hình vuông là tổng của hình vuông ở cuối hai hàng, và hồi quy tổng hình vuông là tổng của hình vuông ở các hàng đầu tiên hai.

Các thuật toán phát hiện collinearity khi nó nhận thấy rằng các mục còn lại trong X 0 cột sống zero. Tại thời điểm này, không có cột vẫn có hệ số có thể cải thiện sự phù hợp. X 0 cột không chứa bất kỳ hữu ích bổ sung thông tin bởi vì X 1 đến X 2 đã được bao gồm trong mô hình. Mặc dù X 2 có một yếu tố của số không, điều này không làm cho nó một cột dự phòng là loại bỏ như là kết quả của collinearity.

Tại thời điểm này, bạn có thể giải nén hầu hết các thống kê sơ lược về LINEST cung cấp. Tuy nhiên, bài viết này không không thảo luận về làm thế nào để xác định lỗi chuẩn của hệ số hồi qui. Giá trị từ LINEST ra trong Excel 2003 được hiển thị trong các tế bào I74:K78 cho so sánh. Hồi quy tổng của hình vuông được tính trong tế bào E74 và R bình phương được tính trong tế bào E75; các giá trị này được hiển thị trong LINEST đầu ra trong tế bào I78 và tế bào I76, tương ứng. Tổng dư của hình vuông (hoặc lỗi tổng của hình vuông) tính toán trong tế bào G72 và hiển thị trong LINEST đầu ra trong tế bào J78.

Other mục có trong LINEST đầu ra phụ thuộc vào các độ tự do (DF). Nhiều thống kê gói báo cáo Regression DF, lỗi DF, và tổng số DF. Excel báo cáo chỉ lỗi DF (trong tế bào J77). Phiên bản trước của Excel tính lỗi DF đúng trong mọi trường hợp ngoại trừ khi coù collinearity nên đã loại một hoặc nhiều dự báo cột. Các giá trị của lỗi DF phụ thuộc vào số lượng các cột dự báo thực sự được sử dụng. Với collinearity, Excel 2003 xử lý tính toán này một cách chính xác, trong khi trước đó phiên bản đếm tất cả các dự báo cột mặc dù một hoặc nhiều nên đã bị loại bỏ bởi collinearity.

Độ tự do được kiểm tra ở đây chi tiết hơn. Giả sử rằng collinearity không phải là một vấn đề. Khi các đánh chặn được trang bị, nói cách khác, đối số thứ ba để LINEST bị thiếu hoặc sự thật:
  • Tổng DF bằng với số lượng hàng (hoặc datapoints) trừ một trong những.
  • Hồi qui DF bằng với số lượng các dự báo cột (không bao gồm cột cho đánh chặn).
  • Lỗi DF bằng tổng DF trừ Regression DF.
Khi đánh chặn không gắn, nói cách khác, thứ ba đối số để LINEST là sai:
  • Tổng DF bằng với số lượng hàng (hoặc datapoints).
  • Hồi qui DF bằng với số lượng dự báo cột.
  • Lỗi DF bằng tổng DF trừ Regression DF.
Sự khác biệt duy nhất giữa hai trường hợp là một"trừ" trong công thức cho tổng DF trong trường hợp phổ biến mà là đánh chặn Trang bị.

Phiên bản trước của Excel sử dụng các công thức để chính xác tính toán DF, ngoại trừ việc Excel 2002 không xem xét cho collinearity. Tìm kiếm collinearity là một trong những lý do cho việc sử dụng phân hủy QR cho những tính toán.

Dự báo cột tạo thành một ma trận. Nếu đánh chặn Trang bị, đó là có hiệu quả một cột bổ sung của 1s không xuất hiện trên bảng tính của bạn. QR phân hủy sẽ xác định thứ hạng của ma trận này. Các trước đây công thức cho Regression DF nên được thay đổi như sau công thức:
  • Đối với trường hợp "trang bị": Regression DF bằng cấp bậc của các ma trận các dự báo cột (bao gồm một cột 1s cho đánh chặn) trừ một (đối với cột cho đánh chặn)
  • Đối với trường hợp "không được trang bị": Regression DF bằng cấp bậc ma trận các dự báo cột
Ngoài ra, vì Excel sử dụng số học hữu hạn, "đánh giá" là thực sự "gần đúng đánh giá", do đó, một cột là linearly phụ thuộc vào một tập hợp các cột khác Nếu có một tổng trọng số của cột trong tập là một véc tơ, mà Euclide khoảng cách từ cột là rất gần đến số không.

Trong ví dụ trên các bảng tính, việc đánh chặn được trang bị. Tổng DF là 4-1 = 3; Hồi qui DF là 2 -1 = 1; Lỗi DF là tổng DF-Regression DF = 3-1 = 2. Ví dụ này, Excel 2002 và phiên bản trước của Excel tính hồi qui DF như 3-1 = 2 và lỗi DF 3 -2 = 1. Sự khác biệt đến từ sự thất bại để tìm collinearity. Phiên bản trước của Excel ghi nhận rằng đã có ba dự báo cột; Excel 2003 kiểm tra những ba cột và thấy rằng có thực sự chỉ hai.

Tiêu chuẩn lỗi của y được tính trong tế bào E77 và được hiển thị trong các LINEST đầu ra trong tế bào J76. Thống kê f được tính trong tế bào H78 và trong các LINEST đầu ra trong tế bào I77. Công thức cho số liệu thống kê f là:
(SSRegression / DF Regression) / (SSError / DF Error)
Trong ví dụ này, số liệu thống kê f là:
(403.333 / 1) / (96.667 / 2) = 8.345
Đầu ra LINEST trong các tế bào I74:K74 cho thấy hệ số hồi quy cho X 1, X 0 và đánh chặn được trang bị. Hệ số cho đánh chặn,-3.1111, khác với hệ số cho cột X 2. Sự khác biệt này xảy ra bởi vì các dữ liệu đã được trung tâm để tìm mô hình phù hợp với hồi quy tuyến tính tốt nhất cho việc này dữ liệu. Các giá trị hệ số tối ưu hồi quy cho X 1 đến X 0 không bị ảnh hưởng bởi tập trung các dữ liệu này. Tập trung các dữ liệu gây ra phù hợp nhất để đi qua các nguồn gốc. Tập trung các dữ liệu này là lý do mà một hệ số tối ưu của zero cho X 2 (cột mà đã được bổ sung để đại diện cho đánh chặn) đã được tìm thấy. May mắn thay, bạn có thể phục hồi hệ số đánh chặn tương ứng cho các mô hình ban đầu với những nỗ lực bổ sung ít. Hệ số đánh chặn có thể tìm thấy bằng cách sử dụng công thức sau đây:
Có nghĩa là đại tá y trừ đi tổng trên tất cả x cột (ngoại trừ các cột đánh chặn) của x col hồi qui hệ số lần x col có nghĩa là
Giá trị này được tính trong C80 và đồng ý với sản lượng LINEST trong tế bào K74.

Bản tóm tắt kết quả trong phiên bản trước của Excel

LINEST sử dụng một công thức đó là không đúng để tìm số tiền hình vuông khi đối số thứ ba để LINEST được đặt thành FALSE. Công thức này gây ra các giá trị mà không phải là chính xác trong hồi quy tổng của hình vuông và các giá trị mà là không chính xác cho sản lượng phụ thuộc vào tổng hồi qui hình vuông: r bình phương và thống kê f.

Bất kể về giá trị của các đối số thứ ba, LINEST đã được tính toán bằng cách sử dụng một cách tiếp cận mà không trả tiền quan tâm đến các vấn đề collinearity. Sự hiện diện của collinearity gây ra vòng tắt lỗi, lỗi chuẩn của hệ số hồi qui mà không phải là thích hợp, và độ của tự do mà không phải là thích hợp. Đôi khi, vòng tắt vấn đề đã đủ nghiêm trọng LINEST đầy bảng đầu ra của nó với #NUM!. LINEST nói chung cung cấp chấp nhận được kết quả nếu các điều kiện sau là đúng:
  • Không có không có dự báo chốt (hoặc gần như chốt) cột.
  • Đối số thứ ba để LINEST là đúng hay là bỏ qua.
Tuy nhiên, giải quyết các hồi quy hệ số bằng cách sử dụng các "bình thường Phương trình"là thêm dễ bị vòng tắt lỗi hơn bằng cách sử dụng phân hủy QR cách tiếp cận được sử dụng trong Excel 2003 và các phiên bản sau của Excel. Mặc dù những hệ nhiều hơn nữa nghiêng để vòng tắt lỗi, họ không phải là khả năng là có vấn đề đối với hầu hết trường hợp thực tế.

Bản tóm tắt kết quả trong Excel 2003

Những cải tiến bao gồm điều chỉnh công thức cho số tiền hình vuông trong trường hợp đó là đối số thứ ba để LINEST được đặt thành FALSE và chuyển đổi sang phương pháp phân hủy QR xác định hồi qui hệ số. QR phân hủy có hai ưu điểm sau:
  • Sự ổn định số tốt hơn (thường nhỏ hơn vòng tắt lỗi)
  • Phân tích về các vấn đề collinearity
Collinearity là mối quan tâm chính, đặc biệt là sau khi các xét nghiệm đã biểu diễn trên NIST datasets.

Kết luận

LINEST đã được cải rất nhiều thiện cho Excel 2003 và các phiên bản sau của Excel. Nếu bạn sử dụng một Phiên bản trước của Excel, xác minh rằng dự báo cột này không chốt trước khi bạn sử dụng LINEST. Hãy cẩn thận để sử dụng các workaround trong bài viết này nếu các đối số thứ ba trong LINEST được đặt thành FALSE. Lưu ý rằng collinearity chỉ một vấn đề ở một tỷ lệ nhỏ của các trường hợp, và các cuộc gọi đến LINEST với thứ ba thiết lập đối số để sai cũng tương đối hiếm trong thực tế. Phiên bản trước của Excel cung cấp cho chấp nhận được LINEST kết quả khi không có không có collinearity và khi các đối số thứ ba trong LINEST là đúng hay bỏ qua. Những cải tiến trong LINEST ảnh hưởng đến các Phân tích ToolPak hồi qui tuyến tính công cụ mà các cuộc gọi LINEST và sau đây chức năng liên quan:
  • XU HƯỚNG
  • LOGEST
  • TỐC ĐỘ TĂNG TRƯỞNG
THAM KHẢO
McCullough, BD và B. Wilson. "Về tính chính xác của Thủ tục thống kê trong Microsoft Excel 97." Tính toán thống kê và Phân tích dữ liệu, 1999, 31, 27-37.

Cảnh báo: Bài viết này được dịch tự động

Thuộc tính

ID Bài viết: 828533 - Xem lại Lần cuối: 09/20/2011 13:28:00 - Bản sửa đổi: 3.0

Microsoft Office Excel 2007, Microsoft Excel 2004 for Mac, Microsoft Office Excel 2003, Microsoft Excel 2010

  • kbexpertisebeginner kbfunctions kbfuncstat kbinfo kbmt KB828533 KbMtvi
Phản hồi