Hướng dẫn sử dụng solver trong 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.

Hồi còn cắp sách tới trường, chắc hẳn ai cũng đã từng miệt mài bên những bài toán giải phương trình, bất phương trình hay tìm giá trị lớn nhất, nhỏ nhất. Đến khi đi làm, ta lại xử lý các bài toán tối ưu hóa trong sản xuất, kinh doanh. Trong hầu hết các trường hợp, để giải quyết thủ công các vấn đề trên là vô cùng phức tạp. May mắn thay, Excel đã xây dựng công cụ Solver tuyệt vời hỗ trợ chúng ta xử lý bài toán này. Và bây giờ, các bạn cùng Master Mos tìm hiểu các sử dụng công cụ Solver qua ví dụ sau nhé.

– Các bước cài đặt: (dành cho phiên bản MS Office 2010, với phiên bản MS Office 2003 có thể tìm thấy trong cuốn giáo trình Tin học ứng dụng-HVTC)

+ Khởi động Excel

+ Vào File chọn Options

+ Trong cửa sổ Excel Options ta bấm chọn Add-Ins, sau đó bấm Go…

+ Trong cửa sổ Add-Ins, ta tích chọn Solver Add-in, sau đó bấm chọn OK để tiến hành cài đặt.

+ Sau khi cài đặt xong sẽ xuất hiện công cụ Solver nằm ở thẻ Data.

2. Sử dụng Solver

– Yêu cầu: giải bài toán quy hoạch tuyến tính sau

F = 4x1 + 2x2 + x3 + 3x4 -> Max

Với các ràng buộc:

9x1 + 4x2 + 2x3 + 6x4 <= 1600

4x2 + 2x3 + 6x4 <= 900

9x1 + 2x3 + 6x4 <= 840

xi >= 0 nguyên, với i = 1, 2, 3, 4.

– Thực hiện:

+ Tổ chức dữ liệu trên bảng tính

Trong đó:

• Từ ô B1:E1, ta khởi tạo các giá trị ban đầu của xi là 0
• Ô B3 tính giá trị của hàm F bằng công thức:

\= B1*B2+C1*C2+D1*D2+E1*E2

Hoặc: =SUMPRODUCT(B1:E1,B2:E2)

• Các ô F5, F6, F7 ta tính giá trị vế phải của các ràng buộc.

Ví dụ công thức tại ô F5: =SUMPRODUCT($B$1:$E$1,B5:E5)

+ Chọn công cụ Solver: thẻ Data, chọn Solver

+ Khai báo các tham số trong cửa sổ Solver Parameters

Trong đó:

• Set Objective: Khai báo ô chứa hàm mục tiêu (B3)
• To: Chọn Max
• By Changing Variable Cells: Ô chứa ẩn ($B$1:$E$1)

+ Khai báo các ràng buộc: bấm Add

Trong đó:

• Cell Reference: Điền giá trị vế phải của ràng buộc
• Chọn dấu phép toán: <=
• Constraint: Giá trị vế phải ($G$5)
• Tiếp tục bấm Add để thêm hai ràng buộc (ở ô F6,F7) và ràng buộc các xi>=0
• Do bài toán quy hoạch nguyên (xi nguyên) nên ta phải thêm ràng buộc $B$2:$E$2 = int, và hiệu chỉnh tham số Integer Optimality(%) trong phần Options là 0%

+ Bấm Solver để giải và máy sẽ hiện ra cửa sổ Solver Result, ta chọn Keep Solver Solution để giữ lại kết quả.