--- title: "Dự báo mùa vụ trong Excel: chỉ số mùa vụ, xu hướng, MAPE" description: "Năm bước dự báo doanh số theo mùa trong Excel: chỉ số mùa vụ, xu hướng tuyến tính, MAPE. Công thức đã chạy thử và so với cách dự báo đơn giản." url: "https://staging.khoi.co.uk/blog/du-bao-mua-vu-excel-mape" lang: vi author: "Lê Quốc Khôi" date: 2026-11-03 updated: 2026-11-03 topics: ["Dữ liệu & phân tích"] tags: ["Dự báo", "Mùa vụ", "Excel", "MAPE", "Chuỗi thời gian"] --- # Dự báo doanh số theo mùa trong Excel và chấm bằng MAPE ## Tóm tắt nhanh - Doanh số có mùa vụ và xu hướng dự báo được bằng năm bước: tính chỉ số mùa vụ, bỏ mùa vụ khỏi số liệu, ước lượng xu hướng tuyến tính, nhân lại chỉ số, chấm bằng MAPE. Phần phân rã cổ điển và các thước đo sai số theo (Hyndman và Athanasopoulos, 2021), mục 3.4, 5.7 và 5.8. - Trên bộ số liệu giả định 36 tháng, dự báo 12 tháng kế tiếp có MAPE 6,6%, còn cách "tháng này năm ngoái" có MAPE 13,3%. - Cùng mô hình đó có MAPE chỉ 2,6% trên chính 36 tháng đã dùng để dựng nó. Sai số trong mẫu thấp hơn hẳn sai số trên dữ liệu mới, nên phải chấm trên phần giữ lại. - Cách đơn giản "tháng này năm ngoái nhân mức tăng của năm ngoái" đạt 6,7%, gần bằng năm bước. Phương pháp chỉ đáng dùng khi thắng những cách như vậy trên phần giữ lại. - Doanh số trong bài do tôi tạo bằng chương trình, không phải số liệu của doanh nghiệp nào. Một cửa hàng quạt và máy làm mát ở Cần Thơ phải chốt đơn nhập cho 12 tháng tới. Tháng 5 bán nhiều hơn hẳn tháng 1, khoảng 70% theo chỉ số mùa vụ ở dưới, và doanh số cả năm vẫn đang tăng. Chủ cửa hàng nên nhập bao nhiêu máy cho từng tháng, và sai số bao nhiêu thì chấp nhận được? Bài này làm từng bước phương pháp trong [Dự báo theo chỉ số mùa vụ và xu hướng](https://staging.khoi.co.uk/framework/seasonal-trend-forecast) bằng Excel. Cửa hàng là ví dụ giả định, số liệu do tôi sinh bằng chương trình. Nếu doanh số của bạn bị một cú sốc như đại dịch thì xem [bài về Starbucks](https://staging.khoi.co.uk/blog/starbucks-du-bao-giua-covid), vì khi đó lịch sử không còn dùng để suy ra tương lai. ## Bộ số liệu giả định gồm những gì? Gồm 48 tháng doanh số, tính bằng số máy bán ra. Tôi dùng 36 tháng đầu để dựng mô hình và giữ 12 tháng cuối làm phần chấm điểm, chiếm 25% mẫu. Hyndman và Athanasopoulos (2021, mục 5.8) nêu phần giữ lại thường khoảng 20% mẫu, tuỳ độ dài mẫu và tầm dự báo, và lý tưởng là dài ít nhất bằng tầm dự báo xa nhất cần dùng (Hyndman và Athanasopoulos, 2021). Cách tạo: mỗi tháng bằng đường xu hướng 200 + 3t (t là số thứ tự tháng), nhân một chỉ số mùa vụ tôi đặt trước, nhân thêm nhiễu ngẫu nhiên với độ lệch chuẩn 4%, rồi làm tròn. Tôi biết đáp án thật, nên sau này có thể hỏi phương pháp tìm lại đáp án đó tốt đến đâu. Với số liệu thật, bạn không có lợi thế này. ``` Tháng 1–12 (năm 1): 173 172 230 280 281 262 221 198 213 200 204 188 Tháng 13–24 (năm 2): 195 204 267 304 346 306 260 232 252 227 238 235 Tháng 25–36 (năm 3): 220 223 304 335 404 342 284 293 251 262 252 240 Tháng 37–48 (năm 4): 237 283 373 411 451 388 336 306 281 267 312 300 ``` Dán 36 số đầu vào C2:C37. Mười hai số cuối là doanh số thật của năm 4, chỉ dùng để chấm điểm, đặt ở J38:J49. ## Bước 1: tách chỉ số mùa vụ thế nào? Theo phân rã nhân cổ điển, lấy [trung bình trượt](https://staging.khoi.co.uk/thuat-ngu#moving-average-smoothing) tâm 2×12 làm xu hướng, chia doanh số thật cho nó, rồi lấy trung bình các tỷ lệ cùng tháng (Hyndman và Athanasopoulos, 2021). Sách ghi các chỉ số này được chuẩn hoá để tổng bằng 12 (mục 3.4). Với chu kỳ chẵn như 12 tháng, sách dùng 2×m: trung bình 12 tháng, rồi trung bình hai lần liền nhau để kết quả đối xứng quanh một tháng. Chọn độ dài khác thì xu hướng thường còn lẫn mùa vụ (mục 3.3). Trong Excel, đặt cột A là số thứ tự tháng, B là tháng trong năm (1 đến 12), C là doanh số; A và B kéo xuống hết dòng 49: ``` D8: =(C2/2+SUM(C3:C13)+C14/2)/12 kéo xuống D31 E8: =C8/D8 kéo xuống E31 N2: =AVERAGEIFS($E$2:$E$37,$B$2:$B$37,M2,$E$2:$E$37,">0") M2:M13 là 1…12 O2: =N2*12/SUM($N$2:$N$13) kéo xuống O13 F2: =INDEX($O$2:$O$13,B2) kéo xuống F49 ``` Trung bình trượt chỉ có từ tháng 7 đến tháng 30, vì cần sáu tháng ở mỗi phía. Vì thế mỗi tháng trong năm chỉ có hai tỷ lệ. Điều kiện ">0" trong AVERAGEIFS bỏ qua các ô trống. Kết quả: | Tháng | Chỉ số ước lượng | Chỉ số tôi đặt khi tạo dữ liệu | |:--|--:|--:| | 1 | 0,81 | 0,80 | | 2 | 0,82 | 0,85 | | 3 | 1,09 | 1,10 | | 4 | 1,21 | 1,30 | | 5 | 1,41 | 1,35 | | 6 | 1,21 | 1,20 | | 7 | 1,01 | 1,00 | | 8 | 0,90 | 0,95 | | 9 | 0,96 | 0,90 | | 10 | 0,87 | 0,85 | | 11 | 0,89 | 0,90 | | 12 | 0,83 | 0,80 | Tháng 5 cao hơn mức trung bình khoảng 41%, tháng 1 thấp hơn khoảng 19%. Tháng 4 lệch nhiều nhất: 1,21 so với 1,30. Hai tỷ lệ mỗi tháng không đủ để nhiễu 4% triệt tiêu nhau. Tôi chọn mô hình nhân vì biên độ mùa vụ tăng theo mức doanh số: chênh lệch giữa tháng cao nhất và thấp nhất là 109 máy ở năm 1 và 184 máy ở năm 3. ## Bước 2 và 3: bỏ mùa vụ, rồi kẻ đường xu hướng ra sao? Chia doanh số từng tháng cho chỉ số của tháng đó, rồi hồi quy tuyến tính doanh số đã bỏ mùa vụ theo số thứ tự tháng. Hyndman và Athanasopoulos (2021, mục 5.7) mô tả việc dự báo hai phần riêng: phần mùa vụ và phần đã bỏ mùa vụ. Với phần sau, sách cho phép dùng bất kỳ phương pháp không mùa vụ nào, ví dụ phương pháp drift, Holt hay ARIMA không mùa vụ (Hyndman và Athanasopoulos, 2021). Đường thẳng bình phương tối thiểu là lựa chọn của tôi, khớp với bước hai của framework trong thư viện. ``` G2: =C2/F2 kéo xuống G37 a: =INTERCEPT($G$2:$G$37,$A$2:$A$37) cho 204,16 b: =SLOPE($G$2:$G$37,$A$2:$A$37) cho 2,653 ``` Đường xu hướng là 204,16 + 2,653 × t, tức mức nền tăng khoảng 2,65 máy mỗi tháng. Đường thật tôi đặt là 200 + 3t. Chênh lệch này đến từ nhiễu và từ các chỉ số ước lượng còn lệch. ## Bước 4: dự báo 12 tháng kế tiếp và so với thực tế? Dự báo tháng t bằng (a + b × t) × chỉ số của tháng đó. Sách ghi phần mùa vụ của tương lai thường lấy bằng phần của năm cuối, và chỉ số cố định theo tháng làm đúng việc này. ``` I38: =(INTERCEPT($G$2:$G$37,$A$2:$A$37)+SLOPE($G$2:$G$37,$A$2:$A$37)*A38)*F38 kéo xuống I49 K38: =ABS(J38-I38)/J38 kéo xuống K49 ``` | Tháng (năm 4) | Dự báo | Thực tế | Sai số tuyệt đối | |:--|--:|--:|--:| | 1 | 245 | 237 | 3,4% | | 2 | 251 | 283 | 11,2% | | 3 | 335 | 373 | 10,2% | | 4 | 375 | 411 | 8,7% | | 5 | 440 | 451 | 2,5% | | 6 | 381 | 388 | 1,9% | | 7 | 322 | 336 | 4,3% | | 8 | 287 | 306 | 6,1% | | 9 | 310 | 281 | 10,3% | | 10 | 284 | 267 | 6,4% | | 11 | 292 | 312 | 6,5% | | 12 | 276 | 300 | 8,0% | Tổng dự báo là 3.798 máy so với 3.945 máy thực tế, thấp hơn 3,7%. Dự báo thấp hơn thực tế ở 9 tháng trong 12. ## MAPE bằng bao nhiêu, và so với cái gì thì mới có nghĩa? [MAPE](https://staging.khoi.co.uk/thuat-ngu#mape) là trung bình của sai số tuyệt đối chia cho doanh số thực, ở đây bằng 6,6%: `=AVERAGE(K38:K49)`. Một con số riêng lẻ chưa nói gì. Sách dùng các phương pháp đơn giản làm mốc so sánh, trong đó "seasonal naïve" lấy giá trị cùng tháng của năm trước làm dự báo (Hyndman và Athanasopoulos, 2021). Tôi thêm một mốc nữa của riêng mình: cùng tháng năm trước nhân mức tăng doanh số cả năm 3 so với năm 2 (11,2%). | Phương pháp | MAPE trên 12 tháng giữ lại | |:--|--:| | Tháng này năm ngoái | 13,3% | | Tháng này năm ngoái × (1 + 11,2%) | 6,7% | | Chỉ số mùa vụ + xu hướng tuyến tính (năm bước) | 6,6% | Hai kết quả cuối gần như hoà. Năm bước chỉ đáng công khi thắng cách làm vài giây này, và ở đây nó thắng rất ít. Cách thứ hai không cần mô hình nào, nhưng nó lấy cả năm 3 làm nền nên mang theo nhiễu của đúng năm đó. Chấm mô hình trên chính dữ liệu đã dựng nó cho MAPE 2,6%, thấp hơn 2,5 lần. Hyndman và Athanasopoulos (2021, mục 5.8) viết rằng độ lớn của phần dư không phản ánh đáng tin cậy sai số dự báo thật, và mô hình khớp tốt dữ liệu cũ chưa chắc dự báo tốt (Hyndman và Athanasopoulos, 2021). Kết quả của tôi khớp với nhận xét đó. Hai điều MAPE không nói: - Mức sàn. Không dự báo nào đưa MAPE về 0. Với số liệu thật bạn không biết sàn nằm đâu. Với bộ số liệu giả, tôi tính được: dùng đúng cả chỉ số lẫn xu hướng tôi đã đặt, MAPE trên 12 tháng này vẫn là 4,4%, do nhiễu tôi thêm vào. - Hướng và chi phí của sai số. Dự báo thấp hơn thực tế ở 9 tháng trong 12 nghĩa là thiếu hàng ở những tháng đó nếu chủ cửa hàng nhập đúng theo con số. Mất doanh số vì hết hàng và vốn nằm trong kho là hai khoản khác nhau, xem [chi phí sai lệch bất đối xứng](https://staging.khoi.co.uk/thuat-ngu#asymmetric-error-cost). Sách nêu sai số phần trăm phạt dự báo quá cao nặng hơn dự báo quá thấp, nhưng MAPE vẫn không phân biệt chi phí thật của hai hướng. ## Khi nào cách này không hợp? Khi lịch sử ngắn, khi mùa vụ đổi hình, và khi có cú sốc. Cụ thể: - Dưới hai năm dữ liệu thì có tháng không có tỷ lệ nào để lấy trung bình (phép tính của tôi với trung bình trượt 2×12), và 36 tháng như bài chỉ cho hai tỷ lệ mỗi tháng. Bảng chỉ số ở trên cho thấy hậu quả. - Phân rã cổ điển giả định mùa vụ lặp lại như nhau mỗi năm. Sách nêu ví dụ nhu cầu điện, đỉnh dịch từ mùa đông sang mùa hè khi máy lạnh phổ biến. Nó cũng không chống chịu được các giá trị bất thường như tháng có tranh chấp lao động, và sách ghi rằng dù còn được dùng rộng rãi, phương pháp này không được khuyến nghị vì nay đã có nhiều phương pháp tốt hơn hẳn (Hyndman và Athanasopoulos, 2021). Theo suy luận của tôi, một đợt khuyến mãi lớn trong năm 2 sẽ đẩy chỉ số của tháng đó lên. - Xu hướng thẳng đến mãi mãi là giả định mạnh. Nếu thị trường bão hoà, đường thẳng nhân với chỉ số sẽ cho số quá cao. Dự báo cuốn chiếu cập nhật đều hơn, xem [Chu trình dự báo cuốn chiếu](https://staging.khoi.co.uk/framework/rolling-forecast-cycle). - Quyết định nhập hàng khó rút lại cần khoảng dự báo thay cho một con số, như trong [Một con số hay nhiều kịch bản?](https://staging.khoi.co.uk/blog/lap-kich-ban-cho-chu-doanh-nghiep). Chọn phương pháp theo [Chọn phương pháp dự báo](https://staging.khoi.co.uk/framework/forecasting-method-selection). ## Đào sâu: tôi đã đọc gì, kiểm gì, chưa làm gì? Tôi đọc các đoạn liên quan trong mục 3.1 đến 3.4 (biến đổi, thành phần, trung bình trượt, phân rã cổ điển), 5.2, 5.7 và 5.8 của Hyndman và Athanasopoulos (2021), bản trực tuyến; tôi bỏ qua các đoạn mã R. Tôi chưa đọc chương 7 về hồi quy, nơi sách trình bày xu hướng tuyến tính, và chưa đọc các bài gốc về MAPE mà sách dẫn. Cách đặt năm bước thành một quy trình là của framework trong thư viện, không phải một công thức nguyên văn trong sách. Việc chọn đường thẳng bình phương tối thiểu cho phần xu hướng và mốc "nhân mức tăng năm ngoái" là của tôi. Tôi tính toàn bộ bằng Python và cho chạy các công thức trên trong LibreOffice Calc, hai nơi cho cùng kết quả. Tôi chưa chạy trong Microsoft Excel. ## Câu hỏi thường gặp ### Cần bao nhiêu tháng dữ liệu để tính chỉ số mùa vụ? Với chu kỳ 12 tháng và trung bình trượt tâm 2×12, 24 tháng chỉ cho 12 giá trị xu hướng, tức mỗi tháng đúng một tỷ lệ để lấy trung bình. Con số này là phép tính của tôi, không phải ngưỡng do sách nêu. Với 36 tháng như bài, mỗi chỉ số dựa trên hai tỷ lệ và vẫn lệch đáng kể so với chỉ số tôi đặt khi tạo dữ liệu. Càng nhiều năm thì chỉ số càng ổn định, miễn là mùa vụ không đổi hình dạng. ### Nên dùng mô hình cộng hay mô hình nhân? Theo Hyndman và Athanasopoulos (2021), dùng mô hình cộng khi biên độ mùa vụ không đổi theo mức của chuỗi, và mô hình nhân khi biên độ tỷ lệ với mức đó (Hyndman và Athanasopoulos, 2021). Trong bộ số liệu của bài, chênh lệch giữa tháng cao nhất và thấp nhất tăng từ 109 máy ở năm 1 lên 184 máy ở năm 3, nên tôi dùng mô hình nhân. ### MAPE hỏng khi nào? Khi có tháng doanh số bằng không thì MAPE không xác định, và khi doanh số rất nhỏ thì sai số phần trăm vọt lên rất lớn. Sách còn nêu sai số phần trăm phạt sai số âm nặng hơn sai số dương, ở đây nghĩa là dự báo cao hơn thực tế bị phạt nặng hơn dự báo thấp hơn thực tế (Hyndman và Athanasopoulos, 2021). Với mặt hàng có tháng bán bằng không, sai số tuyệt đối trung bình (MAE) cùng đơn vị với doanh số là lựa chọn dễ hiểu hơn. ### Tết rơi vào tháng khác nhau mỗi năm thì chỉ số mùa vụ có còn đúng không? Chỉ số theo tháng dương lịch sẽ pha trộn Tết vào hai tháng khác nhau qua các năm. Sách khuyên loại biến động lịch trước khi phân rã, ví dụ dùng doanh số bình quân mỗi ngày bán thay cho tổng tháng (Hyndman và Athanasopoulos, 2021). Việc Tết gây ra hiệu ứng này là suy luận của tôi, sách không nói về Tết. Với hàng bán mạnh quanh Tết, nên xử lý tuần Tết riêng. ## Tài liệu tham khảo - Hyndman, R.J. and Athanasopoulos, G. (2021) *Forecasting: Principles and Practice, 3rd edn*. Available at: https://otexts.com/fpp3/ (Accessed: 2026-11-03). --- Bản HTML chuẩn: https://staging.khoi.co.uk/blog/du-bao-mua-vu-excel-mape