excel在管理中的应用-PPT4-项目四

北京交通大学出版社《Excel在管理的应用》课程结构项目一EXCEL2010基础知识1项目二员工档案管理2项目三销售表单的制作3项目四销售数据透视表的应用4Contents目录项目五销售业绩与提成计算5课程结构项目六会计凭证的制作6项目七薪酬管理实务7项目八产品与资产的管理8项目九

客户满意度调查9Contents目录项目四销售数据透视表的应用工作目标在Excel2010用于销售数据管理时，经常需要进行数据透视分析，也就是从销售数据表的特定字段中概括信息，从多个角度查看、分析、汇总、统计数据，以便快速做出相关决策。其中，数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，而数据透视图是一种能够根据数据处理需要，利用它可以及时查看部分数据的图表对比效果。本项目介绍销售数据管理中的数据透视分析操作，包括数据透视表和数据透视图两个方面。项目四销售数据透视表的应用本项目的工作思路是：首先说明数据透视分析的作用，会用建立相应的数据透视表（图）；然后对数据透视表进行设置、数据编辑等操作。工作思路工作任务一数据透视表的创建在Excel2010中通过使用数据透视表，可对大量的销售数据进行重新组织和统计分析，它可以把很多行和列数据快速转化为有意义的信息，节省很多烦琐的公式计算，并能生成一个基于Excel2010数据表，甚至外部数据库的动态总结报告。

可以认为，数据透视表将数据的排序、筛选和分类汇总等功能进行了集成；并且根据数据透视表，还可以快速生成透视图。案例讲解例如图所示，为顺达电脑公司2009年1月份的散件销售情况记录表。到了月底，需要对这些数据汇总整理和统计分析。下面分析数据透视分析的重要作用。工作任务一数据透视表的创建项目活动1

通过实例了解数据透视分析的作用到了月底，公司销售经理想获取如下信息，从该表格中是难以直接获取的。（1）本月各种不同品名散件的各自销售额是多少？销售额最大的是哪种散件？（2）所有业务员中，销售额最高和最低的人员分别是谁？业绩相差多少？（3）各客户销售额占总销售额的百分比分别是多少？（4）各种不同散件的销售百分比是多少？其中各个客户的销售额百分比又是多少？在Excel2010中，更快捷的方法就是利用数据透视表，通过数据透视分析来实现。工作任务一数据透视表的创建项目活动1

通过实例了解数据透视分析的作用按照要求创建的两种数据透视表。工作任务一数据透视表的创建项目活动1

通过实例了解数据透视分析的作用观察第一张图所示样式的数据透视表，可以很快得到上面问题（1）和问题（2）的答案——在C10:I10单元格区域给出了各类散件的销售额总计，其中打印机销售额最高；在J5:J9单元格区域给出了各个业务员的销售额，其中王宏伟最多，魏漫漫最少，二者的销售额相差很大，前者为后者的5倍还多；单击业务员名称前的“+”可以得到详细的细节数据。观察第二张图样式的数据透视表，可以很快得到上面问题（3）和（4）的答案——在J5:J9单元格区域给出了各家客户的销售额占总销售额的百分比，其中销售额占比最大的客户为硕星公司，其购买金额占总销售额的32.23%。此时单击客户名称前的百分比，其中销售额占比最大的为打印机，占29.4%，而E5：E9单元格区域又给出了这29.4%的总销售额分别来自于哪些客户，可以看出主要是来自于硕星公司和华锐公司。工作任务一数据透视表的创建项目活动1

通过实例了解数据透视分析的作用分析：在Excel2010中，数据透视表的创建是一种基于已经设定数据透视表模型样式的流程操作，用户只要根据目标，将相关的内容拖动到所需要的位置，即可快速制作。

1．数据透视表的创建过程介绍。工作任务一数据透视表的创建项目活动2

数据透视表的创建其操作步骤如下：（1）选取数据表中任意一个单元格，或将插入点放在该表任意一个单元格中。（2）单击【插入】功能区下的【数据透视表】命令按钮，弹出【数据透视表】对话框。1．数据透视表的创建过程介绍。工作任务一数据透视表的创建项目活动2

