用Excel求解运筹学问题

用EXCEL求解运筹学问题主要内容1.用ExcelSolver求解线性规划2.用ExcelSolverTable进行敏感性分析3.用ExcelSolver求解运输问题和指派问题4.用ExcelSolver求解网络问题5.用ExcelSolver做线性回归分析6.用ExcelSolver进行决策分析1.应用Excel求解线性规划问题(1)ExcelSolver的安装Excel工具菜单中选择加载宏加载宏以后,在工具菜单中出现规划求解某企业的产品生产数据如下表分工厂单位产品生产时间每周可利用时间门窗11小时04小时202小时12小时33小时2小时18小时单位利润$300500理论模型0,18231224..500300max21212121xxxxxxtsxxz(2)求解如下的线性规划问题第一步：选择决策变量单元格决策变量的初始值一般赋0，并用较醒目的颜色表示。产品生产问题DoorsWindowsUnitProfit$300$500HoursAvailablePlant1104Plant20212Plant33218DoorsWindowsUnitsProduced00HoursUsedPerUnitProduced第二步：目标单元格，用函数公式表示并用较醒目的颜色表示。DoorsWindowsUnitProfit$300$500HoursAvailablePlant1104Plant20212Plant33218DoorsWindowsTotalProfitUnitsProduced11$800HoursUsedPerUnitProduced1112GTotalProfit=SUMPRODUCT(UnitProfit,UnitsProduced)第三步：约束条件左边项用函数表示DoorsWindowsUnitProfit$300$500HoursHoursUsedAvailablePlant1101=4Plant2022=12Plant3325=18DoorsWindowsTotalProfitUnitsProduced11$800HoursUsedPerUnitProduced56789EHoursUsed=SUMPRODUCT(C7:D7,UnitsProduced)=SUMPRODUCT(C8:D8,UnitsProduced)=SUMPRODUCT(C9:D9,UnitsProduced)第四步:激活规划求解,确定可变单元格和目标单元格DoorsWindowsUnitProfit$300$500HoursHoursUsedAvailablePlant1101=4Plant2022=12Plant3325=18DoorsWindowsTotalProfitUnitsProduced11$800HoursUsedPerUnitProduced第五步:增加约束条件DoorsWindowsUnitProfit$300$500HoursHoursUsedAvailablePlant1101=1Plant2022=12Plant3325=18DoorsWindowsTotalProfitUnitsProduced11$800HoursUsedPerUnitProduced第六步:完成求解对话框第七步:求解方式的选择第八步:从求解结果对话框选择所要的报告WyndorGlassCo.Product-MixProblemDoorsWindowsUnitProfit$300$500HoursHoursUsedAvailablePlant1102=4Plant20212=12Plant33218=18DoorsWindowsTotalProfitUnitsProduced26$3,600HoursUsedPerUnitProduced求解结果报告\灵敏性报告\极限报告MicrosoftExcel9.0运算结果报告工作表[Book1]Sheet1报告的建立:2006-7-1810:04:33目标单元格(最大值)单元格名字初值终值$G$12UnitsProducedTotalProfit$3,600$3,600可变单元格单元格名字初值终值$C$12UnitsProducedDoors22$D$12UnitsProducedWindows66约束单元格名字单元格值公式状态型数值$E$7Plant1Used2$E$7=$G$7未到限制值2$E$8Plant2Used12$E$8=$G$8到达限制值0$E$9Plant3Used18$E$9=$G$9到达限制值0求解结果报告MicrosoftExcel9.0敏感性报告工作表[Book1]Sheet1报告的建立:2006-7-1810:04:40可变单元格终递减目标式允许的允许的单元格名字值成本系数增量减量$C$12UnitsProducedDoors20300450300$D$12UnitsProducedWindows605001E+30300约束终阴影约束允许的允许的单元格名字值价格限制值增量减量$E$7Plant1Used2041E+302$E$8Plant2Used121501266$E$9Plant3Used181001866灵敏性报告MicrosoftExcel9.0极限值报告工作表[Book1]Sheet1报告的建立:2006-7-1810:04:47目标式单元格名字值$G$12UnitsProducedTotalProfit$3,600变量下限目标式上限目标式单元格名字值极限结果极限结果$C$12UnitsProducedDoors20300023600$D$12UnitsProducedWindows6060063600极限值报告2.用ExcelSolverTable进行敏感性分析SolverTable宏的安装使用１．BesurethattheSolverisinstalled.Ifitis,itshouldappearundertheToolsmenu.２．QuitExcelifitiscurrentlyrunning.３．SavetheSolverTable.xlafiletotheexactsamelocationastheSolver.xlafile(C:\programfiles\MicrosoftOffice\Office\Library\Solver\Solver.xla)(Ifitisnot,usetheFindcommandtofindtheSolver.xlafile).４．LaunchExcel.５．UndertheToolsmenu,choosetheAdd-Inscommand.６．ClicktheSolverTablecheckboxtohaveSolverTableloadwithExceleverytimeitisloaded.应用SolverTable做敏感性分析345678910111213141516171819202122232425262728BCDEFGDoorsWindowsUnitProfit$300$500HoursHoursUsedAvailablePlant1102=4Plant20212=12Plant33218=18DoorsWindowsTotalProfitUnitsProduced26$3,600UnitProfitTotalforDoorsDoorsWindowsProfit26$3,600$100$200$300$400$500$600$700$800$900$1,000HoursUsedPerUnitProducedOptimalUnitsProducedSelectthesecells(B18:E28),beforechoosingtheSolverTable.161718CDETotalDoorsWindowsProfit=DoorsProduced=WindowsProduced=TotalProfitOptimalUnitsProducedUnitProfitTotalforDoorsDoorsWindowsProfit43$5,500$10026$3,200$20026$3,400$30026$3,600$40026$3,800$50026$4,000$60026$4,200$70026$4,400$80043$4,700$90043$5,100$1,00043$5,500OptimalUnitsProducedSelectthesecells(B18:E28),beforechoosingtheSolverTable.门的单位利润从$100变到$1000，产品组合的变化(1)只有一个目标函数系数变动的影响(2)有两个目标函数系数同时变动的影响Selectthesecells(C17:H20),beforechoosingtheSolverTable.25C=(&DoorsProduced&,&WindowsProduced&)16门和窗的利润同时变化时,最优解的变化窗的单位利润变化(2,6)$100$200$300$400$500$600$700$800$100(2,6)(2,6)(2,6)(2,6)(2,6)(2,6)(2,6)(2,6)门$200(4,3)(2,6)(2,6)(2,6)(2,6)(2,6)(2,6)(2,6)的$300(4,3)(4,3)(2,6)(2,6)(2,6)(2,6)(2,6)(2,6)单$400(4,3)(4,3)(2,6)(2,6)(2,6)(2,6)(2,6)(2,6)位$500(4,3)(4,3)(4,3)(2,6)(2,6)(2,6)(2,6)(2,6)利$600(4,3)(4,3)(4,3)(4,3)(2,6)(2,6)(2,6)(2,6)润$700(4,3)(4,3)(4,3)(4,3)(2,6)(2,6)(2,6)(2,6)变$800(4,3)(4,3)(4,3)(4,3)(4,3)(2,6)(2,6)(2,6)化$900(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(2,6)(2,6)$1,000(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(2,6)(2,6)$1,100(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(2,6)$1,200(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)$1,300(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)$1,400(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)$1,500(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)$1,600(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)(4,3)3.用ExcelSolver求解运输问题和指派问题例BetterProducts公司决定利用三个有剩余生产能力的工厂生产四种新产品，问题：哪个工厂生产哪种产品？40303020需要的产量452127303737523—2940275$24$28$27$411工厂剩余生产能力4321产品单位成本345678910111213141516BCDEFGHIUnitCostProduct1Product2Product3Product4Plant1$41$27$28$24Plant2$40$29-$23Plant3$37$30$27$21ProducedDailyProductionProduct1Product2Product3Product4AtPlantCapacityPlant103030060=75Plant20001515=75Plant320002545=45ProductsProduced20303040====TotalCostRequi