Excel数据透视表

第一节课-什么是数据透视表

1. 如果需要对数据源进行分类汇总（或者是分类计数，或者是分类平均值等），就可以用数据透视表来解决。
2. 它的优点是高效，易操作（相对函数）

第二节课-创建数据透视表

1. 选中数据源-插入选项卡-数据透视表-打开了创建数据透视表对话框，选择数据透视表放置的位置-确定
2. 数据透视表字段对话框，可以在设计选项卡中显示组，选中数据字段显示或不选中不显示数据透视表字段对话框
3. 字段拖到行标签的意思，是对此字段的记录进行去重，结果放在一列中的各行
4. 字段拖到列标签的意思，是对此字段的记录进行去重，结果放在一行中的各列
5. 字段拖到值区域的意思，是根据行标签或列标签里字段去重的结果，进行汇总
6. 筛选区域，也是对字段记录进行去重，但是是起到筛选的作用
7. 并不是每一个区域都必须有字段才行
8. 一个区域也可以有多个字段，比如行标签有两个字段，那么上面的就是父字段，下面的是子字段

第三节课-数据源规范

1. 数据源区域每一列必须有表头，不能空白
2. 数据源中同一类记录必须在一列中，不能分布在多列中
3. 数据源中表头不能是合并单元格
4. 数据源中字段中的记录不能有合并单元格，同一记录不能用两种形式表示
5. 数据源中字段不能有计算行
6. 数据源中的数据类型要规范

第四节课-数据透视表的布局1

1. 把各个字段在四个区域中拖动其实就是改变了数据透视表的布局
2. 除了拖动字段，还可以在字段列表中右键，选择添加到哪个区域中

已经在四个区域中的字段，点开下拉菜单，可以上下移动（如果有多个字段，可以改变其上下位置），或者是移动到其他区域中，也可以删除字段

在数据透视表上，在相应的字段上右键，也可以选择删除字段，或者是移动字段

1. 筛选区域的设置改变布局：选择选择多项，可以进行多条件的筛选

数据透视表上右键，打开数据透视表选项-布局和格式-然后可以改变筛选字段的是纵向并排还是横向并排

在分析选项卡中，数据透视表组，点开选项，选择显示报表筛选页，可以按筛选字段把数据分布在独立的工作表中

4、2003版本经典布局样式：右键打开数据透视表选项-显示-勾选经典布局样式，可以直接将字段拖到数据透视表上。

第五节课-数据透视表的布局2

1. 重命名数据透视表上的字段名：选中，在编辑栏中重新命名，注意，不能和之前的字段名一致，若要一样，可以加一个空格

批量修改，可以用查找替换，把计数项或汇总项替换成空格

1. 显示or隐藏行标签或列标签，到分析选项卡，显示组，字段标题去掉选中或选中
2. 分析选项卡中，可以设置展开或折叠字段，或者在相应字段上右键设置

在分析选项卡显示组，加减号按钮显示or隐藏

1. 报表布局：压缩形式，大纲形式，表格形式

分类汇总：可以显示或不显示，也可以设置显示在组上方或下方

总计：不显示或只显示行或只只显示列

空行：在每一组下方插入一行空行，以做区分作用

第六节课-更改数据源和刷新数据透视表

1. 数据源添加记录，需要在分析选项卡中，点击更改数据源，重新选择数据源区域，然后再点击分析选项卡中的刷新按钮即可更新到最新结果
2. 也可以在数据透视表上右键，点击刷新
3. 给数据源套用开始选项卡中的表格样式，使它变成列表形式，那么再添加或删除数据源的记录，数据源范围会自动更新，只需要右键刷新下即可
4. 在数据透视表选项中，可以在 数据 选项中，勾选打开文件时刷新数据，即可实现每次打开文件，都会自动刷新
5. 如果是通过外部数据源创建的数据透视表，还可以在分析选项卡-更改数据源-连接属性中，设置刷新的频率。注意只有是外部数据源的数据透视表才有此功能
6. 如果数据源数据非常多，每一次拖动字段都会卡（电脑计算一会），那么可以点击 推迟布局更新，等所有字段都托好了，手机点击更新按钮。最后记得去掉这个勾，因为会影响到排序筛选等
7. 当我们删除了数据源中的相同项的数据，刷新数据透视表结果会更新，但是在筛选里仍然会有此项，若要删除，可以在数据源选型中—数据，把保留从数据源删除的项目—每个字段保留的项数改成 无， 再刷新下数据透视表即可。

第七节课-数据透视表的格式设置

1. 可以在设计选项卡-数据透视表样式组，套用样式，同时可以设置行标题列标题，镶边行和镶边列
2. 我们可以手动设置数据透视表的字体、字号、边框、颜色填充、列宽、数值格式等
3. 在数据透视表选项中，建议不勾选 更新时自动调整列宽，和勾选 更新时保留单元格格式。
4. 值区域里的空单元格，可以在数据透视表选项里设置空单元格显示成什么