数据透视表的创建其操作步骤如下：（3）在“请选择要分析的数据”下方，选择要建立数据透视表的数据区域，在图4-2所示的数据工作表中选择A2:I29区域。（4）在“选择放置数据透视表的位置”中选择“新工作表(N)”，然后点击【确定】按钮。1．数据透视表的创建过程介绍。工作任务一数据透视表的创建项目活动2

数据透视表的创建其操作步骤如下：（5）将“销售日期”字段添加到报表筛选字段区域，将“业务员”字段和“客户名称”字段添加到行区域，将“品名”字段添加到列区域，将“合计金额”字段添加到值字段区域。经过设置后的效果。1．数据透视表的创建过程介绍。工作任务一数据透视表的创建项目活动2

数据透视表的创建其操作步骤如下：（6）在前图中，先后选择了“业务员”和“客户名称”两个字段作为列标签，所以它们进行了分层次的显示，当单击对应业务员名字前面的“-”符号时，可以将其后面的具体客户名称折叠隐藏。2．创建数据透视表的注意事项。工作任务一数据透视表的创建项目活动2

数据透视表的创建（1）注意数据表据的格式要求。

用来制作数据透视表的数据表格，应是数据库格式的表格。特别是数据库中间不能放置空白行或空白列。（2）创建之前定好鼠标的位置。在利用Excel2010中的数据表/数据区域制作数据透视表之前，最好将鼠标置于数据表或数据区域中间某一单元格中，这样就能够智能化地选取数据源区域，省去选取操作步骤。（3）选取好数据透视表的放置位置。

在默认情况下，系统是选择“新工作表”作为放置数据透视表的位置。2．创建数据透视表的注意事项。工作任务一数据透视表的创建项目活动2

数据透视表的创建（4）拖动字段的过程是可逆的。可以将相应字段拖动到各个区域中，也可以直接将相应字段从各区域中拉出。（5）数据透视表的组成部分。页字段：数据透视表中被指定为页方向的源数据库中的字段；行字段：数据透视表中被指定为行方向的源数据库中的字段；列字段：数据透视表中被指定为列方向的源数据库中的字段；数据字段：含有数据的源数据库中的字段项。2．创建数据透视表的注意事项。工作任务一数据透视表的创建项目活动2

数据透视表的创建（6）合理拖动字段的位置。

各个字段的拖动位置，与数据分析的目的关系密切，这直接决定了将来制作出来的数据透视表样式。2．创建数据透视表的注意事项。工作任务一数据透视表的创建项目活动2

数据透视表的创建（7）可以对数据做一定的筛选。

根据数据透视表，可以进行一定的数据筛选，操作时只要单击相应字段右侧的筛选箭头，进行所需要项目的筛选即可。（8）可以进行排序。

在数据透视表中，可以对相关字段进行排序。1．设置数据透视表的工具。工作任务二数据透视表的设置项目活动1数据透视表的布局（1）“数据透视表字段列表”任务窗格的操作。

前面图形中出现的“数据透视表字段列表”任务窗格，在调整数据透视表的最终形状和选取字段方面，具有重要作用。（2）与数据透视表操作相关的命令组和命令按钮。

当在选中制作好的数据透视表任意单元格时，将在Excel2010的功能区菜单区域中增加一个“数据透视表工具”功能区，包含“选项”和“设计”两个选项卡。1．设置数据透视表的工具。工作任务二数据透视表的设置项目活动1数据透视表的布局（3）利用右键快捷菜单提供的相关命令操作。

