Nhiều người dùng Excel 2016 và các phiên bản mới hơn thường bỏ qua hàm FORECAST.ETS.CONFINT mặc dù nó giải quyết vấn đề lớn nhất của mọi dự báo: độ chính xác. Khi bạn dự báo doanh số tháng sau là 500 triệu, con số đó chính xác đến mức nào? Hàm này trả về khoảng tin cậy cho phép bạn nói “doanh số sẽ rơi vào khoảng 450-550 triệu với độ tin cậy 95 phần trăm” thay vì đưa ra một con số duy nhất có thể sai lệch hoàn toàn.

Khoảng Tin Cậy Là Gì Và Tại Sao Quan Trọng

Khoảng tin cậy đo lường độ chính xác của dự báo bằng cách tạo ra một phạm vi giá trị thay vì một điểm duy nhất. Với mức tin cậy 95 phần trăm, có nghĩa là 95 phần trăm các điểm dữ liệu tương lai sẽ rơi vào phạm vi này so với giá trị dự báo gốc.

Trong Excel 2016, Microsoft giới thiệu bộ hàm ETS hoàn chỉnh để dự báo chuỗi thời gian. Hàm FORECAST.ETS tính toán giá trị dự báo, còn FORECAST.ETS.CONFINT tính khoảng sai số xung quanh giá trị đó. Khoảng tin cậy nhỏ hơn cho thấy độ tin cậy cao hơn vào dự báo cho điểm dữ liệu cụ thể đó.

Ví dụ thực tế: Giả sử FORECAST.ETS dự báo doanh số tháng 3 là 61.075 nghìn đồng. FORECAST.ETS.CONFINT trả về 6.441 nghìn đồng. Điều này có nghĩa là với độ tin cậy 95 phần trăm, doanh số thực tế sẽ nằm trong khoảng từ 54.634 đến 67.516 nghìn đồng (61.075 cộng trừ 6.441).

Tôi thường thấy các báo cáo chỉ hiển thị con số dự báo duy nhất mà không có khoảng tin cậy. Điều này khiến người ra quyết định không biết mức độ rủi ro thực sự. Thêm khoảng tin cậy giúp họ chuẩn bị cho nhiều kịch bản khác nhau.

Cú Pháp Và Các Tham Số

Cú pháp đầy đủ của hàm là:

=FORECAST.ETS.CONFINT(target_date, values, timeline, [confidence_level], [seasonality], [data_completion], [aggregation])

Các tham số bắt buộc:

• target_date: Ngày hoặc thời điểm bạn muốn dự báo. Phải là ngày sau ngày cuối cùng trong dữ liệu lịch sử. Nếu ngày mục tiêu đứng trước ngày cuối trong timeline, hàm trả về lỗi NUM.
• values: Mảng các giá trị lịch sử đã biết mà bạn muốn dự báo các điểm tiếp theo.
• timeline: Mảng hoặc phạm vi dữ liệu số độc lập. Các ngày trong timeline phải có bước nhảy đều đặn và không được bằng 0.

Các tham số tùy chọn:

• confidence_level: Giá trị số từ 0 đến 1 chỉ mức độ tin cậy. Ví dụ: 0.9 hoặc 90 phần trăm cho khoảng tin cậy 90 phần trăm. Giá trị mặc định là 0.95 (95 phần trăm). Nếu giá trị nằm ngoài khoảng từ 0 đến 1, hàm trả về lỗi NUM.
• seasonality: Giá trị số chỉ độ dài chu kỳ theo mùa. Giá trị mặc định là 1 nghĩa là Excel tự động phát hiện chu kỳ. Giá trị 0 nghĩa là không có chu kỳ. Các số nguyên dương chỉ độ dài chu kỳ. Giá trị tối đa hỗ trợ là 8.760 (số giờ trong một năm).
• data_completion: Cách xử lý dữ liệu bị thiếu. Giá trị 0 coi các điểm thiếu là 0. Giá trị mặc định là 1 nghĩa là hoàn thành các điểm thiếu bằng trung bình của các điểm lân cận. Hàm hỗ trợ tối đa 30 phần trăm dữ liệu bị thiếu.
• aggregation: Phương pháp tổng hợp dữ liệu khi có nhiều giá trị với cùng dấu thời gian. Giá trị mặc định là 0 (AVERAGE). Các giá trị khác là 1 (SUM), 2 (COUNT), 3 (COUNTA), 4 (MIN), 5 (MAX), 6 (MEDIAN).

