一、固定资产基础参数的设置

固定资产明细表，即存放所有固定资产数据的工作表，在描述固定资产特征数据中有些数据相对固定，并且有一定的规律性，为了提高输入效率，可以将这些相对规律和固定的数据组成一个基础参数表，这些参数如图6－15所示。

图6－15 固定资产基础参数表

如果将这些数据设置为可供用户选择的下拉列表，还需要进行将这些数据区域分别进行命名，具体步骤如下所述。

步骤1：首先建立一个Excel工作簿，在该工作簿中的Sheet1工作表中输入如图6－15所示的项目。然后将该工作表标签改为“系统参数设置”。

步骤2：进行数据区域命名。单击菜单栏中的【插入】︱【名称】︱【定义】命令，弹出“定义名称”对话框，在“在当前工作簿中的名称”文本框中输入名称“类别编号”，为了让数据区域的大小灵活设置，可以将区域设置为动态的单元格区域，因此在“引用位置”文本框中输入公式：＝OFFSET（系统参数设置！＄A＄2，1，COUNTA（系统参数设置！＄A：＄A）－1）

查找与引用函数OFFSET在学习情境二中已详细介绍，此处不再赘述。

如图6－16所示，然后单击“添加”按钮。

图6－16 数据区域命名

步骤3：用同样的方法，可以将其他系统参数分别进行数据区域命名，公式如下：类别编号＝OFFSET（系统参数设置！＄B＄2，1，，COUNTA（系统参数设置！＄B：＄B）－1）

使用部门＝OFFSET（系统参数设置！＄D＄2，1，，COUNTA（系统参数设置！＄D：＄D）－1）

费用科目＝OFFSET（系统参数设置！＄E＄2，1，，COUNTA（系统参数设置！＄E：＄E）－1）

资产来源＝OFFSET（系统参数设置！＄G＄2，1，，COUNTA（系统参数设置！＄G：＄G）－1）

资产去向＝OFFSET（系统参数设置！＄I＄2，1，，COUNTA（系统参数设置！＄I：＄I）－1）

资产状态＝OFFSET（系统参数设置！＄K＄2，1，，COUNTA（系统参数设置！＄K：＄K）－1）

资产性质＝OFFSET（系统参数设置！＄M＄2，1，，COUNTA（系统参数设置！＄M：＄M）－1）

折旧方法＝OFFSET（系统参数设置！＄O＄2，1，，COUNTA（系统参数设置！＄O：＄O）－1）

如图6－17所示，在具体的操作细节上，每一个系统参数的设置只需要修改数据区域名称以及将函数OFFSET中的第一个和第四个参数进行修改，点击“添加”按钮，就可以很快地完成以上的区域命名。

附：统计函数COUNTA介绍

函数功能：返回参数列表中非空值的单元格个数。利用函数COUNTA可以计算单元格区域或数组中包含数据的单元格个数；

图6－17 定义名称

函数参数：COUNTA（value1，value2，…）；

参数说明：Value1，value2，…为所要计算的值，参数个数为1到30个。在这种情况下，参数值可以是任何类型，它们可以包括空字符（""），但不包括空白单元格。如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。

二、创建固定资产明细表

接下来需要创建固定资产明细表，即存放所有固定资产数据的工作表。主要包括三方面的工作：一是设计明细表格式；二是添加相应的基础数据；三是设置计算固定资产折旧的相关公式。

1．设计明细表的结构

固定资产明细表可以用来存放与该固定资产相关的所有数据，以便于对企业的固定资产做到详细、全面的管理，因此要考虑明细表应该包括哪些内容，一般要包括如下的项目：资产名称、资产编号、类别编号、类别名称、使用部门、费用科目、资产状态、资产来源、资产去向、资产性质、购买日期、终止日期、资产原值、资产净残值率、使用年限、月折旧额、已计提月份、累计折旧等基本项目，根据每个企业管理的需要，还可以包括资产设备的规格型号、制造单位等一些辅助项目，项目设置的多少可以根据实际情况灵活掌握，在此只是提供固定资产管理的一般思路。由于表格中的项目太多，我们在此只显示部分项目，如图6－18所示。

2．有效性设置

在固定资产明细表的项目中有些数据需要直接输入，如“资产名称”、“资产编号”、“起始日期（购置日期）”、“资产原值”、“残值率”；有些数据的填列有一定的规律性，且数据相对固定，这些数据在“系统参数设置”的讲解中已经提到，我们可以将这些数据设置为可供用户选择的下拉列表，以提高数据输入效率，如“类别编号”、“使用部门”、“资产来源”、“资产去向”等项目，具体操作步骤如下所述。

