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

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

2、。任务任务引出：引出： 固定资产清单通过存放与该固定资产相关的所有数据，实固定资产清单通过存放与该固定资产相关的所有数据，实现对企业的固定资产详细、全面的管理。清单中一般要包括如现对企业的固定资产详细、全面的管理。清单中一般要包括如下的项目：资产名称、资产编号、类别编号、类别名称、使用下的项目：资产名称、资产编号、类别编号、类别名称、使用部门、费用科目、起始日期、使用年限、终止日期、资产状态部门、费用科目、起始日期、使用年限、终止日期、资产状态、增加方式、资产性质、资产原值、资产净残值率、资产残值、增加方式、资产性质、资产原值、资产净残值率、资产残值、已计提月份、本月折旧额、本年计提月数、本年

3、折旧额等基、已计提月份、本月折旧额、本年计提月数、本年折旧额等基本项目。项目设置的多少可以根据实际情况灵活掌握，如果企本项目。项目设置的多少可以根据实际情况灵活掌握，如果企业管理需要，还可以包括资产设备的规格型号、制造单位等一业管理需要，还可以包括资产设备的规格型号、制造单位等一些辅助项目。些辅助项目。任务任务引出：引出：知识讲解：知识讲解：1. 查找和引用函数查找和引用函数1.1 MATCH（lookup_value,lookup_array,match_type）功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置。（1）参数说明：lookup_value：需要在数据表中查找的数值，

4、可以是数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。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或省略时

5、，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）=3知识讲解：知识讲解：1.2 OFFSET（reference，rows，cols，height，width）功能：以指定的引用为参照系，通过给定的偏移量得到新的引用。知识讲解：知识讲解：（1）参数说明：reference：作为偏移量参照系的引用区域；rows：表示相对偏移量参照系左上角的单元格上（下）偏移的行数；

6、为正数时表示向下偏移，为负数时表示向上偏移；cols：表示相对偏移量参照系左上角的单元格左（右）偏移的列数；为正数时表示向右偏移，为负数时表示向左偏移；height：表示返回的引用区域的行数；width：表示返回的引用区域的列数。知识讲解：知识讲解：（2）注意：）注意：如果行数和列数偏移量超出工作表边缘，函数 OFFSET 返回错误值 #REF!。 如果省略 height 或 width，则假设其高度或宽度与reference区域相同。 函数OFFSET 实际上并不移动任何单元格或更改选定区域，它只是返回一个引用。函数 OFFSET可用于任何需要将引用作为参数的函数。height，width：

