模块四：Excel在固定资产管理中的应用

六EXCEL在固定资产管理中的应用一固定资产清单（一）固定资产清单是用来存放所有固定资产数据的工作表，后续折旧费用的分配、相关记账凭证的生成、资产的分析都是建立在固定资产清单的基础上。利用Excel建立固定资产清单是固定资产管理的前提。我们先来看一下如何建立固定资产清单。

固定资产清单通过存放与该固定资产相关的所有数据，实现对企业的固定资产详细、全面的管理。清单中一般要包括如下的项目：资产名称、资产编号、类别编号、类别名称、使用部门、费用科目、起始日期、使用年限、终止日期、资产状态、增加方式、资产性质、资产原值、资产净残值率、资产残值、已计提月份、本月折旧额、本年计提月数、本年折旧额等基本项目。项目设置的多少可以根据实际情况灵活掌握，如果企业管理需要，还可以包括资产设备的规格型号、制造单位等一些辅助项目。1.查找和引用函数1.1MATCH（lookup_value,lookup_array,match_type）功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。（1）参数说明：lookup_value：需要在数据表中查找的数值，可以是数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。lookup_array：可能包含所要查找的数值的连续单元格区域，可以是数组或数组引用。match_type：可能的值是数字-1、0、1，它指明EXCEL如何在第二个参数中查找第一个参数。（2）查找方式说明：当match_type为-1时，lookup_array必须按降序排列，函数MATCH查找大于或等于lookup_value的最小数值；当match_type为0时，lookup_array可以按任何顺序排列，函数MATCH查找等于lookup_value的第一个数值；当match_type为1或省略时，lookup_array必须按升序排列，函数MATCH查找小于或等于lookup_value的最大数值。（3）举例：MATCH（16，{97，52，36，15}，-1）=3MATCH（15，{20，15，12，15}，0）=2MATCH（68，{31，52，66，79}）=31.2OFFSET（reference，rows，cols，height，width）功能：以指定的引用为参照系，通过给定的偏移量得到新的引用。（1）参数说明：reference：作为偏移量参照系的引用区域；rows：表示相对偏移量参照系左上角的单元格上（下）偏移的行数；为正数时表示向下偏移，为负数时表示向上偏移；cols：表示相对偏移量参照系左上角的单元格左（右）偏移的列数；为正数时表示向右偏移，为负数时表示向左偏移；height：表示返回的引用区域的行数；width：表示返回的引用区域的列数。（2）注意：①如果行数和列数偏移量超出工作表边缘，函数OFFSET返回错误值#REF!。②如果省略height或width，则假设其高度或宽度与reference区域相同。③函数OFFSET实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数OFFSET可用于任何需要将引用作为参数的函数。④height，width：必须为正数，也可以省略，如果省略，则假设其高度或宽度与第一个参数引用的区域相同。（3）举例：公式SUM(OFFSET(C2,1,2,3,1))将计算比单元格C2靠下1行并靠右2列的3行1列的区域的总值。1.3INDEX功能是返回表格或区域中的数值或对数值的引用。（1）数组形式INDEX（array,row_num,column_num）功能：返回数组中指定单元格或单元格数组的数值。①参数说明：array：单元格区域或数组常数；row_num：数组中某行的行序号，函数从该行返回数值。如果省略row_num，则必须有column_num；column_num：数组中某列的列序号，函数从该列返回数值。如果省略column_num，则必须有row_num。②注意：如果同时使用row_num和column_num，函数INDEX返回row_num和column_num交叉处的单元格的数值。如果数组只包含一行或一列，则相对应的参数row_num或column­_num为可选。如果数组有多行和多列，但只使用row_num或column_num，函数INDEX返回数组中的整行或整列，且返回值也为数组。如果将row_num或column_num设置为0，函数INDEX分别返回整个列或行的数组数值。如果需要使用以数组形式返回的数值时，必须指向array中的某一单元格，否则，函数INDEX返回错误值#REF！。③举例：

INDEX（{3，4，5；6，7，8}，2，3）=8。如果作为数组公式输入，则INDEX（{3，4，5；6，7，8}，2，0）={6，7，8}

（2）引用形式INDEX（reference,row_num,column_num,area_num）功能：返回引用中的指定单元格。①参数说明：reference：一个或多个单元格区域的引用。如果为引用输入一个不连续的选定区域，必须用括号括起来。如果引用中的每个区域只包含一行或一列，则相应的参数row_num或column­_num分别为可选项。例如，对单行的引用，可以使用函数INDEX（reference,column_num）。row_num：引用中某行的行序号，函数从该行返回一个引用。column_num：引用中某列的列序号，函数从该列返回一个引用。area_num:选择引用中的一个区域，并返回该区域中row_num和column_num的交叉区域。选取或输入的第一个区域序号为1，第二个为2，以此类推。如果省略area_num，函数INDEX使用区域1。②注意：