步骤1：选定“类别编号”所在的单元格区域C4：C27，单击菜单栏【数据】︱【有效性】命令，弹出“数据有效性”对话框，在“设置”选项卡中的“有效性条件”区域中的“允许”下拉列表中选择“序列”，然后在“来源”文本框中键入“＝类别编号”，如图6－19所示。

图6－18 固定资产明细表

图6－19 设置“类别编号”下拉列表

如果在固定资产明细表中的“资产编号”已经通过手工输入，一般而言，“资产编号”中包括“类别编号”，那么我们也可以通过函数取值的方式得到“类别编号”。例如“资产编号”是“011001”，在编号的前三位其实代表着“类别编号”的信息，我们利用字符取值函数可以直接根据“资产编号”生成“类别编号”。如图6－20所示。

图6－20 公式生成“类别编号”

附：文本LEFT函数介绍

函数功能：从一个文本字符串的第一个字符开始，截取指定数目的字符；

函数语法：LEFT（text，num＿chars）；

参数说明：text代表要截字符的字符串；num＿chars代表给定的截取数目。

特别提醒：此函数名的英文意思为“左”，即从左边截取，Excel很多函数都取其英文的意思。

步骤2：同样的方法，也可以设置“类别名称”的数据下拉列表，如图6－21所示。

图6－21 设置“类别名称”下拉列表

除了数据下拉列表的思路外，由于“类别编号”与“类别名称”具有相互对应关系的特征，我们也可以采用查询函数的思路，根据“类别编号”来形成“类别名称”，因此针对不同的情况，我们可以灵活地采用多种办法解决问题。如图6－22所示。

类别名称＝VLOOKUP（C4，系统参数设置！＄A＄3：＄B＄7，2，FALSE）

查询函数VLOOKUP在前面的任务中已做详细讲解，在此不再赘述。

图6－22 公式生成“类别名称”

步骤3：“使用部门”、“费用科目”、“资产类别”、“资产状态”、“折旧方法”项目都可以采用数据有效性设置，形成数据下拉列表，以方便数据的输入。如图6－23至图6－27所示。

图6－23 设置“使用名称”下拉列表

图6－24 设置“资产状态”下拉列表

图6－25 设置“资产来源”下拉列表

图6－26 设置“资产性质”下拉列表

图6－27 设置“折旧方法”下拉列表

在做数据下拉列表时，经常会出现以下问题。

问题1：在进行数据有效性设置完成，点击“确定”后，出现如图6－28所示的对话框。

图6－28 问题提示1

解析1：出现这个对话框，往往是在设置有效性条件中“来源”文本框中的数据区域没有提前命名。

解决1：检查数据区域是否正确命名。

问题2：在设置有效性条件中“来源”文本框中以如图6－29所示的工作表单元格地址时，则会出现如图6－30所示的对话框。

图6－29 用单元格地址的形式表示数据来源

图6－30 问题提示2

解析2：如果在【有效性条件】中的【允许】选择【序列】命令，在【来源】文本框中表示的数据区域与所设置有效性条件的单元格必须在同一张工作表中，如果不是同一张工作表，必须将数据区域进行命名，才可以作为数据来源，否则就会出现如图6－30所示的对话框。

解决2：对数据区域进行命名（这就是我们为何要在“系统参数设置”中将基础参数都要进行区域命名的原因）。

3．使用折旧函数计算固定资产折旧

计算固定资产折旧的方法很多，较为常见的有平均年限法、固定余额递减法、双倍余额递减法和年数总和法。

平均年限法也被称为直接法折旧，在任务一中已做讲解，在此不再赘述。

余额递减折旧法是一种加速折旧的方法，它采用一定的折旧率乘以一个递减的设备资产初期账面值，得到每期的折旧金额，在Excel中使用DB（）函数实现。

附：财务函数DB介绍

函数功能：用固定余额递减法，返回指定期间内某项固定资产的折旧值；

函数语法：DB（cost，salvage，life，period，month）；

参数说明：Cost为资产原值；Salvage为资产在折旧期末的价值（也称为资产残值）。

Life为折旧期限（有时也称做资产的使用寿命）；Period为需要计算折旧值的期间。Period必须使用与life相同的单位；Month为第一年的月份数，如省略，则假设为12。

双倍余额递减法是在不考虑固定资产残值的情况下，根据双倍直线法折旧率计算固定资产折旧的一种方法。采用这种方法折旧率是固定的，计算折旧的基数是期初固定资产的账面净值。其计算公式是：

