04专题02运用电子表格进行管理决策

1、管理信息系统专题 2：运用电子表格进行管理决策 （1）Excel 高级进阶日常行政事务表格设计预备知识拖动法调整双击自动调整菜单命令法调整一次调整多行与多列行高、列宽的调整预备知识使用“开始”菜单工具栏按钮使用快捷菜单“设置单元格格式”工作区域边框底纹设置预备知识使用“开始”菜单工具栏按钮合并后居中跨越合并合并单元格取消合并使用快捷工具栏按钮合并单元格预备知识使用快捷菜单“设置单元格格式”单元格日期格式设置预备知识使用数据菜单“数据有效性”数据有效性设置预备知识IF（logical_test，value_if_true，value_if_true）logical_test：条件表达式value

2、_if_true：表达式为真时的取值Value_False：表达式为假时的取值IF函数预备知识选定已输入公式的单元格拖放该单元格右下角的控制点公式的快速复制预备知识相对引用：复制公式中单元格地址会自动改变如：=B2+D2绝对引用：复制公式中单元格地址不变如：=C2+$C$6单元格的相对引用与绝对引用表格实例1.输入表头企业来访登记表表格实例2.调整表头格式企业来访登记表表格实例3.设置边框与底纹企业来访登记表表格实例4.设置序号与列单元格格式企业来访登记表表格实例1.输入表头公司文档资料借阅登记表表格实例2.调整表头格式公司文档资料借阅登记表表格实例3.使用Excel2010中套用表格样式功能

