f8:nKb>nq$
偶尔写篇技术性文章,本人一直从事数据相关的工作,EXCEL作为最日常的工作软件当然轻车熟路,虽然说不上非常精通但是略有心得。写此文的初衷是因为看 到网上EXCEL技术论坛上将EXCEL过于神化以及过于偏向复杂表格函数和各种冷僻的功能,而忽略实用性和对数据本质的理解,过于注重技术而弱化了技术 所要解决的问题本身。这种情况在笔者看来很像是在炫耀技术而不是解决实际的问题。 lKf58
mB
笔者将工作中会用到EXCEL处理数据的情况分为三大类: MYS`@%ZV#k
1、自用型。这种情况下,EXCEL完全是一种计算工具,为了得到某个结果而使用EXCEL中的各项功能。实现过程只有使用者自己知道,不需要对别人公开,别人也不要应用这种实现过程。这种情况下不管使用什么手段都可以。 90Ki.K 0
2、协作型。比如,我设计一张表格,为同事配置好逻辑和函数,同事只要在我固定的地方按预设的格式输入数据就可以得到某些数据结果。这种情况下,过程和结 果都需要像他人公开,意味着要避免使用过于复杂的表格函数,因为你自己理解但同事不一定能理解,而且越复杂的函数越是脆弱:一般复杂函数可能需要嵌套多个 基本函数外加各种IF条件,这让维护变得困难,函数灵活性差,而且容易造成复杂函数只有制作者自己看的懂,别人都不明白,一旦表格格式被变动(例如插入一 行,或者不按规则填写数字、日期,多余空格等情况)函数会马上无效,他人也无法重新制作函数。 e'3V4iU]
3、报表型。这种情况是直接面向领导的,所以必须屏蔽一切底层的报表实现过程。你不可能让领导给你下拉个函数,或者改个引用什么的。在数字实现上只能留下 最简单的加减乘除,便于领导手动调整一些数字。报表型应用最重要的是理清数据之间的勾稽关系和业务关联,而不是EXCEL的数据实现技术。 JQ1MuE'
从财务工作来讲,接触最多的是第二类和第三类情况。尤其是第二类来讲,就算自己技术再高深,但工作不可能全部一人来做,考虑同事的对EXCEL的理解和水 平,就要在设计数据表和函数时考虑易用性的问题,TEAM WORK是乘法而不是加法,一人为零,全队白费。我们来看一个例子: Pao^>rj
比如网上流传月薪的个税计算的EXCEL函数: V+E8{|
dYL
=ROUND(MAX(([月薪值]-3500)*0.05*{0.6,2,4,5,6,7,9}-5*{0,21,111,201,551,1101,2701},0),2) v76Gwu$d
这条函数无论从实现原理还是编制过程,恐怕一般人都无法在很短的时间内彻底理解,也很难更具实际情况调整公式。最主要是在,应用的时候基本没人会记住这么 长的函数(还带了这么多数字),所以都是换到别的表格了就去翻出原来的公式然后贴过来,这过程中非常容易出错,应用难度高。如果在断网和找不到以前表格的 情况下,估计没几人能把这个默写出来。 ^^N|:80
那么以下是笔者“写”的公式: M&V'*.xz
=mpt([月薪值]) z{rV|vQ
很简单的一条函数,很快能被人记住。但这条函数并不是系统自带的,而是笔者在VBA上编写了月薪个税计算的程序,屏蔽了复杂的个税计算过程,包装成自定义 函数。当然只能在限定的表格模板里使用(模板可以无限复制)。同上条一样函数一样,实现过程和原理都无法得知,但是应用上明显自定义函数要简单很多。只要 在模板里使用这个函数就可以得到月薪个税,根本不需要记忆多个相互嵌套的复杂函数,在实际工作中,其他同事的使用难度会小很多。 xnZnbgO+
可能你会说,自定义的函数用到了编程,实际上比单纯使用公式函数实现起来复杂多了。笔者认为,宁把复杂情况的留给自己,也不能把复杂的情况留给他人,否则 问题不但得不到解决还会变得无法控制。自定义函数就是将复杂的逻辑屏蔽,展现出最简单方便的应用模式(编程一定要注意接口和扩展性)。 *:n~j9V-
对于第三类情况,就很简单了,直接把结果贴出来(选择性复制-黏贴数值),放到固定格式的报表上,设计一下版式,发送给领导,注意你要考虑的不是表现自己 EXCEL技术多么厉害牛逼,而是要想着怎么向领导解释清楚数据之间的关系和深层业务逻辑,以及从自己的分析意见。对于表格技术上一定不能有外链,更不要 用宏之类的东西,只留最简单的加减乘除。笔者见过有人发送给领导的文件里用了复杂的函数和功能,领导要一打开单元格就开始计算,卡死机器5分钟之后,发现 有些数据外链的都显示为NA了,这样情况给人的印象是非常差的。 >O-KJZ'GV
笔者在工作中遇到需要EXCEL编程的情况其实并不多,个人觉得甚至90%的问题都可以用VLOOKUP+SUMIF+COUNTIF+数据透视表解决 (这里VLOOKUP和SUMIF并不涉及类似数组函数等复杂应用)。笔者也建议大家在EXCEL技术学习上优先掌握这4个功能。 z\]Z/Bz:6
很多人说自己对这四个功能了如指掌,但是业务太复杂仍然要用复杂的函数和功能。这里笔者想说一点,大部分EXCEL技术论坛上对函数公式掌握很好的人确实 有很多,但很少有人接受过数据库训练,对数据范式并不了解,混淆了table和report的概念。如果能宏观上对数据表进行合理编排,很多复杂业务完全 可以通过以上4个功能解决。 &u>dKf)5
举个例子:工作中,很多人喜欢用交叉表(二维表),也就是列是属性,行是项目,然后在该范围内等级信息。这样来看似乎只要日常登记完报表就出来了。但这种 登记方式只有在业务逻辑很简单,量很少的情况下才有效率,当交错信息不止一个时,例如行属性有100多个,列项目1000多个,那么这种混淆数据流水表和 报表的登记方式就显得极为低效率,同时让筛选和分析变得困难。
A(FnU:
笔者目前工作的公司每个员工都有体育活动经费,年500,新员工按照当年转正后的月份折算(3月转正,享有375=500*9/12的经费),一共5项体 育活动,每周三下午固定活动费用也在那时发生,员工来报销时小姑娘要登记发生日期、员工姓名、参与活动项目、费用,最重要的是不能超支。原先公司小姑娘登 记表格时,用了一张很复杂的二维表,将400个员工清单和每人可用经费作为行项目,将每个月的每个周三作为列项目(全年将近50列),交叉部分填写发生金 额,5种颜色标注不同的活动内容。 A &