Giải bài toán tối ưu hệ phương trình trên excel năm 2024

Trong bài viết “Công cụ Solver trong Excel - Phần 1: Giải hệ phương trình” chúng ta đã được giới thiệu và tìm hiểu ở mức độ cơ bản đối với công cụ Solver. Tiếp tục seri chia sẻ kiến thức Excel, chúng ta sẽ áp dụng Solver vào tình huống phổ biến nhất, thực tế nhất, đó là xử lý bài toán tối ưu trong sản xuất kinh doanh các bạn nhé.

Ví dụ: Tối ưu hóa bài toán sản xuất

Giả sử doanh nghiệp của chúng ta sản xuất 3 mặt hàng là Cam, Táo và Chuối. Với nguồn lực sẵn có, ta sẽ tính toán phương án sản xuất để doanh thu sản phẩm bán ra là lớn nhất.

Bảng 1 thể hiện công thức sản xuất mỗi sản phẩm. Chẳng hạn đối với mỗi quả Cam sản xuất ra ta cần nguyên liệu đầu vào là 6 lao động, 9 đất và 18 nước.

Số tài nguyên đầu vào này là hữu hạn, bảng 2 ta có lượng lao động, đất và nước tối đa có thể sử dụng lần lượt là 200, 150 và 600.

Bảng 3 thể hiện đơn giá cho mỗi sản phẩm đầu ra, từ đó tính được doanh thu tương ứng với mỗi sản phẩm và tổng doanh thu của công ty.

Add Your Heading Text Here

Ban đầu ta để số lượng sản xuất Cam, Táo, Chuối là giá trị bất kỳ, chẳng hạn mỗi loại chỉ sản xuất 1 sản phẩm như hình trên.

Tại Bảng 2 – Nguyên liệu đầu vào, ta tính tổng mỗi nguyên liệu đầu vào đã sử dụng tại các ô C9:C11 như sau:

C9: =SUMPRODUCT(B15:B17,B3:B5)

C10: =SUMPRODUCT(B15:B17,C3:C5)

C11: =SUMPRODUCT(B15:B17,D3:D5)

Giá trị các ô C9:C11 phải nhỏ hơn hoặc bằng số lượng tối đa có thể sử dụng tại ô B9:B11

Vào thẻ Data => Solver, cửa sổ Solver hiển thị ta nhập các tham số sau:

Set Objective – thiết lập kết quả mục tiêu: chọn D18 => Tổng doanh thu

To: Max => Giá trị lớn nhất

By Changing Variable Cells – giá trị các ẩn: chọn B15:B17 => Số lượng mỗi sản phẩm sản xuất

Subject to the Constraints – các ràng buộc: chọn Add, thiết lập các ràng buộc sau

– B15:B17 = integer => số lượng sản phẩm là các số nguyên

– B15:B17 >= 0 => số lượng sản phẩm là các số lớn hơn hoặc bằng 0

– C9:C11 <= B9:B11 => nguyên liệu đã sử dụng phải nhỏ hơn số lượng tối đa có thể sử dụng

Click chọn Solver => OK, Excel đã tính toán cho ta kết quả số lượng sản xuất Cam, Táo và Chuối cần tìm lần lượt là 0, 12 và 4 như hình dưới. Khi đó tổng doanh thu đạt tối đa là 236.000.

Add Your Heading Text Here

Bài viết này là một phần trong Series “Học Excel miễn phí cùng chuyên gia”. Bạn hãy click vào link để xem toàn bộ Series bài viết, dễ dàng làm chủ Excel cùng chúng tôi nhé.

Chúng tôi cung cấp kiến thức học Excel miễn phí, nếu bạn muốn có được khóa học bài bản, hệ thống, được hỗ trợ trực tiếp từ chuyên gia của MasterMOS. Bạn có thể tham khảo và đăng ký khóa học Excel của chúng tôi với học phí ưu đãi chỉ từ 599k. Bạn tham khảo Các khóa học Excel ở đây nhé.

Cảm ơn bạn đã đọc hết bài viết này. Đây là bài viết tâm huyết từ đội ngũ hoctinonline.edu.vn, nếu bạn cảm thấy hữu ích, hãy like và share bài viết này để đội ngũ hoctinonline.edu.vn có thêm động lực viết bài nhé. Trân trọng cảm ơn bạn.

Trước khi sử dụng, chúng ta cần phải thêm tiện ích này vào Excel. Tùy theo phiên bản Excel mà bạn đang sử dụng thì cách làm khác nhau đôi chút. Tuy nhiên, cách thực hiện Add-In Solver cho các phiên bản Excel 97-2003 là giống nhau và cách thực hiện Add-In Solver trong Excel 2007- 2010 cũng tương tự nhau. Các hình minh họa trong bài này sẽ sử dụng phiên bản Excel 2003 và Excel 2007.

1. Add-In Solver cho Excel 2003

Các bước thực hiện Add-Ins Solver cho Excel 2003 như sau:

Bước 1. Vào Tools | chọn Add-Ins. Hộp thoại Add-Ins xuất hiện.

Hình 1. Chọn lệnh Add-Ins

Bước 2. Trong hộp thoại Solver, tích vào ô vuông Solver Add-In

Hình 2. Hộp thoại Add-Ins chứa các chức năng mở rộng của Excel

Bước 3. Nhấn nút OK đóng hộp Add-Ins

Bước 4. Khi đó trong thực đơn Tools sẽ xuất hiện thêm lệnh Solver

Hình 3. Lệnh Solver trong thực đơn Tools

1. Add-In Solver cho Excel 2007