7、必须为正数，也可以省略，如果省略，则假设其高度或宽度与第一个参数引用的区域相同。知识讲解：知识讲解：（3）举例：公式SUM(OFFSET(C2,1,2,3,1)将计算比单元格C2靠下1行并靠右2列的3行1列的区域的总值。 知识讲解：知识讲解：1.3 INDEX功能是返回表格或区域中的数值或对数值的引用。功能是返回表格或区域中的数值或对数值的引用。（1）数组形式）数组形式 INDEX（array,row_num,column_num）功能：返回数组中指定单元格或单元格数组的数值。参数说明：array：单元格区域或数组常数；row_num：数组中某行的行序号，函数从该行返回数值。如果省略row_n

8、um，则必须有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分别返回整个列

9、或行的数组数值。如果需要使用以数组形式返回的数值时，必须指向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：一个或多个单元格区域的引用。如果为引用输入一个不连续的选定区域，必须用括号括起来。如果引用中的每个区域只包含一行或一

10、列，则相应的参数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必

11、须指向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函数将I

12、NDEX函数的返回值作为单元格引用。而公式“2*INDEX（A1：B2，1，2）”将INDEX函数的返回值解释为B1单元格中的数字。知识讲解：知识讲解：说明：说明：如果要突出指标体系之间联系的视觉效果，可以不取消表页中的网格线。选择【工具】|【选项】，在【视图】|【窗口】下取消“网格线”选项，单击【确定】。结果如图所示。任务实施：任务实施：1. 固定资产基础参数设置固定资产基础参数设置 固定资产清单中涉及到的有些数据相对固定，有一定规律，为了提高输入效率，可以将这些数据组成一个系统参数表，这些参数如图所示。任务实施：任务实施：任务实施：任务实施：如果将这些数据设置为供用户选择的下拉列表，还需要

13、将这些数据区域分别进行命名，具体步骤如下：步骤步骤1：首先建立一个Excel工作簿，保存工作簿，命名为“固定资产系统”。将该工作簿sheet1工作表标签改为“系统参数”。在该工作表中输入上图所示项目。任务实施：任务实施：步骤步骤2：进行数据区域命名。选定B3：B7区域，执行【公式】【名称管理器】命令，打开【名称管理器】对话框，单击【新建】按钮，打开【编辑名称】对话框，如图所示。在【名称】文本框中输入“类别编号”。通过在【引用位置】文本框中输入公式：=OFFSET(系统参数!$B$2,1,COUNTA(系统参数!$B:$B)-1)，可以实现动态调整命名区域。单击【确定】按钮。任务实施：任务实施：

14、任务实施：任务实施：步骤步骤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,COU

15、NTA(系统参数!$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中的第一、第四个参数，单击【确定】按钮，就可以很快地完成以上的区域命名。结果如图任务实施：任务实施：

16、任务实施：任务实施：2. 固定资产清单格式的设置固定资产清单格式的设置步骤步骤1：打开“固定资产系统”工作簿，将sheet2工作表标签改为“固定资产清单”。在该工作表中输入如图所示项目。其中A3：U3区域的项目包括：“资产名称”、“资产编号”、“类别编号”、“类别名称”、“使用部门”、“费用科目”、“起始日期”、“使用年限”、“终止日期”、“资产状态”、“增加方式”、“资产性质”、“折旧方法”、“原值”、“残值率”、“残值”、“已计提月份”、“本月折旧额”、“本年计提月数”、“本年折旧额”、“累计折旧额”。任务实施：任务实施：任务实施：任务实施：注意：注意：1.在本体系中取数时，“平均资产总额

17、”一般应取期初数和期末数的平均值，由于本例资产负债表中缺少2011年度的年初数，所以分别使用了期末数作为计算依据。2.为了保证计算结果的有效性及合理性，本例对原资产负债表和利润表的个别数据进行了调整。任务实施：任务实施：步骤步骤2：单元格J2中应该填入的是固定资产清单当前日期。在单元格J2中输入公式： =NOW( )或=TODAY( )，使用这两个函数的好处是可以使折旧计算清单中的相关数据随系统日期的更新而自动更新计算。如图所示。任务实施：任务实施：任务实施：任务实施：步骤步骤3：如上图所示，J2单元格显示为日期所对应的序列号，还需要进行如下设置：选定J2单元格，执行【开始】|【数字】|【设置

18、单元格格式】命令，弹出【设置单元格格式】对话框，选择【数字】选项卡，【分类】设置为“日期”，【类型】选择为“2001年3月”。设置好后J2单元格如图所示。任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：3. 录入基础数据录入基础数据 固定资产清单表格中需要输入的基础数据包括：资产名称、资产编号、起始日期、使用年限、资产状态、增加方式、资产性质、原值、残值率等列数据。以下内容均在“固定资产清单”工作表中完成。任务实施：任务实施：1.“资产名称”列数据直接通过键盘录入。如图任务实施：任务实施：2.“资产编号”列数据设置为文本格式，通过键盘输入。本

19、企业固定资产编码方式为：部门编号+类别编号+序号；编码方案为133。步骤：步骤：选定B4：B20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“文本”格式。通过键盘输入相关资产编号，如图任务实施：任务实施：任务实施：任务实施：3.“起始日期”列数据设置为日期格式，通过键盘输入，显示为“年月日”。步骤：步骤：选定G4：G20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“日期”格式。【类型】选择“2001年3月14日”。通过键盘输入相关资产启用日期，如图任务实施：任务实施：任务实施：任务实施：4.“使用年限”列数据设置为数值格式，通过键盘输入数

20、字，显示为“年”。步骤：步骤：选定H4：H20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“自定义”格式。【类型】选择“ #年 ”，如图4-1-12所示。通过键盘输入相关资产使用年限，如图任务实施：任务实施：任务实施：任务实施：任务实施：任务实施：5.“原值”列数据设置为会计专用格式，保留两位小数，无货币符号，通过键盘输入。步骤：步骤：选定N4：N20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“会计专用”格式，保留两位小数，没有货币符号。通过键盘输入相关资产原值，如图任务实施：任务实施：任务实施：任务实施：6.“残值率”列数据设置为百分

21、比格式，保留两位小数，通过键盘输入。步骤：步骤：选定O4：O20，打开【设置单元格格式】对话框。选择【数字】选项卡，设置该区域【分类】为“百分比”格式，保留两位小数。通过键盘输入相关资产残值率，如图所示。任务实施：任务实施：任务实施：任务实施：7.“资产状态”、“增加方式”、“资产性质”、“折旧方法”等列数据通过设置数据有效性选择输入。“资产状态”、“增加方式”、“资产性质”、“折旧方法”等列数据都具有一定规律性，且数据相对固定。这些列中需要填入的数据在“系统参数”表中已经定义，可以将这些列设置为可供用户选择的下拉列表，以提高数据输入效率。任务实施：任务实施：步骤：步骤：选定“资产状态”所在的

22、单元格区域J4：J19，执行【数据】|【数据工具】|【数据有效性】|【数据有效性】命令，弹出【数据有效性】对话框，在【设置】选项卡中的【有效性条件】区域中的【允许】下拉列表中选择“序列”，然后在【来源】文本框中键入“=资产状态”，单击【确定】按钮。如图任务实施：任务实施：任务实施：任务实施：“增加方式”、“资产性质”、“折旧方法”项目都可以采用数据有效性设置，形成下拉列表，以方便数据的输入。利用设置好的下拉列表，完成“资产状态”、“增加方式”、“资产性质”、“折旧方法”列数据输入，如图 所示。任务实施：任务实施：小结评价：小结评价：熟练固定资产清单的设计及快速录入数据。EXCEL在财务中的应用

23、在财务中的应用模块模块任务任务四四 EXCEL在固定资产管在固定资产管理中的应用理中的应用二二 固定资产清单（二）固定资产清单（二）任务任务引出：引出： 在任务一的基础上完成在任务一的基础上完成“固定资产清单固定资产清单”工作表工作表其他列公式的设置。其他列公式的设置。任务分析：任务分析： 固定资产清单中除了基础数据列需要输入外，其固定资产清单中除了基础数据列需要输入外，其他各列均可通过设置公式计算得到。需要设置公式的列他各列均可通过设置公式计算得到。需要设置公式的列包括类别编号、类别名称、使用部门、费用科目、终止包括类别编号、类别名称、使用部门、费用科目、终止日期、残值、已计提月份、本月折旧

24、额、本年计提月数、日期、残值、已计提月份、本月折旧额、本年计提月数、本年折旧额、累计折旧额等。本任务在本年折旧额、累计折旧额等。本任务在“固定资产清单固定资产清单”工作表中完成。工作表中完成。知识讲解：知识讲解：1. 折旧函数折旧函数1.1 SLN(cost,salvage,life)功能：用平均年限法计算某项资产折旧额。（1）参数说明：cost：资产原值；salvage：资产在折旧期末的价值（也称为资产残值）；life：折旧期限（有时也称作资产的使用寿命）。若为年，计算的是年折旧额；若为月，则计算的是月折旧额。（2）注意：如果采用工作量法计算折旧，应在SLN函数的life参数中输入预计的总工

25、作量，这样可以得到每单位工作量的折旧额，然后根据每期的工作量和每单位工作量的折旧额计算各期的折旧额。知识讲解：知识讲解：1.2 DB(cost,salvage,life,period,month)功能：用定率余额递减法计算某项资产折旧额。（1）参数说明：cost：资产原值；salvage：资产在折旧期末的价值（也称为资产残值）；life：折旧期限（有时也称作资产的使用寿命）；period：需要计算折旧值的期间。（2）注意：period必须使用与life相同的单位；month为第一年的月份数，如省略，则假设为12。知识讲解：知识讲解：1.3 DDB(cost,salvage,life,perio

26、d,factor)功能：用双倍余额递减法或其他指定方法，计算某项资产在指定期间内的折旧额。参数说明：cost：资产原值；salvage：资产在折旧期末的价值（也称为资产残值）；life：折旧期限（有时也称作资产的使用寿命）；period：需要计算折旧值的期间，period 必须使用与 life 相同的单位；factor：余额递减速率。如果 factor 被省略，则假设为2（双倍余额递减法）；这五个参数都必须为正数。知识讲解：知识讲解：1.4 VDB(cost,salvage,life,start_period,end_period,factor,no_switch) 功能：用双倍余额递减法或其

27、他指定的方法，计算指定的任何期间内的资产折旧额。（1）参数说明：cost、salvage、life、factor同DDB说明；start_period：进行折旧计算的起始期间，它必须与life的单位相同；end-_period：进行折旧计算的截止期间，它必须与life的单位相同；知识讲解：知识讲解：no-_switch：逻辑值，指定当按直线法计算的折旧额大于按余额递减计算的折旧额时，是否转用直线折旧法。如果no_switch为TRUE，即使按直线法计算的折旧额大于按余额递减计算值，Excel也不转用直线折旧法计算折旧。如果no_switch为FALSE或被忽略，且按直线法计算的折旧额大于余额递

28、减计算值时，Excel将转用直线折旧法计算折旧。（2）注意：以上各参数除no_switch外必须都为正数。知识讲解：知识讲解：1.5 SYD(cost,salvage,life,per)功能：用年数总和法计算某项资产的折旧额。参数说明：cost：资产原值；salvage：资产在折旧期末的价值（也称为资产残值）；life：折旧期限（有时也称作资产的使用寿命）；per：期间，其单位与life相同。任务实施：任务实施：1.“类别编号类别编号”、“类别名称类别名称”、“使用部门使用部门”列公式列公式1.1 “类别编号类别编号”列公式设置列公式设置步骤步骤1：由于“资产编号”列已经通过手工输入，编码方式

29、为：部门编号+类别编号+序号，编码方案为133。因此可以利用Excel中的MID函数从资产编号中取得“类别编号”。例如“资产编号”是“1011001”，在编号的左边第一位“1”是“使用部门”办公室的编号，左边第二到第四位“011”是“类别编号”，表示房屋。因此，C4单元格公式为：=MID（B4,2,3），如图所示。任务实施：任务实施：任务实施：任务实施：步骤步骤2：由于每项固定资产“类别编号”的计算方法相同，利用填充柄将该公式复制到C5：C20中所有填写固定资产“类别编号”的单元格中。任务实施：任务实施：1.2 “类别名称类别名称”列公式设置列公式设置步骤步骤1：“类别名称”与“类别编号”具有

30、对应关系，可以采用查询函数，根据“类别编号”查询“类别名称”。D4单元格公式为：=INDEX(类别名称,MATCH(MID(B4,2,3),类别编号,0) 或者=LOOKUP(C4,类别编号,类别名称)，如图 所示。任务实施：任务实施：任务实施：任务实施：步骤步骤2：由于每项固定资产“类别名称”的计算方法相同，利用填充柄将该公式复制到D5：D20中所有填写固定资产“类别名称”的单元格中。任务实施：任务实施：1.3 “使用部门使用部门”列公式设置列公式设置步骤步骤1：结合“类别编号”、“类别名称”列公式所用知识点，设计“使用部门”列公式。E4单元格公式为：=INDEX(部门名称,MATCH(MI

31、D(B4,1,1),部门编号,0) 或者=LOOKUP(MID(B4,1,1),部门编号,部门名称)，如图所示任务实施：任务实施：任务实施：任务实施：步骤步骤2：由于每项固定资产“使用部门”的计算方法相同，利用填充柄将该公式复制到E5：E20中所有填写固定资产“使用部门”的单元格中。任务实施：任务实施：2.“费用科目费用科目”列公式列公式每月计提的固定资产折旧费，应根据用途计入相关资产的成本或者当前损益，借记“制造费用”、“管理费用”等科目，在固定资产清单中设置“费用科目”列，便于日后费用的归集。“费用科目”列的设置可以利用【数据有效性】的下拉列表功能来输入，前面已经多次讲解，此处不再赘述。步

32、骤步骤1：“费用科目”列还可以利用VLOOKUP函数从“系统参数”表中取得。F4单元格公式为：=VLOOKUP(E4,系统参数!$E$3:$F$7,2,FALSE)。步骤步骤2：利用填充柄复制到其他固定资产“费用科目”单元格中。任务实施：任务实施：任务实施：任务实施：3.“终止日期终止日期”列公式列公式固定资产应当按月计提折旧，通常对当月增加的固定资产，当月不提折旧，从下月起计提；对当月减少的固定资产，当月照提，从下月起不提折旧。因此终止日期的设置主要是起到提醒该项固定资产是否已经到了停止计提折旧的作用。步骤步骤1：终止日期的计算与起始日期和使用年限有关。I4单元格公式为：=DATE(YEAR

33、(G4)+H4,MONTH(G4),DAY(G4)。步骤步骤2：由于每个固定资产终止日期的计算都是相同的方法，利用填充柄将该公式复制到其他固定资产“终止日期”的单元格中，如图任务实施：任务实施：任务实施：任务实施：4.“残值残值”列公式列公式步骤步骤1：固定资产净残值决定于其原值和残值率，利用公式“原值”、“残值率”两列中取得数据计算得到。P4单元格公式为：=N4*O4。步骤步骤2：利用填充柄将该公式复制到其他固定资产“残值”单元格中，如图任务实施：任务实施：任务实施：任务实施：5.“已计提月份已计提月份”列公式列公式 已计提月份是指从起始日期开始到当前日期，该项固定资产已经计提折旧的月份。我

34、国会计制度规定当月新增的固定资产从下月开始计算折旧，因此，固定资产如果是该月新增，则已计提月份为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)”，三部分相加后再减

35、去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，可以得到该项固定资产总共已计提的月份。以上三种方法都可以用来计算已

36、计提月份，但有时计算出来的以上三种方法都可以用来计算已计提月份，但有时计算出来的结果会有误差，一个企业选择其中一种不会影响其折旧总额，结果会有误差，一个企业选择其中一种不会影响其折旧总额，介绍给大家是为了拓展解决问题的思路。介绍给大家是为了拓展解决问题的思路。任务实施：任务实施：利用填充柄将该公式复制到其他固定资产“已计提月份”单元格中。任务实施：任务实施：6. 设置到期提醒设置到期提醒 当月减少的固定资产，当月计提折旧，下月起不提折旧。通过在固定资产清单中提供到期提醒设置，避免使用者忽略到期时间而计提折旧带来不必要的麻烦。可以利用Excel中的条件格式来设置到期提醒。为说明问题，将固定资产清

37、单做如下临时更改：G8单元格数值改为“2009年9月1日”，G13单元格数值改为“2007年12月1日”。任务实施：任务实施：任务实施：任务实施：步骤步骤1：选定A4：U20单元格区域，执行【开始】|【样式】|【条件格式】|【新建规则】命令，打开【新建格式规则】对话框，选择规则类型为“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”文本框中设置公式：=$Q4=$H4*12，单击【格式】按钮，设置符合公式要求的单元格为红色背景，如图任务实施：任务实施：步骤步骤2：效果如图任务实施：任务实施：7.“本月折旧额本月折旧额”列公式列公式 我国大部分企业的固定资产折旧一般都采用直线折旧法

38、，假设我们先考虑所有固定资产均采用直线折旧法，则在计算折旧额时只需要知道三个基本因素（原值、预计净残值、使用年限），就可以计算出当期应提折旧。方法一：方法一：采用Excel的内置折旧函数SLN来计算固定资产折旧额。R4公式单元格公式为：=SLN(N4,P4,H4*12)利用填充柄将该公式复制到其他固定资产“本月折旧额”单元格中，如图任务实施：任务实施：任务实施：任务实施：方法二：方法二：直接用基本运算功能来计算折旧，这种方法比较简单，在此就不再显示图表。公式设置：=(N4-P4)/(12*H4)公式解析：=（原值-预计净残值）/使用期限通过以上的计算，有些固定资产的折旧计算出来后可能会出现很多

39、小数位数，可以通过四舍五入函数的将其保留为两位小数，进一步完善的公式为。公式设置：=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)/12 任务实施：任务实施：任务实施：任务实施：8.“本年计提月数本年计提月数”列公式列公式我国会计制度对固定资产的折旧有如下规定：（1）当月启用的设备从下月开始计算折旧；（2）设备非本年开始启用，且本年全年一直使用，则本年

40、每月均应进行折旧；（3）设备本年开始启用，且一直用到年末，则本年折旧月数从启用的下一个月起至年末。任务实施：任务实施：由于固定资产的使用寿命一般都在几年以上，所以本年启用的设备本年就停用的情况可以不予考虑。在判断本年折旧月份时，可以分为两种情况：一是如果起始的年份小于当前年份，并且当前年份小于起始年份和使用年限之和，则表示资产全年处于正常使用状态；二是本年内到期，则本年应折旧的月份数应为起始月份数。基于以上的分析，S4单元格公式为：=IF(J4=报废,0,IF(AND(YEAR(G4)YEAR($J$2),YEAR($J$2)(YEAR(G4)+H4),12, MONTH(G4)利用填充柄将该

41、公式复制到其他固定资产“本年计提月数”单元格中，如图任务实施：任务实施：任务实施：任务实施：9.“本年折旧额本年折旧额”列公式列公式本年应计提折旧总额即为月折旧额乘以本年折旧月份数。因此T4单元格公式为：=R4*S4利用填充柄将该公式复制到其他固定资产“本年折旧额”单元格中，如图任务实施：任务实施：任务实施：任务实施：10.“累计折旧额累计折旧额”列公式列公式因为直线折旧法是按照固定资产使用年限平均计算年折旧额的计算方法，每期的折旧额是相同的。所以累计折旧只需要通过已计提月份和本期计提折旧额就可以直接计算出来了。U4单元格公式为：=(Q4+1)*R4利用填充柄将该公式复制到其他固定资产“累计折

42、旧”单元格中，如图任务实施：任务实施：小结评价：小结评价：熟练掌握各类折旧函数的灵活使用。EXCEL在财务中的应用在财务中的应用模块模块任务任务四四 EXCEL在固定资产管在固定资产管理中的应用理中的应用三三 固定资产卡片固定资产卡片任务任务引出：引出： 固定资产清单中包含了所有的固定资产信息，但固定资产清单中包含了所有的固定资产信息，但当只需要查看某一项固定资产时，该表格就显得比较当只需要查看某一项固定资产时，该表格就显得比较繁杂，不易于查找。固定资产卡片是按照固定资产项繁杂，不易于查找。固定资产卡片是按照固定资产项目开设，用以进行固定资产明细核算的账簿。在一些目开设，用以进行固定资产明细核

43、算的账簿。在一些管理比较正规的企业，大型的机器设备类固定资产通管理比较正规的企业，大型的机器设备类固定资产通常会通过固定资产卡片进行管理。常会通过固定资产卡片进行管理。任务分析：任务分析： 固定资产卡片中的数据，可以利用固定资产卡片中的数据，可以利用Excel中的公式中的公式从固定资产清单中取得。以固定资产卡片中的数据为基从固定资产清单中取得。以固定资产卡片中的数据为基础，还可以利用公式对折旧额的相关数据进行计算。本础，还可以利用公式对折旧额的相关数据进行计算。本任务的基本流程包括：设置固定资产卡片格式；录入相任务的基本流程包括：设置固定资产卡片格式；录入相关单元格公式；输入固定资产编号，生成

44、固定资产卡片。关单元格公式；输入固定资产编号，生成固定资产卡片。任务实施：任务实施：卡片制作卡片制作 步骤步骤1：打开“固定资产系统”工作簿，将sheet3工作表标签改为“固定资产卡片”步骤步骤2：在“固定资产卡片”工作表中输入如图所示表格项目。任务实施：任务实施：任务实施：任务实施：步骤步骤3：合并及居中单元格区域C2：I2，设置文字的字体为“华文隶书”、字号为“26”，颜色为“蓝色”，下划线为“会计用双下划线”，调整行高至合适的数值。如图。任务实施：任务实施：任务实施：任务实施：步骤步骤4：将表格项目所在单元格填充为灰色，并设置对齐方式和边框，适当调整行高和列宽。设置后的效果如图任务实施：

45、任务实施：任务实施：任务实施：步骤步骤5：设置卡片编号的长度。假设卡片编号的长度是固定的，为了输入方便，我们可以提前设置好单元格D3的格式，例如卡片编号的长度是4位，不够4位的前面补“0”，具体操作步骤为：选择单元格D3，打开【设置单元格格式】对话框【数字】选项卡，在【自定义】分类中，【类型】文本框中输入“0000”即可。步骤步骤6：设置固定资产卡片的日期。卡片的日期可以直接取自于“固定资产清单”中的日期，在单元格I3中输入公式：=固定资产清单!J2，并且要将单元格I3的格式设置为日期型格式，否则将显示为日期所对应的序列号。如图所示，计算当前日期。任务实施：任务实施：任务实施：任务实施：步骤步

46、骤7：固定资产编号的提示设置。每一项固定资产都有唯一的编号，编号的信息直接会影响到其余固定资产信息的取得，因此我们首先设置单元格D4的输入提示。具体步骤为：选定单元格D4，执行【数据】|【数据工具】|【数据有效性】下拉菜单中的【数据有效性】命令，弹出【数据有效性】对话框，单击【输入信息】选项卡，选择“选定单元格时显示输入信息”复选框，然后在【输入信息】文本框中键入“请输入要查询制作卡片的固定资产编号”，如图所示。任务实施：任务实施：任务实施：任务实施：单击【确定】按钮后，关闭【数据有效性】对话框，选定工作表中的单元格D4，此时屏幕上将显示如图所示的提示信息。任务实施：任务实施：任务实施：任务实

47、施：步骤步骤8：固定资产编号的下拉列表设置。步骤6中我们设置了输入的提示信息，为了提高输入的准确度和效率，还可以进一步将该单元格设置成下拉列表式输入。关于数据下拉列表的设置方法我们在前面已经多次讲解，在此需要强调的是作为数据源的区域必须提前进行区域命名。具体步骤为：将“固定资产清单”中的“资产编号”区域进行命名，如图所示。公式设置为：=OFFSET(固定资产清单!$B$3,1,COUNTA(固定资产清单!$B:$B)-1)任务实施：任务实施：任务实施：任务实施：然后再选择单元格D4，打开【数据有效性】对话框，单击【设置】选项卡，在【允许】文本框中选择“序列”，【来源】文本框中输入公式：=资产编

48、号。如图。任务实施：任务实施：步骤步骤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(固定资产清

49、单!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)任务实施

50、：任务实施：部门名称：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

51、($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在财务中的应用在财务中的应用模块模块任务任务四四 EXCEL在固定资产管在固定资产管理中的应用理中的应用四四 固定资产分析固定资产分析任务任务引出：引出： 固定资产是企业用来改变劳动对象的劳动资料，固定资产是企业用来改变劳动对象的劳动资料，与其他资产相比，具有两个主要特点：一是使用年限与其他资产相比，具有两

52、个主要特点：一是使用年限长，能多次加入生产过程并保持其原有实物形态；二长，能多次加入生产过程并保持其原有实物形态；二是单位价值较高，其价值随着使用的磨损逐渐部分地是单位价值较高，其价值随着使用的磨损逐渐部分地通过折旧形式转移到新产品中去。通过折旧形式转移到新产品中去。 企业要求对固定资产的情况进行分析。企业要求对固定资产的情况进行分析。任务分析：任务分析： 固定资产分析的主要目的是及时掌握固定资产的使固定资产分析的主要目的是及时掌握固定资产的使用状况和资金占用情况，为固定资产管理提供依据。固用状况和资金占用情况，为固定资产管理提供依据。固定资产分析中折旧分析是固定资产管理的重要内容。折定资产分

53、析中折旧分析是固定资产管理的重要内容。折旧是固定资产在使用过程中逐渐损耗而消失的那部分价旧是固定资产在使用过程中逐渐损耗而消失的那部分价值，这部分价值应该以折旧费用的形式计入各期成本费值，这部分价值应该以折旧费用的形式计入各期成本费用，并从企业的营业收入中得到补偿，转化为货币资金，用，并从企业的营业收入中得到补偿，转化为货币资金，从而为固定资产的更新提供可能。从而为固定资产的更新提供可能。任务分析：任务分析： 本任务以第一节建立的固定资产清单为基础，基本本任务以第一节建立的固定资产清单为基础，基本流程包括：利用数据透视表编制固定资产折旧费用分配流程包括：利用数据透视表编制固定资产折旧费用分配表

54、，并生成本期凭证；利用数据透视表对固定资产新旧表，并生成本期凭证；利用数据透视表对固定资产新旧程度进行分析；利用数据透视表和数据透视图对固定资程度进行分析；利用数据透视表和数据透视图对固定资产构成进行分析。产构成进行分析。任务实施：任务实施：1. 使用数据透视表编制固定资产折旧费用分配表使用数据透视表编制固定资产折旧费用分配表 数据透视表是一种交互式报表，可以各种不同方式灵活地展示数据的特征。 固定资产管理中，由于受益对象（使用部门）不同，折旧费的借记科目也不同，因此需要对固定资产的折旧按受益对象（使用部门）进行分配汇总。 利用数据透视表来编制折旧费用分配表，具体步骤为：任务实施：任务实施：步

55、骤步骤1：打开“固定资产系统”工作簿，新建“折旧费用分配”工作表，选定C5单元格。执行【插入】|【表】|【数据透视表】下拉菜单中的【数据透视表】命令，在弹出的【创建数据透视表】对话框中，单击“选择一个表或区域”单选按钮。 在【表/区域】文本框输入建立数据透视表的数据源区域为“固定资产清单！$A$3:$U$20”，或通过单击右侧的折叠按钮直接选择数据源区域。【选择放置数据透视表的位置】为“现有工作表”单选按钮。如图。任务实施：任务实施：任务实施：任务实施：步骤步骤2：单击【确定】按钮，在弹出的【数据透视表字段列表】对话框中，选择“费用科目”、“使用部门”字段添加到【行标签】中、选择“原值”、“本

56、月折旧额”字段添加到【数值】中。数据透视表制作完成，如图。 任务实施：任务实施：任务实施：任务实施：步骤步骤3：格式化数据透视表，使其美观、易懂。合并及居中单元格区域C3：F3，设置文字的字体为“华文隶书”、字号为“26”，颜色为“蓝色”，下划线为“会计专用双下划线”，合并及居中单元格区域D4：E4，设置公式为=NOW( )，日期显示格式如图所示。隐藏第五行。任务实施：任务实施：任务实施：任务实施：选择【设计】|【布局】|【分类汇总】|【在组的顶部显示所有分类汇总】选项。选择【设计】|【布局】|【报表布局】|【以大纲形式显示】选项。选择【设计】|【布局】|【空行】|【在每个项目后插入空行】选项

57、。将E列、F列字段名分别改为“原值”、“本月折旧额”。画表格线，设置E列、F列数字为“会计专用”格式，保留两位小数，无货币符号。设置对齐方式，调整行高列宽至合适的数值。 任务实施：任务实施：2. 2. 记账凭证的生成记账凭证的生成折旧费用分配表将计提折旧额分配到有关成本和费用中，它是制作计提折旧记账凭证的依据。下面介绍计提折旧记账凭证的生成。步骤步骤1 1：新建“记账凭证”工作表，制作如下图所示格式，并输入所示项目。任务实施：任务实施：任务实施：任务实施：步骤步骤2：本工作表中借贷方金额通过公式从“折旧费用分配”表中取得。相关单元格公式分别为：D3=NOW( )E5=INDEX(折旧费用分配!

58、$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

59、$3:$U$20”，或通过单击右侧的折叠按钮直接选择数据源区域。【选择放置数据透视表的位置】为“现有工作表”单选按钮。如图。任务实施：任务实施：任务实施：任务实施：步骤步骤2：单击【确定】按钮，在弹出的【数据透视表字段列表】对话框中，选择“使用部门”、“资产名称”字段添加到【行标签】、选择“原值”、“累计折旧额”字段添加到【数值】。如图。任务实施：任务实施：任务实施：任务实施：步骤步骤3：选定数据透视表中任一个单元格，执行【选项】|【工具】|【公式】|【计算字段】命令，打开【插入计算字段】对话框。在【名称：】复合框中输入“折余价值”，在【公式】文本框中输入“=原值-累计折旧额”。如图 所示。单

60、击【确定】按钮，数据透视表中增加了一列：“求和项：折余价值”。任务实施：任务实施：任务实施：任务实施：步骤步骤4：选定数据透视表中任一个单元格，打开【插入计算字段】对话框。在【名称：】复合框中输入“折余价值占原值比重”，在【公式】文本框中输入“=折余价值/原值”。单击【确定】按钮，在数据透视表中增加了一列：“求和项：折余价值占原值比重”。步骤3、步骤4结果如图。任务实施：任务实施：任务实施：任务实施：步骤步骤5：格式化数据透视表，使其美观、易懂。合并及居中单元格区域C2：H2，设置文字的字体为“华文隶书”、字号为“26”，颜色为“蓝色”，下划线为“会计专用双下划线”，合并及居中单元格区域E3：