Báo cáo dự báo doanh số hàng tháng của tôi từng mất 3 giờ để hoàn thành. Mở file doanh số cũ, tính trung bình bằng tay, vẽ biểu đồ xu hướng trong Paint, rồi đoán mò con số cho 12 tháng tới. Độ chính xác dao động 60-70%, đủ để sếp chấp nhận nhưng không đủ để lập kế hoạch. Giờ đây với hàm FORECAST.LINEAR, tôi hoàn thành cùng báo cáo trong 5 phút với độ chính xác 90%.

Dữ liệu phải được sắp xếp theo thứ tự thời gian

Sai lầm đầu tiên tôi mắc phải là dùng dữ liệu lộn xộn. Hàm FORECAST.LINEAR hoạt động dựa trên hồi quy tuyến tính, nghĩa là nó tìm đường thẳng phù hợp nhất qua các điểm dữ liệu. Nếu dữ liệu không theo trình tự thời gian, kết quả sẽ vô nghĩa.

Cách sắp xếp đúng:

1. Tạo cột A cho tháng dưới dạng số (1, 2, 3… hoặc 202401, 202402…)
2. Tạo cột B cho doanh số thực tế
3. Không được có ô trống giữa các dòng dữ liệu
4. Sắp xếp theo cột A từ cũ đến mới (không đảo ngược)

Ví dụ file doanh số 6 tháng của tôi có cột A chứa số từ 1 đến 6, cột B chứa doanh số từ 50 triệu đến 85 triệu. Đơn giản nhưng quan trọng để hàm hiểu được xu hướng tăng trưởng.

Cú pháp chỉ có 3 tham số nhưng thứ tự rất quan trọng

Hàm FORECAST.LINEAR trong Excel 2016 trở lên thay thế hàm FORECAST cũ với cú pháp giống hệt nhau.

Công thức chuẩn:

=FORECAST.LINEAR(x, known_y's, known_x's)

Giải thích từng tham số:

• x: Tháng bạn muốn dự báo (ví dụ: 7 cho tháng thứ 7)
• known_y’s: Dải ô chứa doanh số đã biết (ví dụ: B2:B7)
• known_x’s: Dải ô chứa tháng đã biết (ví dụ: A2:A7)

Công thức thực tế tôi dùng:

=FORECAST.LINEAR(7, $B$2:$B$7, $A$2:$A$7)

Dấu $ giúp cố định vùng dữ liệu khi sao chép công thức xuống dưới. Nhập công thức vào ô B8 để dự báo tháng 7, kéo xuống B19 để có dự báo cho 12 tháng tiếp theo. Mỗi lần kéo xuống, tham số x tự động tăng từ 7, 8, 9… đến 18.

Dữ liệu lịch sử càng nhiều độ chính xác càng cao

Ban đầu tôi chỉ dùng 3 tháng dữ liệu và nhận được kết quả sai lệch 40%. Excel tính toán đường xu hướng dựa trên quá ít điểm nên bất kỳ biến động nhỏ nào cũng làm sai lệch toàn bộ dự báo.

Nguyên tắc dữ liệu tối thiểu:

• 6 tháng: Độ chính xác 70-75% cho dự báo ngắn hạn
• 12 tháng: Độ chính xác 85-90% cho đa số trường hợp
• 24 tháng: Độ chính xác 90%+ nếu không có biến động lớn

Lưu ý quan trọng: Nếu doanh nghiệp có tính mùa vụ rõ rệt (tăng đột biến Tết, giảm mạnh mùa hè), 12 tháng dữ liệu là mức tối thiểu để hàm nhận diện được chu kỳ. Với 6 tháng dữ liệu, hàm sẽ cho rằng xu hướng là tuyến tính đơn thuần.

Tạo cột phụ để phân biệt dữ liệu thực và dự báo

Mẹo này giúp biểu đồ trực quan hơn nhiều. Thay vì đặt công thức FORECAST.LINEAR ngay từ ô B8, tôi tạo cột C có tên “Dự báo” và làm như sau:

Bước thiết lập cột phụ:

1. Ô C7 (dòng cuối của dữ liệu thực): Nhập =B7
2. Ô C8 (dòng đầu tiên của dự báo): Nhập =FORECAST.LINEAR(8, $B$2:$B$7, $A$2:$A$7)
3. Kéo công thức từ C8 xuống C19

