Xây dựng các mô hình ML nhẹ trong Excel bằng Bộ giải &Công thức

Excel là một công cụ mạnh mẽ đáng kinh ngạc dành cho các tác vụ học máy cơ bản.  Mặc dù đây không phải là một nền tảng máy học nhưng nó có thể được sử dụng một cách hiệu quả để thể hiện các khái niệm ML cơ bản như hồi quy tuyến tính và logistic bằng cách sử dụng các hàm và Bộ giải tích hợp sẵn.

Trong hướng dẫn này, chúng tôi sẽ trình bày cách xây dựng các mô hình máy học nhẹ trong Excel bằng Bộ giải và các công thức.

• Hồi quy tuyến tính: Dự đoán các giá trị liên tục (doanh thu bán hàng, giá nhà, điểm kiểm tra, v.v.).
• Hồi quy logistic: Dự đoán kết quả có/không (mua hàng của khách hàng, vỡ nợ, chẩn đoán y tế, đạt/không đạt, v.v.).

Điều kiện tiên quyết:

• Microsoft Excel (khuyên dùng phiên bản 2016 trở lên).
• Bật bổ trợ bộ giải.
  • Đi tới Tệp tab>> chọn Tùy chọn >> chọn Phần bổ trợ>> chọn Phần bổ trợ Excel .
  • Nhấp vào Đi .

• • Chọn Bổ trợ bộ giải .
  • Nhấp vào OK .

• Hiểu 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 thông qua các điểm dữ liệu. Chúng tôi sẽ lập mô hình một kịch bản kinh doanh đơn giản trong đó chi tiêu 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 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 tiêu quảng cáo tính bằng nghìn đô la) và đầu ra (doanh thu bán hàng tính bằng nghìn đô la).

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

Bước 2:Tạo công thức dự đoán

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

• Đoạn chặn (b0) :Nơi đường này cắt trục Y (doanh số cơ bản với quảng cáo $0).
• Độ dốc (b1) :Doanh số tăng lên bao nhiêu cho mỗi lần tăng 1000 đô la quảng cáo.

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

Thông số mô hình:

Predicted Y = b0 + b1 * X

• Đoạn chặn (b0)
• Giá trị ban đầu 0
• Độ dốc (b1)
• Giá trị ban đầu 1

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

Ý nghĩa toán học:

• Nếu b0 =0,5 và b1 =2 thì chi tiêu $3k cho quảng cáo dự đoán:0,5 + 2*3 =$6,5k doanh thu.
• Mô hình tìm hiểu 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 F11.

Bước 4:Tính số dư và sai số

Đo lường mức độ sai lầm của dự đoán của chúng tôi. Điều này rất quan trọng vì mô hình học bằng cách cố gắng giảm thiểu những lỗi này.

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

Dư lượng:

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

Lỗi bình phương:

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

Bước 5:Tính toán số liệu lỗi

Tạo các phép đo có ý nghĩa kinh doanh về hiệu suất của mô hình. Những số liệu này giúp biết liệu mô hình có đủ tốt để sử dụng trong thế giới thực hay không.

Thiết lập các chỉ số chính trong khu vực được chỉ định:

Chỉ số lỗi:

• Tổng số lỗi bình phương (SSE): Tổng số lỗi trên tất cả các dự đoán – càng thấp càng tốt.

• Lỗi bình phương trung bình gốc (RMSE): Sai số trung bình theo đơn vị gốc ($000) – dễ diễn giải hơn.

• R-Bình phương: Phần trăm biến động doanh thu do quảng cáo giải thích (0-100%, càng cao càng tốt).

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

• Lỗi tuyệt đối trung bình (MAE): Sai số tuyệt đối trung bình – ít nhạy cảm hơn với các giá trị ngoại lệ so với RMSE.

Bước 6:Sử dụng Solver để tối ưu hóa thông số

Để Excel tự động tìm các giá trị tốt nhất cho phần đánh chặn và độ dốc giúp giảm thiểu lỗi dự đoán.

• Đi tới Dữ liệu tab>> chọn Bộ giải .
• Đặt mục tiêu:K2 (Ô SSE).
• Tới:Tối thiểu .
• Bằng cách thay đổi ô biến:E3,E5 (thông số của bạn).
• Nhấp vào Giải .

• Nhấp vào OK .

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

Bước 7:Tạo hình ảnh trực quan

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

• Chọn cột Quảng cáo và Bán hàng.
• Đi tới Chèn tab>> từ Biểu đồ >> chọn Biểu đồ phân tán .

• Nhấp chuột phải vào biểu đồ>> chọn Dữ liệu >> chọn Thêm chuỗi .
  • Tên bộ truyện: Chọn ô F1.
  • Giá trị chuỗi X: Chọn giá trị X (ví dụ:B2:B11)
  • Giá trị chuỗi Y: Nhấp và chọn giá trị dự đoán F2:F11.
• Định dạng chuỗi dự đoán dưới dạng một dòng.