Do giao diện Excel 2007 thay đổi nhiều so với các phiên bản Excel cũ nên các bước thực hiện Add-Ins sẽ khác đôi chút. Các bước thực hiện như sau:

Hình 5. Hộp thoại Excel Options

Bước 3. Tại Manage, chọn Excel Add-Ins từ danh sách và nhấn nút Go... để mở hộp thoại Add- Ins.

Hình 6. Solver Add-in

Bước 4. Chọn Solver Add-in từ danh sách Add-Ins avaiable và nhấn nút OK.

Bước 5. Trong ngăn Data xuất hiện thêm nhóm Analysis chứa lệnh Solver.

Hình 7. Lệnh Solver Lần chỉnh sửa cuối: 25 Tháng năm 2010 Loạt bài chào mừng sinh nhật GPE lần 4, năm 2010

SAU KHI ĐẪCÌ ĐẶT ĐƯỢC SOLVER, TA SẼ HỌC CÁCH SỬ DUNG SOLVER ĐỂ

GIAIR CÁC BÀI TOÁN TỐI ƯU.

2. Sử dụng Solver

2. Excel 97-

Để sử dụng tốt Solver ta cần nắm vững các yêu cầu thông số cần phải khai báo trong hộp thoại Solver Parameters: DƯỚI ĐÂY LÀ BẢNG SOLVER PARAMETER TRONG EXCEL 97-2007.

Hình 1. Hộp thoại Solver Excel 97-

 Set Target Cell: Nơi đây ta cần nhập vào địa chỉ của hàm mục tiêu.  Equal To: Hàm mục tiêu muốn đạt tới Max , Min hay Value of (bằng một giá trị mong muốn nào đó thì nhập giá trị vào.)

CÓ NGHĨA LÀ HÀM MỤC TIÊU PHẢI DAT CỰC TIÊU HAY CỰC ĐẠI HAY MỘT GIÁ TRỊ NÀO ĐÓ ( MIN? MÃ? HAY MỘT GIẠ TRỊ NÀO ĐÓ)

 By Changing Cell: Nhập vào địa chỉ chứa các biến của bài toán cần giải.

ĐÂY LÀ ĐỊA CHỈ CỦA CÁC BIẾN CỦA BÀI TOÁN MÀ CHÚNG TA ĐANG ĐI TÌM

NGHIỆM SỐ.

KHI SỬ DỤNG SOLVER TRONG EXCEL 2010 PHẢI CHÚ Ý CÁC ĐIỂM SAU.

2. Excel 2010

Solver trong Excel 2010 đã được thiết kế lại về giao diện và tên gọi của các hộp khai báo thông số cũng như sắp xếp lại các thành phần trên hộp thoại. Solver trong Excel 2010 đã bổ sung thêm phương pháp tìm kiếm lời giải mới Evolutionary Solver dựa trên các thuật toán di truyền (genetic algorithms). Phương pháp này cho phép giải quyết những bài toán có sử dụng bất kỳ hàm nào trong Excel. Solver trong Excel 2010 được tối ưu cho việc giải quyết các bài toán tuyến tính và phi tuyến và bổ sung thêm 2 loại báo cáo kết quả Linearity và Feasibility.

Hình 10. Hộp thoại Solver Parameters của Excel 2010

buộc và các biến không thõa trong bài toán.

Hình 12. Báo cáo Linearity

Báo cáo Feasibility

Nếu không tìm được lời giải khả thi cho bài toán. Solver hiển thị thông báo không tìm được lời giải khả thi và báo cáo sẽ giúp ta xác định nguyên nhân của vấn đề không tìm được lời giải.

Hình 13. Báo cáo Feasibility

Bạn có thể tinh chỉnh các thông số cho quá trình giải bài toán bằng cách nhấn vào nút Options trong hộp thoại Solver Parameters. Hộp thoại Options xuất hiện và được tổ chức thành 3 ngăn All Methods, GRG Nonlinear và Evolutionary tương ứng với các phương pháp tìm kiếm lời giải.

Hình 14. Hộp thoại Options Lần chỉnh sửa cuối: 25 Tháng năm 2010

DƯỚI ĐÂY LÀ CÁC VÍ DỤ

3. Tìm nghiệm cho hệ phương trình

Tìm nghiệm cho hệ phương trình sau:

Các bước thực hiện:

Bước 1. Lập mô hình bài toán trên bảng tính trong vùng A7:F10 theo dạng sau:

Hình 15. Lập mô hình bài toán trên bảng tính

Bước 2. Nhập các hệ số bên vế trái của các phương trình:

 Trong vùng A8:C8 nhập các hệ số của phương trình thứ nhất  Trong vùng A9:C9 nhập các hệ số của phương trình thứ hai  Trong vùng A10:C10 nhập các hệ số của phương trình thứ ba

Hình 19. Tính vế trái

Bước 6. Vào Data | nhóm Analysis | chọn lệnh Solver, hộp thoại Solver Parameters xuất hiện. Chúng ta tiến hành khai báo các thông số:

 Tại By Changing Cells nhập vào địa chỉ của các nghiệm khởi tạo D8 10

Hình 20. Khai báo By Changing Cells

 Tại hộp Subject to the Constraints, nhấp nút Add để thêm ràng buộc vào hộp thoại Add Contraint như hình sau:

Hình 21. Thêm ràng buộc

 Nhấp nút OK sau khi khai báo xong ràng buộc, hộp thoại Solver Parameters như sau:

Hình 22. Khai báo thông số cho Solver

Bước 7. Nhấp vào nút Solve để bắt đầu tìm nghiệm hệ phương trình. Khi Solver tìm được nghiệm thì hộp thoại Solver Results xuất hiện như hình sau: