Mục lục
Tải xuống Sổ làm việc Ví dụTải xuống sổ làm việc mẫu
Hướng dẫn này trình bày cách sử dụng Hàm LINEST trong Excel trong Excel để tính toán thống kê về đường xu hướng.
Tổng quan về hàm LINEST
Hàm LINEST Tính toán thống kê về một đường xu hướng phù hợp với các điểm dữ liệu đã biết bằng phương pháp bình phương nhỏ nhất.
Để sử dụng hàm LINEST Excel Worksheet, hãy chọn một ô và nhập:
[Chú ý cách các đầu vào công thức xuất hiện]
Hàm LINEST Cú pháp và đầu vào
| 1 | = LINEST [đã biết, đã biết_xs, const, thống kê] |
đã biết - Một mảng các giá trị Y đã biết.
known_x's - Một mảng các giá trị X đã biết.
hăng sô - KHÔNG BẮT BUỘC. Giá trị logic cho biết có nên tính B [giao điểm trong y = mx + b] bằng phương pháp bình phương nhỏ nhất [TRUE hoặc Bỏ sót] hay để đặt B = 0 [FALSE] theo cách thủ công hay không.
số liệu thống kê - KHÔNG BẮT BUỘC. Trả về thống kê bổ sung [TRUE] hoặc chỉ trả về m [độ dốc] và b [điểm chặn] [FALSE hoặc Bỏ qua]
LINEST là gì?
Hàm LINEST trong Excel là một hàm được sử dụng để tạo thống kê hồi quy cho mô hình hồi quy tuyến tính. LINEST là một công thức mảng và có thể được sử dụng một mình hoặc với các hàm khác để tính toán các thống kê cụ thể về mô hình.
Hồi quy tuyến tính là một phương pháp trong thống kê được sử dụng để dự đoán dữ liệu theo một đường thẳng bằng cách sử dụng dữ liệu đã biết. Hồi quy được sử dụng để dự đoán các giá trị như tăng trưởng doanh số, yêu cầu hàng tồn kho hoặc xu hướng thị trường đơn giản.
LINEST giống FORECAST ở chỗ nó đạt được một kết quả tương tự, nhưng với nhiều thông tin hơn về mô hình hồi quy của bạn cũng như tùy chọn để phù hợp với nhiều hơn một biến độc lập.
Giả sử tôi có một bảng dữ liệu với NS và y giá trị ở đâu NS là biến độc lập và y là biến phụ thuộc:
Tôi muốn biết phương trình hồi quy của dữ liệu trên là gì. Sử dụng LINEST:
| 1 | = LINEST [B3: B7, C3: C7, TRUE, FALSE] |
Giá trị chặn y ở đây tương đương với 0, trong ký hiệu khoa học.
Phương trình của đường thẳng là y = 2x + 0. Lưu ý rằng LINEST trả về cả hai hệ số góc và hệ số giao nhau của đường thẳng. Để trả về cả hai giá trị, công thức phải được nhập dưới dạng công thức mảng. Tìm hiểu thêm về công thức mảng sau.
Cách sử dụng LINEST
Hàm LINEST có bốn đối số:
| 1 | = LINEST [known_y’s, known_x’s, const, stats] |
Ở đâu,
| Tranh luận | Sự miêu tả |
| đã biết và known_x's | Là NS và y dữ liệu trong bảng dữ liệu của bạn |
| hăng sô | Tùy chọn TRUE / FALSE cho việc liệu chặn y nên được buộc về 0 hay được tính toán bình thường |
| số liệu thống kê | Tùy chọn TRUE / FALSE có được trả về thống kê hồi quy bổ sung hay không |
Sử dụng ví dụ đầu tiên của chúng tôi, hàm được viết là:
| 1 | = LINEST [B3: B7, C3: C7, TRUE, FALSE] |
Khi mà số liệu thống kê được đặt thành TRUE, tổ chức của thống kê hồi quy như sau:
Bạn có thể tự hỏi mỗi biến có nghĩa là gì.
| Thống kê | Sự miêu tả |
| NSn | Hệ số độ dốc cho biến x |
| NS | chặn y |
| sen | Sai số tiêu chuẩn cho mỗi hệ số độ dốc |
| seNS | Lỗi tiêu chuẩn đối với điểm chặn y |
| NS2 | Hệ số xác định |
| sey | Lỗi tiêu chuẩn cho y ước tính |
| NS | Thống kê F [để xác định xem mối quan hệ của các biến có xảy ra tình cờ hay không] |
| NSNS | Mức độ tự do |
| NSreg | Hồi quy tổng bình phương |
| NScư xá | Tổng dư của bình phương |
Các thống kê chính cần hiểu là hệ số góc, hệ số chặn y và hệ số xác định hoặc r2 giá trị của mô hình.
Sử dụng ví dụ trên và chọn ĐÚNG cho số liệu thống kê tham số:
Các ô được đánh dấu hiển thị độ dốc = 2, điểm chặn = 0 và r2 = 1.
Cái r2 giá trị là một chỉ báo về sức mạnh của mối tương quan của mô hình. Nó có thể được coi là một chỉ số của sự phù hợp. Một r thấp2 giá trị sẽ có nghĩa là mối tương quan kém giữa các biến phụ thuộc và độc lập của bạn, và điều ngược lại đúng với r cao2 giá trị, với r2 = 1 là một sự phù hợp hoàn hảo.
Trong các bản phát hành sau tháng 1 năm 2022 của Excel trong Microsoft 365 [trước đây là Office 365], mảng động đã thay đổi cách đánh giá công thức mảng. Không còn bắt buộc phải sử dụng CTRL + SHIFT + ENTER hoặc đánh dấu vùng ô mà mảng sẽ chiếm. Chỉ cần nhập công thức và nhấp enter và các ô kết quả sẽ "tràn ra" vào mảng.
Trong phần còn lại của bài viết này, chúng tôi sẽ tham khảo bằng cách sử dụng LINEST liên quan đến mảng động trong Microsoft 365 Excel.
Dự báo với LINEST [Hồi quy đơn giản]
Kết hợp các hàm LINEST và SUM có thể được sử dụng để dự đoán giá trị của một biến phụ thuộc y, đã biết NS và y dữ liệu. Dưới đây là một ví dụ cho thấy những gì y giá trị sẽ là khi x = 14.
| 1 | = SUM [LINEST [C3: C7, B3: B7] * {14,1}] |
Mô hình có dạng y = mx + b. Điều này giống với y = a + bx, chỉ là một cách khác để biểu diễn phương trình. Một mẹo cần ghi nhớ đối với phương trình tuyến tính là biến bên cạnh NS luôn luôn là hệ số góc và biến theo sau dấu cộng hoặc trừ luôn là giá trị chặn, bất kể các chữ cái được sử dụng trong phương trình.
Sử dụng công thức: = SUM [LINEST [C3: C7, B3: B7] * {14,1}] trả về kết quả là 28. Vì đây là một kết quả duy nhất nên không cần nhập dưới dạng mảng.
Phần cuối của công thức trên * {14,1} chỉ định biến độc lập được sử dụng để dự đoán biến phụ thuộc, trong trường hợp này là 14.
Chúng ta có thể kiểm tra điều này bằng cách nhập x = 14 vào phương trình của đường thẳng, y = 2x + 0.
Dự báo với LINEST [Hồi quy Nhiều tuyến tính]
Bảng dữ liệu sau đến từ trang LINEST của trang web Hỗ trợ của Microsoft.
Trong một số trường hợp, có nhiều hơn một biến độc lập cần được xem xét khi tạo mô hình hồi quy tuyến tính. Đây được gọi là hồi quy nhiều tuyến tính [tức là nhiều biến độc lập]. Nếu tôi muốn ước tính chi phí của một tòa nhà văn phòng, những thứ như diện tích sàn, số lượng lối vào tòa nhà, tuổi của tòa nhà và số lượng văn phòng sẽ là một phần của phương trình. Hãy xem một ví dụ.
Gõ công thức LINEST vào ô G29 và thực thi nó, chúng tôi nhận được:
| 1 | = LINEST [E3: E13, A3: D13, TRUE, TRUE] |
Mô hình có dạng:
Hãy nhớ rằng mảng kết quả LINEST có thứ tự ngược lại với phương trình. Trong ví dụ trên, 52.317,8 là điểm chặn của chúng ta, b và 27,6 là m1 hoặc giá trị độ dốc cho biến Không gian tầng, NS1.
Sử dụng hàm LINEST với dữ liệu được cung cấp, mô hình hồi quy của chúng tôi là:
Với một r2 giá trị 0,997, cho biết một mô hình mạnh hoặc có tương quan cao. Sử dụng mô hình này, giờ đây bạn có thể dự đoán giá trị được đánh giá của một tòa nhà văn phòng sẽ dựa trên bất kỳ sự kết hợp nào của các biến độc lập ở trên.
Mẹo gần đây nhất
- Đảm bảo bạn có phiên bản Microsoft 365 cập nhật nhất để sử dụng LINEST với mảng động. Bạn có thể cần bật Kênh Hiện tại của Người dùng nội bộ Office [Xem trước] để sử dụng các chức năng mảng động. Trên trang tài khoản:
- Nếu bạn đang sử dụng bản phát hành không phải Microsoft 365, bạn sẽ phải sử dụng phương pháp CTRL + SHIFT + ENTER [CSE] kế thừa để đánh giá công thức mảng.
- Nếu sử dụng phương thức kế thừa, số cột cần đánh dấu khi nhập hàm mảng LINEST luôn là số NS các biến trong dữ liệu của bạn cộng với 1. Số hàng để chọn cho mảng là 5.
- Nếu bạn sẽ chia sẻ phiên bản Excel hỗ trợ mảng động của mình với người đang sử dụng bản phát hành không phải của Microsoft 365, hãy sử dụng phương pháp CSE kế thừa để tránh các vấn đề về tương thích.
Quan tâm đến Dự báo khác?
Xem các bài viết khác của chúng tôi về Dự báo với các hàm Exponential Smoothing, TREND, GROWTH và LOGEST.
CUỐI CÙNG chức năng trong Google Trang tính
Hàm LINEST hoạt động hoàn toàn giống trong Google Trang tính cũng như trong Excel.
Ví dụ LINEST trong VBA
Bạn cũng có thể sử dụng hàm LINEST trong VBA. Kiểu:
application.worksheet functions.linest [known_ys, known_xs, const, stats]
Thực thi câu lệnh VBA sau
| 1 | Phạm vi ["D2"] = Application.WorksheetFunction.LinEst [Phạm vi ["A2: A8"], Phạm vi ["B2: B8"]] |
sẽ tạo ra kết quả sau
Đối với các đối số của hàm [known_y’s, v.v.], bạn có thể nhập chúng trực tiếp vào hàm hoặc xác định các biến để sử dụng thay thế.
Quay lại danh sách tất cả các hàm trong Excel