将二维表进行汇总分析,用数据透视表无疑非常方便,不仅如此,数据透视表还可以做各种美化修饰。但是如果今后还需要对结果使用公式、做匹配等,还是要转换成区域比较方便。
既想享受数据透视表的便利,又想沿用数据表的规范化,如何两全?那就先做数据透视表,再复制出一个完全一样的数据表咯。
案例:
下图 1 是各部门销售人员一季度的指标和销售额表,请制作数据透视表,统计每个销售的完成率。然后将数据透视表复制成数据表以便分析,数据表的样式要与数据透视表完全一样。
哪个是数据透视表?哪个是区域?傻傻分不清楚。
效果如下图 2 所示。
解决方案:
1. 选中数据表的任意单元格 --> 选择菜单栏的“插入”-->“数据透视表”
2. 选择“现有工作表”及所需放置的位置 --> 点击“确定”
3. 在右侧的“数据透视表字段”区域,将“姓名”拖动到“行”区域,“指标”和“销售额”拖动到“值”区域。
4. 将行标签名修改为“姓名”
5. 双击 G1 单元格,将名称修改为“ 指标”--> 点击“确定”
* 请注意:“指标”前面要加个空格,否则会因为和现有字段重名而报错。
6. 用同样的方式修改 H 列的名称。
7. 将数据透视表的标题行设置为居中。
8. 选中 G 列的任意单元格 --> 右键单击 --> 在弹出的菜单中选择“数字格式”
9. 在弹出的对话框中选择“数值”--> 按以下方式设置 --> 点击“确定”:
- 小数位数:0
- 勾选“使用千分位分隔符”
10. 用同样的方式修改 H 列的格式。
11. 选中数据透视表的任意单元格 --> 选择菜单栏的“分析”-->“字段、项目和集”-->“计算字段”
12. 在公式区域通过“插入字段”按钮设置公式“=销售额/指标”--> 点击“确定”
13. 将 I 列的标题修改为“完成率”。
14. 选中 I 列的任意单元格 --> 右键单击 --> 选择“数字格式”
15. 将格式修改为百分比 --> 点击“确定”
16. 选中数据透视表的任意单元格 --> 选择菜单栏的“设计”--> 选择一种喜欢的样式
17. 选中 I 列的任意值单元格 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”
18. 在弹出的对话框中按以下方式设置 --> 点击“确定”:
- 规则应用于:选择“所有显示“完成率”值的单元格”
- 格式样式:选择“图标集”
- 图标样式:选择喜欢的样式
- 规则区域:将所有类型都设置为“数字”,根据好、中、差的标准设置三种颜色对应的值
接下来就将数据透视表复制粘贴为样式一样的区域。
19. 选中数据透视表区域 --> 按 Ctrl+C --> 选中目标区域 --> 选择菜单栏的“首页”-->“粘贴”-->“值”
20. 保持选中粘贴区域 --> 选择菜单栏的“开始”-->“粘贴”-->“选择性粘贴”
21. 在弹出的对话框中选择“格式”--> 点击“确定”
22. 再一次选择菜单栏的“开始”-->“粘贴”-->“选择性粘贴”--> 在弹出的对话框中选择“列宽”--> 点击“确定”
一个跟透视表长得一模一样的数据表就复刻好了。
也可以用下面这种方法。
23. 选中数据透视表 --> 复制、粘贴为值
24. 选中数据透视表区域 --> 选择菜单栏的“开始”-->“格式刷”
25. 刷新一下刚才复制的值区域
26. 再次复制数据透视表 --> 选中目标区域 --> 选择菜单栏的“开始”-->“粘贴”-->“选择性粘贴”--> 在弹出的对话框中选择“列宽”--> 点击“确定”
也能复制出一个样式完全一致的数据表。
很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。
现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。