Xây Dựng Mô Hình ML Nhẹ Trong Excel Sử Dụng Solver & Công Thức

Excel là một công cụ mạnh mẽ đáng ngạc nhiên cho các tác vụ machine learning cơ bản. Mặc dù nó không phải là nền tảng machine learning, nhưng có thể sử dụng hiệu quả để minh họa các khái niệm ML nền tảng như hồi quy tuyến tính và logistic bằng cách sử dụng các hàm tích hợp và Solver.

Trong hướng dẫn này, chúng tôi sẽ chỉ cách xây dựng các mô hình machine learning nhẹ trong Excel sử dụng Solver và công thức.

• Hồi Quy Tuyến Tính: Dự đoán giá trị liên tục (doanh thu bán hàng, giá nhà, điểm thi, v.v.).
• Hồi Quy Logistic: Dự đoán kết quả có/không (khách hàng mua, vỡ nợ vay, chẩn đoán y tế, đỗ/trượt, v.v.).

Điều Kiện Tiên Quyết:

• Microsoft Excel (2016 trở lên khuyến nghị).
• Bật Solver Add-in.
  • Chuyển đến tab File >> chọn Options >> chọn Add-ins >> chọn Excel Add-ins.
  • Nhấp Go.

• • Chọn Solver Add-in.
  • Nhấp OK.

• Hiểu biết cơ bản về khái niệm hồi quy.

Phần 1: Mô Hình Hồi Quy Tuyến Tính

Hồi quy tuyến tính tìm đường thẳng tốt nhất để dự đoán các giá trị số liên tục qua các điểm dữ liệu. Chúng ta sẽ mô phỏng một tình huống kinh doanh đơn giản nơi chi phí quảng cáo (X) dự đoán doanh thu bán hàng (Y). Mỗi điểm dữ liệu đại diện cho một tháng dữ liệu kinh doanh.

Bước 1: Thiết Lập Dữ Liệu Mẫu

Tạo một tập dữ liệu thực tế cho thấy mối quan hệ tuyến tính rõ ràng giữa đầu vào (chi phí quảng cáo tính bằng nghìn) và đầu ra (doanh thu bán hàng tính bằng nghìn).

Mỗi hàng là dữ liệu kinh doanh một tháng. Khi chi phí quảng cáo tăng, doanh thu bán hàng cũng tăng, nhưng không hoàn hảo (có một chút ngẫu nhiên, điều này thực tế).

Bước 2: Tạo Công Thức Dự Đoán

Thiết lập các "ngòi điều khiển" toán học mà mô hình của chúng ta sẽ điều chỉnh để tìm đường thẳng tốt nhất. Trong hồi quy tuyến tính, chúng ta cần hai tham số:

• Hệ số chặn (b0): Nơi đường cắt trục Y (doanh số cơ bản với $0 quảng cáo).
• Hệ số góc (b1): Doanh số tăng bao nhiêu cho mỗi $1000 tăng chi phí quảng cáo.

Thiết lập các tham số mô hình trong các ô riêng biệt:

Tham Số Mô Hình:

Predicted Y = b0 + b1 * X

• Hệ số chặn (b0)
• Giá trị khởi tạo 0
• Hệ số góc (b1)
• Giá trị khởi tạo 1

Chúng ta sẽ sử dụng phương trình tuyến tính này để dự đoán doanh số dựa trên chi phí quảng cáo. Đây là cốt lõi của mô hình, nó lấy số tiền quảng cáo và ước tính doanh số nên là bao nhiêu.

Ý Nghĩa Toán Học:

• Nếu b0 = 0.5 và b1 = 2, thì chi $3k cho quảng cáo dự đoán: 0.5 + 2*3 = $6.5k doanh số.
• Mô hình học các giá trị tốt nhất cho b0 và b1 từ dữ liệu.

Công Thức Dự Đoán:

• Chọn một ô và chèn công thức sau.

• Kéo công thức này xuống đến F11.

Bước 4: Tính Toán Phần Dư Và Sai Số

Đo lường dự đoán của chúng ta sai như thế nào. Điều này quan trọng vì mô hình học bằng cách cố gắng tối thiểu hóa các sai số này.

• Phần dư: Sự khác biệt giữa doanh số thực tế và doanh số dự đoán cho mỗi tháng.
• Sai số bình phương: Phần dư bình phương (để làm cho tất cả sai số dương và phạt sai số lớn hơn).

Phần Dư:

• Kéo công thức xuống G11.

Sai Số Bình Phương:

• Kéo công thức xuống H11.

Bước 5: Tính Toán Các Chỉ Số Sai Số

Tạo các phép đo hiệu suất mô hình có ý nghĩa kinh doanh. Các chỉ số này giúp hiểu xem mô hình có đủ tốt cho việc sử dụng thực tế hay không.

Thiết lập các chỉ số chính trong một khu vực dành riêng:

Chỉ Số Sai Số:

• Tổng Sai Số Bình Phương (SSE): Tổng sai số trên tất cả dự đoán – càng thấp càng tốt.

• Sai Số Bình Phương Trung Bình Căn Bậc Hai (RMSE): Sai số trung bình ở đơn vị gốc ($000s) – dễ diễn giải hơn.

• R-Squared: Phần trăm biến thiên doanh số được giải thích bởi quảng cáo (0-100%, càng cao càng tốt).

=1-(K2/SUMPRODUCT((B2:B11-AVERAGE(B2:B11))^2))

• Sai Số Tuyệt Đối Trung Bình (MAE): Sai số tuyệt đối trung bình – ít nhạy cảm với giá trị ngoại lai hơn RMSE.

Bước 6: Sử Dụng Solver Để Tối Ưu Hóa Tham Số

Để Excel tự động tìm các giá trị tốt nhất cho hệ số chặn và góc để tối thiểu hóa sai số dự đoán.

• Chuyển đến tab Data >> chọn Solver.
• Set Objective: K2 (ô SSE).
• To: Min.
• By Changing Variable Cells: E3,E5 (tham số của bạn).
• Nhấp Solve.

• Nhấp OK.

• Nó thử hàng triệu tổ hợp khác nhau của b0 và b1.
• Tính tổng sai số cho mỗi tổ hợp.
• Tiếp tục điều chỉnh cho đến khi tìm thấy tổ hợp có sai số thấp nhất.
• Điều này nhanh hơn và chính xác hơn nhiều so với đoán mò.

Bước 7: Tạo Hình Ảnh Hóa

Xác thực trực quan mô hình có ý nghĩa. Hãy xem đường dự đoán đi gần hầu hết các điểm dữ liệu.

• Chọn cột Advertising và Sales.
• Chuyển đến tab Insert >> từ Charts >> chọn Scatter Plot (Biểu đồ phân tán).

• Nhấp chuột phải vào biểu đồ >> chọn Data >> chọn Add Series.
  • Series Name: Chọn ô F1.
  • Series X Values: Chọn giá trị X (ví dụ: B2:B11)
  • Series Y Values: Nhấp và chọn các giá trị dự đoán F2:F11.
• Định dạng series dự đoán thành đường.

• Đường dự đoán theo đuôi xu hướng chung của các điểm dữ liệu.
• Các điểm phân tán quanh đường (không phải tất cả ở trên hoặc dưới).
• Không có mẫu rõ ràng trong phần dư.

Phần 2: Mô Hình Hồi Quy Logistic

Hồi quy logistic dự đoán xác suất cho quyết định có/không. Khác với hồi quy tuyến tính dự đoán số chính xác, hồi quy logistic dự đoán khả năng xảy ra của một sự việc (0-100%).

Bước 1: Chuẩn Bị Dữ Liệu Phân Loại Nhị Phân

Hãy mô hình hóa hành vi mua hàng của khách hàng. Dựa trên mức thu nhập của khách hàng (X), chúng ta muốn dự đoán xem họ có mua sản phẩm cao cấp của chúng ta (1) hay không (0). Đây là điển hình cho nhắm mục tiêu marketing, chẩn đoán y tế hoặc bất kỳ quyết định nhị phân nào.

Thiết lập dữ liệu cho hồi quy logistic:

Mức thu nhập khách hàng (đơn vị $10k) và quyết định mua hàng. Chú ý khách hàng thu nhập thấp (1-5) xu hướng không mua (0), trong khi khách thu nhập cao (6-10) xu hướng mua (1). Điều này phản ánh mẫu mua hàng thực tế.

Bước 2: Tạo Công Thức Dự Đoán Logistic

Khởi Tạo Tham Số Mô Hình Logistic:

Thiết lập tham số cho hàm logistic. Khác với hồi quy tuyến tính, các tham số này hoạt động qua một biến đổi toán học phức tạp hơn (hàm sigmoid).

• Hệ số chặn (b0): Dịch ngưỡng trái hoặc phải (nơi xác suất 50% xảy ra).
• Hệ số góc (b1): Kiểm soát độ dốc của sự chuyển tiếp từ "không có khả năng" sang "có khả năng".
• Giá Trị Khởi Tạo: Chúng ta bắt đầu với các dự đoán hợp lý; Solver sẽ tối ưu hóa chúng.

Tham Số Logistic:

Probability = 1 / (1 + e^(-(b0 + b1×X)))

• Hệ số chặn (b0)
• -2 (Giá trị khởi tạo)
• Hệ số góc (b1)
• 0.5 (Giá trị khởi tạo)

Tạo Công Thức Dự Đoán Logistic:

Biến đổi tổ hợp tuyến tính thành xác suất bằng hàm sigmoid. Đây là phép toán ma thuật giữ dự đoán trong khoảng 0-1.

