玩转透视表，让工作更甜蜜

第4章　玩转透视表，工作的滋味甜过初恋

源数据表是Excel的灵魂，可是，如果只有灵魂，不就成鬼了？灵魂只有借助肉身，才能形成生命；有生命，才有意义。对管理来说，无论源数据表做得多好，它都是一张无法“读”的表。要读懂它，就要把它变换成各种各样的具体形态，这就是分类汇总表。这让我联想起小时候玩的一种智力拼图游戏——七巧板。

仅仅7块不同形状的板子，据说可以拼凑成多达1600多种的图案。

在Excel里，也有七巧板式的精彩。我们的源数据犹如散落的板子，当把不同的字段按照一定的规则组合以后，数据就会展现出各种具体形态，从而表达特定的含义。所不同的是，玩好真正的七巧板，需要有丰富的想象力和很强的动手能力，是“做”出来的。而组合源数据，只需要一拖一拽就能“变”出来。

第3章讲三表概念的时候，我是用函数做的分类汇总表，但事实上，大多数汇总表都不用“做”。

让我们告别“做”表的日子，像魔术师一样，“变”出精彩。在愉快的变化中，更加能够体会天下第一表的内涵。而这个魔术所依赖的“法宝”，就是——数据透视表。

第1节　数据透视表初体验

如果要评选出最具代表性的Excel功能，我会举双手投给数据透视表。原因有二：首先，我们在前面反复提到的Excel工作目的和工作的重要意义，都与分类汇总表有关，而数据透视表正是搞定分类汇总表的专家；其次，我们一直强调简单、实用，不玩弄高深的技巧就能解决复杂的问题，数据透视表正是这么一个傻瓜工具，拖拽之间，变化万千，使用起来酣畅淋漓，欲罢不能。

你也许曾经用过数据透视表，却并未觉得好用，那是因为缺了一门心法。要记得，天下第一表的设计是心法，数据透视表的运用是招式，要成为“武林高手”，两者缺一不可。

数据透视表在哪里

在Exce12003版本里，数据透视表的全称是“数据透视表和数据透视图”，它乖乖地待在“数据”菜单里听候差遣（见图4-2）；在2007版本里，“数据透视表”与“数据透视图”在第一级选项中就被区分开来，但是我们在“插入”选项卡中看到的功能名称，仍然为使用频率更高的“数据透视表”（见图4-3）。

数据透视表能做什么

数据透视表，顾名思义，就是把数据看透了，用成都话讲就是“把你看白了”。通常，当我们掌握着别人的家门钥匙、银行卡密码、初恋情书时，就可以对他说：我把你看透了。这等于告诉对方，你已经没有什么好隐瞒的，你所有的一切我都知道了。数据透视表的作用，就是帮助我们看透数据背后的意义，洞悉管理的真相。

官方对此的解释简单到位：使用数据透视表可轻松排列和汇总复杂数据，并可进一步查看详细情况。再换个角度讲，它是自动生成分类汇总表的工具，可以根据源数据表的数据内容及分类，按任意角度、任意多层级、不同的汇总方式，得到不同的汇总结果。

例如：有一份源数据表（见图4-4），记录了全国各省、各市、各乡镇所有企业的行业类型、年末从业人员数、营业状态等信息。

源数据表数据量庞大，单行数据的属性也非常多，如果要“做”分类汇总表，想想都累得慌。可是通过数据透视表，仅仅一分钟，就能得到各省年末从业人员汇总表（见图4-5）、各省/市年末从业人员汇总表（见图4-6），以及各省/市/乡镇年末从业人员汇总表（见图4-7）三份汇总表。

数据透视表怎么做

轻松选中，调用功能

要调用数据透视表功能，首先要有源数据表。如果是一份正确的源数据表，只需选中数据区域任意单元格，点击“数据”→“数据透视表和数据透视图”即可。

设置参数，一步完成

此时会出现设置向导。看界面提示，需要三个步骤才能完成设置，可事实上，如果你有一份正确、完整的源数据表，直接点击“完成”即可。

“懒人”的宗旨永远是能省则省，多点一个按钮的事咱都不做。可要成为合格的“懒人”，必须先做有心人，所谓有因才有果嘛。

首先还是先了解一下，基本的三个步骤都需要做些什么？

第一步：确认数据来源和待创建的报表类型。我们的数据是从Excel源数据表来的，所以使用默认选项“MicrosoftOfficeExcel数据列表或数据库”；报表类型是分类汇总表而不是图表，所以使用默认选项“数据透视表”。

第二步：确认选定的数据区域。只要源数据表符合设计规范，Excel就能自动识别数据区域。在调用数据透视表功能时，它能判断与被选中单元格四个方向相邻并连续的单元格为同一数据区域，而不需要我们在调用之前，手工选中这些数据。这也正是源数据表中为什么不能出现手工合计行和分隔列的重要原因之一。当然，如果要自定义数据源区域，可以点击“浏览”进行设置。但有一点要注意，无论选择的数据多与少，标题行都必须被包含在内。

第三步：确认数据透视表的显示位置。前面一直强调，切忌破坏源数据表的完整性，不能在源数据表中做过多操作，也不要存放与源数据无关的其他数据。基于此，数据透视表的显示位置应该在“新建工作表”中，依然是默认选项。

进行到第三步并点击“完成”后，可能会出现“字段名无效”的错误提示（见图4-13）。这是由于选定的数据区域中，有某列数据的标题字段为空所导致的（见 图4-14）。可见，当源数据表标题行有缺失时，Excel最强大的功能也随之失效。还等什么，把缺失的标题字段补上吧！

一般来说，对于标准的源数据表，以上三步设置均可采用默认选项。所以，做好了前期工作，在调用数据透视表功能时，可以跳过设置阶段，一步“完成”（见图4-15）。如果你嫌鼠标点得慢，点不准，那么请记住快捷方式：Alt+D→P→F。更妙的是，尽管数据透视表在2007版本里的调用路径不同，也依然可以使用同样的快捷键进行数据透视表的调用。版本的差异在“懒人”眼里，不过是浮云一片。不断发掘省力又省时的好方法，是“懒人”的本能。

