Excel Solver: Tối ưu hóa kết quả, thêm ràng buộc và lưu giải pháp dưới dạng kịch bản

Bạn thiết lập mô hình Bộ giải Excel của mình bằng cách sử dụng hộp thoại Tham số bộ giải. Bạn sử dụng hộp Đặt Mục tiêu để chỉ định ô mục tiêu và bạn sử dụng nhóm Đến để cho Trình giải quyết Excel biết bạn muốn gì từ ô mục tiêu: giá trị lớn nhất có thể; giá trị nhỏ nhất có thể; hoặc một giá trị cụ thể. Cuối cùng, bạn sử dụng hộp By Change Variable Cells để chỉ định các ô mà Bộ giải có thể sử dụng để cắm các giá trị nhằm tối ưu hóa kết quả.

Tối ưu hóa kết quả của Excel Solver

Khi Solver tìm thấy giải pháp, bạn có thể chọn Giữ giải pháp Solver hoặc Khôi phục các giá trị ban đầu. Nếu bạn chọn Giải pháp Keep Solver, Excel sẽ thay đổi vĩnh viễn trang tính. Bạn không thể hoàn tác các thay đổi.

Với mô hình trang tính sẵn sàng cho Solver của bạn đã sẵn sàng hoạt động, đây là các bước cần làm theo để tìm ra kết quả tối ưu cho mô hình của bạn bằng cách sử dụng Solver:

Chọn Dữ liệu → Bộ giải.
Excel mở hộp thoại Tham số bộ giải.

Trong hộp Đặt Mục tiêu, hãy nhập địa chỉ của ô mục tiêu của mô hình của bạn.
Lưu ý rằng nếu bạn nhấp vào ô để nhập, Bộ giải sẽ tự động nhập địa chỉ ô tuyệt đối (ví dụ: $ B $ 14 thay vì B14). Bộ giải hoạt động tốt theo cả hai cách.

Trong nhóm Tới, hãy chọn một tùy chọn:

• Max: Trả về giá trị lớn nhất có thể.
• Min: Trả về giá trị nhỏ nhất có thể.
• Giá trị Trong: Nhập một số để đặt ô mục tiêu thành số đó.

  Đối với mô hình ví dụ, Giá trị của đã được chọn và nhập 0 vào hộp văn bản.

Trong hộp Bằng cách thay đổi các ô có thể thay đổi, hãy nhập địa chỉ của các ô bạn muốn Bộ giải thay đổi trong khi tìm kiếm giải pháp.
Trong ví dụ, các ô thay đổi là B4 và C4. Hình ảnh sau đây cho thấy hộp thoại Tham số bộ giải đã hoàn thành.

Hộp thoại Tham số bộ giải đã hoàn thành.

Nhấp vào Giải quyết.
Solver bắt tay vào công việc. Khi Solver xử lý sự cố, bạn có thể thấy hộp thoại Show Trial Solution hiển thị một hoặc nhiều lần.

Trong bất kỳ hộp thoại Hiển thị Giải pháp Dùng thử nào xuất hiện, bấm Tiếp tục để di chuyển mọi thứ.
Khi quá trình tối ưu hóa hoàn tất, Excel sẽ hiển thị hộp thoại Solver Results.

Hộp thoại Kết quả giải và giải pháp cho vấn đề hòa vốn.

Chọn tùy chọn Giải pháp Keep Solver.
Nếu bạn không muốn chấp nhận kết quả, hãy chọn tùy chọn Khôi phục Giá trị Gốc để thay thế.

Bấm OK.

Bạn có thể yêu cầu Solver hiển thị một hoặc nhiều báo cáo cung cấp cho bạn thông tin bổ sung về kết quả. Trong hộp thoại Kết quả bộ giải, sử dụng danh sách Báo cáo để chọn từng báo cáo bạn muốn xem:

• Trả lời: Hiển thị thông tin về ô mục tiêu, ô biến và các ràng buộc của mô hình. Đối với ô mục tiêu và ô biến, Bộ giải hiển thị giá trị ban đầu và giá trị cuối cùng.
• Độ nhạy: Cố gắng chỉ ra độ nhạy của giải pháp đối với những thay đổi trong công thức của mô hình. Bố cục của báo cáo Độ nhạy tùy thuộc vào loại mô hình bạn đang sử dụng.
• Giới hạn: Hiển thị ô mục tiêu và giá trị của nó, cũng như các ô biến và địa chỉ, tên và giá trị của chúng.

