Cách vẽ phương trình hồi quy tuyến tính trong Excel

Bài viết này mô tả cú pháp công thức và cách sử dụng hàmLINEST trong Microsoft Excel. Hãy tìm các liên kết để xem thêm thông tin về việc lập biểu đồ và thực hiện phân tích hồi quy trong mục Xem Thêm.

Nội dung chính Show

Chú thích
Ví dụ 1 - Độ dốc và giao cắt Y
Ví dụ 2: Hồi quy Tuyến tính Đơn giản
Ví dụ 3: Hồi quy Tuyến tính Đa biến
Ví dụ 4: Sử dụng Thống kê F và r2
Ví dụ 5: Tính toán thống kê t-Statistics

Mô tả

Hàm LINEST tính toán các thống kê cho một đường thẳng bằng cách dùng phương pháp "bình phương nhỏ nhất" để tính toán đường thẳng phù hợp nhất với dữ liệu của bạn, rồi trả về một mảng mô tả đường thẳng đó. Bạn cũng có thể kết hợp hàm LINEST với các hàm khác để tính toán thống kê cho các kiểu mô hình khác là đường tuyến tính trong các tham số chưa biết, bao gồm chuỗi đa thức, lô-ga-rit, hàm mũ và lũy thừa. Vì hàm này trả về một mảng giá trị, cho nên nó phải được nhập vào dưới dạng công thức mảng. Có các hướng dẫn ở sau các ví dụ trong bài viết này.

Phương trình của đường thẳng là:

y = mx + b

–hoặc–

y = m1x1 + m2x2 + ... + b

nếu có nhiều phạm vi giá trị x, khi mà giá trị y phụ thuộc là một hàm của các giá trị x độc lập. Giá trị m là các hệ số tương ứng với mỗi giá trị x và b là giá trị hằng số. Lưu ý rằng y, x và m có thể là các véc-tơ. Mảng mà hàm LINEST trả về là {mn,mn-1,...,m1,b}. Hàm LINEST cũng có thể trả về cá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'sBắt buộc. Tập giá trị y mà bạn đã biết trong quan hệ y = mx + b.
- Nếu phạm vi của known_y's nằm trong một cột đơn lẻ, thì mỗi cột của known_x's được hiểu là một biến số riêng rẽ.
- Nếu phạm vi của known_y's nằm trong một hàng đơn lẻ, thì mỗi hàng của known_x's được hiểu là một biến số riêng rẽ.
known_x'sTùy chọn. Tập giá trị x mà bạn có thể đã biết trong quan hệ y = mx + b.
- Phạm vi của known_x's có thể bao gồm một hoặc nhiều tập biến số. Nếu chỉ dùng một biến số, thì known_y's và known_x's có thể là các phạm vi với bất kỳ hình dạng nào, miễn là chúng có các kích thước bằng nhau. Nếu dùng nhiều biến số, thì known_y's phải là một véc-tơ (có nghĩa là một phạm vi cao một hàng và rộng một cột).
- Nếu known_x's được bỏ qua, thì nó được giả định là một mảng {1,2,3,...} có cùng kích thước như known_y's.
constTùy chọn. Một giá trị lô-gic chỉ rõ có bắt buộc hằng số b phải bằng 0 hay không.
- Nếu const là TRUE hoặc được bỏ qua, thì b được tính toán bình thường.
- Nếu const là FALSE, thì b được đặt bằng 0 và giá trị m được điều chỉnh để phù hợp với y = mx.
statsTùy chọn. Giá trị lô-gic chỉ rõ có trả về các thống kê hồi quy bổ sung hay không.
- Nếu stats là TRUE, thì giá trị linest trả về các 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 là FALSE hoặc được bỏ qua, thì hàm LINEST chỉ trả về hệ số m và hằng số b.
  
  Các thống kê hồi quy bổ sung như sau.

