Excel22 – 第123页

Excel 双倍余额递减法计算折旧值（DDB函数和VDB函数）

采用双倍余额递减法计算折旧，使用的是双倍余额递减法折旧函数——DDB函数和VDB函数。双倍速余额递减法也是一种加速折旧法，指的是不考虑固定资产预计净残值，根据每期期初固定资产账面余额和双倍的直线法折旧率计算固定资产折旧。本节采用VDB函数进行折旧计算。VDB函数的语法为：

VDB(cost,salvage,life,start_period,end_period,factor,no_switch)

其中，start_period参数为进行折旧计算的起始期间，start_period参数必须与life的单位相同。end_period参数为进行折旧计算的截止期间，end_period参数必须与life的单位相同。factor参数为余额递减速率（折旧因子），如果factor参数被省略，则假设为2（双倍余额递减法）。如果不想使用双倍余额递减法，可改变factor参数的值。no_switch参数为一逻辑值，指定当折旧值大于余额递减计算值时，是否转用直线折旧法。如果no_switch参数为TRUE，即使折旧值大于余额递减计算值，Excel也不转用直线折旧法；如果no_switch参数为FALSE或被忽略，且折旧值大于余额递减计算值时，Excel将转用直线折旧法。除no_switch参数以外的所有参数必须为正数。具体计算步骤如下。

STEP01：按照上述复制“直线法”工作表的方法复制“年数总和法”工作表，将复制后的“年数总和法（2）”工作表重命名为“双倍余额递减法”，并清除J4:J15单元格区域中的数据，保留文本和表格源格式，最终效果如图24-30所示。

图24-30　准备固定资产折旧分析表

STEP02：利用双倍余额递减法计算计算本期折旧。选中J4单元格，在编辑栏中输入公式“=IF(AND(I4<H4,MONTH(B4)<12),VDB(E4,G4,H4,I4-1,I4)*MONTH(B4)/12+VDB(E4,G4,H4,I4,I4+1)*(12-MONTH(B4))/12,IF(AND(I4<H4,MONTH(B4)=12),VDB(E4,G4,H4,I4-1,I4),IF(AND(YEAR($G$2)-YEAR(B4)=H4,MONTH(B4)<12),VDB(E4,G4,H4,I4-1,I4)*MONTH(B4)/12,0)))”，然后按“Enter”键返回，即可计算出第1台机器的本期折旧额，如图24-31所示。

STEP03：选中J4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至J15单元格，通过自动填充功能计算出其他机器的本期折旧额，计算结果如图24-32所示。

图24-31　计算第1台机器的本期折旧额

图24-32　计算其他机器的本期折旧

Excel 年数总和法计算折旧值（SYD函数）

采用年数总和法计算折旧，使用的是年数总和法折旧函数——SYD函数。年数总和法是加速折旧法的一种，用固定资产原值减去预计净残值后的余额作为基数，乘以一个逐年递减的分数来计算每期的折旧额。分数的分子表示固定资产还可以使用的年限，分母代表使用年限逐年数字的总和。SYD函数的语法为：

SYD(cost,salvage,life,per)

其中，per参数为指定要计算折旧的期间，时间单位与life相同。SYD函数用于计算某项固定资产按年数总和法计算的指定期间的折旧额。

STEP01：打开“固定资产折旧分析.xlsx”工作簿，在“直线法”工作表的标签处单击鼠标右键，在弹出的隐藏菜单中选择“移动或复制”选项，如图24-23所示。

图24-23　复制工作表

STEP02：随后会打开“移动或复制工作表”对话框，在“下列选定工作表之前”列表框中选择“（移至最后）”选项，勾选下方的“建立副本”复选框，然后单击“确定”按钮完成工作表的复制操作，如图24-24所示。

STEP03：工作表复制完成后，在“直线法”工作表的后会新增一个“直线法（2）”工作表，将“直线法（2）”工作表重命名为“年数总和法”。在“年数总和法”工作表中删除“K3:N15”单元格区域中的全部内容，将I3单元格和J3单元格中的文本标题分别修改为“当前年限”“本期折旧”，然后仅清除I4:J15单元格区域中的数据，保留其文本和表格源格式。最终将工作表修改为如图24-25所示的效果。

图24-24　复制工作表

图24-25　准备固定资产折旧分析表

STEP04：首先计算当前年限。选中I4单元格，在编辑栏中输入公式“=IF(YEAR($G$2)-YEAR(B4)>H4,H4,YEAR($G$2)-YEAR(B4))”，然后按“Enter”键返回，即可计算出第1台机器的当前年限，如图24-26所示。

STEP05：选中I4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至I15单元格，通过自动填充功能计算出其他机器的当前年限，计算结果如图24-27所示。

图24-26　计算第1台机器的当前年限

图24-27　计算其他机器的当前年限

STEP06：接下来计算本期折旧。选中J4单元格，在编辑栏中输入公式“=IF(AND(I4<H4,MONTH(B4)<12),SYD(E4,G4,H4,I4+1)*(MONTH($G$2)-MONTH(B4))/12+SYD(E4,G4,H4,I4)*MONTH(B4)/12,IF(AND(YEAR($G$2)-YEAR(B4)=H4,MONTH(B4)<12),SYD(E4,G4,H4,I4)*MONTH(B4)/12,0))”，然后按“Enter”键返回，即可计算出第1台机器的本期折旧额，如图24-28所示。

STEP07：选中J4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至J15单元格，通过自动填充功能计算出其他机器的本期折旧额，计算结果如图24-29所示。

图24-28　计算第1台机器的本期折旧额

图24-29　计算其他机器的本期折旧额

Excel 直线法计算折旧值（SLN函数）

采用直线法计算折旧，使用的是直线法折旧函数——SLN函数。直线法是企业计算固定资产折旧最常用的一种方法。SLN函数的语法为：

SLN(cost,salvage,life)

其中，cost参数表示固定资产原值（即例子工作表中的“总金额”），salvage参数表示固定资产报废时的预计净残值（即例子工作表中的“残值”），life参数表示折旧期限，也就是固定资产预计使用年限。使用SLN函数可以计算固定资产在使用期间每期按平均年限计算的线性折旧额。

STEP01：首先计算月折旧额。选中K4单元格，切换至“公式”选项卡，单击“函数库”组中的“插入函数”按钮，打开“插入函数”对话框，如图24-12所示。

图24-12　单击“插入函数”按钮

STEP02：打开“插入函数”对话框后，单击“或选择类别”选择框右侧的下拉按钮，在展开的下拉列表中选择“全部”选项，然后在“选择函数”列表框中选择“SLN”函数，单击“确定”按钮打开“函数参数”对话框，如图24-13所示。

STEP03：打开“函数参数”对话框后，在Cost文本框中输入“E4”，即固定资产的原值（总金额）；在Salvage文本框中输入“G4”，即固定资产残值；在Life文本框中输入I4，即固定资产的可使用月数。此时在这些文本框的右侧会出现具体数值，并在下方计算出固定资产的每期线性折旧费。最后单击“确定”按钮完成函数的插入操作，如图24-14所示。此时如果选中工作表中的K4单元格，可以看到公式“=SLN(E4,G4,I4)”，如图24-15所示。

图24-13　选择函数

图24-14　输入函数参数

STEP04：选中K4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至K15单元格，通过自动填充功能计算出其他机器的月折旧额，计算结果如图24-16所示。

图24-15　计算第1台机器的月折旧额

图24-16　计算其他机器的月折旧额

STEP05：接下来计算本期折旧。选中L4单元格，在编辑栏中输入公式“=(IF(AND(YEAR($G$2)>YEAR(B4),YEAR($G$2)<(YEAR(B4)+H4)),MONTH($G$2),IF(YEAR($G$2)=YEAR(B4)+H4,MONTH(B4),IF(YEAR($G$2)=YEAR(B4),MONTH($G$2)-MONTH(B4),0))))*K4”，然后按“Enter”键返回，即可计算出第1台机器的本期折旧额，如图24-17所示。

STEP06：选中L4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至L15单元格，通过自动填充功能计算出其他机器的本期折旧额，计算结果如图24-18所示。

STEP07：接下来计算累计折旧额。选中M4单元格，在编辑栏中输入公式“=J4*K4”，然后按“Enter”键返回，即可计算出第1台机器的累计折旧额，如图24-19所示。

图24-17　计算第1台机器的本期折旧额

图24-18　计算其他机器的本期折旧额

STEP08：选中M4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至M15单元格，通过自动填充功能计算出其他机器的累计折旧额，计算结果如图24-20所示。

图24-19　计算第1台机器的累计折旧额

图24-20　计算其他机器的累计折旧额

STEP09：最后需要根据机器的原值金额和累计折旧金额计算出机器的账面价值。选中N4单元格，在编辑栏中输入公式“=E4-M4”，然后按“Enter”键返回，即可计算出第1台机器的账面价值，如图24-21所示。

STEP10：选中N4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至N15单元格，通过自动填充功能计算出其他机器的账面价值，计算结果如图24-22所示。

图24-21　计算第1台机器的账面价值

图24-22　计算其他机器的账面价值

Excel 创建固定资产折旧分析表

在使用不同折旧方法之前，首先创建一个用于练习的固定资产折旧分析表。具体操作步骤如下。

STEP01：首先新建一个空白工作簿，将其重命名为“固定资产折旧分析.xlsx”，并将“Sheet1”工作表重命名为“直线法”，如图24-1所示。该工作表主要用于做折旧分析表，其他方法将基于此分析表做适当修改。

图24-1　新建工作表

STEP02：在“直线法”工作表中输入所需文本、数据，并适当设置文本和表格的格式。这里将字体设置为“宋体”，并设置单元格的背景颜色，标题处的背景颜色以“红色，个性色2”和“蓝色”为主，文本内容处的背景颜色以“浅黄色”为主，最后为表格依次添加内边框和外边框。用户可根据实际情况对文本和表格格式进行设置，设置后的效果如图24-2所示。

STEP03：切换至“视图”选项卡，在“显示”组中取消勾选“网格线”复选框，用于隐藏工作表中的网格线，以使表格看起来更加美观，最终效果如图24-3所示。

图24-2　输入所需文本、数据并设置格式

图24-3　隐藏网格线

STEP04：选中E4单元格，在编辑栏中输入公式“=C4*D4”，然后按“Enter”键返回，即可计算出第1台机器的总金额，即固定资产的原值，如图24-4所示。

STEP05：选中E4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至E15单元格，通过自动填充功能计算出其他机器的原值，计算结果如图24-5所示。

图24-4　计算原值

图24-5　计算其他机器的原值

STEP06：选中G4单元格，在编辑栏中输入公式“=E4*F4”，然后按“Enter”键返回，即可计算出第1台机器的残值，如图24-6所示。

STEP07：选中G4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至G15单元格，通过自动填充功能计算出其他机器的残值，计算结果如图24-7所示。

图24-6　计算第1台机器的残值

图24-7　计算其他机器的残值

STEP08：选中I4单元格，在编辑栏中输入公式“=12*H4”，然后按“Enter”键返回，即可计算出第1台机器的可使用月数，如图24-8所示。

STEP09：选中I4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至I15单元格，通过自动填充功能计算出其他机器的可使用月数，计算结果如图24-9所示。

STEP10：选中J4单元格，在编辑栏中输入公式“=IF(($G$2-B4)/365*12>0,IF(($G$2-B4)/365*12<I4,ROUND(($G$2-B4)/365*12,0),I4),0)”，然后按“Enter”键返回，即可计算出第1台机器的已使用月数，如图24-10所示。其中的ROUND函数用于将数值舍入到小数点左边或右边的指定位置，第1个参数是要被舍入的数值，第2个参数是位数，此处要被舍入的数值是当前日期减去入库日期，位数为0。

STEP11：选中J4单元格，将鼠标光标定位于单元格右下角，使用填充柄工具向下复制公式至J15单元格，通过自动填充功能计算出其他机器的已使用月数，计算结果如图24-11所示。

图24-8　计算第1台机器的可使用月数

图24-9　计算其他机器的可使用月数

图24-10　计算第1台机器的已使用月数

图24-11　计算其他机器的已使用月数

Excel 数据透视表筛选汇总结果