Excel Solver có thể sử dụng một trong số các phương pháp giải. Trong hộp thoại Tham số bộ giải, sử dụng danh sách Chọn phương pháp giải để chọn một trong các tùy chọn sau:

• Simplex LP: Sử dụng nếu mô hình trang tính của bạn là tuyến tính. Nói một cách đơn giản nhất có thể, mô hình tuyến tính là mô hình trong đó các biến không được nâng lên thành bất kỳ lũy thừa nào và không có hàm siêu việt nào - chẳng hạn như SIN và COS - được sử dụng.
• GRG phi tuyến: Sử dụng nếu mô hình trang tính của bạn là phi tuyến tính và trơn tru. Nói chung, mô hình trơn là mô hình trong đó đồ thị của phương trình được sử dụng không hiển thị các cạnh sắc hoặc các điểm đứt.
• Tiến hóa: Sử dụng nếu mô hình trang tính của bạn là phi tuyến tính và không trơn tru.

Bạn có phải lo lắng về điều này không? Gần như chắc chắn là không. Excel Solver mặc định sử dụng GRG Nonlinear và điều đó sẽ hoạt động cho hầu hết mọi thứ bạn làm với Solver.

Thêm các ràng buộc vào Excel Solver

Thế giới thực đặt ra các hạn chế và điều kiện đối với các công thức. Một nhà máy có thể có công suất tối đa là 10.000 đơn vị một ngày, số lượng nhân viên trong một công ty không được là số âm và chi phí quảng cáo của bạn có thể bị giới hạn ở 10 phần trăm tổng chi phí.

Tương tự, giả sử rằng bạn đang chạy phân tích hòa vốn trên hai sản phẩm. Nếu bạn chạy tối ưu hóa mà không có bất kỳ hạn chế nào, Solver có thể đạt tổng lợi nhuận bằng 0 bằng cách đặt một sản phẩm bị lỗ nhẹ và sản phẩm kia có lãi nhẹ, trong đó lỗ và lãi triệt tiêu lẫn nhau. Trên thực tế, nếu bạn xem kỹ hình ảnh trước đó, đây chính xác là những gì Solver đã làm. Để có được giải pháp hòa vốn thực sự, bạn có thể muốn xem cả hai giá trị lợi nhuận của sản phẩm là 0.

Những hạn chế và điều kiện như vậy là ví dụ về những gì Solver gọi là ràng buộc. Việc thêm các ràng buộc yêu cầu Solver tìm ra giải pháp để các điều kiện này không bị vi phạm.

Đây là cách chạy Solver với các ràng buộc được thêm vào tối ưu hóa:

Chọn Dữ liệu → Bộ giải.
Excel mở hộp thoại Tham số bộ giải.

Sử dụng hộp Đặt Mục tiêu, nhóm Tới và hộp Bằng cách Thay đổi Ô Biến để thiết lập Bộ giải như được mô tả ở trên.

Nhấp vào Thêm.
Excel sẽ hiển thị hộp thoại Thêm Ràng buộc.

Trong hộp Tham chiếu Ô, hãy nhập địa chỉ của ô bạn muốn giới hạn.
Bạn có thể nhập địa chỉ hoặc chọn ô trên trang tính.

Trong danh sách thả xuống, hãy chọn toán tử bạn muốn sử dụng.
Hầu hết thời gian, bạn sử dụng một toán tử so sánh, chẳng hạn như bằng (=) hoặc lớn hơn (>). Sử dụng toán tử int (số nguyên) khi bạn cần một ràng buộc, chẳng hạn như tổng số nhân viên, là một giá trị nguyên thay vì một số thực (nghĩa là một số có thành phần thập phân; bạn không thể có 10,5 nhân viên!). Sử dụng toán tử bin (nhị phân) khi bạn có một ràng buộc phải là TRUE hoặc FALSE (hoặc 1 hoặc 0).

