问题的形成
假设您工作的公司有 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 任务不会成为问题 - 过一会儿我们将得到以下结果：
请注意供应量在商店之间的分布有多有趣，同时不超过我们仓库的容量并满足每个商店对所需商品数量的所有要求。
如果找到的解决方案适合我们，那么我们可以保存它，或者回滚到原始值并使用其他参数重试。 您还可以将选定的参数组合另存为 EventXtra XNUMX大解决方案. 应用户要求，Excel可以构建三种类型 业务报告 在单独的纸上解决的问题：关于结果的报告，关于解决方案的数学稳定性的报告和关于解决方案的限制（限制）的报告，但是，在大多数情况下，它们只对专家感兴趣.
但是，在某些情况下 Excel 找不到合适的解决方案。 如果我们在示例中指出商店的需求量大于仓库的总容量，则可以模拟这种情况。 然后，在执行优化时，Excel 将尝试尽可能接近解决方案，然后显示找不到解决方案的消息。 尽管如此，即使在这种情况下，我们也有很多有用的信息——特别是，我们可以看到我们业务流程的“薄弱环节”并了解需要改进的领域。
当然，所考虑的示例相对简单，但可以轻松扩展以解决更复杂的问题。 例如：
- 优化财政资源配置 按项目业务计划或预算中的支出项目。 在这种情况下，限制将是融资的数量和项目的时间，优化的目标是最大化利润和最小化项目成本。
- 员工排班优化 以尽量减少企业的工资基金。 在这种情况下，限制将是每个员工根据雇佣时间表和人员配备表的要求的意愿。
- 优化投资投资 – 需要在多家银行、证券或企业股份之间正确分配资金，以实现利润最大化或（如果更重要的话）风险最小化。
在任何情况下，附加 搜索解决方案 (求解器） 是一个非常强大和漂亮的 Excel 工具，值得您关注，因为它可以帮助您在现代商业中必须面对的许多困难情况。