Excel数据透视表教程：查询数据间的差异-天涯论坛

5ep9lzv 发表于 2024-10-1 17:42:38

Excel数据透视表教程：查询数据间的差异

数据透视表绝对是Excel的一大利器，用好它能够让你分分钟完成繁杂的工作。本例便是利用数据透视表查询两组繁杂数据间最细微的差异，堪叫作“明察秋毫的鹰眼”，可供财务对账、统计查询差异参考。
       例：某机构9月30日和10月15日累计收到衬衫订单如下图（原始数据成千上万行）：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBAVSE4J1N164gGvpibBqzm1wwviaGicQSxbLYMmMOk2WaeJMgMk7tBtgqQ/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
            图1 原始数据
两个时点的数据结构都是同样的，均有3个项目和2项数据，此刻需求按一样的数据结构列出前后的差异以供后续工作的开展。
在保准所有项目数据的格式对应相同的前提下，操作过程如下：
       1、单击“0930”工作表名，按住Ctrl后水平向右移动，就可复制一张表，改名为“差异”，置于最末：

<img src="http://mmbiz.qpic.cn/mmbiz_png/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBCnFgRZHHHOg5qEZMbTn1R98YXFwqVYP3VWI3l6jqMcgHm2l7ia5G0ow/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
              图2 复制工作表
2、将“差异表”中的“数量”改为“0930数量”、“金额”改为“0930金额”。
       3、拖选第2张表“1015”中的A～E列，并复制，粘到差异表I列（与第1组数据空数据项数+1=3列），并将“数量”改为“1015数量”、“金额”改为“10150金额”：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBUBFHy7g1zjghtecYibcdgE4ooZ9ZS4YgR7iaOvLDdzROVq1oD1MBoc7Q/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                                            图3 数据准备1
4、在第2组数据值项前（L列前）插进与数据项数相等（此处为2）的空列，这一步极为关键，目的在于经数据透视表汇总时不会与第1组数据混在一块，但相同项的数据会合计为一行：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBcMBReZzvlzyaiaEhqMpPayTGiadx5K8OkC0Y5vLaiaNdcGWn3QxHHUiaTw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                                          图4 数据准备2
5、将第2组数据中除标题外的所有数据剪切（单击数据的最左上角单元格，运用Ctrl+Shift+光标向下，再向右、向右选取完所有数据）拼接至第1组数据下一行，不要有空行，并将第2组数据中的数据项标题拼在第1组数据后边，使两组数据形成一个整体，但各数据项都是掰开列的。再清除原来第2组数据的项目的题。为有效展示，下图隐匿了若干中间行：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBgibrdnWq5gVWcmTic6GsRWc4GrcPial8VBIgcoJMCDkp6pyPZ0Jv9YAFA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                              图5 数据准备3
       6、选中合并后的数据区中的任意一单元格，在本表的J1（或第1行的任意空白单元格）插进数据透视表：
<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBmotdQPoQ0SYQHszLD3H2oAyImuqSicC9JF6RlVsUv2tpKRuribbnOkaA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                                   图6 插进数据透视表
7、将所有项目名拖动进透视表的“行”框、将所有数据项拖进透视表的“Σ值”框中：
<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBkGrvPgVbqYezWzF5Reeyic5iaGKwTUaZ9sfoOVM1JvW3vnia5py1PGflw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                                    图7 添加透视表字段
       8、点击“Σ值”框中的每一个项目，点“值字段设置”，将汇总方式由“计数项”改为“求和项”：
<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBSxutGO0He8LbIJK7SgSxpOviaBp46XvewQmxEx9Yib2OTOxtwS1bJ0TA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">        <img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBxAY9c9AT9RIxLnPicC3t1eIgMOVTrGhGZYwlzMEPuHPuGBkwOlGbwrQ/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                             图8 透视汇总方式调节
       9、单击透视表的任意一单元格，在新境的“数据透视表工具”菜单的“分析”标签里点 “选项”命令：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBYHU4qlfiaRT6h3B8TnFicia5QnwgWOkMXiaQmc4DMibuSudhJjo7wVwKOvw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                                      图9 调节透视设置1
