Cách dự đoán kết quả bằng hàm FORECAST của Excel

Học cách dự đoán kết quả trong Excel có thể tạo ra sự khác biệt khi đưa ra quyết định chiến lược cho các công ty ở mọi quy mô. Phân tích dữ liệu và khả năng dự đoán xu hướng hoặc nhu cầu trong tương lai là rất cần thiết, và Excel cung cấp nhiều chức năng chuyên biệt giúp việc dự báo trở nên đơn giản và hiệu quả.

Hàm FORECAST trong Excel và các biến thể của nó cho phép bạn dự đoán các giá trị trong tương lai. sử dụng dữ liệu lịch sử, đặc biệt hữu ích cho việc dự báo doanh số, quản lý hàng tồn kho hoặc dự đoán các hành vi tiêu dùng khác nhau. Nếu bạn từng tự hỏi làm thế nào để tận dụng tối đa dữ liệu trong Excel, bài viết này chính là những gì bạn cần để thành thạo các hàm dự báo và áp dụng chúng một cách dễ dàng vào bảng tính của mình.

Dự báo trong Excel có nghĩa là gì?

El término dự báo Trong Excel, thuật ngữ này được dịch là "dự báo" và dùng để ước tính các giá trị tương lai dựa trên thông tin quá khứ. Trong bối cảnh kinh doanh, thuật ngữ này thường được sử dụng để dự báo doanh số, tính toán nhu cầu hàng tồn kho, lên lịch sản xuất hoặc phân tích xu hướng thị trường. Ví dụ, dự báo doanh số cho phép bạn dự đoán số lượng hàng bán được trong một khoảng thời gian nhất định., dữ liệu quan trọng để đưa ra quyết định ảnh hưởng trực tiếp đến thành công kinh tế của bất kỳ doanh nghiệp nào.

Thông thường, dự báo được thực hiện định kỳ (hàng tháng, hàng tuần hoặc hàng năm). Dữ liệu lịch sử đáng tin cậy và có tổ chức là điều cần thiết để đưa ra dự báo chính xác, vì tất cả các công cụ toán học này đều dựa trên số liệu thống kê được áp dụng vào hồ sơ của bạn.

Các loại dự báo áp dụng trong Excel

Không có một kỹ thuật dự báo duy nhất; tùy thuộc vào thông tin có sẵn và độ chính xác mong muốn, Excel cho phép bạn áp dụng các mô hình dự báo khác nhau:

• Dự báo định tính: Phương pháp này được sử dụng khi không có đủ thông tin lịch sử. Các công ty có thể sử dụng khảo sát, ý kiến chuyên gia hoặc phân tích thị trường để có được ước tính.
• Dự báo dựa trên chuỗi thời gian: Phân tích dữ liệu lịch sử của công ty bằng các kỹ thuật như đường trung bình động, làm mịn theo hàm mũ hoặc các mô hình nâng cao như Box-Jenkins.
• Dự báo dựa trên mô hình nhân quả: Sử dụng các biến cụ thể ảnh hưởng đến quy mô mà chúng ta muốn dự đoán, chẳng hạn như hồi quy tuyến tính liên quan đến hai hoặc nhiều yếu tố chính.

Các hàm dự báo chính trong Excel

Excel bao gồm một số hàm để tự động tạo dự báo, bao gồm:

• DỰ BÁO.TUYẾN TÍNH (o DỰ BÁO TUYẾN TÍNH): Dự đoán giá trị tương lai bằng hồi quy tuyến tính đơn giản. Phương pháp này lý tưởng cho các mối quan hệ tuyến tính giữa hai tập dữ liệu (ví dụ: doanh số theo thời gian).
• DỰ BÁO.ETS (o DỰ BÁO.ETS): Đưa ra dự đoán bằng thuật toán làm mịn ba lần theo hàm mũ (ETS), được điều chỉnh cho dữ liệu có mô hình theo mùa hoặc biến động định kỳ.

Cả hai chức năng đều cho phép bạn dự đoán kết quả trong tương lai dựa trên dữ liệu lịch sử và là lựa chọn phù hợp cho bất kỳ người dùng nào muốn nâng cao khả năng phân tích dữ liệu mà không cần dùng đến phần mềm thống kê tiên tiến.

Cú pháp và đối số của hàm dự đoán

DỰ BÁO TUYẾN TÍNH và DỰ BÁO TUYẾN TÍNH

