Tối ưu hóa với Excel Solver

Solver là một chương trình bổ trợ của Microsoft Excel mà bạn có thể sử dụng để tối ưu hóa trong phân tích những gì xảy ra.

Theo O'Brien và Marakas, optimization analysislà một phần mở rộng phức tạp hơn của phân tích tìm kiếm mục tiêu. Thay vì đặt giá trị mục tiêu cụ thể cho một biến, mục tiêu là tìm giá trị tối ưu cho một hoặc nhiều biến mục tiêu, theo những ràng buộc nhất định. Sau đó, một hoặc nhiều biến khác được thay đổi liên tục, tuân theo các ràng buộc đã chỉ định, cho đến khi bạn phát hiện ra các giá trị tốt nhất cho các biến đích.

Trong Excel, bạn có thể sử dụng Solver để tìm một optimal value (tối đa hoặc tối thiểu hoặc một giá trị nhất định) cho một công thức trong một ô được gọi là ô mục tiêu, tuân theo các ràng buộc hoặc giới hạn nhất định, trên các giá trị của các ô công thức khác trên trang tính.

Điều này có nghĩa là Bộ giải làm việc với một nhóm ô được gọi là biến quyết định được sử dụng để tính toán các công thức trong ô mục tiêu và ô ràng buộc. Solver điều chỉnh các giá trị trong các ô biến quyết định để thỏa mãn các giới hạn trên các ô ràng buộc và tạo ra kết quả bạn muốn cho ô mục tiêu.

Bạn có thể sử dụng Solver để tìm ra các giải pháp tối ưu cho các vấn đề đa dạng như -

Xác định hỗn hợp sản phẩm hàng tháng cho một đơn vị sản xuất thuốc tối đa hóa lợi nhuận.
Lập kế hoạch cho lực lượng lao động trong một tổ chức.
Giải quyết các vấn đề về giao thông vận tải.
Lập kế hoạch tài chính và ngân sách.

Kích hoạt Add-in Solver

Trước khi bạn tiếp tục tìm giải pháp cho vấn đề với Solver, hãy đảm bảo rằng Solver Add-in được kích hoạt trong Excel như sau:

Nhấp vào tab DATA trên Ribbon. CácSolver lệnh sẽ xuất hiện trong nhóm Phân tích như hình dưới đây.

Trong trường hợp bạn không tìm thấy lệnh Solver, hãy kích hoạt nó như sau:

Nhấp vào tab TỆP.
Nhấp vào Tùy chọn trong ngăn bên trái. Hộp thoại Tùy chọn Excel xuất hiện.
Nhấp vào Bổ trợ trong ngăn bên trái.
Chọn Phần bổ trợ Excel trong hộp Quản lý và nhấp vào Bắt đầu.

Hộp thoại Phần bổ trợ xuất hiện. Kiểm traSolver Add-invà nhấp vào Ok. Bây giờ, bạn sẽ có thể tìm thấy lệnh Solver trên Ribbon trong tab DATA.

Các phương pháp giải được sử dụng bởi Solver

Bạn có thể chọn một trong ba phương pháp giải sau đây mà Excel Solver hỗ trợ, dựa trên loại vấn đề -

LP Simplex

Dùng cho các bài toán tuyến tính. ASolver mô hình là tuyến tính trong các điều kiện sau:

Ô đích được tính bằng cách cộng các số hạng của dạng (ô thay đổi) * (hằng số) với nhau.
Mỗi ràng buộc thỏa mãn yêu cầu mô hình tuyến tính. Điều này có nghĩa là mỗi ràng buộc được đánh giá bằng cách cộng các số hạng của dạng (ô đang thay đổi) * (hằng số) và so sánh các tổng với một hằng số.

Độ dốc giảm tổng quát (GRG) phi tuyến

Dùng cho các bài toán phi tuyến trơn. Nếu ô đích, bất kỳ ràng buộc nào của bạn hoặc cả hai đều chứa tham chiếu đến việc thay đổi ô không thuộc dạng (ô thay đổi) * (hằng số), bạn có một mô hình phi tuyến.

Tiến hóa

Hiểu Đánh giá Người giải quyết

Bộ giải yêu cầu các tham số sau:

Các ô biến quyết định
Ô giới hạn
Ô mục tiêu
Phương pháp giải quyết

Đánh giá người giải quyết dựa trên những điều sau:

Các giá trị trong ô biến quyết định bị giới hạn bởi các giá trị trong ô ràng buộc.
Việc tính toán giá trị trong ô mục tiêu bao gồm các giá trị trong các ô biến quyết định.
Bộ giải sử dụng Phương pháp giải đã chọn để dẫn đến giá trị tối ưu trong ô mục tiêu.

Xác định vấn đề

Giả sử bạn đang phân tích lợi nhuận của một công ty sản xuất và bán một sản phẩm nhất định. Bạn được yêu cầu tìm số tiền có thể chi cho quảng cáo trong hai quý tiếp theo với mức tối đa là 20.000. Mức độ quảng cáo trong mỗi quý ảnh hưởng đến những điều sau:

Số lượng đơn vị bán ra, gián tiếp xác định doanh thu bán hàng.
Các chi phí liên quan, và
Lợi nhuận.

Bạn có thể tiến hành xác định vấn đề là -

Tìm chi phí đơn vị.
Tìm chi phí quảng cáo trên mỗi Đơn vị.
Tìm đơn giá.

Tiếp theo, đặt các ô cho các phép tính bắt buộc như được đưa ra bên dưới.

Như bạn có thể quan sát, các tính toán được thực hiện cho Quý 1 và Quý 2 đang được xem xét là:

Số lượng căn có thể bán trong Quý 1 là 400 căn và Quý 2 là 600 (ô - C7 và D7).
Giá trị ban đầu cho ngân sách quảng cáo được đặt là 10000 mỗi Quý (Ô - C8 và D8).
Số đơn vị bán được phụ thuộc vào chi phí quảng cáo trên mỗi đơn vị và do đó là ngân sách cho quý / Trước. Chi phí cho mỗi đơn vị. Lưu ý rằng chúng tôi đã sử dụng hàm Min để cẩn thận để thấy rằng không. trong tổng số đơn vị được bán trong <= no. trong số các đơn vị có sẵn. (Các ô - C9 và D9).
Doanh thu được tính theo Đơn giá * Số đơn vị đã bán (Ô - C10 và D10).
Chi phí được tính theo Đơn giá * Số lượng Đơn vị Có sẵn + Ứng trước. Chi phí cho quý đó (Ô - C11 và D12).
Lợi nhuận là Doanh thu - Chi phí (Ô C12 và D12).
Tổng lợi nhuận là Lợi nhuận trong Quý 1 + Lợi nhuận trong Quý 2 (Ô - D3).

Tiếp theo, bạn có thể đặt các thông số cho Bộ giải như dưới đây:

Như bạn có thể quan sát, các thông số cho Solver là:

Ô mục tiêu là ô D3 chứa Tổng lợi nhuận mà bạn muốn tối đa hóa.
Các ô biến Quyết định là C8 và D8 chứa ngân sách cho hai quý - Quý 1 và Quý 2.
Có ba ô Ràng buộc - C14, C15 và C16.
- Ô C14 chứa tổng ngân sách là để đặt giới hạn 20000 (ô D14).
- Ô C15 có chứa không. của số đơn vị bán được trong Quý 1 là đặt giới hạn <= no. trong số các đơn vị có sẵn trong Quý 1 (ô D15).
- Ô C16 có chứa không. của các đơn vị bán được trong Quý 2 là đặt giới hạn <= no. trong số các đơn vị có sẵn trong Quý 2 (ô D16).

Giải quyết vấn đề

Bước tiếp theo là sử dụng Solver để tìm ra giải pháp như sau:

Step 1- Chuyển đến DATA> Analysis> Solver trên Ribbon. Hộp thoại Solver Parameters xuất hiện.

Step 2 - Trong hộp Đặt Mục tiêu, chọn ô D3.

Step 3 - Chọn Max.

Step 4 - Chọn dải C8: D8 trong By Changing Variable Cells cái hộp.

