建立良好的制表习惯，我克服了「Excel 恐惧症」

两年前，当我在简历上写下「熟练使用 Excel」并将它递到一家家公司求职时，内心可以用毫无底气来形容。一方面，我简直对数字毫无敏感度（事实证明这种感觉可以通过重复和刻意练习来培养）；另一方面，彼时的 Excel 在我心中简直是一个庞然大物，菜单栏的每一个 Tab 中似乎都放着我看不懂的内容。

而在度过了几乎天天与 Excel 打交道的两年之后，我的 Excel 技术总算达到了工作要求的及格线——做出一张干净整洁、无需解释也能让同事明白的底稿。

Excel 或许终将被时代淘汰，但到目前为止它还是一款不可或缺也确实好用的办公软件。怎样做出一张简洁清晰可复用的 Excel 底稿？我觉得比复杂的函数、快捷键、VBA 更重要的，是树立正确的工作意识和一以贯之的使用习惯。

本文适用对象：会用到 Excel 进行日常办公但还有点怵头的人（进阶用户不需要阅读）。

树立「工作底稿」的意识

在工作中，不管是出于什么目的需要使用 Excel 进行计算分析，一般意味着有一定的 计算量 和 数据价值 。因此，凡是用 Excel 进行的数据分析工作，哪怕仅仅为个人所用，也最好形成「工作底稿」的意识。

工作底稿是什么

工作底稿（working paper） 是一个在财税工作中常常使用到的词。财税工作的成果往往以报告等形式出现，且在报告中不会展示计算过程，而计算过程则成为历史文件。但这些「历史文件」又不是可以永远尘封——1 个月后的你、你的同事、你辞职后的继任者（下文统称为「目标读者」）都随时可能会查看这份底稿，反复检验或者借鉴当时的工作方法和结论。因此，做一份「能得到所有信息且看起来不费劲」的工作底稿至关重要。

对于工作重心不是数据处理和分析的人来说，数据分析可能只是工作中的一个小点缀，向同事或接任者交代计算过程的需要没有那么强烈，但让自己随时对数据有把握也很重要——你也不希望下一次打开自己制作的表格时看到的都是些未完工的半成品，然后在理解自己上一次天马行空的计算过程中耗费大量时间。

因此，对于所有会使用 Excel 进行数据分析的人来说，在日常工作中也最好树立「工作底稿」的意识，让自己有把握永远拥有最新版本的底稿、且能及时追溯回任一时点的底稿版本。任何拥有必要专业背景的人（以及日后的自己）翻看底稿时，都能快速明白底稿中的计算逻辑并验证计算结果。

听起来可能有点复杂，而要达成这一切的手段并不是熟练使用 VBA、背出 100 个高阶公式，而是在正确的意识下培养一些使用习惯。

至于 VBA 和公式——需要的时候再学习以及善用官方帮助文档就好了，对它们不熟悉并不阻碍你使用 Excel。

工作底稿的特点

工作底稿的特点可以用以下三点来形容。在下文中，我也将从这三点展开，解说在编制 Excel 表格的过程中能帮到你的一系列小习惯。

1. 可读性强
2. 所有数据来源可回溯
3. 完整的版本管理

怎样编制「工作底稿」式 Excel 表格

在本节开始前，先统一一下会涉及的几个名词：

怎样做出可读性很强的表格

和文章一样，表格也有「可读性」这个属性。

对于逻辑思维和数学思维很强的人来说，他们几乎能读懂一切乱七八糟的表格（致敬），在表格里简单标记也能顺利计算出结果。而对我而言，如果不按照一定的原则进行，计算到半途可能就忘了自己在干什么。如果你也有类似的烦恼，那么我极力推荐你把「可读性」引入表格制作原则中。

根据我的经验，只要遵循以下原则，一般可以做出一份可读性不错的表格。

1. 只做「从上至下」阅读的表格

一张 Excel 表单最多可扩展至 1,000,000 行和 16,000 列，几乎无限延伸的单元格会让人觉得这几乎是一个「白板」工具。大概不会希望 Excel 像大多数白板一样只供一次性使用，所以按照日常的阅读逻辑编制表格是更明智的选择——从上至下，从左至右。

每张表单中的各个分区都最好「从上至下」纵向排列，将同一区块内的属性横向铺开（先简单记住这个原则，下面的示例会一以贯之地应用）。

如果从某一行向右畅游，既能看到包含了一块原始数据，又能看到根据数据得出的统计结果，就是一种不太好的做法。尤其是三个区块按照「 的形状展开时，读者会不知道应该先往下读、还是先往右读，造成不必要的迟疑，于是表格的可读性就会降低。

