(本科）第3章 数据分析工具.pptx

课程主讲人：第3章 数据分析工具2主要主要内容：内容： 3.1 模拟分析 3.2 数据透视表 33.1 模拟分析1. 模拟运算表模拟运算表v是一种数据表，它可以在工作表中显示因为公式中是一种数据表，它可以在工作表中显示因为公式中的某些变量的值发生变化而对计算结果造成的影响的某些变量的值发生变化而对计算结果造成的影响。v模拟运算表类型模拟运算表类型- 单变量模拟运算表单变量模拟运算表- 双变量模拟运算表双变量模拟运算表2. 单变量求解单变量求解v即解一元方程，通过改变一个可变单元格中的数值即解一元方程，通过改变一个可变单元格中的数值来满足目标单元格中的值。来满足目标单元格中的值。v注意：通常情况下，注意：通常情况下，目标单元格中的数据是以一个目标单元格中的数据是以一个与可变单元格相关的计算公式表示与可变单元格相关的计算公式表示的。的。3.方案分析方案分析43.1.1 模拟运算表 模拟运算表是一种数据表，它可以在工作表中显示因为公式中的某些变模拟运算表是一种数据表，它可以在工作表中显示因为公式中的某些变量的值发生变化而对计算结果造成的影响。量的值发生变化而对计算结果造成的影响。 可以假设公式中的变量有一组变化值，把他们分别带入公式后可以得到可以假设公式中的变量有一组变化值，把他们分别带入公式后可以得到一组不同的结果。一组不同的结果。 (Eg .Y=a+bx x有一组变化值有一组变化值)1.单变量模拟运算表单变量模拟运算表 单单变量模拟运算表是在工作表中变量模拟运算表是在工作表中输入一个变量的多个不同值，分析这些输入一个变量的多个不同值，分析这些不同变量值对一个或多个公式计算结果的影响。不同变量值对一个或多个公式计算结果的影响。 如果可能的取值在同一列中，则含有公式的单元格必须在第一个变量取如果可能的取值在同一列中，则含有公式的单元格必须在第一个变量取值的右上角；值的右上角； 如果可能的取值在同一行中，则含有公式的单元格必须在第一个变量取如果可能的取值在同一行中，则含有公式的单元格必须在第一个变量取值的左下角。值的左下角。53.1.1 模拟运算表1.单变量模拟运算表单变量模拟运算表 【例3-1】某企业的相关资料如图3-1所示，若单位变动成本分别为110、115、120、125、130，其他条件不变时的利润如何变动？图3-1 【单变量模拟运算表】对话框6 2.双变量双变量模拟模拟运算表运算表 双变量模拟运算表用来双变量模拟运算表用来分析两个变量的几组不同的数组变化对公式计分析两个变量的几组不同的数组变化对公式计算结果所造成的影响算结果所造成的影响。 z=z(x,y) Eg. Z=x*y (x1,9; y1,9) 两个变量的变化值分别放在一行与一列中，两个变量所在的行与列交两个变量的变化值分别放在一行与一列中，两个变量所在的行与列交叉的单元格放置的是将这两个变量代入公式后得到的计算结果叉的单元格放置的是将这两个变量代入公式后得到的计算结果3.1.1 模拟运算表7 2.双变量双变量模拟模拟运算表运算表 3.1.1 模拟运算表 【例例3-2】上述单变量模拟运算表中仅考虑了单位变动成本的变化，没有考虑销售量的变化。若销售量和单位变动成本都发生变化时，其他条件不变，那么利润又会是怎样呢？图3-3 设计双变量模拟运算表结构8注意：注意：vExcel规定，规定，如果可能的取值在同一列中，则含有公式的单元格必须在第如果可能的取值在同一列中，则含有公式的单元格必须在第一个变量取值的右上角；如果可能的取值在同一行中，则含有公式的单元一个变量取值的右上角；如果可能的取值在同一行中，则含有公式的单元格必须在第一个变量取值的左下角。格必须在第一个变量取值的左下角。v在在“模拟运算表模拟运算表”对话框中有两个编辑框，分别为对话框中有两个编辑框，分别为“输入引用的行的单输入引用的行的单元格元格”和和“输入引用的列的单元格输入引用的列的单元格”，依情况而定：，依情况而定：F如果变量的一组值在同一行，则在如果变量的一组值在同一行，则在“输入引用的行的单元格输入引用的行的单元格”，输入，输入变量第一个取值所在单元格的左边单元格。变量第一个取值所在单元格的左边单元格。F如果变量的一组值在同一列，则在如果变量的一组值在同一列，则在“输入引用的列的单元格输入引用的列的单元格”，输入，输入变量第一个取值所在单元格的上边单元格。变量第一个取值所在单元格的上边单元格。课后作业：课后作业：编制编制1515的乘法口诀表（的乘法口诀表（5分钟内完成）分钟内完成）3.1.1 模拟运算表93.1.2 单变量求解单变量求解又叫假设分析，即解一元方程。单变量求解又叫假设分析，即解一元方程。 例如：例如：y=a+bx 已知已知Y求求 X操作步骤：工具操作步骤：工具单变量求解单变量求解 说明：说明：“单变量求解单变量求解”菜单命令用于在目标值既定的情况下反过来求某菜单命令用于在目标值既定的情况下反过来求某一单元格的取值。一般是目标单元格引用了其他单元格的取值。一单元格的取值。一般是目标单元格引用了其他单元格的取值。103.1.2 单变量求解 【例例3-3】继续使用图继续使用图3-1所示的所示的【例例3-1】的企业资料，要求利润为的企业资料，要求利润为20000时的单位变动成本为多少？时的单位变动成本为多少？图3-5【单变量求解】对话框113.1.3 方案分析 企业的生产经营活动往往受到各种因素变化的影响，这些因素对企业的影响对于企业做好财务决策具有重要的意义。 利用Excel提供的方案管理器工具可以很方便地建立方案，随时显示各方案的执行结果，还可以建立各方案的摘要报告，从而为有效地做好财务决策提供有用的信息。123.1.3 方案分析 【例例3-4】某企业生产和销售A、B、C三种产品，2020年各种产品的销售额、销售成本和销售利润的有关数据如图3-7上半部分所示。预计2021年市场情况有好、一般和差三种可能情况，每种情况下的销售额及销售成本的增长率如图3-7下半部分所示。利用Excel的方案管理器工具对该企业预计的销售情况进行方案分析。图3-7 产品销售资料及预计增长率131.建立方案建立方案3.1.3 方案分析图3-10 【添加方案】对话框142.显示方案显示方案3.1.3 方案分析图3-14方案一（市场好）的执行结果 图3-15方案二（市场一般）的执行结果图3-16方案三（市场差）的执行结果153.修改、删除或增加方案修改、删除或增加方案v如果需要对已经输入的方案进行修改，应在【方案管理器】对话框中选取需要修改的方案，单击【编辑】按钮，弹出【编辑方案】对话框，在该对话框中单击【确定】按钮后，系统会弹出【方案变量值】对话框，在其中进行相应的修改，然后单击【确定】按钮即可。v若要删除某一方案，应在【方案管理器】对话框中选取需要删除的方案，单击【删除】按钮。v若要增加方案，应在【方案管理器】对话框中单击【添加】按钮，再按上述建立方案的第至步的过程输入新方案的有关信息即可。3.1.3 方案分析164.建立建立方案摘要方案摘要报告报告3.1.3 方案分析图3-18方案摘要报告171. 数据透视表 v数据透视表是一种特殊的工作表，它是一种对大量数据快速汇总和建立交叉列表的交互式表格。v数据透视表允许用户使用特殊的、直接的操作来分析数据库或表格中的数据，用户可以扭转和重组页字段、行字段和列字段来修改其布局，从而可以透视数据库或表格中的数据，因此称为透视表。v数据透视表作用：-用户可以在数据透视表中指定想显示的字段和数据项，以确定如确定如何组织数据何组织数据；-也可以变换行和列来查看源数据的不同汇总结果查看源数据的不同汇总结果；-还可以显示不同的页面以筛选数据筛选数据，甚至还可以根据需要显示区域中的明细数据。18 数据库数据库是与组织的业务活动和结构相对应并以一种合理的方法组织起是与组织的业务活动和结构相对应并以一种合理的方法组织起来的各种相关数据的集合来的各种相关数据的集合。 在Excel中，一片连续的（没有空行或空列）的数据区域称为数据清单。与数据库类似，数据清单中的一行称为一条记录，每一列相当于一个字段，每列第一行包含每一列的标签，称为字段名（或列标题）。1. 数据清单基本操作数据清单基本操作v 创建创建数据清单数据清单v 过滤过滤数据清单数据清单v 筛选和命名筛选和命名区域区域v 定位和删除定位和删除记录记录3.2.1 数据库基础191. 数据清单基本操作数据清单基本操作将【记录单】命令添加到【快速访问工具栏】3.2.1 数据库基础图 3-19 调出【记录单】命令201. 数据清单基本操作数据清单基本操作（1）创建数据清单）创建数据清单（2）过滤数据清单过滤数据清单3.2.1 数据库基础图 3-20 【记录单】窗口图 3-21 【条件】命令窗口211. 数据清单基本操作数据清单基本操作（3）筛选）筛选和命名和命名区域区域（4）定位）定位和删除和删除记录记录 定位定位记录的方法很多，可以使用过滤的方法，记录的方法很多，可以使用过滤的方法，通过通过【记录单记录单】的的【上上一条一条】、【下一条下一条】按钮逐条翻阅找到要删除的记录，还可以通过图按钮逐条翻阅找到要删除的记录，还可以通过图3-20中间的滚动条移动到要删除的记录处。中间的滚动条移动到要删除的记录处。 定位定位到要删除的记录之后，单击图到要删除的记录之后，单击图3-20【记录单记录单】的的【删除删除】按钮即按钮即可将记录删除。可将记录删除。3.2.1 数据库基础图 3-22 【根据所选内容创建】名称命令窗口222. 数据库统计函数数据库统计函数vDSUM函数函数功能：计算满足给定条件的列表或数据库的列中数值的和。功能：计算满足给定条件的列表或数据库的列中数值的和。公式：公式：=DSUM(database, field, criteria)式式中，中，database构成列表或数据库的单元格区域。构成列表或数据库的单元格区域。field指定函数所使用的指定函数所使用的数据列，数据清单中的数据列必须在第一行具有标志项；输入两端带双数据列，数据清单中的数据列必须在第一行具有标志项；输入两端带双引号的列标签，如引号的列标签，如 产品产品 或或 商标商标；或是代表列表中列位置的数字；或是代表列表中列位置的数字（不带引号），（不带引号），1 表示第一列，表示第一列，2 表示第二列，依此类推。表示第二列，依此类推。criteria为包含为包含给定条件的单元格区域。可以为参数给定条件的单元格区域。可以为参数 criteria 指定任意区域，只要此区域指定任意区域，只要此区域包含至少一个列标签，并且列标签下至少有一个在其中为列指定条件的包含至少一个列标签，并且列标签下至少有一个在其中为列指定条件的单元格。单元格。vDCOUNTA函数函数功能：计算满足给定条件的列表或数据库的列中非空单元格的个数。功能：计算满足给定条件的列表或数据库的列中非空单元格的个数。公式：公式：=DCOUNTA(database, field, criteria)式中，各参数含义同式中，各参数含义同DSUM函数。参数函数。参数field为可选参数，如果省略为可选参数，如果省略field，DCOUNTA 计算数据库中符合条件的所有记录数计算数据库中符合条件的所有记录数。3.2.1 数据库基础232. 数据库统计函数数据库统计函数vDAVERAGE函数函数功能：计算满足给定条件的列表或数据库的列中数值的平均值。功能：计算满足给定条件的列表或数据库的列中数值的平均值。公式：公式：=DAVERAGE(database, field, criteria)式中，各参数含义同式中，各参数含义同DSUM函数。函数。vSUBTOTAL函数函数功能：返回列表或数据库的分类汇总。功能：返回列表或数据库的分类汇总。公式：公式：=SUBTOTAL(function_num,ref1,ref2,.)式中式中，ref1是要对其进行分类汇总计算的第一个命名区域或引用。是要对其进行分类汇总计算的第一个命名区域或引用。ref2,. 是要对其进行分类汇总计算的第是要对其进行分类汇总计算的第 2 个至第个至第 254 个命名区域或引个命名区域或引用。用。24Function_numFunction_numFunction_numFunction_num函数函数（包含隐藏值）（包含隐藏值）（忽略隐藏值）（忽略隐藏值）1 1101101AVERAGEAVERAGE2 2102102COUNTCOUNT3 3103103COUNTACOUNTA4 4104104MAXMAX5 5105105MINMIN6 6106106PRODUCTPRODUCT7 7107107STDEVSTDEV8 8108108STDEVPSTDEVP9 9109109SUMSUM1010110110VARVAR1111111111VARPVARP25建立数据透视表建立数据透视表 v 由于数据透视表也是一种表格，可是这种表格是建立在由于数据透视表也是一种表格，可是这种表格是建立在Excel数据库或表格之数据库或表格之上的。也就是说，上的。也就是说，建立数据透视表必须有数据库或表格作为数据源。建立数据透视表必须有数据库或表格作为数据源。v建立数据透视表的条件建立数据透视表的条件完整的表体结构：完整的表体结构：Excel表中的记录以流水方式记录，表头各字段内容应为表中的记录以流水方式记录，表头各字段内容应为文本型，而且不存在空白单元格。文本型，而且不存在空白单元格。规范的列向数据规范的列向数据：同一列中的数据应具有相同的格式，各列中不存在空白的同一列中的数据应具有相同的格式，各列中不存在空白的单元格。单元格。v生成数据透视表的数据来源是生成数据透视表的数据来源是 Excel数据清单或数据库数据清单或数据库 外部数据库外部数据库(会计信息系统各子系统产生的数据库文件、文本文件或网会计信息系统各子系统产生的数据库文件、文本文件或网上数据源）上数据源） 多重合并计算数据区域多重合并计算数据区域 其他数据透视表等。其他数据透视表等。26建立数据透视表建立数据透视表 v建立数据库或表格后，建立数据透视表，可使用建立数据库或表格后，建立数据透视表，可使用“数据透数据透视表向导视表向导”来进行。其操作步骤是：来进行。其操作步骤是： (1)单击数据列表中的任一单元格。单击数据列表中的任一单元格。 (2)从从“插入插入”选项卡中选择选项卡中选择“数据透视表数据透视表”命令，启动命令，启动“数数据透视表向导据透视表向导”设置数据来源。设置数据来源。 （3）确定源数据区域）确定源数据区域 （4）选择数据透视表的生成位置及选项）选择数据透视表的生成位置及选项 （5）建立数据透视表结构）建立数据透视表结构 。 该步骤是使用“数据透视表”的重要步骤，用于指定数据库中的哪些字段作为数据透视表的行、列、值、筛选器字段。27 【例3-6】以图3-27所示的“产品销售收入情况表”数据为例，建立数据透视表。图3-27 产品销售收入情况表（部分记录）28数据透视表的数据透视表的构成：构成：筛选筛选器：将选定的字段作为数据透视表中分页显示的项目。器：将选定的字段作为数据透视表中分页显示的项目。列：将选定的字段作为数据透视表中的列标题。列：将选定的字段作为数据透视表中的列标题。行：将选定的字段作为数据透视表中的行标题。行：将选定的字段作为数据透视表中的行标题。值：将选定的字段作为数据透视表中的汇总项目值：将选定的字段作为数据透视表中的汇总项目。关于区域字段的设置规则包括：关于区域字段的设置规则包括：在报表筛选、行标签、列标签区域（这三个区域称为文本区域）中，在报表筛选、行标签、列标签区域（这三个区域称为文本区域）中，任何字段都只能出现一次，不能同时出现在这任何字段都只能出现一次，不能同时出现在这3个区域中；个区域中；文本字段可以拖动到文本字段可以拖动到“数值区域数值区域”，数值字段可以拖动到其他，数值字段可以拖动到其他3个个文本区域中；文本区域中；一个字段可以同时出现在数值区域和某一个文本区域中；一个字段可以同时出现在数值区域和某一个文本区域中；一个字段可以在数值区域中出现多次。一个字段可以在数值区域中出现多次。29 【例3-6】以图3-27所示的“产品销售收入情况表”数据为例，建立数据透视表。图3-32 销售收入数据透视表301.通过通过添加字段改变添加字段改变结构结构图3-34统计各区域、各位客户数量和金额的数据透视表312.通过通过更改字段位置改变更改字段位置改变结构结构（1）移）移至不同至不同区域区域图3-36按区域汇总的数据透视表322.通过通过更改字段位置改变更改字段位置改变结构结构（2）同）同一区域中的一区域中的移动移动图3-38 按客户汇总的数据透视表33v重命名字段重命名字段v字段的重复出现字段的重复出现v改变改变数据透视表的值汇总数据透视表的值汇总方式方式v改变改变数据透视表的值数据透视表的值显示方式显示方式v字段字段组合组合v计算计算字段字段v计算计算项项341.重命名字段重命名字段图3-40 “值字段设置”对话框352.字段的重复出现字段的重复出现图3-41 添加重复字段（“金额”）的数据透视表 363.改变改变数据透视表的值汇总数据透视表的值汇总方式方式图3-44 设置不同值汇总方式的数据透视表374.改变改变数据透视表的值数据透视表的值显示方式显示方式图3-46设置不同值显示方式的数据透视表385.字段组合字段组合图3-49 设置日期组合的数据透视表图3-51 设置文本组合的数据透视表396.计算字段计算字段图3-53 添加计算字段后的数据透视表407.计算项计算项图3-55 添加计算项的数据透视表41v分类汇总分类汇总方式方式v总计总计方式方式v报表报表布局布局v重复项目重复项目标签标签v空行空行v数据的数据的格式化格式化421.分类分类汇总汇总方式方式图3-56 “在组的顶部显示所有分类汇总”的数据透视表432.总计方式总计方式图3-57 “仅对行启用”总计的数据透视表 443.报表布局报表布局图3-58 大纲形式的数据透视表图3-59表格形式的数据透视表454.重复重复项目项目标签标签图3-60 重复所有项目标签的数据透视表465.空行空行图3-61“在每个项目后插入空行”的数据透视表476.数据数据的的格式化格式化 用户可以对数据区域的数据格式进行设计，方法是： 在【数据透视表】中单击想要格式化的单元格，单击鼠标右键，从展开的下拉列表中单击【值字段设置】选项，弹出【值字段设置】对话框，单击【数字格式】按钮，弹出【设置单元格格式】对话框，在【分类】列表中选择“数值”，并设置小数位数，单击【确定】按钮即可。48 数据数据透视表所依据的数据库或数据清单中的数据一旦透视表所依据的数据库或数据清单中的数据一旦发生变化，数据透视表中的数据也应该发生相应的变化。发生变化，数据透视表中的数据也应该发生相应的变化。数据源的改变包含两种情况：数据源的改变包含两种情况：一是数据源结构没有发生改一是数据源结构没有发生改变，区域中的数据发生了改变；二是数据源结构发生了改变，区域中的数据发生了改变；二是数据源结构发生了改变，比如字段增加或者减少，记录增加或者减少变，比如字段增加或者减少，记录增加或者减少。v第一种改变有第一种改变有三种刷新方法：一是鼠标右键可以调出三种刷新方法：一是鼠标右键可以调出【刷新刷新】命令；二是命令；二是【数据透视表工具数据透视表工具】的的【分析分析】选选项卡下的项卡下的【刷新刷新】命令；三是命令；三是【数据数据】选项卡下的选项卡下的【全全部刷新部刷新】命令。命令。 v第二第二种数据源结构发生了改变，可种数据源结构发生了改变，可重新生成数据透视表重新生成数据透视表或者通过或者通过【数据透视表工具数据透视表工具】的的【分析分析】选项卡下的选项卡下的【更改数据源更改数据源】命令重新选择数据源区域实现刷新命令重新选择数据源区域实现刷新。49 创建创建数据透视表时，数据透视表时，可能会出现数据源分布在多个工可能会出现数据源分布在多个工作表中的情况，使用多个工作表数据源创建数据透视表作表中的情况，使用多个工作表数据源创建数据透视表的的步骤为：步骤为：v （1）在工作表中依次）在工作表中依次按下按下Alt、D和和P键键，此时会弹出，此时会弹出【数据透视表数据透视表和数据透视图向导和数据透视图向导】对话框，在对话框，在【请指定待分析数据的数据源类型请指定待分析数据的数据源类型】选项区中选中选项区中选中【多重合并计算数据区域多重合并计算数据区域】。v （2）单击）单击【下一步下一步】，进入，进入【指定页字段数目指定页字段数目】对话框，默认选中对话框，默认选中【创建单页字段创建单页字段】，保持默认，保持默认设置。设置。v （3）单击）单击【下一步下一步】按钮，进入到按钮，进入到【指定数据区域指定数据区域】对话框，单击对话框，单击【选定区域选定区域】文本框右侧的文本框右侧的【折叠折叠】按钮，在工作表中选择数据源按钮，在工作表中选择数据源所在的单元格所在的单元格区域。区域。v （4）选好区域后，单击）选好区域后，单击【折叠折叠】按钮，返回对话框，单击按钮，返回对话框，单击【添加添加】按钮，将选中的数据区域添加到按钮，将选中的数据区域添加到【所有区域所有区域】列表框列表框中。中。v （5）切换到切换到“多重合并数据源多重合并数据源2”工作表，选择单元格工作表，选择单元格区域，区域，将其添将其添加到加到【所有区域所有区域】列表框列表框中。中。50 创建创建数据透视表时，数据透视表时，可能会出现数据源分布在多个工可能会出现数据源分布在多个工作表中的情况，使用多个工作表数据源创建数据透视表作表中的情况，使用多个工作表数据源创建数据透视表的的步骤为：步骤为：v （6）单击）单击【下一步下一步】按钮，进入到按钮，进入到【指定数据透视表显示位置指定数据透视表显示位置】对对话框。话框。v （7）保持默认设置，单击）保持默认设置，单击【完成完成】按钮，即可在工作表中创建数据按钮，即可在工作表中创建数据透视表。透视表。图3-69 利用多重合并创建的数据透视表效果图51v数据数据透视表的透视表的移动有两种方法：一是鼠标拖动法；二是移动有两种方法：一是鼠标拖动法；二是菜单命令，利用菜单命令，利用【数据透视表工具数据透视表工具】的的【分析分析】选项卡，选项卡，在在【操作操作】组中选择组中选择【移动数据透视表移动数据透视表】按钮按钮。v数据数据透视表的透视表的复制也需要先选中整个数据透视表，然后复制也需要先选中整个数据透视表，然后利用鼠标右键的复制、粘贴命令即可实现利用鼠标右键的复制、粘贴命令即可实现。v删除删除数据透视表的方法主要有两种。数据透视表的方法主要有两种。（1）删除数据透视表所在的工作表）删除数据透视表所在的工作表（2）仅删除数据透视表）仅删除数据透视表52 数据数据透视表动态图表可以很方便、直观地表达数据，透视表动态图表可以很方便、直观地表达数据，动态图表可以有一个列字段和一个行字段，数据可以来自动态图表可以有一个列字段和一个行字段，数据可以来自于透视表中字段的值。于透视表中字段的值。v 1. 创建数据透视图创建数据透视图v 2. 数据透视图的移动、刷新、数据透视图的移动、刷新、删除删除v 3. 数据透视图的编辑数据透视图的编辑53