在会计工作中,经常遇到统计固定资产折旧的问题,如何通过一个表格将需要的折旧数据计算出来显示在一个表中作为各种数据汇总的依据,就是我们今天要讨论的问题。作为一个比较完整的固定资产折旧表,大体需要这样几部分内容:固定资产分类及折旧政策、基础数据录入区域、数据计算区域。 下面来看看具体的制作步骤: 一、固定资产分类及折旧政策 根据公司相关规定,将固定资产分类、使用年限、折旧月数和净残值率等信息录入表格,单独存放于一个sheet备用。下面我的设计使用年限平均法计算折旧。 二、基础数据录入区域 上图中A:I列是一些必须的信息,数据可以使用vlookup函数从资产台账中引用过来即可,这部分数据中需要注意的有以下几点: 1.记账月份 输入的是日期,通过单元格格式设置为年-月显示的方式,这里必须使用日期方式录入,因为后面的公式会根据这个日期来计算摊销月数。 2.分类 这一列需要设置数据有效性,确保只能按照规定的内容进行录入,防止统计数据时出现错误。 3.表头处的日期 本例数据为2017年的数据,所以日期指定到2017年12月,实际使用中可以利用公式 =TEXT(TODAY(),"e年m月")得到最新的月份。 三、数据计算区域 这部分是整个表格的核心,一共有八项内容,都是使用公式计算得到的,以下对各列的公式进行解释。 预计残值: =ROUND(I4*L4,2) 预计残值的计算方法为资产原值×残值率,在涉及到小数计算的时候偶尔会出现一分钱的误差(浮点运算的原因造成的),所以我们使用了ROUND函数进行处理,将I4*L4的结果四舍五入保留两位小数。 摊销折旧年限(月): =VLOOKUP($D4,政策!$B:$E,3,0) 摊销折旧年限(月)就是政策中的折旧月数,这里直接使用VLOOKUP进行查找。 注意这里VLOOKUP的用法,VLOOKUP函数要求查找的内容(第一参数)位于查找区域(第二参数)的首列。当前查找范围是“政策!$B:$E”,并不是从A列开始的。同时在VLOOKUP函数中,第三参数指的是要找的数据位于查找区域的列数而不是位于表格中的列数,这里要找的折旧月数,在查找区域中是第三列,在表格中是第四列(D列),所以公式中写的是3。 残值率: =VLOOKUP($D4,政策!$B:$E,4,0) 同理,残值率也是直接使用VLOOKUP进行查找,第三参数为4。 月折旧额: =ROUND(SLN(I4,J4,K4),2) 这个公式里用到SLN函数,下面简单介绍一下这个函数的用法: 函数的作用就是计算某项资产在一个期间中的线性折旧值,需要三个参数:资产原值(I列)、资产残值(J列)和折旧期数(K列)。 格式为:SLN(原值,残值,期数)。 同样,在外面加上ROUND函数,将计算出的折旧值四舍五入后保留两位小数。 累计摊销月数: =DATEDIF($B4,$F$2,"M") 这里用到DATEDIF函数。Excel帮助中没有这个函数的说明,因为这是一个隐藏函数(隐藏函数是为了与一些非office软件兼容而存在的函数)。该函数的作用是得到指定日期区间内的年数、月数或者天数,格式为:DATEDIF(开始日期,结束日期,"类型代码")。开始日期为记账月份(B4),结束日期为记账周期的结束日期($F$2,因为要保证公式下拉时单元格不变,所以加了$进行锁定),类型代码M代表月数(Y代表年数,D代表天数)。 本月计提折旧: =IF(N4>0,M4,0) 当摊销月数大于0的时候,本月计提折旧就是月折旧额,当摊销月数为零时,本月计提折旧也为零,因此使用IF函数来计算本月计提折旧,公式比较简单也容易理解。 累计折旧: =M4*N4 累计折旧就是用月折旧额(M4)×累计摊销月数(N4) 净值: =MAX(I4-P4,0),为了防止净值出现负数,使用了MAX函数取I4-P4和0的较大者,当折旧完成后,净值显示为0。 折旧超限提示:当资产净值折旧完成后,突出颜色显示提醒我们及时做报废处理,效果如图所示: 这个可以通过设置条件格式来实现,具体方法为: 新建一个规则: 使用公式设置格式,公式为: =$Q4=0,然后点击格式进行设置: 设置填充色后点确定: 再次点击确定,点击管理规则调整变色单元格的生效范围: 修改应用范围: =$J$4:$Q$64 四、小结 在实际应用中,固定资产折旧明细表可以根据自己的需要增加其他计算项目,本文列出的只是一些常规项目。数据计算公式基本都是简单的公式、函数的运用,其中涉及到的DATEDIF函数是一个非常有用的日期函数,SLN函数是专门计算线性折旧值的函数。 通过这个固定资产折旧明细表,我们就可以得到各种汇总表,汇总表并没有统一的格式,都是根据自己的需求来设计,也不需要太复杂的函数,基本上用SUMIF就可以实现大多数需求。 关于折旧表,就说这么多,如果还有其他问题可以留言,对于大家提出的共性问题我们会整理出相关的教程。当然,如果经常用到公式函数的话,还是系统的学习比较好,如此才能有效地利用Excel灵活地解决我们遇到的各种问题。 原创:老菜鸟/部落窝教育(未经同意,请勿转载) |
|
来自: 部落窝教育BLW > 《部落窝excel/VBA》