• Dòng dự đoán tuân theo xu hướng chung của các điểm dữ liệu.
• Các điểm nằm rải rác xung quanh đường thẳng (không phải tất cả ở trên hoặc dưới).
• Không có hình 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 các quyết định có/không. Không giống như hồi quy tuyến tính dự đoán những con số chính xác, hồi quy logistic dự đoán khả năng xảy ra điều gì đó (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 tôi muốn dự đoán liệu họ có mua sản phẩm cao cấp của chúng tôi (1) hay không (0). Đây là điển hình cho việc nhắm mục tiêu tiếp thị, 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 của khách hàng (đơn vị $10k) và quyết định mua hàng. Lưu ý rằng khách hàng có thu nhập thấp hơn (1-5) có xu hướng không mua (0), trong khi khách hàng có thu nhập cao hơn (6-10) có xu hướng mua (1). Điều này phản ánh mô hình 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 các tham số cho hàm logistic. Không giống như hồi quy tuyến tính, các tham số này hoạt động thông qua một phép biến đổi toán học phức tạp hơn (hàm sigmoid).

• Đoạn chặn (b0) :Dịch chuyển ngưỡng sang trái hoặc sang phải (nơi xảy ra xác suất 50%).
• Độ dốc (b1) :Kiểm soát mức độ chuyển tiếp từ “không có khả năng” sang “có thể”.
• Giá trị ban đầu :Chúng tôi bắt đầu với những phỏng đoán hợp lý; Bộ giải sẽ tối ưu hóa chúng.

Thông số hậu cần:

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

• Đoạn chặn (b0)
• -2 (Giá trị ban đầu)
• Độ dốc (b1)
• 0,5 (Giá trị ban đầu)

Tạo công thức dự đoán logistic:

Chuyển đổi các kết hợp tuyến tính thành xác suất bằng cách sử dụng hàm sigmoid. Đây là phép thuật toán học giữ cho các dự đoán luôn nằm trong khoảng từ 0 đến 1.

• Kết hợp tuyến tính :b0 + b1*X (giống như 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 thành phạm vi 0-1.
• Kết quả :Đường cong chữ S trơn biểu thị xác suất. Nếu xác suất là 0,7 thì có 70% khả năng khách hàng này sẽ mua.

Dự đoán xác suất: 

• Kết hợp tuyến tính:

• Dự đoán xác suất:

• Định dạng ô dưới dạng Phần trăm (%) .

Bước 4:Tính toán khả năng ghi nhật ký

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

Thành phần khả năng ghi nhật ký:

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

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

Bước 5:Thiết lập số liệu mô hình logistic

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

Độ chính xác cao có nghĩa là ít lãng phí tiền tiếp thị hơn (tỷ lệ dương tính giả thấp). Tỷ lệ ghi nhớ cao có nghĩa là chúng tôi không bỏ lỡ khách hàng tiềm năng (tiêu cực sai thấp).

Số liệu hậu cần:

• Khả năng phù hợp với mô hình/Khả năng ghi nhật ký âm: Giá trị thấp hơn có 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 chính xác (nếu chúng tôi sử dụng 50% làm giới hạn).

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

• Độ chính xác: Chúng tôi dự đoán bao nhiêu phần trăm khách hàng sẽ mua và bao nhiêu phần trăm đã mua?

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

• Nhớ lại: Bao nhiêu phần trăm khách hàng đã mua, chúng tôi xác định được bao nhiêu phần trăm?

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

Bước 6:Tối ưu hóa mô hình logistic bằng 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. Bộ giải giảm thiểu khả năng ghi nhật ký âm, giúp tối đa hóa xác suất quan sát dữ liệu thực tế.

• Đi tới Dữ liệu tab>> chọn Bộ giải .
• Đặt mục tiêu:K2 (Khả năng ghi nhật ký âm).
• Tới:Tối thiểu .
• Bằng cách thay đổi ô biến:E3,E5 .
• Nhấp vào Giải .

Khắc phục sự cố thường gặp

• Bộ giải không hội tụ :Hãy thử các giá trị ban đầu khác nhau hoặc tăng số lần lặp.
• Bình phương R âm :Kiểm tra lỗi nhập dữ liệu hoặc đặc tả mô hình.
• Sự phân tách hoàn hảo trong hậu cần :Giảm giá trị tính năng hoặc thêm chính quy.

Kết luận

Hướng dẫn này trình bày quy trình từng bước để xây dựng mô hình học máy trực tiếp trong Excel. Mặc dù Excel có những hạn chế so với các công cụ ML chuyên dụng nhưng nó mang lại sự minh bạch và khả năng truy cập để hiểu cơ chế mô hình. Bằng cách sử dụng Bộ giải của Excel và các công thức cơ bản, bạn có thể nhanh chóng triển khai các mô hình máy học nhẹ này, trực quan hóa các 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 sâu sắc. Các kỹ thuật trình bày ở đây có thể được mở rộng sang các tình huống phức tạp hơn và đóng vai trò là công cụ giáo dục để học các khái niệm hồi quy.