excel日期格式改不了_Excel表格制作教程:想让办公更高效,你得先学会这张智能表!...

0938f36e300e027707a67594102f3e59.png

全套Excel视频,限时特价,扫码观看!

在会计工作中,经常遇到统计固定资产折旧的问题,如何通过一个表格将需要的折旧数据计算出来显示在一个表中作为各种数据汇总的依据,就是我们今天要讨论的问题。 作为一个比较完整的固定资产折旧表,大体需要这样几部分内容: 固定资产分类及折旧政策、基础数据录入区域、数据计算区域。 下面来看看具体的制作步骤:

1

固定资产分类及折旧政策

ac38f9eaf58168a483e55e73a3ea549c.png

根据公司相关规定,将固定资产分类、使用年限、折旧月数和净残值率等信息录入表格,单独存放于一个sheet备用。下面我的设计使用年限平均法计算折旧。

2

基础数据录入区域

b572747485675570007e00fb785a48db.png

配套练习课件QQ群:316492581下载

上图中A:I列是一些必须的信息,数据可以使用vlookup函数从资产台账中引用过来即可,这部分数据中需要注意的有以下几点:

1.记账月份

d8ca8c304a87ca863fae171a207f4d7b.png

输入的是日期,通过单元格格式设置为年-月显示的方式,这里必须使用日期方式录入,因为后面的公式会根据这个日期来计算摊销月数。

2.分类

b6fb046ffccb630aefdc2f5004672b63.png

这一列数据需要设置数据有效性,确保只能按照规定的内容进行录入,防止统计数据时出现错误。

3.表头处的日期

92d57e6cc22decfe4e52c970a96bbcf8.png

本例数据为2017年的数据,所以日期指定到2017年12月,实际使用中可以利用公式

=TEXT(TODAY(),"e年m月")得到最新的月份。

def58b5502b24e0deb8dd062d8fc2380.png

3

数据计算区域

ec72ec5201f17a18b1b8357abcbca2aa.png

这部分是整个表格的核心,一共有八项内容,都是使用公式计算得到的,以下对各列的公式进行解释。

预计残值:

=ROUND(I4*L4,2)

b2ca962ed55853cc5d94b0a77c896b0a.png

预计残值的计算方法为资产原值×残值率,在涉及到小数计算的时候偶尔会出现一分钱的误差(浮点运算的原因造成的),所以我们使用了ROUND函数进行处理,将I4*L4的结果四舍五入保留两位小数。

摊销折旧年限(月):

=VLOOKUP($D4,政策!$B:$E,3,0)

50790643b0811faf252d6d37f6545e1f.png

摊销折旧年限(月)就是政策中的折旧月数,这里直接使用VLOOKUP进行查找。

注意这里VLOOKUP的用法,VLOOKUP函数要求查找的内容(第一参数)位于查找区域(第二参数)的首列。当前查找范围是“政策!$B:$E”,并不是从A列开始的。同时在VLOOKUP函数中,第三参数指的是要找的数据位于查找区域的列数而不是位于表格中的列数,这里要找的折旧月数,在查找区域中是第三列,在表格中是第四列(D列),所以公式中写的是3。

e78cbab304e19a1ff002ae4dfc3f4ef4.png

残值率:

=VLOOKUP($D4,政策!$B:$E,4,0)

9882ecead9f5e1ff701e0e5dadb4cae8.png

同理,残值率也是直接使用VLOOKUP进行查找,第三参数为4。

月折旧额:

=ROUND(SLN(I4,J4,K4),2)

4aaa21ee5da1a96526e34a53115ca1a7.png

这个公式里用到SLN函数,下面简单介绍一下这个函数的用法:

fde5aab58482d484914699d5116545c3.png

函数的作用就是计算某项资产在一个期间中的线性折旧值,需要三个参数:资产原值(I列)、资产残值(J列)和折旧期数(K列)。

格式为:SLN(原值,残值,期数)。

同样,在外面加上ROUND函数,将计算出的折旧值四舍五入后保留两位小数。

累计摊销月数:

=DATEDIF($B4,$F$2,"M")

9b12685af334801dec967725171d9e4a.png

