不急,先听一个故事,然后再学Excel。
读者:我们岗位有一项需要根据预算核算毛利,我用了Excel,不仅能判断结果,还能显示毛利不足差多少,呵呵,我们经理今天竟然当众批评不让用,一定要让用计算器核算,我真TM服了。
对于这事我不做点评,卢子说一件自己的事吧。2010年的时候,卢子开始尝试将工作上的数据采用Excel进行计算,前期制作模板花了很多时间,后面只要每月将数据录入就能出汇总表。
卢子就跟同事说,要不你也用我的这个模板吧。同事很不屑的说,不用了,我按计算器也很快就统计完。卢子知道改变一个人很难,所以默默的做自己的事,直到2012年离开这家公司,临走前还交代所有的表格模板使用,但是至今没收到任何反馈。没有问题就是最大的问题,也就是说她们依旧没有使用,而继续采用计算器统计。
这么多年过去了,她们现在究竟还在做什么工作我不得而知。但是,你现在看的每一篇教程却出自一个叫卢子的人之手。成长总是孤独的,不要试图改变所有人,你能改变的只有自己。当你足够强大的时候,你会逐渐影响到身边的每一个人,就比如说,现在每天有不少读者6:40起来看文章。
说了不少废话,现在说今天的Excel真实案例。
隔3行统计目标、实际和差异三个内容,看这公式,+号被运用的炉火纯青,有如按计算器一样快准狠!
看到这种卢子也是没办法,计算器早已经被我扔掉,+这个按键还真不好使,怎么办呢?
1.通过观察数据源可以知道,目标合计就是对所有包含目标的单元格进行求和,这个就是条件求和。
=SUMIF(B5:B34,'*目标*',C5:C34)
'*目标*'就代表包含目标的意思。
同理,实际合计可用:
=SUMIF(B5:B34,'*实际*',C5:C34)
对于痴迷于技术的人,可能会不屑于用这2条公式,而是合并成一条。在C35输入后下拉即可完成统计。
=SUMIF($B$5:$B$34,'*'&LEFT(B35,2)&'*',$C$5:$C$34)
LEFT函数的作用就是将目标和实际两个字提取出来。
2.回归另一种思路,这种不就是隔3行求和吗?
目标合计5、8、11……都是隔着3行,而这些数字除以3的余数都是2,可以借助这个特点来实现。
=SUMPRODUCT((MOD(ROW(5:34),3)=2)*C5:C34)
MOD函数就是获取余数的。
而实际合计6、9、12除以3的余数是0,也就是将2改成0即可。
=SUMPRODUCT((MOD(ROW(5:34),3)=0)*C5:C34)
作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)
联系客服