row_num、column_num和area_num必须指向reference中的单元格，否则，函数INDEX返回错误值#REF！。如果省略row_num和column_num，函数INDEX返回由area_num所指定的区域。函数INDEX的结果为一个引用，且在其他公式中也被解释为引用。根据公式的需要，函数INDEX的返回值可以作为引用或数值。③举例：公式“CELL（“width”，INDEX（A1：B2，1，2））”等价于公式“CELL（“width”，B1）”。CELL函数的功能是返回某一引用区域的指定单元格的格式、位置或内容等信息，此处CELL函数的功能是返回A1：B2区域的右上角单元格的宽度。这里，CELL函数将INDEX函数的返回值作为单元格引用。而公式“2*INDEX（A1：B2，1，2）”将INDEX函数的返回值解释为B1单元格中的数字。说明：如果要突出指标体系之间联系的视觉效果，可以不取消表页中的网格线。选择【工具】|【选项】，在【视图】|【窗口】下取消“网格线”选项，单击【确定】。结果如图所示。1.固定资产基础参数设置固定资产清单中涉及到的有些数据相对固定，有一定规律，为了提高输入效率，可以将这些数据组成一个系统参数表，这些参数如图所示。如果将这些数据设置为供用户选择的下拉列表，还需要将这些数据区域分别进行命名，具体步骤如下：步骤1：首先建立一个Excel工作簿，保存工作簿，命名为“固定资产系统”。将该工作簿sheet1工作表标签改为“系统参数”。在该工作表中输入上图所示项目。步骤2：进行数据区域命名。选定B3：B7区域，执行【公式】︱【名称管理器】命令，打开【名称管理器】对话框，单击【新建】按钮，打开【编辑名称】对话框，如图所示。在【名称】文本框中输入“类别编号”。通过在【引用位置】文本框中输入公式：=OFFSET(系统参数!$B$2,1,,COUNTA(系统参数!$B:$B)-1)，可以实现动态调整命名区域。单击【确定】按钮。步骤3：用同样的方法，可以将其他系统参数分别进行数据区域命名，公式如下：类别编号=OFFSET(系统参数!$B$2,1,,COUNTA(系统参数!$B:$B)-1)类别名称=OFFSET(系统参数!$C$2,1,,COUNTA(系统参数!$C:$C)-1)部门编号=OFFSET(系统参数!$D$2,1,,COUNTA(系统参数!$D:$D)-1)部门名称=OFFSET(系统参数!$E$2,1,,COUNTA(系统参数!$E:$E)-1)费用科目=OFFSET(系统参数!$F$2,1,,COUNTA(系统参数!$F:$F)-1)增加方式=OFFSET(系统参数!$G$2,1,,COUNTA(系统参数!$G:$G)-1)减少方式=OFFSET(系统参数!$H$2,1,,COUNTA(系统参数!$H:$H)-1)资产状态=OFFSET(系统参数!$I$2,1,,COUNTA(系统参数!$I:$I)-1)资产性质=OFFSET(系统参数!$J$2,1,,COUNTA(系统参数!$J:$J)-1)折旧方法=OFFSET(系统参数!$K$2,1,,COUNTA(系统参数!$K:$K)-1)

在具体的操作细节上，每一个系统参数的设置只需要修改数据区域名称以及函数OFFSET中的第一、第四个参数，单击【确定】按钮，就可以很快地完成以上的区域命名。结果如图2.固定资产清单格式的设置步骤1：打开“固定资产系统”工作簿，将sheet2工作表标签改为“固定资产清单”。在该工作表中输入如图所示项目。其中A3：U3区域的项目包括：“资产名称”、“资产编号”、“类别编号”、“类别名称”、“使用部门”、“费用科目”、“起始日期”、“使用年限”、“终止日期”、“资产状态”、“增加方式”、“资产性质”、“折旧方法”、“原值”、“残值率”、“残值”、“已计提月份”、“本月折旧额”、“本年计提月数”、“本年折旧额”、“累计折旧额”。注意：1.在本体系中取数时，“平均资产总额”一般应取期初数和期末数的平均值，由于本例资产负债表中缺少2011年度的年初数，所以分别使用了期末数作为计算依据。2.为了保证计算结果的有效性及合理性，本例对原资产负债表和利润表的个别数据进行了调整。步骤2：单元格J2中应该填入的是固定资产清单当前日期。在单元格J2中输入公式：=NOW()或=TODAY()，使用这两个函数的好处是可以使折旧计算清单中的相关数据随系统日期的更新而自动更新计算。如图所示。步骤3：如上图所示，J2单元格显示为日期所对应的序列号，还需要进行如下设置：选定J2单元格，执行【开始】|【数字】|【设置单元格格式】命令，弹出【设置单元格格式】对话框，选择【数字】选项卡，【分类】设置为“日期”，【类型】选择为“2001年3月”。设置好后J2单元格如图所示。3.录入基础数据固定资产清单表格中需要输入的基础数据包括：资产名称、资产编号、起始日期、使用年限、资产状态、增加方式、资产性质、原值、残值率等列数据。以下内容均在“固定资产清单”工作表中完成。1.“资产名称”列数据直接通过键盘录入。如图2.“资产编号”列数据设置为文本格式，通过键盘输入。本企业固定资产编码方式为：部门编号+类别编号+序号；编码方案为133。步骤：选定B4：B20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“文本”格式。通过键盘输入相关资产编号，如图3.“起始日期”列数据设置为日期格式，通过键盘输入，显示为“××××年×月×日”。步骤：选定G4：G20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“日期”格式。【类型】选择“2001年3月14日”。通过键盘输入相关资产启用日期，如图4.“使用年限”列数据设置为数值格式，通过键盘输入数字，显示为“×年”。步骤：选定H4：H20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“自定义”格式。【类型】选择“#"年"”，如图4-1-12所示。通过键盘输入相关资产使用年限，如图5.“原值”列数据设置为会计专用格式，保留两位小数，无货币符号，通过键盘输入。步骤：选定N4：N20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“会计专用”格式，保留两位小数，没有货币符号。通过键盘输入相关资产原值，如图6.“残值率”列数据设置为百分比格式，保留两位小数，通过键盘输入。步骤：选定O4：O20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“百分比”格式，保留两位小数。通过键盘输入相关资产残值率，如图所示。7.“资产状态”、“增加方式”、“资产性质”、“折旧方法”等列数据通过设置数据有效性选择输入。“资产状态”、“增加方式”、“资产性质”、“折旧方法”等列数据都具有一定规律性，且数据相对固定。这些列中需要填入的数据在“系统参数”表中已经定义，可以将这些列设置为可供用户选择的下拉列表，以提高数据输入效率。步骤：选定“资产状态”所在的单元格区域J4：J19，执行【数据】|【数据工具】|【数据有效性】|【数据有效性】命令，弹出【数据有效性】对话框，在【设置】选项卡中的【有效性条件】区域中的【允许】下拉列表中选择“序列”，然后在【来源】文本框中键入“=资产状态”，单击【确定】按钮。如图“增加方式”、“资产性质”、“折旧方法”项目都可以采用数据有效性设置，形成下拉列表，以方便数据的输入。利用设置好的下拉列表，完成“资产状态”、“增加方式”、“资产性质”、“折旧方法”列数据输入，如图

