一张表，固定资产折旧测算、分类汇总，公式一拖全搞定

      给大家介绍一个 固定资产测算及分类汇总的Excel工作簿。首先丢出下载链接，百度网盘下载。

固定资产表格下载（百度网盘）

      首先来验验货，这份工作簿中包含两个工作表。

分类汇总表：

折旧测算明细表：

      首先，在"分类汇总"表格的[I3][I4]单元格分别录入审计开始日期以及结束日期;在"折旧测算"表格的[A:L]列录入固定资产的未审基本信息，然后将[M:X]列的公式向下拖动。

      其中有两点需要注意的是，残值率与残值，两项选填一项即可。报废日期项目为选填，若此项固定资产在审计期间内报废，则填写报废日期，否则不填空着。

      接下来，需要我们动手的工作就结束了，由表格来完成后续工作。"折旧测算"表格的公式部分会自动进行折旧测算，并列出测算差额。"分类汇总"表格则可以提取 "折旧测算"表格 [C]列的固定资产分类 ，并按照其分类分别对固定资产原值、未审折旧、测算折旧 的 期初数、本期增加、本期减少、期末数 进行分类汇总。

      在下年度审计时，若企业固定资产并未发生增减变动，只需更改审计起止日期以及未审折旧数值，即可完成测算。若固定资产有增减变动，更改审计起止日期后，删除减少的行，在下方录入增加的行，即可完成。

      此外，表格同样适用于年审以外的情况，半年度、季度、2月到8月的审计，都能应对，只需更改审计起止日期，无需更改公式。

[A:L]列：为基本信息，不含公式。

[M]列：测算审计开始日前，应当计提多少个月的折旧。

公式如下：

=MAX(MIN(F2,YEAR(分类汇总!$I$3)*12+MONTH(分类汇总!$I$3)-YEAR(折旧测算!D2)*12-MONTH(折旧测算!D2)-1),0)

这段公式为两层嵌套函数，翻译成人话:第一步，计算固定资产自入账日期到审计开始日期过去了多少个月。第二步，将第一步结果与使用寿命相比较，两者取小。第三步，将第二步结果与0相比较，两者取大。

第一步，计算固定资产自入账日期到审计开始日期过去了多少个月。

      计算过程为:  审计起始日期的年份 X 12 + 审计起始日期的月份 -（入账日期年份 X 12 + 入账日期的月份）-1

      对应公式：YEAR(分类汇总!$I$3)*12+MONTH(分类汇总!$I$3)-YEAR(折旧测算!D2)*12-MONTH(折旧测算!D2)-1

      这里涉及到两个函数，分别是YEAR函数和MONTH函数。YEAR函数的语法为  YEAR（日期）  ，函数计算的结果是，返回括号内参数的年份。相对应的，MONTH函数的语法为  MONTH（日期）  ，函数计算的结果是，返回括号内参数的月份。

      注意：在这里或许有同学使用的是DATEDIF函数（这是个隐藏函数），在以往使用中，DATEDIF函数在某些情况下会产生错误的结果，例如下图中的例子，在入账日期为16年12月1日时，会返回错误的结果，而将日期调至2号及以后则不会出现错误。这大概也是为什么微软将这个函数隐藏的原因吧。

第二步，将第一步结果与使用寿命相比较，两者取小。

      对应公式：MIN(F2,YEAR(分类汇总!$I$3)*12+MONTH(分类汇总!$I$3)-YEAR(折旧测算!D2)*12-MONTH(折旧测算!D2)-1)

      这里涉及到MIN函数，MIN函数语法为  MIN（参数1，参数2，参数3，...)    这个函数参数的数量并不固定。其作用是返回一组参数的最小值。

第三步，将第二步结果与0相比较，两者取大。

      对应公式：MAX(第二步公式,0)

      这里涉及到MAX函数，其作用与MIN函数刚好相反，返回一组参数的最大值。语法则与MIN函数相同。

      将第二步的结果与0比较取大的意义是，审计期间新增的固定资产日期相较审计开始日期要晚，所以在第一步计算时，会得出一个负数值，第二步取小时也会被函数判定为最小值返回。这显然是不对的，当年新增的固定资产在审计开始日期应当计提折旧的期间数只能是0，所以增设第三步的公式来用0带换掉负数值。

