Forecast Sheet: Dự Báo Dữ Liệu Tự Động Trong Excel

Forecast Sheet là công cụ dự báo có sẵn trong Excel, dùng dữ liệu quá khứ để ước tính giá trị tương lai. Chỉ vài cú bấm là ra một biểu đồ dự báo kèm khoảng tin cậy. Công cụ này có từ Excel 2016 trở lên.

Dữ liệu cần chuẩn bị

Forecast Sheet đòi hỏi đúng hai cột:

• Một cột mốc thời gian, phải là kiểu ngày tháng thật và cách đều nhau. Ngày, tuần, tháng đều được, miễn là đều.
• Một cột giá trị tương ứng.

Hai yêu cầu hay bị bỏ qua: cột thời gian không được thiếu mốc nào ở giữa, và phải là kiểu ngày chứ không phải văn bản. Nếu cột ngày đang ở dạng văn bản, chọn cột rồi vào Data → Text to Columns, bấm Finish để ép Excel nhận lại kiểu dữ liệu.

Về lượng dữ liệu: càng nhiều chu kỳ càng tốt. Muốn dự báo theo mùa vụ thì cần ít nhất hai chu kỳ đầy đủ, tức là hai năm dữ liệu tháng nếu mùa vụ theo năm.

Tạo dự báo

1. Chọn cả hai cột dữ liệu.
2. Vào Data → Forecast Sheet.
3. Cửa sổ xem trước hiện ra với biểu đồ dự báo. Chọn ngày kết thúc dự báo ở ô phía dưới.
4. Bấm Create. Excel tạo một sheet mới chứa bảng dự báo và biểu đồ.

Kết quả gồm bốn cột: mốc thời gian, giá trị thực tế, giá trị dự báo, và hai cột giới hạn tin cậy trên dưới.

Các tùy chọn nên chỉnh

Bấm Options trong cửa sổ tạo dự báo để mở phần thiết lập.

Khoảng tin cậy. Mặc định 95%. Giảm xuống 80% thì dải dự báo hẹp lại nhưng khả năng sai cao hơn. Đừng tắt hẳn khoảng tin cậy, vì một đường dự báo đơn độc tạo cảm giác chắc chắn hơn thực tế rất nhiều.

Tính mùa vụ. Mặc định Excel tự phát hiện. Nếu bạn biết rõ chu kỳ, ví dụ doanh số theo tháng có chu kỳ 12, hãy đặt thủ công. Excel đôi khi phát hiện sai chu kỳ và cho dự báo lệch hẳn.

Xử lý ô thiếu. Chọn nội suy thay vì coi là số 0, trừ khi giá trị 0 là đúng thật.

Gộp giá trị trùng mốc. Nếu dữ liệu có nhiều dòng cùng một ngày, chọn cách gộp là tổng hay trung bình.

Hiểu đúng kết quả

Đây là phần quan trọng hơn cả thao tác. Forecast Sheet dùng phương pháp làm mượt theo cấp số nhân, tức là nó kéo dài xu hướng và mùa vụ trong quá khứ ra tương lai.

Nghĩa là nó không biết gì về những thứ nằm ngoài dữ liệu: một chiến dịch khuyến mãi sắp chạy, một đối thủ mới vào thị trường, một thay đổi chính sách. Dự báo của nó chỉ đúng nếu tương lai vận hành giống quá khứ.

Vì vậy nên coi kết quả là một điểm tham chiếu để thảo luận, không phải con số để cam kết. Và luôn nhìn vào dải tin cậy chứ không chỉ nhìn đường giữa: dải rộng nghĩa là dữ liệu quá nhiễu để dự báo đáng tin.

Dùng hàm thay vì công cụ

Nếu bạn muốn dự báo nằm ngay trong bảng tính thay vì ở sheet riêng, dùng hàm:

=FORECAST.ETS(mốc_cần_dự_báo, vùng_giá_trị, vùng_thời_gian)

Hàm này cho kết quả giống công cụ. Kèm theo có FORECAST.ETS.CONFINT để tính khoảng tin cậy và FORECAST.ETS.SEASONALITY để xem Excel phát hiện chu kỳ bằng bao nhiêu.

Cách này tiện khi bạn cần đưa số dự báo vào một bảng theo dõi, xem tạo dashboard trong Excel.

Khi dữ liệu chưa đủ chuẩn

Nếu dữ liệu của bạn nằm rải rác ở nhiều file hoặc có mốc thời gian không đều, nên dọn bằng Power Query trước khi dự báo, xem Power Query trong Excel.

Công cụ Forecast Sheet có từ key Office 2016 trở lên.

Câu hỏi thường gặp

Nút Forecast Sheet bị mờ?

Do chưa chọn đúng hai cột, hoặc cột thời gian không phải kiểu ngày tháng.

Dự báo ra đường thẳng không có mùa vụ?

Dữ liệu chưa đủ dài để Excel phát hiện chu kỳ. Cần ít nhất hai chu kỳ đầy đủ.

Dự báo cho ra số âm dù thực tế không thể âm?

Công cụ không biết ràng buộc thực tế. Bạn tự chặn lại bằng hàm MAX với 0 khi dùng kết quả.