Cách Đánh Giá Độ Tin Cậy Dự Báo Excel Bằng Hàm RSQ Trong 5 Bước

Bạn tạo mô hình dự báo doanh số trong Excel, chạy công thức xong nhận được con số cho quý tiếp theo. Nhưng làm sao biết con số đó đáng tin cậy? Tôi từng đưa dự báo sai lệch tới 40% cho sếp vì tin tưởng mù quáng vào công thức mà không kiểm chứng độ chính xác. Hàm RSQ giải quyết vấn đề này bằng một con số duy nhất từ 0 đến 1.

Mục Lục

RSQ cho biết mô hình giải thích được bao nhiêu phần trăm

Hàm RSQ tính hệ số xác định R² để đo lường mức độ phù hợp của mô hình dự báo. Nếu RSQ trả về 0.85, có nghĩa là 85% sự thay đổi trong dữ liệu được giải thích bởi mô hình của bạn. 15% còn lại là do các yếu tố khác hoặc nhiễu ngẫu nhiên.

Cú pháp đơn giản: =RSQ(known_y's, known_x's)

known_y’s: Dữ liệu thực tế đã biết (doanh số, lợi nhuận, sản lượng)
known_x’s: Biến dự đoán (thời gian, chi phí quảng cáo, số nhân viên)

Công thức làm việc trên Excel 2016 trở lên, bao gồm Excel 365 và Excel 2021. Cả hai đối số phải có cùng số lượng điểm dữ liệu, nếu không hàm trả về lỗi #N/A.

Bước 1: Chuẩn bị dữ liệu lịch sử trong hai cột

Tổ chức dữ liệu thành hai cột liền kề. Cột X chứa biến độc lập (những gì bạn nghĩ ảnh hưởng đến kết quả), cột Y chứa biến phụ thuộc (kết quả thực tế cần dự đoán).

Ví dụ dự báo doanh số theo chi phí quảng cáo: Cột A: Chi phí quảng cáo (triệu đồng) – 10, 15, 20, 25, 30, 35, 40, 45, 50, 55 Cột B: Doanh số thực tế (triệu đồng) – 45, 62, 78, 95, 108, 125, 142, 155, 172, 188

XEM THÊM: Hàm CODE Trong Excel: 5 Ứng Dụng Thực Tế Bạn Cần Biết

Đảm bảo không có ô trống trong hai cột này. Các ô trống sẽ bị bỏ qua nhưng ô chứa số 0 vẫn được tính. Nếu có văn bản hoặc lỗi, hàm RSQ sẽ báo lỗi.

Tôi thường format hai cột này dưới dạng Table (Ctrl + T) để dễ quản lý và mở rộng dữ liệu sau này.

Bước 2: Tạo mô hình dự báo với hàm FORECAST hoặc biểu đồ

Trước khi đánh giá độ tin cậy, cần có mô hình dự báo. Hai cách phổ biến nhất:

Phương pháp 1 – Dùng hàm FORECAST: Tại ô dự báo, nhập: =FORECAST(60, B2:B11, A2:A11) Công thức này dự đoán doanh số khi chi phí quảng cáo là 60 triệu.

Phương pháp 2 – Dùng biểu đồ tự động: Chèn biểu đồ Scatter với dữ liệu từ hai cột. Chuột phải vào chuỗi dữ liệu, chọn Add Trendline. Tick vào Display Equation và Display R-squared. Excel hiển thị phương trình và giá trị R² ngay trên biểu đồ.

Tôi ưu tiên phương pháp biểu đồ vì thấy trực quan hơn, nhưng hàm FORECAST nhanh hơn khi cần nhiều giá trị dự báo.

Bước 3: Tính toán giá trị RSQ

Chọn một ô trống để hiển thị kết quả, nhập công thức RSQ với hai đối số là phạm vi dữ liệu thực tế.

Với ví dụ trên: =RSQ(B2:B11, A2:A11)

Nhấn Enter. Excel trả về một giá trị thập phân từ 0 đến 1. Trong trường hợp này, giả sử kết quả là 0.9734.

Xử lý lỗi thường gặp:

Lỗi #N/A: Hai cột có số lượng điểm dữ liệu khác nhau. Kiểm tra lại phạm vi.
Lỗi #DIV/0!: Chỉ có 1 điểm dữ liệu trong mỗi cột. Cần tối thiểu 2 điểm.
Lỗi #VALUE!: Có văn bản không thể chuyển đổi thành số trong phạm vi.

Format ô kết quả dưới dạng Percentage hoặc Number với 4 chữ số thập phân để dễ đọc.

Bước 4: Giải thích giá trị R² theo thang đo chuẩn

Giá trị RSQ dao động từ 0 đến 1, nhưng ý nghĩa phụ thuộc vào ngữ cảnh ngành nghề.

XEM THÊM: Dự Báo Doanh Thu Chính Xác Với Hàm FORECAST.ETS Excel 2016

Thang đo tham khảo:

0.90 – 1.00: Mô hình rất mạnh. Dự báo có độ tin cậy cao. Thường thấy trong quy trình sản xuất hoặc khoa học tự nhiên.
0.70 – 0.89: Mô hình tốt. Phù hợp cho hầu hết ứng dụng kinh doanh như dự báo doanh số, chi phí.
0.50 – 0.69: Mô hình trung bình. Chấp nhận được với dữ liệu có nhiều biến động như thị trường tài chính.
Dưới 0.50: Mô hình yếu. Không nên dùng để ra quyết định quan trọng.

