25个人人都能学会的超实用Excel函数,同事都收藏了!

好久没送福利了,1分钱 跟卢子学函数25讲,欢迎将福利送给需要学习的朋友。

长按二维码购买,关注小鹅通知识助手,在我的已购看视频。


昨天的文章,粉丝在实际操作中,出现了点小问题。用下面的公式,会出错。

=SUMPRODUCT(--B2:B8)

--是将文本数字转换成标准数字,但是如果遇到空文本,是会出错的。

遇到这种,只需用0&区域,这样空文本就变成了0,而原来的数字在前面加0,实际大小并没有改变。--023=--23。

=SUMPRODUCT(--(0&B2:B8))

Excel细节很重要,稍微有点差异,结果就完全不同。

1.见分进角

根据社保缴费基数计算个人社保缴费金额时候,见分进角。与四舍五入不同,除了0,数字为1-9时都要向上进位加1。

很多人看到这里就想到ROUNDUP函数,没错,这个就是向上进位。不过只对了一半,当小数点第2位为0,第3位大于4的时候就出错。

正确的方法,应该将小数点第3位舍去,剩下的再进位。UP是向上,DOWN是向下,两者组合起来就可以。

=ROUNDUP(ROUNDDOWN(A2*0.005,2),1)

2.根据签订日期自动编制合同编号

合同编号为:FXZ+8位数日期+当天签订的次数递增。

如果一下子解决这个可能有点难,先来看个简单的。

将增值税专用发票号都为8位数字,统一补足位数。多少位数,就写多少个0。

=TEXT(A2,"00000000")

当天签订的次数递增,次数用COUNTIF累计,要统一变成两位数,将TEXT第2参数,写成"00"。

=TEXT(COUNTIF(B$2:B2,B2),"00")

日期转换成8位数字,也是用TEXT,将第2参数改成"emmdd"。e代表4位数的年,mm代表2位数的月,dd代表2位数的日。

=TEXT(B2,"emmdd")

最后,将公式合并起来。

="FRX"&TEXT(B2,"emmdd")&TEXT(COUNTIF(B2:B$3,B2),"00")

3.自动生成分录

根据左边的客户和金额,自动生成右边的分录。

借贷、科目这2个列,只需输入前2个,然后下拉就可以。

客户、金额是根据左边重复生成。ROW(A3)/2就是1.5,嵌套ROUND就得到2,相当于引用第2行的内容。ROW(A4)/2就是2,嵌套ROUND依然是2。其他依次类推。

=INDEX(A:A,ROUND(ROW(A3)/2,0))

如果需要重复3次,可以将公式略作修改。反正,只要是有规律的,都可以用类似的套路解决。

=INDEX(A:A,ROUNDUP(ROW(A4)/3,0))

4.合并账龄(合并3年数据)

这是两个看似不一样,实际上解决方法一样的案例。

往来单位有多条记录,要合并成一条记录。

要将三年的产品数据分别统计。

这种其实都可以用透视表解决,不过列数太多不太方便。用删除重复值+SUMIF操作起来更快捷,现在以其中一个为例进行说明。

将产品复制到G列,点数据,删除重复值,确定。

获取唯一值,现在就可以用SUMIF获取3年数据。B:B不锁定,这样右拉的时候就会变成C:C、D:D从而可以一次解决。

=SUMIF($A:$A,$G2,B:B)

其实,这个也能用前几天提到的合并计算,超级方便。

选择G1,点数据,合并计算,添加区域,勾选首行、最左列,确定。

这样就轻松搞定。

合并计算这个方法,也是写文章的时候突然想到的。

今天就分享到这里,明天见。

推荐:做财务真累,200万的回款额却为了1块钱的奖金纠结50分钟。。。

上文:财务必须熟练掌握的6个Excel函数!

你最想学什么教程?

作者:卢子,清华畅销书作者,《Excel效率手册 早做完,不加班》系列丛书创始人,个人公众号:Excel不加班(ID:Excelbujiaban)

(0)

相关推荐