Hồi quy đơn trong Excel: đọc R² và phần dư mà không bị đánh lừa

Bảng có 12 cửa hàng của một chuỗi bán lẻ: mỗi dòng là chi quảng cáo và doanh thu quý vừa rồi, tính bằng triệu đồng. =SLOPE ra 7,59 và =RSQ ra 0,87, bạn sắp ghi lên slide "thêm 1 triệu quảng cáo, thêm 7,6 triệu doanh thu; mô hình giải thích 87%". Trước khi ghi, nên kiểm gì? Bài đi theo quy trình hồi quy tuyến tính đơn: vẽ, ước lượng, đánh giá, rồi dự đoán trong giới hạn. Ba bộ số đều là giả định do tôi sinh bằng Python, nên tôi biết cơ chế thật mà bảng thật không cho thấy. Về cách đọc biểu đồ phân tán, xem 5 câu hỏi trước khi tin một biểu đồ.

Hàm Excel nào cho ra con số nào?

SLOPE và INTERCEPT cho đường thẳng, RSQ cho tỷ lệ biến thiên được giải thích, STEYX cho mức lệch điển hình của doanh thu quanh đường. =LINEST(y; x; TRUE; TRUE) trả cả lưới thống kê. Tôi không chạy được công cụ Regression của Data Analysis ToolPak nên không đối chiếu bố cục của nó.

Bộ A là 12 cửa hàng xếp theo mức chi. Ba cột đầu là dữ liệu; hai cột sau là kết quả của đường 130,9 + 7,59 × chi để bạn đối chiếu:

Chi  Doanh thu  Diện tích  Dự đoán  Phần dư
  8        233         75    191,7     41,3
 10        217         80    206,8     10,2
 14        194         40    237,2    -43,2
 18        231         60    267,6    -36,6
 19        226         55    275,1    -49,1
 21        339        110    290,3     48,7
 25        308         95    320,7    -12,7
 25        379        150    320,7     58,3
 31        331        120    366,2    -35,2
 34        389        135    389,0      0,0
 43        489        170    457,3     31,7
 50        497        190    510,4    -13,4

=SLOPE(B2:B13;A2:A13)                    7,59   mức chênh doanh thu đi kèm mỗi triệu chi thêm
=INTERCEPT(B2:B13;A2:A13)                130,9  giá trị đường tại chi = 0
=RSQ(B2:B13;A2:A13)                      0,868  tỷ lệ biến thiên được giải thích
=STEYX(B2:B13;A2:A13)                    39,7   mức lệch điển hình, triệu đồng
=INDEX(LINEST(B2:B13;A2:A13;1;1);2;1)    0,936  sai số chuẩn của hệ số dốc

Dùng dấu phẩy thay dấu chấm phẩy nếu máy bạn dùng dấu chấm thập phân. Lưới LINEST có năm hàng: hệ số (dốc trước, chặn sau), sai số chuẩn của chúng, R² và sai số chuẩn ước lượng, F và bậc tự do, hai tổng bình phương, theo tài liệu Microsoft về hàm này. INDEX bọc ngoài để khỏi nhập công thức mảng.

R² và sai số chuẩn nói gì, và không nói gì?

R² 0,868 nghĩa là đường thẳng giải thích khoảng 87% biến thiên của doanh thu giữa 12 cửa hàng (James et al., 2021, p. 70). Sai số chuẩn 39,7 là mức lệch điển hình quanh đường; James và cộng sự nói đó cũng là mức dự đoán vẫn lệch trung bình ngay cả khi biết đúng đường thật (James et al., 2021, p. 69).

Hai lưu ý. Sách nói R² thế nào là tốt tuỳ lĩnh vực: vật lý đòi sát 1, còn trong sinh học, tâm lý, marketing, mức thấp hơn 0,1 nhiều có thể thực tế hơn (James et al., 2021, p. 70); bộ A đẹp hơn hẳn mức đó vì tôi sinh nó để các bẫy dễ thấy. Với một biến X, R² bằng bình phương hệ số tương quan (James et al., 2021, p. 71): =CORREL(A2:A13;B2:B13)^2 cho đúng 0,868.

Kiểm dốc: t bằng dốc chia sai số chuẩn, 7,59 / 0,936 = 8,11, với bậc tự do n − 2 = 10; =T.DIST.2T(8,11;10) cho giá trị p khoảng 0,00001 (James et al., 2021, p. 67). Khoảng tin cậy 95% là 7,59 ± T.INV.2T(0,05;10) × 0,936, tức 5,50 đến 9,68; sách dùng xấp xỉ ±2 sai số chuẩn (James et al., 2021, p. 66). Giá trị p nhỏ cho thấy khó có chuyện thấy quan hệ rõ như vậy nếu hai biến thật sự không liên quan (James et al., 2021, pp. 67-68). Nó không nói đường có đúng dạng, cũng không nói nguyên nhân.