Cú pháp như sau:

DỰ BÁO. TUYẾN TÍNH(x, known_y, known_x)

Sau đây là những lập luận chính:

• x: Giá trị mà bạn muốn dự đoán kết quả (ví dụ: ngày hoặc chỉ số trong tương lai).
• đã biết và: Phạm vi dữ liệu phụ thuộc hoặc giá trị đã được ghi lại (ví dụ: doanh số bán hàng lịch sử).
• đã biết_x: Phạm vi dữ liệu độc lập hoặc biến ảnh hưởng (ví dụ: el tiempo hoặc giá thành đơn vị).

Tất cả các đối số đều bắt buộc. Nếu bất kỳ giá trị nào trong số chúng trống hoặc có số điểm khác nhau, công thức sẽ trả về lỗi. Ngoài ra, nếu phương sai của dữ liệu trong đã biết_x nếu bằng 0 thì kết quả sẽ là lỗi (#DIV/XNUMX!).

DỰ BÁO.ETS hoặc PRONOSTICO.ETS

Hàm này được khuyến nghị khi dữ liệu có tính thời vụ. Cú pháp của hàm này là:

DỰ BÁO.ETS(ngày_mục_tiêu, giá_trị, thang_thời_gian, , , )

• ngày đến: Ngày, giờ hoặc thời điểm trong tương lai cần dự đoán.
• Valores: Chuỗi giá trị lịch sử.
• thang thời gian: Ngày tháng hoặc chỉ số lịch sử; phải có các bước thường xuyên.
• tính thời vụ (tùy chọn): Số biểu thị tần suất lặp lại của mẫu. Nếu bỏ qua, Excel sẽ tự động phát hiện.
• điền dữ liệu (tùy chọn): Kiểm soát cách xử lý dữ liệu bị thiếu (0 để coi là số không, 1 để nội suy).
• tổng hợp (tùy chọn): Phương pháp kết hợp các giá trị có cùng ngày (mặc định là trung bình).

Cách tạo dự báo trong Excel từng bước

1. Chuẩn bị dữ liệu của bạn: Nhập hai chuỗi, một chuỗi theo thang thời gian (ngày hoặc chỉ số thông thường) và chuỗi còn lại theo giá trị lịch sử.
2. Chọn cả hai loạt và đi đến tab Dữ liệu > Dự báo > Bảng dự báo.
3. Cấu hình biểu đồ: Chọn kiểu hình ảnh (dòng hoặc cột) để hiển thị kết quả.
4. Chọn ngày kết thúc để dự báo của bạn và nhấp vào Tạo.
5. Excel sẽ tự động tạo một trang tính mới với bảng dữ liệu lịch sử, các giá trị dự đoán và biểu đồ.

Ngoài ra, từ cửa sổ cấu hình bạn có thể điều chỉnh các tùy chọn nâng cao chẳng hạn như khoảng tin cậy, tính thời vụ, phương pháp tổng hợp hoặc cách xử lý dữ liệu bị thiếu.

Tùy chỉnh dự báo: tùy chọn nâng cao

Excel không chỉ đơn thuần là một dự đoán: nó cho phép bạn tùy chỉnh nhiều thông số khác nhau để điều chỉnh dự báo cho phù hợp với nhu cầu cụ thể của từng trường hợp. Một số tùy chọn đáng chú ý nhất bao gồm:

• Bắt đầu và kết thúc dự báo: Chọn nơi bắt đầu ước tính và phạm vi bạn muốn dự tính.
• Khoảng tin cậy: Nó cho thấy phạm vi mà 95% kết quả trong tương lai dự kiến sẽ nằm trong đó. Phạm vi này càng hẹp, bạn càng chắc chắn về ước tính của mình.
• tính thời vụ: Đặt tần suất dữ liệu; nếu không điều chỉnh chính xác, dự báo có thể không phản ánh đúng thực tế.
• Xử lý dữ liệu bị thiếu: Quyết định xem các giá trị bị thiếu có được nội suy (trung bình lân cận) hay được coi là số không. Điều này rất quan trọng đối với các chuỗi có khoảng cách nhỏ.
• Tổng hợp dữ liệu trùng lặp: Nếu có nhiều mục nhập có cùng ngày hoặc dấu thời gian, Excel có thể tính trung bình, đếm, cộng hoặc sử dụng các phương pháp khác.
• Bao gồm số liệu thống kê: Bạn có thể tạo thêm các bảng với các số liệu như lỗi tuyệt đối trung bình, hệ số làm mịn hoặc R bình phương để đánh giá chất lượng dự báo.

Ứng dụng thực tế: Ví dụ dự báo đơn giản với Excel

Hãy tưởng tượng một công ty muốn biết sẽ bán được bao nhiêu sản phẩm với giá thành đơn vị mới. Để làm được điều này, công ty cần có lịch sử giá và số lượng sản phẩm đã bán:

Chi phí đơn vị	Đơn vị đã bán
120	8.000
130	10.500
140	12.100
150	14.350

Sau khi phân tích đồ thị XY (chi phí đơn vị so với doanh số), một xu hướng tuyến tính được quan sát thấy. Sử dụng hàm DỰ BÁO TUYẾN TÍNH Hoặc bằng cách thêm đường xu hướng vào biểu đồ, Excel sẽ tạo ra một công thức toán học liên hệ cả hai biến. Ví dụ: Doanh số = 102,4 x (giá thành đơn vị) – 191,64.

Nếu bạn muốn ước tính doanh số bán hàng cho một đơn giá là 150 €, công thức sẽ là: 102,4 x 150 – 191,64 = 15.168 đơn vị.

Để xác thực tính chắc chắn của dự đoán:

• Hãy nhìn vào biểu đồ: Nếu các điểm gần với đường xu hướng thì có mối tương quan tốt.
• sử dụng chức năng ĐÚNG: Nếu kết quả gần bằng 1, mối quan hệ tìm thấy là mạnh; nếu gần bằng 0, mối quan hệ yếu và dự đoán có thể không đáng tin cậy.

Trong trường hợp có mối quan hệ không tuyến tính, Excel cũng cho phép áp dụng mô hình hàm mũ hoặc các mô hình khác thông qua tiện ích bổ sung Analysis Toolpak.

Dự báo nâng cao: Bộ công cụ phân tích và làm mịn theo hàm mũ

Có những tình huống mà mối quan hệ tuyến tính đơn giản là không đủ và cần áp dụng các phương pháp phức tạp hơn, chẳng hạn như làm mịn theo cấp số nhânVí dụ, một số quy trình kinh tế hoặc dữ liệu tăng trưởng thường tuân theo các kiểu mẫu này. Excel giúp việc này trở nên dễ dàng bằng cách sử dụng Phân tích ToolpakĐể cài đặt, chỉ cần vào Excel Add-ins và kích hoạt; từ đó, bạn sẽ có quyền truy cập vào các công cụ như hồi quy tuyến tính, làm mịn và phân tích phương sai.

Mô-đun phân tích dữ liệu cho phép bạn thu thập:

• Hồi quy tuyến tính và hàm mũ tự động với các báo cáo chi tiết.
• Hệ số xác định (R bình phương) để xác định xem dự đoán có phù hợp với dữ liệu hay không.
• Chẩn đoán nâng cao trên độ tin cậy của các ước tính, để phát hiện các lỗi hoặc điểm yếu có thể có trong mô hình được sử dụng.

Tất cả những điều này giúp tự động hóa quá trình so sánh các phương pháp dự báo khác nhau, cho phép bạn lựa chọn phương pháp phù hợp nhất dựa trên bản chất của dữ liệu.

Các hàm bổ sung và tham chiếu trong Excel

Excel không chỉ cho phép bạn dự báo các giá trị cụ thể mà còn tạo ra toàn bộ bảng tính với kết quả dự kiến, biểu đồ minh họa và các bảng bổ sung, đặc biệt là bằng cách sử dụng các tùy chọn dự báo tự động. Bạn cũng có thể tìm hiểu sâu hơn về các công thức được sử dụng, phân tích các thuật toán làm mịn hàm mũ (ETS) và tham khảo tài liệu để mở rộng khả năng phân tích của mình.

Nếu bạn có dữ liệu lịch sử theo một chu kỳ rõ ràng, Hàm FORECAST.ETS cung cấp ước tính thực tế hơn trong bối cảnh có các đỉnh thường xuyên (ví dụ: doanh số hàng tháng bị ảnh hưởng bởi mùa vụ). Các tham số nâng cao bao gồm tự động phát hiện tính thời vụ, quản lý dữ liệu bị thiếu thông qua nội suy và tùy chỉnh phương pháp tổng hợp cho các ngày có nhiều bản ghi.