认识界面，熟悉“车间”

设置完成后，会进入“变”表界面，并出现“变”表工具。先来认识一下它们吧！

字段列表：源数据表的字段库，犹如七巧板的七块板子，不同的是，这里的板子可多可少。

报表区域：制造分类汇总表的生产车间，零部件在这里被组装成产品。

工具栏：提供更多个性化设置及数据刷新。

在制造产品之前，还要熟悉一下生产车间。

页字段：可以理解为汇总表的总表头，当页字段为月份时，汇总表显示为某月或某几个月的数据分类汇总情况；当页字段为产品种类时，汇总表显示为某种或者某几种产品的数据分类汇总情况。同时，设置页字段也是汇总表分页显示的前提条件。

列字段：汇总表显示在不同列上的字段，通俗地说，就是表上面的汇总字段。

行字段：汇总表显示在不同行上的字段，通俗地说，就是左边表的汇总字段。

数据项：待汇总的字段。

以图4-18为例，这是一张按照营业状态，汇总不同行业、不同省份年末从业人员数的 分类汇总表。

“营业状态”为页字段，所以该表反映了在“停业”状态下的汇总数据；

“所在省份”为列字段，所以各省份按列一字排开，在列上面按省份进行汇总；

“行业”为行字段，所以各行业按行一字排开，在行上面按行业进行汇总；

“年末从业人员数”为数据项，汇总方式默认为求和，所以字段的行/列交叉点显示为某省某行业的年末从业人员总数。

拖拽之间，“变”化万千

认识了透视表的界面，马上来看看表是如何“变”出来的。每当进入数据透视表拖拽阶段时，就代表我们的工作进入尾声，之前的辛勤劳动就要看到成果了。我最享受的正是这个时刻：喝着咖啡，轻松惬意地“变”出各类汇总表。

在数据透视表里“变”表，只用鼠标，不用键盘，所有的操作仅仅是把字段拖进去或者拽出来。在“生产车间”里，Excel会自动分析并组装数据，然后显示正为确的汇总表样式，以及准确的汇总结果。

比如“变”一张统计各省/市/乡镇年末从业人员总数的三级汇总表（见图4-19），总共只需以下四步鼠标操作。

第一步：将字段列表里的“所在省份”拖为行字段，拖动时，鼠标下方的微型视会图帮助我们确定字段是否进入了正确的区域；

第二步：将字段列表里的“所在地”拖为行字段，“位于所在省份”右侧；

第三步：将字段列表里的“所在地区”拖为行字段，位于“所在地”右侧；

第四步：将字段列表里的“年末从业人员数”拖入数据项，即完成了这张三级汇总表。由于数据透视表默认的汇总方式是求和，与该表的汇总要求一致，所以不用另行置设。

如果不喜欢汇总表行间的汇总项，可以把它们隐藏起来。只要选中其中一个汇总项，调出右键菜单并选择“隐藏”就可以了。对于同一字段，一次操作可以隐藏该字段所有的汇总项。

我们也可以随时删除汇总表的任何字段，添加用拖，删除用拽。只需将“生产车间”里不再需要的零件拽到大门外，就能轻松搞定。微型视图会用一把大红叉提示你该何时松手。

怎么样？“变”表的感觉很好吧！如此多的字段组合，如此神奇的表格“变”化，如此便捷的操作方法。到了此时此刻，你依然选择害怕分类汇总，还是彻底爱上它呢？至少，我已经是无法自拔了。设想一下，源数据表如果有10个字段，两两组合，或三三组合，或四四组合，甚至五五组合……究竟能生成多少份分类汇总表呢？没有一千也有八百吧。所谓得天下第一表和数据透视表者得天下，拖拽之间“变”化万千，这可不是吹的。

说到这里，我又要分享一个“歪理”，供大家一笑。前面我们谈了很多Excel对于工作和企业的意义，其实对于家庭，Excel也有重要意义，家庭是否和睦，有时候与Excel还真有点儿关系。

老张和老王在同一家公司，做同样的工作，可两人的生活状态却截然不同。老张Excel玩得很溜，无论工作是否繁忙，都能轻松应对。由于他做报表又快又准，公司领导对他很信任，常常把重要的数据统计工作交给他，理所当然，他也就成为领导办公室的常客。而且老张很少加班，8个小时就能完成工作，准时回家。他每天晚上都要给儿子讲故事，陪儿子玩游戏，周末还一家人开着车四处郊游。

反观老王，兢兢业业对待工作，可由于真的不懂Excel，一份报表要做上一个星期。如果赶上做表旺季，连续一个月加班加点，每日深夜回家，这些都是家常便饭。所以领导总觉得老王能力有限，交代的事情做得很慢，效果也不好。渐渐地，他失去了领导的重视，常常一个人默默地出入办公室。

有一天，当老王凌晨一点回到家，老婆终于忍不住发火了：“你看你，每天这么晚才回家，儿子你也管不了，你知道他现在都已经习惯了没有爸爸的日子吗？我上班也很忙，还要接孩子。你辛苦我知道，可你回家倒头就睡，咱们话都说不上几句，这日子过得……唉！听说你们公司的老张年底拿了8000块的奖金，你怎么才800……（此处略去一万个字）”

一切都是Excel惹的祸。

当然，这个故事只是笑谈，可话丑理不歪，做不好工作一定会影响家庭和睦。常常不回家吃饭，没时间陪伴家人，在公司又不能挣得好表现的人，仅靠一点自嘲自愉的精神是解决不了根本问题的。学好本领，改变现状，才是应该做的事。没有必要时，就不要加班，上班时间做好本职工作，下班时间尽情享受生活。

小技巧——还我汇总项

学会了隐藏汇总项，就必须知道如何取消隐藏。多数Excel菜单功能的反向操作都在同一个位置，比如如果设置了“保护工作表”，同样位置的功能就变为“撤消工作表保护”。