在使用数据透视表时，除了可以对汇总数据进行排序和更改汇总方式之外，还可以对汇总的结果进行筛选。使用筛选功能可以完成许多复杂的操作。下面通过实例说明筛选汇总结果的具体操作步骤。

STEP01：打开“数据透视表4.xlsx”工作簿，切换至“数据透视表”工作表。本例中要对汇总结果进行筛选的数据透视表如图23-50所示。

STEP02：选择“步步高”所在的单元格，即A5单元格，单击鼠标右键，在弹出的隐藏菜单中选择“筛选”选项，然后在展开的级联列表中选择“仅保留所选项目”选项，如图23-51所示。筛选后的结果如图23-52所示。

图23-50　数据透视表

图23-51　选择“仅保留所选项目”选项

STEP03：如图23-53所示，在A5单元格处再次单击鼠标右键，在弹出的隐藏菜单中选择“筛选”选项，然后在展开的级联列表中选择“从‘产品名称’中清除筛选”选项，即可清除筛选结果，此时数据透视表将恢复到打开时的状态。

如果要对数据透视表中的业务员进行筛选，例如筛选出业务员“陈圆圆”和“李红艳”的相关数据，具体操作步骤如下。

STEP01：单击“列标签”处的筛选按钮，在展开的下拉列表中取消勾选“全选”复选框，然后依次勾选“陈圆圆”复选框和“李红艳”复选框，最后单击“确定”按钮即可返回筛选结果，如图23-54所示。

STEP02：此时，筛选结果如图23-55所示。工作表中只显示了“陈圆圆”和“李红艳”两位业务员的销售数据。

图23-52　筛选结果

图23-53　清除筛选

图23-54　筛选业务员

图23-55　业务员筛选结果

STEP03：再次单击“列标签”处的筛选按钮，在展开的下拉列表中选择“从‘业务员’中清除筛选”选项即可清除当前的筛选结果，如图23-56所示。

如果要在数据透视表中筛选出销售额大于或等于30000的业务员，具体操作步骤如下。

STEP01：单击“列标签”处的筛选按钮，在展开的下拉列表中选择“值筛选”选项，然后在展开的级联列表中选择“大于或等于”选项，如图23-57所示。

STEP02：随后会打开如图23-58所示的“值筛选（业务员）”对话框，在数值条件文本框中输入“30000”，单击“确定”按钮便可以返回工作表。此时，筛选结果如图23-59所示。

图23-56　清除业务员筛选结果

图23-57　选择值筛选条件

STEP03：随后会打开如图23-58所示的“值筛选（业务员）”对话框，在数值条件文本框中输入“30000”，单击“确定”按钮便可以返回工作表。此时，筛选结果如图23-59所示。

图23-58　输入筛选条件

图23-59　筛选结果

Excel 更改数据透视表汇总方式的步骤

默认情况下，数据透视表的汇总方式为求和汇总，也可以根据需要将其更改为其他汇总方式，例如平均值、最大值、最小值、计数等。下面通过实例说明如何更改数据透视表的汇总方式。

STEP01：打开“数据透视表3.xlsx”工作簿，切换至“数据透视表”工作表。在数据透视表的数值区域选择任意单元格，如B5单元格，单击鼠标右键，在展开的下拉列表中选择“值汇总依据”选项，然后在展开的级联列表中选择“计数”选项，如图23-46所示。

STEP02：更改汇总方式后的数据透视表如图23-47所示。此时的数据透视表按“计数”方式进行汇总。

图23-46　更改值汇总方式

图23-47　计数汇总结果

也可以通过“值字段设置”命令更改汇总方式，具体操作步骤如下。

STEP01：选择B5单元格，单击鼠标右键，在展开的下拉列表中选择“值字段设置”选项，打开“值字段设置”对话框，如图23-48所示。

STEP02：打开“值字段设置”对话框后，切换至“值汇总方式”选项卡，在“计算类型”列表框中选择“计数”选项，然后单击“确定”按钮即可完成数据透视表汇总方式的更改，如图23-49所示。

图23-48　选择“值字段设置”选项

图23-49　选择计算类型

Excel 更改数据透视表的排序方式

在数据透视表中可以方便地对数据进行排序。下面通过实例介绍具体操作步骤。

