Excel求解基于覆盖问题的选址问题

Excel求解基于覆盖问题

的生鲜配送网点选址Excel求解基于覆盖问题的生鲜配送网点选址摘要覆盖问题在设施选址的问题中具有很重要的作用，文章主要通过覆盖模型在社区外卖系统中的作用，通过Excel的规划求解，来寻找适合的生鲜配送网点，在节约运输成本的同时，使服务范围更广，服务质量更好，并且对配送效率有较高要求的物流，消防等等也有启示作用。关键词：最大覆盖问题；选址；规划求解0.引言随着人们的生活质量的提高，从以前的“吃得饱”到如今追求的“吃得好”，人们对物质的需求也在不断提高，人们对“卫生，健康”更加迫切，因此生鲜配送应运而生，用户网上下单，快递员送货上门，使用户可以买到新鲜的蔬菜水果，用户对快捷便利的运输方式需求迫切，但该地区目前并没有生鲜配送网点，因此需考虑在该地区建立生鲜配送网点，正确的选址有利于降低成本，提高效率，因此本文将围绕如何选址进行分析求解。地区情况介绍通过调查，该地区有5个社区，不同社区间距离较远，地区仅有一家大型超市，位于五个社区中央位置，人们通常去超市购买蔬菜水果，通过对超市调查，发现很多蔬菜都不新鲜，顾客大多数是老人。经过询问，大部分年轻人都是点外卖买饭，而用外卖买蔬菜水果而又十分配送费很高，很少有人选择，去超市又觉得麻烦。由于该地区没有生鲜配送服务，没办法对该数据调查，因此选择每天的外卖情况调查搜集数据，由于工作量大，调查五天的数据，求其平均值。近似估计生鲜网点建成后的每日接单数与收银额，数据如表一所示。男性人口（元/单）平均消费（单/元）女性人口（元/单）平均消费（单/元）合计（元）社区一931088512120329社区二452604830024300社区三11018012322046860社区四471565517817122社区五611025330022122表一各个社区的外卖订单详情生鲜配送网点情况介绍目前该地区有四个备选网点，由于地区房租价格不同，因此不同地区的网点租金不同。并且有大网点和小网点可供选择，由于配送费的限制，大网点可覆盖以自身为圆心6千米的范围，小网点可以覆盖以自身为圆心3千米的范围。各地区大小网点租金与覆盖距离如表二所示（注：X11表示网点一建小网点，X12表示网点一建大网点，x21表示网点二建小网点，以此类推）租金（元/月）覆盖距离（千米）xll110003xl2250006x21120003x22260006x3190003x32230004x4180005x42210006表二大小网点租金与覆盖距离不同网点到各个社区距离不同，具体数据如表三：网点一网点二网点三网点四社区一3687社区二4278社区三5444社区四9752社区五8335表三各社区到各网点的距离（单位/千米）2.1选址目标首先，只能建立两个网点。其次，由于资金有限，每月月租不得超过40000元最后，应使营业额尽可能大。利用EXcel规划求解3.1建模部分3.11设置变量P：要建的网点数xjk:是否在备选网点j建k网点，1建，0不建cjk:在备选网点j建k网点的租金hi:社区i的总需求yi:社区i被是否覆盖，1覆盖，0未覆盖T:网点建设成本预算Dk:k网点的覆盖半径dij:社区倒备选网点j的距离Ni:能覆盖社区i的网点建设方案集合3.12建立目标函数maxEhiyi3.13设置约束条件（1）一个网点只能建立大网点或小网点（2）总租金不大于用预算（3）建设网点数等于要求的网点数（4）xjk的0或1约束（5）yi的0或1约束数学表达式如下：ExjkWlk$K,VjejExjk*cjkWTEjk=P,jej,VkeKxjke（0,1）,Vjej,keKyie（0,1）,Viel3.2求解环节分析各个网点所能覆盖的社区Dk36363636Nixllx12x21x22x31x32x41x42yl11010000y201110000y301010101y400000111y500111101表四3.21形成初始表在Excel表格中输入各个社区以及营业额，各个大小网点的租金，以及xjk来表示是否被选择。由于I3:Q3最后会选为可变单元格，因此先默认填0。通过表四可知，结果如下TOC\o"1-5"\h\z:iIBCII*IIBHI_TELK]IQFQEYL世旳-rf呃xLl述锻送却iJE:・41拠可ri诽0000000TTId20339舅3004閒閒IHI2K!：I2ZcikLIH10WEUIFIMDKOO23100SOOD21000BfiitWi3.22列出约束条件利用表四不难看出对于yl,有xll,xl2,x22可以覆盖到，因此C3位置填入“=SUM(J3,K3,M3)”,对于y2,有x12,x21,x22可以覆盖到，因此D4位置填入“=SUM(K3,L3,M3)”同理E5填入“=SUM(K3,M3,O3,Q3)”，F3填入“=SUM(O3,P3,Q3)”，G3填入“=SUM(L3,M3,N3,O3,Q3)”目标函数即为刀yihi,i=1,2,3,4,5.因此C6处填“=SUMPRODUCT(C3:G3,C4:G4)”另外对于约束条件刀xjkW1k^K,jGJ，可用两个单元格值之和小于一来确定。对于总租金不大于预算的约束，利用SUMPRODUCT(J3:Q3,J4:Q4)可计算出总租金，使其不大于40000即可。对于选两个网点，利用函数SUM(J3:Q3)即可算出总共选择的网点数量,使其等于2即可。约束条件填写完成后如图一，具体函数如图二A.BCDErGHI,12yl谑y4yE3yi000004hi20323243004S8601712222122cik56目标廉1勘07S1r0<=191F0<=1101F0<=1111尸0<=1120<=40000130214图一ECDFFB12Jiy2对丫4yS3yl二别冃灯亀k:答i$)^iax'CkSjLSins)斗曲饭績i［為os理劝wi肚心廿.舌口静斗吐Cl爲ixn生胸山4hi20£2^i243W^606017LZ22212260目=3UHFEC'DUUT<C3:G3,宇&!=£UI(JSiK2)<=1号=5lHtL3：li：3：l<=110-HJE(HE;Q3i<-111<=112=EaiKFR'7CiUCT(J3：Q3?<=13-SDK(J3;Q3)3.23利用规划参数求解选择“数据”f“模拟分析”，点击“规划求解”。如图三=A*tfHS9A皿布■JJtEfn审蔺贬也开业Hit1*邑£E用Q査此"-..CJ圖V那医哦題⑥曲用冒国削固勵曲囲@S«翅£冊曰动礙attff抵量貝皿，SHR芒比-⑥拒MAW^-y#JWtu诜d沁"■U.'F&fJS单赵彌心：_twfift-sSftwjm...-Ga二］龜J>=SUI(J3jI3)图三由于需要我们的目标在满足约束的条件下获得最大的营业额，因此在“设置目标”栏选择我们的目标函数“C6”到最大值。其次，需要用xjk的数值0或1来进行选址，因此将“通过更改可变单元格”选为“J3:Q3”单元格。由于网点最多只能被覆盖一次，最少零次，因此C3:G3的值均小于等于一且大于等于零。总租金不大于预算，即单元格G13=I13。各个大小网点只能被选择或不被选择，因此J3:Q3的值均小于等于一且大于等于零。并且由于该部分为可变单元格部分，结合实际，不存在开半个店的情况，因此J3:Q3的值必须为整数。填好后的规划求解参数如图四XX设畳自标E：運）o最山直㈣o目标值CV）：通过嵐改可畫单元榕同:tJ$3：^QS3區守釣乘（U）;0使无邸^呈为非贷数（椅选瞅円选瞅円非违性円点法求解方法为治^儼悝规划求解冋越鋅嘰性內点法^擊为馳规划求墀问麺遷星单纯垃性规划百I粵期⑸矢诩【0】图四点击求解后，输出内容如图五，C3:G3均为1,说明五个社区全部被覆盖,并且目标函数为130733，则预计营业额130733元，M3、P3为1，也说明选择其对应的x22与x41建立网点。yi,,ysy4y5yi1|!111hi2032924300468601712222122目标亟数1307331JKLiMiN01p1Qall112i2L122j31i32i41疏2ajk00010010cjkLIOOO2500012000260009000230003000210003.3得出结论通过求解可得：选择x22与x41,即网点二建立大网点，网店四建立小网点。总收益额为130733。案例总