Thống kê	Mô tả
se1,se2,...,sen	Giá trị lỗi chuẩn chủa các hệ số m1,m2,...,mn.
seb	Giá trị lỗi chuẩn của hằng số b (seb = #N/A khi const là FALSE).
r2	Hệ số xác định. So sánh các giá trị y ước tính và thực tế và nằm trong phạm vi giá trị từ 0 tới 1. Nếu nó là 1, thì có một tương quan hoàn hảo trong mẫu— không có sự khác biệt nào giữa giá trị y ước tính và giá trị y thực tế. Ở thái cực ngược lại, nếu hệ số xác định là 0, thì phương trình hồi quy không còn hữu ích trong việc dự đoán giá trị y. Để biết cách tính toán2, hãy xem mục "Ghi chú" ở phần sau bài viết này.
sey	Lỗi chuẩn cho ước tính y.
F	Thống kê F, hoặc giá trị F quan sát được. Dùng thống kê F để xác định xem quan hệ quan sát được giữa các biến số độc lập và phụ thuộc có ngẫu nhiên xảy ra không.
df	Bậc tự do. Dùng bậc tự do để giúp bạn tìm giá trị F tới hạn trong bảng thống kê. So sánh các giá trị bạn tìm thấy trong bảng với thống kê F mà hàm LINEST trả về để xác định mức độ tin cậy của mô hình. Để tìm hiểu cách tính toán df, hãy xem mục "Ghi chú" ở phần sau bài viết này. Ví dụ 4 nói về cách dùng F và df.
ssreg	Tổng bình phương hồi quy.
ssresid	Tổng bình phương thặng dư. Để biết cách tính toán ssreg và ssresid, hãy xem mục "Ghi chú" ở phần sau bài viết này.

Minh họa sau đây cho thấy thứ tự mà các thống kê hồi quy bổ sung được trả về.

Chú thích

Bạn có thể mô tả bất kỳ đường thẳng nào bằng độ dốc và 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).

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 trình của đường thẳng là y = mx + b. Khi đã biết giá trị của m và b, bạn có thể tính toán bất kỳ điểm nào trên đường thẳng bằng cách nhập giá trị y hoặc y vào phương trình đó. Bạn cũng có thể dùng hàm TREND.
Khi bạn chỉ có một biến độc lập x, bạn có thể tìm được độ dốc và giá trị giao cắt y trực tiếp bằng cách dù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)
Độ chính xác của đường thẳng do hàm LINEST tính toán phụ thuộc vào độ phân tán trong dữ liệu của bạn. Dữ liệu càng tuyến tính, thì mô hình LINEST càng chính xác. Hàm LINEST dùng phương pháp bình phương nhỏ nhất để xác định sự phù hợp nhất của dữ liệu. Khi bạn chỉ có một biến số độc lập x, thì các phép tính cho m và b dựa vào công thức sau đây:

trong đó x và y là các trung độ mẫu, tức là x = AVERAGE(known x's) và y = AVERAGE(known_y's).
Các hàm phù hợp với đường thẳng và đường cong LINEST và LOGEST có thể tính toán đường thẳng hoặc đường cong hàm mũ phù hợp nhất với dữ liệu của bạn. Tuy nhiên, bạn phải quyết định kết quả nào trong hai kết quả là phù hợp nhất với dữ liệu của mình. Bạn có thể tính toá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 hàm mũ. Những hàm này, không có đối số new_x's, trả về một mảng giá trị y được dự đoán dọc theo đường thẳng hoặc đường cong tại điểm dữ liệu thực của bạn. Sau đó, bạn có thể so sánh giá trị dự đoán với giá trị thực tế. Bạn có thể muốn vẽ đồ thị cho cả hai để có được so sánh trực quan.
Trong phân tích hồi quy, Excel tính toán tại mỗi điểm bình phương của hiệu số giữa giá trị y ước tính cho điểm đó và giá trị y thực tế của điểm đó. Tổng của các bình phương hiệu này được gọi là tổng bình phương thặng dư, ssresid. Sau đó, Excel tính toán tổng cộng bình phương, sstotal. Khi đối số const = TRUE hoặc được bỏ qua, thì tổng cộng bình phương là tổng của các bình phương hiệu giữa giá trị y thực tế và bình quân các giá trị y. Khi đối số const = FALSE, thì tổng cộng bình phương là tổng các bình phương của các giá trị y thực tế (mà không trừ giá trị y trung bình ra khỏi mỗi giá trị y). Sau đó có thể tìm thấy tổng bì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 các bình phương, thì giá trị của hệ số xác định, r2, cànglớn, mà đây là một chỉ báo cho biết phương trình kết quả của phân tích hồi quy thể hiện rõ đến đâu mối quan hệ giữa các biến số. 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 (giả sử rằng Y’s và X’s nằm trong các cột) có thể không có giá trị dự đoán bổ sung nào khi có mặt các cột X khác. Nói cách khác, loại bỏ một hoặc nhiều cột X có thể dẫn tới các giá trị dự đoán Y chính xác bằng nhau. Trong trường hợp đó, các cột X dư thừa nên được bỏ qua trong mô hình hồi quy. Hiện tượng này được gọi là "tính cộng tuyến" vì bất kỳ cột X dư thừa nào cũng có thể được diễn giải là tổng của nhiều cột X không dư thừa. Hàm LINEST kiểm tra tính cộng tuyến và loại bỏ mọi cột X dư thừa ra khỏi mô hình hồi quy khi phát hiện thấy chúng. Các cột X được loại bỏ có thể được nhận biết trong kết quả của hàm LINEST là chúng có hệ số 0 ngoài các giá trị se 0. Nếu một hoặc nhiều cột bị loại bỏ vì dư thừa, df bị ảnh hưởng vì df phụ thuộc vào số cột X được thực sự dùng cho mục đích dự đoán. Để biết chi tiết cách tính toán df, hãy xem Ví dụ 4. Nếu df bị thay đổi do các cột X dư thừa bị loại bỏ, các giá trị của sey và F cũng bị ảnh hưởng. Tí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 mà tính cộng tuyến có nhiều khả năng xuất hiện là khi một vài cột X chỉ chứa các giá trị 0 và 1 như là chỉ báo về việc liệu một đối tượng trong một thực nghiệp có phải là hoặc không phải là một phần tử của một nhóm cụ thể. Nếu const = TRUE hoặc được bỏ qua, thì hàm LINEST chèn một cột X bổ sung với tất cả các giá trị 1 để mô tả giao cắt. Nếu bạn có một cột trong đó mỗi đối tượng nam được biểu thị là 1, nữ được biểu thị là 0, và bạn cũng muốn một cột trong đó mỗi đối tượng nữ được biểu thị là 1, nam biểu thị là 0, thì cột thứ hai là thừa vì có thể có được các mục nhập của nó bằng cách trích nhập mục từ cột "chỉ báo nam" từ nhập mục trong cột bổ sung với tất cả các giá trị 1 mà hàm LINEST thêm vào.
Giá trị của df được tính toán như sau, khi không có cột X nào được loại bỏ khỏi mô hình do tính cộng tuyến: nếu có các cột k chứa known_x’s và const = TRUE hoặc được bỏ qua, thì df = n – k – 1. Nếu const = FALSE, thì df = n - k. Trong cả hai trường hợp, cột X đã được loại bỏ do tính cộng tuyến sẽ làm tăng giá trị của df thêm 1.
Khi nhập một hằng số mảng (chẳng hạn như known_x's) làm đối số, bạn hãy dùng dấu phẩy để phân tách các giá trị chứa trong cùng một hàng và dùng dấu chấm phẩy để phân tách hàng. Ký tự phân tách có thể khác nhau tùy thuộc vào thiết đặt vùng của bạn.
Hãy lưu ý rằng các giá trị y mà phương trình hồi quy dự đoán có thể không hợp lệ nếu chúng nằm ngoài phạm vi các giá trị y mà bạn dùng để xác định phương trình.
Thuật toán ẩn dưới dùng trong hàm LINEST khác với thuật toán ẩn dưới dùng trong các hàm SLOPE và INTERCEPT. Sự khác nhau giữa các thuật toán này có thể dẫn đến các kết quả khác nhau khi dữ liệu chưa được xác định và cộng tuyến. Ví dụ, nếu các điểm dữ liệu của đối số known_y's là 0 và các điểm dữ liệu của đối số known_x's là 1:
- Hàm LINEST trả về giá trị 0. Thuật toán của hàm LINEST được thiết kế để trả về kết quả hợp lý của dữ liệu cộng tuyến và trong trường hợp này, có thể tìm thấy ít nhất một câu trả lời.
- Hàm SLOPE và INTERCEPT trả về giá trị lỗi #DIV/0! lỗi. Thuật toán của hàm SLOPE và INTERCEPT được thiết kế để chỉ tìm kiếm một câu trả lời và trong trường hợp này có thể có nhiều câu trả lời.
Ngoài việc dùng hàm LOGEST để tính toán các thống kê hoặc các kiểu hồi quy khác, bạn có thể dùng hàm LINEST để tính toán một phạm vi các kiểu hồi quy khác bằng cách nhập các hàm của các biến số x làm các chuỗi x và y cho hàm LINEST. Ví dụ, công thức sau đây:

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

hoạt động khi bạn có một cột đơn các giá trị y và một cột đơn các giá trị x cần tính toán phép xấp xỉ lập phương (đa thức lũy thừa bậc 3) của biểu mẫu:

y = m1*x + m2*x^2 + m3*x^3 + b

Bạn có thể điều chỉnh công thức này để tính toán các kiểu hồi quy khác, nhưng trong một số trường hợp nó đòi hỏi phải điều chỉnh giá trị đầu ra và các thống kê khác.
Giá trị F-test mà hàm LINEST trả về khác với giá trị F-test mà hàm FTEST trả về. Hàm LINEST trả về thống kê F, còn hàm FTEST trả về xác suất.

Ví dụ

Ví dụ 1 - Độ dốc và giao cắt Y

Sao chép dữ liệu củ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ô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 đã biết	X đã biết
1	0
9	4
5	2
7	3
Kết quả (độ dốc)	Kết quả (giao cắt y)
2	1

Công thức (công thức mảng trong ô A7:B7)
=LINEST(A2:A5,B2:B5,,FALSE)

Ví dụ 2: Hồi quy Tuyến tính Đơn giản

Thá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 quả
=SUM(LINEST(B1:B6, A1:A6)*{9,1})	$11.000
	Tính toán ước tính doanh số bán hàng trong tháng thứ chín, dựa trên doanh số các tháng từ1 đến 6.

Ví dụ 3: Hồi quy Tuyến tính Đa biến

Diện tích mặt sàn (x1)	Văn phòng (x2)	Cửa vào (x3)	Tuổi thọ (x4)	Giá trị định giá (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 ô A19)
=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 đối số LINEST), thể hiện một quan hệ mạnh mẽ giữa các biến số độc lập và giá bán. Bạn có thể dùng thống kê F để xác định xem những kết quả này, với giá trị r2 cao như vậy, có ngẫu nhiên xảy ra hay không.