Khi tạo biểu đồ đường, Excel sẽ vẽ 2 đường riêng biệt: đường xanh cho doanh số thực (cột B) và đường đỏ cho dự báo (cột C). Hai đường giao nhau tại điểm cuối của dữ liệu thực (tháng 7), giúp trực quan hoá rõ ràng phần nào là lịch sử và phần nào là dự báo.

Lỗi thường gặp và cách xử lý nhanh

Hàm FORECAST.LINEAR trả về 3 loại lỗi chính mà tôi đã gặp hết.

Lỗi #N/A: Nguyên nhân: Hai dải known_y’s và known_x’s có số lượng ô khác nhau. Ví dụ B2:B7 có 6 ô nhưng A2:A8 có 7 ô. Cách fix: Kiểm tra lại công thức, đảm bảo cả hai dải có cùng số dòng.

Lỗi #DIV/0!: Nguyên nhân: Tất cả giá trị trong known_x’s giống hệt nhau (phương sai bằng 0). Điều này xảy ra khi tôi vô tình tham chiếu sai cột, ví dụ dùng $B$2:$B$7 cho cả hai tham số. Cách fix: Xác nhận known_x’s phải là cột chứa tháng (thay đổi theo thời gian), không phải cột doanh số.

Lỗi #VALUE!: Nguyên nhân: Tham số x không phải là số. Có thể do ô chứa text như “Tháng 7” thay vì số 7. Cách fix: Chuyển cột tháng về định dạng số thuần túy, không có text.

Kiểm tra độ chính xác bằng phương pháp train-test

Thủ thuật này tôi học được từ khoa học dữ liệu nhưng áp dụng được ngay trong Excel. Thay vì dùng toàn bộ 12 tháng để dự báo, tôi chỉ dùng 10 tháng đầu làm dữ liệu huấn luyện, sau đó so sánh dự báo của hàm cho tháng 11 và 12 với doanh số thực tế đã biết.

Quy trình kiểm tra:

1. Giả sử có dữ liệu từ tháng 1-12 với doanh số thực tế
2. Nhập công thức =FORECAST.LINEAR(11, $B$2:$B$11, $A$2:$A$11) vào ô D11
3. Nhập công thức =FORECAST.LINEAR(12, $B$2:$B$11, $A$2:$A$11) vào ô D12
4. So sánh D11 với B11 và D12 với B12
5. Tính độ lệch phần trăm: =(D11-B11)/B11*100

Nếu độ lệch dưới 10%, hàm hoạt động tốt với dữ liệu của bạn. Nếu trên 20%, có thể doanh số có yếu tố phi tuyến tính (tăng đột biến hoặc giảm mạnh) mà FORECAST.LINEAR không thể dự đoán chính xác.

Kết hợp với điều kiện IF cho dự báo linh hoạt

Công thức nâng cao tôi thường dùng khi muốn chỉ hiển thị dự báo cho các tháng chưa có dữ liệu thực tế:

=IF(B8="", FORECAST.LINEAR(A8, $B$2:$B$7, $A$2:$A$7), "")

Công thức này kiểm tra nếu ô B8 trống (chưa có doanh số thực), thì tính dự báo. Nếu B8 đã có số liệu, trả về rỗng. Điều này hữu ích khi cập nhật file hàng tháng: nhập doanh số tháng mới vào cột B, dự báo tự động cập nhật cho các tháng còn lại.

Tương thích và phiên bản Excel

Hàm FORECAST.LINEAR khả dụng từ Excel 2016, Excel 2019, Excel 2021, và Microsoft 365. Phiên bản Excel 2013 trở về trước chỉ có hàm FORECAST cũ, nhưng cú pháp và kết quả giống hệt nhau.

Nếu file cần chia sẻ với người dùng Excel cũ hơn, dùng =FORECAST() thay vì =FORECAST.LINEAR() để đảm bảo tương thích. Kết quả tính toán hoàn toàn giống nhau, chỉ khác tên hàm.

Đối với dữ liệu có tính mùa vụ phức tạp, Excel 2016 trở lên cung cấp thêm hàm FORECAST.ETS sử dụng thuật toán Exponential Smoothing. Tuy nhiên, FORECAST.LINEAR đã đủ cho 80% các trường hợp dự báo doanh số thông thường với xu hướng tăng trưởng ổn định.