6种方法，让Excel效力加倍

“如何在Excel中少犯二”。从这个话题开始的理由特别简单：首先，时常在别人的Excel数据模型中发现各种细小错误，哪怕作者花费再多的时间把图表搞得再精美，顿时对整个模型的结论产生怀疑；其次，Excel数据模型是经常要在现场分享并不断调试数据，经常看见作者手忙脚乱得更改输入和公式，忙中经常出错。

巴菲特曾说，犯错少就是成功。特别是那些重大而简单的弱智错误。这个理论套用到Excel做数据模型上面，一样成立。对于Excel这种人人都会的工具，少犯二的目标看似简单。然而，量变产生质变，当成千上万的数据堆积在一起，同时带来千丝万缕的函数关系，少犯二就会成为一个高大上的目标。如果不信，可以试试从1开始写数字写到600，能否保证一字不差（这是一个经典的街头骗局）。

因此在Excel中少犯二并不简单，如同要做到如何写好程序一样，需要引入一套方法论，在下面分步骤列出。

第一层：提升输入质量

通过使用“数据有效性”功能，减少输入的错误，设置输入的有效性检查（比如：手机号码是11位等等），尽量避免"Garbage In, Garge Out"。

Excel数据模型中，存在不同类型的数据：常数、可调参数、中间结果、最终结果等等。不少的错误发生在混淆各种类型的数据或者更改了不能调整的中间结果而污染最终结果。因此可以考虑对不同的数据，用颜色或者worksheet进行区隔，下图左方就是利用颜色标注不同的数据：有些是供修改的，有些是最后结果及中间过程而不能修改。这些措施都是为了在模型的制作过程以及调试过程中，避免误改误删数据。如果要想做到极致，还可以使用下图右方的方法，直接利用“保护工作表”功能，对不能修改的数据进行保护。

另外，对于一些经常使用的输入区域（比如，A1:A299），往往反复出现在Sum或者Vlookup等函数中，可以考虑将它们定义成变量，并可以在“名称管理器”中进行修改、增加和删除等管理。反复使用的时候就会非常方便，比如：=Vlookup(A1,data,2,false)这种简单的写法。

第二层，提升输出质量和增加输出维度

不少人在用Excel输出结果的时候，往往就事论事，把输出局限在较小的范围，不仅容易忽略错误，而且会失去多个视角。但如果能够在现有输出结果的基础扩展审视输出的维度，就能获得更全息的视角。以财务三张报表而言，当然可以妥妥得输出经典的报表格式，但如果能够多计算一些财务指标并放在合适的位置（如下图中所示的EBIDTA、Cash flow from operation、EBIDA/Interest Expenses等等），不仅可以部分校验运算过程，还能从多个维度反应企业的财务状况，当然也更容易发现可能存在的计算误差，从而提升输出的质量。

第三层，检验运算过程

最简单粗暴的方式，利用函数框中对输入不同颜色的标注，对运算过程及输入参数进行检查，看是否达到预期或者有犯二的差错。

升级一些，可通过“追踪引用/从属单元格”，对运算过程进行检查，尽可能避免公式运算中输入参数的犯二。

再升级一些，可以对一些关键指标以及核心等式就行复检。比如对于财务报表中最基础的等式“资产=负债+权益”，在做Financial Modeling的过程中，都要设置专门的一行进行检查，类似于化学方程式的配平检查。

着重讲下图用红心标注的第四层和第五层

第四层，提升自动化程度

就像在IDE中使用了关键词提示以及经常检查Code Review，还是不能写好程序一样，这里面还有套路。

Excel中的错误经常发生在不断的手工人肉操作，再简单的事情做个几十遍或者几百遍，出错的概率也会非常低。因此在Excel中可通过使用系统工具、高阶函数甚至VBA来提高自动化程度，避免反复输入函数或者重复操作，就能大大降低出错概率。

比如，逐渐学会使用Excel自带的丰富数据清洗（排序、筛选、根据统一分隔符来分隔数据等）及分析工具（包括高阶的统计工具，ANOVA及多元线性回归等等一个都不少），减少人肉人工参与的过程。

再比如，下面表格中，要求白色区域中的矩阵元素等于所在行、列及worksheet上对应数字的总和。最笨的办法是每个单元格写一次加总函数，重复几十次值几百次（应该会有许多张worksheet），非常容易出错；进阶的办法是利用绝对地址和相对地址，写一次函数，整个矩阵的函数拷贝粘贴就完成，出错概率大大降低，但是每出现一个新的表格就要更新函数，仍然有出错的不低概率；最高级的办法就是在上一个办法的基础上，利用CELL函数获取Worksheet的名字并提炼数字，然后一气呵成，整个表格的函数完全是动态的，Worksheet复制之后只要改成相应的名字就可以完成任务，在出错方面的鲁棒性很强。

