Cách Dùng FORECAST.ETS.SEASONALITY Để Phát Hiện Tính Thời Vụ Trong Dữ Liệu Excel

Xác định chu kỳ lặp lại trong dữ liệu có thể mất hàng giờ nếu làm thủ công. Tôi thường phải vẽ biểu đồ, quan sát pattern, đếm số điểm dữ liệu giữa các đỉnh để tìm độ dài mùa vụ. Mỗi khi data thay đổi phải lặp lại toàn bộ quy trình. Hàm FORECAST.ETS.SEASONALITY trong Excel 2016 tự động phát hiện chu kỳ này trong vài giây.

Mục Lục

Hàm FORECAST.ETS.SEASONALITY Làm Gì

Hàm này trả về độ dài của chu kỳ lặp lại mà Excel phát hiện trong chuỗi dữ liệu theo thời gian. Khác với việc bạn tự đoán hoặc thử từng giá trị, hàm sử dụng thuật toán Exponential Triple Smoothing để phân tích pattern tự động.

Cú pháp đầy đủ:

=FORECAST.ETS.SEASONALITY(values, timeline, [data_completion], [aggregation])

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

values: Phạm vi chứa dữ liệu lịch sử (doanh số, nhiệt độ, lưu lượng truy cập)
timeline: Phạm vi chứa các mốc thời gian tương ứng (ngày, tháng, quý)

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

data_completion: 0 hoặc 1 (mặc định 1) – Cách xử lý điểm dữ liệu thiếu
aggregation: 1-7 – Phương pháp tổng hợp khi có nhiều giá trị cùng timestamp

Hàm trả về một số nguyên biểu thị độ dài chu kỳ theo đơn vị thời gian trong timeline. Nếu không phát hiện pattern nào, kết quả là 0.

Ví Dụ Với Dữ Liệu Doanh Số Tháng

Giả sử bạn có doanh số 24 tháng trong cột B (B2:B25) và ngày tháng tương ứng trong cột A (A2:A25). Dữ liệu cho thấy doanh số tăng mỗi mùa hè và giảm mỗi mùa đông.

XEM THÊM: Tôi Mất 30 Phút Tính Xác Suất Chuẩn Cho Đến Khi Phát Hiện Hàm GAUSS

Công thức cơ bản:

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

Kết quả trả về: 12

Con số 12 có nghĩa dữ liệu có chu kỳ lặp lại 12 tháng, tức pattern theo năm. Nếu data là theo quý, kết quả sẽ là 4 (4 quý trong một năm). Với data theo tuần, có thể là 52.

Điểm quan trọng về timeline:

Các mốc thời gian phải có khoảng cách đều đặn (daily, monthly, quarterly, yearly)
Không cần sắp xếp theo thứ tự – Excel tự động sort khi tính toán
Nếu có khoảng cách không đồng đều, hàm trả về lỗi NUM

Kết Hợp Với FORECAST.ETS Để Dự Báo

Giá trị chính của hàm này là xác định seasonality tự động cho FORECAST.ETS. Thay vì đoán chu kỳ là 12 hay 4, bạn để Excel phát hiện.

Cách làm thực tế:

Tìm seasonality tự động:

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

Dùng kết quả trong FORECAST.ETS:

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

Tham số thứ 4 là 1 nghĩa “tự động phát hiện seasonality”. Excel sử dụng cùng thuật toán như FORECAST.ETS.SEASONALITY, nên kết quả nhất quán.

Tôi thường dùng FORECAST.ETS.SEASONALITY riêng để kiểm tra độ dài chu kỳ trước khi chạy dự báo chính thức. Nếu kết quả là 0, data không có pattern rõ ràng và nên dùng phương pháp linear thay vì ETS.

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

Excel hỗ trợ tới 30% dữ liệu thiếu và tự động điều chỉnh. Tham số data_completion kiểm soát cách xử lý:

Giá trị 1 (mặc định):

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

Điểm thiếu được tính bằng trung bình của các điểm liền kề (linear interpolation).

Giá trị 0:

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

Điểm thiếu được coi là số 0.

Trong thực tế, giá trị 1 cho kết quả chính xác hơn với hầu hết dữ liệu kinh doanh. Chỉ dùng 0 khi điểm thiếu thực sự nghĩa là không có hoạt động (ví dụ: cửa hàng đóng cửa những ngày đó).