年折旧率＝1／预计使用年限×100％×2

年折旧额＝期初固定资产账面净值×年折旧率

期初固定资产账面净值＝固定资产原值－累计折旧

根据我国会计制度的规定，企业采用双倍余额递减法计提固定资产折旧时，应当在固定资产折旧年限到期前两年内，将固定资产净值扣除预计净残值后的净额平均摊销。在Excel中，双倍余额递减法的函数为DDB（）。

附：财务函数DDB介绍

函数功能：用双倍余额递减或其他指定方法，返回指定期间内某项固定资产的折旧值；

函数语法：DDB（cost，salvage，life，period，factor）；

参数说明：Cost为资产原值；Salvage为资产在折旧期末的价值（也称为资产残值）；Life为折旧期限（有时也称做资产的使用寿命）；Period为需要计算折旧值的期间。Period必须使用与life相同的单位；Factor为余额递减速率。如果factor被省略，则假设为2（双倍余额递减法）；这五个参数都必须为正数。

年限总和法是将固定资产原值减去预计净残值后的余额乘以一个逐年递减的分数，这个分数分子代表固定资产尚可使用的年限，分母是使用年限的各年年数之和。其计算公式是：

年折旧率＝尚可使用的年数／年数总和

年折旧额＝（固定资产原值－预计净残值）×年折旧率

附：财务函数SYD介绍

函数功能：返回某项固定资产按年限总和折旧法计算每期折旧金额；

函数语法：SYD（cost，salvage，life，per）；

参数说明：Cost为资产原值；Salvage为资产在折旧期末的价值（也称为资产残值）；Life为折旧期限（有时也称做资产的使用寿命）；Per为期间，其单位与life相同。

在熟悉了函数的使用方法后，我们就可以根据参数的要求，进行折旧的计算，例如固定资产“厂房”，采用的折旧方法为“双倍余额递减法”，在单元格Q5中输入公式为：

Q5＝DDB（J5，J5＊K5，M5，INT（P5／12）＋1）／12

如图6－31所示，在公式中，前三个参数分别代表固定资产原值，净残值，使用寿命，可以直接通过引用单元格得出，第四个参数需要通过简单的运算才能得到。INT（P5／12）＋1是根据已计提折旧的月份数计算当前的折旧期间，INT（）函数的作用是向下取整，即如果该设备使用不到一年，那么此时的折旧期间是在一年，所以需要加1后再向下取整。

图6－31 双倍余额递减法的公式设置(www.daowen.com)

在使用DDB函数时，需要注意的是，由于我国的会计制度规定某年中每月的折旧额为该年折旧额在12个月内平均分摊，因此，在计算每月的折旧额时，需要将DDB函数计算出来的某年折旧额除以12，而不需要直接使用DDB函数来计算某月的折旧额。

学习了平均年限法和双倍余额递减法设置公式的思路后，可以计算出固定资产明细表中其余资产的月折旧额，在操作上可以直接在每个单元格中输入公式，也可以将采用同样折旧方法的公式复制过去。

附：数学与三角函数INT介绍

函数功能：将数值向下取整为最近的整数；

函数语法：INT（Number）；

参数说明：Number为要取整的实数。

4．本年折旧月份数的公式设置

我国会计制度对固定资产的折旧有如下规定：

①当月启用的设备从下月开始计算折旧；

②设备非本年开始启用，且本年全年一直使用，则本年每月均应进行折旧；

③设备本年开始启用，且一直用到年末，则本年折旧月数从启用的下一个月起至年末。

由于固定资产的使用寿命一般都在几年以上，所以本年启用的设备本年就停用的情况可以不予考虑。在判断本年折旧月份时，可以分为两种情况：一是如果起始的年份小于当前年份，并且当前年份小于起始年份和使用年限之和，则表示资产全年处于正常使用状态；二是本年内到期，则本年应折旧的月份数应为12减去起始月份数的差额。如图6－32所示。

基于以上的分析，可以将公式设置如下：

R4＝IF（G4＝"报废"，0，IF（AND（YEAR（L4）＜YEAR（＄J＄2），YEAR（＄J＄2）＜（YEAR（L4）＋M4）），12，12－MONTH（L4）））

图6－32 本年折旧月数的公式设置

附：函数逻辑AND介绍

函数功能：当所有参数的逻辑值为真时，返回TRUE；只要一个参数的逻辑值为假，即返回FALSE；

函数语法：AND（logical1，logical2，．．．）；

参数说明：Logical1，logical2，．．．表示待检测的1到30个条件值，各条件值可为TRUE或FALSE 。