所示。熟练固定资产清单的设计及快速录入数据。四EXCEL在固定资产管理中的应用二固定资产清单（二）在任务一的基础上完成“固定资产清单”工作表其他列公式的设置。固定资产清单中除了基础数据列需要输入外，其他各列均可通过设置公式计算得到。需要设置公式的列包括类别编号、类别名称、使用部门、费用科目、终止日期、残值、已计提月份、本月折旧额、本年计提月数、本年折旧额、累计折旧额等。本任务在“固定资产清单”工作表中完成。1.折旧函数1.1SLN(cost,salvage,life)功能：用平均年限法计算某项资产折旧额。（1）参数说明：cost：资产原值；salvage：资产在折旧期末的价值（也称为资产残值）；life：折旧期限（有时也称作资产的使用寿命）。若为年，计算的是年折旧额；若为月，则计算的是月折旧额。（2）注意：如果采用工作量法计算折旧，应在SLN函数的life参数中输入预计的总工作量，这样可以得到每单位工作量的折旧额，然后根据每期的工作量和每单位工作量的折旧额计算各期的折旧额。1.2DB(cost,salvage,life,period,month)功能：用定率余额递减法计算某项资产折旧额。（1）参数说明：cost：资产原值；salvage：资产在折旧期末的价值（也称为资产残值）；life：折旧期限（有时也称作资产的使用寿命）；period：需要计算折旧值的期间。（2）注意：period必须使用与life相同的单位；month为第一年的月份数，如省略，则假设为12。1.3DDB(cost,salvage,life,period,factor)功能：用双倍余额递减法或其他指定方法，计算某项资产在指定期间内的折旧额。参数说明：cost：资产原值；salvage：资产在折旧期末的价值（也称为资产残值）；life：折旧期限（有时也称作资产的使用寿命）；period：需要计算折旧值的期间，period必须使用与life相同的单位；factor：余额递减速率。如果factor被省略，则假设为2（双倍余额递减法）；这五个参数都必须为正数。1.4VDB(cost,salvage,life,start_period,end_period,factor,no_switch)功能：用双倍余额递减法或其他指定的方法，计算指定的任何期间内的资产折旧额。（1）参数说明：cost、salvage、life、factor同DDB说明；start_period：进行折旧计算的起始期间，它必须与life的单位相同；end­_period：进行折旧计算的截止期间，它必须与life的单位相同；no­_switch：逻辑值，指定当按直线法计算的折旧额大于按余额递减计算的折旧额时，是否转用直线折旧法。如果no_switch为TRUE，即使按直线法计算的折旧额大于按余额递减计算值，Excel也不转用直线折旧法计算折旧。如果no_switch为FALSE或被忽略，且按直线法计算的折旧额大于余额递减计算值时，Excel将转用直线折旧法计算折旧。（2）注意：以上各参数除no_switch外必须都为正数。1.5SYD(cost,salvage,life,per)功能：用年数总和法计算某项资产的折旧额。参数说明：cost：资产原值；salvage：资产在折旧期末的价值（也称为资产残值）；life：折旧期限（有时也称作资产的使用寿命）；per：期间，其单位与life相同。1.“类别编号”、“类别名称”、“使用部门”列公式1.1“类别编号”列公式设置步骤1：由于“资产编号”列已经通过手工输入，编码方式为：部门编号+类别编号+序号，编码方案为133。因此可以利用Excel中的MID函数从资产编号中取得“类别编号”。例如“资产编号”是“1011001”，在编号的左边第一位“1”是“使用部门”办公室的编号，左边第二到第四位“011”是“类别编号”，表示房屋。因此，C4单元格公式为：=MID（B4,2,3），如图所示。步骤2：由于每项固定资产“类别编号”的计算方法相同，利用填充柄将该公式复制到C5：C20中所有填写固定资产“类别编号”的单元格中。1.2“类别名称”列公式设置步骤1：“类别名称”与“类别编号”具有对应关系，可以采用查询函数，根据“类别编号”查询“类别名称”。D4单元格公式为：=INDEX(类别名称,MATCH(MID(B4,2,3),类别编号,0))或者=LOOKUP(C4,类别编号,类别名称)，如图

