1、第第9 9章章 数据的分析与决策数据的分析与决策o9.1 9.1 数据透视表数据透视表o9.2 9.2 数据透视图数据透视图o9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.1 9.1 数据透视表数据透视表p数据透视表可以将大量数据转换成汇总表。数据透视表可以将大量数据转换成汇总表。p从本质上讲，数据透视表是从数据库产生的从本质上讲，数据透视表是从数据库产生的一个动态汇总表格。一个动态汇总表格。9.1 9.1 数据透视表数据透视表9.1.1 9.1.1 数据透视表的创建数据透视表的创建基本数据表基本数据表o使用数据透视表统计每使用数据透视表统计每个学院不同地区的学生个学院不同地区的
2、学生数和平均入学成绩。数和平均入学成绩。9.1 9.1 数据透视表数据透视表9.1.1 9.1.1 数据透视表的创建数据透视表的创建单击数据表中任意位置，选择单击数据表中任意位置，选择“插入插入”-“数据透视表数据透视表”9.1 9.1 数据透视表数据透视表9.1.1 9.1.1 数据透视表的创建数据透视表的创建设置透视表的布局设置透视表的布局9.1 9.1 数据透视表数据透视表9.1.1 9.1.1 数据透视表的创建数据透视表的创建9.1 9.1 数据透视表数据透视表9.1.2 9.1.2 数据透视表的查看数据透视表的查看如：查看安徽地区男女生如：查看安徽地区男女生的人数。的人数。9.1 9
3、.1 数据透视表数据透视表9.1.3 9.1.3 数据透视表的编辑数据透视表的编辑o数据表建好之后，表中的数据不可能一成不变。数据表建好之后，表中的数据不可能一成不变。o用户可以根据需要更改数据透视表的数据源。如用户可以根据需要更改数据透视表的数据源。如添加、删除数据透视表的字段，更新数据透视表添加、删除数据透视表的字段，更新数据透视表中的数据等等。中的数据等等。9.1 9.1 数据透视表数据透视表9.1.3 9.1.3 数据透视表的编辑数据透视表的编辑更改数据源更改数据源9.1 9.1 数据透视表数据透视表9.1.3 9.1.3 数据透视表的编辑数据透视表的编辑更新透视表中的数据更新透视表中
4、的数据o当数据源的数据发生改变时，所创建的数据透视表中当数据源的数据发生改变时，所创建的数据透视表中的数据不会发生改变。的数据不会发生改变。o为了使透视表中的数据，随着数据源中的数据改变而为了使透视表中的数据，随着数据源中的数据改变而改变，需要更新数据透视表中的数据。改变，需要更新数据透视表中的数据。9.1 9.1 数据透视表数据透视表9.1.3 9.1.3 数据透视表的编辑数据透视表的编辑更新透视表中的数据更新透视表中的数据选中数据透视表中的任意单元格选中数据透视表中的任意单元格执行执行“数据数据”“刷新刷新”命令命令9.1 9.1 数据透视表数据透视表9.1.3 9.1.3 数据透视表的编
5、辑数据透视表的编辑更改透视表中的数据汇总方式更改透视表中的数据汇总方式9.1 9.1 数据透视表数据透视表o9.1.3 9.1.3 数据透视表的编辑数据透视表的编辑改变数据透视表的布局改变数据透视表的布局9.1 9.1 数据透视表数据透视表9.1.3 9.1.3 数据透视表的编辑数据透视表的编辑o报表布局报表布局 数据透视表为用户提供了数据透视表为用户提供了“以压缩形式显示以压缩形式显示”、“以大纲形式显示以大纲形式显示”和和“以表格形式显示以表格形式显示”3种报种报表布局的显示形式。表布局的显示形式。9.1 9.1 数据透视表数据透视表o9.1.3 9.1.3 数据透视表的编辑数据透视表的编
6、辑o显示报表筛选页显示报表筛选页 通过选择报表筛选字段的项目，可以对整个数据透通过选择报表筛选字段的项目，可以对整个数据透视表的内容进行筛选，但筛选结果仍然显示在一张视表的内容进行筛选，但筛选结果仍然显示在一张表格中，即每次只能进行一种筛选。利用表格中，即每次只能进行一种筛选。利用【数据透数据透视表工具视表工具】的的【显示报表筛选页显示报表筛选页】功能，可以创建功能，可以创建一系列链接在一起的数据透视表，每一张工作表显一系列链接在一起的数据透视表，每一张工作表显示报表筛选字段中的一项。示报表筛选字段中的一项。9.1 9.1 数据透视表数据透视表o9.1.3 9.1.3 数据透视表的编辑数据透视
7、表的编辑重命名字段重命名字段 如果要对字段重命名，让列标题更加简洁，可以直如果要对字段重命名，让列标题更加简洁，可以直接修改数据透视表的字段名称。接修改数据透视表的字段名称。数据透视表中每个字段的名称必须唯一，数据透视表中每个字段的名称必须唯一，Excel不不接受任意两个字段具有相同的名称，即创建的数据接受任意两个字段具有相同的名称，即创建的数据透视表的各个宇段的名称不能相同，创建的数据透透视表的各个宇段的名称不能相同，创建的数据透视表字段名称与数据源表头标题行的名称也不能相视表字段名称与数据源表头标题行的名称也不能相同，否则将会出现错误提示。同，否则将会出现错误提示。9.2 9.2 数据透视
8、图数据透视图p使用数据透视表可以准确计算和分析数据，但是有使用数据透视表可以准确计算和分析数据，但是有时候如果数据源较大，则数据透视表中的数据将非时候如果数据源较大，则数据透视表中的数据将非常多，数据排列将非常复杂。常多，数据排列将非常复杂。p与数据透视表相比，数据透视图更能直观地分析数与数据透视表相比，数据透视图更能直观地分析数据，展示数据之间的关系。据，展示数据之间的关系。p创建数据透视图会同时创建数据透视表创建数据透视图会同时创建数据透视表9.2 9.2 数据透视图数据透视图9.2.1 9.2.1 创建数据透视图创建数据透视图-直接创建直接创建9.2 9.2 数据透视图数据透视图9.2.
9、1 9.2.1 创建数据透视图创建数据透视图9.2 9.2 数据透视图数据透视图9.2.1 9.2.1 创建数据透视图创建数据透视图-用数据透视表创建用数据透视表创建 选中已经创建的数据透视表选中已经创建的数据透视表-“插入插入”-“图表图表”9.2 9.2 数据透视图数据透视图9.2.2 9.2.2 数据透视图的修饰数据透视图的修饰-改变透视图的布局改变透视图的布局9.2 9.2 数据透视图数据透视图9.2.2 9.2.2 数据透视图的修饰数据透视图的修饰-改变透视图的格式改变透视图的格式9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算o合并计
10、算是指用来汇总一个或多个源区域中数据的方法。合并计算是指用来汇总一个或多个源区域中数据的方法。o在进行合并计算时，首先必须为汇总信息定义一个目的在进行合并计算时，首先必须为汇总信息定义一个目的区，另外需要选择要合并计算的数据项。区，另外需要选择要合并计算的数据项。o数据源可以来自一个工作表、多个工作表或多个工作簿。数据源可以来自一个工作表、多个工作表或多个工作簿。o合并计算包括按位置合并计算和按分类合并计算合并计算包括按位置合并计算和按分类合并计算9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按位置合并计算按位置合并计算o按位置合并计算是指在
11、所有数据源区域中的数据被相按位置合并计算是指在所有数据源区域中的数据被相同地排列以进行合并计算。同地排列以进行合并计算。o按位置合并计算只适合具有相同结构数据区域的计算。按位置合并计算只适合具有相同结构数据区域的计算。9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按位置合并计算按位置合并计算插入新工作表，并在其中输入上图内容插入新工作表，并在其中输入上图内容9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按位置合并计算按位置合并计算选中单元格区域，执行选中单元格区域，执行“合并计算合并计算”命
12、令命令9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按位置合并计算按位置合并计算 “合并计算合并计算”对话框中确定引用位置对话框中确定引用位置9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按位置合并计算按位置合并计算 “合并计算合并计算”结果结果9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按分类合并计算按分类合并计算o如果要汇总计算一组具有相同的行和列标志但以不同如果要汇总计算一组具有相同的行和列标志但以不同的方式组织数据的工作表的方式组
13、织数据的工作表,则可按分类进行合并计算。则可按分类进行合并计算。o这种方法对每一张工作表中具有相同标志的数据进行这种方法对每一张工作表中具有相同标志的数据进行合并计算。合并计算。9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按分类合并计算按分类合并计算插入新工作表，并在其中输入上图内容插入新工作表，并在其中输入上图内容9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按分类合并计算按分类合并计算将光标定位将光标定位A2A2单元格，执行单元格，执行“合并计算合并计算”命令命令9.3 9.3 数据的
14、高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按分类合并计算按分类合并计算将光标定位将光标定位A A单元格，执行单元格，执行“合并计算合并计算”命令命令9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-按分类合并计算按分类合并计算执行执行“合并计算合并计算”结果结果9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.1 9.3.1 合并计算合并计算-多表分类汇总多表分类汇总9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.2 9.3.2 规划求解规划求解o规划求解适用于需要通过多个变量的变化
15、来找到一个规划求解适用于需要通过多个变量的变化来找到一个目标值的情况。目标值的情况。o只要问题有单一的目标，有明确的不等式约束条件，只要问题有单一的目标，有明确的不等式约束条件，有直接或间接影响约束条件的一组输入值，就可以应有直接或间接影响约束条件的一组输入值，就可以应用规划求解解决。用规划求解解决。Excel规划求解问题由以下规划求解问题由以下3部分组成部分组成 n（1）可变单元格）可变单元格n（2）目标函数）目标函数n（3）约束条件）约束条件o打开打开Excel2007工作文件工作文件【鼠标左键鼠标左键】单单击击【Microsoft office】按钮按钮【鼠标左键鼠标左键】单击单击【Ex
16、cel 选项选项】按钮；按钮；o然后在弹出的然后在弹出的【Excel选项选项】下下【鼠标左键鼠标左键】单击单击【加载项加载项】在在【管理：管理：】右方选择右方选择【Excel 加载项加载项】选项卡选项卡【鼠标左键鼠标左键】单击单击【转到转到】按钮；按钮；再在弹出的再在弹出的【加载宏加载宏】方框下方框下【鼠标左键鼠标左键】勾选勾选【规划求解加载项规划求解加载项】【鼠标左键鼠标左键】单击单击【确定确定】按钮；按钮；o最后在最后在Excel工作文档中工作文档中【鼠标左键鼠标左键】单击单击【数据数据】选项卡即可看到位于该选项卡下右方的选项卡即可看到位于该选项卡下右方的【数据分数据分析析】模块，模块，【
17、鼠标左键鼠标左键】单击单击【规划求解规划求解】即可看即可看到到【规划求解规划求解】模块。模块。案例n某肥料厂专门收集有机物垃圾，如青草、树枝、凋某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等。该厂利用这些废物，并掺进不同比例谢的花朵等。该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的的泥土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥肥料分为底层肥料、中层肥料、上层肥料、劣质肥料料4种。为使问题简单，假设收集废物的劳动力是种。为使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。自愿的，除了收集成
18、本之外，材料成本是低廉的。n该厂目前的原材料、生产各种肥料需要的原材料比该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下各表所示。例，各种肥料的单价等如下各表所示。n问题：求出在现有的情况下，即利用原材料的现有问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得库存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？最大利润，最大利润是多少？表2 生产肥料的库存原材料库存情况库存情况现有库存现有库存泥土泥土4100有机垃圾有机垃圾3200矿物质矿物质3500修剪物修剪物1600表1 各肥料成品用料及其价格表产品产品泥土泥土有
19、机垃圾有机垃圾矿物质矿物质修剪物修剪物单价单价底层肥料底层肥料55547623105.00中层肥料中层肥料6432452084.00上层肥料上层肥料43329844105.00劣质肥料劣质肥料1845231857.00表表3单位原材料成本单价单位原材料成本单价项 目单位成本泥土0.20有机垃圾0.15矿物质0.10修剪物0.23o规划求解第一步规划求解第一步建立求解工作表建立求解工作表规划求解第二步规划求解第二步设置求解参数设置求解参数n选择选择“开发开发工具工具”|“规划求解规划求解”菜单，设置求菜单，设置求解的各项参数，如下图所示。解的各项参数，如下图所示。规划求解第规划求解第3步步求解求
20、解o 规划求解的结果报告规划求解的结果报告1.运算结果报告运算结果报告2、敏感性报告、敏感性报告3、极限报告、极限报告9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.2 9.3.2 规划求解规划求解o已知某家具厂目前库存情况是：木料已知某家具厂目前库存情况是：木料40004000个单位，油个单位，油漆漆45004500个单位，活页个单位，活页20002000个单位，螺丝个单位，螺丝20002000个单位。个单位。工厂生产工厂生产A,B,C,DA,B,C,D这四种产品。不同产品消耗的材料这四种产品。不同产品消耗的材料不同，售价也不同。试问，该厂利用现有的库存应该不同，售价也不同。
21、试问，该厂利用现有的库存应该生产生产4 4种产品各多少件才能达到利润的最大化。种产品各多少件才能达到利润的最大化。9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.2 9.3.2 规划求解规划求解建立规划模型建立规划模型9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.2 9.3.2 规划求解规划求解规划求解规划求解9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.2 9.3.2 规划求解规划求解规划求解规划求解9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.2 9.3.2 规划求解规划求解规划求解规划求解9.3 9.3 数据的高级分析
22、与处理数据的高级分析与处理9.3.2 9.3.2 规划求解规划求解规划求解规划求解1、加载“数据分析”宏。如添加、删除数据透视表的字段，更新数据透视表中的数据等等。2 规划求解规划求解3 数据的高级分析与处理例如，根据以下基本数据，预测某人65岁时的收入。选中已经创建的数据透视表-“插入”-“图表”某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等。2 数据透视图的修饰-改变透视图的格式Excel规划求解问题由以下3部分组成选择“数据”-“数据分析”选项，打开“数据分析”对话框，在其中选择“回归”选项，单击“确定”按钮。3 数据的高级分析与处理3 数据的高级分析与处理3 数据的高级分析与处
23、理在进行合并计算时，首先必须为汇总信息定义一个目的区，另外需要选择要合并计算的数据项。案例：销售公司在各大城市都有分销公司，公司采用的是经销商销售模式。9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.3 9.3.3 回归分析回归分析o回归分析研究变量之间的非确定因果关系。回归分析研究变量之间的非确定因果关系。o通常把其中的一些变量作为自变量（预测变量，通常把其中的一些变量作为自变量（预测变量，X X），），来预测另外一些随自变量变化而变化的变量（被预测来预测另外一些随自变量变化而变化的变量（被预测变量，变量，Y Y）。）。9.3 9.3 数据的高级分析与处理数据的高级分析与处理
24、9.3.3 9.3.3 回归分析回归分析线性回归线性回归o两个变量之间有准确严格的直线相关关系。两个变量之间有准确严格的直线相关关系。o一个变量的变化引起另外一个变量变化的程度可以用一个变量的变化引起另外一个变量变化的程度可以用线性回归方程式来表示。线性回归方程式来表示。9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.3 9.3.3 回归分析回归分析线性回归线性回归o例如，下表是某公司例如，下表是某公司1010年来广告费和销售量数据，试探据年来广告费和销售量数据，试探据此估计该公司为了达到此估计该公司为了达到40004000万的销售量，万的销售量，20102010年的广告费年的
25、广告费应投入多少。应投入多少。9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.3 9.3.3 回归分析回归分析线性回归线性回归9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.3 9.3.3 回归分析回归分析线性回归线性回归9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.3 9.3.3 回归分析回归分析线性回归线性回归9.3 9.3 数据的高级分析与处理数据的高级分析与处理回归系数：选中回归系数：选中b15:c15，输入公式，输入公式=LINEST(C2:C11,B2:B11),按下按下Ctrl+shift+Alt相关系数相关系数=CORREL(C
26、2:C11,B2:B11)9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.3 9.3.3 回归分析回归分析非线性回归非线性回归o两个变量之间之间的关系不是单纯的直线关系。两个变量之间之间的关系不是单纯的直线关系。o如年龄与收入的关系，这种情况下要用到非线性回归如年龄与收入的关系，这种情况下要用到非线性回归分析方法。分析方法。9.3 9.3 数据的高级分析与处理数据的高级分析与处理9.3.3 9.3.3 回归分析回归分析非线性回归非线性回归o例如，根据以下基本数据，预测某人例如，根据以下基本数据，预测某人6565岁时的收入。岁时的收入。9.3 9.3 数据的高级分析与处理数据的高
27、级分析与处理9.3.3 9.3.3 根据以下基本数据，预测某人根据以下基本数据，预测某人6565岁时的收入。岁时的收入。9.3 9.3 数据的高级分析与处理数据的高级分析与处理o9.3.4 多元线性回归分析多元线性回归分析一元回归分析适用于分析一个自变量和一个因变量之一元回归分析适用于分析一个自变量和一个因变量之间的关系，而在实际情况中，常会遇到一个因变量间的关系，而在实际情况中，常会遇到一个因变量和多个自变量数量关系的问题，多元回归分析正是和多个自变量数量关系的问题，多元回归分析正是为了解决这些问题而产生的。为了解决这些问题而产生的。9.3 9.3 数据的高级分析与处理数据的高级分析与处理o
28、9.3.4 多元线性回归分析多元线性回归分析案例：销售公司在各大城市都有分销公司，公司采用案例：销售公司在各大城市都有分销公司，公司采用的是经销商销售模式。公司统计了的是经销商销售模式。公司统计了2005年各大城年各大城市的人数、公司在该城市的经销商个数以及销售量市的人数、公司在该城市的经销商个数以及销售量的数值，公司需要以该数据为基础，分析影响公司的数值，公司需要以该数据为基础，分析影响公司销售情况的各个因素以及预测公司的销售前景。销售情况的各个因素以及预测公司的销售前景。9.3 9.3 数据的高级分析与处理数据的高级分析与处理o9.3.4 多元线性回归分析多元线性回归分析9.3 9.3 数
29、据的高级分析与处理数据的高级分析与处理o9.3.4 多元线性回归分析多元线性回归分析o案例实现案例实现n1、加载、加载“数据分析数据分析”宏。选择宏。选择“开发工具开发工具”-“加载项加载项”，打开加载宏对话框，在其中选择加载，打开加载宏对话框，在其中选择加载“分析工具库分析工具库”，然后单击，然后单击“确定确定”按钮。按钮。n2、打开、打开“回归回归”对话框。选择对话框。选择“数据数据”-“数据分析数据分析”选项，打开选项，打开“数据分析数据分析”对话框，在其中选择对话框，在其中选择“回归回归”选项，单击选项，单击“确定确定”按钮。按钮。9.3 9.3 数据的高级分析与处理数据的高级分析与处理o9.3.4 多元线性回归分析多元线性回归分析o案例实现案例实现n3、设置回归参数。在回归对话框中，在、设置回归参数。在回归对话框中，在Y值输入区域值输入区域选择因变量数据区，在选择因变量数据区，在X值输入区域选择自变量数据区值输入区域选择自变量数据区域。同时选择域。同时选择“线性拟合图线性拟合图”选项。选项。n4、根据回归结果得到回归函数。、根据回归结果得到回归函数。
