创建数据透视表的源数据的数据格式所需要考虑的规则
- 确保每一列都有一个单元格标题,并且标题是唯一的,两列中不能使用同一标题;行方向不要出现标题或字段;这样形成每一行是列字段,其它行是记录;
- 如果一列中包含数字数据,在列中不允许使用空单元格。使用0替代空白;
- 不能使用空行或空列;
- 不要包含汇总行;
- 不要有合并格;
高级筛选
- 如果你正在使用条件,从数据集中将一个或多个标题复制到工作表的空白部分。在每个标题下面,列出想要包含的值;
- 如果你正在使用一个输出区域,并且想对列重新排序或是包含这些列的一个子集,那么在工作表的空白部分,以合适的顺序复制标题。如果想让所有原来的列保持它们原来的顺序,那么输出区域是任意空白单元格;
数据透视表的局限性
项目 |
旧版限制 |
Excel 2007 |
允许的行数 |
64000 |
1000000 |
允许的列数(或字段数) |
256 |
16000 |
- 使用数据透视表传统模式:菜单(选项)→选项卡(数据透视表)→选项→选项卡(显示)→经典数据透视表;
- 不显示分类汇总:数据透视表工具→设计→分类汇总→不显示分类汇总;
- 使用公式向数据透视表添加字段:数据透视表工具→选项→工具→公式→计算字段:结存=收入-发出;
- 以百分比代替数值显示(即应用数字格式):数据透视表工具→选项→活动字段→字段设置→值显示方式→数字格式;
- 报表筛选显示多个页字段:页字段下拉箭头→选择多项;
- 分布显示:报表筛选→选项→选项→显示报表筛选页→选择需要分页的页字段→确定;
- 分类汇总的显示:在组的顶部或底部显示分类汇总有不同的效果;
- 设计→数据透视表样式选项→镶边行;
- 更新时保留单元格样式:刷新时会变化单元格样式,可通过以下方式操作:选项→选择→整个数据透视表→格式化;(数据透视表选项→勾选:更新时保留单元格样式)
- 避免更新时自动调整列宽:数据透视表选项→取消勾选:更新时自动调整列宽;
- 打开文档时自动刷新数据透视表:数据透视表选项→数据→勾选:打开文档时刷新数据;
- 刷新工作簿中的全部工作表:选项→刷新→全部刷新;
- 自动刷新基于外部数据源的透视表:选项→更新数据源→连接属性;
- 打开数据表时所有工作表自动刷新的VBA:
Private Sub Worksheet_Activate()
activateSheet.PivotTables("数据透视表名称").PivotCache.Refresh
End Sub
- 保留从数据源删除的项目:数据透视表选项→数据→-;
- 同一字段也能应用多种汇总方式;
- 移动项目:右击→移动...;
- 对同一字段实施多重筛选:数据透视表选项→汇总和筛选→每个字段允许多重筛选;
- 数据透视表的字段名可以更改;
- 将所选内容分组,如年龄:年龄做为行字段,身份证号码或姓名作为值;→数据透视表选项的选项卡→定位到年龄的某一单元格→将所选内容分组→起始,结束,步长;(分组的字段必须是数值字段)
- 值显示方式:值字段单击→值字段设置→值显示方式→行汇总百分比:显示值点行汇总的百分比值;
- 按月统计:日期做为行字段→选择日期列的某一单元格→数据透视表选项的选项卡→将所选内容分组→月;
- 字段设置:数据透视表工具→选项→活动字段选项组→不同列→字段设置;
- ctrl+shift+*:选择整个数据透视表
- 数据透视表汇总方式默认为“计数”,是因为在数据源的列中存在任何文本值或空白单元格;
- 数据透视表中的数据无法直接引用:出现GETPIVOTDATA字符,原因是数据透视表函数有默认状态下是打开的,解决的办法是:数据透视表工具→选项→选项下拉箭头→取消勾选:生成GETPIVOTDATA;