数据透视表里的隐藏与取消隐藏却有所不同，当我们把所有按部门汇总的数据隐藏过后，在A列数据区域点击鼠标右键，菜单功能依然是“隐藏”，而没有变为“取消隐藏”。

如果要取消一级行字段隐藏的汇总项，则需选中一级行字段所在区域的任意单元格（即图4-27a蓝色区域），点击鼠标右键，选中“字段设置”，将分类汇总的选项从“无”改为“自动”（见图4-27b）。只要选中相应区域的单元格，用同样的方法，就可以显示其他被隐藏的汇总项。

第2节　早知如此，何必当初

第2章介绍了源数据表的十宗罪。之所以出现错误的表格设计，并非我们的主观意愿，而是在不熟悉Excel的规范和功能时，为了更好地完成工作所采取的一种补救方式。初衷其实都是好的。那么，就让我们用数据透视表，重现美好的初衷吧。

“拖”出合计行

错误设计之提前合计，初衷是得到按某字段汇总的合计行，以便“读”表者清晰地了解该字段各部分的汇总数据。该设计的错误在于，不应该在源数据表中做此操作。

根据源数据表，如果用数据透视表来重现初衷，只需把“月份”“拖”为一级行字段，“日期”作为二级行字段，“天数”作为数据项，进行求和计算，就可以得到同样的结果。

“变”出汇总表

错误设计之越俎代庖，初衷是得到分类汇总表。该设计的错误在于，不应该手工制作汇总表。

越俎代庖有两种情况，最令人揪心的是只有分类汇总表，没有源数据表（见图4-30）。这是一种无法弥补的过错，就工作本身而言，操作者前期所做的努力因此被彻底抹杀；而对于企业，没有源数据如同没有过去，而不知道过去，就无法预知未来。

根据源数据表，如果用数据透视表来重现初衷，将“月份”作为一级行字段“，类别”作为二级行字段，“姓名”作为列字段，“天数”作为数据项，进行求和计算，就可以得到同样的结果。不一样的是，这张分类汇总表来源于源数据表，并创建于新的工作表，而源数据始终安全、完整地待在它应该在的地方。

拆分源数据的“偏方”

错误设计之源数据分表记录，初衷是让表格看起来更直观，并且方便查询。该设计的错误在于，不应该将源数据分别记录，这样会造成数据难以合并，也影响了对源数据整体的查找、筛选、排序及汇总。轻者，数据被分为十几个工作表；重者，多达上百个工作表。初衷和结果，有时失之毫厘，谬之千里。

如果用数据透视表来重现初衷，则既能拥有完整的源数据表，又能将数据分页显示，而页字段在其中发挥了关键作用。但要注意一点：数据透视表分页显示的概念是将分类汇总表分页，而非将源数据分页。所以，我们需要运用一些方法，让分页显示的效果看上去是源数据被拆分。

首先，将需要分页的“月份”拖入页字段。然后，按照源数据表的顺序，将各个字段依次拖入行字段，并隐藏所有汇总项（见图4-33）。

接下来，调出数据透视表工具栏，打开“数据透视表”下拉菜单，点击“分页显示”（见图4-34）。由于只有一个页字段，所以分页显示的字段选项也只有一个。于是，选中“月份”并确定，数据就这样被Excel智能拆分（见图4-35）。

用分页显示功能拆分源数据算是“偏方”，而它最正统的用法是拆分汇总表，以便他人查 阅或者将其批量打印。例如：将全年总的请假分析表分成12页，显示为每个月的汇总表 。

这里我为大家来完成第一步，最后由你来画点睛之笔。

职场感悟——羡慕甜不如学习苦

数据透视表使用起来确实很爽，但是如果没有正确的源数据表支持，它是不能充分发挥作用的。我们与其羡慕使用数据透视表时的爽，不如多学习在这之前细致而辛苦的准备工作。

职场上很多事情也是一样。当我们看到别人小有成就时，应该多多思考和借鉴他成功的原因，看他是如何一步一步走过来的。学习别人的苦胜过羡慕别人的甜，否则我们就会活在无尽的攀比之中，却永远无法改变现状。吃不到葡萄咱不说葡萄酸，谁吃着葡萄了，咱就虚心向他请教方法去。

第3节　汇总报表，拖拽有“理”

同样是通过数据透视表“变”出来的汇总表，可读性有时却相去甚远。有的汇总表我们横看竖看都看不顺眼，怎么读也读不懂它想表达的意思；有的却清清楚楚，将数据背后的意义展现得淋漓尽致。

造成差异的根本原因，是我们在“变”表时采用了不同的字段排列顺序。可别小看它，它和我们着装的顺序一样有讲究。一个身着白衬衣、黑西装、系紫色领带的帅哥，的确是帅哥。但如果他把西裤套在头上，西服围在腰间，心白背外穿，白衬衣打底，领带系腿上，这位帅哥就会被医生带走。

分类汇总表一定要别人读得懂，看得明白，所以字段的拖拽，有一些规则需要遵循。

“躺着”不如“站着”

有的汇总表非常宽，迫使我们在阅读的时候必须从A列看到Z列，感觉很不方便，也不舒服（见图4-37）。现代人的阅读习惯不同于古代，如果你把汇总表做成清明上河图，只会让你的同事和老板无从下“眼”。

所以，在制作汇总表时，分类多的字段，应该尽量作为行字段；分类少的字段，才可以作为列字段（见图4-38）。这样比较有利于在一个页面上显示更多的数据内容，也符合一般人的阅读习惯。形象地说，我们在拖拽字段时，一定不要让汇总表“躺着”，而要让它“站起来”。根据Excel默认的数据结构，工作表中的行数比列数多出几百倍，整个工作表是姚明般的身材。我们做汇总表，当然也要与Excel的审美标准保持一致才行。

按“天”汇总要不得

虽然日期是源数据的一个属性，但通常情况下，不建议按照日进行汇总。很少有企业需要管理每一天的经营状况，并制定以天为单位的工作计划。按日汇总，无异于提供了一份无法决策的报表，也就失去了制作它的必要。