Biểu đồ phần dư tìm gì mà R² không thấy?

Tìm mẫu hình. Với hồi quy đơn, vẽ phần dư theo X; lý tưởng là không thấy mẫu hình nào (James et al., 2021, pp. 93-94). Chữ U báo đường không phải đường thẳng (James et al., 2021, p. 94); chữ U ngược cũng báo như vậy (suy luận của tôi). Hình phễu, phần dư rộng ra theo mức dự đoán, báo phương sai không đều (James et al., 2021, p. 96). Với dữ liệu theo thời gian, phần dư liền kề giống nhau, gọi là bám nhau, báo sai số tương quan (James et al., 2021, pp. 94-95). Trong Excel, thêm một cột phần dư rồi vẽ phân tán theo cột X:

D2: =B2-(INTERCEPT($B$2:$B$13;$A$2:$A$13)+SLOPE($B$2:$B$13;$A$2:$A$13)*A2)    kéo xuống D13

Bộ B: 12 tuần của một cửa hàng trực tuyến, chi 5 đến 60 triệu mỗi tuần, doanh thu bão hoà dần. Tôi sinh nó từ đường cong 420 × (1 − e^(−chi/30)) cộng nhiễu, nên biết đường thật cong. Đường thẳng cho R² 0,947, dốc 5,29, giá trị p cỡ 10⁻⁷: mọi chỉ số trông tốt. Phần dư thì khác:

Chi:        5  10  15  20  25  30  35  40  45  50  55  60
Doanh thu: 64 122 163 196 233 257 290 321 322 335 357 366
Phần dư:  -43 -11   3  10  21  18  25  29   4 -10 -14 -32

Âm ở hai đầu, dương ở giữa: chữ U ngược. Thêm cột chi² và chạy LINEST với hai cột X liền nhau cho R² 0,996 và sai số chuẩn 6,5 thay vì 23,6. Sách gợi ý biến đổi X, như log X, √X hay X², khi phần dư báo phi tuyến (James et al., 2021, p. 94).

Một điểm có thể kéo cả đường hồi quy không?

Có, nếu điểm đó có X khác thường. Sách phân biệt điểm ngoại lai, có Y khác thường so với X, với điểm đòn bẩy cao, có X khác thường; bỏ một điểm đòn bẩy cao đổi đường nhiều hơn hẳn bỏ một điểm ngoại lai (James et al., 2021, pp. 97-98). Với một biến, đòn bẩy của dòng i là 1/n + (xᵢ − x̄)² / Σ(xᵢ − x̄)²; mức trung bình luôn là (p + 1)/n, ở đây 2/12 = 0,167, và điểm vượt xa mức đó đáng nghi (James et al., 2021, p. 99). Trong Excel:

E2: =1/COUNT($A$2:$A$13)+(A2-AVERAGE($A$2:$A$13))^2/DEVSQ($A$2:$A$13)    kéo xuống E13

Ở bộ A, cửa hàng 12 (chi 50) có đòn bẩy 0,435, cao nhất nhóm, nhưng nằm sát đường (phần dư −13,4) nên không gây hại. Bộ C thì khác: 11 cửa hàng chi 8 đến 28 triệu, doanh thu 178 đến 274 triệu sinh ngẫu nhiên, không liên hệ gì với mức chi, cộng một cửa hàng đầu tàu chi 90 và bán 430.

Chi:        8  10  12  14  15  17  19  21  24  26  28  90
Doanh thu: 231 268 264 216 222 215 246 228 251 178 274 430

Đòn bẩy của cửa hàng đầu tàu là 0,924. Phần dư của nó chỉ +17,8, nhỏ hơn −79,6 của một cửa hàng khác: đường bị kéo về phía nó nên biểu đồ phần dư trông êm. Sách nói đáng lo khi đường bị chi phối bởi một hai điểm, vì lỗi ở các điểm đó làm hỏng cả mô hình (James et al., 2021, pp. 98-99). Cách kiểm là chạy =RSQ và =SLOPE hai lần, có và không có điểm, và báo cả hai.

Hệ số dốc có phải tác động của quảng cáo không?

Không. Sách định nghĩa dốc là mức tăng trung bình của Y đi kèm khi X tăng một đơn vị, và coi sai số là nơi chứa những gì mô hình bỏ sót, trong đó có các biến khác làm Y đổi (James et al., 2021, p. 63). Tôi sinh bộ A theo doanh thu = 100 + 1,0 × chi + 1,8 × diện tích + nhiễu (độ lệch chuẩn 16), và để cửa hàng lớn chi nhiều hơn: tương quan giữa chi và diện tích là 0,87. Dốc đơn 7,59 gấp 7,6 lần tác động thật 1,0, vì chi quảng cáo nhận phần công của diện tích.

