Giải bài toán tối ưu hóa bằng công cụ Excel Solver (phần I)

Tối ưu hóa nguồn lực sẵn có là vấn đề được rất nhiều doanh nghiệp, cá nhân quan tâm. Excel Solver cung cấp cho bạn 1 công cụ rất hữu ích để mô hình hóa và tìm ra phương án cho những vấn đề như vậy. Bài viết sẽ đưa ra một số bài toán tối ưu hóa thường gặp và cách sử dụng Excel Solver để giải quyết (down file Sample để theo dõi)

Trước hết, nếu Excel của bạn chưa có sẵn Solver, làm theo hướng dẫn của bài Solver add-in để thêm vào.

1. Vấn đề 1: Chọn nhóm sản phẩm tối ưu

Một công ty thường sản xuất rất nhiều mặt hàng, mỗi mặt hàng lại có mức lợi nhuận (LN), chi phí (CP) khác nhau, vấn đề ở đây là xác định lượng hàng hóa cần sản xuất của mỗi sản phẩm sao cho thu được LN tối đa, tuy nhiên vẫn phải đảm bảo 1 số điều kiện sau:

• Không dùng hơn nguồn lực sẵn có (nhân công, nguyên vật liệu…)
• Cầu sản phẩm là hữu hạn, không thể sản xuất tràn lan vượt quá cầu của thị trường

Hãy cùng đến với ví dụ đầu tiên ở Sheet “Productmix”

Giả sử bạn là quản lý nhà máy X, nhà máy này sản xuất ra 6 mặt hàng là A,B,C,D,E,F, mỗi mặt hàng cần số giờ lao động và khối lượng nguyên vật liệu (NVL) khác nhau để sản xuất ra 1 kg sản phẩm (hình dưới).  Giá bán và CP cho 1 đơn vị sản phẩm của được thể hiện ở các ô B13:G13 và B14:G14 tương ứng. Trong tháng này, nhà máy X chỉ có thể đáp ứng tối đa 4500 giờ lao động và 1600 kg NVL, vậy nhà máy nên sản xuất như thế nào để có thể tối đa hóa LN?

Các ô B15:G15 thể hiện LN của 1 đơn vị sản phẩm, tính bằng Giá – CP, giả sử khối lượng bán ra mỗi sản phẩm được thể hiện trong ô B23:G23 thì tổng LN của nhà máy X trong tháng (ô B25) sẽ là: =SUMPRODUCT(B15:G15,$ B$ 23:$ G$ 23)

Một mô hình tối ưu hóa cơ bản sẽ có 3 yếu tố: ô Mục tiêu (The target cell), các giá trị biến đổi (The changing cells) và các điều kiện ràng buộc (The constraints), ở VD này 3 yếu tố này được xác định như sau:

• Target cell: B25, ô mục tiêu cần tối đa hóa LN
• Changing cells: ô B23:G23, khối lượng cần sản xuất của từng sp
• Constraints: 2 điều kiện ràng buộc
  • Nguồn lực: tổng số giờ làm việc không vượt quá 4500 (D19<=4500)  và tổng khối lượng NVL không vượt quá 1600 kg (D20<=1600)
  • Tổng cầu: khối lượng sx không vượt quá cầu sản phẩm: B23:G23 <= B16:G16

Chúng ta sẽ cùng nhập các giá trị này vào Solver. Đầu tiên,  trên tab Data, ở phần Analysis chọn Solver, hiện ra bảng chọn như hình dưới

Tại Set objective nhập B25 sau đó tick Max (cần tối ưu hóa LN), tại By changing variable cells chọn vùng giá trị cần tìm là B23:G23. Với phần Constraints, click Add sẽ hiện ra bảng chọn như ở dưới:

Chúng ta có 2 điều kiện ràng buộc cần nhập:

+ Số giờ lao động: B19:B20 <= D19:D20

+ Cầu sản phẩm: B23:G23 <= B16:G16

