excel 6种方法技巧，让Excel效力加倍-利剑分享-科技生活

excel 6种方法技巧，让Excel效力加倍

在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做数据分析的基本思想其实和编程非常类似，许多框架都可以参考编程思想，这样就能提高效率和降低出错概率。

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