Thiết Lập Dữ Liệu Cơ Bản

Trước khi sử dụng FORECAST.ETS.CONFINT, bạn cần chuẩn bị dữ liệu đúng định dạng. Timeline phải có bước nhảy đều đặn giữa các điểm dữ liệu.

Yêu cầu timeline:

• Cột ngày tháng phải có khoảng cách đều: hàng ngày, hàng tuần, hàng tháng hoặc hàng năm
• Không cần sắp xếp vì hàm tự động sắp xếp trong quá trình tính toán
• Không được có giá trị trùng lặp trong timeline
• Nếu có giá trị trùng lặp, hàm trả về lỗi VALUE

Cách kiểm tra timeline hợp lệ:

1. Chọn cột chứa ngày tháng
2. Tạo cột phụ tính khoảng cách giữa các ngày: =A3-A2
3. Kiểm tra tất cả khoảng cách có bằng nhau không
4. Nếu không đều, cần điều chỉnh hoặc bổ sung dữ liệu

Tôi từng gặp lỗi NUM vì dữ liệu có một tháng bị thiếu khiến khoảng cách không đều. Thêm hàng với giá trị trung bình giữa hai tháng liền kề đã giải quyết vấn đề.

Sử Dụng Cơ Bản Với Dự Báo Đơn Giản

Ví dụ đơn giản nhất là tính khoảng tin cậy cho một dự báo tuyến tính không có chu kỳ mùa vụ.

Dữ liệu mẫu:

• Cột A: Tháng từ 1/1/2024 đến 31/12/2024 (12 tháng)
• Cột B: Doanh số tương ứng
• Dự báo cho tháng 1/2025

Công thức tính khoảng tin cậy:

=FORECAST.ETS.CONFINT(A14, B2:B13, A2:A13)

Trong đó:

• A14 là ô chứa ngày 1/1/2025
• B2:B13 là dãy giá trị doanh số lịch sử
• A2:A13 là dãy ngày tháng tương ứng

Công thức này sử dụng các giá trị mặc định: độ tin cậy 95 phần trăm, tự động phát hiện chu kỳ, hoàn thành dữ liệu thiếu bằng trung bình.

Để tính dự báo thực tế, kết hợp với FORECAST.ETS:

Dự báo: =FORECAST.ETS(A14, B2:B13, A2:A13)
Khoảng tin cậy: =FORECAST.ETS.CONFINT(A14, B2:B13, A2:A13)
Giới hạn trên: =C14+D14
Giới hạn dưới: =C14-D14

Giả sử FORECAST.ETS trả về 925 và FORECAST.ETS.CONFINT trả về 226. Phạm vi dự báo là từ 699 đến 1.151 với độ tin cậy 95 phần trăm.

Điều Chỉnh Mức Độ Tin Cậy

Mức độ tin cậy mặc định 95 phần trăm phù hợp với hầu hết trường hợp, nhưng đôi khi bạn cần điều chỉnh tùy theo mức độ rủi ro chấp nhận được.

Mức độ tin cậy 90 phần trăm (khoảng hẹp hơn, rủi ro cao hơn):

=FORECAST.ETS.CONFINT(A14, B2:B13, A2:A13, 0.9)

Mức độ tin cậy 99 phần trăm (khoảng rộng hơn, an toàn hơn):

=FORECAST.ETS.CONFINT(A14, B2:B13, A2:A13, 0.99)

Với cùng một bộ dữ liệu:

• 90 phần trăm có thể cho khoảng tin cậy là 180 (hẹp hơn)
• 95 phần trăm cho khoảng 226 (chuẩn)
• 99 phần trăm cho khoảng 298 (rộng hơn)

Khi nào dùng mức độ tin cậy khác nhau:

• 90 phần trăm: Dự báo nội bộ, quyết định linh hoạt được
• 95 phần trăm: Báo cáo tiêu chuẩn, phần lớn trường hợp
• 99 phần trăm: Kế hoạch tài chính quan trọng, không chấp nhận sai số

Tôi thường dùng 95 phần trăm cho báo cáo hàng tháng nhưng tăng lên 99 phần trăm khi lập ngân sách năm cho các dự án lớn.

Xử Lý Dữ Liệu Có Chu Kỳ Mùa Vụ

