数据的分析与决策概述和特殊情况下的投资决策

第9章数据的分析与决策9.1数据透视表9.2数据透视图9.3数据的高级分析与处理9.1数据透视表数据透视表可以将大量数据转换成汇总表。从本质上讲，数据透视表是从数据库产生的一个动态汇总表格。9.1数据透视表9.1.1数据透视表的创建基本数据表使用数据透视表统计每个学院不同地区的学生数和平均入学成绩。9.1数据透视表9.1.1数据透视表的创建①单击数据表中任意位置，选择“插入”-“数据透视表”9.1数据透视表9.1.1数据透视表的创建②设置透视表的布局9.1数据透视表9.1.1数据透视表的创建9.1数据透视表9.1.2数据透视表的查看如：查看安徽地区男女生的人数。9.1数据透视表9.1.3数据透视表的编辑数据表建好之后，表中的数据不可能一成不变。用户可以根据需要更改数据透视表的数据源。如添加、删除数据透视表的字段，更新数据透视表中的数据等等。9.1数据透视表9.1.3数据透视表的编辑更改数据源9.1数据透视表9.1.3数据透视表的编辑更新透视表中的数据当数据源的数据发生改变时，所创建的数据透视表中的数据不会发生改变。为了使透视表中的数据，随着数据源中的数据改变而改变，需要更新数据透视表中的数据。9.1数据透视表9.1.3数据透视表的编辑更新透视表中的数据①选中数据透视表中的任意单元格②执行“数据”→“刷新”命令9.1数据透视表9.1.3数据透视表的编辑更改透视表中的数据汇总方式9.1数据透视表9.1.3数据透视表的编辑改变数据透视表的布局9.1数据透视表9.1.3数据透视表的编辑报表布局数据透视表为用户提供了“以压缩形式显示”、“以大纲形式显示”和“以表格形式显示”3种报表布局的显示形式。9.1数据透视表9.1.3数据透视表的编辑显示报表筛选页通过选择报表筛选字段的项目，可以对整个数据透视表的内容进行筛选，但筛选结果仍然显示在一张表格中，即每次只能进行一种筛选。利用【数据透视表工具】的【显示报表筛选页】功能，可以创建一系列链接在一起的数据透视表，每一张工作表显示报表筛选字段中的一项。9.1数据透视表9.1.3数据透视表的编辑重命名字段

如果要对字段重命名，让列标题更加简洁，可以直接修改数据透视表的字段名称。数据透视表中每个字段的名称必须唯一，Excel不接受任意两个字段具有相同的名称，即创建的数据透视表的各个宇段的名称不能相同，创建的数据透视表字段名称与数据源表头标题行的名称也不能相同，否则将会出现错误提示。9.2数据透视图使用数据透视表可以准确计算和分析数据，但是有时候如果数据源较大，则数据透视表中的数据将非常多，数据排列将非常复杂。与数据透视表相比，数据透视图更能直观地分析数据，展示数据之间的关系。创建数据透视图会同时创建数据透视表9.2数据透视图9.2.1创建数据透视图-直接创建9.2数据透视图9.2.1创建数据透视图9.2数据透视图9.2.1创建数据透视图-用数据透视表创建

选中已经创建的数据透视表-“插入”-“图表”9.2数据透视图9.2.2数据透视图的修饰-改变透视图的布局9.2数据透视图9.2.2数据透视图的修饰-改变透视图的格式①9.3数据的高级分析与处理9.3.1合并计算合并计算是指用来汇总一个或多个源区域中数据的方法。在进行合并计算时，首先必须为汇总信息定义一个目的区，另外需要选择要合并计算的数据项。数据源可以来自一个工作表、多个工作表或多个工作簿。合并计算包括按位置合并计算和按分类合并计算9.3数据的高级分析与处理9.3.1合并计算-按位置合并计算按位置合并计算是指在所有数据源区域中的数据被相同地排列以进行合并计算。按位置合并计算只适合具有相同结构数据区域的计算。9.3数据的高级分析与处理9.3.1合并计算-按位置合并计算①插入新工作表，并在其中输入上图内容9.3数据的高级分析与处理9.3.1合并计算-按位置合并计算②选中单元格区域，执行“合并计算”命令9.3数据的高级分析与处理9.3.1合并计算-按位置合并计算③

