Solver Add-in là 1 trong công cụ bao gồm sẵn trong Excel cung cấp các lệnh và những tính năng cấu hình thiết lập để giải quyết và xử lý các vụ việc quyết định. Solver Add-inđặc biệt bổ ích khi giải quyết các việc tối ưu, ví như tối thiểu đưa ra phí, về tối đa lợi nhuận,...

Bạn đang xem: Giải bài toán tối ưu bằng excel

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

Xây dựng hàm kim chỉ nam (Objective Function)Xây dựng những ràng buộc (Constraints)Tổ chức tài liệu trên bảng tính Excel
Sử dụng Solver nhằm tìm phương án buổi tối ưu

Trong nội dung bài viết này, chúng tôi sẽ phía dẫn các bạn cách mua và áp dụng công gắng Solver Add-in trong Excel thông sang một ví dụ đối chọi giản.


XEM nhanh BÀI VIẾT


Cách download Solver Add-in vào Excel

Solver Add-in vào Excel là 1 trong những công nuốm được thêm do tính năng địa chỉ cửa hàng in vào Excel cho phép bạn giải quyết các vấn đề tối ưu hóa cùng tìm ra giải pháp tốt độc nhất dựa trên các ràng buộc sẽ cho.

Để thiết lập phần hỗ trợ solver, bạn có thể làm theo các bước sau:

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

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

*

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

4. Chúng ta cũng có thể tìm thấy Solver bên trên tab Data, trong đội Analyze.

*
*

Để xem thêm về cách xây dựng mô hình bằng Solver Add-in, bạn có thể tham khảo khóa đào tạo Excel nâng cấp tại Gitiho:


*

EXG01: tuyệt đỉnh công phu Excel | khóa huấn luyện Excel online tự cơ phiên bản đến cải thiện G-LEARNING
499,000đ 799,000đ
Đăng cam kết học tập thử

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

Các mô hình bọn họ sẽ áp dụng công thế 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 giám sát số lượng đặt hàng của mỗi thành phầm (bicycles, mopeds cùng child seats) làm sao để cho tổng lợi nhuận có được cao nhất.

*

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

Các ra quyết định được tiến hành là gì? Đối với câu hỏi này, họ cần Excel nhằm tìm ra số lượng đặt đơn hàng của mỗi thành phầm (bicycles, mopeds và child seats).Những ràng buộc đối với những ra quyết định này là gì? các hạn chế ở đây là số vốn cùng kho áp 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) gồm sẵn. Ví dụ, từng chiếc xe đạp sử dụng 300 đơn vị vốn cùng 0,5 đơn vị lưu trữ.Thước đo toàn diện và tổng thể về hiệu suất của những quyết định này là gì? Thước đo toàn diện và tổng thể của vận động là tổng roi của bố sản phẩm, bởi vậy phương châm là buổi tối đa hóa con số này.

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

Range Name
CellsUnit
Profit
C4:E4Order
Size
C12:E12Resources
Used
G7:G8Resources
Available
I7:I8Total
Profit
I12

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

*

Trong đó:

Tính hàng tồn kho bằng hàm Sumproduct cùng với range C8: E8 với Order
Size.Tổng lợi nhuận được xem bằng sản phẩm của Unit
Profit với Order
Size.

Với phương pháp này, chúng ta cũng có thể sử dụng cách thức thử Đúng - sai để xử lý vấn đề. Ví dụ: nếu họ đặt mua 20 xe đạp, 40 xe cộ máy với 100 ghế trẻ nhỏ thì tổng lượng tài nguyên sử dụng không được vượt quá con số tài nguyên hiện có. Chiến thuật này có tổng roi là 19000.

*

Tuy nhiên để tìm giải pháp nhanh nệm hơn, bọn họ sẽ mày mò sử dụng Excel Solver

Để tìm kiếm ra chiến thuật tối ưu, bạn hãy thực hiện theo quá trình sau.

1. Trên tab Data, trong team Analyze, click vào Solver.

Nhập các thông số kỹ thuật của solver. Tác dụng trả về đã phải y như hình dưới.

*

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

*

2. Nhập Total
Profit mang lại Objective.

Xem thêm: Mẹo hay giúp ghi nhớ các công thức hóa học khó, 1001 phương trình phản ứng hóa học phức tạp nhất

3. Nhấp vào Max.

4. Nhập Order
Size mang đến Changing Variable Cells (các ô hoàn toàn có thể thay đổi).

5. Nhấp vào showroom để thêm những ràng buộc.

*

6. Soát sổ "Make Unconstrained Variables Non-Negative" và chọn "Simplex LP".

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

Kết đúng như sau:

*

Kết luận: 94 xe đạp điện và 54 xe thiết bị là chiến thuật tối ưu nhất. Phương án này sẽ mang lại lợi nhuận về tối đa là 25600. Phương án này sử dụng toàn bộ các tài nguyên có sẵn.

