EXCEL高级应用与建模.ppt

《EXCEL高级应用与建模.ppt》由会员分享，可在线阅读，更多相关《EXCEL高级应用与建模.ppt（20页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、目录 入门与矩阵运算 通过优化问题看“规划求解” 人力资源例题 财务问题 通过库存仿真看“模拟运算” 通过股票组合优化看“数据分析” EXCEL中的DEA和AHPExcel的函数与建模 四则运算 + - * / 常用的函数 =sum() =average() =stdev() =sumproduct() = mdeterm() =mmult() =transpose() =minverse() =rand() 常用的宏函数宏函数 单变量求解单变量求解 模拟运算模拟运算 规划求解 数据分析Excel建模的要点2 输入公式时 首先是“=” 公式输入完毕，不要忘记“回车键” Excel的复制功能，公

2、式也能够复制 等差数列实现(全选) 1，2 3 复制 自然数 累加的实现 =1 =B1+A2 单元格有绝对地址和相对地址之分 F4功能键重复切换线性方程组Excel实现 矩阵行列式 =mdeterm() 矩阵求逆 =minverse() ctrl+shift+enter 矩阵乘积 =mmult() ctrl+shift+enter线性方程组矩阵表示线性方程组（一）线性方程组（一）2x1 + 2x2 x3 = 6x1 2x2 + 4x3 = 35x1 +7x2 + x3 = 28(1)(2)(3)系数矩阵系数矩阵A=2 2 -11 -2 45 7 1 常数项列阵常数项列阵 b= 6 328x1

3、x2x3未知变量列阵未知变量列阵 X=增广矩阵增广矩阵A=2 2 -1 61 -2 4 35 7 1 28 AX=b若若|A|0，且满秩，则，且满秩，则 X=A-1b人力资源问题（例8）工序工序紧固件1所需人工数紧固件2所需人工数各部门可提供人工数工序10.600.90650工序20.500.80500工序31.000.70700工序40.150.30120表8.1人力资源问题（例8）决策变量：X1-产品1的产量X2-产品2的产量O.bO.b： 利润最大例8 (1)问题求解：约束条件：约束条件： s.ts.t: : (1) (1)各部门的人力资源约束。即各部门实际各部门的人力资源约束。即各部门

4、实际使用人数不得超过可提供人数使用人数不得超过可提供人数 (2)(2)非负约束非负约束14 15 16 17 18 19 20 21 22 23 24 25 26 A B C D 生产计划生产计划产品1产品2决策变量决策变量646.1576.92总利润最大化总利润最大化69076.92人工数人工数实际使用数可提供数松弛量松弛量456.92=650193.08384.62=500115.38700.00=7000.00120.00=1200.00人力资源问题（例8）决策变量：X1-产品1的产量X2-产品2的产量Xij表示从i部门调到j部门的人工数。O.bO.b： 利润最大例8 (2)问题求解：约

5、束条件：约束条件： s.ts.t: : (1) (1)各部门的人力资源约束。原来的人力资各部门的人力资源约束。原来的人力资源约束右边发生变化，各部门可提供的源约束右边发生变化，各部门可提供的人工数人工数= =（原来可提供的人工数）（原来可提供的人工数）+ +（从（从其他部门调入的人工数）其他部门调入的人工数）- -（调出其他部（调出其他部门的人工数）门的人工数） (2)(2)人力资源调动数约束。即各部门调出人人力资源调动数约束。即各部门调出人工数不得超过工数不得超过5050人人 (3)(3)非负约束非负约束16 17 18 19 20 21 22 23 24 25 A B C D E 生产计划

6、生产计划产品1产品2606205总利润最大化总利润最大化74004转换矩阵转换矩阵调出调出调入调入12341005002000333000040000人力资源情况产 量总利润紧固件1紧固件2合计人力资源调配前6467772369077人力资源调配后606205811740047.1%财务管理问题（例6） 例5：某部门投资问题（题干详见管理运筹学P53，例8）问题描述：年份项目12345AX1aX2aX3aX4aX5aBX1bX2bX3bX4bCX3cDX2d财务管理问题（例6）决策变量：Xij=第i年初投资于j项目的金额（单位万元）O.bO.b： 第五年末该部门所拥有的资金额最大问题求解：约束

7、条件：约束条件： s.ts.t: : (1) (1)项目项目B B、C C、D D投资额的限制投资额的限制 (2)(2)部门每年末收回资金后，应把全部资金部门每年末收回资金后，应把全部资金投出去，手中不应有剩余的滞呆资金投出去，手中不应有剩余的滞呆资金 (3)(3)非负约束非负约束Multi-period Financial Models Brief Introduction: Finco Investment Corporation must determine investment strategy during the three years. At present (time 0),

8、$100000 is available for investment. There are five investments A, B,C,D, E available. The cash flow with investing $1 is given. Formulate an LP that will maximize cash on hand at time 3 (years from now).Project0123ABCDE-10-1-100.5-11.20010.500-10101.91.5To ensure that the companys portfolio is dive

9、rsified, Finco required that at most $75000 be place in any single investment.Finco can earn interest at 8% per year by keeping un-invested cash in money bank fund.Assume that returns from investments may be immediately reinvested, In addition, Finco can not borrow funds.E-1 Model00112012max1.91.51.

10、081000000.51.21.080.51.08, , ,75000, , , ,0zBDESA CDSACSBSABSESA B C D EA B C D E S S S排队系统1船舶装卸时间分布船舶到达时间间隔分布到达时刻上一艘传到达时刻+到达时间间隔开始装卸时刻max(到达时刻，上一艘船装卸完成时刻)装卸完成时刻开始装卸时刻+装卸时间模拟计算平均逗留时间、等待时间等排队系统2开始装卸时刻=模拟计算平均逗留时间、等待时间等 两个泊位均空闲时，进入其中一个；只有一个泊位空闲时，两个泊位均空闲时，进入其中一个；只有一个泊位空闲时，则进入这个泊位；当均不空闲时，则等待；则进入这个泊位；当均不空闲时，则等待；船舶到达时刻泊位最早开始空闲时刻泊位1开始空闲时刻=本次装卸完成时刻 上次两个泊位开始上次泊位1开始空闲时刻 空闲时刻相比泊位2开始空闲时刻=本次装卸完成时刻 上次两个泊位开始上次泊位2开始空闲时刻 空闲时刻相比Excel中相关函数 期望值：average() 方差：var() 标准方差：stdev() 相关系数：correl() 数据分析工具 Excel中的“宏” 简单宏制作