当然，有几类企业比较例外，如生产企业和快递企业。生产企业可以使用透视表得到每日各生产线各类产品的排产计划汇总表，以便合理组织生产力；快递企业可以得到每日全国各站点已派发件及滞留件的汇总情况，以此评估派送能力，并及时做出调整。

而对于一般的企业，只需按月进行统计和分析，关注一年12个月的经营趋势就足够了。这也就要求在源数据表里要有月份字段。它可以通过month函数（提取日期中的月份），由日期自动获得；也可以在填写源数据的时候手工添加。无论采用什么方法，都要保证我们最终能得到按月汇总的汇总表（见图4-40）。

其实，还有一种更牛的方法处理日期，这在后面的内容中会详细介绍。

不超过两个列字段

当汇总表的列字段超过两个，我们就无法正常理解数据的意义了。数据透视表的逻辑是，先展开第一级字段，在第一级字段的每一个分类下，展开第二级字段，以此类推。如图4-41所示，在贵州省下面，显示了贵州省的所有行业，然后在每个行业下面又显示所有的营业状态。这不仅造成一页的信息量非常少，而且让人看不明白各字段之间的关系，这类汇总表，阅读起来异常困难。

做汇总表时，两个列字段是极限，一个列字段是标准。建议大家在任何时候，都尽可能只设置一个列字段，其余想要汇总的字段，可以按顺序添加在行字段。

只要把这张表换个方向，感觉就会好很多。

汇总跟着文字走

我常听人说，数据透视表里的字段拖不好，大家似乎不知道应该先添加什么，再添加什么，添加到行字段还是列字段。的确，要制作分析角度准确的汇总表，需要操作者有很强的逻辑思维能力，而且在制表前必须想清楚什么是第一关注点，什么是第二关注点，谁是谁的从属关系，及该如何表达这种关系。其实，我们想要表达的意思往往已经到了嘴边，只要手和嘴保持一致，一份准确的汇总表就能轻松获得。

说到一致性，我想起一件事。曾经有一个朋友在写公式时遇到麻烦，他想不明白他要的结果如何用正确的公式来表达。有一天我们刚好约着吃饭，他带上了笔记本，指给我看：“我想在D这个单元格，得到A单元格和B单元格的和减去C单元格的值，我该怎么写这个公式？”我确信当时我呆住了，这个问题……

回过神来，我叫他做了一件事，让他把刚才说的话再慢慢地说一遍。于是他说：“我想在D这个单元格，得到A单元格和B单元格的和减去C单元格的值。”我告诉他：“你已经知道答案了。”然后，就没有再和他讨论这件事情。两分钟过后，他自己解决了这道“难题”。

回到本例，这张汇总表用中文应该这样翻译：求，不同省份、不同会计制度类别、不同行业在不同营业状态下的全年营业收入总和。这是我做分类汇总表时使用 的标准描述方式，句子中字段名称出现的先后顺序即添加字段的顺序。

我先说不同省份，那么省份就是一级行字段，然后是执行会计制度类别，再然后是行业。句子中间会出现一个“在”字，它后面的字段是列字段。前面我们说了，列字段最好只有一个，所以“在”字后面也只有一个字段名称。

另外还有一个“的”字，它后面的字段是数据项，也就是需要汇总的数据字段。句子最末的“总和”，说明了该汇总表的汇总方式为求和。这种方法屡试不爽，不仅能有效地 帮助我们分析字段，还确保了字段添加顺序、添加位置以及汇总方式的准确性。

我们来做一个实验：这里有一份字段列表，照上面的方法说两句话，看看能得到怎样的汇总表。

求，不同省份、不同城市、不同行业在不同机构类型的主营业务收入总和。

一级行字段：所在省份。

二级行字段：所在地。

三级行字段：行业。

一级列字段：机构类型。

数据项字段：主营业务收入。

汇总方式：求和。

求，不同企业成立月份、不同省份在不同营业状态下的年末从业人员数总和。

一级行字段：企业成立时间。

二级行字段：所在省份。

一级列字段：营业状态。

数据项字段：年末从业人员数。

汇总方式：求和。

是不是非常清晰？所以，制作汇总表，跟着文字走准没错。

字段主次要分明

在分类汇总表的结构里，行字段和列字段没有主次关系，不会影汇总响表的关注焦点。但是行字段之间却有非常明显的主次关系，它们的顺序决定了分类汇总表所传递的信息侧重点所在，也因此为企业管理提供了不同角度的决策数据。

图4-48以省份为一级行字段，重点关注不同省份的数据变化，之后才细化到每个省份不同的行业有怎样的数据表现。从功用上讲，它可以做各省份的横向评估，聚焦全国各省的企业主营业务收入对比，以此定义发达地区与欠发达地区，并由此制定地区发展规划。

如果结合营业状态综合评估，则可以确定某地区各行业的活跃性，推断出该地区经济处于快速发展期，还是衰退期，然后制定相应的经济政策，重点扶持高速发展的行业，并加大力度帮助夕阳产业尽快转型。

如果调整汇总表行字段的顺序，数据意义就会发生本质的变化。如图4-49，当把行业作为一级行字段时，关注的重点就不再是地区发展了。从这张报表的数 据内容来看，我们更容易联想到国民产业结构、行业发展、国民消费等问题。是 不是某些行业完全主导了国民经济的发展，应该如何避免泡沫的产生？关联行业 的数据是否比例正确，其中出现了什么问题？国民消费的流向如何，在消费集中 的行业，企业的营业状态如何，是否需要提高或降低准入门槛以及规范市场？

(www.xing528.com)

瞧，只是简单地将两个行字段换位，就得到了截然不同的分析结果以及行动方针。可见，字段的主次关系对于数据分析结果有多么重要的意义。这其中的奥妙，还需你在实践中细细揣摩。

技能心得——可以删的汇总

手工制作一份复杂的分类汇总表，犹如建造一栋 摩天大楼，需要夜 以继日地辛勤工作，一砖一瓦地堆砌而成，任谁都不会忍心让自己的心血毁于一旦。因此，电脑里就存放着各式各样的过渡报表、最终报表、报表版本123、报表年份ABC、报表月份甲乙丙。随着工作年限逐渐增加，文 档库会越变越大，分类会越来越细，到最后，找一份报表都将成为一项工 作。就企业而言，核心员工调岗后，大量的报表交接也会令新人无所适从，从而产生一系列的工作问题。