Hy vọng, qua ví dụ đơn giản trên, bạn đã hiểu phương pháp sử dụng công cụSolver trong Excel nhằm giải việc tối ưu. Kề bên đó, nhằm không bỏ dở những mẹo với thủ thuật tin học văn phòng hữu dụng khác, hãy gia nhập Gitiho tức thì hôm nay.

Lưu ý: Chúng tôi ước ao cung cấp cho mình nội dung trợ giúp tiên tiến nhất ở chính ngữ điệu của bạn, nhanh nhất có thể có thể. Trang này được dịch tự động hóa nên hoàn toàn có thể chứa các lỗi về ngữ pháp hoặc nội dung không chủ yếu xác. Mục đích của chúng tôi là khiến cho nội dung này trở đề xuất hữu ích cùng với bạn. Vui lòng cho chúng tôi biết ở dưới trang này rằng tin tức có hữu ích với chúng ta không? sau đây là bài viết bằng giờ Anh để bạn xem thêm dễ hơn.


Bài viết này đề cập tới sự việc dùng cỗ giải, hãy thêm trong hocfull.com Excel chương trình bạn cũng có thể dùng đến phân tích what-if, để xác minh một tập đúng theo các thành phầm tối ưu.

Làm rứa nào tôi có thể xác định trộn thành phầm hàng tháng buổi tối đa hóa lợi nhuận?

Các doanh nghiệp thường cần phải xác định số lượng mỗi sản phẩm để tạo ra trên cửa hàng hàng tháng. Trong biểu mẫu đơn giản nhất của nó, sản phẩm phối kết hợp các sự việc liên quan cho làm nuốm nào để khẳng định giá trị của mỗi sản phẩm sẽ được chế tạo trong một mon để tối đa hóa lợi nhuận. Thành phầm hợp thường đề xuất tuân theo để số lượng giới hạn sau đây:

Sản phẩm thích hợp không thể thực hiện tài nguyên khác rộng sẵn dùng.

Không có một yêu cầu giới hạn cho từng sản phẩm. Shop chúng tôi không thể tạo thành nhiều sản phẩm trong một tháng hơn ra yêu cầu, do sản xuất quá mức cần thiết là tiêu tốn lãng phí (ví dụ, một ma túy dễ dàng hỏng).

Hãy xử lý ngay ví dụ dưới đây về sự việc hợp sản phẩm. Chúng ta có thể tìm thấy giải pháp cho vấn đề này vào tệp Prodmix.xlsx, Hiển thị trong hình 27-1.

*
đưa sử chúng tôi làm câu hỏi cho một công ty ma túy mang lại sáu sản phẩm khác nhau ở cây của họ. Cung ứng của từng sản phẩm này yêu ước lao hễ và nguyên liệu. Chỉ đến hàng 4 vào hình 27-1 tiếng lao động quan trọng để tạo nên một thăng từng sản phẩm và hàng 5 cân nguyên liệu cần thiết để tạo ra một thăng từng sản phẩm. Ví dụ: sản xuất một thăng sản phẩm 1 yêu mong sáu giờ lao cồn và 3.2 £ của nguyên liệu. Đối với từng ma túy, giá chỉ trên từng thăng được hỗ trợ trong sản phẩm 6, ngân sách chi tiêu đơn vị mỗi thăng được cung ứng trong sản phẩm 7 và đóng góp lợi nhuận cho từng thăng được cung ứng ở sản phẩm 9. Ví dụ, sản phẩm 2 cung cấp $11,00 cho mỗi thăng, làm cho phát sinh phí đơn vị của $5,70 cho từng thăng và góp phần $5,30 lợi nhuận cho từng thăng. Yêu cầu của tháng cho mỗi ma túy được cung cấp trong mặt hàng 8. Ví dụ, yêu cầu về thành phầm 3 là 1041 £. Mon này, 4500 giờ đồng hồ lao đụng và 1600 £ của nguyên vật liệu sẵn dùng. Làm vậy nào rất có thể công ty này buổi tối đa hóa lợi nhuận mặt hàng tháng?

Nếu chúng tôi biết điều gì về Excel cỗ giải, chúng tôi sẽ tiến công vấn đề này bởi xây dựng một trang tính nhằm theo dõi những cách cần sử dụng lợi nhuận với tài nguyên links với thành phầm hợp. Sau đó bọn họ dùng bạn dạng dùng thử cùng lỗi để biến đổi tập hợp sản phẩm để về tối ưu hóa lợi nhuận nhưng mà không dùng nhiều lao đụng hoặc nguyên liệu hơn sẵn gồm và không có bất kỳ ma túy vượt thừa yêu ước sản xuất. Chúng tôi dùng bộ giải trong tiến trình này chỉ sinh sống giai đoạn dùng thử và lỗi. Cơ bản, bộ giải là một công cụ tối ưu hóa tuyệt vời nhất sẽ tìm kiếm bản dùng thử và lỗi.

