問題的形成
假設您工作的公司有 XNUMX 個倉庫，貨物從這些倉庫運往分散在莫斯科各地的 XNUMX 家商店。
每家商店都能夠銷售我們已知的一定數量的商品。 每個倉庫的容量都是有限的。 任務是合理選擇從哪個倉庫到哪個店鋪發貨，以盡量減少總運輸成本。
在開始優化之前，有必要在 Excel 表格上編譯一個簡單的表格——我們描述這種情況的數學模型：
據了解：
- 淺黃色表 (C4:G6) 描述了將一件物品從每個倉庫運送到每個商店的成本。
- 紫色單元格 (C15:G14) 描述了每個商店銷售所需的商品數量。
- 紅色單元格 (J10:J13) 顯示每個倉庫的容量——倉庫可以容納的最大貨物數量。
- 黃色 (C13:G13) 和藍色 (H10:H13) 單元格分別是綠色單元格的行和列總和。
- 總運費（J18）計算為商品數量的乘積與其對應的運費之和——計算，這裡使用函數 SUMPRODUCT （總和）.
因此，我們的任務被簡化為選擇綠色單元格的最佳值。 並且使該行的總金額（藍色單元格）不超過倉庫的容量（紅色單元格），同時每個商店收到它需要銷售的商品數量（每個商店的數量在黃色單元格應盡可能接近要求 - 紫色單元格）。
解決方案
在數學中，這種選擇資源最優分配的問題已經被制定和描述了很長時間。 當然，解決這些問題的方法早已不是通過生硬的枚舉（這很長），而是在非常少的迭代中開發出來的。 Excel 使用加載項為用戶提供此類功能。 搜索解決方案 （求解器） 從標籤 數據 （日期）:
如果在選項卡上 數據 你的 Excel 沒有這樣的命令——沒關係——這意味著加載項根本還沒有連接。 激活它打開 文件，然後選擇 參數 - 附加組件 - 關於我們 （選項 - 加載項 - 轉到）. 在打開的窗口中，選中我們需要的行旁邊的框 搜索解決方案 （求解器）.
讓我們運行插件：
在此窗口中，您需要設置以下參數：
- 優化目標函數 （設置噸錢 細胞） – 這裡有必要指出我們優化的最終主要目標，即帶有總運費（J18）的粉紅色框。 目標單元可以被最小化（如果是費用，如我們的例子），最大化（如果是，例如，利潤）或嘗試將其帶到給定值（例如，完全適合分配的預算）。
- 更改可變單元格 (By 改變 細胞） – 在這裡我們表示綠色單元格（C10：G12），通過改變我們想要達到我們的結果的值 - 最低交付成本。
- 符合限制條件 (主題 至 約束） – 優化時必須考慮的限制列表。 要向列表添加限制，請單擊按鈕 加入 (添加） 並在出現的窗口中輸入條件。 在我們的例子中，這將是需求約束：
以及最大倉庫數量限制：
除了與物理因素（倉庫容量和運輸方式、預算和時間限制等）相關的明顯限制外，有時還需要添加“Excel 專用”的限制。 因此，例如，Excel 可以通過提供將貨物從商店運回倉庫的方式輕鬆安排您“優化”交付成本——成本將變為負數，即我們將獲利！ 🙂
為防止這種情況發生，最好啟用該複選框。 使無限變量非負 甚至有時在限制列表中明確記錄這些時刻。
設置所有必要參數後，窗口應如下所示：
在選擇求解方法下拉列表中，您還需要從三個選項中選擇合適的數學方法來求解：
- 單純形法 是一種解決線性問題的簡單而快速的方法，即輸出線性依賴於輸入的問題。
- 通用降級梯度法 (OGG) – 對於非線性問題，輸入和輸出數據之間存在復雜的非線性依賴關係（例如，銷售對廣告成本的依賴關係）。
- 進化搜索解決方案 – 一種基於生物進化原理的相對較新的優化方法（你好達爾文）。 這種方法的工作時間比前兩種方法長很多倍，但幾乎可以解決任何問題（非線性、離散）。
我們的任務顯然是線性的：交付 1 件 - 花費 40 盧布，交付 2 件 - 花費 80 盧布。 等等，所以單純形法是最好的選擇。
輸入計算數據後，按下按鈕 找到解決方案 (解決）開始優化。 在具有大量更改單元格和約束的嚴重情況下，找到解決方案可能需要很長時間（尤其是使用進化方法），但我們的 Excel 任務不會成為問題 - 過一會兒我們將得到以下結果：
請注意供應量在商店之間的分佈有多有趣，同時不超過我們倉庫的容量並滿足每個商店對所需商品數量的所有要求。
如果找到的解決方案適合我們，那麼我們可以保存它，或者回滾到原始值並使用其他參數重試。 您還可以將選定的參數組合另存為 <span class="notranslate">EventXtra 6大解決方案</span>. 應用戶要求，Excel可以構建三種類型 銷售報告 在單獨的紙上解決的問題：關於結果的報告，關於解決方案的數學穩定性的報告和關於解決方案的限制（限制）的報告，但是，在大多數情況下，它們只對專家感興趣.
但是，在某些情況下 Excel 找不到合適的解決方案。 如果我們在示例中指出商店的需求量大於倉庫的總容量，則可以模擬這種情況。 然後，在執行優化時，Excel 將嘗試盡可能接近解決方案，然後顯示找不到解決方案的消息。 儘管如此，即使在這種情況下，我們也有很多有用的信息——特別是，我們可以看到我們業務流程的“薄弱環節”並了解需要改進的領域。
當然，所考慮的示例相對簡單，但可以輕鬆擴展以解決更複雜的問題。 例如：
- 優化財政資源配置 按項目業務計劃或預算中的支出項目。 在這種情況下，限制將是融資金額和項目時間，優化的目標是最大化利潤和最小化項目成本。
- 員工排班優化 以盡量減少企業的工資基金。 在這種情況下，限制將是每個員工根據僱傭時間表和人員配備表的要求的意願。
- 優化投資投資 – 需要在幾家銀行、證券或企業股份之間正確分配資金，以實現利潤最大化或（如果更重要的話）風險最小化。
在任何情況下，附加 搜索解決方案 (求解器） 是一個非常強大和漂亮的 Excel 工具，值得您關注，因為它可以幫助您在現代商業中必鬚麵對的許多困難情況。