数据透视表数据透视图和合并计算1(应用2).pptx

《数据透视表数据透视图和合并计算1(应用2).pptx》由会员分享，可在线阅读，更多相关《数据透视表数据透视图和合并计算1(应用2).pptx（118页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel 2010 Excel 2010 应用应用2张桂香l数据数据透视表和数据透视图透视表和数据透视图l合并计算合并计算主要内容主要内容u单表数据源创建数据透视表和数据透视图u多表创建动态数据透视表u多表创建单页字段单页字段数据透视表u多表创建自定义页字段自定义页字段数据透视表u利用数据透视表快速统计重复项目数据数据透视表和数据透视图透视表和数据透视图4 Excel的数据透视表是一种可以轻松的对数据进行交叉分析的三维表格。它的特点在于表格的不固定性，可以随时根据实际需要进行调整，从而得到不同的视图。它可以将数据的排序、筛选和分类汇总3个过程结合在一起，可以转换行和列查看数据的不同汇总结果，

2、可以显示不同页面来筛选数据，还可以根据需要显示指定区域的明细数据，非常便于用户在一个清单中重新组织和统计数据。数据透视表和数据透视图数据透视表和数据透视图数据透视表和数据透视图数据透视表和数据透视图制作步骤分三步：制作步骤分三步：3.指定数据透指定数据透视表位置表位置2.选择数据源区域数据源区域1.选择数据源数据源类型型一、单表数据源做数据透视表和数据透视图6一、单表数据源做数据透视表和数据透视图选择要分析的要分析的数据区域数据区域一、单表数据源做数据透视表和数据透视图一、单表数据源做数据透视表和数据透视图一、单表数据源做数据透视表和数据透视图改改为“订货日期日期”改改为“城市城市”关关闭数据

3、透数据透视表字段列表表字段列表11一、单表数据源做数据透视表和数据透视图12(创建组创建组)右右键单击一、单表数据源做数据透视表和数据透视图13一、单表数据源做数据透视表和数据透视图(创建组创建组)一、单表数据源做数据透视表和数据透视图筛选数据筛选数据筛选数据数据一、单表数据源做数据透视表和数据透视图美化表格美化表格16一、单表数据源做数据透视表和数据透视图增加列增加列一、单表数据源做数据透视表和数据透视图增加列增加列一、单表数据源做数据透视表和数据透视图改变报表布局改变报表布局一、单表数据源做数据透视表和数据透视图20一、单表数据源做数据透视表和数据透视图数据筛选页数据筛选页数据数据筛选页的

4、效果的效果一、单表数据源做数据透视表和数据透视图数据筛选页数据筛选页一、单表数据源做数据透视表和数据透视图数据筛选页数据筛选页23一、单表数据源做数据透视表和数据透视图数据透视图数据透视图一、单表数据源做数据透视表和数据透视图数据透视图数据透视图可以可以筛选地区地区可可筛选年份年份小结：小结：该案例讲解了利用数据透视表和数据透视图从产品的销售明细中得到了各地区各季度各年份的销售情况，可以灵活立体三维分析数据。二、多表创建单页字段单页字段数据透视表二、多表创建单页字段单页字段数据透视表二、多表创建单页字段单页字段数据透视表二、多表创建单页字段单页字段数据透视表二、多表创建单页字段单页字段数据透视

5、表二、多表创建单页字段单页字段数据透视表二、多表创建单页字段单页字段数据透视表二、多表创建单页字段单页字段数据透视表单击单击“列标签列标签”，选择，选择“金额金额”和和“数量数量”，如图：，如图：二、多表创建单页字段单页字段数据透视表二、多表创建单页字段单页字段数据透视表二、多表创建单页字段单页字段数据透视表三、多表创建自定义自定义页页字段字段数据透视表以上题的数据源为例讲解以上题的数据源为例讲解三、多表创建自定义自定义页页字段字段数据透视表页字段字段别忘忘了，要和上了，要和上面配套哦面配套哦三、多表创建自定义自定义页页字段字段数据透视表三、多表创建自定义自定义页页字段字段数据透视表三、多表创