一个简单的判断方法：想象（或实际）在最靠近 A1 单元格的分区进行「筛选」，不会影响到另一个分区则为通过。

这个原则在越重量的计算中越需要被严格遵循，在简单的计算里遵循也不会有坏处。

2. 数据不要从 A1 单元格开始：添加说明区

「数据不要从 A1 单元格开始」是《为什么精英都是 Excel 控》中的一个建议。但原文的用意是为了表格的美观和灵活，而在这里我想说明「数字以外」的内容的重要性。

如果表格是一篇文章，那么「数据」相当于「正文」，和普通文章一样，为了不让读者一下子撞到硬邦邦的术语和干货，一般会有一个柔和的开头让读者进入自己的语境。

为了让目标读者能对表格内容有更充分的理解，在数据开始之前，最好把关于这张表格的一切重要信息在数据上方先行说明——原始数据口径、数据来源、制表日期、基本假设、分析方法，以及对理解底稿有帮助的其他内容。这种说明可以出现在任何一张表单中（只要这张表单中的内容需要说明），不过最常出现的场所还是下文会提到的「输出区」上方。

由于我们做的表格「只从上到下阅读」，所以读者在顺着你的逻辑查看时，会更容易读懂表格内容。

注：在下文中，我会使用一个我对 2020 年 1 月负责文章进行数据分析的表格示例（真实数据已隐去），虽然例子简单，但希望能尽可能说明。

3. 对数据处理过程分区：取数区、运算区、输出区

一般的数据分析过程会分为三步——①对原始数据的粗加工、②在粗加工的基础上进行有目的的运算、③将运算结果输出。

很多表格会将②和③混在一起，对于聪明人来说当然无妨，但是我建议容易绕晕的各位和我一样有意识地划分以下三个区域：

• 取数区（input）：在原始数据基础上进行基本加工后的数据，基本保留了数据原貌（原始数据需要另外保存一份，这个在后面会讲到）。
• 运算区（working）：公式运算区，复杂的计算过程应该一步步在这里瓦解，并得出最终想要得到的数字。如果公式比较复杂的话，可以把计算过程拆分成两步、三步。用拆分运算步骤的方法不仅可以让每个参与计算的要素都一目了然，容易检查错误、自然也不容易出错。 
• 输出区（output）：在简单的计算中，输出区常常与运算过程合并在一起，但把两者分开会让表格结构更清晰。输出区域的公式最好全部=sheet1!B1，即链接到运算区的计算结果，不再包含任何公式。这样即便发现运算区有什么错误，也只需要在运算区修改，输出区只要负责呈现就可以。相比运算区，输出区要更注意标注和解释，这会在下面几点讲到。

在表格不会变得过于复杂的前提下，尽可能把三个区域储存在三张表单中，这样在切换区域时能更明确「我在干什么」；如果需要在一张表单中完成一套流程，那么可以利用表头进行标注说明，时刻提醒自己、让自己心中有数。

以下是我对工作簿的现有分区，「raw data」代表原始数据，「数据整理」是加工后的原始数据，这张工作簿的取数区和运算区都在这里（因为运算比较简单），而输出区则是最后一张「output」。

如果要从头开始制作一个复杂的测算模型，那么最好也先在心里梳理一下表格逻辑（和写长文章要列大纲一样），再从上到下一步步去做。

4. 谨慎且清晰地使用颜色

在底稿中使用颜色的目的往往不是美观（要贴进报告的表格可以链接出去另行格式管理），而是醒目。《为什么精英都是 Excel 控》的许多原则在日常工作中一一践行实在是过于繁琐，但关于颜色的使用我基本复用了下来：

• 手动输入的数字——蓝色
• 计算公式的数字——黑色
• 拥有链接的数字——绿色（关于「链接」的解释看下一节）

我一般只在输出区使用颜色，全部=sheet1!B1是一种理想情况。如果一时偷懒，例如在输出区也使用了公式，那么可以通过颜色提醒下次打开表格的自己——这里没有严格链接回去。越是需要大量的计算，区分数据来源就越重要，这样可以省下反复检查公式的时间。

例子：

以下我的输出区域，有部分数字是黑色，检查公式发现是=COUNT('2020-01-三羊-数据整理'!D3:D15)，这说明我在数据整理区偷懒没数，而是在输出区「两步并作一步」了。那么即便我此时不想修改，下个月再复用这个模板时我也会先行修改输出区的公式，不会跳进自己挖的坑。

同样的，「12 月没拉数字」因而保留了蓝色字体也是特殊情况，下个月修改时能一眼看到，不会让手动输入的数字蒙混过关。