在创建好的数据透视表上，选取相关元素后，然后单击鼠标右键，利用快捷菜单中提供的相关命令，也可以对数据透视表进行一定的格式调整和优化设置。2．调整相关字段的布局。工作任务二数据透视表的设置项目活动1数据透视表的布局下面将调整相关字段的布局中，经常用到的几种操作情况总结如下：（1）行、列字段互换：将行字段拖动到列字段中，列字段拖动到行字段即可。（2）在字段内移动数据项：如果要在字段内移动数据项，首先选中要移动的的数据项，移动鼠标靠近黑色边框，当鼠标指针变为白色箭头时按下鼠标左键（整行将有一条灰色线），然后拖动鼠标到需要的位置释放按键即可。（3）添加字段：在“数据透视表”字段对话框中，选取需要添加的字段名，并拖放到相应区域的适当位置即可（例如，添加列字段，则将其拖动到列标签中）。2．调整相关字段的布局。工作任务二数据透视表的设置项目活动1数据透视表的布局下面将调整相关字段的布局中，经常用到的几种操作情况总结如下：（4）删除字段：将要删除的字段名单元格拖动到透视表数据区以外即可。删除某个字段后，与之相关的数据会从数据透视表中删除。需要时，可将已删除的字段再次添加。（5）设置数据透视表选项：单击数据透视表数据区域的任一单元格，在右键菜单中选择“数据透视表选项(O)”命令按钮，将会弹出如图4-12所示的“数据透视表选项”对话框。1．数据汇总方式的修改。工作任务二数据透视表的设置项目活动2数据透视表的修改在数据透视表中，数据的汇总方式除了前面呈现的求和之外，还有其他的多种方式，如计数、求平均值、求最大值、求最小值、乘积等。默认情况下，对数字型字段应用“求和”方式，对非数字型字段应用“计数”方式。如：请将前面的数据透视表的汇总方式，由“求和”修改为“计数”。第一种方法（右键菜单法）：如后图所示，在A3单元格（其中包含了“求和项：合计金额”内容）上单击右键，从弹出的快捷菜单中选择“值字段设置”菜单项，接着从弹出的对话中，可以看到有多种不同的数据汇总方式，当前的方式为“求和”，此时只要在下面的列表中选取“计数”项，即可实现汇总方式由“求和”修改为“计数”。1．数据汇总方式的修改。工作任务二数据透视表的设置项目活动2数据透视表的修改1．数据汇总方式的修改。工作任务二数据透视表的设置项目活动2数据透视表的修改第二种方法（命令按钮法）：选中数据透视表中显示“求和项”的单元格，在功能区中新出现的【数据透视表工具】的【选项】中选择“字段设置”命令按钮，将弹出如上图所示的“值字段设置”对话框，选中其下面的“汇总方式”选项卡，其下面的列表框中给出了多种数据汇总方式，选取其中的“计数”，然后单击“确定”按钮，也能很快得到如下图的数据透视表显示效果。2．数据显示形式的修改。工作任务二数据透视表的设置项目活动2数据透视表的修改问题分析：这里的数据透视表，与前面不同之处就在于其中数据的显示形式不是销售数据，而是用了百分比数字表示，这里的百分比数字是对应行列处数据占总和的百分比。2．数据显示形式的修改。工作任务二数据透视表的设置项目活动2数据透视表的修改操作步骤如下：（1）按【活动项目2数据透视表的创建】的流程，制作如下图所示的按普通数据方式显示的数据透视表。2．数据显示形式的修改。工作任务二数据透视表的设置项目活动2数据透视表的修改（2）在前图所示的表格中的A3单元格上单击鼠标右键，从弹出的快捷菜单中单击“值字段设置”菜单项，之后将弹出“值字段设置”对话框，；单击对话框中的“选项”按钮，在弹出的“值显示方式”中选择“全部汇总百分比”，确定后，会出现如下图所示的按“全部汇总百分比”方式显示数据的数据透视表效果。2．数据显示形式的修改。工作任务二数据透视表的设置项目活动2数据透视表的修改（3）单击“文件”菜单，打开“选项”对话框，在其中的“高级”选项卡中找到“在具有零值的单元格中显示零值(Z)”复选框，取消其选中状态，将数据透视表中的零值隐藏起来，就得到了所要求的数据透视表效果。2．数据显示形式的修改。工作任务二数据透视表的设置项目活动2数据透视表的修改表4-1数据透视表中各种数据显示方式的含义方式含义普通按一般的数据形式显示数据差异结果与基本字段和基本项对话框中指定的字段和项之间的差百分比结果除以指定的基本字段和项，表示成百分比差异百分比结果与指定的字段和项之间的差，除以基本字段和项的百分比按列增加按基本字段中指定的列递增的方式显示数据占同列数据总和的百分比透视表中的结果数据除以同列数据总和的百分比占同行数据总和的百分比透视表的结果数据除以同行数据总和的百分比占总和的百分比结果与所有数据总和的百分比指数用公式“（单元格的值×总计）/（行总计×列总

