Dùng excel giải bài toán tìm giá trị tối đa năm 2024
Excel có một công cụ được gọi là solver 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. Show
Tải Solver Add-inĐể tải solver add-in, bạn hãy thực hiện các bước sau. 1. Trên tab File, click Options. 2. Trong Add-ins, chọn Solver Add-in và click vào nút Go. 3. Kiểm tra Solver Add-in và click OK. 4. Bạn có thể tìm thấy Solver trên tab Data, trong nhóm Analyze. Formulate the ModelModel chúng ta sẽ solve trông như sau trong Excel. 1. Để hình thành mô hình linear programming, bạn hãy trả lời ba câu hỏi sau.
2. Để làm cho mô hình dễ hiểu hơn, hãy tạo các named ranges. 3. Insert ba hàm SUMPRODUCT sau đây. Giải thích: Số vốn được sử dụng tương đương với sản phẩm của phạm vi C7: E7 và OrderSize. Lượng bộ nhớ được sử dụng bằng với sản phẩm của phạm vi C8: E8 và OrderSize. Tổng lợi nhuận bằng sản phẩm thu được của UnitProfit và OrderSize. Trial and ErrorVới công thức này, việc phân tích bất kỳ trial solution nào cũng sẽ trở nên dễ dàng hơn. Ví dụ: nếu chúng ta đặt mua 20 xe đạp, 40 xe gắn 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ó. Đối với giải pháp này sẽ thu được tổng lợi nhuận là 19000. Không nhất thiết phải sử dụng trial và error. Tiếp theo, chúng ta sẽ mô tả cách sử dụng Excel Solver để nhanh chóng tìm ra các giải pháp tối ưu. Solve the ModelĐể tìm ra giải pháp tối ưu, bạn hãy thực hiện các bước sau. 1. Trên tab Data, trong nhóm Analyze, click Solver. Nhập các thông số của solver (đọc tiếp). Kết quả cần phải phù hợp với hình dưới đây. Bạn có thể chọn nhập range names hoặc nhấp vào các ô spreadsheet. 2. Nhập TotalProfit cho Objective. 3. Click Max. 4. Nhập OrderSize cho Changing Variable Cells. 5. Click Add để nhập các ràng buộc sau. 6. Kiểm tra "Make Unconstrained Variables Non-Negative" và chọn "Simplex LP". 7. Cuối cùng, click Solve. Kết quả: Giải pháp tối ưu: Kết luận: Đặt 94 xe đạp và 54 xe gắn máy sẽ là giải pháp tối ưu. Giải pháp này cho ra lợi nhuận tối đa là 25600 với việc sử dụng tất cả các nguồn lực có sẵn. Bạn hãy thử thực hành bằng cách áp dụng vào dữ liệu của công ty bạn nào. Transportation ProblemSử dụng solver trong Excel để tìm số lượng đơn vị cần vận chuyển từ mỗi nhà máy đến từng khách hàng để giảm thiểu chi phí. Formulate the ModelMô hình chúng ta sẽ solve trông như sau trong Excel. 1. Để hình thành mô hình transportation problem, bạn hãy trả lời ba câu hỏi sau:
2. Để làm cho mô hình dễ hiểu hơn, bạn hãy tạo các phạm vi named ranges. 3. Insert các chức năng sau. Giải thích: Hàm SUM sẽ tính toán tổng số hàng được vận chuyển từ mỗi nhà máy (Total Out) cho mỗi khách hàng (Total In). Tổng chi phí bằng sản phẩm của UnitCost và Shipments. Trial and ErrorVới công thức này, việc phân tích bất kỳ trial solution nào cũng trở nên dễ dàng hơn. Ví dụ: Nếu chúng ta vận chuyển:
Total Out bằng Supply và Total In bằng Demand. Giải pháp này có tổng chi phí là 27800. Không nhất thiết phải sử dụng trial và error. Tiếp theo, chúng ta sẽ mô tả cách sử dụng Excel Solver để nhanh chóng tìm ra giải pháp tối ưu. Solve the ModelĐể tìm ra giải pháp tối ưu, bạn hãy thực hiện các bước sau. 1. Trên tab Data, trong nhóm Analyze, click Solver. Nhập các thông số của solver (đọc tiếp). Kết quả sẽ phải phù hợp với hình dưới đây. Bạn có thể chọn nhập range names hoặc click vào các ô trong spreadsheet. 2. Nhập TotalCost cho Objective. 3. Click Min. 4. Nhập Shipments cho Changing Variable Cells. 5. Click Add để nhập ràng buộc sau. 6. Click Add để nhập ràng buộc sau. 7. Kiểm tra "Make Unconstrained Variables Non-Negative" và chọn "Simplex LP". 8. Cuối cùng, click Solve. Kết quả: Giải pháp tối ưu: Kết luận: Tối ưu nhất là vận chuyển:
Giải pháp này tốn chi phí tối thiểu là 26000 với tất cả các ràng buộc đều được thỏa mãn. Assignment ProblemSử dụng solver trong Excel, để tìm ra sự phân công dành cho mọi người trong các nhiệm vụ nhằm giảm thiểu chi phí. Formulate the ModelMô hình chúng ta sẽ solve trông như sau trong Excel. 1. Để hình thành mô hình assignment problem, bạn hãy trả lời ba câu hỏi sau:
2. Để làm cho mô hình dễ hiểu hơn, bạn hãy tạo các phạm vi named ranges. 3. Insert các chức năng sau. Giải thích: Các hàm SUM sẽ tính toán số lượng nhiệm vụ được giao cho một người và số lượng người được giao cho một nhiệm vụ. Tổng chi phí bằng kết quả của Cost và Assignment. Trial and ErrorVới công thức này, việc phân tích bất kỳ trial solution nào cũng trở nên dễ dàng hơn. Ví dụ: Nếu chúng ta chỉ định:
Nhiệm vụ được giao bằng Supply và Người được giao bằng Demand. Giải pháp này có tổng chi phí là 147. Không nhất thiết phải sử dụng trial và error. Tiếp theo, chúng ta sẽ mô tả cách sử dụng Excel Solver để nhanh chóng tìm ra giải pháp tối ưu. Solve the ModelĐể tìm ra giải pháp tối ưu, bạn hãy thực hiện các bước sau. 1. Trên tab Data, trong nhóm Analyze, click Solver. Nhập các thông số của solver (đọc tiếp). Kết quả phải phù hợp với hình dưới đây. Bạn có thể chọn nhập range names hoặc click vào các ô trong spreadsheet. 2. Nhập TotalCost cho Objective. 3. Click Min. 4. Nhập Assignment cho Changing Variable Cells. 5. Click Add để nhập ràng buộc sau. Lưu ý: các biến nhị phân là 0 hoặc 1. 6. Click Add để nhập ràng buộc sau. 7. Click Add để nhập ràng buộc sau. 8. Kiểm tra "Make Unconstrained Variables Non-Negative" và chọn "Simplex LP". 9. Cuối cùng, click Solve. Kết quả: Giải pháp tối ưu: Kết luận: Tối ưu nhất là giao
Giải pháp này tốn chi phí tối thiểu là 129 phù hợp với tất cả các ràng buộc. Shortest Path ProblemSử dụng solver trong Excel để tìm ra các phím tắt từ nút S đến nút T. Các Points trong mạng được gọi là các nút (S, A, B, C, D, E và T). Các Lines trong mạng được gọi là cung (SA, SB, SC, AC, ...). Formulate the ModelMô hình chúng ta sẽ giải quyết trông như sau trong Excel. 1. Để hình thành mô hình shortest path problem, bạn hãy trả lời ba câu hỏi sau:
2. Để làm cho mô hình dễ hiểu hơn, bạn hãy tạo các phạm vi named ranges. 3. Insert các chức năng sau. Giải thích: Các hàm SUMIF tính Net Flow của mỗi node. Đối với nút S, hàm SUMIF tính tổng các giá trị trong Go column với một "S" trong From column. Do đó, chỉ ô F4, F5 hoặc F6 có thể là 1 (one outgoing arc). Đối với nút T, hàm SUMIF tính tổng các giá trị trong Go column với một "T" trong From column. Do đó, chỉ ô F15, F18 hoặc F21 mới có thể là 1 (one ingoing arc). Đối với tất cả các nút khác, Excel sẽ tìm trong From và To column. Tổng khoảng cách bằng kết quả của Distance và Go. Trial and ErrorVới công thức này, việc phân tích bất kỳ trial solution nào cũng trở nên dễ dàng hơn. 1. Ví dụ, đường dẫn SBET có tổng khoảng cách là 16. Không nhất thiết phải sử dụng trial and error. Tiếp theo, chúng ta sẽ mô tả cách sử dụng Excel Solver để nhanh chóng tìm ra giải pháp tối ưu. Solve the ModelĐể tìm ra giải pháp tối ưu, bạn hãy thực hiện các bước sau. 1. Trên tab Data, trong nhóm Analyze, click Solver. Nhập các thông số của solver (đọc tiếp). Kết quả phải phù hợp với hình dưới đây. Bạn có thể chọn nhập range names hoặc click vào các ô trong spreadsheet. 2. Nhập TotalDistance cho Objective. 3. Click Min. 4. Nhập Go cho Changing Variable Cells. 5. Click Add để nhập ràng buộc sau. 6. Kiểm tra "Make Unconstrained Variables Non-Negative" và chọn "Simplex LP". 7. Cuối cùng, click Solve. Kết quả: Giải pháp tối ưu: Kết luận: SADCT là đường đi ngắn nhất có tổng quãng đường là 11. Maximum Flow ProblemSử dụng solver trong Excel để tìm ra các phím tắt từ nút S đến nút T. Các Points trong mạng được gọi là các nút (S, A, B, C, D, E và T). Các Lines trong mạng được gọi là cung (SA, SB, SC, AC, ...). Formulate the ModelMô hình chúng ta sẽ giải quyết trông như sau trong Excel. 1. Để hình thành mô hình maximum flow problem, bạn hãy trả lời ba câu hỏi sau:
2. Để làm cho mô hình dễ hiểu hơn, bạn hãy tạo các phạm vi named ranges. 3. Insert các chức năng sau. Giải thích: Các hàm SUMIF tính Net Flow của mỗi node. Đối với nút A, hàm SUMIF đầu tiên tính tổng các giá trị trong Flow column với một "A" trong From column (Flow Out). Hàm SUMIF thứ hai tính tổng các giá trị trong Flow column với một "A" trong cột To column (Flow In). Maximum Flow bằng giá trị trong ô I4, là flow out khỏi nút S. Vì nút A, B, C, D và E có Net Flow bằng 0 nên Flow Out từ nút S sẽ bằng Flow In của nút T. Trial and ErrorVới công thức này, việc phân tích bất kỳ trial solution nào cũng trở nên dễ dàng hơn. 1. Ví dụ: đường dẫn SADT với flow là 2. Đường dẫn SCT với flow là 4. Đường dẫn SBET với flow là 2. Các đường dẫn này cho tổng flow là 8. Không nhất thiết phải sử dụng trial and error. Tiếp theo, chúng ta sẽ mô tả cách sử dụng Excel Solver để nhanh chóng tìm ra giải pháp tối ưu. Solve the ModelĐể tìm ra giải pháp tối ưu, bạn hãy thực hiện các bước sau. 1. Trên tab Data, trong nhóm Analyze, click Solver. Nhập các thông số của solver (đọc tiếp). Kết quả phải phù hợp với hình dưới đây. Bạn có thể chọn nhập range names hoặc click vào các ô trong spreadsheet. 2. Nhập MaximumFlow cho Objective. 3. Click Max. 4. Nhập Flow cho Changing Variable Cells. 5. Click Add để nhập ràng buộc sau. 6. Click Add để nhập ràng buộc sau. 7. Kiểm tra "Make Unconstrained Variables Non-Negative" và chọn "Simplex LP". 8. Cuối cùng, click Solve. Kết quả: Giải pháp tối ưu: Kết luận:
Các đường dẫn này cho flow tối đa là 12. Capital InvestmentSử dụng solver trong Excel để tìm kiếm sự kết hợp giữa các khoản đầu tư vốn tối đa hóa total profit. Formulate the ModelMô hình chúng ta sẽ solve trông như sau trong Excel. 1. Để hình thành mô hình binary integer programming (BIP), bạn hãy trả lời ba câu hỏi sau:
3. Insert năm hàm SUMPRODUCT sau đây. Giải thích: ô K7 (số vốn được sử dụng) bằng tổng sản phẩm (sumproduct) của phạm vi C7: I7 và YesNo, ô K8 bằng tổng sản phẩm của phạm vi C8: I8 và YesNo,... Tổng lợi nhuận (Total Profit) bằng kết quả của lợi nhuận và YesNo. Trial and ErrorVới công thức này, việc phân tích bất kỳ trial solution nào cũng trở nên dễ dàng hơn. 1. Ví dụ, nếu chúng ta thực hiện đầu tư 1 và 2 vậy ràng buộc thứ hai bị vi phạm. 2. Ví dụ, nếu chúng ta thực hiện đầu tư 6 và 7, mà không thực hiện đầu tư 5 thì ràng buộc thứ tư sẽ bị vi phạm. 3. Tuy nhiên, bạn nên đầu tư 1, 5 và 6 để tất cả các ràng buộc đều được thỏa mãn. Không nhất thiết phải sử dụng trial and error. Tiếp theo, chúng ta sẽ mô tả cách sử dụng Excel Solver để nhanh chóng tìm ra giải pháp tối ưu. Solve the ModelĐể tìm ra giải pháp tối ưu, bạn hãy thực hiện các bước sau. 1. Trên tab Data, trong nhóm Analyze, click Solver. Nhập các thông số của solver (đọc tiếp). Kết quả phải phù hợp với hình dưới đây. 2. Nhập TotalProfit cho Objective. 3. Click Max. 4. Nhập YesNo cho Changing Variable Cells. 5. Click Add để nhập ràng buộc sau. 6. Click Add để nhập ràng buộc sau. Lưu ý: các biến nhị phân là 0 hoặc 1. 7. Kiểm tra "Make Unconstrained Variables Non-Negative" và chọn "Simplex LP". 8. Cuối cùng, click Solve. Kết quả: Giải pháp tối ưu: Kết luận: đầu tư 2, 4, 5 và 7 sẽ là tối ưu vì giải pháp này cho lợi nhuận tối đa là 146 với tất cả các ràng buộc đều được thỏa mãn. Sensitivity AnalysisSensitivity analysis (phương pháp phân tích độ nhạy) sẽ cung cấp cho bạn có cái nhìn tổng quát hơn về các giải pháp tối ưu khi bạn thay đổi các hệ số của mô hình. Sau khi solver tìm ra giải pháp, bạn có thể tạo báo cáo sensitivity. 1. Trước khi bạn click OK, hãy chọn Sensitivity từ phần Reports. Dưới đây, bạn có thể tìm thấy giải pháp tối ưu và báo cáo sensitivity. Tối ưu là bạn đặt 94 xe đạp và 54 xe gắn máy. Với giải pháp này bạn sẽ đạt được lợi nhuận tối đa là 25600 bằng việc sử dụng tất cả các nguồn lực có sẵn (93000 units vốn và 101 units lưu trữ). Bạn có thể tìm thấy những con số này trong Final Value column. Reduced CostGiảm chi phí sẽ cho chúng ta biết objective coefficients (unit profits) có thể tăng hoặc giảm bao nhiêu trước khi giải pháp tối ưu thay đổi. Nếu chúng ta tăng unit profit của Ghế trẻ em lên 20 chiếc vậy thì giải pháp tối ưu sẽ có sự thay đổi. 1. Với unit profit là 69, đặt 94 xe đạp và 54 xe gắn máy vẫn được xem là tối ưu. Dưới đây bạn có thể tìm thấy giải pháp tối ưu. 2. Với unit profit là 71, giải pháp tối ưu sẽ thay đổi. Kết luận: Ghế trẻ em chỉ có lãi nếu bạn bán được ít nhất 70 chiếc. Shadow PriceShadow prices sẽ cho chúng ta biết giải pháp tối ưu có thể tăng hoặc giảm bao nhiêu nếu chúng ta thay đổi các giá trị bên phải (tài nguyên có sẵn) bằng một unit. 1. Với 101 units of storage có sẵn, tổng lợi nhuận là 25600. Dưới đây, bạn có thể tìm thấy các giải pháp tối ưu. 2. Với 102 units of storage có sẵn, tổng lợi nhuận là 25700 (+100). Lưu ý: Shadow price là 100 cho tài nguyên này, đây được xem là đã đủ kỳ vọng với chúng ta rồi. Shadow price này chỉ có giá trị trong khoảng 101 - 23,5 và 101 + 54 (xem báo cáo sensitivity). System of Linear EquationsVí dụ này cho bạn thấy cách giải hệ phương trình tuyến tính trong Excel. Ví dụ, chúng ta có hệ phương trình tuyến tính sau: Trong ký hiệu ma trận, có thể được viết là AX = B Nếu tồn tại A-1 (nghịch đảo của A), chúng ta có thể nhân cả hai vế với A-1 để thu được X = A-1B. Để giải hệ phương trình tuyến tính này trong Excel, bạn hãy thực hiện các bước sau. 1. Sử dụng hàm MINVERSE để trả về ma trận nghịch đảo của A. Đầu tiên, bạn hãy chọn phạm vi B6: D8. Tiếp theo, bạn hãy chèn hàm MINVERSE được hiển thị bên dưới và kết thúc bằng cách nhấn CTRL + SHIFT + ENTER. Lưu ý: formula bar chỉ ra rằng các ô chứa công thức array. Do đó, bạn không thể xóa một kết quả duy nhất, mà để xóa kết quả, bạn hãy chọn phạm vi B6: D8 và nhấn Delete. 2. Sử dụng hàm MMULT để trả về tích của ma trận A-1 và B. Đầu tiên, bạn hãy chọn phạm vi G6: G8. Tiếp theo, bạn chèn hàm MMULT được hiển thị bên dưới. Kết thúc bằng cách nhấn CTRL + SHIFT + ENTER. 3. Đặt tất cả lại với nhau. Đầu tiên, bạn hãy chọn phạm vi G6: G8. Tiếp theo, bạn chèn công thức hiển thị bên dưới. Kết thúc bằng cách nhấn CTRL + SHIFT + ENTER. 4. Nếu bạn có Excel 365 hoặc Excel 2021, bạn chỉ cần chọn ô G6, nhập công thức tương tự như trên và nhấn Enter, không cần dấu ngoặc nhọn. Tài liệu của seriesĐể chuẩn bị cho việc thực hành, bạn tải tài liệu của series bằng cách điền vào form bên dưới nhé! Chúc bạn có trải nghiệm học tập vui vẻ tại 200Lab! Nếu bạn có định hướng trở thành Data Analyst chuyên nghiệp thì bạn có thể tham khảo bộ khóa học toàn diện chuyên nghiệp với combo 4 khóa học trong 1 bootcamp của 200Lab tại đây. |