STEP01：打开“数据透视表3.xlsx”工作簿，切换至“数据透视表”工作表，本例中要进行排序操作的数据透视表如图23-40所示。

STEP02：单击行标签右侧的筛选按钮，在展开的下拉列表中选择“降序”选项，如图23-41所示。经过上述操作，行标签更改为按降序排列，结果如图23-42所示。

图23-40　数据透视表

图23-41　选择“降序”选项

如果要将行标签的排序方式改回升序排列，按以下步骤进行即可实现。

STEP01：单击行标签右侧的筛选按钮，在展开的下拉列表中选择“其他排序选项”命令，打开“排序（产品名称）”对话框，如图23-43所示。

图23-42　行标签降序排列

图23-43　选择“其他排序选项”命令

STEP02：打开“排序（产品名称）”对话框后，在“排序选项”列表区域中单击选中“升序排序（从A到Z）依据”单选按钮，然后单击下方选择框右侧的下拉按钮，在展开的下拉列表中选择“产品名称”选项，最后单击“确定”按钮将产品名称（行标签）的排序方式改回升序排列，如图23-44所示。

如果要设置更多的排序选项，可以在图23-44所示的“排序（产品名称）”对话框中单击“其他选项”按钮，打开“其他排序选项（产品名称）”对话框，然后设置自动排序、主关键字排序次序、排序依据、方法等选项，如图23-45所示。例如，如果希望每次更新报表时都自动排序数据，则勾选“每次更新报表时自动排序”复选框即可。

图23-44　设置排序方式

图23-45　“其他排序选项（产品名称）”对话框

Excel 显示与隐藏字段列表

默认情况下，当选中数据透视表中的任一单元格时，在窗口右侧就会显示“数据透视表字段”窗格。如果数据透视表占用屏幕空间比较大，而暂时又不需要使用字段列表时，可以将其隐藏，当需要时再将其显示出来。

如果要隐藏字段列表，可以按照以下步骤进行操作。

打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如B4单元格，单击鼠标右键，在弹出的隐藏菜单中选择“隐藏字段列表”选项，便可以将“数据透视表字段”窗格进行隐藏，如图23-38所示。

如果要将隐藏的字段列表显示出来，可以按照以下步骤进行操作。

打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如C4单元格，单击鼠标右键，在弹出的隐藏菜单中选择“显示字段列表”选项即可，如图23-39所示。

图23-38　隐藏字段列表

图23-39　显示字段列表

Excel 刷新数据透视表图解

如果修改了工作表中数据透视表的源数据，数据透视表并不会自动随之发生相应的变化，需要用户手动进行刷新。下面通过实例介绍刷新数据透视表的具体操作步骤。

STEP01：打开“数据透视表.xlsx”工作簿，切换至“Sheet1”工作表，该工作表为创建数据透视表的源数据，如图23-34所示。

STEP02：选中C2单元格，修改第一季度可口可乐的销售额，将销售额“¥3000”修改为“¥5000”，如图23-35所示。

图23-34　源数据

图23-35　修改销售额

STEP03：切换至“Sheet4”工作表，即数据透视表所在的工作表，选择B5单元格，单击鼠标右键，在弹出的隐藏菜单中选择“刷新”选项对当前的数据透视表进行刷新，如图23-36所示。刷新后的数据透视表如图23-37所示，透视表中第一季度可口可乐的销售额被修改为“¥5000”。

图23-36　选择“刷新”选项

图23-37　刷新效果

Excel 重命名数据透视表

在创建数据透视表时，默认情况下Excel会使用“数据透视表1”“数据透视表2”这样的名称为数据透视表命名，可以更改数据透视表的名称以使其更有意义。具体操作步骤如下。

打开“数据透视表.xlsx”工作簿，切换至“Sheet4”工作表。选择数据透视表区域中的任意单元格，如C4单元格，切换至“数据透视表分析”选项卡，在“数据透视表”组中单击“数据透视表名称”文本框，使文本框处于可编辑的状态。然后在文本框中输入新的名称，如“销售分析表”，输入完成后按“Enter”返回即可，如图23-33所示。

图23-33　输入名称