2116阅读
1回复

[EXCEL使用技巧]为什么不用复杂的EXCEL函数 [复制链接]

上一主题下一主题查看指定楼层

离线饭团儿

财务副经理

发帖: 198

学分: 2615

经验: 946

精华: 1

金币: 0

只看楼主倒序阅读使用道具 0楼发表于: 2013-07-24

　　偶尔写篇技术性文章，本人一直从事数据相关的工作，EXCEL作为最日常的工作软件当然轻车熟路，虽然说不上非常精通但是略有心得。写此文的初衷是因为看到网上EXCEL技术论坛上将EXCEL过于神化以及过于偏向复杂表格函数和各种冷僻的功能，而忽略实用性和对数据本质的理解，过于注重技术而弱化了技术所要解决的问题本身。这种情况在笔者看来很像是在炫耀技术而不是解决实际的问题。
　　笔者将工作中会用到EXCEL处理数据的情况分为三大类：
　　1、自用型。这种情况下，EXCEL完全是一种计算工具，为了得到某个结果而使用EXCEL中的各项功能。实现过程只有使用者自己知道，不需要对别人公开，别人也不要应用这种实现过程。这种情况下不管使用什么手段都可以。
　　2、协作型。比如，我设计一张表格，为同事配置好逻辑和函数，同事只要在我固定的地方按预设的格式输入数据就可以得到某些数据结果。这种情况下，过程和结果都需要像他人公开，意味着要避免使用过于复杂的表格函数，因为你自己理解但同事不一定能理解，而且越复杂的函数越是脆弱：一般复杂函数可能需要嵌套多个基本函数外加各种IF条件，这让维护变得困难，函数灵活性差，而且容易造成复杂函数只有制作者自己看的懂，别人都不明白，一旦表格格式被变动（例如插入一行，或者不按规则填写数字、日期，多余空格等情况）函数会马上无效，他人也无法重新制作函数。
　　3、报表型。这种情况是直接面向领导的，所以必须屏蔽一切底层的报表实现过程。你不可能让领导给你下拉个函数，或者改个引用什么的。在数字实现上只能留下最简单的加减乘除，便于领导手动调整一些数字。报表型应用最重要的是理清数据之间的勾稽关系和业务关联，而不是EXCEL的数据实现技术。
　　从财务工作来讲，接触最多的是第二类和第三类情况。尤其是第二类来讲，就算自己技术再高深，但工作不可能全部一人来做，考虑同事的对EXCEL的理解和水平，就要在设计数据表和函数时考虑易用性的问题，TEAM WORK是乘法而不是加法，一人为零，全队白费。我们来看一个例子：
　　比如网上流传月薪的个税计算的EXCEL函数：
　　=ROUND(MAX(([月薪值]-3500)*0.05*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701},0),2)
　　这条函数无论从实现原理还是编制过程，恐怕一般人都无法在很短的时间内彻底理解，也很难更具实际情况调整公式。最主要是在，应用的时候基本没人会记住这么长的函数（还带了这么多数字），所以都是换到别的表格了就去翻出原来的公式然后贴过来，这过程中非常容易出错，应用难度高。如果在断网和找不到以前表格的情况下，估计没几人能把这个默写出来。
　　那么以下是笔者“写”的公式：
　　=mpt([月薪值])
　　很简单的一条函数，很快能被人记住。但这条函数并不是系统自带的，而是笔者在VBA上编写了月薪个税计算的程序，屏蔽了复杂的个税计算过程，包装成自定义函数。当然只能在限定的表格模板里使用（模板可以无限复制）。同上条一样函数一样，实现过程和原理都无法得知，但是应用上明显自定义函数要简单很多。只要在模板里使用这个函数就可以得到月薪个税，根本不需要记忆多个相互嵌套的复杂函数，在实际工作中，其他同事的使用难度会小很多。
　　可能你会说，自定义的函数用到了编程，实际上比单纯使用公式函数实现起来复杂多了。笔者认为，宁把复杂情况的留给自己，也不能把复杂的情况留给他人，否则问题不但得不到解决还会变得无法控制。自定义函数就是将复杂的逻辑屏蔽，展现出最简单方便的应用模式（编程一定要注意接口和扩展性）。
　　对于第三类情况，就很简单了，直接把结果贴出来（选择性复制-黏贴数值），放到固定格式的报表上，设计一下版式，发送给领导，注意你要考虑的不是表现自己 EXCEL技术多么厉害牛逼，而是要想着怎么向领导解释清楚数据之间的关系和深层业务逻辑，以及从自己的分析意见。对于表格技术上一定不能有外链，更不要用宏之类的东西，只留最简单的加减乘除。笔者见过有人发送给领导的文件里用了复杂的函数和功能，领导要一打开单元格就开始计算，卡死机器5分钟之后，发现有些数据外链的都显示为NA了，这样情况给人的印象是非常差的。
　　笔者在工作中遇到需要EXCEL编程的情况其实并不多，个人觉得甚至90%的问题都可以用VLOOKUP+SUMIF+COUNTIF+数据透视表解决（这里VLOOKUP和SUMIF并不涉及类似数组函数等复杂应用）。笔者也建议大家在EXCEL技术学习上优先掌握这4个功能。
　　很多人说自己对这四个功能了如指掌，但是业务太复杂仍然要用复杂的函数和功能。这里笔者想说一点，大部分EXCEL技术论坛上对函数公式掌握很好的人确实有很多，但很少有人接受过数据库训练，对数据范式并不了解，混淆了table和report的概念。如果能宏观上对数据表进行合理编排，很多复杂业务完全可以通过以上4个功能解决。
　　举个例子：工作中，很多人喜欢用交叉表（二维表），也就是列是属性，行是项目，然后在该范围内等级信息。这样来看似乎只要日常登记完报表就出来了。但这种登记方式只有在业务逻辑很简单，量很少的情况下才有效率，当交错信息不止一个时，例如行属性有100多个，列项目1000多个，那么这种混淆数据流水表和报表的登记方式就显得极为低效率，同时让筛选和分析变得困难。
　　笔者目前工作的公司每个员工都有体育活动经费，年500，新员工按照当年转正后的月份折算（3月转正，享有375=500*9/12的经费），一共5项体育活动，每周三下午固定活动费用也在那时发生，员工来报销时小姑娘要登记发生日期、员工姓名、参与活动项目、费用，最重要的是不能超支。原先公司小姑娘登记表格时，用了一张很复杂的二维表，将400个员工清单和每人可用经费作为行项目，将每个月的每个周三作为列项目（全年将近50列），交叉部分填写发生金额，5种颜色标注不同的活动内容。
　　这样小姑娘自己登记的时候就非常痛苦，因为员工递交给她的体育活动清单是一张excel列表（哪些人，时间，参加了什么活动，每人多少钱），人员顺序都是打乱的，她需要一个个人筛选，然后填到自己的表格里，这样非常低效，她自己做的也很累，最后领导问她要哪项活动参与人最多和参与经费最高这样可以去和对方谈折扣，由于不会按照颜色合计数字，她一筹莫展。
　　她问笔者有没有简单的方法，笔者说要在你的现有表格上实现可能要用很复杂的方法，但是改造一下你的表格，就可以很快得到结果了。笔者改造如下：将原先的单张表，拆分为3张表：基础档案表+流水表+结果报表。
　　基础档案表存放员工编号、员工姓名、可用经费总额+验证列(countif验证是否有人员档案重复)。
　　流水表，破除二维表模式，采用一维表列项目为：员工编号，员工姓名，部门，活动日期、活动项目、活动金额+余额列（按照员工姓名vlookup该员工当年总经费-sumif该员工已发生经费合计）。
　　报表：直接对流水表做数据透视，按照员工编号+员工姓名+部门+活动项目为分组，合计发生金额。
　　这样做的好处在于三张表之间关系独立而又清晰，基础表可以新建新员工，而不破坏流水表和报表的结构，可以直接复制人事部门发送的员工信息，不需要考虑顺序问题。对于流水表，日常登记简单方便，员工发送过来的excel活动清单只需要直接往流水表里复制即可，完全不需要一个个自己先去查询筛选再更新，大大节省时间，而且哪怕是某个员工中途更换了部门也不会对原先登记的数据产生影响。最后的数据透视表作为报表，每次有新的流水表更新只要刷新下数据透视表即可，不需要手动考虑增行，减少错误，而且由于数据透视表的特性，完全可以做各种统计分析，领导要求的那个活动项目发生最频繁只要筛选即可。远比原先表做颜色的数字合计要简单方便多。
　　由此可见笔者使用了拆表而不是单纯地去用非常复杂的方法实现颜色合计，拆表看上去只用了4个最基本的EXCEL功能，技术上完全是最简单的，但是带来的效果无论是易用性还是直观性都比原先的模式要好很多，并非复杂的函数而是合理的数据表编排才是解决问题的良方。拆表的原理也正式数据库方面的知识应用。
　　举这个列子是想告诉大家在实际工作中，使用 EXCEL不能只偏执于单纯地解决眼前的问题和在复杂问题上钻牛角尖，而是要更多更宏观地着眼于问题的本身，如何改进让问题本身变得简单才是最重要。
　　面对更加复杂的数据形式，个人并不建议固守EXCEL，灵活配合应用ACCESS会简单多，毕竟有些时候EXCEL要花很大力气才能实现的功能，在ACCESS上一句简单的SQL语言就能做到。
　　简单、直接、有效这就是EXCEL的使用之道。