数据透视表是个宝藏工具,其性能和用途比大部分人所知的要广得多。
比如可以直接在数据透视表中设置和修改计算字段公式,达到计算目的,而且计算结果也是数据透视表的一部分,同样可以通过数据透视表进行分析。
案例:
下图 1 是某公司客户的出入账记录表,希望能够方便地按日期、按人计算净入账金额,还能根据需要随时调整分析需求。
效果如图 2、3。
解决方案:
1. 选中表格区域的任意单元格 --> 选择菜单栏的“插入”-->“数据透视表”创建数据透视表;为了方便查看,本例将数据透视表创建在现有工作表中。
2. 在右侧的“数据透视表字段”区域,将“日期”和“客户”拖入行区域,“进账”和“出账”拖入值区域
3. 选中数据透视表的任意区域,选择菜单栏的“分析”-->“字段、项目和集”-->“计算字段”
会出现如下“插入计算字段”对话框。
4. 进行如下设置:
- 名称:输入“净入账”
- 公式:点击“=”后面的位置 --> 点击“进账”--> 点击“插入字段”按钮
5. 在“公式”区域的“进账”后输入“-”号 --> 点击“出账”--> 点击“插入字段”按钮 --> 点击“确定”
6. 现在数据透视表区域就多出了一列“净入账”的计算字段,其结果为进账减去出账的差额。
7. 修改值区域的数据标题:删除多余的“Sum of”,在需要显示的文字前加一个空格即可实现
关于具体操作步骤及原理,可以参见【】。
8. 调整负数显示:选中 I 列 --> 按 Ctrl+1,在弹出的“设置单元格格式”对话框中选择“数字”选项卡 -->“数值”,进行以下设置 --> 点击“确定”:
- 小数位数:选择“0”
- 勾选“使用千分位符”复选框
- 负数:选择红色带括号的样式
9. 选中 G 和 H 列,通过菜单栏的千分位符和小数位数选项,给这两列数字也加上千分位符,从而使所有数据列格式保持一致。
现在就实现了案例需求,可以在“数据透视表字段”区域通过拖动字段,方便地从不同角度进行数据分析。
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。