第1章 "懒人"眼中的Excel

第1节 把Excel当系统

当你面对一个空白的工作簿时，先别急着往里面填内容。Excel可不是电子版的记事本，每张工作表也不应该孤立存在。要知道，它其实是一个小型的数据处理系统，以系统的思维、看待它，方能领悟制表、用表的一般规律。在我身上，这个方法屡试不爽。

最"荒谬"的三表概念

三表概念源于我的胡思乱想。在与Excel打了多年交道的某24小时，我忽然想要弄明白，为什么新建的工作簿默认为三张工作表，而不是两张或者四张(如图1-1)。结合自己做表的经验，以及使用系统和参与系统设计的体会，我发现，三张表才能构成一个完整的数据管理体系，缺一不可。从功能上划分，它们分别是分类汇总表、源数据表和参数表，这就是三表概念。此时我恍然大悟，这竟然正是我一直遵循的制表、用表方法，只不过到现在才提炼出一个可以说得明白的概念来。

这个概念之所以被我形容为"荒谬"，是因为它并非由官方公布，仅代表我个人的观点。究竟是巧合，还是微软有意为之，我不得而知。但无论怎样，三表概念让我掌握了使用Excel的核心，并让本来单薄的工作表变得立体起来；也使我对表格的思考不再停留于技巧层面，而是站在了数据结构和数据关系的高度上。

最靠谱的表格结构

三表概念的重点在于三张工作表所代表的不同意义。根据工作流程和目的来看，抛开查询和对比不谈，与数据打交道无非要做两件事：记录数据和分析数据。在这个过程中，还涉及数据记录时的率和效率等问题。

源数据表的用途是存放所有的数据明细，所以录入的动作应该在源数据表中完成；分类汇总表里体现的是数据的分析结果，这些结果往往只起到展示的作用，而不用在分类汇总表中进行过多的操作；参数作为辅助数据，通常被源数据表和分类汇总表所引用，它同时也为数据的录入提供了保障，这部分数据存放于参数表中。

我们只需要规范地填写好源数据，就能通过各种技术手段自动获得分析结果。如图1-2所示，这是利用三表概念制作的"车辆情况使用表"。其中，参数提供了"所在部门"录入时的选项，较大程度避免了由于人为失误造成的数据录入错误；源数据逐条记录了车辆使用的详细情况；分类汇总结果不是靠手工做的，而是通过源数据自动生成的。

三张表中，需要手工打理的是源数据表，它是三表概念的核心，是一切分析结果的来源，也是最重要的数据资料。

致，有理念做支撑，才可以将实践引向正确的方向。想也不想就埋头苦干，只会事倍功半甚至前功尽弃。了解三表，就能了解数据之间的关系，以及工作的进程。先有、规范的基础数据，然后才谈得上数据分析，这两个步骤缺一不可，但也不能混为一谈。"表"哥、"表"姐们往往忽略了这个关键，没日没夜纠结于"制作"各种分析结果，到头来得到的却只是一堆一次性报表，时间就在这些无效而又痛苦的重复中，白白浪费了。

"调皮鬼"捣蛋——默认两张表

调皮鬼是一枚热爱Excel的小高手，他总能从别人觉得枯燥的事情中找到游戏的乐趣，因为Excel对他来讲是一个可以捉弄人的大玩具。当听说三表概念源自默认三张工作表之后，爱捣蛋的他硬要让新建的工作簿默认为两张工作表。只见他偷偷地在"工具"→"选项"→"常规"中，修改了一下"新工作簿内的工作表数"(如图1-3)，于是阴谋得逞。

第2节 天下只有一张表

理清了源数据和分析结果的关系，也知道源数据表是三表的核心，那这张至关重要的表到底应该长成什么样子呢？作为一张的源数据表，表中的数据应该描述简洁、字段完整、录入规范、逻辑关系清晰。行业的不同、岗位的不同、工作任务的不同，只会使数据内容不同，但并不影响表格的设计以及数据记录的方式。所以，从样式上讲，天下其实只有一张表，这就是"天下及时表"。

以"不变"应万变

汽车行业使用的表格与餐饮行业使用的表格有什么不同吗？

行政部和销售部使用的表格有什么不同吗？

现金日记账和现金流量表有什么不同吗？

如果你曾经认为以上这些表格应该各不相同，那很遗憾，在未来与表格打交道的漫长日子里，你必须不断学习新的表格样式，并且无法将过往的经验应用于新的环境之中。其实，只要换个角度看待这些表格，你会发现，它们全都一样。

汽车行业关注车型和车价，餐饮行业关注菜名和菜价，数据属性是一样的！

行政部关注耗材名称和数量，销售部关注产品名称和销售数量，数据属性也是一样的！