错误值也可以设置显示成什么

1. 如果行标签或列标签中有（空白），可以使用查找替换方式替换掉
2. 在数据透视表中也可以使用条件格式，和普通表格使用方法一样

第八节课-数据透视表的排序和筛选

1. 在相应的字段上右键-排序可以选择升序或降序，默认是升序排序的
2. 在行标签里的字段排序是对这一列进行排序，在列标签里的字段进行排序是对这一行排序，如果对值区域里的一个单元格排序，那么默认是对这一列进行排序，如果想对这一行排序，可以右键-排序-其他排序选项-选择方向-从左到右
3. 注意，排序时定位一个单元格即可，选中一行或一列是不能排序的
4. 如果想按自己的想法排序，可以定位到一个单元格，然后鼠标指针放在边缘，变成四向箭头的时候拖动，可以移动它的位置，也可以右键-移动-上下移动或移动到头或尾
5. 另一个方法是先到  文件-选项-高级-自定义序列-先添加顺序，然后再升序排序即可
6. 在行标签或列标签下拉选项里可以勾选需要显示的项即可完成筛选
7. 也可以在行标签或列标签下拉选项里进行搜索完成筛选，针对项比较多的情况
8. 可以使用标签筛选，等于，不等于，开头是等等等。
9. 行标签里的值筛选是筛选与行标签对应的总计里的数值，列标签里的值筛选是与列标签对应的总计里的数值
10. 若要对值区域里其他列进行筛选，可以把活动单元格定位到数据透视表表头旁边，但不能定位到数据透视表上，然后在数据选项卡里，选择筛选，除了常规的筛选，还可以按颜色来筛选（如果有颜色的话）

第九节课-切片器

1. 切片器也是用来筛选的，但是它不仅可以让数据透视表显示筛选结果，也可以直观显示哪些数据项被筛选了
2. 选中数据透视表-分析选项卡或者插入选项卡都可以插入切片器
3. 点击切片器中的某一个字段项即可完成筛选了，若要筛选多个字段项，可以先选中多选按钮，就可以多选了，或者没有选中 多选按钮，安住ctrl键也可以多选
4. 一个切片器是可以控制多个数据透视表的，操作是，两个或多各数据透视表的数据源必须一致，然后在数据透视表上右键选择报表连接，-勾选要连接的数据透视表确定。这样在一个切片器中筛选，两个或多个连接了的数据透视表都会起作用
5. 把鼠标指针放在边缘变成四向箭头的时候拖动，就可以移动切片器了
6. 可以在选项选项卡中，设置筛选字段的列数，也可以设置选项按钮的大小
7. 选中切片器可以在四角拖动改变切片器的大小
8. 在切片器上右键，可以排序，也可以在切片器设置里设置排序，还可以在选项选项卡里套用样式
9. 在切片器设置里，去掉删除数据源选项的勾，可以在切片器上不显示已经删除的项
10. 切片器设置里的其他设置，可以修改切片器的名称，设置是否显示页眉等

第十节课-项目组合1

1. 分析选项卡-组合组-分组选择:它的作用是把不同类的项组合到一起(右键-组合也是此功能)
2. 手动组合方法: 在数据透视表上,把需要组合到一起的项移动到一起,然后选中,右键-组合.即为他们添加了一个父级字段, 即把他们都组合到一起了
3. 不使用组合功能, 也能实现把不同类的项组合到一起, 方法是: 在数据源中添加辅助列, 把需要组合到一起的项都在新列中对应相同的名称,  那再用新数据源插入数据透视表,把新列字段拖到行标签,  就是组合的效果了

第十一节课-项目组合2

1. 对于数值型字段，可以使用手动组合，也可以使用自动组合
2. 步骤：选中此字段的一个单元格，右键-组合，打开组合对话框，设置规律，即起始于、终止于 和步骤，告诉excel了规律，excel就会按此规律自动分组了
3. 因为文本字段的分组无规律可循，所以只能使用手动分组
4. 若数值字段的分组要求无规律，也不能使用自动分组
5. 对于数值型字段也是可以在数据源中添加辅助列的方式来完成分组的

第十二节课-项目分组3

1. 日期可以按年月日时分秒来分组
2. 步骤：在日期字段上右键-组合，选择相应的分组要求（年月日时分秒）
3. 只有数据源中的日期是规范日期，创建的数据透视表才能进行自动的分组
4. Excel不能识别的分组要求，可以在数据源中添加辅助列的方法来分组（见视频中例子）
5. 总结：文本字段只能用手动分组

      数值字段可以使用手动也可以使用自动分组

      日期字段可以使用手动也可以使用自动分组

      以上分组效果都能在数据源中添加辅助列完成分组（不使用分组功能）

      自动分组无法完成的，也能在数据源中添加辅助列完成分组

第十三节课-值的汇总方式和显示方式

