创建数据透视表的源数据的数据格式所需要考虑的规则

  1. 确保每一列都有一个单元格标题,并且标题是唯一的,两列中不能使用同一标题;行方向不要出现标题或字段;这样形成每一行是列字段,其它行是记录;
  2. 如果一列中包含数字数据,在列中不允许使用空单元格。使用0替代空白;
  3. 不能使用空行或空列;
  4. 不要包含汇总行;
  5. 不要有合并格;

高级筛选

  1. 如果你正在使用条件,从数据集中将一个或多个标题复制到工作表的空白部分。在每个标题下面,列出想要包含的值;
  2. 如果你正在使用一个输出区域,并且想对列重新排序或是包含这些列的一个子集,那么在工作表的空白部分,以合适的顺序复制标题。如果想让所有原来的列保持它们原来的顺序,那么输出区域是任意空白单元格;

数据透视表的局限性

项目 旧版限制 Excel 2007
允许的行数 64000 1000000
允许的列数(或字段数) 256 16000
  1. 使用数据透视表传统模式:菜单(选项)→选项卡(数据透视表)→选项→选项卡(显示)→经典数据透视表;
  2. 不显示分类汇总:数据透视表工具→设计→分类汇总→不显示分类汇总;
  3. 使用公式向数据透视表添加字段:数据透视表工具→选项→工具→公式→计算字段:结存=收入-发出;
  4. 以百分比代替数值显示(即应用数字格式):数据透视表工具→选项→活动字段→字段设置→值显示方式→数字格式;
  5. 报表筛选显示多个页字段:页字段下拉箭头→选择多项;
  6. 分布显示:报表筛选→选项→选项→显示报表筛选页→选择需要分页的页字段→确定;
  7. 分类汇总的显示:在组的顶部或底部显示分类汇总有不同的效果;
  8. 设计→数据透视表样式选项→镶边行;
  9. 更新时保留单元格样式:刷新时会变化单元格样式,可通过以下方式操作:选项→选择→整个数据透视表→格式化;(数据透视表选项→勾选:更新时保留单元格样式)
  10. 避免更新时自动调整列宽:数据透视表选项→取消勾选:更新时自动调整列宽;
  11. 打开文档时自动刷新数据透视表:数据透视表选项→数据→勾选:打开文档时刷新数据;
  12. 刷新工作簿中的全部工作表:选项→刷新→全部刷新;
  13. 自动刷新基于外部数据源的透视表:选项→更新数据源→连接属性;
  14. 打开数据表时所有工作表自动刷新的VBA:
    Private Sub Worksheet_Activate()
    activateSheet.PivotTables("数据透视表名称").PivotCache.Refresh
    End Sub
  15. 保留从数据源删除的项目:数据透视表选项→数据→-;
  16. 同一字段也能应用多种汇总方式;
  17. 移动项目:右击→移动...;
  18. 对同一字段实施多重筛选:数据透视表选项→汇总和筛选→每个字段允许多重筛选;
  19. 数据透视表的字段名可以更改;
  20. 将所选内容分组,如年龄:年龄做为行字段,身份证号码或姓名作为值;→数据透视表选项的选项卡→定位到年龄的某一单元格→将所选内容分组→起始,结束,步长;(分组的字段必须是数值字段)
  21. 值显示方式:值字段单击→值字段设置→值显示方式→行汇总百分比:显示值点行汇总的百分比值;
  22. 按月统计:日期做为行字段→选择日期列的某一单元格→数据透视表选项的选项卡→将所选内容分组→月;
  23. 字段设置:数据透视表工具→选项→活动字段选项组→不同列→字段设置;
  24. ctrl+shift+*:选择整个数据透视表
  25. 数据透视表汇总方式默认为“计数”,是因为在数据源的列中存在任何文本值或空白单元格;
  26. 数据透视表中的数据无法直接引用:出现GETPIVOTDATA字符,原因是数据透视表函数有默认状态下是打开的,解决的办法是:数据透视表工具→选项→选项下拉箭头→取消勾选:生成GETPIVOTDATA;