Một khóa để xử lý vấn đề hợp thành phầm là một cách tác dụng tính toán áp dụng tài nguyên cùng lợi nhuận liên kết với bất kỳ kết phù hợp các sản phẩm đã cho. Một công cụ đặc biệt quan trọng mà chúng tôi có thể dùng để thực hiện đo lường này là hàm SUMPRODUCT. Hàm SUMPRODUCT nhân những giá trị tương ứng trong phạm vi ô cùng trả về tổng của những giá trị này. Mỗi phạm vi ô được sử dụng trong một đánh giá hàm SUMPRODUCT phải có cùng kích thước, mà ý niệm mà bạn cũng có thể dùng SUMPRODUCT với hai hàng hoặc nhị cột, nhưng không tồn tại một cột với một hàng.

Làm ví dụ về cách shop chúng tôi có thể áp dụng hàm SUMPRODUCT trong thành phầm của công ty chúng tôi trộn ví dụ, hãy thử để tính thực hiện tài nguyên của bọn chúng tôi. Biện pháp dùng lao cồn của cửa hàng chúng tôi được thống kê giám sát bằng

(Lao động sử dụng cho mỗi thăng ma túy 1) *(Drug 1 pounds produced) + (lao động sử dụng cho từng thăng ma túy 2) * (ma túy 2 cân sản xuất) +... (Lao cồn sử dụng cho từng thăng ma túy 6) * (ma túy 6 cân nặng sản xuất)

Chúng tôi rất có thể tính lao động thực hiện nhiều tedious luôn luôn là D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Tương tự, áp dụng nguyên liệu rất có thể được tính cho việc đó dưới dạng D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Tuy nhiên, nhập các công thức trong một trang tính dành riêng cho sản phẩm sáu là tốn. Giả sử sẽ mất bao lâu nếu khách hàng đang thao tác làm việc với một công ty sản xuất, ví dụ, các thành phầm 50 tại cây của họ. Một cách dễ ợt hơn nhiều để tính lao đụng và cách dùng vật liệu là xào luộc từ D14 D15 phương pháp SUMPRODUCT($D$2:$I$2,D4:I4). Công thức này tính D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (là giải pháp dùng lao động của chúng tôi) nhưng thuận tiện hơn để nhập! lưu ý rằng tôi sử dụng dấu $ với phạm vi D2:I2 nhằm khi tôi xào nấu công thức tôi vẫn chụp hợp thành phầm từ sản phẩm 2. Phương pháp trong ô D15 tính nguyên liệu sử dụng.

Một bí quyết tương tự, roi của cửa hàng chúng tôi được khẳng định bởi

(Ma túy lợi nhuận 1 cho mỗi thăng) * (sản xuất bảng ma túy 1) + (ma túy 2 lợi nhuận cho mỗi thăng) * (ma túy 2 cân sản xuất) +... (Ma túy 6 lợi nhuận cho mỗi thăng) * (ma túy 6 cân nặng sản xuất)

Lợi nhuận dễ dàng được giám sát và đo lường trong ô D12 với cách làm SUMPRODUCT(D9:I9,$D$2:$I$2).

Bây giờ shop chúng tôi có thể xác minh ba cấu phần của tập hợp thành phầm của chúng tôi tìm phát âm mô hình.

Ô đích.Mục tiêu của cửa hàng chúng tôi là về tối đa hóa lợi tức đầu tư (tính cho việc đó trong ô D12).

Thay đổi ô.Số lượng cân sản xuất của từng thành phầm (được liệt kê trong phạm vi ô D2:I2)

Ràng buộc. chúng tôi có ràng buộc thao tác làm việc sau:

Không áp dụng nhiều lao cồn hoặc nguyên vật liệu hơn sẵn dùng. Bao gồm nghĩa là, những giá trị trong ô D14:D15 (tài nguyên được sử dụng) phải nhỏ hơn hoặc bằng những giá trị vào ô F14:F15 (tài nguyên sẵn dùng).

Không tạo ra nhiều trong một ma túy hơn trong yêu thương cầu. Có nghĩa là, những giá trị trong ô D2:I2 (bảng tiếp tế của mỗi ma túy) phải nhỏ dại hơn hoặc bằng yêu cầu cho từng ma túy (được liệt kê vào ô D8:I8).

Chúng tôi quan trọng tạo ra một số trong những âm ma túy bất kỳ.

Tôi sẽ trả lời bạn phương pháp để nhập ô đích, thay đổi ô, và ràng buộc vào bộ giải. Sau đó tất cả bạn cần làm là nhấn vào nút giải quyết và xử lý để search một tập hợp sản phẩm tối đa hóa lợi nhuận!