在弹出的界面里点“表示”标签项，勾选“经典数据透视表布局（启用网络格中的字段拖放）”并确定：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJB8cZ8WxicWfyMPAzNJ1Ikeibc4FRiapBcrgOeia6ibG6HHeld1XicbOlT6pqw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
              图10 调节透视设置2
       十、再点“数据透视表工具”菜单中的“设计”-“报表布局”-“重复所有项目的签”：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBhCpWYEvOL3LHqCLsmzRLgcOTXgmyQN7HNfRnbxdT9z0AFo7xqyYxog/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                               图11 调节透视设置3
       完成后的效果见图12。
       11、逐一右击所有项目的任意单元格，去掉“归类汇总…”前的勾：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBvKfsW9uTVmibzVQuAuhVajPvkkNfPf5sfQstbCp0NncaibH3upbs9Rww/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
图12 调节透视设置4
       完成后的数据透视表效果图：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBiawia4xv8Oa2VbcCEKXmAGf9UZOmtpLXhQ0o44hcB8Sbscn3oHE292iag/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                             图13 调节透视设置后的效果
       12、选取所有透视表列，复制所有数据并原位置选取性粘贴数值，按Ctrl+H将“求和项：”替换为空，最后删除透视表的数据源、删除透视的最后一行“总计”：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBpX3y2BZTjbQGMocHVCicPAuSccN3Csmp2qOhkBnaTtSbjpictrYq9ibpw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                   图14 整理后的数据
13、在标题末续两列，标题名为“数量差”和“金额差”，在数量差下首个单元格写入公式：用1015数量减0924数量，即在H3中写入：=F3-D3，并回车；再选中H3，鼠标右移至右下角直至鼠标变为细黑十字向右拖动复制到金额差中；最后连选H3:I3，一样鼠标右移至右下角直至鼠标变为细黑十字后双击，即自动完成向下填充公式。有多项数据的，以此类推：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBA6o063nFBeDzjbY92YsNj0uC85jF8aXoCibJK3sR3Lj2GhGMC4368fg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                                     图15 计算差异
14、选取所有标题，加上自动筛选，筛选数量差和金额差均为“0”的行并选取筛选出的所有行号（点第1个筛选出的行，而后按Ctrl+Shift+光标向下键可一次选完）右键删除：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBHUFFaQXGzOGsqsh8KDFwBQgnY2DNdssOPiaq6WfLwrUbn0KHQDKBLiaw/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                                    图16 删除无差异行
       15、最后取消自动筛选，差异结果就呈此刻了你的眼前：

<img src="http://mmbiz.qpic.cn/mmbiz_jpg/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBwKh9llY86Agh2QCMERlhJI9fOPRsFzxhzN9ooAnpP5iapkSFEiblLeUA/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
                                    图17 差异结果
本办法看似过程繁多，实质操作起来几分钟就完成了，况且差异项级别随心所欲一次实现，特别有“万军从中取‘差异首级’如探囊取物”般容易的感觉。
　　教程配套的Excel源文件在QQ群：488925627共享下载。
<img src="http://mmbiz.qpic.cn/mmbiz_png/k6kCsib3eJMwvop3J0fvUibVSc55d6NJJBWzqZMrvZMnLam0Segur7Lt1TGfFSOnIJN07yEHVnttYs2cWOR7UaUA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">
　　新一期《Excel极速贯通班》11月4日开班，需要全面系统学习的小伙伴，加老师QQ：603830039咨询报名。
<img src="http://mmbiz.qpic.cn/mmbiz/k6kCsib3eJMwpWOV8GqBlibDC5FTOu6icVQTy18nibI8WSqy7ugJV4K6K0dPERArlgYIIcedF4oywbd8dJJpm5fIKg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">

7wu1wm0 发表于 2024-10-20 01:44:00

外链论坛的成功举办，是与各位领导、同仁们的关怀和支持分不开的。在此，我谨代表公司向关心和支持论坛的各界人士表示最衷心的感谢！

nykek5i 发表于 2024-11-5 11:06:17

seo常来的论坛，希望我的网站快点收录。

1fy07h 发表于 2024-11-6 12:42:12

我赞同你的看法，你的智慧让人佩服，谢谢分享。

4lqedz 发表于 2024-11-10 17:03:22

你的话语如春风拂面，温暖了我的心房，真的很感谢。

页: [1]

天涯论坛's Archiver

Excel数据透视表教程：查询数据间的差异