3、快速美化表格公司文档资料借阅登记表表格实例4.设置”资料类型”、“借阅状态”序列公司文档资料借阅登记表表格实例5.设置”可借天数”RF1：20天RF2：30天RF3：40天RF4：60天公司文档资料借阅登记表=IF(资料类型=RF1,20,IF(资料类型=RF2,30,IF(资料类型=RF3,40,IF(资料类型=RF4,60,)表格实例5.设置”应还日期”公司文档资料借阅登记表=IF(借阅时间=,借阅时间+可借天数)表格实例5.设置”是否过期”公司文档资料借阅登记表=IF(借阅状态=未还,IF(应还时间TODAY(),未过期,过期),)表格实例6.设置”过期天数”公司文档资料借阅登记表=IF

4、(是否过期=过期,TODAY()-应还时间,)表格实例5.设置”罚款金额”过期1-10天：5元过期10-20天：10元过期20-30天：15元过期超30天：30元公司文档资料借阅登记表=IF(过期天数=,IF(过期天数10,5,IF(过期天数20,10,IF(过期天数30,15,30)客户信息管理表格设计预备知识MAX（mumber1，mumber1，.）MIN（mumber1，mumber1，.）umber1，mumber1，.：从这些值里找出最大值或最小值MAX/MIN函数预备知识COUNTIF（range，criteria）Range：范围Criteria：条件COUNTIF函数: 计算

5、指定范围内符合条件的单元格数预备知识SUMIF（range，criteria，sum_range）Range：条件判定范围Criteria：条件sum_range：求和范围SUMIF函数: 对指定范围内符合条件的单元格求和预备知识MATCH（lookup_value，lookup_array,match_type）lookup_value：要匹配的值lookup_array：查找范围match_type：匹配方式（1：不大于lookup_value的最大值；0：等于lookup_value；-1：不小于lookup_value的最小值）MATCH函数: 返回指定区域内匹配某个值的单元格位置表格

6、实例1.输入表头企业客户资料统计表表格实例2.调整表头格式、边框、底纹企业客户资料统计表表格实例3.设置编号、开户行、经营范围、客户类型、授信等级企业客户资料统计表表格实例4.建立客户名称的超级链接企业客户资料统计表表格实例销售产品清单表格实例1.建立表头并设置样式客户销售记录表表格实例2.设置交易日期与交易数量格式客户销售记录表表格实例3.设置客户名称、产品编号序列客户销售记录表表格实例4.设置“产品名称”公式客户销售记录表=IF(D4=“”,“”,INDEX(销售产品清单!$C$4:$C$24,MATCH(D4,销售产品清单!$B$4:$B$24,0),1)表格实例5.设置“计量单位”公式

7、客户销售记录表=IF(D4=,INDEX(销售产品清单!$D$4:$D$24,MATCH($D$4,销售产品清单!$B$4:$B$24,0),1)表格实例7.设置“单价”公式客户销售记录表=IF(D4=,INDEX(销售产品清单!$E$4:$E$24,MATCH($D$4,销售产品清单!$B$4:$B$24,0),1)表格实例8.设置“客户类型”公式客户销售记录表=IF(C4=,INDEX(企业客户资料管理!$L$4:$L$13,MATCH(C4,企业客户资料管理!$C$4:$C$13,0),1)表格实例9.设置“折扣率”公式大客户：20%中客户：10%小客户：0%客户销售记录表=IF(I4=

8、,IF(I4=大客户,0.2,IF(I4=中客户,0.1,0)表格实例10.设置“交易额”公式客户销售记录表=IF(I4=,IF(I4=大客户,0.2,IF(I4=中客户,0.1,0)表格实例11.保护工作表客户销售记录表表格实例12.输入数据客户销售记录表表格实例1.建立表头并设置样式客户交易评估表表格实例2.粘贴客户编号与客户名称客户交易评估表表格实例3.设置“交易次数”公式客户交易评估表=COUNTIF(销售记录表!$C$4:$C$24,C4)表格实例4.设置“交易金额”公式客户交易评估表=SUMIF(销售记录表!$C$4:$C$24,C4,销售记录表!$K$4:$K$24)表格实例5.

9、设置“最多交易次数”公式客户交易评估表=MAX(D4:D13)表格实例6.设置“交易次数最多的客户”公式客户交易评估表=INDEX(C5:C14,MATCH(G7,D5:D14,0),1)表格实例7.设置“最大交易金额”公式客户交易评估表=MAX(E4:E13)表格实例8.设置“交易金额最大的客户”公式客户交易评估表=INDEX(C5:C14,MATCH(G12,E5:E14,0),1)表格实例9.设置条件格式客户交易评估表表格实例10.插入统计图表客户交易评估表表格实例10.按交易次数排序列客户交易评估表销售分析表格设计表格实例1.建立表头并设置样式销售产品清单表格实例2.设置自动换行销售产

10、品清单表格实例产品销售记录表表格实例建立数据透视表表格实例建立数据透视图决策分析表格表格实例产品生产计划决策线性规划决策品名成本(元/件)机器耗时(分钟/件)毛利(元/件)A产品45325B产品75550C产品105775约束项目约束条件每天原材料金额限制=15000每天生产时间约束=60件B产品产量约束=45件C产品产量约束=60件表格实例建立表格，输入基本参数与计算公式线性规划决策表格实例加载“规划求解”功能线性规划决策表格实例规划求解线性规划决策表格实例建立基本表格投资敏感性分析表格实例放入并设置流动条控件投资敏感性分析表格实例设置变动百分比，变化后数值公式投资敏感性分析表格实例自定义“

11、净现值”函数投资敏感性分析Public Function 净现值(初始投资, 期末残值, 寿命期, 年付现成本, 年销售量, 产品价格, 单位变动成本, 基准收益率, 所得税税率)净现金流量 = (年销售量 * (产品价格 - 单位变动成本) / 10000 - 年付现成本) * (1 - 所得税税率) + (初始投资 - 期末残值) / 寿命期 * 所得税税率净现值 = 净现金流量 * (1 - (1 + 基准收益率) -寿命期) / 基准收益率 - 初始投资 + 期末残值 / (1 + 基准收益率) 寿命期End Function表格实例设置多因素影响结果的公式投资敏感性分析表格实例设置单

12、因素影响结果的公式投资敏感性分析表格实例仿真分析步骤仿真分析建立仿真数学模型确实随机变量的分布规率，并用计算机产生随机数序列将随机数序列代入仿真数学模型进行计算分析计算结果，得出结论表格实例仿真案例仿真分析在需求不确定情况下，企业成品的最优库存与那些因素有关？这些因素又是如何影响最优库存的？表格实例仿真建模仿真分析建立仿真模型：设：D：每月需求台数（随机变量）P1：单位进价P2：单位销价C1：单位月库存费用C2：单位月缺货成本L：月初库存水平开始，设定初始数据设定月初存贮水平L(台)模拟月销售量DD L？售出D台售出L台毛收P=（P2P1）D毛收P=（P2P1）L存贮费用C=C1（L-D）缺货

13、损失C=C1（D-L）净收益=P-C结 束i T？YesNoNY模拟下月i=i+1表格实例确定并取得随机变量仿真分析假设D服从 N（，2）取得随机变量序列：在 EXCEL 中用下列函数取得：INT（NORMINV（RND（）， ，）表格实例设定初始值仿真分析10020P175P2125C115C230L70,80,90,100,110,120,130,140表格实例EXCEL进行模拟仿真分析ABCDEFG1存储水平=100234模拟样本模拟需求销售量销售收入存储成本缺货损失净收益51=INT(NORMINV(RAND(),100,20)=IF(B5$B$1,B5,$B$1)=50*C5=IF(

14、B5$B$1, 15*($B$1-B5),0)=IF(B5$B$1,0,30*(B5-$B$1)=D5-E5-F562=INT(NORMINV(RAND(),100,20)=IF(B6$B$1,B6,$B$1)=50*C6=IF(B6$B$1, 15*($B$1-B6),0)=IF(B6$B$1,0,30*(B6-$B$1)=D6-E6-F673=INT(NORMINV(RAND(),100,20)=IF(B7$B$1,B7,$B$1)=50*C7=IF(B7$B$1, 15*($B$1-B7),0)=IF(B7$B$1,0,30*(B7-$B$1)=D7-E7-F7表格实例EXCEL进行模拟

15、仿真分析表格实例模拟100个月，计算月平均净收益，找到最佳库存点仿真分析存贮水平L月平均净收益100台4271110台4523120台4610130台4598140台4505表格实例最优库存点是否与库存费用有关？仿真分析改变 C1，使C15,10,15,20,25，重复上述模拟过程，分别找出最优库存，找出最优库存点与单位月库存费用之间是否存在一定的规律。依此类推，可进一步考察最优库存点是否与缺货费用、购销成本，需求分布之间是否有关。模拟分析举例最优库存点是否与库存费用有关？改变 C1，使C15,10,15,20,25，重复上述模拟过程，分别找出最优库存，找出最优库存点与单位月库存费用之间是否存在一定的规律。依此类推，可进一步考察最优库