Sách có cùng hiện tượng: báo giấy trông liên quan đến doanh số trong hồi quy đơn, nhưng hệ số gần 0 khi hồi quy cùng TV và radio, vì chi báo giấy tăng theo chi radio (James et al., 2021, p. 74). Chèn cột diện tích ngay sau cột chi để hai cột X liền nhau (doanh thu dịch sang cột C), rồi chạy =LINEST(C2:C13; A2:B13; TRUE; TRUE): hệ số của chi là 2,78, khoảng 95% từ 0,87 đến 4,70 (chứa mức thật 1,0), hệ số của diện tích 1,48, R² 0,977. Excel trả hệ số theo thứ tự ngược với thứ tự cột.

Khoảng 0,87 đến 4,70 vẫn rộng vì 12 dòng và hai biến tương quan mạnh khó tách nhau; thêm biến đo được cũng không loại được biến chưa đo (suy luận của tôi). Sách phân biệt dự đoán, chỉ cần đoán đúng, với suy diễn, cần hiểu quan hệ (James et al., 2021, pp. 19-20). Để ước doanh thu cửa hàng cùng loại khi biết ngân sách, dốc đơn có thể đủ dùng. Để biết tăng quảng cáo 10 triệu ở cửa hàng X thì được gì, dữ liệu quan sát không trả lời trực tiếp; tôi gợi ý đổi mức chi ở vài cửa hàng tương đồng và giữ vài cửa hàng làm mốc.

Dự đoán xa vùng dữ liệu được đến đâu?

Chỉ trong khoảng X đã quan sát, và ngay trong đó khoảng dự đoán đã rộng. Bộ A có chi từ 8 đến 50. Tại 30, =FORECAST(30;B2:B13;A2:A13) cho 358,6, khoảng dự đoán 95% từ 265,9 đến 451,4. Tại 100, dự đoán là 889,9, khoảng từ 708 đến 1.072. Khoảng này vẫn giả định đường thẳng đúng ở mức chi gấp đôi mức cao nhất đã thấy; sách nói khi tính khoảng dự đoán, họ bỏ qua sai lệch do mô hình tuyến tính chỉ là xấp xỉ (James et al., 2021, p. 82). Công thức khoảng dự đoán tôi dùng:

=FORECAST(x0;B2:B13;A2:A13) ± T.INV.2T(0,05;10)*STEYX(B2:B13;A2:A13)*SQRT(1+1/COUNT(A2:A13)+(x0-AVERAGE(A2:A13))^2/DEVSQ(A2:A13))

Đó là công thức giáo trình chuẩn; tôi không thấy nó trong phần sách đã đọc.

Bộ B cho thấy ngoại suy sai thế nào. Đường thẳng dự đoán 504 ở chi 80 và 609 ở chi 100; đường cong tôi dựng cho 391 và 405, tiệm cận 420. R² 0,947 không báo trước. Hệ số chặn 130,9 của bộ A cũng là ngoại suy: không cửa hàng nào chi dưới 8, nên đừng đọc nó là "doanh thu khi không quảng cáo".

Đào sâu: tôi đã đọc gì, chưa đọc gì?

James và cộng sự (2021, ấn bản 2): tôi đọc hết mục 3.1, mục 3.2.1 đến bảng 3.5, đoạn khoảng dự đoán ở trang 82, mục 3.3.3 từ vấn đề 1 đến 5 (trang 92 đến 99) và đoạn dự đoán với suy diễn ở mục 2.1. Tôi không đọc phần cộng tuyến, phần thực hành bằng R, các chương khác và bài tập. Bản tôi đọc là PDF của nhóm tác giả do Internet Archive lưu ngày 19/11/2021; tôi không tải được bản sửa lỗi tháng 6/2023 nên chưa đối chiếu. Số trang là số in. Sách không dùng từ "extrapolation"; phần ngoại suy là suy luận của tôi.

Về Excel, tôi đọc trang Microsoft về LINEST (https://support.microsoft.com/en-us/excel/functions/linest-function) để lấy bố cục lưới và ghi chú về SLOPE; tôi không đọc tài liệu ToolPak và không chạy Microsoft Excel. Dữ liệu sinh bằng Python (A: hạt giống 851, chọn sau khi thử nhiều hạt giống để dốc đơn quanh 7,5, nên không phải mẫu ngẫu nhiên; B: hạt giống 7; C: hạt giống 11). Mọi thống kê trong bảng và khối mã được tính bằng Python và bằng công thức chạy trong LibreOffice Calc, và khớp.

Giới hạn lớn nhất: dữ liệu là giả định, nên bài chỉ cho thấy các bẫy hoạt động ra sao khi bạn biết sự thật. Dữ liệu thật không có phép đối chiếu đó.