FORECAST.ETS.STAT Excel 2016: Cách Đọc 8 Chỉ Số Thống Kê Để Dự Báo Chính Xác

Hàm FORECAST.ETS trong Excel dự báo giá trị tương lai, nhưng làm sao biết liệu dự báo đó có chính xác không? Đây chính là lúc FORECAST.ETS.STAT phát huy tác dụng. Hàm này trả về 8 loại giá trị thống kê giúp bạn đánh giá chất lượng mô hình dự báo, từ các tham số thuật toán cho đến độ chính xác sai số. Tôi sử dụng những chỉ số này hàng ngày để kiểm tra xem liệu dự báo doanh số có đáng tin cậy trước khi đưa vào báo cáo quản lý.

Mục Lục

Cú pháp và tham số cơ bản

FORECAST.ETS.STAT xuất hiện lần đầu trong Excel 2016 và không khả dụng trên Excel for Web hoặc Mac. Cú pháp đầy đủ như sau:

=FORECAST.ETS.STAT(values, timeline, statistic_type, [seasonality], [data_completion], [aggregation])

Trong đó tham số quan trọng nhất là statistic_type – một số nguyên từ 1 đến 8 chỉ định loại thống kê bạn muốn lấy. Tham số values chứa dữ liệu lịch sử, timeline là dãy thời gian tương ứng. Ba tham số cuối là tùy chọn và hoạt động giống như trong hàm FORECAST.ETS.

Mỗi statistic type trả về một chỉ số khác nhau về mô hình dự báo. Hiểu rõ ý nghĩa từng chỉ số giúp bạn điều chỉnh mô hình hoặc quyết định có nên tin vào kết quả dự báo hay không.

Alpha – Tham số giá trị cơ sở

Alpha parameter kiểm soát trọng số của dữ liệu gần đây trong mô hình. Sử dụng statistic_type = 1 để lấy giá trị này.

=FORECAST.ETS.STAT(B2:B29, A2:A29, 1)

Giá trị Alpha nằm trong khoảng từ 0 đến 1. Alpha cao hơn nghĩa là mô hình tập trung nhiều vào dữ liệu gần đây, bỏ qua các giá trị cũ. Alpha thấp hơn trải đều trọng số cho toàn bộ lịch sử dữ liệu.

XEM THÊM: Hàm MINIFS Excel 2019: Cú Pháp Và 5 Ví Dụ Thực Tế Bạn Cần Biết

Nếu dữ liệu của bạn có xu hướng thay đổi nhanh, Alpha thường cao hơn 0.3. Với dữ liệu ổn định theo thời gian, Alpha thấp hơn 0.3 cho kết quả tốt hơn. Tôi thường kiểm tra Alpha trước tiên vì nó cho biết liệu mô hình có đang phản ứng quá mẫn cảm với biến động ngắn hạn hay không.

Beta – Tham số xu hướng

Beta parameter đo lường độ nghiêng của xu hướng tuyến tính. Dùng statistic_type = 2 để truy xuất.

=FORECAST.ETS.STAT(B2:B29, A2:A29, 2)

Giống Alpha, Beta cũng nằm giữa 0 và 1. Beta cao cho biết xu hướng tăng hoặc giảm mạnh trong dữ liệu. Beta thấp nghĩa là dữ liệu tương đối phẳng, không có xu hướng rõ ràng.

Khi dự báo doanh số tăng trưởng, tôi mong đợi Beta trong khoảng 0.1 đến 0.4. Beta quá cao có thể báo hiệu mô hình đang overfitting với các đợt tăng trưởng ngắn hạn. Beta quá thấp khi dữ liệu rõ ràng có xu hướng cũng là dấu hiệu cần kiểm tra lại.

Gamma – Tham số tính thời vụ

Gamma kiểm soát trọng số của chu kỳ theo mùa gần đây nhất. Lấy bằng statistic_type = 3.

=FORECAST.ETS.STAT(B2:B29, A2:A29, 3)

Gamma cũng có giá trị từ 0 đến 1. Gamma cao hơn ưu tiên các mẫu thời vụ gần đây, trong khi Gamma thấp xem xét toàn bộ lịch sử thời vụ.

Với dữ liệu bán lẻ có mùa cao điểm cuối năm, Gamma thường nằm quanh 0.4 đến 0.7. Nếu mẫu thời vụ thay đổi theo năm, Gamma cao hơn cho phép mô hình thích nghi nhanh hơn. Dữ liệu có mẫu thời vụ ổn định qua nhiều năm thì Gamma thấp hơn đủ.

Số chu kỳ thời vụ được phát hiện

Chỉ số này cho biết độ dài của mẫu lặp lại mà Excel tự động phát hiện. Dùng statistic_type = 4.

=FORECAST.ETS.STAT(B2:B29, A2:A29, 4)

Kết quả trả về số nguyên biểu thị độ dài chu kỳ tính theo đơn vị thời gian trong timeline. Ví dụ với dữ liệu hàng tháng, kết quả 12 nghĩa là mẫu lặp lại hàng năm. Với dữ liệu hàng ngày, kết quả 7 báo hiệu chu kỳ tuần.

XEM THÊM: Hàm QUARTILE.EXC Trong Excel: Hướng Dẫn Chi Tiết Từ Cú Pháp Đến Ứng Dụng Thực Tế

Nếu bạn biết dữ liệu có chu kỳ cụ thể nhưng hàm phát hiện sai, hãy chỉ định tham số seasonality trực tiếp trong FORECAST.ETS thay vì để Excel tự động. Seasonality tối đa hỗ trợ là 8760 giờ trong một năm nhuận.