“合并计算”对话框中确定引用位置9.3数据的高级分析与处理9.3.1合并计算-按位置合并计算④

“合并计算”结果9.3数据的高级分析与处理9.3.1合并计算-按分类合并计算如果要汇总计算一组具有相同的行和列标志但以不同的方式组织数据的工作表,则可按分类进行合并计算。这种方法对每一张工作表中具有相同标志的数据进行合并计算。9.3数据的高级分析与处理9.3.1合并计算-按分类合并计算①插入新工作表，并在其中输入上图内容9.3数据的高级分析与处理9.3.1合并计算-按分类合并计算②将光标定位A2单元格，执行“合并计算”命令9.3数据的高级分析与处理9.3.1合并计算-按分类合并计算③将光标定位A单元格，执行“合并计算”命令9.3数据的高级分析与处理9.3.1合并计算-按分类合并计算④执行“合并计算”结果9.3数据的高级分析与处理9.3.1合并计算-多表分类汇总9.3数据的高级分析与处理9.3.2单变量求解单变量求解就是寻求公式中的特定解。使用单变量求解，能够通过调整可变单元格中的数值，按照给定公式来满足目标单元格中的目标值。解决一些实际生活和工作中的问题。9.3数据的高级分析与处理9.3.2单变量求解假设某人每月工资收入为1200元，其中基本生活费600，房租费220，电话费100以及车费50已经不能再进行调整，可自由支配的费用是230。如果现在工资上涨为2023，那么可自由支配的费用将变成多少？9.3数据的高级分析与处理9.3.2单变量求解①在目标单元格中进行求和计算9.3数据的高级分析与处理9.3.2单变量求解②执行“工具”→“单变量求解”命令9.3数据的高级分析与处理9.3.3方案求解实际工作中，可能遇到很多可变因素的问题，或者要在几种假设中找出最佳执行方案。这就需要用方案管理器，创建和管理自定义的方案9.3数据的高级分析与处理9.3.3方案求解—案例已知某食品公司2023年的总销售额以及各种食品的销售成本，要在此基础上制定一个五年计划。由于市场的变化，只能对总销售额及各种食品销售成本的增长率做一些估计。最好的方案就是总销售额增长率高，各种食品的销售成本增长率低。9.3数据的高级分析与处理9.3.3方案求解—案例假设2023年总销售额为800000，饼干、牛肉干、方便面和饮料的销售成本分别为50000，60000，70000和80000。最好的估计是总销售额增长率达到18%，饼干、牛肉干、方便面和饮料的销售成本分别增长8%，9%，12%和7%。最坏的估计是饼干、牛肉干、方便面和饮料的销售成本分别增长9%，12%，15%和9%。9.3数据的高级分析与处理9.3.3方案求解—案例最好的估计情况下，公司五年内的成本及利润估算情况如图所示：9.3数据的高级分析与处理9.3.3方案求解—定义方案①从“数据”-“模拟分析”-“方案管理器”9.3数据的高级分析与处理9.3.3方案求解—定义方案②将估算结果保存为方案9.3数据的高级分析与处理9.3.3方案求解—定义方案③将估算结果保存为方案9.3数据的高级分析与处理9.3.3方案求解—定义方案④将估算结果保存为方案9.3数据的高级分析与处理9.3.3方案求解—定义方案⑤将估算结果保存为方案9.3数据的高级分析与处理9.3.3方案求解—定义方案⑥将估算结果保存为方案9.3数据的高级分析与处理9.3.3方案求解—定义方案⑦将估算结果保存为方案9.3数据的高级分析与处理9.3.3方案求解—定义方案⑧将估算结果保存为方案9.3数据的高级分析与处理9.3.3方案求解—显示方案建立方案后，任何时候都可以查看不同方案对总利润带来的影响。9.3数据的高级分析与处理9.3.3方案求解—合并方案9.3数据的高级分析与处理9.3.3方案求解—合并方案合并之前合并之后9.3数据的高级分析与处理9.3.3方案求解—建立方案报告当用户对一张工作表建立多个方案时，如果想对比各种方案，可以建立方案报告。建立方案报告，便于对各种方案采用的可变参数及方案结果进行对比并寻求最佳方案。9.3数据的高级分析与处理9.3.3方案求解—建立方案报告①9.3数据的高级分析与处理9.3.3方案求解—建立方案报告②9.3数据的高级分析与处理9.3.3方案求解—建立方案报告③9.3数据的高级分析与处理9.3.3方案求解—建立方案报告④9.3数据的高级分析与处理9.3.4规划求解规划求解适用于需要通过多个变量的变化来找到一个目标值的情况。只要问题有单一的目标，有明确的不等式约束条件，有直接或间接影响约束条件的一组输入值，就可以应用规划求解解决。9.3数据的高级分析与处理9.3.4规划求解已知某家具厂目前库存情况是：木料4000个单位，油漆4500个单位，活页2023个单位，螺丝2023个单位。工厂生产A,B,C,D这四种产品。不同产品消耗的材料不同，售价也不同。试问，该厂利用现有的库存应该生产4种产品各多少件才能达到利润的最大化。9.3数据的高级分析与处理9.3.4规划求解—①建立规划模型9.3数据的高级分析与处理9.3.4规划求解—②规划求解9.3数据的高级分析与处理9.3.4规划求解—②规划求解9.3数据的高级分析与处理9.3.4规划求解—②规划求解9.3数据的高级分析与处理9.3.4规划求解—②规划求解9.3数据的高级分析与处理9.3.5回归分析回归分析研究变量之间的非确定因果关系。通常把其中的一些变量作为自变量（预测变量，X），来预测另外一些随自变量变化而变化的变量（被预测变量，Y）。9.3数据的高级分析与处理9.3.5回归分析—线性回归两个变量之间有准确严格的直线相关关系。一个变量的变化引起另外一个变量变化的程度可以用线性回归方程式来表示。9.3数据的高级分析与处理9.3.5回归分析—线性回归例如，下表是某公司10年来广告费和销售量数据，试探据此估计该公司为了达到4000万的销售量，2023年的广告费应投入多少。9.3数据的高级分析与处理9.3.5回归分析—线性回归①9.3数据的高级分析与处理9.3.5回归分析—线性回归②9.3数据的高级分析与处理9.3.5回归分析—线性回归③9.3数据的高级分析与处理回归系数：选中b15:c15，输入公式=LINEST(C2:C11,B2:B11),按下Ctrl+shift+Alt相关系数=CORREL(C2:C11,B2:B11)9.3数据的高级分析与处理9.3.5回归分析—非线性回归两个变量之间之间的关系不是单纯的直线关系。如年龄与收入的关系，这种情况下要用到非线性回归分析方法。9.3数据的高级分析与处理9.3.5回归分析—非线性回归例如，根据以下基本数据，预测某人65岁时的收入。9.3数据的高级分析与处理9.3.5根据以下基本数据，预测某人65岁时的收入。9.3数据的高级分析与处理9.3.6多元线性回归分析一元回归分析适用于分析一个自变量和一个因变量之间的关系，而在实际情况中，常会遇到一个因变量和多个自变量数量关系的问题，多元回归分析正是为了解决这些问题而产生的。9.3数据的高级分析与处理9.3.6多元线性回归分析案例：销售公司在各大城市都有分销公司，公司采用的是经销商销售模式。公司统计了2023年各大城市的人数、公司在该城市的经销商个数以及销售量的数值，公司需要以该数据为基础，分析影响公司销售情况的各个因素以及预测公司的销售前景。9.3数据的高级分析与处理9.3.6多元线性回归分析9.3数据的高级分析与处理9.3.6多元线性回归分析案例实现1、加载“数据分析”宏。选择“开发工具”-“加载项”，打开加载宏对话框，在其中选择加载“分析工具库”，然后单击“确定”按钮。2、打开“回归”对话框。选择“数据”-“数据分析”选项，打开“数据分析”对话框，在其中选择“回归”选项，单击“确定”按钮。9.3数据的高级分析与处理9.3.6多元线性回归分析案例实现3、设置回归参数。在回归对话框中，在Y值输入区域选择因变量数据区，在X值输入区域选择自变量数据区域。同时选择“线性拟合图”选项。4、根据回归结果得到回归函数。几种特殊情况下的投资决策设备更新决策设备更新决策是比较设备更新与否对企业的利弊。通常采用净现值作为投资决策指标。设备更新决策可采用两种决策方法，一种是比较新、旧两种设备各自为企业带来的净现值的大小；另一种是计算使用新、旧两种设备所带来的现金流量差量，考察这一现金流量差量的净现值的正负，进而做出恰当的投资决策。例（教材97－98页）

