Mỗi cuối tháng, tôi phải nộp báo cáo dự báo doanh số cho quý tiếp theo. Trước đây, tôi mất 2 giờ để tính toán thủ công, vẽ biểu đồ, rồi ước lượng bằng mắt. Giờ đây, hàm TREND trong Excel làm tất cả chỉ trong 30 giây với độ chính xác cao hơn.

TREND tính toán xu hướng tuyến tính tự động

Hàm TREND là công cụ thống kê trong Excel sử dụng phương pháp hồi quy tuyến tính để dự đoán giá trị tương lai. Thay vì bạn phải vẽ đường xu hướng bằng tay hoặc ước lượng dựa vào cảm tính, TREND phân tích dữ liệu đã có và tính toán đường thẳng phù hợp nhất theo công thức y = mx + b. Excel sử dụng phương pháp bình phương nhỏ nhất để tìm đường thẳng có tổng khoảng cách vuông tới các điểm dữ liệu là nhỏ nhất.

Ưu điểm lớn nhất là hàm này trả về một mảng giá trị thay vì chỉ một số đơn lẻ. Nghĩa là bạn có thể dự đoán doanh số cho 3, 6 hoặc 12 tháng tiếp theo chỉ bằng một công thức duy nhất. Dữ liệu tự động cập nhật khi bạn thay đổi số liệu gốc, không cần tính lại thủ công.

Cú pháp và bốn tham số của TREND

Cú pháp đầy đủ của hàm TREND như sau:

=TREND(known_y's, [known_x's], [new_x's], [const])

known_y’s là tham số bắt buộc, chứa các giá trị phụ thuộc đã biết. Ví dụ nếu bạn dự đoán doanh số, đây là cột chứa doanh số các tháng trước từ B3 đến B11. Mảng này phải là các giá trị số, không chứa chữ hoặc ô trống. Nếu known_y’s chứa ô trống hoặc chữ, Excel trả về lỗi #VALUE!.

known_x’s là tham số tùy chọn, chứa các giá trị độc lập tương ứng với known_y’s. Thường là cột thời gian như tháng 1, 2, 3 trong cột A3 đến A11. Nếu bỏ qua tham số này, Excel tự động gán giá trị {1, 2, 3, …} có độ dài bằng known_y’s. Hai mảng known_y’s và known_x’s phải có số lượng phần tử giống nhau, nếu không Excel báo lỗi #N/A.

new_x’s là tham số tùy chọn, chứa các giá trị x mới mà bạn muốn dự đoán. Nếu bạn muốn dự đoán tháng 10, 11, 12 thì nhập các giá trị này vào A12:A14. Bỏ qua tham số này sẽ dùng lại known_x’s làm giá trị mặc định.

const là tham số logic tùy chọn. Giá trị TRUE hoặc bỏ qua thì hằng số b trong công thức y = mx + b được tính bình thường. Giá trị FALSE thì b bị đặt bằng 0, công thức trở thành y = mx. Hầu hết trường hợp nên để TRUE hoặc bỏ qua để có kết quả chính xác nhất.

Dự đoán doanh số ba tháng với ví dụ thực tế

Giả sử bạn có dữ liệu doanh số từ tháng 1 đến tháng 9 trong năm 2024, được lưu trong cột B3 đến B11. Cột A3 đến A11 chứa số thứ tự tháng từ 1 đến 9. Bây giờ bạn cần dự đoán doanh số cho tháng 10, 11, 12.

Bước 1: Chuẩn bị dữ liệu cho tháng mới

Nhập số thứ tự tháng mới vào cột A12, A13, A14 với giá trị 10, 11, 12. Đây là các giá trị x mới mà bạn muốn dự đoán. Đảm bảo các ô này chứa số, không phải chữ. Ô số có căn lề phải, ô chữ căn lề trái.

Bước 2: Nhập công thức TREND cho Excel 365

Trong Excel 365 hoặc Excel 2021, nhấp vào ô B12 và nhập công thức:

=TREND(B3:B11, A3:A11, A12:A14)

Nhấn Enter. Excel tự động điền kết quả cho cả 3 ô B12, B13, B14 với doanh số dự đoán cho 3 tháng tiếp theo. Đây gọi là công thức mảng động, tính năng mới của Excel 365 giúp công thức tự động mở rộng sang các ô liền kề.

Bước 3: Xử lý với Excel 2016 hoặc cũ hơn

Nếu dùng Excel 2016 trở về trước, phải sử dụng công thức mảng theo cách cũ. Chọn trước vùng B12:B14 bằng cách nhấp B12 rồi kéo xuống B14. Nhập công thức như trên, sau đó nhấn Ctrl + Shift + Enter thay vì chỉ Enter. Excel sẽ tự động thêm dấu ngoặc nhọn {=TREND(…)} bao quanh công thức để đánh dấu đây là công thức mảng.

Tôi thường thấy kết quả dự đoán chênh lệch thực tế khoảng 8-12% nếu xu hướng ổn định. Con số này chấp nhận được cho việc lập kế hoạch kinh doanh quý tới, tốt hơn nhiều so với ước lượng bằng mắt thường sai 25-40%.

Ba điều quan trọng khi sử dụng TREND

Dữ liệu phải có xu hướng tuyến tính rõ ràng

TREND chỉ hiệu quả khi dữ liệu thực tế có xu hướng tăng hoặc giảm đều. Nếu doanh số nhảy múa không theo quy luật, kết quả dự đoán sai lệch lớn. Hãy vẽ biểu đồ phân tán của dữ liệu hiện có trước để kiểm tra. Cách nhanh nhất là chọn hai cột dữ liệu, nhấn Alt + F1 để tạo biểu đồ ngay lập tức. Nếu các điểm dữ liệu xếp gần thành đường thẳng thì phù hợp dùng TREND. Nếu các điểm tạo thành đường cong hoặc hình sin thì không nên dùng.

Không dùng TREND cho dữ liệu có tính mùa vụ

Doanh số bán hàng thường tăng đột biến vào dịp lễ Tết, giảm vào tháng ế ẩm. Đây là dữ liệu có tính mùa vụ hoặc chu kỳ. TREND không tính được yếu tố này vì nó chỉ vẽ đường thẳng đơn giản. Trong trường hợp này, dùng hàm FORECAST.ETS thay thế, hàm này được thiết kế riêng cho dữ liệu có chu kỳ lặp lại theo thời gian. FORECAST.ETS có sẵn từ Excel 2016 trở đi.

Kiểm tra độ chính xác bằng dữ liệu cũ

Trước khi tin tưởng kết quả dự đoán, hãy thử nghiệm lại. Lấy dữ liệu từ tháng 1 đến tháng 6, dùng TREND dự đoán tháng 7, 8, 9. So sánh giá trị dự đoán với doanh số thực tế đã biết. Nếu sai lệch dưới 15% thì công thức đáng tin. Nếu sai quá 25%, có thể dữ liệu không phù hợp với mô hình tuyến tính hoặc cần thêm điểm dữ liệu. Tôi thường cần ít nhất 8 điểm dữ liệu để có kết quả ổn định.

Độ chính xác tăng theo số lượng dữ liệu

Hàm TREND hoạt động tốt nhất với dữ liệu từ 8-12 tháng trở lên. Càng nhiều điểm dữ liệu, đường xu hướng càng chính xác. Tôi thường dùng dữ liệu 9 tháng gần nhất để dự đoán 3 tháng tiếp theo, độ chính xác đạt 88-92% khi thị trường ổn định. Hàm này tương thích với Excel 2016 trở đi, bao gồm cả Excel Online. Google Sheets cũng có hàm TREND với cú pháp tương tự.