所示。步骤2：由于每项固定资产“类别名称”的计算方法相同，利用填充柄将该公式复制到D5：D20中所有填写固定资产“类别名称”的单元格中。1.3“使用部门”列公式设置步骤1：结合“类别编号”、“类别名称”列公式所用知识点，设计“使用部门”列公式。E4单元格公式为：=INDEX(部门名称,MATCH(MID(B4,1,1),部门编号,0))或者=LOOKUP(MID(B4,1,1),部门编号,部门名称)，如图所示步骤2：由于每项固定资产“使用部门”的计算方法相同，利用填充柄将该公式复制到E5：E20中所有填写固定资产“使用部门”的单元格中。2.“费用科目”列公式每月计提的固定资产折旧费，应根据用途计入相关资产的成本或者当前损益，借记“制造费用”、“管理费用”等科目，在固定资产清单中设置“费用科目”列，便于日后费用的归集。“费用科目”列的设置可以利用【数据有效性】的下拉列表功能来输入，前面已经多次讲解，此处不再赘述。步骤1：“费用科目”列还可以利用VLOOKUP函数从“系统参数”表中取得。F4单元格公式为：=VLOOKUP(E4,系统参数!$E$3:$F$7,2,FALSE)。步骤2：利用填充柄复制到其他固定资产“费用科目”单元格中。3.“终止日期”列公式固定资产应当按月计提折旧，通常对当月增加的固定资产，当月不提折旧，从下月起计提；对当月减少的固定资产，当月照提，从下月起不提折旧。因此终止日期的设置主要是起到提醒该项固定资产是否已经到了停止计提折旧的作用。步骤1：终止日期的计算与起始日期和使用年限有关。I4单元格公式为：=DATE(YEAR(G4)+H4,MONTH(G4),DAY(G4))。步骤2：由于每个固定资产终止日期的计算都是相同的方法，利用填充柄将该公式复制到其他固定资产“终止日期”的单元格中，如图4.“残值”列公式步骤1：固定资产净残值决定于其原值和残值率，利用公式“原值”、“残值率”两列中取得数据计算得到。P4单元格公式为：=N4*O4。步骤2：利用填充柄将该公式复制到其他固定资产“残值”单元格中，如图5.“已计提月份”列公式已计提月份是指从起始日期开始到当前日期，该项固定资产已经计提折旧的月份。我国会计制度规定当月新增的固定资产从下月开始计算折旧，因此，固定资产如果是该月新增，则已计提月份为0；如果不是该月新增，则计算出来的月份数的差额还应减去1，才能得出正确的已计提折旧的月份数。Q4单元格公式为：方法一：=(12-MONTH(G4))+((YEAR($J$2)-YEAR(G4)-1)*12+MONTH($J$2)-1)公式解析：把已计提月份划分为三个部分：一是起始当年计提的月份“12-MONTH(G4)”；二是中间的整年份已计提的月份“((YEAR($J$2)-YEAR(G4)-1)*12”；三是计提当年已计提的月份“MONTH($J$2)”，三部分相加后再减去1所得的就是该项固定资产总共已计提的月份。方法二：=(YEAR($J$2)-YEAR(G4))*12+MONTH($J$2)-MONTH(G4)-1公式解析：根据当前日期和起始日期之间的月份差来计算已计提折旧的月份数，计算出来的月份数的差额再减去1；方法三：=INT(DAYS360(G4,$J$2)/30)-1公式解析：根据函数DAYS360按照一年360天的算法（每个月以30天计，一年共计12个月），返回两日期间相差的天数，由相差的天数转换为月份取整后减去1，可以得到该项固定资产总共已计提的月份。以上三种方法都可以用来计算已计提月份，但有时计算出来的结果会有误差，一个企业选择其中一种不会影响其折旧总额，介绍给大家是为了拓展解决问题的思路。利用填充柄将该公式复制到其他固定资产“已计提月份”单元格中。6.设置到期提醒当月减少的固定资产，当月计提折旧，下月起不提折旧。通过在固定资产清单中提供到期提醒设置，避免使用者忽略到期时间而计提折旧带来不必要的麻烦。可以利用Excel中的条件格式来设置到期提醒。为说明问题，将固定资产清单做如下临时更改：G8单元格数值改为“2009年9月1日”，G13单元格数值改为“2007年12月1日”。步骤1：选定A4：U20单元格区域，执行【开始】|【样式】|【条件格式】|【新建规则】命令，打开【新建格式规则】对话框，选择规则类型为“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”文本框中设置公式：=$Q4>=$H4*12，单击【格式】按钮，设置符合公式要求的单元格为红色背景，如图步骤2：效果如图7.“本月折旧额”列公式我国大部分企业的固定资产折旧一般都采用直线折旧法，假设我们先考虑所有固定资产均采用直线折旧法，则在计算折旧额时只需要知道三个基本因素（原值、预计净残值、使用年限），就可以计算出当期应提折旧。方法一：采用Excel的内置折旧函数SLN来计算固定资产折旧额。R4公式单元格公式为：=SLN(N4,P4,H4*12)利用填充柄将该公式复制到其他固定资产“本月折旧额”单元格中，如图方法二：直接用基本运算功能来计算折旧，这种方法比较简单，在此就不再显示图表。公式设置：=(N4-P4)/(12*H4)公式解析：=（原值-预计净残值）/使用期限通过以上的计算，有些固定资产的折旧计算出来后可能会出现很多小数位数，可以通过四舍五入函数的将其保留为两位小数，进一步完善的公式为。公式设置：=ROUND(SLN(N4,P4,H4*12),2)如果R5、R6单元格分别采用“双倍余额递减法”和“年数总和法”，则单元格R5、R6中输入公式分别为：R5=DDB(N5,P5,H5,INT(Q5/12)+1)/12R6=SYD(N6,P6,H6,INT(Q6/12)+1)/128.“本年计提月数”列公式我国会计制度对固定资产的折旧有如下规定：（1）当月启用的设备从下月开始计算折旧；（2）设备非本年开始启用，且本年全年一直使用，则本年每月均应进行折旧；（3）设备本年开始启用，且一直用到年末，则本年折旧月数从启用的下一个月起至年末。由于固定资产的使用寿命一般都在几年以上，所以本年启用的设备本年就停用的情况可以不予考虑。在判断本年折旧月份时，可以分为两种情况：一是如果起始的年份小于当前年份，并且当前年份小于起始年份和使用年限之和，则表示资产全年处于正常使用状态；二是本年内到期，则本年应折旧的月份数应为起始月份数。基于以上的分析，S4单元格公式为：=IF(J4="报废",0,IF(AND(YEAR(G4)<YEAR($J$2),YEAR($J$2)<(YEAR(G4)+H4)),12,MONTH(G4)))利用填充柄将该公式复制到其他固定资产“本年计提月数”单元格中，如图9.“本年折旧额”列公式本年应计提折旧总额即为月折旧额乘以本年折旧月份数。因此T4单元格公式为：=R4*S4利用填充柄将该公式复制到其他固定资产“本年折旧额”单元格中，如图10.“累计折旧额”列公式因为直线折旧法是按照固定资产使用年限平均计算年折旧额的计算方法，每期的折旧额是相同的。所以累计折旧只需要通过已计提月份和本期计提折旧额就可以直接计算出来了。U4单元格公式为：=(Q4+1)*R4利用填充柄将该公式复制到其他固定资产“累计折旧”单元格中，如图熟练掌握各类折旧函数的灵活使用。四EXCEL在固定资产管理中的应用三固定资产卡片固定资产清单中包含了所有的固定资产信息，但当只需要查看某一项固定资产时，该表格就显得比较繁杂，不易于查找。固定资产卡片是按照固定资产项目开设，用以进行固定资产明细核算的账簿。在一些管理比较正规的企业，大型的机器设备类固定资产通常会通过固定资产卡片进行管理。固定资产卡片中的数据，可以利用Excel中的公式从固定资产清单中取得。以固定资产卡片中的数据为基础，还可以利用公式对折旧额的相关数据进行计算。本任务的基本流程包括：设置固定资产卡片格式；录入相关单元格公式；输入固定资产编号，生成固定资产卡片。卡片制作

步骤1：打开“固定资产系统”工作簿，将sheet3工作表标签改为“固定资产卡片”步骤2：在“固定资产卡片”工作表中输入如图所示表格项目。步骤3：合并及居中单元格区域C2：I2，设置文字的字体为“华文隶书”、字号为“26”，颜色为“蓝色”，下划线为“会计用双下划线”，调整行高至合适的数值。如图。步骤4：将表格项目所在单元格填充为灰色，并设置对齐方式和边框，适当调整行高和列宽。设置后的效果如图步骤5：设置卡片编号的长度。假设卡片编号的长度是固定的，为了输入方便，我们可以提前设置好单元格D3的格式，例如卡片编号的长度是4位，不够4位的前面补“0”，具体操作步骤为：选择单元格D3，打开【设置单元格格式】对话框【数字】选项卡，在【自定义】分类中，【类型】文本框中输入“0000”即可。步骤6：设置固定资产卡片的日期。卡片的日期可以直接取自于“固定资产清单”中的日期，在单元格I3中输入公式：=固定资产清单!J2，并且要将单元格I3的格式设置为日期型格式，否则将显示为日期所对应的序列号。如图所示，计算当前日期。步骤7：固定资产编号的提示设置。每一项固定资产都有唯一的编号，编号的信息直接会影响到其余固定资产信息的取得，因此我们首先设置单元格D4的输入提示。具体步骤为：选定单元格D4，执行【数据】|【数据工具】|【数据有效性】下拉菜单中的【数据有效性】命令，弹出【数据有效性】对话框，单击【输入信息】选项卡，选择“选定单元格时显示输入信息”复选框，然后在【输入信息】文本框中键入“请输入要查询制作卡片的固定资产编号”，如图所示。单击【确定】按钮后，关闭【数据有效性】对话框，选定工作表中的单元格D4，此时屏幕上将显示如图所示的提示信息。步骤8：固定资产编号的下拉列表设置。步骤6中我们设置了输入的提示信息，为了提高输入的准确度和效率，还可以进一步将该单元格设置成下拉列表式输入。关于数据下拉列表的设置方法我们在前面已经多次讲解，在此需要强调的是作为数据源的区域必须提前进行区域命名。具体步骤为：将“固定资产清单”中的“资产编号”区域进行命名，如图所示。公式设置为：=OFFSET(固定资产清单!$B$3,1,,COUNTA(固定资产清单!$B:$B)-1)然后再选择单元格D4，打开【数据有效性】对话框，单击【设置】选项卡，在【允许】文本框中选择“序列”，【来源】文本框中输入公式：=资产编号。如图。步骤9：固定资产卡片中的其他信息，如“固定资产名称”、“类别编号”、“类别名称”、“增加方式”、“部门名称”、“使用状况”、“原值”、“残值”、“折旧方法”、“已计提月份”、“开始使用日期”、“费用科目”等都可以通过公式的设置，取自于“固定资产清单”。公式设置为：类别编号：D5=INDEX(固定资产清单!C$4:C$500,MATCH($D$4,固定资产清单!B$4:B$500,0))增加方式：D6=INDEX(固定资产清单!K$4:K$500,MATCH($D$4,固定资产清单!B$4:B$500,0))使用状况：D7=INDEX(固定资产清单!J$4:J$500,MATCH($D$4,固定资产清单!B$4:B$500,0))原值：D8=INDEX(固定资产清单!N$4:N$500,MATCH($D$4,固定资产清单!B$4:B$500,0))折旧方法：D9=INDEX(固定资产清单!M$4:M$500,MATCH($D$4,固定资产清单!B$4:B$500,0))固定资产名称：F4=INDEX(固定资产清单!A$4:A$500,MATCH($D$4,固定资产清单!B$4:B$500,0))类别名称：F5=INDEX(固定资产清单!D$4:D$500,MATCH($D$4,固定资产清单!B$4:B$500,0))部门名称：F6=INDEX(固定资产清单!E$4:E$500,MATCH($D$4,固定资产清单!B$4:B$500,0))使用年限：F7=INDEX(固定资产清单!H$4:H$500,MATCH($D$4,固定资产清单!B$4:B$500,0))净残值率：F8=INDEX(固定资产清单!O$4:O$500,MATCH($D$4,固定资产清单!B$4:B$500,0))已提折旧月数：F9=INDEX(固定资产清单!Q$4:Q$500,MATCH($D$4,固定资产清单!B$4:B$500,0))开始使用日期：H7=INDEX(固定资产清单!G$4:G$500,MATCH($D$4,固定资产清单!B$4:B$500,0))净残值：H8=D8*F8费用科目：H9=INDEX(固定资产清单!F$4:F$500,MATCH($D$4,固定资产清单!B$4:B$500,0))公式的运算结果如图熟练掌握固定资产卡片的设计。四EXCEL在固定资产管理中的应用四固定资产分析固定资产是企业用来改变劳动对象的劳动资料，与其他资产相比，具有两个主要特点：一是使用年限长，能多次加入生产过程并保持其原有实物形态；二是单位价值较高，其价值随着使用的磨损逐渐部分地通过折旧形式转移到新产品中去。

企业要求对固定资产的情况进行分析。

固定资产分析的主要目的是及时掌握固定资产的使用状况和资金占用情况，为固定资产管理提供依据。固定资产分析中折旧分析是固定资产管理的重要内容。折旧是固定资产在使用过程中逐渐损耗而消失的那部分价值，这部分价值应该以折旧费用的形式计入各期成本费用，并从企业的营业收入中得到补偿，转化为货币资金，从而为固定资产的更新提供可能。本任务以第一节建立的固定资产清单为基础，基本流程包括：利用数据透视表编制固定资产折旧费用分配表，并生成本期凭证；利用数据透视表对固定资产新旧程度进行分析；利用数据透视表和数据透视图对固定资产构成进行分析。1.使用数据透视表编制固定资产折旧费用分配表数据透视表是一种交互式报表，可以各种不同方式灵活地展示数据的特征。固定资产管理中，由于受益对象（使用部门）不同，折旧费的借记科目也不同，因此需要对固定资产的折旧按受益对象（使用部门）进行分配汇总。利用数据透视表来编制折旧费用分配表，具体步骤为：步骤1：打开“固定资产系统”工作簿，新建“折旧费用分配”工作表，选定C5单元格。执行【插入】|【表】|【数据透视表】下拉菜单中的【数据透视表】命令，在弹出的【创建数据透视表】对话框中，单击“选择一个表或区域”单选按钮。

在【表/区域】文本框输入建立数据透视表的数据源区域为“固定资产清单！$A$3:$U$20”，或通过单击右侧的折叠按钮直接选择数据源区域。【选择放置数据透视表的位置】为“现有工作表”单选按钮。如图。步骤2：单击【确定】按钮，在弹出的【数据透视表字段列表】对话框中，选择“费用科目”、“使用部门”字段添加到【行标签】中、选择“原值”、“本月折旧额”字段添加到【数值】中。数据透视表制作完成，如图。步骤3：格式化数据透视表，使其美观、易懂。合并及居中单元格区域C3：F3，设置文字的字体为“华文隶书”、字号为“26”，颜色为“蓝色”，下划线为“会计专用双下划线”，合并及居中单元格区域D4：E4，设置公式为=NOW()，日期显示格式如图所示。隐藏第五行。选择【设计】|【布局】|【分类汇总】|【在组的顶部显示所有分类汇总】选项。选择【设计】|【布局】|【报表布局】|【以大纲形式显示】选项。选择【设计】|【布局】|【空行】|【在每个项目后插入空行】选项。将E列、F列字段名分别改为“原值”、“本月折旧额”。画表格线，设置E列、F列数字为“会计专用”格式，保留两位小数，无货币符号。设置对齐方式，调整行高列宽至合适的数值。2.记账凭证的生成折旧费用分配表将计提折旧额分配到有关成本和费用中，它是制作计提折旧记账凭证的依据。下面介绍计提折旧记账凭证的生成。步骤1：新建“记账凭证”工作表，制作如下图所示格式，并输入所示项目。步骤2：本工作表中借贷方金额通过公式从“折旧费用分配”表中取得。相关单元格公式分别为：D3=NOW()E5=INDEX(折旧费用分配!$E:$E,MATCH(B4,折旧费用分配!$B:$B,0))E6=INDEX(折旧费用分配!$E:$E,MATCH(B5,折旧费用分配!$B:$B,0))F7=INDEX(折旧费用分配!$E:$E,MATCH("总计",折旧费用分配!$B:$B,0))E9=SUM(D4:D7)F9=SUM(E4:E7)3.固定资产新旧程度分析利用数据透视表对固定资产新旧程度进行分析。步骤1：新建“固定资产新旧程度分析”工作表，选定C4单元格。打开【创建数据透视表】对话框，单击“选择一个表或区域”单选按钮。在【表/区域】中输入建立数据透视表的数据源区域为“固定资产清单！$A$3:$U$20”，或通过单击右侧的折叠按钮直接选择数据源区域。【选择放置数据透视表的位置】为“现有工作表”单选按钮。如图。步骤2：单击【确定】按钮，在弹出的【数据透视表字段列表】对话框中，选择“使用部门”、“资产名称”字段添加到【行标签】、选择“原值”、“累计折旧额”字段添加到【数值】。如图。步骤3：选定数据透视表中任一个单元格，执行【选项】|【工具】|【公式】|【计算字段】命令，打开【插入计算字段】对话框。在【名称：】复合框中输入“折余价值”，在【公式】文本框中输入“=原值-累计折旧额”。如图

所示。单击【确定】按钮，数据透视表中增加了一列：“求和项：折余价值”。步骤4：选定数据透视表中任一个单元格，打开【插入计算字段】对话框。在【名称：】复合框中输入“折余价值占原值比重”，在【公式】文本框中输入“=折余价值/原值”。单击【确定】按钮，在数据透视表中增加了一列：“求和项：折余价值占原值比重”。步骤3、步骤4结果如图。步骤5：格式化数据透视表，使其美观、易懂。合并及居中单元格区域C2：H2，设置文字的字体为“华文隶书”、字号为“26”，颜色为“蓝色”，下划线为“会计专用双下划线”，合并及居中单元格区域E3：F3，设置公式为=NOW()，调整日期显示格式，隐藏第四行。选择【设计】|【布局】|【报表布局】|【以大纲形式显示】选项。将E列、F列、G列、H列字段名分别改为“原值”、“累计折旧额”、“折余价值”、“折余价值占原值的比重”。画表格线，设置E列、F列、G列数字为“会计专用”格式，保留两位小数，无货币符号，H列数字为“百分比”格式，保留两位小数。设置对齐方式，调整行高列宽至合适的数值。结果如图所示。4.固定资产构成分析利用数据透视表和数据透视图对固定资产构成进行分析。步骤1：新建“固定资产构成分析”工作表，选定C5单元格。打开【创建数据透视表】对话框中，单击“选择一个表或区域”单选按钮。在【表/区域】文本框输入建立数据透视表的数据源区域为“固定资产清单！$A$3:$U$20”，或通过单击右侧的折叠按钮直接选择数据源区域。【选择放置数据透视表的位置】为“现有工作表”单选按钮。步骤2：单击【确定】按钮，在弹出的【数据透视表字段列表】对话框中，选择“使用部门”字段添加到【报表筛选】中、选择“类别名称”字段添加到【行标签】中、选择“折余价值”字段添加到【数值】中。如图。步骤3：选定数据透视表中任一个单元格，打开【插入计算字段】对话框。在【名称：】复合框中输入“比重”，在【公式】文本框中输入“=原值-累计折旧额”。单击【确定】按钮，数据透视表中增加了一列：“求和项：比重”。步骤4：双击“求和项：比重”所在单元格E6，打开【值字段设置】对话框。选择【值显示方式】，在【值显示方式】列表框中选择“占同列数据总和的百分比”。如图4-4-11所示。单击【确定】按钮，如图。步骤5：格式化数据透视表，使其美观、易懂。合并及居中单元格区域C2：E2，设置文字的字体为“华文隶书”、字号为“26”，颜色为“蓝色”，下划线为“会计专用双下划线”。设置E6单元格公式为=NOW()，调整日期显示格式。隐藏第四、第五行。设置D列数字为“会计专用”格式，保留两位小数，无货币符号，E列数字为“百分比”格式，保留两位小数。设置对齐方式，调整行高列宽至合适的数值。结果如图

所示。步骤6：选定数据透视表任一单元格，执行【插入】|【图表】|【饼图】|【分离型三维饼图】命令，即可生成图表。步骤7：格式化图表，使其美观、易懂。设置数据标签：选定图表，执行【布局】|【标签】|【数据标签】|【数据标签外】命令。设置数据标签格式：选定数据标签，单击【格式】|【当前所选内容】|【设置所选内容格式】”按钮，打开【设置数据标签格式】对话框，在【标签选项】|【标签包括】中选择“类别名称”、“百分比”复选框；在【数字】中“类别”选择“百分比”，保留两位小数。删除图例。将图表标题改为“固定资产构成分析”。选定图表区，执行【格式】|【当前所选内容】|【设置所选内容格式】命令，打开【设置图表格式】对话框，在【填充】中选择“图片或纹理填充”单选项，单击【纹理】按钮，选择第三行第五列纹理。调整字符格式、绘图区格式到合适状态。结果如图所示。步骤7：通过筛选不同使用部门，可以得到不同部门的构成数据和相应图表。如图所示。熟练掌握固定资产分析表和分析图的设计制作。四EXCEL在固定资产管理中的应用五固定资产系统的建立通过前面的任务，我们已经建立了七个工作表。利用这些工作表，可以实现以下功能：建立了固定资产清单；以固定资产清单为基础生成固定资产卡片；依托固定资产清单，形成折旧分配表，生成记账凭证；利用固定资产清单，进行固定资产新旧程度分析和构成分析。现在，我们希望将各个工作表整合成一个完整的系统，密切相互之间的联系。我们可以通过为“固定资产系统”工作簿设计一个用户界面，使财务管理人员能够一目了然了解到本工作簿的主要功能，并实现利用按钮进入各个工作表，利用宏改进固定资产系统，高效完成各种管理、分析。本任务的基本流程包括：利用绘图工具，建立用户界面；通过超链接功能，实现进入相应工作表；当完成管理和分析工作后，单击“返回首页”自动返回到用户界面；利用控件工具和宏代码改进完善本系统。1.固定资产系统用户界面的设计1.1用户界面的设计步骤1：打开“固定资产系统”工作簿，新建“首页”工作表，执行【开发工具】|【控件】|【插入】|【分组框】命令，鼠标显示为十字光标，拖动鼠标，在“首页”工作表中绘制出一个分组框。单击新建分组框的文字，将其改为“资产信息”。。如图步骤2：绘制启动各功能的按钮。Excel的【开发工具】|【控件】|【插入】下拉列表中提供有按钮，但不能控制其样式，本任务中使用Excel提供的自选图形来制作漂亮的按钮。选择【插入】|【插图】|【形状】|【矩形】|【圆角矩形】，然后设置其填充效果，再设置其阴影效果，输入文字“新增资产”。按同样方式设置“资产卡片”按钮。结果如图步骤3：向工作表中再添加两个“分组框”，复制刚绘制的按钮，修改分组框的文字，给按钮添加上相应的文字，调整其摆放位置，如图步骤4：选择【插入】|【插图】|【形状】|【矩形】|【矩形】，向工作表中添加一个矩形自选图形。选择【格式】|【形状样式】|【形状填充】|【白色】；选择【格式】|【形状样式】|【形状轮廓】|【主题颜色】|【黑色】；选择【格式】|【形状样式】|【形状轮廓】|【粗细】|【0.5磅】；选择【格式】|【形状样式】|【形状效果】|【阴影】|【外部】|【右下斜偏移】，，为其添加阴影；设置【叠放次序】|【置于底层】，然后输入标题文字“固定资产系统”，并设置为“华文隶书”“32”号，蓝色。得到如图所示的主界面。步骤5：单击【Office按钮】|【Excel选项】，打开【Excel选项】，选【高级】,取消“显示水平滚动条”、“显示垂直滚动条”、“显示行和列标题”、“显示网格线”各项前面的选中符号，如图步骤6：“首页”工作表最终显示效果，如图1.2超链接的创建步骤1：打开“首页”工作表，选中“资产卡片”按钮，执行【插入】|【链接】|【超链接】命令，打开【编辑超链接】对话框，在【链接到：】中选择“本文档中的位置”，【请键入单元格引用】中输入A1，【或在这篇文档中选择位置】中选择“固定资产卡片”，单击【确定】按钮。如图所示步骤2：将前面设置好的圆角矩形按钮复制到“固定资产卡片”工作表中，放在如图所示位置，修改按钮上的文字为“<<返回首页”，并为其创建超链接，位置为“首页”工作表。如图步骤3：打开“首页”工作表，为其他按钮创建超链接，“或在这篇文档中选择位置”列表框中选择位置分别为：“新增资产”-“固定资产清单”“折旧分配”-“折旧费用分配”“凭证生成”-“记账凭证”“新旧程度”-“固定资产新旧程度分析”“构成分析”-“固定资产构成分析”创建过程与步骤1相同步骤4：为其他工作表创建“<<返回首页”按钮，创建过程与步骤2相同。步骤5：为了防止用户操作时拖动“首页”工作表中的按钮，打开“首页”工作表，执行【审阅】|【更改】|【保护工作表】命令，可以将“首页”工作表保护起来。如图2.利用宏改进系统2.1基础数据的输入任务二中资产名称、资产编号、起始日期、使用年限、资产状态、增加方式、资产性质、原值、残值率等列数据是在“固定资产清单”工作表中通过键盘直接输入或通过数据有效性选择录入。这种方法将使所有数据处于可修改范围，用户的误操作容易破坏原来数据。我们还可以利用窗体录入以上数据，然后再填充到工作表中，这种方法的优点是将用户和工作表进行隔离，保证数据的安全。通过窗体录入数据，首先要做的工作是设计窗体，并在窗体中放置相应的控件。步骤1：执行【开发工具】|【代码】|【VisualBasic】命令（或按快捷键Alt+F11）进入VisualBasic编辑器。执行【插入】|【用户窗体】命令，插入一个用户窗体。如图步骤2：单击如图所示的属性窗口，将窗体的“Caption”属性设为“新增资产”。如图步骤3：单击控件工具箱中的标签按钮“A”，在窗体中绘制一个标签，设置其“AutoSize”属性为“True”，使标签控件的大小与输入的文字自动适应；再设置其“Caption”属性为“资产名称”；单击控件工具箱中的文本框按钮，紧接着“资产名称”标签放置一个文本框控件。如图步骤4：用类似的方法创建其他控件，然后再进行对齐操作，最后得到如图所示的用户窗体。如图。步骤5：如表4-5-1所示，列出了在编写代码时要用到的控件名称（标签控件另命名）表

控件名称设置控件名称控件名称资产名称txtname资产编号txtid启用日期txtdate使用年限txtyear资产原值txtzcyz残值率txtczl资产状态cbxzczt增加方式cbxzjfs资产性质cbxzcxz折旧方法cbxzjff增加cmdSave放弃cmdCancel2.2设计新增资产代码步骤1：首先编写窗体的初始化代码，在窗体初始化时，可添加复合框控件中的下拉列表项目。双击窗体打开代码窗口，单击事件列表框右侧的下拉箭头，选择“Initialize”事件。如图步骤2：在窗体的初始化事件中，主要是为各组合框添加列表项目。其代码如下：PrivateSubUserForm_Initialize()cbxzczt.AddItem"未使用"cbxzczt.AddItem"在用"cbxzczt.AddItem"已提足折旧"cbxzczt.AddItem"正常使用"cbxzczt.AddItem"报废"cbxzjfs.AddItem"购入"cbxzjfs.AddItem"自建"cbxzjfs.AddItem"投入"cbxzjfs.AddItem"盘盈"cbxzjfs.AddItem"捐赠"cbxzjfs.AddItem"内部调拨"cbxzjfs.AddItem"其他"cbxzcxz.AddItem"正常"cbxzcxz.AddItem"当月新增"cbxzcxz.AddItem"当月减少"cbxzcxz.AddItem"当月新增并减少"cbxzcxz.AddItem"其他"cbxzjff.AddItem"平均年限法"cbxzjff.AddItem"双倍余额递减法"cbxzjff.AddItem"固定余额递减法"cbxzjff.AddItem"年数总和法"EndSub步骤3：因为日期类的值有特殊格式要求，为了保证输入的正确性，需要对输入进行验证。双击“启用日期”文本框txtdate，为其BeforeUpdate事件编写代码如下：PrivateSubtxtdate_BeforeUpdate(ByValCancelAsMSForms.ReturnBoolean)IfNotIsDate(txtdate.Value)ThenMsgBox"请输入正确的启用日期！",,"提示"txtdate.SelStart=0txtdate.SelLength=Len(txtdate.Value)Cancel=TrueEndIfEndSub步骤4：单击“放弃”按钮cmdCancel时，将退出窗体，事件代码如下：PrivateSubcmdCancel_Click()Me.HideSheets("首页").ActivateEndSub步骤5：单击“增加”按钮cmdSave时，首先判断“资产名称”文本框中是否输入有内容，然后再调用add子过程将内容添加到“固定资产清单”中。具体代码如下：PrivateSubcmdSave_Click()Iftxtname.Value=""ThenMsgBox"请输入资产名称！",,"提示"txtname.SetFocusEndIfaddEndSub步骤6：add子过程是执行向“固定资产清单”工作表中保存窗体中输入的内容。其代码如下：Subadd()DimintrowAsIntegerSheets("固定资产清单").Activateintrow=Sheets("固定资产清单").Range("a65536").End(xlUp).Row+1Cells(introw,1)=txtname.ValueCells(introw,2).NumberFormatLocal="@"Cells(introw,2)=txtid.ValueCells(introw,7)=txtdate.ValueCells(introw,8)=txtyear.ValueCells(introw,10)=cbxzczt.ValueCells(introw,11)=cbxzjfs.ValueCells(introw,12)=cbxzcxz.ValueCells(introw,13)=cbxzjff.ValueCells(introw,14)=txtzcyz.ValueCells(introw,15)