Nội dung
Công thức của vấn đề
Giả sử rằng công ty nơi bạn làm việc có ba kho hàng, từ đó hàng hóa đi đến năm cửa hàng của bạn rải rác khắp Matxcova.
Mỗi cửa hàng có thể bán một số lượng hàng hóa nhất định mà chúng ta đã biết. Mỗi nhà kho đều có sức chứa hạn chế. Nhiệm vụ là phải lựa chọn hợp lý từ kho nào đến cửa hàng nào để giao hàng nhằm giảm thiểu tổng chi phí vận chuyển.
Trước khi bắt đầu tối ưu hóa, cần phải biên dịch một bảng đơn giản trên trang tính Excel - mô hình toán học của chúng tôi mô tả tình huống:
Nó được hiểu rằng:
- Bảng màu vàng nhạt (C4: G6) mô tả chi phí vận chuyển một mặt hàng từ mỗi kho đến mỗi cửa hàng.
- Các ô màu tím (C15: G14) mô tả số lượng hàng hóa cần bán của mỗi cửa hàng.
- Các ô màu đỏ (J10: J13) hiển thị sức chứa của từng kho - số lượng hàng hóa tối đa mà kho có thể chứa.
- Các ô màu vàng (C13: G13) và xanh lam (H10: H13) lần lượt là tổng hàng và cột cho các ô màu xanh lục.
- Tổng chi phí vận chuyển (J18) được tính bằng tổng của các sản phẩm của số lượng hàng hóa và chi phí vận chuyển tương ứng của chúng - để tính toán, hàm được sử dụng ở đây SUMPRODUCT (GIỚI THIỆU).
Do đó, nhiệm vụ của chúng ta được giảm xuống việc lựa chọn các giá trị tối ưu của các ô màu xanh lá cây. Và sao cho tổng số tiền cho dòng (ô màu xanh) không vượt quá sức chứa của kho (ô màu đỏ), đồng thời mỗi cửa hàng nhận được số lượng hàng cần bán (số lượng cho mỗi cửa hàng trong ô màu vàng nên càng gần với yêu cầu càng tốt - ô màu tím).
Dung dịch
Trong toán học, những vấn đề như vậy về lựa chọn phân phối tài nguyên tối ưu đã được hình thành và mô tả từ lâu. Và, tất nhiên, các cách giải quyết chúng từ lâu đã được phát triển không phải bằng cách liệt kê thẳng thừng (rất dài), mà với một số lượng rất nhỏ các lần lặp lại. Excel cung cấp cho người dùng chức năng như vậy bằng cách sử dụng một phần bổ trợ. Giải pháp tìm kiếm (Người giải quyết) từ tab Ngày (Ngày):
Nếu trên tab Ngày Excel của bạn không có lệnh như vậy - không sao cả - điều đó có nghĩa là bổ trợ chưa được kết nối. Để kích hoạt nó, hãy mở Tập tin, Sau đó chọn Thông số – Tiện ích – Giới thiệu (Tùy chọn - Phần bổ trợ - Đi tới). Trong cửa sổ mở ra, hãy chọn hộp bên cạnh dòng chúng ta cần Giải pháp tìm kiếm (Người giải quyết).
Hãy chạy tiện ích bổ sung:
Trong cửa sổ này, bạn cần thiết lập các thông số sau:
- Tối ưu hóa chức năng mục tiêu (Đặt ttiền tế bào) - ở đây cần chỉ ra mục tiêu chính cuối cùng của việc tối ưu hóa của chúng tôi, tức là hộp màu hồng với tổng chi phí vận chuyển (J18). Ô mục tiêu có thể được tối thiểu hóa (nếu đó là chi phí, như trong trường hợp của chúng tôi), tối đa hóa (nếu nó là, ví dụ, lợi nhuận) hoặc cố gắng đưa nó đến một giá trị nhất định (ví dụ, phù hợp chính xác với ngân sách được phân bổ).
- Thay đổi các ô biến (By thay đổi ô) - ở đây chúng tôi chỉ ra các ô màu xanh lá cây (C10: G12), bằng cách thay đổi các giá trị mà chúng tôi muốn đạt được kết quả của mình - chi phí phân phối tối thiểu.
- Phù hợp với các hạn chế (Tiêu Đề đến các Hạn chế) - danh sách các hạn chế phải được tính đến khi tối ưu hóa. Để thêm các hạn chế vào danh sách, hãy nhấp vào nút Thêm (Cộng) và nhập điều kiện vào cửa sổ xuất hiện. Trong trường hợp của chúng tôi, đây sẽ là hạn chế nhu cầu:
và giới hạn khối lượng kho tối đa:
Ngoài những hạn chế rõ ràng liên quan đến các yếu tố vật chất (sức chứa của kho hàng và phương tiện vận chuyển, hạn chế về ngân sách và thời gian, v.v.), đôi khi cần thêm những hạn chế “đặc biệt cho Excel”. Vì vậy, ví dụ, Excel có thể dễ dàng sắp xếp để bạn “tối ưu hóa” chi phí giao hàng bằng cách đề nghị vận chuyển hàng hóa từ cửa hàng về kho - chi phí sẽ trở nên âm, tức là chúng ta sẽ kiếm được lợi nhuận! 🙂
Để ngăn điều này xảy ra, tốt nhất bạn nên để hộp kiểm được bật. Làm cho các biến không giới hạn thành không phủ định hoặc thậm chí đôi khi đăng ký rõ ràng những khoảnh khắc như vậy trong danh sách các hạn chế.
Sau khi thiết lập tất cả các thông số cần thiết, cửa sổ sẽ trông như thế này:
Trong danh sách thả xuống Chọn một phương pháp giải, bạn cũng cần chọn phương pháp toán học thích hợp để giải một lựa chọn trong số ba tùy chọn:
- Phương pháp Simplex là một phương pháp đơn giản và nhanh chóng để giải các bài toán tuyến tính, tức là các bài toán trong đó đầu ra phụ thuộc tuyến tính vào đầu vào.
- Phương pháp Gradient Hạ cấp Chung (OGG) - đối với các bài toán phi tuyến tính, trong đó có sự phụ thuộc phi tuyến tính phức tạp giữa dữ liệu đầu vào và đầu ra (ví dụ: sự phụ thuộc của doanh số bán hàng vào chi phí quảng cáo).
- Tiến hóa tìm kiếm giải pháp - một phương pháp tối ưu hóa tương đối mới dựa trên các nguyên tắc tiến hóa sinh học (xin chào Darwin). Phương pháp này hoạt động lâu hơn nhiều lần so với hai phương pháp đầu tiên, nhưng có thể giải quyết hầu hết mọi vấn đề (phi tuyến tính, rời rạc).
Nhiệm vụ của chúng tôi là tuyến tính rõ ràng: giao 1 mảnh - chi 40 rúp, giao 2 mảnh - chi 80 rúp. vv, vì vậy phương pháp simplex là lựa chọn tốt nhất.
Bây giờ dữ liệu cho phép tính đã được nhập, hãy nhấn nút Tìm một giải pháp (Giải quyết)để bắt đầu tối ưu hóa. Trong những trường hợp nghiêm trọng với nhiều ô thay đổi và các ràng buộc, việc tìm ra giải pháp có thể mất nhiều thời gian (đặc biệt là với phương pháp tiến hóa), nhưng nhiệm vụ của chúng tôi đối với Excel sẽ không thành vấn đề - trong một vài giây, chúng tôi sẽ nhận được kết quả sau :
Hãy chú ý đến mức độ thú vị của khối lượng cung ứng được phân phối giữa các cửa hàng, đồng thời không vượt quá sức chứa của các kho hàng của chúng tôi và đáp ứng mọi yêu cầu về số lượng hàng hóa cần thiết cho mỗi cửa hàng.
Nếu giải pháp tìm thấy phù hợp với chúng tôi, thì chúng tôi có thể lưu nó hoặc quay trở lại các giá trị ban đầu và thử lại với các tham số khác. Bạn cũng có thể lưu kết hợp các tham số đã chọn dưới dạng Kịch bản. Theo yêu cầu của người dùng, Excel có thể xây dựng ba loại Báo cáo về vấn đề đang được giải quyết trên các trang tính riêng biệt: báo cáo về kết quả, báo cáo về độ ổn định toán học của giải pháp và báo cáo về các giới hạn (hạn chế) của giải pháp, tuy nhiên, trong hầu hết các trường hợp, chúng chỉ được các chuyên gia quan tâm. .
Tuy nhiên, có những tình huống mà Excel không thể tìm ra giải pháp phù hợp. Có thể mô phỏng một trường hợp như vậy nếu trong ví dụ của chúng ta chỉ ra yêu cầu của các cửa hàng với số lượng lớn hơn tổng sức chứa của các kho. Sau đó, khi thực hiện tối ưu hóa, Excel sẽ cố gắng đến gần giải pháp nhất có thể, rồi hiển thị thông báo không tìm thấy giải pháp. Tuy nhiên, ngay cả trong trường hợp này, chúng tôi có rất nhiều thông tin hữu ích - đặc biệt, chúng tôi có thể nhìn thấy “các liên kết yếu” trong các quy trình kinh doanh của mình và hiểu các lĩnh vực cần cải thiện.
Tất nhiên, ví dụ được xem xét là tương đối đơn giản, nhưng dễ dàng mở rộng để giải quyết các vấn đề phức tạp hơn nhiều. Ví dụ:
- Tối ưu hóa việc phân phối các nguồn tài chính theo mục chi trong kế hoạch kinh doanh hoặc ngân sách của dự án. Các hạn chế, trong trường hợp này, sẽ là số lượng tài chính và thời gian của dự án, và mục tiêu tối ưu hóa là tối đa hóa lợi nhuận và giảm thiểu chi phí dự án.
- Tối ưu hóa lập kế hoạch cho nhân viên nhằm giảm thiểu quỹ tiền lương của doanh nghiệp. Những hạn chế, trong trường hợp này, sẽ là mong muốn của mỗi nhân viên theo lịch trình làm việc và yêu cầu của bảng nhân sự.
- Tối ưu hóa các khoản đầu tư - nhu cầu phân phối một cách chính xác tiền giữa một số ngân hàng, chứng khoán hoặc cổ phiếu của doanh nghiệp một lần nữa, để tối đa hóa lợi nhuận hoặc (nếu quan trọng hơn) giảm thiểu rủi ro.
Trong mọi trường hợp, tiện ích bổ sung Giải pháp tìm kiếm (Người giải quyết) là một công cụ Excel rất mạnh mẽ và đẹp mắt và đáng để bạn quan tâm, vì nó có thể giúp bạn trong nhiều tình huống khó khăn mà bạn phải đối mặt trong kinh doanh hiện đại.