excel在经济和管理中的应用Excel-运输优化.ppt

转化为供需平衡 转化：产销平衡 产地 销 地 产量 B1 B2 B3 A1 6 4 6 200 A2 6 5 5 300 A3 0 0 0 150 销量 250 200 200 　 销大于产运输问题的数学模型 例5 供需不平衡的运输问题 　 销 地 及 单 位 运 费 　 产地 1区 2区 3区 产量 山西盂县 1.8 1.7 1.55 4000 河北临城 1.6 1.5 1.75 1500 需求量 3000 1000 2000 　 某单位有3个生活区。每年分别需要用煤3000、1000、2000吨，由河北临城、山西盂县两处煤矿负责供应，价格、质量相同。供应能力分别为1500、4000吨，单位运价如表所示。 由于需大于供，经研究决定1区供应量最多可减少300吨，2区必须满足需求量，3区供应量不少于1500吨，试求总费用为最低的调运方案。 模 型 设： 目标函数： s.t. Excel求解结果： 目标函数： H18：=SUMPRODUCT(C6:E7,C13:E14) 约束： F13：=SUM(C13:E13) F14：=SUM(C14:E14) C17：=SUM(C13:C14) D17：=SUM(D13:D14) E17：=SUM(E13:E14) 例5 EXCEL求解 运输问题线性规划的一般模型 容量线路 如果从起点i到终点j的路线有容量Lij ，那么可以增加约束 xij = Lij ，含有这种约束条件的运输问题被称为容量运输问题。 如果从起点i到终点j的路线至少需要运输Mij ，可以增加约束xij = Mij 。 如果起点i不能向终点j送货，则增加约束 xij = 0 。 例 6如果起点i不能向终点j送货，则增加约束 Xij = 0 生产厂 需求1 需求2 需求3 需求4 产量 F1 16 13 22 17 50 F2 14 13 19 15 60 F3 19 20 23 ---- 50 最低需求量 30 70 0 10 最高需求量 50 70 30 不限 生产能力、需求量和单位运输费用等数据如下表所示。注意工厂3不能向需求4供货。求解最佳配送方案。 例6 EXCEL 求解过程 例 7 转运1 ? 1 h4 f1 h3 R5 R6 R7 2 3 3 f2 ? 2 6 3 6 4 4 6 5 ? R8 600 400 200 150 350 300 求解步骤： 1. 问题转化为表格。 2. 写出目标函数和约束。 3. 使用解题工具求解。 例7 求解 1.转运站的输入=输出 2.所有不可配送单元格=0 目标函数： K15：=SUMPRODUCT(C2:H5,C9:H12) 约束： I9：=C9+D9 I10：=C10+D10 I11：=SUM(E11:H11) I12：=SUM(E12:H12) C13：=C9+C10 D13：=D9+D10 E13：=E11+E12 F13：=F11+F12 G13：=G11+G12 H13：=H11+H12 C11:D12=0 E9:H10=0 例 8 转运2 例8-1求解结果（F2—R8） 1. 问题转化为表格。 2. 写出目标函数和约束。 3. 使用解题工具求解。 EXCEL求解 目标函数： K15：=SUMPRODUCT(C2:H5,C9:H12) 约束： I9：=C9+D9 I10：=C10+D10+H10 I11：=SUM(E11:H11) I12：=SUM(E12:H12) C13：=C9+C10 D13：=D9+D10 E13：=E11+E12 F13：=F11+F12 G13：=G11+G12 H13：=H10+H11+H12 C11:D12=0 E9:H9=0 E10:G10=0 F2向R8送货 EXCEL求解过程 Excel公式： K16：=SUMPRODUCT(C2:H6,C10:H14) I10：=SUM(C10:H10) I11：=SUM(C11:H11) I12: =SUM(C12:H12) I13: =SUM(C13:H13) I14：=H14 C15：=SUM(C10:C11) D15：=SUM(D10:D11) E15：=SUM(E10:E14) F15：=SUM(F10:F14) G15：=SUM(G10:G14)-I14 H15：=SUM(H10:H14) E10：H10=0 E11：G11=0 C12：D14=0 E14：G14=0 --F2和R7均可向R8送货 1.转运站的输入=输出 2.所有不可配送单元格=0 3.f2可以直接向r8送货，h12单元格不能置0 4.r7可以向r8送货，r7得到数量减掉送出数量