那些我常用的excel小技巧之函数篇
在之前的帖子里,有她蜜留言说让阿卡写一写工作中常用的办公技能,今天刚好有空,就来写一写。
1、SUM求和家族,包括SUM,SUMIF,以及SUMIFS
作为财务工作人员,少不了要和数据打交道,最常见的就是求和,所以SUM家族是阿卡最常用的函数,其中,单一数据求和,用SUM;附条件求和,用SUMIF;如果附的条件比较多,上SUMIFS。
2、SUMPRODUCT
这个函数也是阿卡常用之一,SUM是求和,PRODUCT是乘积,两者综合到一起,就是对各个数组进行计算并且相乘,最后返回乘积之和,这是基础用法,同时还可以像SUMIF一样嵌套条件,按条件返回数组的乘积。
3、COUNT计数家族,包括COUNT,COUNTA,COUNTIF,以及COUNTIFS
这个函数阿卡在计算考勤的时候用的最多,现在是设置好了表格模板直接给助理用了,每个月自动统计。用法也很简单,感兴趣的她蜜可以试着研究一下。
4、字符提取系列,包括MID,RIGHT,LEFT,CONCAT,以及PHONETIC
这个系列阿卡在每月合并报表的时候用的最多,主要是提取字符串,有时候也会帮办公室的同事从不规范的文本内提取一些重要字段。
用法同样很简单,MID是从中间提取,RIGHT是从右边提取,LEFT是从左边提取,至于CONCAT和PHONETIC,是将提取出的字符进行整合。
5、TEXT和TEXTJOIN
TEXT这个函数的用法千变万化,但阿卡知道和常用的就有六七种组合方式,分别解决不同的问题,这里不做一一阐述,感兴趣的她蜜可以研究研究。
至于TEXTJOIN,字符链接,用法也是很多,阿卡经常用他来组合和拆分不同的会计科目,用于合并报表。
6、VLOOKUP和LOOKUP查找家族
这个不用多介绍,VLOOKUP号称函数界的大众情人,跟谁都能来电,缺点就是不能逆向查找,但没关系,这个缺点LOOKUP可以堵上,正向反向随便什么向,想用什么姿势怎么查就用什么姿势查。
这里提一下阿卡最常用的LOOKUP经典一对多,俗称以一敌百/以多欺少,长这样:=LOOKUP(1,0/(($A$2:$A$=E2)*($B$2:$B99$=F2)),$G$2:$G$99)
这个套路阿卡真是百用不厌,套路也很简单,=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),返回区域),每个月对账分分钟完成。
7、MATCH和INDEX双剑合璧
这两个函数,每个单独拆开来用没有什么两点,平平无奇,但结合在一起用,就很威力无穷,比如:
=INDEX($A$2:$A$12,MATCH(0,0/((F2=$B$2:$B$12)*(F2=$D$2:$D$12)),0))
简直合纵连横无所不能,阿卡经常用它来快速匹配数据,尤其是其他部门交上来的表格杂乱无章,数据不规范的时候,非常好用。
8、INDIRECT
这个函数主要作用是引用,单个函数阿卡用的少,最常用的是跟其他函数一起嵌套使用,比如下面这个用法:
=SUM(OFFSET(INDIRECT("'"$B5"'!B:B"),0,COLUMN(A1)))/2
这条函数是阿卡做年度预算的时候用的,目的是引用每个月的预算数据进行比对分析,直接引用,节约了合并计算每月的数据的麻烦。
9、OFFSET
这个函数主要作用是“漂移”,用于定位目标数据所在,单个用的少,也没什么特色,经常和其他函数一起组合使用,比如:
=OFFSET(原始数据!$A$1,MATCH(B2"*",原始数据!$A:$A,0)-1,,COUNTIF(原始数据!$A:$A,B2"*"))
10、IF
这个函数也是阿卡的心头好,之所以放在最后,是因为此函数跟VBA关系匪浅,在函数界IF也许资质平平,但到了VBA的世界,跟FOR……NEXT一结合,那就是所向披靡。
11、函数嵌套
所谓嵌套就是将函数互相结合使用,因为有时候单个的函数只能解决单一问题,但实践中经常出现多种需求,这时候将两三个或者四五个函数按照不同的顺序嵌套在一起使用,就能一招解决所有需求。
比如,上面提到过的MATCH和INDEX双剑合璧,就是阿卡常用的嵌套函数,这里面只嵌套了两个函数,算是比较简单的。
还有阿卡之前比较常用的个税计算函数,长这样:
=MAX(ROUND(MAX((H4-I4)*{0.03;0.1;0.2;0.25;0.3;0.35;0.45}-{0;2520;16920;31920;52920;85920;181920},0)-J4,2),0)
这个之前用于计算员工个税,计算结果跟税务系统几乎没有差异,还是比较准确的,后来用了新版的财务软件,这个公式就此闲置了。
再比如,阿卡最最最最常用的,万金油公式,长这样:
=IFERROR(INDEX(数据源!B:B,SMALL(IF((数据源!$L$5:$L$19=SUBSTITUTE($A$1,"合并报表",""))*(数据源!$R$5:$R$19=$O$1),ROW($5:$19)),ROW(A1))),"")
用这个公式,阿卡每月只需三秒就能完成合并查询,可以说想查什么查什么,想怎么拆分就怎么拆分,工作效率提升了N倍不止。
再再比如,凑金额,做财务的想必都经历过发票金额拆分,客户或供应商一个金额分好几笔开票,时不时还有红冲等等,以前每个月助理都会凑金额凑到抑郁,后来阿卡研究了一下,每月只需要填入最终要凑的金额,自动出数据,这个函数长这样:
=IF(D$2,IF(E$2,D4,IF(SUM($C4:C4),"",IF(SUM(D$4:D$100)=D$1,D4,$B4*INT(RAND()*2)))),"")
=IF(SUM(A$2:A$13)=20000,A2,RANDBETWEEN(500,2000))
感觉就是函数嵌套可以解决所有问题,那句话怎么说,众人拾柴火焰高,一个人的力量毕竟有限,用在函数界也是一样,一群函数嵌套,比单一的函数威力更大。
好了,函数篇先写到这里,后面会写阿卡最喜欢的VBA,我们下一篇见。
今日以上。
更新于:20天前