Bài toán tối ưu hóa trên excel hay năm 2024

Vấn đề tối ưu hóa là những vấn đề trong quá trình làm bài tập, giải bài tập hay làm luận văn, tiểu luận mà chúng ta gặp phải trong nhiều môn học thuộc kỹ thuật, khoa học, kinh tế. Quá trình tối ưu hoá là tìm ra phương pháp hiệu quả nhất, sử dụng các nguồn lực hạn chế để đạt được mục tiêu của tình huống. Các vấn đề tối đa hóa lợi nhuận, tối ưu danh mục đầu tư như trong môn học Quản trị Danh Mục Đầu tư; Giảm thiểu chi phí, giảm thiểu tổng quãng đường đã đi như trong môn học Quản trị chuỗi cung ứng; Thiết kế mạng lưới cung ứng....

Một số ví dụ về bài toán tối ưu mà các bạn thường gặp như sau:

1. Đầu tư: Tối ưu hóa danh mục đầu tư - Mô hình Markowitz: Phân bổ tiền vào cổ phiếu để giảm thiểu rủi ro cho tỷ lệ hoàn vốn mục tiêu - với phương sai và hiệp phương sai đã biết hoặc được tính toán.
2. Sản xuất sản phẩm: Xác định số lượng sản phẩm của từng loại để lắp ráp từ các bộ phận nhất định để tối đa hóa lợi nhuận trong khi không vượt quá số lượng bộ phận có sẵn trong kho.
3. Mô hình vận chuyển trong môn Quản trị chuỗi cung ứng: Xác định có bao nhiêu sản phẩm để vận chuyển từ mỗi nhà máy đến từng kho, hoặc từ mỗi nhà máy đến từng kho và trực tiếp đến từng khách hàng cuối, để giảm thiểu chi phí vận chuyển trong khi đáp ứng nhu cầu kho và không vượt quá nguồn cung của nhà máy.

Phương pháp chung để giải quyết bài toán tối ưu

Đối với bài toán đã cho, chúng ta sẽ xây dựng một mô tả toán học gọi là mô hình toán học để biểu diễn tình huống. Mô hình để tối ưu bao gồm các thành phần sau:

Đặt tên biến quyết định: Các biến được biểu diễn bằng các ký hiệu như X1, X2, X3, ..Xn. Các biến này đại diện cho số lượng không xác định (số lượng mặt hàng để sản xuất, số tiền để đầu tư và vv). • Hàm mục tiêu: Mục tiêu của bài toán được biểu thị dưới dạng biểu thức toán học trong các biến quyết định. Mục tiêu có thể là tối đa hóa lợi nhuận, giảm thiểu chi phí, khoảng cách, thời gian, v.v. • Các hàm ràng buộc: Các hạn chế hoặc yêu cầu của vấn đề được thể hiện dưới dạng bất đẳng thức hoặc phương trình trong các biến quyết định.Nếu mô hình bao gồm hàm mục tiêu tuyến tính và các ràng buộc tuyến tính trong các biến quyết định, thì nó được gọi là mô hình tối ưu tuyến tính. Như vậy tối ưu tuyến tính là tối ưu mô hình với hàm mục tiêu tuyến tính và các ràng buộc tuyến tính. Thuật toán Simplex được phát triển bởi Dantzig (1963) được sử dụng để giải quyết các vấn đề tối ưu tuyến tính.

Một số ví dụ sử dụng Excel Solver

Trong bài viết này, Hocthue.net trình bày cách sử dụng mô hình hóa bảng tính và Excel Solver để giải quyết các vấn đề tối ưu tuyến tính. Bây giờ Hocthue.net trình bày cách sử dụng mô hình hóa sử dụng Excel để tìm giải pháp tối ưu cho các vấn đề tối ưu hóa.

Ở mức đơn giản, nếu mô hình có hai biến, phương pháp đồ thị có thể được sử dụng để giải mô hình. Rất ít vấn đề trong thế giới thực chỉ liên quan đến hai biến. Đối với các vấn đề có nhiều hơn hai biến, chúng ta cần sử dụng các kỹ thuật phức tạp để tìm ra giải pháp tối ưu. Cách tiếp cận bảng tính và Solver làm cho việc giải các bài toán tối ưu hóa trở thành một nhiệm vụ đơn giản cho những sinh viên trong quá trình làm bài tập, làm tiểu luận liên quan đến tình huống tối ưu tuyến tính.

Bước đầu tiên là tổ chức bảng tính để đại diện cho mô hình. Học Thuê.net sử dụng các ô riêng biệt để biểu diễn các biến quyết định, tạo công thức trong một ô để biểu diễn hàm mục tiêu và tạo công thức trong một ô cho mỗi ràng buộc bên trái. Khi mô hình được triển khai trong bảng tính, bước tiếp theo là sử dụng Solver để tìm giải pháp. Trong Solver, chúng ta cần xác định vị trí (ô) của hàm mục tiêu, các biến quyết định, bản chất của hàm mục tiêu (tối đa hóa / tối thiểu hóa) và các ràng buộc.

Ví dụ 1: Bài toàn vận tải đơn giản:

Bài toán được mô tả với dữ liệu đơn giản ở các bảng ở hình bên dưới như sau:

Ở Bảng 1:Dữ liệu gồm có : Bên cung gồm 4 đơn vị là A, B, C, D với lượng hàng tương ứng 200... 500. Bên Cầu gồm có 6 khách hàng KH1..KH5.

Ở Bảng 2: Kết quả tối ưu dựa trên sử dụng Solver của Excel với các điều kiện sau:

Hàm mục tiêu (objective: Tối thiểu hoá chi phí ở I32 (hàng cuối cùng trong hình bên dưới).

Ô thay đổi: Các ô màu vàng bên dưới.

Hàm ràng buộc:

- Bên gửi <=Bên cung

- Bên nhận = Bên gửi.

Bảng bài toán vận tải tối ưu với chi phí thấp nhất.

Ví dụ 2: Tối ưu hoá danh mục đầu tư sử dụng Solver theo phương pháp Markowitz.

Bài toán ở đây là nhà đầu tư muốn đầu tư 5 mã chứng khoán với kỳ vọng lợi nhuận đã có. Tuy nhiên câu hỏi đặt ra là tỷ lệ mỗi mã cổ phiếu bao nhiêu cho ít rủi ro nhất. Rủi ro ở đây được đo lường bằng phương sai nhé.

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 đặc 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

Solver Add-in trong Excel là một công cụ được thêm bởi tính năng Add in trong Excel cho phép bạn giải quyết các vấn đề tối ưu hóa và tìm ra giải pháp tốt nhất dựa trên các ràng buộc đã cho.

Để 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.

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.

Để tìm hiểu 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 học Excel nâng cao tại Gitiho:

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.

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 NameCellsUnitProfitC4:E4OrderSizeC12:E12ResourcesUsedG7:G8ResourcesAvailableI7:I8TotalProfitI12

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

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.

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.

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

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.

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:

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.