6、建自定义自定义页页字段字段数据透视表三、多表创建自定义自定义页页字段字段数据透视表三、多表创建自定义自定义页页字段字段数据透视表多表创建数据透视表创建多重合并计算数据区域的数据透视表：数据源可以是同一个工作簿的多张工作表，也可以是其他工作簿的多张工作表，但待但待合并的数据源工作表结构必须完全一致，合并的数据源工作表结构必须完全一致，当数据内容更改时，可以刷新数据透视表来更新数据透视表，但当数据源的数据量更改时则不可以更新数据透视表。怎么创建动态的数据透视表呢：即记录量增怎么创建动态的数据透视表呢：即记录量增减时，能自动更新数据减时，能自动更新数据透视透视表表？45四、多表创建动态数据透视表四、

7、多表创建动态数据透视表四、多表创建动态数据透视表四、多表创建动态数据透视表四、多表创建动态数据透视表50Offset函数函数以以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。元格或单元格区域。并可以指定返回的行数或列数。语法：语法：OFFSET(reference,rows,cols,height,width)Reference作为偏移量作为偏移量参照系参照系的引用区域。的引用区域。Reference必须为对单元格或相连单必须为对单元格或相连单元格区域的引

8、用；否则，函数元格区域的引用；否则，函数OFFSET返回错误值返回错误值#VALUE!。Rows相对于偏移量参照系的左上角单元格，上（下）相对于偏移量参照系的左上角单元格，上（下）偏移的行数偏移的行数。如果使用。如果使用5作为参数作为参数Rows，则说明目标引用区域的左上角单元格比，则说明目标引用区域的左上角单元格比reference低低5行。行行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。Cols相对于偏移量参照系的左上角单元格，左（右）相对于偏移量参照系的左上角单元格，左（右）偏移的列数偏移的列数。