Dữ liệu bán hàng thường có chu kỳ lặp lại theo tháng hoặc quý. FORECAST.ETS.CONFINT tự động phát hiện chu kỳ này nhưng bạn có thể chỉ định thủ công.

Để kiểm tra chu kỳ tự động phát hiện:

=FORECAST.ETS.SEASONALITY(B2:B25, A2:A25)

Nếu hàm trả về 12, nghĩa là có chu kỳ 12 tháng. Nếu trả về 1, nghĩa là không phát hiện chu kỳ rõ ràng.

Chỉ định chu kỳ thủ công:

=FORECAST.ETS.CONFINT(A26, B2:B25, A2:A25, 0.95, 12)

Tham số thứ năm là 12 nghĩa là chu kỳ 12 tháng. Điều này hữu ích khi:

• Bạn biết chắc có chu kỳ nhưng Excel không phát hiện được
• Dữ liệu lịch sử chưa đủ dài để Excel phát hiện tự động
• Muốn kiểm soát chính xác chu kỳ sử dụng

Ví dụ dữ liệu bán lẻ: Cửa hàng bán quần áo có doanh số cao vào tháng 12 (mua sắm cuối năm) và tháng 4 (mua sắm hè). Dữ liệu 24 tháng cho thấy pattern này lặp lại. Chỉ định seasonality=12 giúp dự báo chính xác hơn cho các tháng có chu kỳ cao.

Với dữ liệu có chu kỳ, khoảng tin cậy thường hẹp hơn vào các tháng có pattern ổn định và rộng hơn vào các tháng có biến động lớn.

Xử Lý Dữ Liệu Bị Thiếu

Excel cho phép tối đa 30 phần trăm dữ liệu bị thiếu và tự động điều chỉnh. Tham số data_completion kiểm soát cách xử lý.

Phương pháp mặc định (data_completion = 1):

=FORECAST.ETS.CONFINT(A26, B2:B25, A2:A25, 0.95, 1, 1)

Excel tính trung bình của các điểm lân cận để điền vào chỗ trống. Phương pháp này phù hợp khi dữ liệu thiếu ngẫu nhiên.

Coi dữ liệu thiếu là 0 (data_completion = 0):

=FORECAST.ETS.CONFINT(A26, B2:B25, A2:A25, 0.95, 1, 0)

Điều này phù hợp khi dữ liệu thiếu có nghĩa là không có giao dịch (ví dụ: cửa hàng đóng cửa tháng đó).

Lưu ý quan trọng:

• Nếu thiếu quá 30 phần trăm dữ liệu, hàm trả về lỗi NUM
• Kiểm tra số lượng ô trống: =COUNTBLANK(B2:B25)/COUNTA(B2:B25)
• Nếu kết quả lớn hơn 0.3, cần bổ sung thêm dữ liệu hoặc thu hẹp phạm vi

Tôi từng gặp lỗi này với dữ liệu xuất khẩu có nhiều tháng bị gián đoạn. Thay vì sử dụng data_completion=0, tôi đã loại bỏ các khoảng trống lớn và chỉ giữ lại dữ liệu liên tục.

Tổng Hợp Dữ Liệu Trùng Lặp

Khi có nhiều giao dịch trong cùng một ngày nhưng bạn cần dự báo theo ngày, tham số aggregation quyết định cách tổng hợp.

Các phương pháp tổng hợp:

• 0: AVERAGE (mặc định) – lấy trung bình
• 1: SUM – cộng tất cả
• 2: COUNT – đếm số lượng
• 3: COUNTA – đếm số ô không trống
• 4: MIN – lấy giá trị nhỏ nhất
• 5: MAX – lấy giá trị lớn nhất
• 6: MEDIAN – lấy trung vị

Ví dụ dữ liệu bán hàng theo giờ:

=FORECAST.ETS.CONFINT(A100, B2:B97, A2:A97, 0.95, 24, 1, 1)

Tham số cuối cùng là 1 nghĩa là cộng tất cả doanh số trong cùng một ngày. Điều này phù hợp khi bạn có dữ liệu theo giờ nhưng cần dự báo tổng doanh số ngày.

Khi nào dùng phương pháp nào:

• SUM: Dự báo tổng doanh thu từ nhiều giao dịch
• AVERAGE: Dự báo giá trị trung bình mỗi giao dịch
• MAX: Dự báo giá trị giao dịch cao nhất trong ngày
• COUNT: Dự báo số lượng giao dịch

