MỘT SỐ DÒNG LỆNH EXCEL TRONG CÔNG TÁC DỰ BÁO THEO CÁC MÔ HÌNH HÔI QUY

Như chúng ta biết các mô hình hồi quy có ý nghĩa thiết thực trọng công tác dự báo của các doanh nghiệp, chẳng hạn như dự báo về nhu cầu, dự báo doanh thu, dự báo lợi nhuận, … Sau đây chúng ta đề cập đến các dòng lệnh excel rất hữu ích và nên được sử dụng rộng rãi trong quá trình dự báo theo các mô hình hồi quy khác nhau gồm: Mô hình hồi quy đơn (Linear Regression Model); Mô hình hồi quy bậc hai (Quadratic Regression Model); Mô hình hồi quy bậc ba (Cubic Regression Model) và mô hình hồi quy mũ (Exponential Regerssion Model). Lệnh INTERCEPT và SLOPE Lệnh INTERCEPTvà SLOPE cung cấp một cách khác để đánh giá hệ số chặn và độ dốc của đường thẳng thích hợp nhất với dữ liệu. Cú pháp cho các lệnh này là: INTERCEPT (phạm vi của biến phụ thuộc, phạm vi của biến độc lập) SLOPE (phạm vi của biến phụ thuộc, phạm vi của biến độc lập). Lệnh CORREL của excel và hệ số tương quan và hệ số xác định CORREL(array1;array2) Bạn có thể hiểu ý nghĩa của các hệ số tốt hơn nếu bạn nhớ lại việc sử dụng các số liệu thống kê mẫu như ước tính số liệu thống kê dân số. Trong sự vắng mặt của bất kỳ mô hình phù hợp trong một tập hợp các giá trị mẫu, giá trị trung bình của mẫu là ước lượng tốt nhất có thể làm cho một giá trị tương lai của dân số mà từ đó lấy mẫu. Sự thay đổi của các giá trị mẫu về giá trị trung bình của mẫu được thể hiện bằng độ lệch chuẩn của mẫu. Mặt khác, nếu có một xu hướng nhất quán trong các giá trị của mẫu, chúng ta có thể sử dụng xu hướng này để phát triển một mô hình (ví dụ, một phương trình hồi quy) cung cấp cho một ước lượng tốt hơn của một giá trị trong tương lai hơn mức trung bình đơn giản của quá khứ . Các giá trị tốt hơn, các giá trị dự đoán với mô hình. Bây giờ chúng ta có thể so sánh độ lệch chuẩn của các giá trị dữ liệu về các giá trị dự báo với độ lệch chuẩn của các giá trị dữ liệu về trung bình. Hoặc, thuận tiện hơn, chúng ta có thể sử dụng độ lệch chuẩn trong việc so sánh. (Nhớ lại rằng phương sai chỉ đơn giản là bình của độ lệch chuẩn). Lệnh LINEST của Excel Phần lớn các thông tin cung cấp ở trên bằng cách sử dụng các chức năng lệnh riêng biệt có thể thu được một cách dễ dàng hơn bằng cách sử dụng lệnh LINEST Excel, cung cấp thông tin thống kê bổ sung (một số trong đó người đọc có thể bỏ qua một cách an toàn hoặc tham khảo sau này). Cú pháp của lệnh là: LINEST (phạm vi của các giá trị y đã biết, phạm vi của các giá trị x, const, stats) Các mục const là TRUE hoặc FALSE, tùy thuộc vào hệ số chặn là phải được tính toán bình thường, như chúng ta đã thực hiện trong các trước, hoặc được thiết lập bằng số không, với giá trị của b được điều chỉnh để phù hợp với phương trình y = bx. Giá trị 1 có thể được thay thế cho TRUE, và nếu một mục nhập cho const được bỏ qua, b được tính bình thường. Các mục cho các số liệu thống kê là TRUE (hoặc 1) nếu các số liệu thống kê hồi quy bổ sung mô tả dưới đây được mong muốn. Nếu mục nhập cho các số liệu thống kê là FALSE (hoặc bỏ qua), giá trị duy nhất số chặn và độ dốc được trả về.   Đối với hồi quy đơn: y = ax + b Để sử dụng lệnh LINEST, đầu tiên chọn một khối 2 cột 5 dòng  bằng cách kéo chuột qua chúng. Sau đó, gõ lệnh LINEST và nhập vào bằng cách nhấn ba phím "Ctrl", "Shift" và "Enter" . Kết quả là tập hợp của các giá trị được hiển thị trong các ô của khối đã quét chọn. Định dạng đầu ra của lệnh LINEST Excel cho hồi quy tuyến tính đơn với một biến phụ thuộc. <table align="center" border="1" cellpadding="0" cellspacing="0"> <tbody> <tr> <td> Độ dốc </td> <td> Hệ số chặn </td> </tr> <tr> <td> Sai số cho độ dốc Hệ số xác định bội (R2) Thống kê F Tổng bình phương sai số ( RSS) </td> <td> Sai số cho hệ số chặn Sai số chuẩn của ước lượng ( SEE) Bậc tự do Tổng các bình phương phần dư </td> </tr> </tbody> </table> <div style="clear:both;">  </div> Đối với hồi quy bậc hai (Quadratic regresstion model): y = ax2 + bx + c Đối với mô hình hồi quy bậc hai, đầu tiên chọn một khối 3 cột 5 dòng  bằng cách kéo chuột qua chúng. Sau đó, gõ lệnh LINEST và nhập vào bằng cách nhấn ba phím "Ctrl", "Shift" và "Enter" . Kết quả là tập hợp của các giá trị được hiển thị trong các ô của khối đã quét chọn như sau: <table align="center" border="1" cellpadding="0" cellspacing="0" style="width:627px;" width="627"> <tbody> <tr> <td style="width:225px;"> Hệ số với x2 </td> <td style="width:248px;"> Hệ số với x </td> <td style="width:155px;"> Hệ số chặn </td> </tr> <tr> <td style="width:225px;"> Sai số cho Hệ số với x2 Hệ số xác định bội (R2) Thống kê F Tổng bình phương sai số ( RSS) </td> <td style="width:248px;"> Sai số cho Hệ số với x Sai số chuẩn của ước lượng ( SEE) Bậc tự do Tổng các bình phương phần dư </td> <td style="width:155px;"> Sai số cho hệ số chặn   </td> </tr> </tbody> </table> <div style="clear:both;">  </div>   Đối với hồi quy bậc ba (Cubic regresstion model): y = ax3 + bx2 + cx + d Đối với mô hình hồi quy bậc hai, đầu tiên chọn một khối 4 cột 5 dòng  bằng cách kéo chuột qua chúng. Sau đó, gõ lệnh LINEST và nhập vào bằng cách nhấn ba phím "Ctrl", "Shift" và "Enter" . Kết quả là tập hợp của các giá trị được hiển thị trong các ô của khối đã quét chọn như sau: <table align="center" border="1" cellpadding="0" cellspacing="0" style="width:679px;" width="679"> <tbody> <tr> <td style="width:173px;"> Hệ số với x3 </td> <td style="width:197px;"> Hệ số với x2 </td> <td style="width:155px;"> Hệ số với x </td> <td style="width:155px;"> Hệ số chặn </td> </tr> <tr> <td style="width:173px;"> Sai số cho Hệ số với x3 Hệ số xác định bội (R2) Thống kê F RSS </td> <td style="width:197px;"> Sai số cho Hệ số với x2 SEE Bậc tự do Tổng bình phương phần dư </td> <td style="width:155px;"> Sai số cho hệ số với x   </td> <td style="width:155px;"> Sai số cho hệ số chặn   </td> </tr> </tbody> </table> <div style="clear:both;">  </div> Lệnh LOGEST trong excel Sử dụng lệnh LOGEST để xác định các tham số của hàm hồi quy mũ (Exponential regresstion model):Y = ABX, theo đó hai tham số A, B sẽ được xác định. Cú pháp: LOGEST (Vùng của các giá trị y đã biết; Vùng của các giá trị x;1;1) Với các dòng lệnh nên trên chúng ta sẽ sử dụng một cách phù hợp với các mô hình dự báo hồi quy để có kết quả một cách nhanh chóng, chính xác, góp phần giúp các doanh nghiệp có sự thuận tiện trong hoạt động sản xuất kinh doanh của mình mà cụ thể là ứng dụng để dự báo các yếu tố như sản lượng tiêu thụ, doanh thu, chi phí, lợi nhuận, nhận thấy bức tranh của tương lại để có những hành động thích ứng và phù hợp nhất. Nguyễn Huy Tuân – Đại học Duy Tân

Trường Kinh Tế & Kinh Doanh - Đại học Duy Tân

Khoa Quản trị kinh doanh

MỘT SỐ DÒNG LỆNH EXCEL TRONG CÔNG TÁC DỰ BÁO THEO CÁC MÔ HÌNH HÔI QUY