对于需要协作的文档，由于这些颜色的使用并不是公司的统一规定，我会在「说明区」提前说明颜色的含义；对于自己不常使用的颜色，我也会提前说明防止自己遗忘——为什么这里把字标红了？为什么那个格子是黄色？这一列浅蓝色又代表了什么？

5. 不厌其烦地标注

为了增强表格的可读性，一定要不厌其烦地标注，这样才能保证读者通过表格就能了解想了解的所有内容。在进行复杂运算的过程中，这也是一个帮助自己保持思路的好方法。在制表过程中，我已经养成习惯并向大家推荐的有以下两种：

不要把表头留空

辅助列的表头是一个很常见的情况，我过去经常心里想着「这一列是用来标记是否抽到凭」「这一列用来作为 vlookup 的返回列」，觉得这么简单的事自己肯定不会忘记，就把表头留空在那。但经常过了两小时（好一点的情况是隔天），我就会对着空空的表头回忆自己加这行辅助列是干什么用的。（注：就标注方式来说，「y/n」这样什么时候看都明白的标识肯定比「1/0」来得好）

另外，即便是随手进行很小的运算，例如「我想算算这一列的平均数」「下面是除掉那一格得出的平均数」，我都会在运算格的左边空白处写明计算口径。不然转个头看到孤零零的两个数留在表格下方，还要回忆它们是哪来的真的有点恼人。

每一次思考都留下痕迹

如果在几种计算方法中选择了自己不常用的一种，我会在运算单元格旁备注选择的原因（是不是其他几种方法不行？）；如果某次讨论后否定了以前的计算方法，我也会进行标注（以防自己脑子一抽又换回了自己最熟悉的计算方法），勤快地备注可以免于重复进入同一个思考漩涡。

我一般会直接在当前区块的最右侧加入一条「备注」列，相关的文字说明都可以放去这一列。我不太推荐在密集的表格中使用自带的「附注」功能来标注，因为它在非隐藏形态很容易挡住临近单元格，而隐藏起来又不易查看。

6. 使用「绝对表述」

相对表述是指「本月」「环比」「同比」，这是一种需要在大脑中二次加工、甚至需要寻找额外佐证（例如回忆制表时间）的表述，而绝对表述是指「2020 年 1 月」「2019 年 1 月」「2019 年 12 月」。

在表头中我基本只使用绝对表述，「环比」这种相对表述只出现在文字分析中。而在不同表单中来回取数时，绝对表述也能帮助快速定位，不会把自己绕晕。

怎样让所有数据都可回溯：使用链接

当我们说一个 Excel 中的运算复杂，一般而言并不是说其中用到了微积分、线性代数和超难的函数，而是表格之间的勾稽关系可能非常复杂。整张表格用到的不过是加减乘除、平均数，但怎样将数据串联起来得到最后的运算结果，怎样从运算结果回溯到原始数据，这不是每个人都会下意识去做的，而一旦做好了就会让读者非常愉悦。

「这数是哪来的？」是以往工作中前辈和上级经常对我进行的灵魂拷问。在一开始不注意管理数据来源时，我总是被问得一脑门子汗，然后再凭借若有若无的记忆寻找来源。

