第一章EXCEL高级应用 1.3 数据分析和决策.

Presentation on theme: "第一章EXCEL高级应用 1.3 数据分析和决策."— Presentation transcript:

1 第一章EXCEL高级应用 1.3 数据分析和决策

2 数据的筛选和排序 （1）自动筛选 （2）高级筛选
①选中要筛选的区域，通过“数据”选项卡“排序和筛选”组中的“筛选”命令，启用自动筛选功能，筛选区域中列标签的右侧将显示自动筛选箭头 ②单击要筛选列的自动筛选箭头 ，指定筛选条件，Excel将自动筛选并显示满足条件的数据 （2）高级筛选 在数据区域的上方或下方单独建立高级筛选条件区域，并键入筛选条件

3 高级筛选(1) ①建立条件区域，并指定筛选条件
②“数据”-“排序和筛选”-“高级”，打开“高级筛选”对话框。分别指定要筛选的数据区域、条件区域，以及筛选结果的存放位置，单击【确定】按钮，筛选并显示满足条件的数据 条件区域一般位于数据区域的上方或下方，并且与数据区域之间至少空1行 条件区域中，同一行中的条件被解释为逻辑“与”；不同行之间被解释为逻辑“或”

4 高级筛选(2) 显示产品名称中包含“肉”的产品 显示供应商为“佳佳乐”且类别为“饮料”的数据，或者供应商为“百达”且类别为“调味品”的数据
显示库存量小于订购量的数据

5 数据的排序(1) ①数字对象：数字从最小的负数到最大的正数进行排序。日期和时间的排序与数字对象相同
②文本对象：按字母先后顺序对文本项进行排序时，从左到右逐个字符地进行排序 ③逻辑值：FALSE排在TRUE之前 ④序列排序：按照序列定义中指定的顺序

6 数据的排序(2)

7 数据的分类汇总 两个操作要点：分类(排序)和汇总 分类汇总的步骤： 分类汇总的嵌套和分级显示 选择已排序的数据区域
在“分类汇总”对话框中 指定分类字段、汇总方式（即统计方式，可以为求和、计数、平均值、最小值、最大值等）、以及选定汇总项 分类汇总的嵌套和分级显示

8 数据透视表

9 Excel加载项 三种类型的加载项：Excel加载项、可下载的加载项和自定义加载项
Excel加载项通常包括Excel加载项(.xlam)、Excel 加载项(.xla)或DLL加载项()文件，也可以是自动化加载项。Excel程序本身提供了一组加载项（例如规划求解和分析工具库）。通常情况下，只需要激活这些加载项即可使用 执行“文件”选项卡中“选项”命令，打开“Excel选项”对话框，选择“加载项”类别，在“管理”下拉列表中选择“Excel加载项”，然后单击“转到”按钮，打开 “加载宏”对话框。在“可用加载宏”列表框中，选中要激活的加载项旁边的复选框，然后单击“确定”，即可激活Excel加载项

10 模拟运算表 当目标单元格为一个或多个单元格参数的计算公式，使用模拟运算表，可以分析计算公式中一个或两个单元格参数取值发生变化时，目标单元格的值的变化趋势 单变量模拟运算表和双变量运算表 创建模拟运算表一般分为三步

11 单变量模拟运算表 假设固定利率，且贷款总额一定（20万）时，分析付款分期总数变化（5年-30年）时，月偿还额的变化情况
（1）创建模拟运算参数单元格区域 （2）建立模拟运算表 A6：=PMT(B3/12,B4*12,B2) （3）进行模拟运算 选择模拟运算区域A5:G6 “模拟运算表”-“输入引用行的单元格”文本框中指定单元格$B$4

12 双变量模拟运算表 假设固定利率时，分析每月付款额变化（1000元-5000元），付款分期总数变化（5年-30年）时，可贷款总额的变化情况
（1）创建模拟运算参数单元格区域 （2）建立模拟运算表 A5：=PV(B3/12,B4*12,B2) （3）进行模拟运算 选择A5:G10 “模拟运算表”对话框-“输入引用行的单元格”文本框指定单元格“$B$4”（期限（年））；“输入引用列的单元格”文本框指定单元格“$B$2”（月返款额）