这里用到DATEDIF函数。Excel帮助中没有这个函数的说明,因为这是一个隐藏函数(隐藏函数是为了与一些非office软件兼容而存在的函数)。该函数的作用是得到指定日期区间内的年数、月数或者天数,格式为:DATEDIF(开始日期,结束日期,"类型代码")。开始日期为记账月份(B4),结束日期为记账周期的结束日期($F$2,因为要保证公式下拉时单元格不变,所以加了$进行锁定),类型代码M代表月数(Y代表年数,D代表天数)。想了解更多这个函数知识的小伙伴,可以查看往期教程《用上DATEDIF,您永不再缺席那些重要的日子!》

本月计提折旧:

=IF(N4>0,M4,0)

2339ce9965e4ca13d2c611bf9ee711c3.png

当摊销月数大于0的时候,本月计提折旧就是月折旧额,当摊销月数为零时,本月计提折旧也为零,因此使用IF函数来计算本月计提折旧,公式比较简单也容易理解。

累计折旧:

=M4*N4

6a895306c675f30d980a65e3e36c9947.png

累计折旧就是用月折旧额(M4)×累计摊销月数(N4)

净值:

=MAX(I4-P4,0),为了防止净值出现负数,使用了MAX函数取I4-P4和0的较大者,当折旧完成后,净值显示为0。

36c6f8cca3df2053cbb2f4f8bb0d6ee5.png

折旧超限提示:当资产净值折旧完成后,突出颜色显示提醒我们及时做报废处理,效果如图所示:

03a2927729d1b291f759767905970e29.png

这个可以通过设置条件格式来实现,具体方法为:

新建一个规则:

0e1fe920d1634e5b4f624b7def435adc.png

使用公式设置格式,公式为:

=$Q4=0,然后点击格式进行设置:

42777b923387a6bf98a1cbb07dd22ebc.png

设置填充色后点确定:

cae50de5656c9c28a80510788a607545.png

再次点击确定,点击管理规则调整变色单元格的生效范围:

b2a3d85fee0daba055f90f88079279d8.png

修改应用范围:

=$J$4:$Q$64

ba6dcdc42f3aeb442eb2120037adb06a.png

4

小结

在实际应用中,固定资产折旧明细表可以根据自己的需要增加其他计算项目,本文列出的只是一些常规项目。数据计算公式基本都是简单的公式、函数的运用,其中涉及到的DATEDIF函数是一个非常有用的日期函数,SLN函数是专门计算线性折旧值的函数。

通过这个固定资产折旧明细表,我们就可以得到各种汇总表,汇总表并没有统一的格式,都是根据自己的需求来设计,也不需要太复杂的函数,基本上用SUMIF就可以实现大多数需求。

关于折旧表,就说这么多,如果还有其他问题可以留言,对于大家提出的共性问题我们会整理出相关的教程。当然,如果经常用到公式函数的话,还是系统的学习比较好,如此才能有效地利用Excel灵活地解决我们遇到的各种问题。

扫一扫添加老师微信

207a8dc50608541d37f80c8d258e95ae.png

在线咨询Excel课程

8452301bcbc3c4b6a1a9a483bde2eada.png

Excel教程相关推荐

用上DATEDIF,您永不再缺席那些重要的日子!

比VLOOKUP好用10倍,你却只会用MAX求最大值?

VLOOKUP&LOOKUP双雄战(三):LOOKUP守得云开见月明

想要全面系统学习Excel,不妨关注部落窝教育的《一周Excel直通车》视频课或者《Excel极速贯通班》。

《一周Excel直通车》视频课

包含Excel技巧、函数公式、

数据透视表、图表。

一次购买,永久学习。

d6e197e6c0355b02b3690774b811844c.png

最实用接地气的Excel视频课

《一周Excel直通车》

风趣易懂,快速高效,带您7天学会Excel

38 节视频大课

(已更新完毕,可永久学习)

理论+实操一应俱全

主讲老师: 滴答

09c73449d7989fd4db9dc9e11cc41d33.png 

Excel技术大神,资深培训师;

课程粉丝100万+;

开发有《Excel小白脱白系列课》

        《Excel极速贯通班》。

原价299元

限时特价 99 元,随时涨价

少喝两杯咖啡,少吃两袋零食

就能习得受用一生的Excel职场技能!

  长按下面二维码立即购买学习

89c6135e0f93e886e2d8870680aed4ee.png

购课后,加客服微信:blwjymx2领取练习课件

cd7e5de9a793866aafcf219f87e0e5a9.gif

让工作提速百倍的「Excel极速贯通班」

↓ 点击,可直接购买。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值