而在有意识做这个工作后，我几乎再没听到过这个问题——通过 ctrl/cmd+[ 就能追溯回取数源，自然不用我张口回答。（后来我发现，我在做表的过程中会自然地对自己进行这样的灵魂拷问，所以这也可以算一个 Tips）

1. 永远使用链接取数

尽量让数据来源存在于同一个工作簿中（如果数据来源不庞杂，可以通过复制表单做到），并永远用「=单元格」进行取数，而不是复制粘贴和手打。

那么结合清晰的数据分区，可以稍微设想一下结果：输出区的结果来源于运算区的运算，运算区的运算则是在取数区的数据基础上加工得到。因此任何对数据来源有疑问的人，都可以通过运算结果回溯到原始数据。

这是非常简单的原则，应用起来就能让整张工作簿的不同分区互相关联。

2. 尽量保持取数区域固定

说一个常见的计算场景，如果在网站原始数据中以「三羊」为负责人筛选出了结果，现在需要计算平均数，有以下三种基本操作：

①使用 SUBTOTAL 函数 

=SUBTOTAL(101,三天阅读数)（101 代表取当前可见区域的平均数，给给数据区域起名见最后一部分）

②用 AVERAGEIF 函数包含筛选条件「负责人为三羊」，再计算出结果

=AVERAGEIF(负责,"三羊",三天阅读数)

③筛选➡️将筛选结果复制到空白处➡️对复制得到的结果用 AVERAGE 函数加总

以上三种方法中，方法①的速度最快，但得出的数据是临时的，因为筛选区域并不是为取得当前需要的数单独打造，一旦更改筛选标准，用 subtotal 函数得出的值就会变化；

方法②解决了数据「临时」的问题，加总数不会随筛选结果的改变而改变，但统计区域并不能直观展现，需要眼动排查，后续查看时可能还会有「筛选」这个操作；

方法③最笨，但结果直观且恒定。「筛选-复制-粘贴」的步骤就打造了专属的取数区域，只要这一步不出错，后续就几乎没有出错的可能。翻回上面取数区的图，我也是从原始数据筛选后粘贴在「取数区」。

注：并不是所有情形下都要使用方法③，在临时演示计算时我也会用到方法①，而方法②可以适应频繁更新（变化）的数据源。思考什么情形下用什么方法也很重要。

3. 链接到最后一个加工步骤

在上述「加工」取数区的过程中，一个工作簿中很自然地会在多处存在同一行数据。

但一步步加工的目的就是为了让最终的加工成果成为运算的直接取数来源，所以在计算时一定要取走最终加工形态的数字。如果计算过程中用到同一计算口径的数字，也一定要从一个加工场所取走。

例如上面通过方法③已经筛选出了我负责的文章，那么要统计文章的总阅读量、三天阅读量、互动量、选题和内容类型，都一致地从这一区域进行（即便在总表中也能得出相关数值）。

怎样进行版本管理

通常的文件版本管理想必大家都很擅长，Excel 的版本管理也没什么特别之处，这里只简单说我自己的工作习惯：

1. 原始数据和工作底稿分开保存

不论在动手之前认为自己对原始数据做的改动多么轻微——筛选、排序、加一个辅助列，都一定要留下一份原始数据的原貌。

我一般会用一个文件夹单独保存原始数据，要进行加工时就复制一份到「working」文件夹展开工作（底稿工作簿中的标注的「raw data」其实是复制件）。

2. 每一次修改都先另存为新的文件

如果打开后再另存为，很容易顺手保存覆盖了上次的工作成果，Excel 的历史版本又比较玄学，不是次次都能找回，因此在打开之前就复制成一个新文件是不太容易出错的方法（哪怕直到 v24）。

3. 在文件名中体现变化

在文件命名时，除了标准的日期+文件标题+版本号，我还会在文件名中体现重大变化，例如原始数据有更新（src-updated）、客户增加了修改意见（commented）、已经基于修改意见作出了更新（cleared），甚至把计算口径也写在标题里（使用 A 方法测算、使用 B 方法测算）。

如果觉得文件名太长不方便查看，我还会开一个空白的 txt 文件，把变动写在标题中作为分割线。例如：「200221.原始数据有更新.txt」，按文件名排列就会乖乖地待在更新底稿附近（在 windows 上随手新建比较方便，mac 参照 这篇文章）。

最想安利的几个原生功能

Excel 中可供使用的功能实在太多，我想安利以下几个趁手好用的小功能：

1. 打开新的视窗

如果要在表单之间取数，计算量大时来回跳转非常麻烦。推荐「视图-新建窗口」，为当前工作簿打开一个新视窗。

第一次知道这个功能时，我觉得办公室的天都亮了……

2. 给数据区域起名字

除了表单的名称，如果要频繁引用到一个区域，还可以给数据区域起名。这样可以让公式看起来更简单明了，在数据量大的情况下也不容易出错。

起名的方法非常简单，只要选中你想命名的区域，然后在左上角输入名称即可，中英文不拘。需要注意的是，如果在多个表单中都想给相同区域命名，那么最好带上表单名字，例如「三天阅读量1912、三天阅读量2001」（名称不能用数字打头）。

在「名称管理器」中也可以一次管理所有数据区域的姓名。

怎样最快比较两列本应重合的数字差异在哪？

这是上次同事向我提出的一个问题，我用 Vlookup 函数两次尝试不成，一时也检查不出错误，考虑到数据量不大，就用到了条件格式查出重复值。

条件格式还可以突出显示满足一定条件的数值（大于多少、小于多少、前/后 10%），也可以作为轻量的「筛选」使用。

4. 按颜色筛选

如果要进行两个筛选条件展示并集，在 Excel 中就会有点麻烦（在 A 列进行筛选时看不到 B 列，反之亦然）。

当然有很多种操作的方式，我一般会给两种筛选条件下的筛选结果涂色，然后再使用「按颜色筛选」得出结果（这也是很多在线表格缺失的功能）。

每个人使用 Excel 的习惯都有不同，你认为有哪些切实帮到你工作的好习惯？请评论区分享~如果你对我提出的观点有质疑，也欢迎在评论区讨论。

> 下载少数派 客户端、关注 少数派公众号，找到数字时代更好的生活方式 🎊

> 分享你提升效率的种种心得，参加  征文活动还能赢取效率工具 🛠️