第9章数据的分析与决策

第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规划求解规划求解适用于需要通过多个变量的变化来找到一个目标值的情况。只要问题有单一的目标，有明确的不等式约束条件，有直接或间接影响约束条件的一组输入值，就可以应用规划求解解决。Excel规划求解问题由以下3部分组成（1）可变单元格（2）目标函数（3）约束条件打开Excel2007工作文件——【鼠标左键】单击【Microsoftoffice】按钮——【鼠标左键】单击【Excel选项】按钮；然后在弹出的【Excel选项】下——【鼠标左键】单击【加载项】——在【管理：】右方选择【Excel加载项】选项卡——【鼠标左键】单击【转到】按钮；再在弹出的【加载宏】方框下——【鼠标左键】勾选【规划求解加载项】——【鼠标左键】单击【确定】按钮；最后在Excel工作文档中【鼠标左键】单击【数据】选项卡即可看到位于该选项卡下右方的【数据分析】模块，【鼠标左键】单击【规划求解】即可看到【规划求解】模块。案例某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等。该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料4种。为使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下各表所示。问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？表2生产肥料的库存原材料库存情况现有库存泥土4100有机垃圾3200矿物质3500修剪物1600表1各肥料成品用料及其价格表产品泥土有机垃圾矿物质修剪物单价底层肥料55547623105.00中层肥料6432452084.00上层肥料43329844105.00劣质肥料1845231857.00表3单位原材料成本单价项目单位成本泥土0.20有机垃圾0.15矿物质0.10修剪物0.23规划求解第一步——建立求解工作表规划求解第二步——设置求解参数选择“开发工具”|“规划求解”菜单，设置求解的各项参数，如下图所示。规划求解第3步——求解规划求解的结果报告1.运算结果报告2、敏感性报告3、极限报告9.3数据的高级分析与处理9.3.2规划求解已知某家具厂目前库存情况是：木料4000个单位，油漆4500个单位，活页2000个单位，螺丝2000个单位。工厂生产A,B,C,D这四种产品。不同产品消耗的材料不同，售价也不同。试问，该厂利用现有的库存应该生产4种产品各多少件才能达到利润的最大化。9.3数据的高级分析与处理9.3.2规划求解—①建立规划模型9.3数据的高级分析与处理9.3.2规划求解—②规划求解9.3数据的高级分析与处理9.3.2规划求解—②规划求解9.3数据的高级分析与处理9.3.2规划求解—②规划求解9.3数据的高级分析与处理9.3.2规划求解—②规划求解9.3数据的高级分析与处理9.3.3回归分析回归分析研究变量之间的非确定因果关系。通常把其中的一些变量作为自变量（预测变量，X），来预测另外一些随自变量变化而变化的变量（被预测变量，Y）。9.3数据的高级分析与处理9.3.3回归分析—线性回归两个变量之间有准确严格的直线相关关系。一个变量的变化引起另外一个变量变化的程度可以用线性回归方程式来表示。9.3数据的高级分析与处理9.3.3回归分析—线性回归例如，下表是某公司10年来广告费和销售量数据，试探据此估计该公司为了达到4000万的销售量，2010年的广告费应投入多少。9.3数据的高级分析与处理9.3.3回归分析—线性回归①9.3数据的高级分析与处理9.3.3回归分析—线性回归②9.3数据的高级分析与处理9.3.3回归分析—线性回归③9.3数据的高级分析与处理回归系数：选中b15:c15，输入公式=LINEST(C2:C11,B2:B11),按下Ctrl+shift+Alt相关系数=CORREL(C2:C11,B2:B11)9.3数据的高级分析与处理9.3.3回归分析—非线性回归两个变量之间之间的关系不是单纯的直线关系。如年龄与收入的关系，这种情况下要用到非线性回归分析方法。9.3数据的高级分析与处理9.3.3回归分析—非线性回归例如，根据以下基本数据，预测某人65岁时的收入。9.3数据的高级分析与处理9.3.3根据以下基本数据，预测某人65岁时的收入。9.3数据的高级分析与处理9.3.4多元线性回归分析一元回归分析适用于分析一个自变量和一个因变量之间的关系，而在实际情况中，常会遇到一个因变量和多个自变量数量关系的问题，多元回归分析正是为了解决这些问题而产生的。9.3数据的高级分析与处理9.3.4多元线性回归分析案例：销售公司在各大城市都有分销公司，公司采用的是经销商销售模式。公司统计了2005年各大城市的人数、公司在该城市的经销商个数以及销售量的数值，公司需要以该数据为基础，分析影响公司销售情况的各个因素以及预测公司的销售前景。9.3数据的高级分析与处理9.3.4多元线性回归分析9.3数据的高级分析与处理9.3.4多元线性回归分析案例实现1、加载“数据分析”宏。选择“开发工具”-“加载项”，打开加载宏对话框，在其中选择加载“分析工具库”，然后单击“确定”按钮。2、打开“回归”对话框。选择“数据”-“数据分析”选项，打开“数据分析”对话框，在其中选择“回归”选项，单击“确定”按钮。9.3数据的高级分析与处理9.3.4多元线性回归分析案例实现3、设置回归参数。在回归对话框中，在Y值输入区域选择因变量数据区，在X值输入区域选择自变量数据区域。同时选择“线性拟合图”选项。4、根据回归结果得到回归函数。