excel 6种方法技能，让Excel效率加倍-英雄云拓展知识分享

在Excel中少犯2其实不简单，犹如要做到如何写好程序一样，需要引入一套方法论，在下面分步骤列出。

第一层：提升输入质量

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

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

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

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

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

第3层，检验运算进程

最简单粗鲁的方式，利用函数框中对输入不同色彩的标注，对运算进程及输入参数进行检查，看是不是到达预设或有犯2的过失。

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

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

侧重讲下图用红心标注的第4层和第5层

第4层，提升自动化程度

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

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

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

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

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

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

-OFFSET($M4,0,INT((COLUMN(Constant!A1)⑴)/2))+OFFSET($C4,0,INT((COLUMN(Constant!A1)⑴)/2)))

又比如，在第2层中，使用设置Check Point（检查站）的方式来检测3张报表是不是配平，但是这类土法炮制的方式只能避免最后的结果不能出错，而不能保证中间的状态和提升效力。为了偷懒和提高财务模型的硬朗性，将各类索引函数及数组函数用到极致，因而实现自动配平和检查。

函数写法是：=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))

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

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

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

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

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

• 变通性高，需要有灵活的框架快捷满足老板及客户多变的需求
• 复用性强，这个项目做得Financial Model，随意改改就可以投入到下一个绝不相干的项目中使用
• 硬朗性强，尽可能减少频繁的手工输入或操作，将原始数据集中在一个模块，改一个数据，相干的数据及模块自动更改

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

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

• 常数/关键数据/假定数据部份，包括：商业常数（汇率及税率等）、历史数据（过去的财报和市场范围的历史数据）、认为靠谱而不能改动的预估数据、关键假定（比如假定宏观经济依照6⑺%来增长）等等。这些数据略等于C语言的h文件部份，动一发而动全身，所以要单独对待。犹如程序一样，Excel的函数中是不能出现hard-code的数字，所以如果一个财务模型中出现“=2*3.14*r”，基本是可以打回去重做的。
• Scenario场景，包括：模型中需要常常调理的重要输入参数（比如：市场渗透率、Exit PE ratio等）。这些参数最好剥离出来成为一个单独的界面，可以比较方便的控制和调剂，为以后的Sensitivity Analysis做准备，乃至可能遇到在上文中提到的类似于用梯度降落法寻求最优值的情况。
• 基础模型。这一步的关键就是做出预估的3张财务报表，最使人痛苦的是配平。可使用各类复杂函数（Indirect/Offset/VLookup等）来进行配平而不会出错，而且复用性极高。
• 进阶模型。基于历史及预估的3张报表，做一些更复杂的财务分析或估值预估，包括：DCF、Comparable、敏感性分析等等。
• 显现。把用户（包括老板或客户）最关心的产出放出来，用最友好的界面展现出来。固然做得极致些，可以把调剂Scenario和重要参数的界面也放出来，方便用户Manipulate Data（其实翻译成中文更有趣一些：猥亵数据）以便得到最满意的结果。

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