方法1，新旧设备净现值比较继续使用旧设备:每年经营现金流量为20万元，净现值为：NPV＝20万元×PVIFA（10％，10）＝20万元×6.145＝122.9万元使用新设备：初始投资额＝120－10－16＝94(万元)经营现金流量现值＝40×PVIFA(10%,10)＝40×6.145＝245.8(万元)终结现金流量现值＝20×0.386＝7.72(万元)净现值＝－94＋245.8＋7.72＝159.52(万元)由于使用新设备的净现值大于继续使用旧设备的净现值，故采用新设备。方法2：差量比较法初始投资额＝120－10－16＝94(万元)经营现金流量差量＝40－20＝20(万元)经营现金流量差量现值＝20×6.145＝122.9(万元)终结现金流量现值＝20×0.386＝7.72(万元)现金流量差量净现值＝－94＋122.9＋7.72＝36.62(万元)设备比较决策这一决策比较购置不同设备的效益高低。一般来讲，进行这一决策时应比较不同设备带来的成本与收益，进而比较其各自净现值的高低。但有时我们也假设不同设备带来的收益是相同的，因而只比较其成本高低即可。很多情况下，不同设备的使用期限是不同的，因此我们不能直接比较不同设备在使用期间的净现值大小，而需要进行必要的调整。这种调整有两种：一种是将不同设备的净现值转化为年金。一种是将不同设备转化为相同的使用年限。例：（教材98－99页）

设备A、B的使用期间成本现值分别为643573元和471622元，虽然B设备的成本现值小于设备A，但使用期限也小于设备A，所以二着不能直接比较。方法1，等年金比较年金现值公式：PV＝A×年金现值系数所以：A＝PV/年金现值系数A设备的成本现值＝40＋6.1×PVIFA(8%,5)=40＋6.1元×3.993＝64.36万元其年金为：AA＝64.36万元/3.993＝16.12万元B设备的成本现值＝25＋8.6×PVIFA(8%,5)＝25＋8.6×3.993＝47.16(万元)其年金为：AB＝47.16元/3.993＝18.30万元

由于设备B的成本年金高于设备A，所以应选择设备A。方法2，按相同使用年限比较这一方法首先要求出设备A、B使用年限的最小公倍数（本例中是15），在15年内，设备A要更新3次，设备B要更新5次，分别计算出使用设备A、B各15年的总成本现值进行比较。资本限量决策资本限量决策是在投资资金有限的情况下，选择能给企业带来最大投资收益的投资组合。资本限量产生的原因：企业无法筹措到足够的资金；企业成长过快，缺乏足够的管理