1. 对于拖到值区域里的字段，如果是数值，默认是求和汇总，我们也可以修改成过计数，平均数，最大值，最小值，等
2. 值的显示方式：见表

序号	选项	功能描述
1	无计算	数据区域字段显示为数据透视表中的原始数据
2	全部汇总百分比	数据区域字段分别显示为每个数据项占该列和行所有项总和的百分比
3	列汇总的百分比	数据区域字段显示为每个数据项占该列所有项总和的百分比
4	行汇总的百分比	数据区域字段显示为每个数据项占该行所有项总和的百分比
5	百分比	数据区域显示为基本字段和基本项的百分比
6	父行汇总的百分比	数据区域字段显示为每个数据项占该列父级项总和的百分比
7	父列汇总的百分比	数据区域字段显示为每个数据项占该行父级项总和的百分比
8	父级汇总的百分比	数据区域字段分别显示为每个数据项占该列和行父级项总和的百分比
9	差异	数据区域字段与指定的基本字段和基本项的差值
10	差异百分比	数据区域字段显示为与基本字段项的差异百分比
11	按某一字段汇总	数据区域字段显示为基本字段项的汇总
12	按某一字段汇总的百分比	数据区域字段显示为基本字段项的汇总百分比
13	升序排列	数据区域字段显示为按升序排列的序号
14	降序排列	数据区域字段显示为按降序排列的序号
15	指数	使用公式:(单元格的值)×(总体汇总之和)(行汇总)×(列汇总)

第十四节课-计算字段

1. 计算字段是字段与字段相计算，得到新字段
2. 步骤：设计选项卡-计算组-字段、项目和集-计算字段-命名名称-把需要计算的字段插入到公式中
3. 计算字段可以是字段与字段相计算，也可以字段和常量相计算，也可以使用公式
4. 但是公式使用有一定的局限性，基本上只能使用IF,NOT,COUNT,SUM,TEXT等函数
5. 在计算字段对话框中，点开名称框的下拉选项，选中添加的字段就可以直接修改了，选中后，点击删除，即可删除
6. 如果数据源中有单价或者比值这样的字段，切勿拖到值区域或进行计算字段，因为数据透视表会把单价也进行分类汇总，这显然是不对的

第十五节课-计算项

1. 计算项和计算字段的区别是前者是把同一字段中的两个项计算，后是多个字段相计算
2. 步骤：设计选项卡-字段、项目和集-计算项-命名名称-把需要计算的字段中的项插入到公式中
3. 行标签里的字段可以计算项，列标签里的字段也是可以的
4. 修改和删除计算项与修改删除字段类似：在名称框下拉选项中选择已经添加的项-修改或点击删除
5. 如果有多个计算项，可以在设计选项卡-字段、项目和集-求解次序中调整先后顺序，此对话框也可以删除项
6. 列出公式：即把所有的计算字段和计算项都罗列出来，方便查看

第十六节课-创建动态数据透视表

1. 基于数据源是列表创建的数据透视表就是动态数据透视表
2. 除了给数据源套用表格样式来转换成列表，还可以在插入选项卡-插入表格来转换
3. 数据透视表如果引用的数据源是一个动态的名称的话，那也是动态数据透视表
4. 具体操作：创建一个名称，名称的区域使用offset函数

=offset(A1,0,0,COUNTA(A:A),COUNTA(1:1)) ，具体函数请观看课程

第十七节课-创建复合范围的数据透视表1

1、按alt  d   p ,打开创建数据透视表或数据透视图的向导，第一步选择多重合并计算数据区域，和数据透视表

2、选择单页字段：即在创建完成后数据透视表的筛选区域只能显示成项1 ，项2 等

3、把多个数据源区域添加到向导中

4、选择数据透视表显示位置 完成

5、此类数据透视表特点：

   A 每一个数据透视表的结构都必须一致

   B 数据源最左侧列的字段会被添加到行标签中

   C 其他列的字段都会被添加到值区域中（若字段既有文本字段又有数值字段，默认是计数）

   D 每一个工作表会作为筛选项里的项1项2等

1. 自定义页字段：即可以把每一个添加的数据源命名一个名称，显示在筛选项中
2. 同一级的只有一级的 页字段数目选择1
3. 最后需要在列标签筛选出发票金额，其他的都不要，且值汇总方式改为求和。

第十八节课-创建复合范围的数据透视表2

1. 当有多个数据源时，在多表合并计算数据区域数据透视表时，要根据级数选择合适的页字段数目 （请观看课程讲解）

第十九节课-创建复合范围的数据透视表3

1. 创建多条件显示的形式，可以先在数据源中把需要显示的多个字段连接成一个，再创建复合范围的数据透视表
2. 创建动态的复合范围数据透视表，同样是引用列表形式的数据源，或者是动态名称，注意在引用的时候直接输入列表名称，或动态名称即可，不需要框选数据区域