但如果这栋“摩天大楼”只是孙悟空的一根猴毛变出来的，我们就不用太紧张它了。只要猴哥在，猴毛无限多，源数据表与汇总表的关系正该如此。删表只是一个概念，本身并不重要。重要的是由于可以删表，代 表我们已经掌握了正确的方法，能够快速、准确地制表，这种能力将为我们个人提供更多的机会。

像往常一样，你抱着笔记本进了老板的办公室。在汇报过程中，老板提出N个问题，他希望不仅仅看到一个角度的分析数据，可是他没有提前告诉你。换作从前，你也许会默默记下他所有的要求，并答复：“好的，我尽快统计，预计要五个工作日，下周三之前向您汇报。”现在，你可以直接打开源数据表，当场“变”给你的老板看，并和他一同分析数据。这样不仅表现了你出色的工作能力，也缩短了老板决策的时间，同时争取到更多与老板相处的机会。不知不觉中，你和老板的距离就被悄悄地拉近了。

第4节　巧妙组合日期

在用透视表做分类汇总表时，有一个特殊的字段值得单独说一说，它就是日期。原则上，任何数据分析都应该基于特定的时间范围，否则数据就没有意义。如果我告诉你我奢侈了一把，花了整整一万块钱，你的第一个问题一定是：“多长时间花的？”一年花一万，不多；一分钟花掉一万，奢侈。

可实际上，这样的理解也不完全正确，你还应该问我一个问题：“通常你一年花多少？”“3000！”那么，对我而言，一年花一万真的已经非常多了。所以，在分析数据时，除了要按时间范围进行汇总，一般还要对比相同时间范围内的数据变化情况。

不管怎么说，日期都是汇总表不可或缺的组成部分。下面，我们就以这张办公用品领用表（见图4-50）为例，聊聊日期的事儿。

日期字段怎么放

作为页字段

如果不需要对比每个月的领用情况，就可以将日期放入页字段。不作特殊处理的话，此类汇总表只能显示单月或者全年的汇总情况，而不能显示某几个月的汇总数（2007版可以）。

我们前面介绍过分页显示功能，这种布局支持透视表将每个月的领用情况分别显示，一键打印，常用于需要以纸张形式对每月数据进行存档的情况。

作为行字段

如果以日期作为一级行字段（见图4-52），汇总表关注的是每个月领用的整体情况。这种分析不为指导明年的办公用品预算，也不关注具体品类的领用状况。它的意义是站在更高的管理层面，对领用情况进行概括性的展示而已。这就好像我们看自己一年的信用卡账单，不看吧，不知道今年整体状况如何；看了吧，也不意味着明年就要对消费习惯做出调整。

在外企里写邮件，经常用到一个英文缩写——FYI（for your information），中文意思是“告知”。这意味着发件人不需要收件人在看到邮件后做任何动作，有时甚至都不需要回复，只是告诉收件人有这么个事，这叫知情权。对老板而言，知情权是他最重要的权利之一，所以，上面这种概括性的报告也要记得“告知”他。

如果以日期作为二级行字段，汇总表的关注重点就转变为同类办公用品不同时期的领用趋势。由此，我们可以对明年同期的各类办公用品采购数量做粗略的估算。

作为列字段

将日期作为列字段，好像更符合通常的阅读习惯。如下面这张汇总表，如果单纯关注日期的作用，它完全不同于之前三张表，既可以让我们直观又完整地感受到全年的办公用品领用状况，又能提供最佳的阅读体验。让老板的眼睛舒服，也是我们的责任。

以上三种日期字段的处理方式，没有对错，只有合适与不合适。具体采用一哪种方式，取决于我们的工作目的。只要搞清楚它们各自表达的重点，就能做出最恰当的汇总表。

日期很特殊

有朋友可能要说：“时间范围很广泛，可以是年、季度、月、日、小时、分钟，甚至秒。如果要按照不同的时间范围制作汇总表，我的源数据表可就不好处理了。”这说得一点都没有错。

遵循要分析什么就要有什么字段的原则，如果要根据月份进行统计，就 要有一列记录月份；根据季度进行统计，就要有一列记录季度；根据年进行统计，就要有一列记录年。那么，就算我们知道如何运用month/ceiling/roundup/ year等函数，可以将日期自动转换成相应的时间数据，在源数据表中也需要有 专门的N列来记录它们（见图4-55）。然而，我们却不知道这些字段何年何月 才能派上用场。更何况，如果真要统计到小时、分钟、秒，又该怎么办？

这似乎不是轻松工作的方式，反而加大了工作难度。“一定有好办法可以解决”，我当初就是凭着这股信念，四处寻找解决方法。信念的来源是对Excel的信任，相信它有这个能力。我常常对学员讲：“有的问题虽然一点就通，但如果你不敢去想，就不可能去学，如此一来，你就永远学不到。遇到数据问题，要相信Excel能解决，至于具体技能和操作步骤，满大街都是”。

Excel是真的考虑到了我们要按照不同时间范围进行统计的个性化需求。于是，在数据透视表里出现了一项功能，叫做“组合”。只需经过简单设置，就能出现不可思议的画面。

“组合”指的是把几个部分的数据组织成整体。这项功能，虽然对其他字段也适用，但却需要手工设置，我并不推荐。而对于日期字段，它是全自动运行的。之所以日期特殊，是因为日期有标准，2010/1/10在全世界任何地方都代表2010年、1季度、1月、10日。于是，Excel可以自动拆解日期中所包含的属性，而不需要我们手工区别，所以对于源数据表，有一个日期列就足够了。

日期怎么组合，各位看官睁大眼睛瞧好了。

首先，进入数据透视表的操作界面（Alt+D→P→F），当我们把日期字段拖入行字段区域后，会得到一个密密麻麻按日期汇总的报表。

