Excel规划求解实战：以供应链配送成本优化为例

1. 供应链配送成本优化问题解析最近帮一家制造企业做供应链优化发现他们每个月花在配送上的成本高得离谱。三个仓库往四个城市发货光是运输费就占了总成本的15%。老板急得直挠头问我能不能用Excel帮他们省点钱。我拍胸脯说没问题这不就是典型的运输问题嘛运输问题的核心其实特别生活化。想象你是个水果摊主要从三个批发市场进货再配送给四个小区的老顾客。每个批发市场的库存有限供给约束每个小区的需求量固定需求约束不同批发市场到不同小区的运费还不一样成本矩阵。你的目标很简单用最少的运费把水果送齐。回到制造业案例我们具体数据是这样的供给端三个仓库月产能分别为5000、6000、2500件需求端四个城市月需求分别为6000、4000、2000、1500件运输成本矩阵单位元/件路线城市A城市B城市C城市D仓库13276仓库27523仓库32545这种问题用线性规划建模再合适不过。我们需要确定12个决策变量3仓库×4城市在满足供需平衡的前提下让总运输成本最小。这就像玩拼图既要严丝合缝又要图案完美。2. Excel建模全流程实操2.1 数据准备与模型搭建打开Excel新建工作表我习惯用三块区域来组织数据成本矩阵区B2:E4直接输入上述运输成本表供给需求区F列记录各仓库总发货量F25000F36000F42500第5行记录各城市总到货量B56000C54000D52000E51500决策变量区B7:E9这里放待求解的运输量初始全填0关键公式设置总成本公式在B11输入SUMPRODUCT(B2:E4,B7:E9)这个会实时计算当前方案的总运费供给约束在F7输入SUM(B7:E7)下拉填充到F9计算各仓库实际发货量需求约束在B10输入SUM(B7:B9)右拉填充到E10计算各城市实际到货量提示用不同颜色区分输入数据蓝色、公式计算绿色和决策变量黄色后期调试会更方便2.2 规划求解参数配置点击「数据」→「规划求解」按以下步骤设置目标设置选择B11单元格勾选「最小值」变量单元格选择B7:E9区域约束条件供给约束F7:F9 ≤ F2:F4各仓库发货不超产能需求约束B10:E10 B5:E5各城市到货满足需求非负约束B7:E9 ≥ 0求解方法选择「单纯线性规划」这里有个易错点一定要勾选「使无约束变量为非负数」。我去年就栽在这结果求出的方案居然有负的运输量被同事笑话了好久。3. 结果分析与方案优化点击求解按钮不到1秒就出结果了。最优方案显示仓库1→城市B4000件仓库1→城市D1000件仓库2→城市C2000件仓库2→城市D500件仓库3→城市A6000件总成本39,500元对比原来的经验方案总成本52,000元直接省了24%的运费这里有几个insight值得分享低成本路线优先仓库3到城市A每件运费仅2元所以6000件全走这条线就近原则仓库1离城市B最近成本2元因此承包了其全部需求产能利用率高成本路线的仓库2仍有2500件闲置产能说明当前需求结构下无法完全利用为了验证方案的鲁棒性我做了个灵敏度分析在「规划求解结果」对话框选择「敏感性报告」重点关注影子价格城市A需求每增加1件总成本增加2元等于仓库3→城市A运费仓库1产能每增加1件总成本最多可降低1元取决于分配路线4. 实战中的避坑指南去年给某快消品公司做类似项目时踩过几个坑这里分享给大家坑1忽略整数约束服装运输必须整箱发货但默认求解可能得到小数结果。解决方法在规划求解约束中添加B7:E9 整数或使用「演化」求解方法坑2多目标冲突既要成本最低又要配送时效最快怎么办我的经验是先单独优化成本记录最优值如39,500元新增时效目标添加约束总成本 ≤ 39,500×1.05允许成本上浮5%然后优化时效目标坑3数据量过大当有20仓库和100门店时Excel可能卡死。这时候需要改用Python的PuLP库或专业优化软件或先按地理区域聚类分层优化最近发现Excel 365的「Python内置」功能特别香可以直接在单元格里写Python代码调用OR-Tools等库。比如处理供需不平衡时总供给≠总需求用Python自动添加虚拟仓库或虚拟门店比手动调整方便多了。