Excel规划求解在设施选址中的应用

时间：2024-04-24

何明宇

(成都工贸职业技术学院，四川成都 611731)

1 设施选址

无论是一家新建企业或者是已经存在的企业，设施选址对于企业的最终成功起着至关重要的作用。设施选址是指企业根据经营战略的需要，综合考虑政治、经济、成本、劳动力等各种要素，运用科学的决策方法，从多个候选地当中寻找最佳位置的过程。设施选址的优劣不但与企业的运营成本休戚相关，而且对企业的竞争优势以及企业的后续经营策略有着重大而深远的影响。选址不当会大幅度推高企业的运营成本，无法获取足够的原料和劳动力，甚至失去竞争优势。因此，科学、合理的选址是企业运营管理中的一项重要战略决策。

设施选址的影响因素众多，企业在进行选址分析时应根据本企业的产品特点、资源需求和市场环境，确定选址的目标和要求，综合考虑相关因素收集数据，初步确定若干候选地址。要从初步确定的候选地中选出最优方案，需要确定合理的评价指标和选择恰当的评价方法，科学决策。文献[1]总结出目前常用的评价方法分为成本因素评价和综合因素评价两大类。成本因素评价包括盈亏点平衡法、重心法、线性规划法等，综合因素评价常用加权因素法、因次分析法等。

图1 设施选法的常用方法

在选址决策中，如果备选方案的各个影响因素对决策结果的作用程度没有明显差异时，生产运输成本就变成了决策的关键。在成本因素评价时，线性规划方法是学者们经常选用的方法之一，在设施选址的线性规划模型中，通常运用表上作业法进行求解，先找出一个初始方案,再以进行调整改进,直到获得最优解。该方法的缺点在于计算过程十分繁琐， Excel软件的“规划求解”宏是一款专门求解线性规划问题的插件，既方便快捷，也易于操作。

陈雪菱女士在文献[1]中，通过具体实例介绍了对选址优化模型求解的一种方法。每一次选取一个候选地点加入原有供应网络，利用Excel软件的规划求解工具计算该方案下的总生产运输成本，然后将所有方案的总成本进行比较，总成本最低的方案对应的候选点即为最佳选址方案。该方法虽然简便易行，但是美中不足的是需要经过多次计算才能得出结果。本文在此模型基础上加以改进，建立了设施选址的混合整数规划模型，并得到一致的结果。

2 Excel规划求解实例

某公司现有2家工厂，代号分别是F1和F2，它们的产品运往A、B、C、D4个分销地销售，因为市场的需求量迅速增长，公司决定正在F3或F4再新建一家工厂，已知各工厂到各销售点的运输费用如表1所示，请问选择其中哪个地址建厂会更好。

表1 产品的单位运输成本及供需表

2.1 建立选址的数学模型

约束条件为

xij≥0,i=1,2,3,4,j=1,2,3,4

2.2 建立工厂选址的电子表格模型

图2 工厂选址模型的电子表格模型

其中，单元格区域L14:L15用于记录实际的选址方案，数字0表示不建议在此地建厂，用数字1表示优先选择在此地建厂。

第G列用于统计每个工厂向各个销售地发出货物的总数量，在G15单元格输入公式“=SUM(C15:F15)”，然后向下复制填充至I18单元格。

第19行用于统计各销售地从各工厂接收的货物总数量，在C19单元格输入公式“=SUM(C15:C18)”，然后向右复制填充至F19单元格。

在L14单元格输入公式“=SUM(L12:L13)”，表示选址数量。

在M14单元格输入公式“=L12*I15-G15”，然后向下复制填充至M15单元格。分别代表约束函数10500y1-x31-x32-x33-x34和10500y2-x41-x42-x43-x44。

C24单元格则是用于计算生产运输的总成本，在此输入公式“=SUMPRODUCT(C5:F8,C13:F16)”，它将作为规划求解的目标单元格。

2.3 模型参数设置

图3 工厂选址的规划求解模型参数

选中目标函数值所在单元格C24，点击“数据|规划求解”，在“规划求解参数”对话框中，确认在“设置目标单元格”对应文本框中显示为$C$22(目标函数值)，然后根据目标函数的要求选中 “最小值”按钮，再在“可变单元格”对应的文本框中输入$C$15:$F$18，$L$14:$L$15(模型变量)。再单击“添加”按钮，再打开“添加约束”对话框中添加模型的约束条件，本实例的约束条件如下：

条件1：单元格区域L14:L15的值为二进制数

条件2：C19:F19= C21:F21

条件3：G15:G16=I15:I16

条件4：L16=L18

条件5：M14:M15≥0，设置该条件以便在规划求解模型之中强制执行可能的新建工厂选址地的生产能力约束。

设置好规划求解模型的参数，其结果如图3所示。