接下来，只用两步就能得到不一样的汇总表。

第一步：在日期字段数据区域的任意单元格，点鼠标右键选中“组及显示明细数据”下的“组合”。

第二步：设置期望的汇总时间范围（可以多选），点击“确定”即完成。

如果设置了按月进行汇总，就会得到忽略年份的月领用情况。

如果设置为按年/月进行汇总，就会得到不同年份不同月的领用情况。

还可以按年/季度进行汇总，得到不同年份不同季度的领用情况。

或者按年/季度/日进行汇总，得到不同年份不同季度每一天的领用情况。

看看它，再回想当初做这么多份报表时，是多么的痛苦，甚至绝望。可是现在，两个步骤，轻松搞定。细心的朋友还会发现，汇总的时间范围竟然可以小到小时、分、秒，谁敢说“组合”不牛？如此工作，心情如何，谁用谁知道。

正确的日期才特殊

“组合”好用，可是也有前提——日期格式必须正确。

日期的录入原本只应是一个小技巧，可是，一旦录入错误，上面提到的福利我们就无法享受到。所以，我在此非常隆重地为日期再写一小节。

在为好几个客户做培训前的报表咨询时，我都发现一个简单却严重的问题：他们几乎所有报表里的日期，都是错误的。日期的错误并不是指把2010/1/10录入为2010/2/10，数据内容是否正确，我管不着，也没法管。我所说的日期错误是，对Excel而言，他们录入的压根儿就不是日期，这就严重了。既然Excel无法判定录入的内容为日期，也就无法为其拆分属性，从而无法智能得到组合后的汇总表。

我们说过，原则上，所有汇总表都应该基于某一个时间范围来制作。如果Excel不能识别日期，就意味着所有的汇总表都要手工打造，难怪做表成为很多人心中的痛。

日期常见的错误录入方式有以下几种：

“点”型——以“.”为分隔符号录入的日期，如：2011.1.10；

空格型——以“”为分隔符号录入的日期，如：2011110；

外星型——以地球以外的语言为分隔符号录入的日期，如：2011@1@10、2010&1&10。

正确的录入方式虽然有很多种，常见的却只有两种：

“减号”型——以“_”为分隔符号录入的日期，如：2011110--；

“正斜杠”型——以“/”为分隔符号录入的日期，如：2011/10。

其他格式的正确日期，通过设置单元格格式就能找到。

检验日期是否正确的方法很简单：将单元格格式设置为常规，如果日期变成了一组数字，就是正确的日期；如果日期仍然是日期，就是错误的日期。例如：单元格内容为2011/1/10，设置为常规后，变成了40553，代表日期是正确的；单元格内容为2011.1.10，设置为常规后，依然是2011.1.10，代表该日期是错误的。

这是什么道理？这里就涉及Excel中日期的本质，我简单为大家介绍一下：Excel默认的起始日期为1900/1/1，在系统里，它代表第一天，用数字1表示。之后每增加一天，数字就增加1，2011/1/10距离1900/1/1有40552天，40552+1=40553，于是40553就代表了2011/1/10。正因如此，两个日期才可以做减法，得到的结果为它们相隔的天数。

一个日期包含很多字符，一个个地输入可不是办法。输入2011/1/10要敲击键盘9+1次，这还没算上喝了酒、没睡醒、心情不好输错重来的次数。咱们学一个快捷键，只用1+1次就能准确输入当前的计算机日期，让效率提高5倍：“Ctrl+；”，再按“回车”搞定。有的工作需要记录当前时间，别再看手表或者系统时钟了，看的时候20∶16，输入单元格时就已经变成20∶17了，不但不准确，还很麻烦。还是用1+1个动作准确输入“Ctrl+Shift+；”，再按“回车”搞定。

如果你正在使用的源数据表已经存在大量的错误日期，不用担心，只要将错误的分隔符替换为“_”或“/”就可以修复了。替换就不用再讲了吧，你懂的。

第5节　追根溯“源”

做汇总数据的人有两大难事：第一难是做出一张像样的汇总表；第二难是接 受老板对数据提出的质疑以及新的分析要求。对于第一难，学会运用数据透视表，制作汇总表这件事情已经不足挂齿。但是，我相信大家有过这样的经验：当我们 向老板汇报的时候，他会质疑某一些数据，并且要求看到明细。如果汇报前准备 不充分，被问到时，要么胡乱说一通，要么过后认真地查清楚了再反馈，经验不足的人干脆呆立当场，听候训斥。

为什么我们做的汇总表永远能够被老板挑刺？那是因为我们和老板所处的位置不同、责任不同、看问题的角度不同而造成的。有的时候，就算我们再认真努力，也无法真正了解老板想要什么。

对我们来说，处理一个数字也许仅仅是工作；但对决策者来说，一个数字可能就决定了一件事的成败，当然错不得。这也就是为什么上级总能发现我们的错误，而我们自己却浑然不知的原因。

在这个问题上，没有对错，只是各司其职罢了，但却苦了我们这些做表的人。有时候被问得哑口无言，脸红心跳，下去后还要加班加点整理数据，准备第二天向老板交差。更辛苦的是，老板通常不会一次罢休，今天质疑了这个汇总结果，要求看明细，明天又突发奇想，质疑其他的汇总结果，再一次要求看明细。如此反复多次，汇报者的心情不好，老板的心情同样也不好，更关键的是影响了决策的时间。

但我们是幸运的，因为数据透视表提供了查看明细这项功能，并且操作简单。我们要为老板提供明细，只需动动鼠标就能轻松获得；老板要看更多的明细，只需花10秒钟教会他，我们就不必再伺候左右，他想怎么看就怎么看。如此一来，皆大欢喜。

具体的操作方法，请看下面这个例子。

首先根据这张员工信息表，按部门和文化程度来统计相应的员工人数。

统计结果出来以后（见图4-67），如果想要知道财务部和技术部高中学历的员工是谁，全公司大专学历的是哪几位，并不用再回到源数据表辛辛苦苦地筛选、复制、粘贴，只需用鼠标左键双击汇总数，就能得到结果。

