长期资产（固定资产、无形资产、长期待摊费用等）的摊销，在会计学理论中是一个很简单的问题。但是在没有实现高度自动化的会计实务工作中，如果没有一个清晰准确的统计表，就很容易出现各种计量差错。例如新增加的资产忘记摊销，已经摊销完毕的资产还在继续摊销以及摊销尾数余留（超支）等。
本节中，我们以逸凡公司长期待摊费用为例，讨论如何用Excel来设计一套自动统计摊销数据的“长期资产摊销统计表”（简称“摊销统计表”，参见示例文件“表5-3 长期资产摊销统计表”）。
一、基本框架与功能展示
“摊销统计表”由长期资产摊销表（简称“摊销表”，见图5-45）和费用查询表（简称“查询表”，见图5-46）构成。
图5-45 长期资产摊销表
图5-46 费用查询表（输入查询参数后自动生成）
查询表是用于查询某个由用户指定期间各项资产当月摊销额、累计摊销额以及账面价值等信息的统计表。由于其显示的信息针对性更强，所以该表非常适合作为查询期间摊销记账凭证的附件。
我们的目标是：只需要在摊销表中手工录入资产名称、费用类型、资产原值、摊销期限、摊销起始期间和以前年度累计摊销额，在查询表中输入需要查询的期间，就可以自动实现以下功能。
1.主要信息功能
（1）自动计算统计年度各项资产的月摊销额、资产摊销的结束期间。
（2）自动计算统计年度各期间的摊销额并在摊销末期考虑四舍五入的偏差调整。
2.辅助信息功能
（1）自动计算统计年度每个期间的长期资产科目账面价值，摊销额按费用类别汇总。
（2）用户录入待查询期间后，自动统计该期间内每项资产的当期摊销额、累计摊销额和账面价值。
3.逻辑校验功能
（1）自动校验各期间费用类别汇总数与当期摊销额是否匹配。
（2）自动校验以前年度累计摊销额是否合理。
（3）自动校验自定义期间查询数据与摊销表对应数据是否匹配。
二、基本前提及假设
1.摊销方式为直线法，且不考虑残值和减值
2.不适用于固定资产
由于固定资产具有当月新增次月才开始摊销的特殊个性，为避免分析过于繁琐，本案例设计的“摊销统计表”仅适用于新增当月开始摊销的长期资产。
3.使用周期为一年
为避免跨度过大，本案例设计的“摊销统计表”适用周期为一个自然年度，跨年需结转。在实际工作中，可根据个人习惯对适用周期进行调整。
本表版式设计适用于预期长期资产项目较多的情况。如果预期长期资产项目较少，摊销表应该采用纵向排版设计，以方便阅读。
三、注意事项
由于日期型格式都是以“年月日”进行反映的（即使你把显示格式设置为“年月”，但是其实质还是“年月日”）。如图5-47所示，我们在A1单元格输入“2014年1月5日”，虽然通过设置使得单元格显示的是“2014年1月”，但是从编辑栏中，我们可以看到其真实面目实际上仍然是“2014-1-5”。
图5-47 日期单元格的真相
为了统一相关计算口径，在摊销表的使用过程中，需要规定：凡是日期型单元格，日的参数，均要求统一。实际操作时，建议只需要输入“某年某月”，系统将默认为期间的1日。例如输入“2014-1”（日期格式），按回车键确定后，该日期实际即为“2014-1-1”。
四、知识点装备
在阅读本节下面的内容前，请各位读者朋友首先确认大脑中是否已经基本装备了图5-48中的相关知识点。
五、主要信息的公式设计方法
在对“摊销统计表”的框架和相关知识点有了基本的认识后，我们就通过逸凡公司长期待摊费用的案例来讨论“摊销统计表”的设计了。
图5-48 相关知识点
【案例5-3】逸凡公司的长期待摊费用信息如图5-49（单位：元）所示。
图5-49 长期待摊资产清单
结合“摊销统计表”结构，相关设计方法如下。
1.月摊销额（G4:G13单元格区域）
月摊销额的函数没有什么难度，但是，我们需要用ROUND函数将摊销额控制在小数点后两位，以免引起累计尾数误差。此外，在第四章第一节关于IF函数的拓展应用中，我们提到的利用IF函数防止单元格出现报错的功能，就可以在此时派上用场。
G4单元格的公式为：=IF(F4=0,0,ROUND(E4/F4,2))
执行列填充后，G列就可以根据资产原值及摊销期限自动计算出该项长期资产的月摊销额了（见图5-50）。
图5-50 自动计算的月摊销额
2.摊销结束期间（I列）
摊销结束期间是指某项资产摊销的最后一期的期间。该数据由摊销起始期间和摊销期限计算而成。三者的逻辑关系为：
摊销结束期间-摊销起始期间=摊销期限-1
其中：摊销结束期间-摊销起始期间是指的两个期间之间的月份差。例如，摊销起始期间为2013年1月，摊销结束期间为2013年12月，则两者之差为11（个月）。
在这里，就需要打出一组组合拳函数来实现了，当然，还是不要忘了用IF函数来防范报错信息的出现。
I4单元格的公式为：=IF(H4="","",DATE(YEAR(H4),MONTH(H4)+F4-1,DAY(H4)))
执行列填充后，当我们在F列输入摊销期限、在H列输入摊销起始期间后，I4就可以自动计算生成匹配的摊销结束期间了（见图5-51）。
图5-51 自动计算生成摊销结束期间
3.期间摊销额（L4:W13单元格区域）
从现在开始，我们将进行稍微复杂一点的各期间摊销额计算的公式设计了。
我们首先来分析相关逻辑关系：对于一项长期资产来说，每一个期间有四种情况（见图5-52）。（假设该长期资产摊销期间为2014年5月～2015年4月。）
图5-52 长期资产在每个期间的四种情况
从图5-52可以看出，四种情况其实主要分两大类：
（1）第一类是摊销期间外，摊销额为0。
（2）第二类是摊销期内，由于大部分情况下长期资产总额并不是摊销期限的整数倍，所以月摊销额实际上是一个保留两位小数的四舍五入近似值。于是，在摊销的最后一期，摊销额不能直接用前面计算出的月摊销额的计算结果，而需要用倒算的方式（资产原值-前期累计摊销）来考虑对尾数的处理。
例如，某长期资产原值10000元，摊销期限30个月。则计算出的月摊销额为333.33元。但是，这个333.33元只是第1期～第29期的摊销额。最后一期（第30期）的摊销额如果仍然是333.33元，则总摊销额为9 999.90元（333.33×30），账面上就会留下0.10元的尾数误差。故最后一期的摊销额计算公式应为：10000-333.33×29=333.43（元）。
所以摊销末期的摊销额，需要通过资产原值减去前期累计摊销额的倒算法确定。
各期间考虑尾数处理的摊销额计算公式如图5-53所示。
从图5-53中可以看出，1月的公式和2～12月的公式之间不具备拓展性逻辑。所以，1月摊销额公式只需要考虑列填充，而在2月的公式中，就需要考虑区域填充了。
如果我们希望在1月就直接编制出能覆盖全年的区域填充公式，就需要让已摊销字段搬迁，使得以前年度摊销与各月摊销额打成一片。
图5-53 各期间处理尾数的计算公式
根据上述的逻辑分析，我们就可以按以下步骤来设计摊销额公式了。
（1）1月摊销额（L4:L13单元格区域）。
第一步：判断当前期间是否在摊销期内且不是摊销结束期间。
如果当前期间是在摊销期内且不是摊销结束期间，则说明当前期间大于等于摊销起始期间且小于摊销结束期间。此时，摊销额等于月摊销额。否则，进入第二步。
这里我们来回顾一下我们之前约定的一个问题——输入相关日期时，需要统一年月日中“日”参数的问题。这是因为在进行日期的大小（前后）判断时，是以“年月日”组团的方式比较的。而在本统计表中，只需要（准确说是只应该）对“年月”进行识别。如果不对日参数进行统一，则极有可能出现判断错误的情况。
例如：在“摊销统计表”的逻辑中，2013年4月和2013年4月比较本应该是相等的，但是如果一个日期实质为2013年4月1日，另外一个日期实质为2013年4月5日，则会判断为前者小于后者，将严重影响公式的合理运算。
L4单元格的第一步公式为：=IF(AND(H4<=L$3,L$3<I4),G4,进入第二步)
第二步：判断当前期间是否是摊销结束期间。
通过第一步的过滤，进入第二步的情形只可能是当前期间为摊销结束期间或当前期间不在摊销期内。
如果摊销期间等于摊销结束期间，则摊销额等于资产原值减去以前年度累计摊销，否则进入第三步，即不在摊销期内，其摊销额为0。
L4单元格的第二步公式为：=IF(L$3=I4,ROUND(E4-J4,2),0)。
将上述两个步骤的公式合并，即可得“2014年1月”摊销额的计算公式。
L4单元格的完整公式为：
=IF(AND(H4<=L$3,L$3<I4),G4,IF(L$3=I4,ROUND(E4-J4,2),0))
执行列填充后（因为尾数处理公式的原因，1月摊销额不适合采用区域填充），各项长期资产1月的摊销额就自动计算生成（见图5-54）。
图5-54 自动计算生成2014年1月摊销额
（2）2～12月摊销额（M4:W13单元格区域）。
M4单元格的公式为：
=IF(AND($H4<=M$3,M$3<$I4),$G4,IF(M$3=$I4,ROUND($E4-$J4-SUM($L4:L4),2),0))
执行区域填充后，各期间摊销额均可自动计算生成了（见图5-55）。（3）汇总数据的计算（E14单元格和J14:W14单元格区域等）。
E14单元格的公式为：=SUM(E4:E13)
执行行填充后，即可完成各参数的汇总计算。
图5-55 各月摊销额自动计算生成
六、辅助信息的公式设计方法
1.各期（合计）账面价值（J16:W16单元格区域）
资产摊销后的账面价值是除摊销额外最重要的一个指标。它不仅揭示资产当前的参考剩余价值，而且我们也需要用它与财务账上的账面价值进行核对，以确保当期摊销核算的正确性。
我们知道在不考虑减值的情况下：
账面价值=资产原值-累计摊销
也许有性急的读者朋友此时会踊跃的说：“这个简单，某月的账面价值不就是资产原值的合计减去以前年度累计摊销合计，再减去本年截至当月的摊销合计嘛。”其实笔者最开始就是这么想的，于是栽了个大跟头。
因为，这个思路忽略了长期资产项目会不断增加的情况。而新增的资产会引起资产原值合计的增加，但是累计摊销额不会增加。于是新增资产之前月份的账面价值，就会悄悄虚增。所以，这事儿还真没那么简单。
合理的逻辑应该是：
某月账面价值=某月当期已经存在的资产的原值合计-某月当期已经存在的价值的累计摊销额
那么，如何界定“某月当期已经存在”？毫无疑问，可以用当月是否有摊销额来判断。由于长期资产的存在必然会产生摊销。所以我们只把当期有摊销的资产的原值和累计摊销纳入公式计算的范畴。这时，又有读者朋友会问，已经摊销完毕的资产也属于已经存在的资产了，但是在当期是没有摊销额的。这个其实没有影响，因为我们在不考虑残值的情况下，已经摊销完毕的资产，其原值和累计摊销额是相等的。在被减数和减数同额增加或减少的情况下，并不影响计算结果。
找到了计算的逻辑，我们就可以讨论每个期间资产账面价值合计的问题了。
（1）期初账面价值。
J16单元格的公式为（见图5-56）：=SUMIF(J4:J13,">0",E4:E13)-J14
图5-56 期初账面价值的计算公式
（2）各期间账面价值。
从前面的逻辑分析可以看出，本案例中账面价值我们可以理解为与三个部分相关（见图5-57）。
图5-57 账面价值的相关参数
其中前两项的区域是固定的，而第三项的区域仅有左边边界（L列）是固定的，右边边界则会随着期间的变化而同步变化。所以，我们在设置1月账面价值的公式时，应考虑行填充。此时就需要用到数组公式了。
L16单元格的公式为：
{=SUMIF(L4:L13,">0",$E$4:$E$13)-SUMIF(L4:L13,">0",$J$4:$J$13)-SUM(IF(L4:L13>0,$L$4:L13)) }
需要再次提示一下：数组公式外的“｛｝”是输入完公式后，同时按【Ctrl+Shift+Enter】键确认时自动生成的，手工输入无效。
执行行填充后，则可完成1～12月账面价值的计算公式设置（见图5-58）。
图5-58 各期间账面价值的公式
2.摊销额按费用类别汇总
大多数情况下，不同的长期资产是归属于不同部门的。而不同的用途也使得其摊销额会计入不同类型的费用。于是，我们还需要制作一个摊销费用按费用类别进行汇总的统计表。
我们再次请出前面已经有过出场记录的SUMIF函数。有了前面的基础，我们就不再重复介绍相关设置原理，而直接给出相关公式了。
L17单元格的公式为：=SUMIF($C$4:$C$13,$C17,L$4:L$13)
执行区域填充后，我们就可以一目了然地看到每期摊销额中各个费用类型的金额了（见图5-59）。
如果我们还有按部门（隐藏在D列中）进行摊销额汇总统计的需求，也可参照上述方式处理。
3.查询指定期间信息
前面完工的摊销表都是以全局展现的形式传递相关信息的，但是有时我们需要从视觉上过滤掉不需要的信息，只对指定查询期间的信息做针对性更强的专门列示。比如，我们在制作每期资产摊销凭证的附件时，就只需要一个仅列示当期摊销信息的统计表。下面，我们将移师查询表继续讨论公式的设计。
图5-59 各期间按费用类别汇总摊销额
（1）序号等同步信息（A4:D13单元格区域）的公式设计。
为了使资产项目和资产原值与摊销表的信息同步，我们需要使用“=”将序号、资产名称、费用类型和资产原值四项固定信息进行同步关联。
A4单元格的公式为：=IF(摊销表!A4="","",摊销表!A4)
在设置资产名称的公式时，需要注意规避一个问题——表中不能出现查询期间尚未开始摊销的资产——否则，就成穿越剧了。据此，相关公式如下：
B4单元格的公式为：=IF(OR(摊销表!B4="",$E$2<摊销表!H4),"",摊销表!B4)
费用类型和资产原值规避穿越的措施就不用那么复杂了。因为只要资产名称没有穿越（不是空白），那么就说明一切正常。所以，我们在此只需要考虑向B列看齐，就一定是安全的。
C4单元格的公式为：=IF(B4="","",摊销表!C4)
D4单元格的公式为：=IF(B4="",0,摊销表!E4)
最后，同时对上述公式执行列填充，即可完成相关信息的同步（见图5-60）。
接下来，我们开始考虑如何实现变动信息的自动计算生成。
（2）本期摊销（E4:E13单元格区域）的公式设计。
本期摊销额公式设计的关键，是让我们能够在摊销表各月摊销额中引用到各项资产在查询期间指定期间的摊销额信息。直观地说，就是我们要实现用资产名称与查询期间两个维度，在摊销表的L4:W13单元格区域内，锁定一个准确的单元格并显示其数值。
图5-60 将查询表中的固定信息与摊销表进行关联
要完成这个工作，当然就需要我们的INDEX函数了。
INDEX函数的三个参数设置的分析如下：
1）区域参数是很清晰的，即摊销表的L4:W13单元格区域。
2）行坐标（资产名称锁定）其实就是我们的序号（不起眼的序号在这里起到了重要的作用）。
3）列坐标（查询期间锁定）稍微麻烦一点，由于查询期间是一个固定的单元格，所以我们需要一个以不变应万变的方法，可以随时带着查询期间显示的信息，去找到其在摊销表的L3:W3单元格区域中对应的座位号。这个时候，我们情不自禁地想到了INDEX函数的黄金搭档——MATCH函数。
E4单元格的公式为：
=IF(E2="","",INDEX(摊销表!$L$4:$W$13,A4,MATCH($E$2,摊销表!$L$3:$W$3,0)))
执行列填充后，本期摊销信息就可以根据查询期间的不同而自动匹配了（见图5-61）。
图5-61 自动计算生成的本期摊销额
（3）累计摊销（F4:F13单元格区域）的公式设计。
累计摊销实际上就是以前年度摊销额加上本年度1月至查询期间的累计摊销额。
以前年度累计摊销我们可以直接在摊销表的J列取数，而本年度1月至查询期间的累计摊销额，实际上就是在摊销表的L3:W3单元格区域中，满足不超过指定查询期间的单元格对应的资产摊销额的求和。
于是，我们终于用到了横版的SUMIF函数了。
F4单元格的公式为：
=IF($E$2="",0,摊销表!J4+SUMIF(摊销表!$L$3:$W$3,"<="&$E$2,摊销表!L4:W4))
执行列填充后，即可得查询期间对应的累计摊销额了（见图5-62）。
（4）账面价值（G4:G13单元格区域）的公式设计。
账面价值就没什么可说的啦，F4单元格的公式为：=D4-F4
执行列填充后，即可得各项资产在查询期间的账面价值（见图5-63）。
图5-62 自动计算生成的本期累计摊销额
图5-63 自动计算生成的本期账面价值
（5）统计各参数合计。
D14单元格的公式为：=SUM(D4:D13)
执行行填充后，查询表的设计就大功告成了。
七、逻辑校验信息的公式设计方法
1.摊销表的逻辑校验设计
（1）各期间费用类别汇总数与当期摊销额合计的校验。
各期间费用类别汇总数与当期摊销额合计的校验逻辑为：
各期各类费用合计=各期摊销额合计
假设我们设置逻辑校验正确时显示“OK”，逻辑校验错误时显示“偏差X（X为偏差金额，下同）”。
L21单元格的公式为：
=IF(ROUND(L14-SUM(L17:L20),2)=0,"OK","偏差"&ROUND(L14-SUM(L17:L20),2))
执行行填充后，我们便可以直观地看到每个月的摊销额和摊销额按费用分类汇总是否匹配了（见图5-64）。
图5-64 各月摊销额按费用分类汇总的逻辑校验
（2）以前年度累计摊销准确性的校验。
以前年度累计摊销是在年度结转“摊销统计表”时，根据各项长期资产实际记账的累计摊销额录入的数据。由于前期差错、调整事项等原因，很可能导致该实际数并不是正确的摊销数。所以，我们需要判断前期记账的累计摊销额是否有重大偏差，以确保后续摊销额的正确。
以前年度累计摊销的逻辑关系存在三种情况（见图5-65）。
图5-65 以前年度累计摊销逻辑关系的三种情况
第一步：长期资产在年初尚未开始摊销。
长期资产在年初尚未开始摊销，则其摊销起始期间不早于2014年1月。此时，当以前年度累计摊销为0时通过校验，否则应列示其差异，即J4单元格录入的金额。
K4单元格的第一步公式为：
=IF(OR(L3<H4,L3=H4),IF(J4=0,"OK","偏差"&J4),进入第二步)
第二步：长期资产在摊销中。
如果长期资产年初已在摊销中，则说明其摊销起始期间早于2014年1月（进入第二步后该条件已经必然实现），且其摊销结束期间不早于2014年1月。此时，如果以前年度累计摊销额等于月摊销额乘以以前年度摊销的期间数，则通过校验。
在以前年度累计摊销额这个公式中，月摊销额是现成的。以前年度摊销的期间数却需要计算。
我们知道，一年为12个月，那么我们把一个期间的年份数乘以12，再加上其月份数，得到的值其实就表示该期间是从公元1年1月（含）起的第几个月了。我们可以把这个值称为该期间的纯月份值。两个期间的纯月份值之差就是两个期间相差的月份了。
至此，我们的第二步公式思路就有了。
K4单元格的第二步公式为：
=IF(OR(L3<I4,L3=I4),IF(J4=G4*((12*YEAR(L3)+MONTH(L3))-((12*YEAR(H4)+MONTH(H4)))),"OK","偏差"&(J4-((G4*((12*YEAR(L3)+MONTH(L3))-((12*YEAR(H4)+MONTH(H4)))))))),进入第三步)。
第三步：长期资产在年初已经完成摊销。
通过前两步的过滤，进入第三步的情形只可能是年初时摊销已经完成。此时以前年度累计摊销额应等于资产原值才符合逻辑。
K4单元格的第三步公式为：
=IF(J4=E4,"OK","偏差"&(J4-E4))
将上述三个步骤的公式合并并考虑列填充，则得到了以前年度累计摊销的逻辑验证公式。
K4单元格的完整公式为：
=IF(OR($L$3<H4,$L$3=H4),IF(J4=0,"OK","偏差"&J4),IF(OR($L$3<I4,$L$3=I4),IF(J4=G4*((12*YEAR($L$3)+MONTH($L$3))-((12*YEAR(H4)+MONTH(H4)))),"OK","偏差"&(J4-((G4*((12*YEAR($L$3)+MONTH($L$3))-((12*YEA R(H4)+MONTH(H4)))))))),IF(J4=E4,"OK","偏差"&(J4-E4))))
执行列填充后，我们就可以对每项资产的以前年度累计摊销的数据进行复核验证了（见图5-66）。
图5-66 以前年度累计已摊销验证
从序号6开始，我们看到上述公式下，已摊销验证会默认资产信息为空白的项目为“OK”，如果读者朋友希望尚未录入资产信息的情况下验证结果也为空白，仍然可以再嵌套一层IF函数进行设置。
2.查询表的逻辑校验设计
查询表的源头是摊销表。所以，我们至少有必要校验查询表计算出来的各项合计数是否与摊销表的相关数据匹配。
由于本部分内容使用的函数均是老熟人，所以此处不再进行过多的公式分析，而仅就相关逻辑思路予以阐述。（1）资产原值合计数与摊销表对应数据的校验。资产原值合计数与摊销表对应数据的逻辑是：
查询表资产原值合计数=摊销表中在查询期间已经存在的资产的原值的合计数
这个公式告诉我们，甄别资产是否入围的标准是该资产的摊销起始期间应小于等于查询期间。只有满足这个条件的资产的原值，才能参与摊销表数据的求和计算。
D15单元格的公式为（见图5-67）：
=IF(ROUND(SUMIF(摊销表!H4:H13,"<="&E2,摊销表!E4:E13)-D14,2)= 0,"OK","偏差"&(SUMIF(摊销表!H4:H13,"<="&E2,摊销表!E4:E13)-D14))
图5-67 查询表资产原值的校验
（2）本期摊销合计数与摊销表对应数据的校验。本期摊销合计数与摊销表对应数据的逻辑是：
查询表本期摊销合计数=摊销表对应期间摊销额的合计数
这个公式告诉我们：要在摊销表的L14:W14单元格区域内，找出行坐标为1，列坐标为MATCH(E2,摊销表!L3:W3,0)的单元格。
E15单元格的公式为（见图5-68）：
=IF(ROUND(INDEX(摊销表!L14:W14,1,MATCH(E2,摊销表!L3:W3,0))-E14,2)=0,"OK","偏差"&(INDEX(摊销表!L14:W14,1,MATCH(E2,摊销表!L3: W3,0))-E14))
图5-68 查询表本期摊销的校验
（3）累计摊销与摊销表对应数据的校验。累计摊销与摊销表对应数据的逻辑是：
查询表累计摊销合计数=摊销表以前年度累计摊销合计数+本年截至查询当期摊销额的合计数
这个公式告诉我们：本年截至查询当期摊销额的合计数应该等于摊销表J14单元格加上摊销表L14:*14（*为查询期间对应的列号）单元格区域。
F15单元格的公式为（见图5-69）：
=IF(ROUND(摊销表!J14+SUMIF(摊销表!L3:W3,"<="&E2,摊销表!L14: W14)-F14,2)=0,"OK","偏差"&摊销表!J14+SUMIF(摊销表!L3:W3,"<="&E2,摊销表!L14:W14)-F14)
图5-69 查询表累计摊销的校验
（4）账面价值合计数与摊销表对应数据的校验。
账面价值合计数校验的逻辑与本期摊销合计数校验的逻辑近似。
G15单元格的公式为（见图5-70）：
=IF(E2="","",IF(ROUND(INDEX(摊销表!L16:W16,1,MATCH(E2,摊销表! L3:W3,0))-G14,2)=0,"OK","偏差"&(INDEX(摊销表!L15:W15,1,MATCH(E2,摊销表!L3:W3,0))-G14)))
图5-70 查询表账面价值的校验
网友评论