又比如，制作Financial Modeling的时候经常需要将季度或者半年度数据汇总成年度的（或者反向实施），一般的做法都是写加减等简单的函数，然而却不能成块拖拽或者复制函数而需要手工不断写函数，不仅麻烦而且容易出错，利用Offset等函数，可以写好函数就一步成型，完成整个过程。

函数写法是：
=IF(MOD(COLUMN(Constant!A1),2)=1,OFFSET($M4,0,INT((COLUMN(Constant!A1)-1)/2)), 
-OFFSET($M4,0,INT((COLUMN(Constant!A1)-1)/2))+OFFSET($C4,0,INT((COLUMN(Constant!A1)-1)/2)))

又比如，在第二层中，使用设置Check Point（检查站）的方式来检测三张报表是否配平，然而这种土法炮制的方式只能防止最后的结果不能出错，而不能保证中间的状态以及提升效率。为了偷懒和提高财务模型的健壮性，将各类索引函数及数组函数用到极致，于是实现自动配平以及检查。

函数写法是：=SUM(('Balance Sheet'!$AA$8:$AA$100='Cash Flow'!$B44)*('Balance Sheet'!O$8:O$100-'Balance Sheet'!N$8:N$100)*('Balance Sheet'!$AB$8:$AB$100))

第五层，使用先进的“编程思想”

以上都是技法，让编程真正成为一门科学或者手艺的是，里面存在心法或者思想。围绕着这些编程思想，构建出一套套体系：MVC框架、MVP框架以及OO等等。这些体系的目的大概都是提高工作效率、复用率以及鲁棒性等等，都是多快好省少出错得完成任务。然而世间万物，不少都是触类旁通。利用Excel做数据分析的基本思想其实和编程非常类似，许多框架都可以参考编程思想，这样就能提高效率和降低出错概率。

所以归根结底，还是要做“有思想”的人和“有思想”的事。

Excel最大的实战价值就是制作各类财务模型（Financial Model）或者简单的数学模型，用正确的方式方法来做模型（所谓的“套路”）才是心法。

比如可以借鉴著名而老套的MVC到Excel的Financial Modeling，实战性强且效果好。将构建Financial Model的逻辑被分成三层， Model（负责数据），View（负责呈现）和Controller（负责业务逻辑），理想状态下其中一层的改动不会影响到另一层。

• 灵活性高，需要有灵活的框架快速满足老板及客户多变的需求

• 复用性强，这个项目做得Financial Model，随便改改就能投入到下一个毫不相关的项目中使用

• 健壮性强，尽量减少频繁的手工输入或者操作，将原始数据集中在一个模块，改一个数据，相关的数据及模块自动更改

在做大部分Financial Model的时候基本就是按照MVC的框架来要求自己的。

Financial Model搭建的过程就如同修建高楼一层层往上累加模块

• 常数/核心数据/假设数据部分，包括：商业常数（汇率及税率等）、历史数据（过去的财报以及市场规模的历史数据）、认为靠谱而不能改动的预测数据、核心假设（比如假定宏观经济按照6-7%来增长）等等。这些数据略等于C语言的h文件部分，动一发而动全身，所以要单独对待。如同程序一样，Excel的函数中是不能出现hard-code的数字，所以如果一个财务模型中出现“=2*3.14*r”，基本是可以打回去重做的。

• Scenario场景，包括：模型中需要经常调节的重要输入参数（比如：市场渗透率、Exit PE ratio等）。这些参数最好剥离出来成为一个单独的界面，可以比较方便的控制和调整，为之后的Sensitivity Analysis做准备，甚至可能遇到在上文中提到的类似于用梯度下降法寻求最优值的情况。

• 基础模型。这一步的核心就是做出预测的三张财务报表，最令人痛苦的是配平。可以使用各类复杂函数（Indirect/Offset/VLookup等）来进行配平而不会出错，而且复用性极高。

• 进阶模型。基于历史及预测的三张报表，做一些更复杂的财务分析或者估值预测，包括：DCF、Comparable、敏感性分析等等。

• 呈现。把用户（包括老板或者客户）最关心的产出放出来，用最友好的界面展现出来。当然做得极致些，可以把调整Scenario以及重要参数的界面也放出来，方便用户Manipulate Data（其实翻译成中文更有趣一些：猥亵数据）以便得到最满意的结果。

下图是曾经奋战过的一个Financial Model，基本涵盖了上述的逻辑和构建过程，供大家参考。

何明科，曾服务于BCG和软银赛富等金融机构，曾在美国Mellon Capital担任Trading Analyst，从事Trading Cost定量研究，前快播SVP，负责部分产品的技术研发，图像技术及AI研究。目前专注于数据和互联网产品中。（微信公众号：数据冰山）

本文由专栏作者 @数据冰山 原创发布于产品社区，未经许可，禁止转载。