Step 5 - Tiếp theo, nhấp vào nút Thêm để thêm ba ràng buộc mà bạn đã xác định.

Step 6- Hộp thoại Add Constraint xuất hiện. Đặt ràng buộc cho tổng ngân sách như được cung cấp bên dưới và nhấp vào Thêm.

Step 7- Đặt ràng buộc cho tổng số không. trong số các đơn vị đã bán trong Quý 1 như được cung cấp bên dưới và nhấp vào Thêm.

Step 8- Đặt ràng buộc cho tổng số không. trong số các căn hộ đã bán trong Quý 2 như được đưa ra bên dưới và bấm OK.

Hộp thoại Solver Parameters xuất hiện với ba ràng buộc được thêm vào trong hộp –Đối tượng vào các ràng buộc.

Step 9 - Trong Select a Solving Method chọn Simplex LP.

Step 10- Nhấp vào nút Giải quyết. Hộp thoại Giải kết quả xuất hiện. Lựa chọnKeep Solver Solution và bấm OK.

Kết quả sẽ xuất hiện trong trang tính của bạn.

Như bạn có thể quan sát, giải pháp tối ưu tạo ra tổng lợi nhuận tối đa, tuân theo các ràng buộc nhất định, được tìm thấy là:

Tổng lợi nhuận - 30000.
Tiến lên Ngân sách cho Quý 1 - 8000.
Tiến lên Ngân sách cho Quý 2 - 12000.

Bước qua các Giải pháp dùng thử Solver

Bạn có thể bước qua các giải pháp dùng thử của Solver, xem kết quả lặp lại.

Step 1 - Nhấp vào nút Options trong hộp thoại Solver Parameters.

Các Options hộp thoại xuất hiện.

Step 2 - Chọn hộp Hiển thị kết quả lặp lại và bấm OK.

Step 3 - Sự Solver Parametershộp thoại xuất hiện. Nhấp chuộtSolve.

Step 4 - Sự Show Trial Solution hộp thoại xuất hiện, hiển thị thông báo - Solver paused, current solution values displayed on worksheet.

Như bạn có thể quan sát, các giá trị lặp hiện tại được hiển thị trong các ô làm việc của bạn. Bạn có thể ngăn Bộ giải chấp nhận kết quả hiện tại hoặc tiếp tục cùng Bộ giải tìm giải pháp trong các bước tiếp theo.

Step 5 - Nhấp vào Tiếp tục.

Các Show Trial Solutionhộp thoại xuất hiện ở mỗi bước và cuối cùng sau khi tìm ra giải pháp tối ưu, hộp thoại Solver Results xuất hiện. Trang tính của bạn được cập nhật ở mọi bước, cuối cùng hiển thị các giá trị kết quả.

Lưu các lựa chọn của trình giải quyết

Bạn có các tùy chọn lưu sau cho các vấn đề mà bạn giải quyết với Solver -

Bạn có thể lưu các lựa chọn cuối cùng trong hộp thoại Tham số bộ giải với một trang tính bằng cách lưu sổ làm việc.
Mỗi trang tính trong sổ làm việc có thể có các lựa chọn Bộ giải riêng và tất cả chúng sẽ được lưu khi bạn lưu sổ làm việc.
Bạn cũng có thể xác định nhiều vấn đề trong một trang tính, mỗi vấn đề có các lựa chọn Trình giải quyết riêng. Trong trường hợp này, bạn có thể tải và lưu các sự cố riêng lẻ bằng hộp thoại Tải / Lưu trong Tham số bộ giải.
- Nhấn vào Load/Savecái nút. Hộp thoại Load / Save xuất hiện.
- Để lưu mô hình vấn đề, hãy nhập tham chiếu cho ô đầu tiên của một dải ô trống theo chiều dọc mà bạn muốn đặt mô hình vấn đề. Nhấp vào để lưu.

Mô hình vấn đề (bộ Tham số giải) xuất hiện bắt đầu từ ô mà bạn đã cung cấp làm tham chiếu.

Để tải mô hình vấn đề, hãy nhập tham chiếu cho toàn bộ phạm vi ô chứa mô hình vấn đề. Sau đó, nhấp vào nút Nạp.