计）”计算数据1．使用透视表筛选数据。工作任务二数据透视表的设置项目活动3数据透视表的使用问题分析：这里的数据透视表，与前面不同之处就在于其中的两个标签项目进行了筛选：一个是指表筛选区域的“销售日期”页字段，它已经不再是所有的销售日期，而是选择了部分日期，另一个是“客户名称”行字段，其中进行了一定的客户筛选。1．使用透视表筛选数据。工作任务二数据透视表的设置项目活动3数据透视表的使用下面就以上述要求为例，说明在数据透视表中进行数据筛选的操作。操作步骤如下：（1）在前面的数据透视表上，单击“销售日期”页字段右侧的箭头，弹出如下图所示的日期列表框，单击列表区中需要的日期，即可筛选出选中的日期数据。1．使用透视表筛选数据。工作任务二数据透视表的设置项目活动3数据透视表的使用下面就以上述要求为例，说明在数据透视表中进行数据筛选的操作。操作步骤如下：（2）单击“确定”按钮，数据秀视表进行了一定的筛选，结果如图所示。1．使用透视表筛选数据。工作任务二数据透视表的设置项目活动3数据透视表的使用下面就以上述要求为例，说明在数据透视表中进行数据筛选的操作。操作步骤如下：（3）单击“客户名称”行字段右侧的箭头，将弹出如下图所示的菜单，利用这个菜单中的相关菜单项，可以对客户名称进行排序和筛选操作。2．在数据透视表中排序。工作任务二数据透视表的设置项目活动3数据透视表的使用一般情况下，数据透视表中各项目的顺序是按一定的顺序排列的。根据需要，可以通过手工方式拖动，或者通过自定义方式，对数据透视表中各个字段下的各个项目进行重新排序。这里对数据透视表进行的排序，使得各个业务员的销售业绩由高到低，便于对比：而客户名称按照汉字笔划顺序进行升序排列，也便于查找。2．在数据透视表中排序。工作任务二数据透视表的设置项目活动3数据透视表的使用具体操作步骤如下：（1）按【活动项目2数据透视表的创建】的流程，制作如图所示的按默认顺序方式显示的数据透视表。2．在数据透视表中排序。工作任务二数据透视表的设置项目活动3数据透视表的使用具体操作步骤如下：（2）选中“业务员”所在单元格，从“数据透视表工具”功能区中单击“排序”命令按钮，在弹出的对话框中的“降序排序依据”中选择“求和项：合计金额”。2．在数据透视表中排序。工作任务二数据透视表的设置项目活动3数据透视表的使用具体操作步骤如下：（3）在数据透视表中选中“客户名称”所在单元格，进行以上类似的操作，可以实现按客户名称进行的排序。1．数据更新。工作任务三数据透视表的编辑项目活动1

对数据透视表的数据操作数据透视表的数据来源于数据源，不能在透视表中直接修改；当原数据表中的数据被修改之后，数据透视表中的数据不会自动地更新，必须执行更新操作才能刷新数据透视表。当原数据表中的数据发生变化，需要更新数据透视表时，有以下三种方法可用：方法一：在数据透视表任意位置单击右键，从弹出的快捷菜单中选择“刷新”命令。方法二：先用鼠标在数据透视表上单击，使之为活动状态，然后单击【数据透视表工具】功能区的“刷新”命令按钮1．数据更新。工作任务三数据透视表的编辑项目活动1

对数据透视表的数据操作方法三：先用鼠标在数据透视表上单击，使之为活动状态，然后按Alt+F5组合键。2．内容的复制。工作任务三数据透视表的编辑项目活动1

对数据透视表的数据操作对于数据透视表中的数据，可以进行复制操作，此时只要选取需要复制的内容，然后直接执行“复制”操作即可。可以复制数据透视表的一部分，也可以复制数据透视表的整体，还可以使用“选择性粘贴”中的“粘贴值”只粘贴数值，或者通过“粘贴链接”实现复制后的数据能与数据透视表中的数据保持同步变化。