Với R² = 0.9734 trong ví dụ trên, mô hình giải thích được 97.34% sự thay đổi của doanh số dựa trên chi phí quảng cáo. Chỉ có 2.66% do yếu tố khác.

Lưu ý quan trọng: Trong phân tích tài chính như dự đoán giá cổ phiếu, R² dưới 0.50 vẫn có thể chấp nhận được vì thị trường chịu ảnh hưởng bởi quá nhiều biến số không thể kiểm soát.

Bước 5: So sánh RSQ với CORREL để hiểu rõ hơn

Hàm CORREL tính hệ số tương quan r, còn RSQ tính bình phương của r. Hai hàm liên quan chặt chẽ và cung cấp góc nhìn bổ sung.

Thử nghiệm: =CORREL(B2:B11, A2:A11) Kết quả: 0.9866 (giả sử)

Bình phương kết quả này: 0.9866² = 0.9734, khớp với giá trị RSQ.

Sự khác biệt quan trọng:

CORREL cho biết hướng mối quan hệ (dương hay âm). Giá trị -0.95 nghĩa là tương quan âm mạnh.
RSQ chỉ cho biết độ mạnh, luôn dương từ 0 đến 1. Không phân biệt tương quan thuận hay nghịch.

Khi kiểm tra mô hình dự báo, tôi luôn chạy cả hai hàm. CORREL giúp phát hiện mối quan hệ nghịch (chi phí tăng nhưng hiệu quả giảm), còn RSQ cho biết dự báo đáng tin cậy đến mức nào.

Tăng độ tin cậy khi R² thấp

Nếu RSQ dưới 0.70, có ba cách cải thiện không cần thay đổi dữ liệu gốc.

Loại bỏ điểm dị biệt: Sắp xếp dữ liệu theo cột Y, tìm các giá trị bất thường (cao hoặc thấp đột ngột so với xu hướng chung). Xóa 3-5% quan sát ngoại lai, tính lại RSQ. Trong nhiều trường hợp, R² tăng từ 0.48 lên 0.65 chỉ sau khi loại 2-3 điểm dữ liệu nhiễu.

XEM THÊM: Cách Sử Dụng Hàm T.INV Để Tính Giá Trị Tới Hạn Trong Kiểm Định T

Tăng kích thước mẫu: Thêm dữ liệu lịch sử. Thay vì 10 tháng, thu thập 24 tháng. Mô hình với nhiều điểm dữ liệu thường cho R² ổn định hơn.

Thử biến đổi dữ liệu: Một số mối quan hệ không tuyến tính. Thay vì dùng giá trị gốc, thử logarit hoặc căn bậc hai. Ví dụ: =RSQ(B2:B11, LN(A2:A11)) có thể cho kết quả tốt hơn với dữ liệu tăng trưởng theo cấp số nhân.

Khi nào không nên phụ thuộc vào RSQ

Hàm RSQ chỉ đánh giá mô hình hồi quy tuyến tính đơn giản. Không áp dụng cho hồi quy đa biến (nhiều biến độc lập) hoặc mô hình phi tuyến phức tạp.

Nếu dự báo phụ thuộc vào nhiều yếu tố (doanh số bị ảnh hưởng bởi chi phí quảng cáo, giá sản phẩm, và mùa vụ), cần dùng công cụ Data Analysis Toolpak trong Excel. Vào Data > Data Analysis > Regression để phân tích hồi quy bội với Adjusted R Square chính xác hơn.

RSQ cao không đảm bảo mô hình tốt trong mọi trường hợp. Giá trị 0.99 nhưng chỉ dựa trên 3 điểm dữ liệu không đáng tin bằng R² = 0.75 từ 50 điểm quan sát.

Cuối cùng, RSQ chỉ đo mối quan hệ tương quan, không chứng minh nhân quả. Chi phí quảng cáo cao đi kèm doanh số cao không có nghĩa là quảng cáo gây ra doanh số tăng. Có thể cả hai đều tăng do yếu tố thứ ba như xu hướng thị trường.

Ứng dụng thực tế ngoài dự báo doanh số

RSQ hữu ích trong nhiều tình huống kinh doanh khác. Phân tích mối quan hệ giữa số giờ đào tạo và năng suất nhân viên, R² = 0.68 giúp quyết định có nên tăng ngân sách đào tạo. Đánh giá hiệu quả chiến dịch marketing bằng cách so sánh chi phí với lượt truy cập website, R² = 0.82 xác nhận kênh quảng cáo đang chạy hiệu quả.

Tôi dùng RSQ hàng tuần để kiểm tra mô hình định giá sản phẩm. Khi R² đột ngột giảm từ 0.75 xuống 0.52, đó là tín hiệu thị trường thay đổi và cần cập nhật chiến lược giá.

Hàm này có sẵn trên Excel từ phiên bản 2016 trở đi, không cần cài thêm add-in. Đối với mô hình phức tạp hơn với nhiều biến, cân nhắc nâng cấp lên Power BI hoặc học thêm về Adjusted R Square trong công cụ Regression của Excel.