Microsoft khuyến nghị tổng hợp dữ liệu trước khi sử dụng FORECAST.ETS.CONFINT để có kết quả chính xác hơn. Sử dụng Pivot Table hoặc hàm SUMIFS để tổng hợp trước, sau đó mới dự báo.

Tạo Biểu Đồ Với Khoảng Tin Cậy

Trực quan hóa dự báo với khoảng tin cậy giúp người xem hiểu rõ mức độ bất định.

Chuẩn bị dữ liệu cho biểu đồ:

Tạo 5 cột:

• A: Timeline (bao gồm cả lịch sử và tương lai)
• B: Giá trị lịch sử
• C: Dự báo (sử dụng FORECAST.ETS)
• D: Giới hạn trên (=C+FORECAST.ETS.CONFINT)
• E: Giới hạn dưới (=C-FORECAST.ETS.CONFINT)

Lưu ý quan trọng: Ô cuối của cột B và ô đầu của cột C nên có cùng giá trị để đảm bảo đường biểu đồ liên tục không bị gãy.

Tạo biểu đồ:

1. Chọn phạm vi A1:E30 (bao gồm cả header)
2. Insert > Charts > Line Chart > Line with Markers
3. Chọn đường Giới hạn trên và Giới hạn dưới
4. Format > Shape Fill > Pattern Fill (chọn màu nhạt)
5. Hoặc dùng Area Chart cho 3 cột C, D, E

Sử dụng Forecast Sheet tự động: Excel 2016 có công cụ tạo biểu đồ dự báo tự động:

1. Chọn dữ liệu lịch sử (2 cột: timeline và values)
2. Data tab > Forecast Sheet
3. Điều chỉnh Forecast End date
4. Chọn Confidence Interval (mặc định là 95 phần trăm)
5. Click Create

Công cụ này tự động tạo bảng tính mới với các cột dự báo và khoảng tin cậy, kèm theo biểu đồ hoàn chỉnh. Tôi sử dụng phương pháp này cho báo cáo nhanh vì tiết kiệm thời gian so với tạo thủ công.

Các Lỗi Thường Gặp Và Cách Khắc Phục

Lỗi NUM:

• Target_date đứng trước ngày cuối trong timeline: Kiểm tra lại ngày dự báo
• Không tìm thấy bước nhảy đều trong timeline: Kiểm tra khoảng cách giữa các ngày
• Confidence_level nằm ngoài khoảng 0 đến 1: Sửa lại giá trị (ví dụ: 0.95 thay vì 95)
• Seasonality nhỏ hơn 0 hoặc lớn hơn 8760: Điều chỉnh về phạm vi hợp lệ

Lỗi VALUE:

• Timeline có giá trị trùng lặp: Sử dụng Remove Duplicates hoặc aggregation
• Dữ liệu không phải số: Kiểm tra định dạng ô, chuyển text thành number

Lỗi N/A:

• Phạm vi timeline và values có kích thước khác nhau: Đảm bảo cả hai có cùng số hàng
• Ví dụ: Nếu timeline là A2:A25 thì values phải là B2:B25 (cùng 24 hàng)

Lỗi NUM do quá nhiều dữ liệu thiếu:

• Tính phần trăm thiếu: =COUNTBLANK(B2:B25)/ROWS(B2:B25)
• Nếu lớn hơn 0.3, cần giảm phạm vi hoặc bổ sung dữ liệu

Kết quả không hợp lý (khoảng tin cậy quá lớn):

• Dữ liệu có biến động lớn: Đây là phản ánh thực tế, không phải lỗi
• Seasonality không phù hợp: Thử để Excel tự động phát hiện (giá trị 1)
• Dữ liệu lịch sử quá ít: Cần ít nhất 2 chu kỳ hoàn chỉnh

Tôi thường gặp lỗi NUM với dữ liệu bán hàng có nhiều ngày nghỉ lễ. Giải pháp là loại bỏ các ngày không có giao dịch thay vì để trống hoặc điền 0.

Kết Hợp Với Các Hàm ETS Khác

FORECAST.ETS.CONFINT hoạt động tốt nhất khi kết hợp với các hàm khác trong bộ ETS.

FORECAST.ETS – Tính dự báo chính:

=FORECAST.ETS(A26, B2:B25, A2:A25, 1, 1, 1)