Nếu bạn đã chọn toán tử so sánh ở Bước 5, trong hộp Ràng buộc, hãy nhập giá trị mà bạn muốn giới hạn ô.
Hình ảnh này cho thấy một ví dụ về hộp thoại Thêm Ràng buộc đã hoàn thành. Trong mô hình ví dụ, ràng buộc này yêu cầu Solver tìm giải pháp sao cho lợi nhuận của sản phẩm Bàn cờ bơm hơi (ô B12) bằng 0.

Hộp thoại Thêm Ràng buộc đã hoàn thành.

Để chỉ định các ràng buộc khác, hãy nhấp vào Thêm và lặp lại các Bước từ 4 đến 6, nếu cần.

Ví dụ: bạn thêm một ràng buộc yêu cầu lợi nhuận của sản phẩm Dog Polisher (ô C12) là 0.

Bấm OK.

Excel quay trở lại hộp thoại Tham số bộ giải và hiển thị các ràng buộc của bạn trong hộp danh sách Chủ đề cho các Ràng buộc.

Nhấp vào Giải quyết.

Trong bất kỳ hộp thoại Hiển thị Giải pháp Dùng thử nào xuất hiện, bấm Tiếp tục để di chuyển mọi thứ.
Hình ảnh dưới đây cho thấy ví dụ về giải pháp hòa vốn với các ràng buộc được thêm vào. Lưu ý rằng không chỉ ô Tổng lợi nhuận (B14) được đặt thành 0, mà cả hai ô Lợi nhuận sản phẩm (B12 và C12) cũng vậy.

Hộp thoại Kết quả giải và giải pháp cuối cùng cho vấn đề hòa vốn.

Chọn tùy chọn Giải pháp Keep Solver.
Nếu bạn không muốn chấp nhận kết quả, hãy chọn tùy chọn Khôi phục Giá trị Gốc để thay thế.

Bấm OK.

Bạn có thể thêm tối đa 100 ràng buộc. Ngoài ra, nếu bạn cần thực hiện thay đổi đối với một ràng buộc trước khi bắt đầu giải quyết, hãy chọn ràng buộc trong hộp danh sách Chủ đề Ràng buộc, bấm Thay đổi, sau đó thực hiện các điều chỉnh của bạn trong hộp thoại Thay đổi Ràng buộc xuất hiện. Nếu bạn muốn xóa một ràng buộc mà bạn không cần nữa, hãy chọn ràng buộc rồi bấm Xóa.

Lưu giải pháp Excel Solver dưới dạng một kịch bản

Bất cứ khi nào bạn có một mô hình bảng tính sử dụng một tập hợp các giá trị đầu vào nhất quán - được gọi là thay đổi ô - bạn sẽ có cái mà Excel gọi là một kịch bản. Với Solver, các ô thay đổi này là các ô biến đổi của nó, vì vậy giải pháp Solver tương ứng với một loại kịch bản trong Excel . Tuy nhiên, Solver không cung cấp cho bạn cách dễ dàng để lưu và chạy lại một giải pháp cụ thể. Để khắc phục sự cố này, bạn có thể lưu giải pháp dưới dạng tình huống mà sau đó bạn có thể gọi lại bằng cách sử dụng tính năng Trình quản lý tình huống của Excel.

Làm theo các bước sau để lưu giải pháp Solver dưới dạng một kịch bản:

Chọn Dữ liệu → Bộ giải.
Excel mở hộp thoại Tham số bộ giải.

Sử dụng hộp Đặt Mục tiêu, nhóm Tới, hộp Bằng cách Thay đổi Ô Biến và danh sách Chủ đề Ràng buộc để thiết lập Bộ giải như được mô tả ở trên.

Nhấp vào Giải quyết.

Bất cứ khi nào hộp thoại Hiển thị Giải pháp Thử nghiệm xuất hiện, hãy chọn Tiếp tục.
Khi quá trình tối ưu hóa hoàn tất, Excel sẽ hiển thị hộp thoại Solver Results.

Nhấp vào Lưu kịch bản.
Excel sẽ hiển thị hộp thoại Lưu Kịch bản.

Trong hộp thoại Tên kịch bản, hãy nhập tên cho kịch bản và sau đó bấm OK.
Excel đưa bạn trở lại hộp thoại Kết quả giải.

Chọn tùy chọn Giải pháp Keep Solver.
Nếu bạn không muốn chấp nhận kết quả, hãy chọn tùy chọn Khôi phục Giá trị Gốc để thay thế.

Bấm OK.