• Tổ Hợp Tuyến Tính: b0 + b1*X (giống hồi quy tuyến tính).
• Biến Đổi Sigmoid: 1/(1+e^(-(tổ hợp tuyến tính))) chuyển đổi bất kỳ số nào sang khoảng 0-1.
• Kết Quả: Một đường cong S mượt đại diện cho xác suất. Nếu xác suất là 0.7, có 70% cơ hội khách hàng này sẽ mua.

Dự Đoán Xác Suất:

• Tổ Hợp Tuyến Tính:

• Dự Đoán Xác Suất:

• Định dạng ô thành Percentage (%).

Bước 4: Tính Toán Log-Likelihood

Đo lường dự đoán xác suất của chúng ta khớp với kết quả thực tế như thế nào. Điều này phức tạp hơn sai số đơn giản vì chúng ta đang xử lý xác suất, không phải giá trị chính xác.

Các Thành Phần Log-Likelihood:

=IF(B2=1,LN(MAX(G2,0.0001)),LN(MAX(1-G2,0.0001)))

• Với kết quả nhị phân, chúng ta không thể sử dụng phép trừ đơn giản (thực tế – dự đoán).
• Thay vào đó, chúng ta đo lường mức độ "ngạc nhiên" của chúng ta về kết quả thực tế cho thấy dự đoán của chúng ta.
• Nếu chúng ta dự đoán 90% cơ hội mua và khách mua, chúng ta không ngạc nhiên (mô hình tốt).
• Nếu chúng ta dự đoán 10% cơ hội mua và khách mua, chúng ta rất ngạc nhiên (mô hình kém).

Bước 5: Thiết Lập Chỉ Số Mô Hình Logistic

Tạo các phép đo hiệu suất phân loại liên quan đến kinh doanh. Các chỉ số này giúp quyết định xem mô hình có đủ tốt để đưa ra quyết định kinh doanh thực tế hay không.

Độ chính xác cao nghĩa là ít tiền marketing bị lãng phí (thấp false positives). Độ nhạy cao nghĩa là chúng ta không bỏ lỡ khách hàng tiềm năng (thấp false negatives).

Chỉ Số Logistic:

• Độ Phù Hợp Mô Hình/Negative Log-Likelihood: Giá trị thấp hơn nghĩa là dự đoán xác suất tốt hơn.

• Độ Chính Xác: Phần trăm khách hàng được phân loại đúng (nếu sử dụng 50% làm ngưỡng cắt).

=SUMPRODUCT((G2:G11>0.5)*(B2:B11=1)+(G2:G11<=0.5)*(B2:B11=0))/10

• Độ Chính Xác (Precision): Phần trăm khách hàng chúng ta dự đoán sẽ mua, thực sự mua?

=IF(SUMPRODUCT((G2:G11>0.5))=0,"No Predictions",SUMPRODUCT((G2:G11>0.5)*(B2:B11=1))/SUMPRODUCT((G2:G11>0.5)))

• Độ Nhạy (Recall): Phần trăm khách hàng đã mua, chúng ta xác định được bao nhiêu?

=SUMPRODUCT((G2:G11>0.5)*(B2:B11=1))/SUMPRODUCT(B2:B11)

Bước 6: Tối Ưu Hóa Mô Hình Logistic Với Solver

Tìm các giá trị tham số phù hợp nhất với mẫu xác suất trong dữ liệu. Solver tối thiểu hóa negative log-likelihood, điều này tối đa hóa xác suất quan sát dữ liệu thực tế.

• Chuyển đến tab Data >> chọn Solver.
• Set Objective: K2 (Negative Log-Likelihood).
• To: Min.
• By Changing Variable Cells: E3,E5.
• Nhấp Solve.

Khắc Phục Sự Cố Thường Gặp

• Solver không hội tụ: Thử giá trị khởi tạo khác hoặc tăng số lần lặp.
• R-squared âm: Kiểm tra lỗi nhập dữ liệu hoặc đặc tả mô hình.
• Tách biệt hoàn hảo trong logistic: Giảm giá trị đặc trưng hoặc thêm regularization.

Kết Luận

Hướng dẫn này cho thấy quy trình từng bước để xây dựng mô hình machine learning trực tiếp trong Excel. Mặc dù Excel có giới hạn so với các công cụ ML chuyên dụng, nhưng nó cung cấp sự minh bạch và dễ tiếp cận để hiểu cơ chế mô hình. Bằng cách sử dụng Solver và các công thức cơ bản của Excel, bạn có thể nhanh chóng triển khai các mô hình machine learning nhẹ này, hình ảnh hóa dự đoán và hiểu độ chính xác của mô hình thông qua một phương pháp đơn giản nhưng có عمق. Các kỹ thuật được trình bày ở đây có thể mở rộng cho các tình huống phức tạp hơn và đóng vai trò như công cụ giáo dục để học các khái niệm hồi quy.

Nhận Bài Tập Excel Nâng Cao Miễn Phí Kèm Giải Pháp!