FORECAST.ETS.SEASONALITY – Phát hiện chu kỳ:

=FORECAST.ETS.SEASONALITY(B2:B25, A2:A25)

FORECAST.ETS.STAT – Lấy thống kê dự báo:

=FORECAST.ETS.STAT(B2:B25, A2:A25, 1)

Tham số thứ ba chỉ loại thống kê cần lấy:

• 1: Alpha (tham số làm mượt cơ bản)
• 2: Beta (tham số xu hướng)
• 3: Gamma (tham số mùa vụ)
• 4: MASE (đo độ chính xác)
• 5: SMAPE (phần trăm sai số tuyệt đối đối xứng)
• 6: MAE (sai số tuyệt đối trung bình)
• 7: RMSE (căn bậc hai sai số bình phương)
• 8: Step size (bước nhảy phát hiện)

Công thức tổng hợp trong một bảng:

Chu kỳ: =FORECAST.ETS.SEASONALITY($B$2:$B$25,$A$2:$A$25)
Dự báo: =FORECAST.ETS(A26,$B$2:$B$25,$A$2:$A$25,$D$1,1,1)
Khoảng TC: =FORECAST.ETS.CONFINT(A26,$B$2:$B$25,$A$2:$A$25,0.95,$D$1,1,1)
Trên: =B26+C26
Dưới: =B26-C26
MASE: =FORECAST.ETS.STAT($B$2:$B$25,$A$2:$A$25,4)

Trong đó D1 chứa giá trị seasonality đã tính trước. Kỹ thuật này đảm bảo tất cả các hàm sử dụng cùng một chu kỳ, giúp kết quả nhất quán.

Tương Thích Và Giới Hạn

FORECAST.ETS.CONFINT khả dụng trong:

• Excel 2016 trở lên (Windows và Mac)
• Excel 2019
• Excel 2021
• Microsoft 365 Excel
• Excel 2024

Không khả dụng trong:

• Excel for Web
• Excel for iOS
• Excel for Android
• Excel 2013 và các phiên bản cũ hơn

Nếu bạn đang dùng Excel 2013 hoặc cũ hơn, cần nâng cấp để sử dụng bộ hàm ETS. Các phiên bản cũ chỉ có hàm FORECAST cơ bản cho dự báo tuyến tính đơn giản mà không hỗ trợ chu kỳ mùa vụ hay khoảng tin cậy.

Giới hạn cần lưu ý:

• Seasonality tối đa 8760 (số giờ trong năm)
• Hỗ trợ tối đa 30 phần trăm dữ liệu thiếu
• Yêu cầu bước nhảy đều trong timeline
• Không xử lý được dữ liệu không theo chuỗi thời gian

Khi chuyển file sang Excel for Web để chia sẻ, các hàm ETS sẽ hiển thị kết quả cuối cùng nhưng không tính toán lại khi thay đổi dữ liệu. Người xem cần mở trong Excel desktop để cập nhật dự báo.

Ứng Dụng Trong Lập Kế Hoạch Kinh Doanh

Tôi thường sử dụng FORECAST.ETS.CONFINT cho ba tình huống chính trong công việc:

Dự báo doanh số với kịch bản tốt xấu: Thay vì báo cáo một con số doanh số duy nhất, tôi trình bày ba kịch bản:

• Kịch bản bi quan: Dự báo trừ khoảng tin cậy
• Kịch bản trung bình: Dự báo chính
• Kịch bản lạc quan: Dự báo cộng khoảng tin cậy

Cách tiếp cận này giúp ban lãnh đạo chuẩn bị ngân sách linh hoạt hơn.

Đánh giá độ tin cậy của dự báo tồn kho: Khoảng tin cậy lớn cho thấy dự báo không chắc chắn, cần tăng lượng tồn kho an toàn. Khoảng tin cậy nhỏ cho thấy có thể giảm tồn kho mà không rủi ro hết hàng.

So sánh độ chính xác giữa các mô hình: Tạo hai bảng dự báo: một với seasonality tự động, một với seasonality thủ công. So sánh khoảng tin cậy trung bình của cả hai. Mô hình có khoảng tin cậy nhỏ hơn thường chính xác hơn.

Sau 6 tháng sử dụng, dự báo với khoảng tin cậy giúp team giảm 15 phần trăm sai số trong kế hoạch sản xuất so với phương pháp dự báo điểm cũ.