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

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.

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

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:

• B25, ô mục tiêu cần tối đa hóa LN
• ô B23:G23, khối lượng cần sản xuất của từng sp
• • 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

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"

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 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

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

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?

• ở đâ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
• ô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
• • C19<= E19 (không quá 4 nhân viên part-time)

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.

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