Giả sử rằng trên thực tế không có quan hệ nào giữa các biến số, nhưng bạn đã lấy một mẫu hiếm gặp về 11 tòa cao ốc văn phòng, khiến cho phân tích thống kê thể hiện một quan hệ mạnh mẽ. Thuật ngữ "Alpha" được dùng để chỉ xác xuất của kết luận sai lầm rằng có 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. Có thể so sánh F với 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 giá trị F lớn hơn xuất hiện tình cờ. Phân bố F thích hợp có bậc tự do v1 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 giá trị F cao hơn xuất hiện tình cờ. 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, mức quan trọng 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 đến vậy. (Với Alpha = 0,05, giả thiết rằng không có mối quan hệ nào giữa mức quan hệ của known_y và của known_x là bị từ chối khi F vượt quá mức giới hạn, 4,53.) Bạn có thể dùng hàm FDIST trong Excel để có được xác suất giá trị F cao đến mức này do vô tình xảy ra. 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 F trong bảng hoặc bằng cách dùng hàm FDIST, rằng phương trình hồi quy hữu ích trong việc dự đoán giá trị định giá của các cao ốc văn phòng trong khu vực này. Hãy nhớ rằng điều quan trọng là sử dụng các giá trị đúng của v1 và v2 được tính toán trong đoạn văn trước đó.

Ví dụ 5: Tính toán thống kê t-Statistics

Một kiểm tra giả thuyết khác sẽ xác định xem mỗi hệ số độ dốc có hữu ích không trong việc ước tính giá trị định giá của một cao ốc văn phòng trong Ví dụ 3. Ví dụ, để kiểm tra hệ số tuổi thọ cho ý nghĩa thống kê, hãy chia -234,24 (hệ số độ dốc tuổi thọ) cho 13,268 (lỗi chuẩn ước tính của hệ số tuổi thọ trong ô A15). Dưới đây là giá trị t-quan sát:

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

Nếu giá trị tuyệt đối của t đủ lớn, thì có thể kết luận rằng hệ số độ dốc là hữu ích trong việc ước tính giá trị định giá của một cao ốc văn phòng trong Ví dụ 3. Bảng sau đây thể hiện giá trị tuyệt đối của 4 giá trị t-quan sát.

Nếu bạn tham khảo bảng trong sổ tay thống kê, bạn sẽ thấy rằng t-tới hạn, hai phía, với 6 bậc tự do và Alpha = 0,05 là 2,447. Cũng có thể tìm được giá trị tới hạn này bằng cách dùng hàm TINV trong Excel. TINV(0,05,6) = 2,447. Vì giá trị tuyệt đối của t (17,7) lớn hơn 2,447, cho nên tuổi thọ là một biến số quan trọng khi ước tính giá trị định giá của một cao ốc văn phòng. Mỗi trong số các biến số độc lập khác có thể được kiểm tra ý nghĩa thống kê theo cách tương tự. Dưới đây là các giá trị t-quan sát cho mỗi biến số độc lập.

Biến số	giá trị t-quan sát
Diện tích mặt sàn	5,1
Số lượng văn phòng	31,3
Số lượng cửa vào	4,8
Tuổi thọ	17,7

Tất cả những giá trị này đều có giá trị tuyệt đối lớn hơn 2,447, vì vậy tất cả các biến số dùng trong phương trình hồi quy đều hữu ích trong việc dự đoán giá trị định giá của các cao ốc văn phòng trong vùng này.