Cách thực hiện Phân tích hồi quy trong Excel
Hồi quy là một Công cụ phân tích, chúng tôi sử dụng để phân tích một lượng lớn dữ liệu và đưa ra các dự báo và dự đoán trong Microsoft Excel.
Bạn muốn dự đoán tương lai? Không, chúng ta sẽ không học chiêm tinh. Chúng ta đang nghiên cứu các con số và chúng ta sẽ học phân tích hồi quy trong Excel hôm nay.
Để dự đoán các ước tính trong tương lai, chúng ta sẽ nghiên cứu:
Hãy làm điều đó …
Tình huống:
Giả sử bạn bán nước giải khát. Sẽ tuyệt biết bao nếu bạn có thể dự đoán:
-
Có bao nhiêu nước giải khát sẽ được bán trong năm tới dựa trên số liệu của năm trước?
-
Những lĩnh vực nào cần tập trung?
-
Và làm thế nào bạn có thể tăng doanh số bán hàng bằng cách thay đổi chiến lược của mình?
Nó sẽ rất tuyệt vời. Đúng không?… Tôi biết. Vậy hãy bắt đầu.
Bạn có 11 hồ sơ về nhân viên bán hàng và nước ngọt đã bán.
Bây giờ, dựa trên dữ liệu này, bạn muốn dự đoán số lượng nhân viên bán hàng cần thiết để đạt được 2000 doanh số bán nước giải khát.
Phương trình hồi quy là một công cụ để thực hiện các ước tính gần như vậy. Để làm được như vậy, trước tiên chúng ta cần biết về Regression.
PHÂN TÍCH ĐỊA ĐIỂM SỬ DỤNG CÁC CHỨC NĂNG EXCEL (TÌM KIẾM ĐỊA CHỈ HƯỚNG DẪN)
Phần này sẽ giúp bạn hiểu rõ hơn về hồi quy hơn là chỉ nói về thủ tục hồi quy trong excel.
Giới thiệu:
Hồi quy tuyến tính đơn giản:
Việc nghiên cứu mối quan hệ giữa hai biến được gọi là hồi quy tuyến tính đơn giản. Trường hợp một biến phụ thuộc vào biến độc lập khác. Biến phụ thuộc thường được gọi bằng các tên như Biến định hướng, Đáp ứng và Biến mục tiêu. Và biến độc lập thường được phát âm như là một Lái xe, Dự đoán hoặc đơn giản là Biến độc lập.
Những cái tên này mô tả rõ ràng chúng.
Bây giờ, hãy so sánh điều này với kịch bản của bạn. Bạn muốn biết số lượng nhân viên bán hàng cần thiết để đạt được 2000 doanh số. Vì vậy, ở đây, biến phụ thuộc là số lượng người bán hàng và biến độc lập là nước giải khát được bán.
Biến độc lập chủ yếu được ký hiệu là x và biến phụ thuộc là y. Trong trường hợp của chúng ta, nước giải khát được bán là x và số người bán hàng là y.
Nếu chúng ta muốn biết sẽ bán được bao nhiêu nước giải khát nếu chúng ta chỉ định 200 nhân viên bán hàng, thì kịch bản sẽ ngược lại.
Tiếp tục.
Toán học “Đơn giản” của phương trình hồi quy tuyến tính:
Chà, nó không đơn giản. Nhưng Excel đã làm cho điều đó trở nên đơn giản.
Chúng ta cần dự đoán số lượng nhân viên bán hàng cần cho cả 11 trường hợp để có dự đoán gần nhất thứ 12.
Giả sử:
Nước ngọt đã bán là x
Số lượng nhân viên bán hàng là y
Dự đoán y (số lượng nhân viên bán hàng) còn được gọi là Phương trình hồi quy, sẽ là
|
* (relax, I’ve got it covered) |
Bây giờ bạn phải tự hỏi nơi stat bạn sẽ nhận được độ dốc và điểm giao cắt. Đừng lo lắng, excel có các chức năng dành cho chúng. Bạn không cần phải học cách tìm độ dốc và đánh chặn nó bằng tay.
Nếu bạn muốn, tôi sẽ chuẩn bị một hướng dẫn riêng cho điều đó. Hãy cho tôi biết trong phần ý kiến. Đây là một số công cụ phân tích dữ liệu quan trọng.
Bây giờ chúng ta hãy bước vào tính toán của chúng tôi:
Bước 1: Chuẩn bị bàn nhỏ này
Bước 2: Tìm độ dốc của đường hồi quy
Hàm Excel cho sườn là
|
=SLOPE(known_y’s,known_x’s) |
Known_y’s của bạn nằm trong dải B2: B12 và known_x’s nằm trong dải C2: C12
Trong ô B16, viết công thức dưới đây
|
=SLOPE(B2:B12, C2:C12) |
(_Lưu ý: Độ dốc còn được gọi là hệ số của x trong phương trình hồi quy) _ Bạn sẽ nhận được 0,058409. Làm tròn đến 2 chữ số thập phân và bạn sẽ nhận được 0,06.
Bước 3: Tìm điểm giao nhau của đường hồi quy
Hàm Excel cho chặn là
|
=INTERCEPT(known_y’s, known_x’s)* |
Chúng ta biết những gì đã biết về x và y của chúng ta Trong ô B17, hãy viết ra công thức này
|
=INTERCEPT(B2:B12, C2:C12)* |
Bạn sẽ nhận được giá trị -1.1118969. Làm tròn đến 2 chữ số thập phân. Bạn sẽ nhận được -1.11. Phương trình hồi quy tuyến tính của chúng tôi là = x0.06 + (-1.11). Bây giờ chúng ta có thể dự đoán y khả thi tùy thuộc vào mục tiêu x một cách dễ dàng. Bước 4: * Trong D2 viết công thức dưới đây
|
=C2*$B$16+$B$17 (Regression Equation) |
Bạn sẽ nhận được giá trị là 13,55.
Chọn D2 đến D13 và nhấn CTRL + D để điền công thức trong phạm vi D2: D13
Trong ô D13, bạn có số lượng nhân viên bán hàng cần thiết.
|
Hence, to achieve the target of 2000 Soft Drink Sales, you need an estimate of 115.71 salesmen or say 116 since it is illegal to cut humans into pieces. |
Bây giờ bằng cách sử dụng này, bạn có thể dễ dàng tiến hành phân tích What-If trong excel. Chỉ cần thay đổi số lượng bán hàng và nó sẽ cho bạn thấy nhiều người bán hàng sẽ phải mất để đạt được mục tiêu bán hàng đó.
Chơi xung quanh nó để tìm hiểu:
Bạn cần bao nhiêu nhân lực để tăng doanh số bán hàng?
Bao nhiêu doanh số sẽ tăng nếu bạn tăng nhân viên bán hàng của bạn?
Làm cho ước tính của bạn đáng tin cậy hơn:
Bây giờ bạn biết rằng bạn cần 116 nhân viên bán hàng để hoàn thành 2000 doanh số.
Trong phân tích, không có gì chỉ được nói và tin. Bạn phải đưa ra phần trăm độ tin cậy trên ước tính của mình. Nó giống như đưa ra một chứng chỉ về phương trình của bạn.