[N]列：以[O]列数值减去[M]列数值，获得在审计期间内应计提的月份数。

[O]列：测算截止审计结束日或固定资产报废日，应当计提折旧的月份数。

公式如下：

=IF(L2>0,

MIN(F2,YEAR(分类汇总!$I$4)*12+MONTH(分类汇总!$I$4)-YEAR(折旧测算!D2)*12-MONTH(折旧测算!D2),YEAR(L2)*12+MONTH(L2)-YEAR(D2)*12-MONTH(D2)),

MIN(F2,YEAR(分类汇总!$I$4)*12+MONTH(分类汇总!$I$4)-YEAR(折旧测算!D2)*12-MONTH(折旧测算!D2)))

{以上公式为一段，回车符是为了方便阅读}

       最外层的函数是一个IF函数。IF函数语法  IF（判断，结果1，结果2）。公式的作用是，对第一个参数的公式进行判断，判断成立时执行结果1，不成立时执行结果2。

       这套公式的流程，首先判[L2]单元格是否有填写；填写的情况下：使用寿命、自入账日期到审计截止日期的月份数、自入账日期到报废日期的月份数，三者取小；未填写的情况下：使用寿命、自入账日期到审计截止日期的月份数，两者取小。使用的函数同样是MIN函数。

       判断是否填写了报废日期的公式为，L2>0，若大于0则识别为填写了，若不大于0则识别为没有填写。所用的原理是Excel的一个小知识，Excel中所有的日期都是以整数的形式来储存的。从1900年1月1日开始，对应的数值为1，1900年1月2日对应的数值为2。过去一天增加1，以此类推。所以，我们看到的日期格式的信息，是Excel将原始数据化了个妆，再呈现出来。因此，填写了报废日期，实际上是在单元格里填写了一个大于40000的数字，而不填的情况则是空值，空值在运算时等于0。

       在这里用其他公式来判断也是可以的，例如L20或是LEN(L2)>0。第一个公式中，符号表示的是不等于。第二个公式涉及到的LEN函数，语法LEN(参数)。其作用是返回参数的长度，如LEN(234)=3和LEN(78)=2。空值的长度为0。

[P]列：计算每月应计提的折旧额。

公式如下：

=MIN($E2*(1-$G2),$E2-$H2)/$F2

       考虑到工作中，可能遇到企业提供的是残值，也可能遇到企业提供的是残值率信息，所以在这里加入了一个MIN函数，将{原值-残值}与{(1-残值率)*原值}两个结果相比较，两者取小。这样基础信息里残值率或是残值两项信息只需要填写一项即可。

[Q]-[S]列：分别根据[M]-[O]列计算得出的月份信息，乘[P]列的月折旧额，计算得出期初、本期、期末的折旧额。

[T]列：以[R]列的本期应提折旧额 减去 [J]列的本期账面已提折旧额，得出差额。

[U]列：以[S]列的期末应提累计折旧额 减去 [K]列的期末账面已提折旧额，得出差额。

[V]列：这一列为辅助列，是在"分类汇总"用到的，对固定资产入账时点是否在审计开始前进行判断，作为分类汇总期初原值和本期增加的标准。

公式如下：=IF(D2>分类汇总!$I$3,"本年","前年")

[W]列：备注信息，审计说明等需要记录的事项填写在这里。

           （之所以放在这一列，左右都是公式区，是为了方便打印，底稿完成后隐藏[W]列和[X]列，备注信息会出现在最右列）

[X]列：以[U]列的累计差额除以[P]列的月折旧额，辅助审计工作过程中查找差额原因。

[I3]、[I4]单元格：基础信息单元格，分别录入开始日期和结束日期。

[B3:B10]单元格：不重复的提取"折旧测算"表C列的固定资产分类信息。