现金日记账关注科目和金额，现金流量表也关注科目和金额，数据属性还是一样的！

将这个概念扩展到全天下也同样适用：美国人用的表和印度人用的表只是语言不同，500强用的表和杂货铺用的表只是数量级不同，上级用的表和下级用的表只是数据的机密程度不同……

于是，我们发现，可以用相同的表格样式、相同的数据记录方法(如图1-4)，来应对各式各样的工作需求。有了"天下只有一张表"的认识，将大大降低你学习Excel的难度，也能让你过往的经验在新的环境中依然有价值。

能成为"天下及时表"的源数据表必须具备以下几个条件：

一维数据——只有顶端标题行，没有左端标题列；

一个顶端标题行——只有及时行是标题(即下文提到的字段)，从第二行开始就是数据；

没有合并单元格——不能出现任何形式的合并单元格；

连续的数据——数据区域中不能出现空白单元格、空白行，以及空白列；

的数据内容——大致包括完整的字段、一致的描述，以及分列记录的数值与单位。

懒人梦话

一朝学会，终生受益。这不是汽修学校的广告，而是Excel的心声。无论你是谁，服务于怎样的公司，在做怎样的工作，只要用好Excel，你就掌握了一项全球通用的技能。

以不变生"万变"

拥有了的源数据以后，你就可以通过Excel各种强大的数据处理功能使其变化万千。除了运用分类汇总的"神器"——数据透视表(如图1-5)以外，掌握函数(如图1-6)和相应的菜单功能也是玩转三表必不可少的手段

要始终坚信一点，分析结果应该"变"出来，绝非靠手工一点点"做"出来。初学时，你可以说不懂具体技巧的运用，但一定不能说因为Excel做不到，于是自己就傻乎乎地埋头瞎干。

懒人梦话

知道自己不懂不可怕，不知道自己不懂才真正可怕。前者顶多没有学习的动力，但至少有清晰的目标，后者却将低效视为常态，只能在矮子里充高个儿。

第3节 造智能表格，享"懒人"幸福

掌握了Excel设计和使用的核心理念之后，只需要辅以几个关键性技巧，就能玩转这个看似复杂，或许还有点高深莫测的桌面数据处理工具。就技术实现的环节，二八原则依然有效，虽然Excel能做的远远超出我们的想象，但回归到最普遍的工作需求，使用少量常用的技巧即可打造出智能表格，并完成80%的表格工作。让我们先预览一下。

授权E表，坐享其成

用过企业系统的朋友应该知道，由于限定了条件，在系统中犯错的概率相对较低。我的意思是，在这样的系统中，王老五只能被选为"王老五"，而不会变成"王老伍"。但在Excel中，源数据却常常由于描述不一致而惨遭破坏，并造成严重的后果，比如数据整个作废。想要弥补也不是不行，轻则，一个人加班加点修正数据；重则，上下级单位几十号人集体大战Excel。通常，数据来源越复杂，这种现象越明显。就算三令五申要求数据提供者不许犯错，也无济于事，错误的数据依然源源不断地出现。

其实，Excel本身是有法子的，称之为"数据有效性"，主要用于参数表和源数据表之间的互动(如图1-7)。通俗地说，就是只有满足了限定的条件才允许在单元格中录入。你可别小看这个功能，于管理，它意义非凡；说操作，它可深可浅。

根据不同的案例，在后面的章节我将为大家介绍一级有效性、二级有效性，以及数据依次录入等技巧的实现。

妙招生花，事半功百倍

按照正常的流程，我们应该先做源数据，再变出汇总结果。但有时候迫于无奈，只能得到汇总结果，而没有源数据，并且在这种情况下，还被要求对多个汇总结果进行再汇总(如图1-8)。既然无法改变"存在即合理"的残酷事实，那么就只能想办法提高自己的能力了。

对数据进行批量处理是Excel的强项，哪怕是我们熟悉得不能再熟悉的SUM函数，也能在这类工作中发挥出极其强大的批量处理能力，从而使不可能的汇总变为可能。

我从上百种Excel技巧中，精心挑选了最常见也最震撼的批量处理技巧，足以让你为之疯狂。把它们搭配在五类表格的案例中，你就能真正体会到Excel无穷的魅力。

一技傍身——工作表间的快速切换

在多个工作表中反复切换，最有趣的方式莫过于使用快捷键：切换到下一个工作表按Ctrl+PgDn，切换回来则按Ctrl+PgUp。

不变的追求：牵一发而动全身

在与Excel相关的资料中，经常能看到"牵一发而动全身"这个说法。这是Excel的精髓，也是我认为的较高境界。我对它的理解是，如果一个单元格的数据涉及十个汇总结果，那么当它发生变化的时候，与之相关的所有结果都相应地发生变化，这才是真正的"动全身"。也只有这样，才能保障数据同步更新，并得到最的数据分析结果。

