EXCEL高级应用与建模.pptVIP

EXCEL高级应用与建模 杨静蕾 目录 入门与矩阵运算 通过优化问题看“规划求解” 人力资源例题 财务问题 通过库存仿真看“模拟运算” 通过股票组合优化看“数据分析” EXCEL中的DEA和AHP Excel的函数与建模 四则运算 + - * / ^ 常用的函数 =sum()=average() =stdev()=sumproduct() = mdeterm() =mmult() =transpose() =minverse() =rand() 常用的宏函数 单变量求解 模拟运算 规划求解 数据分析 Excel建模的要点2 输入公式时 首先是“=” 公式输入完毕，不要忘记“回车键” Excel的复制功能，公式也能够复制 等差数列实现(全选) 1，2 3 复制 自然数 累加的实现 =1 =B1+A2 单元格有绝对地址和相对地址之分 F4功能键重复切换 线性方程组—Excel实现 矩阵行列式 =mdeterm() 矩阵求逆 =minverse() ctrl+shift+enter 矩阵乘积 =mmult() ctrl+shift+enter 线性方程组—矩阵表示 人力资源问题（例8） 人力资源问题（例8） 决策变量： X1-产品1的产量 X2-产品2的产量 Xij—表示从i部门调到j部门的人工数。 O.b： 利润最大 财务管理问题（例6） 例5：某部门投资问题 （题干详见《管理运筹学》P53，例8）问题描述： 财务管理问题（例6） 决策变量： Xij=第i年初投资于j项目的金额（单位万元） O.b： 第五年末该部门所拥有的资金额最大 Multi-period Financial Models Brief Introduction: Finco Investment Corporation must determine investment strategy during the three years. At present (time 0), $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). E-1 Model Excel中相关函数 期望值：average() 方差：var() 标准方差：stdev() 相关系数：correl() 数据分析工具 Excel中的“宏” 简单宏制作 * 线性方程组（一） 2x1 + 2x2 – x3 = 6 x1 – 2x2 + 4x3 = 3 5x1 +7x2 + x3 = 28 (1) (2) (3) 系数矩阵 A= 2 2 -1 1 -2 4 5 7 1 常数项列阵 b= 6 3 28 x1 x2 x3 未知变量列阵 X= 增广矩阵A’= 2 2 -1 6 1 -2 4 3 5 7 1 28AX=b 若|A|≠0，且满秩，则X=A-1×b 650 0.90 0.60 工序1 120 0.30 0.15 工序4 700 0.70 1.00 工序3 500 0.80 0.50 工序2 各部门可提供人工数 紧固件2所需人工数 紧固件1所需人工数 工序 表8.1 人力资源问题（例8） 决策变量： X1-产品1的产量 X2-产品2的产量 O.b： 利润最大 例8 (1)问题求解： 约束条件： s.t: (1)各部门的人力资源约束。即各部门实际使用人数不得超过可提供人数 (2)非负约束 例8 (2)问题求解： 约束条件： s.t: (1)各部门的人力资源约束。原来的人力资源约束右边发生变化，各部门可提供的人工数=（原来可提供的人工数）+（从其他部门调入的人工数）-（调出其他部门的人工数） (2)人力资源调动数约束。即各部门调出人工数不得超过50人 (3)非负约束 7.1% 74004 811 205 606 人力资源调配后 69077 723 77 646 人力资源调配前 合计 紧固件2 总利润 紧固件1 产 量 人力资源情况 X5a 5 X4b X3b X2b X1b B X2d D X3c C X4a X3a X2a X1a A 4 3 2 1 年份 项目 问题求解： 约束条件： s.t: (1)项目B、C、D投资额的限制 (2)部门每年末收回资金后，应把全部资金