EXCEL-数据透视.ppt

EXCEL实战技巧交流,高效办公之数据透视表,主讲：李启涛,数据透视表,数据透视表的认识数据透视表的创建数据透视表的编辑数据透视表的简单运用数据透视表的综合运用数据透视表的动态运用,一、数据透视表介绍1、什么是数据透视表数据透视表是一种可以快速汇总、分析大量数据表格的交互式工具。使用数据透视表可以按照数据表格的不同字段从多个角度进行透视，并建立交叉表格，用以查看数据表格不同层面的汇总信息、分析结果以及摘要数据。使用数据透视表可以深入分析数据，以帮助用户发现关键数据，并做出有关关键数据的决策。2、数据透视表的设计用途以友好的方式，查看大量的数据表格对数据快速分类汇总，按分类和子分类查看数据信息展开或折叠所关注的数据，快速查看摘要数据的明细信息建立交叉表格(行列之间的相互移动)，以查看源数据的不同汇总快速计算数据的汇总信息、差异、个体占总体的百分比信息等,3、数据透视表的规则若要创建数据透视表，要求数据源必须是比较规则的，也只有比较大量的数据才能体现数据透视表的优势。如：表格的第一行是字段名称，字段名称不能为空；数据记录中最好不要有空白单元格或合并单元格；每个字段中数据的类型必须一致(如：“日期”字段的值即有日期型数据又有文本型数据，则无法按照日期进行组合)，数据越规则，数据透视表使用越方便。4、为什么要使用数据透视表如：五金仓库入仓明细中包括入库日期，材料名称，供应商，以及单价，数量，金额等，如需从此明细中统计出：按材料名称统计入仓数据和金额是多少按供应商统计入仓数据和金额是多少按材料类别统计入仓数据和金额是多少分别计算各项目入仓百分比等.如使用普通计算方法，不仅工作量大，而且数据统计容易出错，特别是数据明细发生变化时，需重新统计结果。而使用数据透视表一步即可轻松解决。,二、数据透视表的使用1、创建数据透视表尽管数据透视表的功能非常强大，但是创建的过程却是非常简单。A、将光标点在表格数据源中任意有内容的单元格，或将整个数据区域选中B、选择“数据”选项卡，单击“数据透视表和数据透视图”命令。,以五金入库明细为例做分析说明,C、在“图向导-3步骤之1”对话框中选择“MicrosoftExcel数据列表或数据库”和“数据透视表”，单击“下一步”按钮。D、打开“图向导-3步骤之2”对话框，在其中用鼠标选定建立数据透视表的数据源区域(透视表会自动默认选择当前区域)，单击“下一步”按钮。E、打开“图向导-3步骤之3”对话框，在其中选择“新建工作表”(也可选择“现有工作表”并在工作表中单击产生透视表的起始位置)，单击“完成”按钮,F、在“数据透视表字段列表”中，单击选择“供应单位”，将其拖放至“将行字段拖至此处”区域，同样，把字段“大类”拖放至“将列字段拖至此处”区域，把“不含税金额”，“税金”，“含税金额”字段分别拖放至“将数据项拖至此处”区域，这样就得到了数据透视表(如图),2、编辑数据透视表A、设置报告格式点击“数据透视表”工具栏中的“设置报告格式”按钮，在“自动套用格式”对话框中选取格式模板，点击“确定”按钮,以选取模板一为例做分析说明,B、变换透视角度为更清楚地反映出各供应单位采购类别情况，现将数据透视表中的行字段和列字段交换位置。点击“数据透视表”工具栏中“数据透视表”下拉列表，选择“数据透视表向导”打开“图向导-3步骤之3”对话框，单击“布局”按扭，将“供应单位”与“大类”位置进行调换。单击“确定”按钮，再点击“完成”按钮,无需通过向导直接在数据区域拖放也可快速实现,C、添加和删除字段D、更新数据透视表数据E、数据透视表的计算功能F、.等由于数据透视表的功能过于强大将以举例来做综合的演示说明,三、数据透视表的演示A、从五金仓库出仓单明细中统计出各部门领用各材料类别的费用金额。一般操作方法：1、排序分类汇总筛选2、SUMIF函数和SUMPRODUCT函数3、其它方法本次演示：两种创建数据透视表的方法,B、制作带本页小计和累计的数据表当我们打印某一份报表时，需要在每一页上都打印出某列小计数，在最后一页上打印累计数。一般操作方法：1、数据量少时可手动增加2、利用VBA(宏)或函数本次演示：重构数据源，增加辅助字段，数据分组合并，设置自动分页等功能,C、制作材料收发存汇总表运用导入外部数据“编辑OLEDB查询”将“期初明细”、“入仓明细”、“出仓明细”，三张数据列表合并汇总并生成动态的数据透视表，再利用数据透视表添加计算项的功能计算出结存数据，从而完成“收发存”的编制。一般操作方法：手动将三张表合并后进行操作本次演示：导入外部数据创建数据透视表，透视表的布局，透视表的计算项功能SQL语句解析,SQL语句分析select材料编号,结存数量,结存金额,期初as类别from期初明细$unionallselect材料编号,实收数量,不含税金额,入仓as类别from入仓明细$unionallselect材料编号,实发数量,金额,出仓as类别from出仓明细$将期初明细表中的“材料编号”列，“结存数量”列，“结存金额”列，并新增加“类别”列为期初；入仓明细表中的“材料编号”列，“实收数量”列，“不含税金额”列，并新增加“类别”列为入仓；出仓明细表中的“材料编号”列，“实发数量”列，“金额”列，并新增加“类别”列为出仓；进行合并如三张表结构完全相同，只需编写SQL语句为：Select“期初”*from期初明细$unionallselect“入仓*from入仓明细$unionallselect出仓*from出仓明细$此语句类似模拟将三张表所需数据资料合并为一张表当数据源发生改变时，透视表中数据也相应发生改变,D、制作复合分类项数据透视表在同一张表中分别统计出五金仓出仓明细，各部门领用数量金额，各材料领用数量金额，以及各材料类别领用数量金额。一般操作方法：分三次做数据透视表然后合并到报表中本次演示：利用SQL语句对同一数据源表的相关字段在同一位置上进行多次引用，重新构建数据透视表数据源，从面制作出复杂结构的复合分类项数据透视表,SQL语句分析select领用部门,实发数量,金额,部门as分类from出仓明细$unionallselect材料编号,实发数量,金额,材料as分类from出仓明细$unionallselect大类,实发数量,金额,类别as分类from出仓明细$将出仓明细表中的“领用部门”列，“实发数量”列，“金额”列，并新增加“分类”列为部门；再次将“材料编号”列，“实发数量”列，“金额”列，并新增加“分类”列为材料；再次将“大类”列，“实发数量”列，“金额”列，并新增加“分类”列为类别；进行合并此语句类似模拟将表中所需数据资料复制三次后合并为一张表当数据源发生改变时，透视表中数据也相应发生改变,四、数据透视表常见问题诊断1、数据透视表字段名无效在创建透视表时，必须使用组合为带有标志列列表的数据，如果要更改数据透视表字段的名称，必须键入字段的新名称。解决方法：查找是否有标题列，或标题列中某列无标题或出现有合并单元格2、刷新数据透视表时数据消失刷新数据透视表后，放入值区域的字段消失，并删除了数据透视表中的数据解决方法：出现这种情况是因为更改了放入值区域的字段名称，数据透视表无法计算不存在的字段，需打开”数据透视表列表“，将新字段拖入值区域中,3、数据透视表总是使用“计数”而不使用“求和”在数据源中有一列数字，但是每次添加数据透视表时，总是使用“计数”，而不是“求和”，需要手动将计算方法更改为“求和”解决方法：如果在源数据列存在任何文本值，都会自动对该列的数据字段应用“计数”，即使有一个空白单元格也会默认“计数”。只需从源数据列中删除或填充文本值和空白单元格，刷新数据表即可。4、总是将列调整为最适合标题的列宽刷新数据透视表或者选择刷选字段中的一个新项时，包含标题的列自动调整为列宽适合列标题。如果不希望更改已经做好的报表格式时会很麻烦。解决方法：右键单击数据透视表，选择“表格选项”打开“数据透视表选项”窗口，在新的对话框中，选择“格式选项”卡，取消选中“自动套用格式”,5、怎样让数据透视表自动刷新某些情况下可能需要让数据透视表自动刷新解决方法：右键单击数据透视表，选择“表格选项”打开“数据透视表选项”窗口，在新的对话框中选择“数据选项”卡，勾选“打开时刷新”属性。6、怎样同时刷新一个工作簿中的所有数据透视表或指定的透视表。解决方法：A、同第5点，将所有数据透视表手动设定为自动刷新B、需要借助VBA(宏)来完成，参考代码为：Subrefresh_all()Thisworkbook.refreshallEndsub,五、小结数据透视表是Excel数据处理中的一个难点，涉及到多方面知识和操作技能。使用数据透视表的过程实质是数据计算和数据管理的集成应用。制作数据透视表的过程是从