特别提醒：

（1）参数必须是逻辑值TRUE或FALSE，或者包含逻辑值的数组或引用；

（2）如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略；

（3）如果指定的单元格区域内包括非逻辑值，则AND将返回错误值＃VALUE！。

5．本年折旧额的计算

本年应计提折旧总额即为月折旧额乘以本年折旧月份数。因此公式为：

S4＝Q4＊R4，如图6－33所示。

图6－33 本年折旧额的公式设置

三、创建固定资产卡片

虽然固定资产明细表中包含了所有的固定资产信息，但当只需要查看某一项固定资产时，该表格就显得比较繁杂，不易于查找。固定资产卡片是按照固定资产项目开设，用以进行固定资产明细核算的账簿。在一些管理比较正规的企业，大型的机器设备类固定资产通常会通过固定资产卡片进行管理。

在Excel中固定资产卡片的大部分信息都可以通过公式的设置取自于固定资产明细表，基本思路将在下面做详细的分析和介绍。

1．设计固定资产卡片的格式

根据企业的具体情况，固定资产卡片的格式依企业不同而有所不同。假设某企业的固定资产格式如图6－34所示。下面详细介绍在Excel上制作固定资产卡片的方法和步骤。

图6－34 固定资产卡片的格式

步骤1：插入一个空白工作表，在工作表中输入如图6－35所示的表格项目。

步骤2：合并及居中单元格区域A1：I2，设置文字的字体为“华文隶书”、字号为“26”，颜色为“蓝色”，下画线为“会计用双下画线”，调整行高至合适的数值。如图6－36所示。

步骤3：将表格项目所在单元格填充为灰色，并设置对齐方式和边框，适当调整行高和列宽。设置后的效果如图6－37所示。

图6－35 固定资产卡片表格项目

图6－36 设置标题单元格格式

步骤4：设置卡片编号的长度。假设卡片编号的长度是固定的，为了输入方便，可以提前设置好单元格C3的格式，例如卡片编号的长度是4位，不够4位的前面补“0”，具体操作步骤为：选择单元格C3，单击菜单栏【格式】︱【单元格】︱【数字】标签，在【自定义】格式设置的“类型”文本框中输入“0000”即可。

图6－37 设置好格式后的效果

步骤5：设置固定资产卡片的日期。卡片的日期可以直接取自于“固定资产明细表”中的日期，在单元格H3中输入公式：＝固定资产明细表！J2，并且要将单元格H3的格式设置为日期型格式，否则将显示为日期所对应的序列号。如图6－38所示，计算当前日期。

图6－38 设置并显示当前日期

步骤6：固定资产编号的提示设置。每一项固定资产都有唯一的编号，编号的信息直接会影响到其余固定资产信息的取得，因此首先设置单元格C4的输入提示。具体步骤为：选定单元格C4，单击菜单栏【数据】︱【有效性】命令，弹出【数据有效性】对话框，单击【输入信息】标签切换到【输入信息】选项卡中，选择【选定单元格时显示输入信息】复选框，然后在【输入信息】文本框中键入“请输入要查询制作卡片的固定资产编号”，如图6－39所示。单击“确定”按钮后，关闭【数据有效性】对话框，选定工作表中的单元格C4，此时屏幕上将显示如图6－40所示的提示信息。

图6－39 设置提示信息

图6－40 显示提示信息

步骤7：固定资产编号的下拉列表设置。步骤5中设置了输入的提示信息，为了提高输入的准确度和效率，还可以进一步将该单元格设置成下拉列表式输入。关于数据下拉列表的设置方法在前面已经多次讲解，在此需要强调的是作为数据源的区域必须提前进行区域命名。具体步骤为：将“固定资产明细表”中的资产编号区域进行区域命名，如图6－41所示。

公式设置为：＝OFFSET（固定资产明细表！＄A＄4，1，，COUNTA（固定资产明细表！＄A：＄A）－1）

然后再选择单元格C4，单击菜单栏【数据】︱【有效性】｜命令，弹出【数据有效性】对话框，单击【设置】标签，在【允许】文本框中选择“序列”，【来源】文本框中输入公式：＝资产编号。如图6－42和图6－43所示。

图6－41 “资产编号”区域命名

图6－42 下拉列表设置

步骤8：固定资产卡片中的其他信息，如“固定资产名称”、“类别编号”、“类别名称”、“增加方式”、“部门名称”、“使用状况”、“原值”、“残值”、“折旧方法”、“已计提月份”、“开始使用日期”、“费用科目”等都可以通过公式的设置，取自于“固定资产明细表”。