MASE – Sai số tuyệt đối có tỷ lệ trung bình

MASE đo độ chính xác dự báo so với phương pháp dự báo đơn giản nhất. Lấy bằng statistic_type = 5.

=FORECAST.ETS.STAT(B2:B29, A2:A29, 5)

MASE nhỏ hơn 1 nghĩa là mô hình ETS tốt hơn phương pháp naive forecast. MASE lớn hơn 1 cho biết mô hình phức tạp không cải thiện được gì so với dự báo đơn giản.

Trong kinh nghiệm thực tế, MASE dưới 0.5 là tuyệt vời. MASE từ 0.5 đến 1.0 vẫn chấp nhận được. MASE trên 1.5 thường báo hiệu nên xem xét lại cách xử lý dữ liệu hoặc chọn mô hình khác. Đây là chỉ số tôi ưu tiên kiểm tra đầu tiên khi đánh giá chất lượng dự báo.

SMAPE – Sai số phần trăm tuyệt đối đối xứng

SMAPE tính sai số theo phần trăm, giúp so sánh dễ dàng giữa các tập dữ liệu có đơn vị khác nhau. Dùng statistic_type = 6.

=FORECAST.ETS.STAT(B2:B29, A2:A29, 6)

SMAPE trả về giá trị từ 0 đến 200, với 0 là dự báo hoàn hảo. Giá trị dưới 10 phần trăm coi là dự báo rất tốt. Từ 10 đến 20 phần trăm vẫn hữu dụng. Trên 50 phần trăm thì mô hình có vấn đề nghiêm trọng.

Ưu điểm của SMAPE là đối xứng – xử lý sai số quá cao và quá thấp công bằng. Tôi thường báo cáo SMAPE cho ban quản lý vì dễ hiểu hơn các chỉ số khác. Doanh số dự báo sai 8 phần trăm nghe trực quan hơn MASE 0.45.

MAE – Sai số tuyệt đối trung bình

MAE tính trung bình sai số tuyệt đối giữa giá trị dự báo và thực tế. Lấy bằng statistic_type = 7.

=FORECAST.ETS.STAT(B2:B29, A2:A29, 7)

MAE trả về giá trị có cùng đơn vị với dữ liệu gốc. Nếu dự báo doanh số tính bằng triệu đồng, MAE cũng tính bằng triệu đồng. Điều này giúp đánh giá trực tiếp mức độ sai lệch.

XEM THÊM: Hàm SKEW.P Trong Excel: Cách Tính Độ Xiên Chính Xác Cho Toàn Bộ Dữ Liệu

MAE có ưu điểm dễ hiểu nhưng khó so sánh giữa các tập dữ liệu có quy mô khác nhau. Tôi dùng MAE kết hợp với SMAPE để có cả góc nhìn tuyệt đối lẫn tương đối về độ chính xác.

RMSE – Căn bậc hai của sai số bình phương trung bình

RMSE phạt nặng những sai số lớn hơn các sai số nhỏ. Lấy bằng statistic_type = 8.

=FORECAST.ETS.STAT(B2:B29, A2:A29, 8)

RMSE luôn lớn hơn hoặc bằng MAE. Nếu RMSE gần bằng MAE nghĩa là sai số phân bố đều. Nếu RMSE lớn hơn MAE nhiều, có những điểm dự báo sai lệch nghiêm trọng cần chú ý.

Khi xây dựng mô hình dự báo cho tài chính hoặc sản xuất, tôi ưu tiên giảm RMSE thay vì MAE. Một vài lần dự báo sai lớn gây hậu quả nặng nề hơn nhiều lần sai nhỏ.

Kích thước bước được phát hiện

Chỉ số cuối cùng cho biết khoảng cách giữa các điểm dữ liệu trong timeline. Không nằm trong danh sách 8 statistic types chính thức nhưng một số tài liệu Microsoft đề cập.

Excel yêu cầu timeline có bước đều đặn. Với dữ liệu ngày, bước thường là 1. Với dữ liệu tháng, bước là 30 hoặc 31 ngày tùy tháng. Nếu Excel không phát hiện được bước nhất quán, hàm trả về lỗi NUM.

Kiểm tra step size giúp xác nhận Excel đang đọc timeline đúng cách. Tôi từng gặp trường hợp dữ liệu tháng bị thiếu một vài điểm giữa chừng, Excel tính sai chu kỳ và cho kết quả dự báo lệch hoàn toàn.

Áp dụng vào quy trình dự báo thực tế

Thay vì chạy riêng lẻ từng statistic type, tạo bảng dashboard với tất cả 8 chỉ số. Đặt các ô chứa FORECAST.ETS.STAT với statistic_type từ 1 đến 8 cùng một vùng. Khi dữ liệu cập nhật, toàn bộ chỉ số tự động tính lại.

Tôi thiết lập ngưỡng cảnh báo: MASE trên 1.0 hoặc SMAPE trên 15 phần trăm tô màu đỏ. Điều này giúp phát hiện ngay khi mô hình suy giảm chất lượng. Với dự báo dài hạn quá 12 tháng, kiểm tra lại các tham số này mỗi quý.

Hàm FORECAST.ETS.STAT khả dụng từ Excel 2016, Excel 2019, và Microsoft 365. Không hoạt động trên Excel for Web, iOS hoặc Android. Nếu file cần chia sẻ rộng rãi, cân nhắc tính trước các chỉ số và paste values thay vì để công thức động.