公式如下：=INDEX(折旧测算!C:C,1+MATCH(0,COUNTIF($B$3:B3,折旧测算!$C$2:$C$100000),))&""

       这里的公式是一个数组函数，在输入完成时需要同时按下Ctrl+Shift+Enter键完成输入。首先介绍下出现的三个函数：

       COUNTIF（区域，条件）：计算在参数2的条件在参数1的区域里出现的次数。条件参数可以以文本、公式、单元格引用，的三种方式引用。需要注意的是，这里用公式作为条件时，需要在公式的两边加上英文状态的双引号。举个栗子：

       MATCH（参数1，区域，匹配方式）：参数1为需要在参数2的区域中查找的值，参数3匹配方式设定查找的方式，有三种1、0、-1，省略参数3时默认为1。当匹配方式为1时，查找小于或等于参数1的值在区域中的位置，区域必须按升序排列，否则遇到比参数1大的值时会停止查找并返回此前的值。当匹配方式为0时，查找等于参数1的值在区域中的位置。当匹配方式为-1时，查找大于或等于参数1的值在区域中的位置，区域必须为降序排列。

       {INDEX有两种用法，这里只介绍上述用到的这种}  INDEX（区域，X行，Y列）。函数的作用时返回区域中，第X行，第Y列的值。区域可以时一块单元格区域，也可以是一组数组。当区域中，只包含1行或是1列，则相应的不使用行参数或列参数。当区域中包含多行以及多列时，若行参数或列参数为0，则返回整列或整行的数组。

       接下说一下三个公式的组合。公式中的COUNTIF($B$3:B3,折旧测算!$C$2:$C$100000)部分，在公式所在单元格上方的区域中，用COUNTIF函数依次统计  "折旧测算"  表  [C2:C100000]  单元格区域中每个分类的个数。返回一个由0和1构成的内存数组，其中0表示是首次出现的数据，1表示对应位置的固定资产分类在公式上方出现过，也就是重复出现的分类。

       当公式向下复制时，参数$B$3:B3依次变$B$3:B4、$B$3:B5，也就是前面的公式结果，会被继续作为后面公式的参数进行排除。

       然后用MATCH函数在COUNTIF函数返回的数组中查找0的位置，也就是首次出现的数据所在位置。由于  "折旧测算"  表  {C]列 的标题行占了1行，所以将这个数字再加1。

       最后利用INDEX函数，根据MATCH函数的结果返回"折旧测算"  表  [C]列 对应位置的数值。

      ORZ。。。

      这套数组公式不光非常绕脑子，运行起来还异常的卡，所以我建议以手动的方式来提取固定资产的分类信息，利用高级筛选功能即可做到。

      具体过程是：首先选中固定资产分类信息（点选C1单元格，再按下Ctrl+Shift+↓），选择数据选项卡→高级筛选（依次按下Alt、A、Q），勾选“选择不重复的记录”，点选“确定”按钮。然后将结果中除了[C1]标题单元格以外的数据复制粘贴到"分类汇总"表的[B4]单元格。就像这样：

      注意这里只设置了7个单元格，如果不幸遇到了7种以上分类需要披露，那就需要修改一下模板了。

[C:F]列：利用sumifs函数对期初余额、本期增加、本期减少进行分类汇总，并加减获得期末余额。

      sumifs（汇总区域，条件区域1，条件1，条件区域2，条件2，....）公式作用是，对条件区域1 符合 条件1，并且条件区域2 符合 条件2...的 汇总区域 数值，进行加计。sumifs函数可以有多个条件区域和对应条件。和countif相同，这里的条件可以是文本、公式、单元格引用三种方式。再举个栗子：

下面是具体的公式：（原值。折旧部分相应更改参数1的区域。）

期初余额    =SUMIFS(折旧测算!E:E,折旧测算!C:C,B4,折旧测算!V:V,"前年")

本期增加    =SUMIFS(折旧测算!E:E,折旧测算!C:C,B4,折旧测算!V:V,"本年")

本期减少    =SUMIFS(折旧测算!E:E,折旧测算!C:C,B4,折旧测算!L:L,">0")

       在这里用来区分以前年度增加的固定资产和本期增加的固定资产就用到了之前提到的辅助列。我查了一些信息，没有找到能直接用[I3]单元格来作为判断公式的一部分的方法，所以还需要用辅助列。如果有知道方法的同学还望不吝赐教。

以上即是这份模板的使用说明和啰嗦讲解。谢谢阅读！

彩蛋：（另外几个小工具）

用于上年未做过账龄分析的往来：

借方账龄分析工具

贷方账龄分析工具

用于上年做过账龄分析的往来：

借方账龄分析工具5年版