公式设置为：

类别编号C5＝INDEX（固定资产明细表！C＄4：C＄500，MATCH（＄C＄4，固定资产明细表！A＄4：A＄500，0））

增加方式C6＝INDEX（固定资产明细表！H＄4：H＄500，MATCH（＄C＄4，固定资产明细表！A＄4：A＄500，0））

图6－43 设置好后的下拉列表

使用状况C7＝INDEX（固定资产明细表！I＄4：I＄500，MATCH（＄C＄4，固定资产明细表！A＄4：A＄500，0））

原值C8＝INDEX（固定资产明细表！J＄4：J＄500，MATCH（＄C＄4，固定资产明细表！A＄4：A＄500，0））

折旧方法C9＝INDEX（固定资产明细表！O＄4：O＄500，MATCH（＄C＄4，固定资产明细表！A＄4：A＄500，0））

固定资产名称＝INDEX（固定资产明细表！B＄4：B＄500，MATCH（＄C＄4，固定资产明细表！＄A＄4：＄A＄500，0））

类别名称E5＝INDEX（固定资产明细表！D＄4：D＄500，MATCH（＄C＄4，固定资产明细表！＄A＄4：＄A＄500，0））

部门名称E6＝INDEX（固定资产明细表！E＄4：E＄500，MATCH（＄C＄4，固定资产明细表！＄A＄4：＄A＄500，0））

使用年限E7＝INDEX（固定资产明细表！M＄4：M＄500，MATCH（＄C＄4，固定资产明细表！＄A＄4：＄A＄500，0））

净残值率E8＝INDEX（固定资产明细表！K＄4：K＄500，MATCH（＄C＄4，固定资产明细表！＄A＄4：＄A＄500，0））

已提折旧月数E9＝INDEX（固定资产明细表！P＄4：P＄500，MATCH（＄C＄4，固定资产明细表！＄A＄4：＄A＄500，0））

开始使用日期G7＝INDEX（固定资产明细表！L＄4：L＄500，MATCH（＄C＄4，固定资产明细表！＄A＄4：＄A＄500，0））

费用科目G9＝INDEX（固定资产明细表！F＄4：F＄500，MATCH（＄C＄4，固定资产明细表！＄A＄4：＄A＄500，0））

净残值G8＝C8＊E8

公式的运算结果如图6－44所示。

上述查找与引用函数INDEX和MATCH在学习情境二中已详细介绍，此处不再赘述。

图6－44 公式运算后的结果

步骤9：设置折旧额计算的公式：

（1）计算需要折旧的年份

单元格B13是未提折旧的初始年份，用“0”表示，在单元格B14中输入计算折旧年份的公式：＝IF（＄C＄9＝""，""，IF（ROW（）－ROW（＄B＄13）＜＝＄E＄7，ROW（）－ROW （＄B＄13），""））

（2）计算年折旧额

在单元格C14中输入下面的公式，计算第1年的年折旧额：

“＝IF（B14＝""，""，IF（＄C＄9＝"平均年限法"，SLN（＄C＄8，＄G＄8，＄E＄7），IF（＄C＄9＝"双倍余额递减法"，IF（B14＜＝＄E＄7－2，DDB（＄C＄8，＄G＄8，＄E＄7，B14）， （INDEX（＄H＄14：＄H＄202，MATCH（＄E＄7－2，＄B＄14：＄B＄202））－＄G＄8）／2））））”

（3）计算年折旧率

在单元格D14中输入公式“＝IF（B14＝""，""，ROUND（IF（＄C＄9＝"平均年限法"， （1－＄E＄8）／＄E＄7，IF（＄C＄9＝"双倍余额递减法"，2／＄E＄7）），4））”计算第1年的年折旧率。

（4）计算月折旧额

在单元格E14中输入公式“＝IF（B14＝""，""，ROUND（C14／12，2））”计算第1年的月折旧额。

（5）计算月折旧率

在单元格F14中输入公式“＝IF（B14＝""，""，ROUND（D14／12，4））”计算第1年的月折旧率。

（6）计算累计折旧额

在单元格G14中输入公式“＝IF（B14＝""，""，G13＋C14）”计算第1年的累计折旧额。

（7）计算折余价值

在单元格H14中输入公式“＝IF（B14＝""，""，＄H＄13－G14）”计算第1年的折余价值。

其余各年的公式都可以用自动填充柄进行复制生成。

公式设置完成后，只需要输入卡片编号，选择固定资产编号，就可以在卡片中列示出该固定资产的相关信息，如图6－45所示。

图6－45 固定资产卡片的完整样式