Tick phần “Make Unconstrained Variables Non-Negative” check box để đảm bảo các giá trị trả ra ko là số âm.  Ở phần “Select A Solving Method”, vì đây là dạng mô hình bậc nhất (chỉ có 1 biến thay đổi) nên chọn “Simplex LP”

Click Solve –> OK, Solver sẽ tìm ra 1 tập hợp thỏa mãn các điều kiên và có LN cao nhất, kết quả như hình dưới, điều này có nghĩa là để có LN cao nhất chúng ta nên sản xuất 596.67 kg sản phẩm D và 1084 kg sản phẩm E

2. Bài toán 2: Phân bổ nguồn lực lao động

Rất nhiều tổ chức như ngân hàng, nhà hàng, tổng đài…có lực lượng lao động làm việc tại các thời gian khác nhau trong ngày và họ cần 1 phương pháp để bố trí thời gian biểu phù hợp nhằm đáp ứng yêu cầu công việc. Chúng ta có thể sử dụng Excel Solver giải quyết điều này khá dễ dàng.

Ví dụ (sheet Scheduling_workforce) : Tổng đài A đang cần bố trí lại thời gian biểu cho các nhân viên trực điện thoại. Hàng ngày, tổng đài A nhận cuộc gọi của KH trong khoảng thời gian từ 8AM – 6PM, mỗi giờ cần tối thiểu một số lượng nhân viên trực như ô B23:B32 ở dưới:

Nhân viên của tổng đài A có 2 dạng: full-time và part-time, trong đó:

• Nhân viên full-time có thể chọn làm việc từ 8AM đến 5PM (nghỉ trưa từ 12AM đến 1PM) hoặc  từ 9AM đến 6PM (nghỉ trưa từ 1PM đến 2PM)  – nhận lương $ 300/ngày
• Nhân viên part-time làm việc từ 10AM đến 2PM – nhận lương $ 60/ngày

Tối đa có thể thuê 4 nhân viên Part-time, câu hỏi là tổng đài A phải bố trí lao động như thế nào để có thể chi phí trả lương thấp nhất?

Ở ô D23:F32 chúng ta điền trạng thái 0 và 1, trong đó 1 thể hiện giờ làm việc, 0 là ngoài giờ làm việc. Ví dụ nhân viên full-time làm ca 8AM-5PM (full-time 1) sẽ được đánh 0 ở khoảng thời gian 12AM-1PM (nghỉ trưa) và 5PM-6PM (hết giờ làm việc). Hãy cùng xác định các yếu tố trong mô hình:

• Changing cells ở đây là C17:C19, các ô D21:E21 là các ô linked tương ứng, đặt công thức SUMPRODUCT (xem trong file) chúng ta sẽ có số lượng nhân viên trực từng giờ ở H23:H32
• Target cells: ô B35, công thức =SUMPRODUCT($ C$ 17:$ C$ 19,$ E$ 12:$ E$ 14)  là số lương nhân viên từng loại nhân với lương tương ứng
• Constraints:
  • C19<= E19 (không quá 4 nhân viên part-time)
  • H23:H32>=B23:B32 (phải đảm bảo số lượng trực tối thiểu ở mỗi giờ)

Cách nhập các yếu tố này vào mô hình tương tự như phần 1, chỉ lưu ý 1 điểm là số lượng nhân viên không thể là số thập phân (đương nhiên!!!) nên chúng ta sẽ phải thêm 1 ràng buộc như hình dưới.

Click Solve và OK, kết quả là để có chi phí nhân công thấp nhất (2940$ ) thì chúng ta phải có 4 nhân viên full time làm ca 8AM-5PM, 5 nhân viên full-time làm ca 9AM-6PM và có thêm 4 nhân viên part-time.

Bài trên đã giới thiệu cho bạn 2 dạng bài toán tối ưu hóa thường thấy có thể giải quyết bằng Excel Solver, hãy download file sample để xem chi tiết. Những dạng khác sẽ được giới thiệu ở bài viết lần sau

Download file sample: https://drive.google.com/file/d/1fmK_agCT_7PkfDgIgqnl17FuxybmowV_/view