数据透视表提供的查看明细功能非常简单，想看什么数据的明细，就双击它。Excel会自动新建一张工作表，逐项罗列出所有相关数据，瞬间定位，一目了然。看完不用了，删除新建的工作表即可。下表即双击汇总表里大专的总计数7后，得到的明细数据。

但要千万注意以下一点，否则会泄露商业机密。

数据透视表很神奇，当我们把一份通过数据透视表得到的汇总表复制到新的工作簿时，只要双击任意汇总数，就可以在没有源数据表的情况下，显示完整的数据明细。你可以理解为：透视表中集成了源数据，汇总表走到哪里，这些源数据就跟到哪里。

企业的汇总表也许可以公布，但是数据明细一定要保密。拿本例来讲 ，看看企业的学历组成情况无伤大雅，但如果通过双击后看到明细，员工的个人资料就会全部泄露。后果可想而知，挖墙脚的，推销保险的，卖假发票的，相亲的，寻找仇人多年的，可就缠上这些人了。

所以，如果是通过数据透视表生成的汇总表，将它发送给其他人之前，务必先确认对 方是否需要看到数据明细。如果答案为否，就辛苦一点，用选择性粘贴把汇总表粘贴为数值再发送。千万记住一点，未经处理的通过透视表生成的汇总表，不可以随意传播，切记切记！！！

第6节　关联数据齐步走

手工打造汇总表的时代，还有一件令人头痛的事，那就是汇总表与源数据表数据的同步问题。每当源数据有添加、删除、修改时，汇总数据都要重新统计，然后人工更新。制表者的工作量因此严重增加，在成千上万行的数据面前，大海捞针般地更正数据并重复统计令人郁闷。

“！”

使用数据透视表的刷新数据功能，就没有这个烦恼了。由于汇总表是根据源数据表自动获得的，所以在它们之间本来就有数据关联。因此，当源数据发生变化时，只要点击数据透视表工具栏中的“刷新数据”按钮，汇总数据就会自动更新。要问“刷新数据”按钮长什么样，大红色的“！”是也。

还是这张某企业员工学历汇总表。

在源数据表中，将所有“大专”替换为“高中”。

返回数据透视表界面，选中数据区域任意单元格，点击工具栏中的“！”，即可得到同步更新的汇总表。此时可以看到，大专学历的员工数已经减少为0，而高中学历的员工数则增加为9。

使用“刷新数据”功能时，有一点需要注意：虽然一份源数据表可以新建N张汇总表，但每次只能刷新一张汇总表，而非批量刷新所有由该源数据表自动生成的汇总表。要实现批量刷新，则需借助Excel二次开发工具VBA，有兴趣的朋友可以在网络上找到源代码。

对于大多数人来说，逐个刷新汇总表未必是件坏事，因为对每一个工作结果认真负责是正确的态度。我反而建议“懒人”们在这里放慢脚步，仔细观察每一份汇总表的数据变化，在更新数据的同时，于心里记下变化背后的意义。这样一来，在提交报告时，不用复习也能做到心中有数。快和慢并不是绝对的，应该视情况而定，有时欲速则不达。

预约源数据

前面说了数据修改时的更新，现在咱们再来说说数据添加时的更新。添加数据，意味着源数据区域扩大，也意味着数据透视表选定的数据源区域要扩大。那么，数据透视表的选定区域能否随着源数据的添加而自动扩大呢？答案是：不能！但是，我们可以通过预约选定区域的方式来解决这个问题。

由于预约的区域是空白数据区域，没有数据时，10加0依然等于10，丝毫不影响汇总结果；当有新的数据添加进来后，10加1就变成了11，添加的数据被同步更新到汇总结果中。预约操作在数据透视表设置向导的第二步里完成，只要将选定区域放大即可。

如下表，源数据区域本来只在$A$1∶$BF$8有数据，现在扩大选定区域范围至$A$1∶$BF$3000。当源数据表3000行以内（含）有新的数据添加时，只需点击“！”，汇总数据就会自动变更，实现同步更新。

本例由于预约了源数据，蓝色部分新增的5名员工信息，就自动添加进了汇 总表。

新建明细数据≠源数据

前面说过，双击汇总表任意数字，即可以查看数据明细，它们显示在新建的工作表中。可是要注意，该明细的意义并不完全等同于真正的源数据，它的变化不能导致汇总表的数据变化。因为，与汇总表关联的数据只来源于源数据表中的选定区域。所以，如果希望汇总表同步更新，应该在源数据表中做添加、删除、修改操作（见图4-76），而非在查看数据明细的工作表里（见图4-77）。

找得到的工具栏

有时候，我们会找不到数据透视表的工具栏，而如果没有工具栏，就不能执行诸如分页显示、刷新数据等命令。我知道有的朋友会选择从第一步开始，把数据透视表功能再调用一次，这样工具栏就会默认出现了。其实不用这么麻烦，只需记住一句话：Excel界面应该有的东西，都在视图菜单进行管理。去找找看吧！

关键时刻要备份

同步更新固然方便，可别忘了关键时刻对源数据进行备份。因为源数据一旦被改变，就无法追回。我曾经见过有人如此更新他的汇总表：先把之前的源数据完全删除，再把从系统中导出的最新数据粘贴到源数据表，最后刷新透视表。他没有对源数据做任何备份，永远都在唯一的一张源数据表中大做文章，这样操作风险很大。

即使是系统数据，也并非100%的准确，如果有人无意间修改了系统中的历史数据，还要用以往下载的数据反向追查。所以，面对大范围的源数据变动，做好备份是有必要的。备份文档一般按照源数据所属的日期范围进行命名，方便以后查找。这并不是每天的日常工作，根据报表性质不同，可以一个月一次，也可以半年、一年才做一回。

第7节　你还应该知道这几招

去掉数据的“分身”

Excel应用中被问到比较多的问题是去重统计，一般是指：统计同一列数据有多少个不重复项。