13 单变量求解（1） 当目标单元格为包含一个或多个单元格引用参数的计算公式时，如果已知目标单元格的预期结果值，要推算计算公式中某个变量的合适取值时，则可以使用Excel提供的单变量求解功能 使用单变量求解一般分为两步

14 单变量求解（2） 假设2006年中国人口为13亿，如果要控制2020年人口在15亿以内，求人口的年增长率应该控制为百分之多少
（1）创建单变量求解运算的参数单元格和目标单元格区域 （2）单变量求解运算

15 单变量求解（3）-求解1元n次方程 求方程2X5-4X4+5X3+X2+8=0的解 （1）创建单变量求解运算的参数单元格和目标单元格区域内容

16 习题与实验 选择题：8-16 填充题：5-9 实验： 实验二 数据分析和决策（1）

17 第一章EXCEL高级应用 1.3 数据分析和决策(2)

18 规划求解(1) 在满足所有的约束条件下，对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果

19 规划求解(2) 规划求解包含三个部分内容

20 规划求解(3) 问题

21 规划求解(4) （1）创建用于规划求解的各参数单元格和目标单元格区域

22 规划求解(5) （2）输入规划求解参数 指定规划求解目标 ②指定规划求解可变参数 ③添加规划求解的约束条件 ④设置具有整数约束的求解

23 规划求解(6) 规划求解的结果报告 在“规划求解结果”对话框中，在“报告”列表框中选择要创建的报告类型 “运算结果报告” “敏感性报告”
“极限值报告”

24 方案分析（1） 所谓方案，就是Excel保存在工作表中并可进行自动替换的一组值
所谓方案分析，就是使用方案来预测工作表模型的输出结果，在工作表中创建并保存不同的数值组（方案）。可以切换到不同方案以查看不同的结果；也可以合并方案，从而比较不同的方案对结果的影响 使用方案分析一般包含下列内容

25 方案分析（2） 假设企业利润=销售收入-生产销售成本+营业外收入。要求为公司的销售部门、生产部门、人事部门、投资部门分别制定一个方案

26 方案分析（3） （1）创建用于方案分析的原始数据单元格区域 （2）创建增收方案

27 方案分析（4） 按同样方法，创建“减支”、“减员”、“投资”方案，并观察各方案的结果 合并方案 方案显示和总结
在【方案管理器】对话框中，单击【合并】按钮，打开【合并方案】对话框，选择包含其他方案分析的工作表 方案显示和总结 【方案管理器】对话框中，选中已创建的方案，单击【显示】按钮，可显示所选定方案的结果 单击【摘要】按钮，则可以创建“方案摘要”或“方案数据透视表”，显示各方案对目标数据的影响比较表，以帮助用户进行决策

28 数据分析工具库（1）

29 直方图统计 计算并以图表显示数据区域中各数据分段分布情况
在学生成绩表中，学生成绩的分布范围为0-100，可以划分为：“0-60；60-70；70-80；80-90；90-100”；“直方图”分析工具，可以直接统计各分数段学生人数，并给出频数分布和累计频数表的直方图以供分析 （1）准备用于分析的数据区域 （2）调用“直方图”分析工具 （3）指定“直方图”分析工具所需的数据和参数 （4）执行分析并显示结果

30 描述统计 生成数据源区域中数据的单变量统计分析报表，提供有关数据趋中性和易变的信息 （1）准备用于分析的数据输入区域
（2）调用“描述统计”分析工具 （3）指定“描述统计”分析工具所需要的数据和参数

31 移动平均 基于特定的过去某段时期中变量的平均值，对未来值进行预测 （1）准备用于分析的数据输入区域 （2）调用“移动平均”分析工具
（3）指定“移动平均”分析工具所需要的数据和参数 （4）执行分析并显示结果

32 习题与实验 选择题： 填充题： 思考题： 实验： 实验三 数据分析和决策（1） 实验四 数据分析和决策（2）