Hướng dẫn sử dụng Solver trong Excel để giải bài toán tối ưu

Bến Hà Trương
Bến Hà Trương
Jan 14 2023

Solver Add-in là một công cụ có sẵn trong Excel cung cấp các lệnh và các tính năng tùy chỉnh để giải quyết các vấn đề quyết định. Solver Add-in  đặt biệt hữu ích khi giải quyết các bài toán tối ưu, ví dụ như tối thiểu chi phí, tối đa lợi nhuận,...

Quy trình để giải các bài toán tối ưu sử dụng Solver, bạn cần thực hiện theo các bước sau:

  1. Xây dựng hàm mục tiêu (Objective Function)
  2. Xây dựng các ràng buộc (Constraints)
  3. Tổ chức dữ liệu trên bảng tính Excel
  4. Sử dụng Solver để tìm phương án tối ưu

Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách tải và sử dụng công cụ Solver Add-in trong Excel thông qua một ví dụ đơn giản.

Cách tải Solver Add-in trong Excel

Để tải phần bổ trợ solver, bạn có thể làm theo các bước sau:

1. Trên tab File, click vào Options.

2. Trong Add-ins, bạn cần chọn Solver Add-in và nhấp vào nút Go.

Cách tải Solver Add-in trong Excel
 

3. Tích vào ô Solver Add-in và nhấp vào OK.

4. Bạn có thể tìm thấy Solver trên tab Data, trong nhóm Analyze.

Cách tải Solver Add-in trong Excel

Cách tải Solver Add-in trong Excel
 

Xây dựng mô hình với Solver Add-in trong Excel

Các mô hình chúng ta sẽ sử dụng công cụ solve trong Excel trông như hình dưới.

Bài toán này về cơ bản, chúng ta tính xem cần tính toán số lượng đặt hàng của mỗi sản phẩm (bicycles, mopeds và child seats) sao cho tổng lợi nhuận đạt được cao nhất.

Xây dựng mô hình với Solver Add-in trong Excel
 

1. Để xây dựng mô hình lập trình tuyến tính này, bạn hãy trả lời ba câu hỏi sau.
 

  • Các quyết định được thực hiện là gì? Đối với bài toán này, chúng ta cần Excel để tìm ra số lượng đặt hàng của mỗi sản phẩm (bicycles, mopeds và child seats).
  • Những ràng buộc đối với những quyết định này là gì? Các hạn chế ở đây là số vốn và kho sử dụng của các sản phẩm không được vượt quá số vốn và kho dự trữ (tài nguyên) có sẵn. Ví dụ, mỗi chiếc xe đạp sử dụng 300 đơn vị vốn và 0,5 đơn vị lưu trữ.
  • Thước đo tổng thể về hiệu suất của những quyết định này là gì? Thước đo tổng thể của hoạt động là tổng lợi nhuận của ba sản phẩm, vì vậy mục tiêu là tối đa hóa số lượng này.

2. Để mô hình dễ hiểu hơn, tôi sẽ đặt tên cho các phạm vi (named range) như sau.

Range NameCells
UnitProfitC4:E4
OrderSizeC12:E12
ResourcesUsedG7:G8
ResourcesAvailableI7:I8
TotalProfitI12

 

3. Tiếp theo, bạn cần chèn ba hàm SUMPRODUCT như hình bên dưới

Xây dựng mô hình với Solver Add-in trong Excel

 

Trong đó:

  • Tính số vốn bằng hàm SUMPRODUCT với range C7: E7 và OrderSize.
  • Tính hàng tồn kho bằng hàm Sumproduct với range C8: E8 và OrderSize.
  • Tổng lợi nhuận được tính bằng sản phẩm của UnitProfit và OrderSize.

Với công thức này, bạn có thể sử dụng phương pháp thử Đúng - Sai để giải quyết vấn đề. Ví dụ: nếu chúng ta đặt mua 20 xe đạp, 40 xe máy và 100 ghế trẻ em thì tổng lượng tài nguyên sử dụng không được vượt quá số lượng tài nguyên hiện có. Giải pháp này có tổng lợi nhuận là 19000.

Phương pháp thử đúng - sai (trial and error)
 

Tuy nhiên để tìm giải pháp nhanh chóng hơn, chúng ta sẽ tìm hiểu sử dụng Excel Solver 

Để tìm ra giải pháp tối ưu, bạn hãy thực hiện theo các bước sau.

1. Trên tab Data, trong nhóm Analyze, click vào Solver.

Nhập các thông số của solver. Kết quả trả về sẽ phải giống như hình dưới.
 

Hướng dẫn sử dụng Solver trong Excel để giải bài toán tối ưu
 

Bạn có thể chọn nhập tên range hoặc nhấp vào các ô trong bảng tính.

Hướng dẫn sử dụng Solver trong Excel để giải bài toán tối ưu
 

2. Nhập TotalProfit cho Objective.

3. Nhấp vào Max.

4. Nhập OrderSize cho Changing Variable Cells (các ô có thể thay đổi).

5. Nhấp vào Add để thêm các ràng buộc.

Hướng dẫn sử dụng Solver trong Excel để giải bài toán tối ưu
 

6. Kiểm tra 'Make Unconstrained Variables Non-Negative' và chọn 'Simplex LP'.

7. Cuối cùng, nhấp vào Solve.

Kết quả như sau:

Hướng dẫn sử dụng Solver trong Excel để giải bài toán tối ưu
 

Kết luận: 94 xe đạp và 54 xe máy là giải pháp tối ưu nhất. Giải pháp này sẽ cho lợi nhuận tối đa là 25600. Giải pháp này sử dụng tất cả các tài nguyên có sẵn.

Hy vọng, qua ví dụ đơn giản trên, bạn đã biết cách sử dụng công cụ Solver trong Excel để giải bài toán tối ưu. Bên cạnh đó, để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng hữu ích khác, hãy tham gia Gitiho ngay hôm nay.

5/5 - (2 bình chọn)

5/5 - (2 bình chọn)

Bài viết liên quan

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Cách ứng dụng hàm INT và MOD cho lễ tân và các công việc khác

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

Hướng dẫn cách tách địa chỉ ra khỏi chuỗi

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

CÁCH TÍNH ĐIỂM TRUNG BÌNH VÀ XẾP LOẠI TRONG EXCEL (KÈM BÀI TẬP)

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Hướng dẫn kiểm tra giá trị trùng lặp cực đơn giản trên Excel 2010, 2013, 2016, 2019

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Cách cộng, trừ ngày tháng năm trong Excel để tính số ngày siêu nhanh cho kế toán

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

Hướng dẫn 4 cách bôi đen trong Excel đơn giản và nhanh chóng

@ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội