第1章 “懒人”眼中的Excel
第1节 把Excel当系统
当你面对一个空白的工作簿时，先别急着往里面填内容。Excel可不是电子版的记事本，每张工作表也不应该孤立存在。要知道，它其实是一个小型的数据处理系统，以系统的思维、看待它，方能领悟制表、用表的一般规律。在我身上，这个方法屡试不爽。
“荒谬”的三表概念
三表概念源于我的胡思乱想。在与Excel打了多年交道的某一天，我忽然想要弄明白，为什么新建的工作簿默认为三张工作表，而不是两张或者四张（如图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……依此类推。