XEM THÊM: Hàm PERMUT Giúp Tôi Tính Xác Suất Xổ Số Nhanh Gấp 10 Lần

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

Khi timeline có nhiều giá trị cùng timestamp, tham số aggregation quyết định cách gộp chúng:

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

Ví dụ với SUM:

=FORECAST.ETS.SEASONALITY(B2:B100, A2:A100, 1, 7)

Điều này hữu ích khi có nhiều giao dịch trong cùng một ngày và bạn muốn tổng doanh thu thay vì trung bình.

Các Lỗi Thường Gặp Và Cách Fix

Lỗi NUM:

Nguyên nhân: Timeline không có khoảng cách đều đặn
Cách fix: Kiểm tra các ngày trong cột timeline có missing hoặc duplicate không. Sử dụng conditional formatting để highlight duplicates: Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

Lỗi VALUE:

Nguyên nhân: Timeline có giá trị trùng lặp
Cách fix: Remove duplicates bằng Data > Remove Duplicates, chọn cột timeline

Lỗi N/A:

Nguyên nhân: Phạm vi values và timeline không cùng kích thước
Cách fix: Đảm bảo B2:B25 và A2:A25 có cùng số hàng (24 rows)

Kết quả 0:

Nguyên nhân: Không có pattern lặp lại rõ ràng trong data
Giải pháp: Dữ liệu có thể là linear trend thay vì seasonal. Dùng FORECAST.LINEAR thay vì FORECAST.ETS

Tôi gặp lỗi NUM nhiều nhất với dữ liệu được export từ database có timestamp giờ phút giây. Solution là round các timestamp về đầu ngày bằng INT() hoặc DATE() function.

Use Cases Thực Tế

Doanh số bán lẻ: Dữ liệu tháng thường có seasonality 12. Retail store experience tăng mùa lễ (tháng 11-12) và giảm sau Tết (tháng 2-3).

XEM THÊM: Hàm COUNT Trong Excel Chỉ Đếm Số - Đây Là Lý Do Kết Quả Của Tôi Cứ Ra 0

Website traffic: Dữ liệu theo giờ có thể có seasonality 24 (pattern 24 giờ) hoặc 168 (pattern 7 ngày). Traffic cao giờ hành chính và thấp ban đêm.

Nhiệt độ: Dữ liệu ngày có seasonality 365 (annual cycle). Nếu data theo tháng thì seasonality là 12.

Doanh thu quý: Với 8 quý dữ liệu (2 năm), kết quả có thể là 4, nghĩa pattern lặp lại mỗi năm (4 quý).

Giới Hạn Cần Biết

Seasonality tối đa được hỗ trợ là 8,760 (số giờ trong một năm). Giá trị lớn hơn sẽ trả về lỗi NUM. Điều này có nghĩa:

Data theo giờ: Maximum pattern là 1 năm
Data theo ngày: Maximum pattern là 24 năm
Data theo tháng: Maximum pattern là 730 năm (không thực tế)

Hàm này không khả dụng trong Excel for Web, iOS, hoặc Android. Chỉ có trong Excel 2016 và các phiên bản mới hơn cho Windows và Mac.

Để kết quả chính xác, cần ít nhất 2 chu kỳ đầy đủ. Với seasonality 12, cần tối thiểu 24 điểm dữ liệu. Với seasonality 4, cần ít nhất 8 điểm.

Khi Nào Nên Dùng Hàm Này

Dùng FORECAST.ETS.SEASONALITY khi bạn nghi ngờ data có pattern lặp lại nhưng không chắc độ dài chu kỳ. Hàm này đặc biệt hữu ích khi:

Làm việc với data mới lần đầu
Có nhiều chu kỳ khả thi (weekly, monthly, quarterly)
Cần verify seasonality trước khi build forecast model
Muốn automate reporting process mà không hard-code giá trị seasonality

Nếu bạn đã biết chắc pattern (ví dụ: doanh số luôn theo chu kỳ 12 tháng), có thể skip hàm này và nhập trực tiếp giá trị 12 vào FORECAST.ETS. Tuy nhiên, việc để Excel tự phát hiện giúp catch những pattern không ngờ tới.

Hàm yêu cầu Excel 2016 trở lên. Với Excel 2013 hoặc cũ hơn, bạn cần tính toán seasonality thủ công bằng cách vẽ biểu đồ và đếm các điểm giữa các đỉnh lặp lại.