就三表概念而言，源数据表中某单元格的变化，应该导致同一行关联单元格的自动变化，同时使分类汇总表中相应的汇总数据发生变化。要实现这种设想，所用到的技能非函数莫属。

很多人都曾经面临过一个难题：当源数据表中"有东西进出"时(这个东西可以是办公用品、库房的商品、资金等)，如何才能得到当前库存数和期初、期末库存数？运用SUMIF函数的动态引用，恰好能地解决这个问题，从而让Excel自动为我们提供最直观的库存现状(如图1-9)。

不仅如此，还有更多"牵一发而动全身"的思路和操作方法将在后面详细地介绍给大家。

懒人梦话

"牵一发而动全身"是懒人偷懒的终极目标，但对于缺乏兴趣和时间钻研Excel技巧的"表"哥、"表"姐们，只要能在自己的工作中玩转数据之间的关联，也就足够了。至于掌握技巧的多与少，其实并不那么重要。毕竟，Excel也只是一个工具，而非工作的全部。

第2章 借双慧眼识源表

第1节 认准主角是关键

每一张源数据表都有一个主角，与之同行的数据负责讲述主角的故事，至于故事情节，则由该表所涉及的工作流程和目的而定。于是，当我们从无到有新建一张源数据表时，认准主角就成了关键。

先看一个最简单的例子，一张采购明细表。顾名思义，既然是采购明细，采购的货品自然应该是主角。而整张表的设计则围绕采购事件展开，于是就有了什么时间采购的、向谁采购的、采购了多少、采购的单价这些字段(如图2-1)。

下面增加一点难度，我们来设计一张美容院用的表格。设计的时候，首先要了解美容院的业务情况：客人来店里消费，有技师为其提供服务，美容院有各种消费项目，每个项目对应不同的产品，技师推荐产品有提成。从以上的描述可以看出，在这个案例中，主角明显不止一个，客人、技师、消费项目、产品都可以成为主角。但是与电影不同，源数据表只允许有一个主角，而故事情节必须围绕该主角进行。所以，在设计这张表的时候，需要认真分析美容院的工作流程和目的，以便地找到那个关键的角色。

根据工作目的，如果关注各种消费项目的情况，就应该以消费项目为主角；如果关注产品售卖的情况，就应该以产品为主角……依此类推。可是从流程来看，是客人到店后才引发了后续的商业活动，假设选择了产品作为主角，用产品去对应客人，那么数据发生的顺序就颠倒了，表格反而会影响美容院的正常工作流程。所以，在这个案例中，以客人为主角才是最适合的(如图2-2)。

细心一点你就会发现，即使是以客人为主角，这张表所提供的数据依然能对消费项目、产品、技师进行分析。原因是，它们与主角同在一个故事当中，而Excel有办法处理故事中的任意片段。

懒人梦话

用数值和字母组成的代码，来替代容易重复的中文"主角"，将为后续的数据处理带来不少便利。

第2节 火眼金睛挑毛病

仅仅认准了主角还不够，还得学会辨认源数据表的品质。也许是长期的使用习惯，或者是过分夸大了天下只有一张表的概念，要不就是还未掌握相关的技巧，在你的源数据表中，可能还存在以下这些缺陷。

常有一列录序号，殊不知画蛇添足

回想一下你的表格，及时列是否也是"序号"(如图2-3)？

这种做法非常常见，虽然大家可能并不清楚为什么要这样做，却一直保持着这个习惯。我也真不能告诉你这就是错的，相反，它还是还原数据初始顺序的一种技巧。

我们都知道，在表格中按某个字段进行排序以后，数据的初始顺序就被打乱了。如果数据本身不是以日期作为排序依据，想要还原几乎是不可能的。可别告诉我你玩的是"撤消"(Ctrl+Z)，请千万记住，它十分不。假如在排序之前制作一个辅助的序号列，像图2-3所示那样，就能让Excel记住数据初始的位置。

可据我所知，大多数人并不是带着这个目的来做序号列的。而且，数据的初始顺序通常是以日期为依据。这样的话，序号列就显得画蛇添足了。

一技傍身——快速生成序号列

利用Excel对数字规律的判断，以及参照相邻列快速复制的特性，当有需要时，即使在庞大的源数据中，也能快速生成序号。

及时步，在A2、A3单元格分别输入数字1和2，并选中这两个单元格(如图2-4)。

第二步，将光标移至A3单元格右下角，使其呈黑色十字形，然后双击鼠标左键完成序列的填充(如图2-5)。

聪明的Excel能够自动判断两个数之间的步长值(也就是差值)，如果输入的数字为1和3，则得到1、3、5、7、9……依此类推。