用通俗的话来说，就是要去掉数据的“分身”。这种需求很常见，比如：从本月采购明细表中得到采购的商品种类，从本季度请假明细表中得到请假员工的人数，从本年经销商订货明细表中得到经销商数量等。因为明细数据的记录方式会造成同一名称多次重复出现，而不同的名称重复的次数又不同，所以，去重统计难倒了不少人，一时间成为Excel的典型问题之一。

去重统计的解决方法有以下几种：

函数法——1/Countif，求单个名称重复次数倒数的和；

版本法——用2007及以上版本菜单自带的“删除重复项”功能，再计算剩余数据的行数；

透视法——运用数据透视表，拖拽去重，再计算汇总表的行数。

函数法很妙，但是写函数很麻烦；版本法很方便，但要安装相应版本；透视法最简单，没有以上要求，新老版本均可用，操作起来也很方便。

看下面这个例子：即使源数据表只有一列数据，也能调用数据透视表功能。

针对这一个字段，咱们做两次处理：首先把它拖为行字段，然后再把它从字段列表中拖入数据项区域。

这样生成的汇总表，不仅完成了去重统计，还显示了每个项目被重复的次数。

你也许会问，为什么全是文本的字段可以作为数据项呢？为什么同一个字段可以被拖动两次呢？

在数据透视表的汇总方式里，有一种方式叫做“计数”（见图4-83、图4-84）。它的概念是计算数据出现的次数，与数据是文本还是数值无关，所以文本字段也可以作为数据项。对Excel来说，只要被计数的单元格不为空，则计一次。

我们在讲到源数据表的设计时强调过，数据区域中不能留空白单元格，正是以透视表的计数统计方式为依据的。一旦某列数据不完整，统计对应字段出现的次数时就会得到错误的结果。更糟糕的是，由于这种错误很隐蔽，不容易被发现，于是，我们就会在不知不觉中用错误的分析数据做决策。

至于同一个字段可以被拖动多次的问题，Excel就这么规定的，记下就好。正因为有这个规定，才有接下来要讲到的并列汇总。

汇总数排排站

既然汇总表是为决策服务的，有时就需要同时显示更多的数据信息。例如：一份现金流量汇总表，除了关注总金额，还需要同时关注单笔最大金额、单笔最小金额、平均金额。这就要求同一字段被多次添加，在汇总表中并列显示汇总数。要做到并列显示，有一个前提：汇总表无列字段。否则，你的表格会变得很宽很混乱，失去阅读和决策的价值。

这是我们想要得到的汇总表：

从源数据表开始，首先，进入数据透视表界面，依次拖入行字段及第一数据项（见图4-86）。

重点来了，当我们再次把“金额”拖为数据项时，汇总数据会变成上下结构（见图4-87）。

接下来是一步最重要的调整：将数据透视表中的“数据”字段，拖入写有“汇总”二字的单元格（见图4-88）。

此时，汇总数据变为并列显示，之后再添加其他数据项，就不用再做调整了（见图4-89）。

数据项添加完毕后，将各字段的汇总方式分别设置为：求和、最大值、最小值、平均值（见图4-90）。

复制透视表

学会了并列汇总还不够，因为，做决策不能只靠一张汇总表，越精确的决策，越依赖于多角度的数据分析。虽然是由同一份源数据得来，汇总表的样式却可以多种多样。

这么多的汇总表，不用每张都从调用透视表功能开始做。数据透视表是允许复制的，粘贴后的表格具备透视表的所有功能，可以单独进行设置，这为一源多表提供了最大的便捷。

以一份网吧维护业务明细表为例。

首先，调用数据透视表功能，得到第一个透视表；

其次，复制该透视表，粘贴到同一工作表内；

最后，分别进行设置，得到从不同角度反映企业经营管理状况的多个汇总结果。

职场感悟——职场参照物

回想几年前，我的Excel应用水平不算好，可又长时间没有取得更大的进步。究其原因，不是当时的我不好学，而是身边没有一位高手鞭策我，于是，我失去了目标。

职场上低标准的人分两类：一类明明知道可以做得更好却不愿意努力；另外一类根本就不知道什么是更好。幸运的是，在苏州明基工作的两年里，我周围的每个同事都一丝不苟，这让我学会了什么是严谨。试想，如果一直在松散的环境下工作，没听说也没见过严谨的做事风格，再努力的人也无法做到高标准的严谨。后来，在北京李宁，我深刻体会过每天上十万件的发货量，而服务于其他公司的朋友，却在抱怨一万件货要人命；在卓越亚马逊，我有幸与一群非常专业的同事共事，逼得我往更专业、更高效的方向发展。

环境对人的影响很大，有高标准的参照物，才能造就高素质的职场精英。职场上不怕努力，怕的是朝错误的目标努力。找好职场参照物，对个人发展尤为重要。即便没能身处好的环境，也要多问多听多看，向更好的参照物看齐，不断拔高自己的标准。

第8节　找到初恋的感觉了吗

“甜过初恋”这四个字源自2010年最强民间广告文案。一位卖柑橘的老奶奶在成堆的橘子上竖起了一块纸牌，上面写着四个字——甜过初恋。短短四个字，既勾起了人们对橘子的兴趣，也勾起了对过往的回忆。网友们纷纷评论说：“老奶奶淡然的眼神背后，又有着怎样美丽的初恋？”“老奶奶卖的是回忆，而我们买的是柑橘。”……

工作是什么味道？酸酸的、涩涩的、甜甜的、印象深刻的，像初恋一样。在职场打拼的人，每天都要面对太多的挑战，尝遍各种滋味。与在发达国家工作的单纯、专一不同，在国内生存要求知识面广而博。在发达国家，你可以告诉老板这个你真不懂，可是在国内，不能不懂，不懂就去学到懂。所以，多学一项技能傍身永远没错。

数据透视表不能解决所有的工作问题，甚至不能解决所有分类汇总表的问题。但是有了 它，80%最常见、最重要的分类汇总工作，都可以快速、准确、高品质地完成，这样也就足 够了。希望它能让你的加班少一点，机会多一点 ，心情好一点，回忆起工作中“甜”的滋味。