如果不再想要数据表视表，可以将其删除，但是只能整体(行或列)删除，而不能只删除其中的一部分。如果试图删除数据透视表中的部分数据。3．删除数据透视表。工作任务三数据透视表的编辑项目活动1

对数据透视表的数据操作删除数据透视表之前，需要先选取数据透视表整体，再执行删除。操作步骤如下：（1）单击要删除的数据透视表中任意一个单元格。（2）在【数据透视表工具】功能区上，单击【选项】功能中的“清除”菜单。（3）在弹出的下拉菜单中，选择“全部清除(C)”即可。3．删除数据透视表。工作任务三数据透视表的编辑项目活动2数据透视表的其他操作数据透视表中，除了已经提供的数据汇总方式和数据显示方式之外，如果需要，用户还可以自己定义数据字段或者数据项。问题分析：此处题目已经给定了提成金额的计算公式，可以根据该公式在数据透视表中增加一个由自定义公式创建的计算字段。1．通过自定义公式来计算提成金额。工作任务三数据透视表的编辑项目活动2数据透视表的其他操作具体操作步骤如下：（1）单击数据透视表中的任一单元格。（2）单击【数据透视表工具】功能区的“项目和集”命令，在弹出的下拉菜单中选择“计算字段”项。1．通过自定义公式来计算提成金额。工作任务三数据透视表的编辑项目活动2数据透视表的其他操作具体操作步骤如下：（3）在“插入计算字段”对话框中，先在“名称”框内输入“提成金额”；然后将光标移动到“公式”框中，清除掉原有的“=0”，双击“字段”列表框中的“合计金额”，最后在“公式”框的“=合计金额”后面再输入“*0.02”。1．通过自定义公式来计算提成金额。工作任务三数据透视表的编辑项目活动2数据透视表的其他操作具体操作步骤如下：（4）单击“确定”按钮，包含“提成金额”字段的透视表制作完毕。1．通过自定义公式来计算提成金额。工作任务三数据透视表的编辑项目活动2数据透视表的其他操作在数据透视表中可对有关项目进行组合操作，包括按日期进行组合、对数字项进行组合、按指定项目组合等操作，组合之后也可再取消。例如按“日期”进行组合2．对销售日期项按月份进行组合。工作任务三数据透视表的编辑项目活动2数据透视表的其他操作在数据透视表中，也可根据需要，自己指定项目进行组合操作。问题分析：下图中的数据透视表，按照各种费用的不同性质，对费用类别进行了组合处理，并对组合的整体自定义了相关的名称。3．按照指定项目对销售费用进行组合。工作任务三数据透视表的编辑项目活动2数据透视表的其他操作在数据透视表中，也可根据需要，自己指定项目进行组合操作。问题分析：下图中的数据透视表，按照各种费用的不同性质，对费用类别进行了组合处理，并对组合的整体自定义了相关的名称。其操作步骤如下：（1）打开前面建的数据透视表。（2）单击“办公费”所在的A5单元格，然后按下Ctrl键，再去选择需要组合的“文印费”所在的A10单元格，最后单击鼠标右键，弹出快捷菜单。3．按照指定项目对销售费用进行组合。工作任务三数据透视表的编辑项目活动2数据透视表的其他操作其操作步骤如下：（3）在右键快捷菜单中单击“创建组(G)”命令按钮，组合选定的两个费用类别。其默认的组合名称为“数据组1”。（4）按照与上面步骤（2）～（3）类似的方法，把其他需要组合的项目组合起来。之后，数据透视表将变为如图所示的效果。3．按照指定项目对销售费用进行组合。工作任务三数据透视表的编辑项目活动2数据透视表的其他操作其操作步骤如下：（5）将光标置于上图所示表格的“数据组1”所在的单元格中，然后在编辑栏中输入组合后的名字“日常费用”。（6）按照同样的方法，编辑其他数据组的名字为对应名称，之后将得到下图所要求的效果，双击其中组合后项目的单元格，可只显示组合后的名称。3．按照指定项目对销