运寿学上机材料2002年4刀上机共8学时，4次，第次2学时次数时间地点内容12小时机械机房Excel及其“规划求解”加载宏的使用22小时机械机房用“规划求解”解线性规划问题32小时机械机房用“规划求解”解运输问题42小时机械机房“规划求解”上机考评运输问题：已知单位运价的产销平衡问题如下图:I 3销地1销地2销地3销地4销地5产量产地11023159255产地25101524306产地31551471520|7产地42015133083020203010 . 25 二用Excel求解的思路是：设置初始工作表，正确表示各个量之间的关系，在规划求解参数中设置目标 单元格及最小（人）值，并添加约束条件。步骤如下：一、设置初始工作表。34567产销2 销O 5111地销11203 地 销2 O5 51*1420305 地 销5 230O1151J252030B11公式=SUMPRODUCT(B14F17fB4F7)16121314产地115产地210求解:971X1.001 001 001 001 001.001 001001 001 001.001 001 001 001 001.001 001.001.001 0018 16 16 26 6 2119 B18 公式:|20 =B8-SUM(B 14317)217销地1销地2销地3销地4销地520 G14公式251525=G4NUMTimes New Roman12 -B Z U垂垂手宦11竝屈100%-闻,FBll且=SUMPRODUCT(B 14 F17 卫4F7)ABJ CDEFGHIJKL1运輸问题2文件（B编辑旧视图M插入山格式0工具03数据ID）窗口凶帮助10】 Microsoft Excel - solver-! xl-Ifflxl1、在上图中区域B4:F7输入各项单位运价，区域B&F8和区域G4:G7分别输入销量和产最。2、按上图中第10行开始输入所示文字，其中区域B14:F17均输入初始值1 （使用自动填充）。3、在B18中输入公式 =B8-SUM（B14:B17）,然后自动填充到C18:F18中；在G14中输入公式 =G4-SUM（B14:F14）,然后自动填充到 G15:G17 中。4、在 B11 中输入公式=SUMPRODUCT（B14:F17,B4:F7）二、求解。1、打开“工具”菜单，选择“加载宏”选项，从“加载宏”对话框中选中“规划求解”后单击“确定”。 （注意：Office可能提示你要安装该选项）2、现在可以打开“工具”菜单，选择“规划求解”选项。3、在设置目标单元格：单击型按钮，选择bii单元格。然后选择“最小值”。4、选择BI4:F17作为可变单元格区域。5、单击“添加”按钮，出现“添加约束”对话框。6、首先添加变量非负约束：选择区域B14:F17,再选择“=”， 在第三个框中输入“0”，再单击“添加”。如右图所示。7、设置约束B18:F18=O,即销量无剩余。再单击“添加”按钮。置完成，如下左图所示。9.单击“求解”按钮，即可得到结果如上右图所示。最小总运价560销地1销地2销地3销地4销地5产地1 J0.000.0025.000.000 000产地220.000.000 0010000 000产地30.0020.000.000.000 000产地410.000.005.000.0025.00000000|8、设置约束G14:G17即产罐无剩余。最示单击“确定”按钮，冋到“规划求解参数”对话框中，约束设上机练习：1、线性规划：运筹学书中P40页问题，并验证书上的结果。2、运筹学书中P力页第29题。3、运输问题运筹学书中P92页问题，具中M可取一个人数如1004、运筹学书中P100页第（3）小题。上机考核内容：在2小时内完成1个线性规划问题和1个运输问题。English Reading about Operations ResearchLinear programming （LP） is one of the most widely applied O.R. techniques and owes its popularity principally to George Danzig simplex method （单纯形法）（Danzig 1963） and the revolution in computing It is a very powerful technique for solving allocation problems and has become a standard tool for many businesses and organizations. According to the simplex method, data expressing the relationships between the objective function （目标函数）,the variables（决策变量）,and the constraints（纟勺束条件）are arranged in a simplex Tableau（单纯形表）.Once the initial Tableau （初始单纯形表）is constructed, the steps for the simplex method arc:1. Select the entering variable （换入变量）by labeling the variable whose entry in the Z row is the most negative 2. Select the departing variable （换出变量）by the smallest positive ratio of bja with the cfs in the column of the entering variable The intersection value of the entering and departing variables is called the pivot （主元素）3. Use pivotal elimination to construct the new Tableau by deriving the pivot to 1 and all other values in that column to zero.4 Repeat step 1 3 until the entries in the z row in the columns labeled with variables are all nonnegativeHowever, Danzig*s simplex method allows solutions to be generated by hand; the iterative nature of producing solutions is so tedious that had the computer never been invented then linear programming would have remained an interesting academic idea, relegated to the mathematics classroom. Fortunately, computers were invented and as they have become so powerful for so little cost, linear programming has become possibly one of the most widespread uses for a personal PCThere are of course numerous software packages which are dedicated to solving linear programs (and other types of mathematical program), of which possibly LINDO, GAMS and XPRESS-MP arc the most popular. All these packages tend to be DOS based and are intended for a specialist market which requires tools dedicated to solving LPs. In recent years, however, several standard business packages, such as spreadsheets, have started to include an LP solving option, and Microsoft spreadsheet program-Excel is no exception. We can use Excel to solve the LP problem, and this is the solver add-in.