9、如果使用。如果使用5作作为参数为参数Cols，则说明目标引用区域的左上角的单元格比，则说明目标引用区域的左上角的单元格比reference靠右靠右5列。列列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height高度，即所要返回的高度，即所要返回的引用区域的行数引用区域的行数。Height必须为正数。必须为正数。Width宽度，即所要返回的宽度，即所要返回的引用区域的列数引用区域的列数。Width必须为正数。必须为正数。四、多表创建动态数据透视表51函数：函数：sum(offset(b3,2,1,3,2

10、)等价于等价于sum(c5:d7)、sum(11,13,15,15,18,21)等价等价四、多表创建动态数据透视表52四、多表创建动态数据透视表定义数据源名称定义数据源名称=OFFSET(河东区河东区!$A$1,COUNTA(河东区河东区!$A:$A),COUNTA(河东区河东区!$1:$1)四、多表创建动态数据透视表参照位置参照位置无偏移量无偏移量所有行所有行所有列所有列四、多表创建动态数据透视表四、多表创建动态数据透视表56四、多表创建动态数据透视表四、多表创建动态数据透视表在选定区域框中输入在选定区域框中输入“河东区河东区”，并单击，并单击“添加添加”按钮，按同按钮，按同样的方法将四个区

11、域添加到所有区域下拉列表中。并选中样的方法将四个区域添加到所有区域下拉列表中。并选中“1”单选按钮。单选按钮。添加数据源添加数据源四、多表创建动态数据透视表选中“北辰区“，在“字段1”文本框中输入输入“北辰区北辰区”，再选中“河西区”，在“字段1”文本框中输入“河西区”，按同样的方法把别的区域做完给页字段命名给页字段命名类似上似上题的的筛选项哦哦四、多表创建动态数据透视表四、多表创建动态数据透视表61四、多表创建动态数据透视表右右键单击生成数据透视表生成数据透视表62四、多表创建动态数据透视表同同样方法修改方法修改“行行标签”为“品名品名”修改修改“页1”为“地区地区”四、多表创建动态数据透视

12、表四、多表创建动态数据透视表组合数据组合数据选中中电器器类产品品四、多表创建动态数据透视表组合数据组合数据选中，右中，右键单击66四、多表创建动态数据透视表组合数据组合数据四、多表创建动态数据透视表组合数据组合数据按同按同样方法分方法分组四、多表创建动态数据透视表单击【数据透视表工具】单击【数据透视表工具】|【设计】【设计】|【报表布局】【报表布局】|【以表格形式显【以表格形式显示】示】。更改报表布局更改报表布局四、多表创建动态数据透视表将将“品名品名2”改名为改名为“类别类别”，在分类汇总下拉列表框中选择，在分类汇总下拉列表框中选择“求和求和”，然后更改单元格，然后更改单元格“数据组数据组1

13、”的名字为的名字为“电器电器”，同样更改，同样更改“”“”数据组数据组2为为“其它其它”等等等等选中电器组合项下的任一单元格，如选中电器组合项下的任一单元格，如A6，单击鼠标右键，弹出快，单击鼠标右键，弹出快捷菜单，选单击捷菜单，选单击“数据透视表选项数据透视表选项”，打开，打开“数据透视表选项数据透视表选项”对对话框，话框，“布局和格式布局和格式”选项卡中的选项卡中的“布局布局”，勾选，勾选“合并且居中排合并且居中排列带标签的单元格列带标签的单元格”。更改部分名称，符合常态理解更改部分名称，符合常态理解四、多表创建动态数据透视表单击右右键选择显示列字段选择显示列字段四、多表创建动态数据透视表

14、四、多表创建动态数据透视表使用定使用定义名称的方法名称的方法创建的数据透建的数据透视表是表是动态的，即在的，即在数据数据源表中可增减源表中可增减记录。但。但是要求数据源区域中的首行和首列不能有是要求数据源区域中的首行和首列不能有空白空白单元格，否元格，否则将无法用定将无法用定义名称取得名称取得正确的区域。正确的区域。73数据透视表的排序数据透视表的排序在需要排序的列的任意非字段单元格上单击右键，在弹出的快捷菜单中选择【排序/升序】,如下图。四、多表创建动态数据透视表数据透视表的刷新数据透视表的刷新方法1：在数据透视表的任意单元格上单击鼠标右键，在弹出的快捷菜单中单击【刷新】。如下图。方法2：在

15、【数据】选项卡中单击【全部刷新】。四、多表创建动态数据透视表数据透视表的刷新方法3，打开工作簿时刷新，右键单击数据透视表的任意单元格，弹出如下快捷菜单，选择“数据透视表选项”。选择此项五、使用数据透视表快速统计重复项目使用Excel函数公式可以统计数据列表中某个字段的重复次数，但更为快捷的方式是使用数据透视表。统计各各组的的人数人数五、使用数据透视表快速统计重复项目五、利用数据透视表快速统计重复项目插入切片器插入切片器 选择数据透视表工具下的选项，点击插入切片器在弹出的“插入切片器”对话框中勾选“姓名”和“组别”选项。单击“确定”按钮。79五、利用数据透视表快速统计重复项目关关闭切片器切片器

16、单击清除筛选器按钮或使用Alt+C键即可关闭切片器。如果要删除切片器，选择某个切片器，按Delete键即可。80五、利用数据透视表快速统计重复项目五、利用数据透视表快速统计重复项目在数据透视表中添加计算项数据透视表提供了强大的自动汇总功能，除了“求和”、“计数”、“求平均”、“方差”等多种汇总方式。如不能满足需求还可以在数据透视表中添加计算项的方法达到目的。在数据透视表中添加计算项在数据透视表中添加计算项选中数据透视表的“列标签”单元格，在【数据透视表工具】的【选项/域、项目和集】中选择“计算项”，如图：85更改数据透视表的数据源 当数据源中添加新的记录时，刷新数据透视表，新增的记录不会自动添

17、加到数据透视表中，（当记录内容更改时，刷新纪录，能实现数据透视表的更新），此时可以更改数据透视表的数据源，使数据透视表的数据源包括新增记录。更改数据透视表的数据源原数据原数据更改数据透视表的数据源原数据透原数据透视表表更改数据透视表的数据源更改数据源更改数据源更改数据透视表的数据源更改数据透视表的数据源重新选择数据源重新选择数据源更改数据透视表的数据源合并计算合并计算u利用合并计算创建分户报表利用合并计算创建分户报表u有选择的合并计算有选择的合并计算u多工作表筛选不重复的值多工作表筛选不重复的值u利用合并计算对数值型数据进行校对利用合并计算对数值型数据进行校对u利用合并计算对文本型数据进行校对

18、利用合并计算对文本型数据进行校对合并计算93Excel中的“合并计算”功能可以汇总或合并多个数据源区域中的数据。合并计算的数据源区域可以是同一个工作表中的不同表格，也可以是同一工作簿中的不同工作表，还可以是不同工作簿中的表格。求各城市的求各城市的销售数售数量和金量和金额选中A13单元格，作为合并计算后结果的存放起始位置，再单击【数据/合并计算】命令按钮，打开【合并计算】对话框。合并计算结果结果修饰后修饰后注意：注意：u在使用按类别合并的功能时，数据源列表必须包含行或列标题，并且在【合并计算】对话框的【标签位置】组合框中勾选相应的复选框。u合并的结果表中包含行列标题，但在同时选中的【首行】和【最

19、左列】复选项时，所生成的合并结果表会缺失第一列的标题。u合并后，结果表的数据项排列顺序是按第一个数据表的数据项顺序排列。u合并计算过程不能复制数据表的格式，如果设置结果表的格式，可以使用【格式刷】。合并计算利用合并计算创建分户报表合并计算可以按类别进行合并，如果引用区域的行、列方向均包含了多个类别时，则可以利用合并计算功能将引用区域中的全部类别汇总到同一表格上并显示所有明细。利用合并计算创建分户报表有选择的合并计算五月份五月份销售情况售情况六月份六月份销售情况售情况七月份七月份销售情况售情况怎么按日期怎么按日期汇总销售数量和售数量和销售售金金额？有选择的合并计算1.在“汇总”工作表的A1:C1

20、单元格区域，分别输入所需汇总的列字段名称：“日期”，“销售数量”和“销售金额”，然后选中然后选中A1:C1单元格区域，这是最关键的步骤单元格区域，这是最关键的步骤。2.单击【数据/合并计算】命令，打开【合并计算】对话框。有选择的合并计算有选择的合并计算修改修改为日期格日期格式式有选择的合并计算修改数据透修改数据透视表后表后的的样子子做数据透做数据透视表表后的后的样子子有选择的合并计算多工作表筛选不重复的值多工作表筛选不重复的值注意：注意：合并计算的按类别求和功能不能对不包含任何数值的数据区域进行合并操作，但只要选择合并的区域内包含一个数字即可进行合并，利用这一特性，在表1的B2单元格输入0，进行合并即可添加添加辅助助计算列算列多工作表筛选不重复的值多工作表筛选不重复的值合并后的合并后的结果果利用合并计算对数值型数据进行校对利用合并计算对数值型数据进行校对利用合并计算对数值型数据进行校对不一不一样的的数据数据利用合并计算对数值型数据进行校对利用合并计算对文本型数据进行校对添加辅助列，如下图：添加辅助列，如下图：利用合并计算对文本型数据进行校对筛选后后利用合并计算对文本型数据进行校对休息！休息！