Bài viết này mô tả cú pháp công thức và cách dùng hàm LINEST trong Microsoft Excel.
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 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 mô 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ê cho các kiểu mô 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.
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, khi 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 ý 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, [known_x's], [const], [stats])
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 hệ y = mx + b.
-
Nếu phạm vi của known_y's nằm trong một cột đơn lẻ, thì mỗi cột của known_x's được hiểu 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 đơn lẻ, thì mỗi hàng của known_x's được hiểu là một biến số riêng rẽ.
-
-
known_x's Tùy chọn. Tập giá trị x mà bạn có thể đã biết trong quan 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 hay 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 hay không.
-
Nếu stats là TRUE, thì hàm LINEST trả về thống kê hồi quy bổ 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 trong phạm vi giá trị từ 0 tới 1. Nếu nó là 1, thì có một tương quan hoàn hảo trong mẫu — không 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 không còn hữu ích trong việc dự đoán giá trị y. Để biết thông tin về cáchtính toán 2 , hãy xem mục "Ghi chú" ở phần sau của chủ đề này. |
|
sey |
Lỗi chuẩn cho ước tính y. |
|
F |
Thống kê F, hoặc giá trị F quan sát được. Dùng thống kê F để xác định xem quan hệ quan 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ự do để giúp bạn tìm giá trị F tới hạn trong 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 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 xem mục "Ghi chú" ở phần sau bài viết này. |
Minh họa sau đây cho 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ể mô 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 thẳng, thường được viết là m, lấy hai điểm trên đường thẳng, (x1,y1) và (x2,y2); độ dốc bằng (y2 - y1)/(x2 - x1).
Giao cắt Y (b): Giao cắt y của một đường thẳng, thường được viết là b, là giá trị của y tại điểm mà đường thẳng cắt 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)
Giao cắt Y: =INDEX(LINEST(known_y's,known_x's),2)
-
Độ chính xác của đường thẳng do 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 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) và 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, bạn phải quyết định kết quả nào trong 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) 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ị cho cả hai để 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 cho đ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. Khi đố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. Khi đố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 với tổng cộng bình phương, thì giá trị của hệ số xác định, r2 càng lớn, vốn là chỉ báo cho biết phương trình thu được từ phân tích hồi quy giải thích mối quan hệ giữa các biến số như thế nào. Giá trị của r2 bằng ssreg/sstotal.
-
Trong một số trường hợp, một hoặc nhiều cột X (giả sử rằng Y’s và X’s nằm trong các cột) có thể không có giá trị dự đoán bổ sung nào khi có mặt các cột X khác. Nói cách khác, loại bỏ một hoặc nhiều cột X có thể dẫn tới các giá trị dự đoán Y chính xác bằng nhau. Trong trường hợp đó, các cột X dư thừa nên được bỏ qua trong mô hình hồi quy. Hiện tượng này được gọi là "tính cộng tuyến" vì bất kỳ cột X dư thừa nào cũng có thể được diễn giải là tổng của nhiều cột X không dư thừa. Hàm LINEST kiểm tra tính cộng tuyến và loại bỏ mọi cột X dư thừa ra khỏi mô hình hồi quy khi phát hiện thấy chúng. Các cột X được loại bỏ có thể được nhận biết trong kết quả của hàm LINEST là chúng có hệ số 0 ngoài các giá trị se 0. Nếu một hoặc nhiều cột bị loại bỏ vì dư thừa, df bị ảnh hưởng vì df phụ thuộc vào số cột X được thực sự dùng cho mục đích dự đoán. Để biết chi tiết cách tính toán df, hãy xem Ví dụ 4. Nếu df bị thay đổi do các cột X dư thừa bị loại bỏ, các giá trị của sey và F cũng bị ảnh hưởng. Tính cộng tuyến tương đối hiếm gặp trong thực tế. Tuy nhiên, một trường hợp mà tính cộng tuyến có nhiều khả năng xuất hiện là khi một vài cột X chỉ chứa các giá trị 0 và 1 như là chỉ báo về việc liệu một đối tượng trong một thực nghiệp có phải là hoặc không phải là một phần tử của một nhóm cụ thể. Nếu const = TRUE hoặc được bỏ qua, thì hàm LINEST chèn một cột X bổ sung với tất cả các giá trị 1 để mô tả giao cắt. Nếu bạn có một cột trong đó mỗi đối tượng nam được biểu thị là 1, nữ được biểu thị là 0, và bạn cũng muốn một cột trong đó mỗi đối tượng nữ được biểu thị là 1, nam biểu thị là 0, thì cột thứ hai là thừa vì có thể có được các mục nhập của nó bằng cách trích nhập mục từ cột "chỉ báo nam" từ nhập mục trong cột bổ sung với tất cả các giá trị 1 mà hàm LINEST thêm vào.
-
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 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ả hai trường hợp, cột X đã được loại bỏ do 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 ý rằng các giá trị y mà phươ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à 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 trong 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 khi 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à 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, 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 cho hàm LINEST. Ví dụ, công thức sau đây:
=LINEST(yvalues, xvalues^COLUMN($A:$C))
hoạt động khi bạn có một cột đơn các giá trị y và một cột đơn 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, nhưng 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.
-
Giá trị F-test mà hàm LINEST trả về khác với giá trị F-test mà hàm FTEST trả về. Hàm LINEST trả về thống kê F, còn hàm FTEST trả về xác suất.
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 sau đây và dán vào ô A1 của một trang tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả 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 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à dán vào ô A1 của một trang tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng 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 ước tính doanh số bán hàng trong 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 dữ liệu của ví dụ trong bảng sau đây và dán vào ô A1 của một trang tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả 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 động được nhập vào Ô 19) |
||||
|
=LINEST(E2:E12,A2:D12,TRUE,TRUE) |
Ví dụ 4 - Sử dụng Thống kê F và r2
Trong ví dụ trên đây, hệ số xác định, hay r2, là 0,99675 (xem ô A17 trong kết quả của hàm LINEST), cho biết mối quan hệ mạnh mẽ giữa các biến số độc lập và giá 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ế không có quan 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 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 sai lầm rằng có một quan hệ.
Có thể dùng giá trị F và df trong đầu ra từ hàm LINEST để đánh giá khả năng xảy ra giá trị F cao hơn một cách tình cờ. Có thể so sánh F với các giá trị tới hạn trong bảng phân bố F đã phát hành hoặc hàm FDIST trong Excel để tính toán xác suất của một giá trị F lớn hơn có tình cờ xảy ra. Phân bố F thích hợp có bậc tự do pb1 và v2. Nếu n là số điểm dữ liệu và const = TRUE hoặc được bỏ qua, thì v1 = n – df – 1 và v2 = df. (Nếu const = FALSE, thì v1 = n – df và v2 = df.) Hàm FDIST — với cú pháp FDIST(F,v1,v2) — sẽ trả về xác suất của một giá trị F cao hơn có tình cờ xảy ra. Trong ví dụ này, df = 6 (ô B18) và F = 459,753674 (ô A18).
Giả sử giá trị Alpha là 0,05, v1 = 11 – 6 – 1 = 4 và v2 = 6, thì cấp độ tới hạn của F là 4,53. Vì F = 459,753674 cao hơn nhiều so với 4,53, rất khó có khả năng xảy ra giá trị F cao như vậy. (Với Alpha = 0,05, giả thiết rằng không có mối quan hệ giữa known_y và known_x là bị từ chối khi F vượt quá mức giới hạn, 4,53.) Bạn có thể sử dụng hàm FDIST trong Excel để có được xác suất mà một giá trị F cao này có thể xảy ra do tình cờ. Ví dụ, FDIST(459,753674, 4, 6) = 1,37E-7, một xác suất cực nhỏ. Bạn có thể kết luận bằng cách tìm mức độ tới hạn của F trong bảng hoặc bằng cách sử dụng hàm FDIST , rằng phương trình hồi quy rất hữu ích trong việc dự đoán giá trị định giá của các tòa nhà văn phòng trong khu vực này. Hãy nhớ rằng việc sử dụng các giá trị chính xác của v1 và v2 đã được tính toán trong đoạn trước đó là rất quan trọng.
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 trong Ví dụ 3. Ví dụ, để kiểm tra hệ số tuổi thọ cho ý nghĩa thống kê, hãy 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 đâ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 trong việc ước tính giá trị định giá của một cao ốc văn phòng trong 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 trong 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ự do 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 hơn 2,447, cho nên tuổi thọ là một biến số quan trọng khi ước tính giá trị định giá của một cao ốc văn phòng. Mỗi trong 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 đây 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 hơn 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 trong việc dự đoán giá trị định giá của các cao ốc văn phòng trong vùng này.
