办公软件应用技术考试课件

数值数据

1、可以使用科学计数法表示数字，如123000可写成1.23E5。 2、数值数字右对齐，数值输入超长，以科学记数法显示。

3、如果数字太长无法在单元格中显示，单元格将以“###”显示，此时需更改列宽。 4、负数前加负号“-”。

5、数字前加“￥”或“$”则以货币形式显示，例如： ￥ 123，456。 6、数字也可用百分比形式表示。如45%

【例】以下数据都是合法的数字输入形式：

2.780；1.789%；50E2；0 4/5；$7.6；￥8.3

数据自动填充

使用填充柄自动填充一批有规律的数据，例如等差、等比数列和预定义的序列。

– 单个单元格内容为纯字符、纯数字、或是公式，填充相当于复制

– 单个单元格内容为文字数字混合体，填充时文字不变，最右边的数字递增

单元格的引用

(1) 概念

相对引用：公式中引用的单元格地址在公式复制、移动时自行调整 绝对引用：公式中引用的单元格地址在公式复制、移动时不会改变 (2)表示

相对引用：列坐标行坐标 例B6， A4 , C5:F8 绝对引用：$列坐标$行坐标 例$B$6, $A$4 , $C$5:$F$8 混合引用：列坐标$行坐标 例 B$6, A$4, C$5:F$8 $列坐标行坐标 例 $B6, $A4, $C5:$F8 引用同一工作簿中其它工作表中的单元格： Sheet2!B6+Sheet1!A4 (3) 相互转换

选定单元格中的引用部分 反复按 F4 键

EXCEL 常用的数据安全策略

(1)数据隐藏

隐藏工作簿或者工作表的一部分，数据将从视图中消失，但并没有从工作簿中删除。如果保存并关闭了工作簿，下次打开它时隐藏的数据仍然会是隐藏的。打印工作簿时，Microsoft Excel不会打印隐藏部分。

可以隐藏工作簿、工作表、行、列、单元格、公式或者区域。 (2)数据保护

保护工作簿和工作表，可以限制修改数据。如想修改数据必须取消保护。

• 工作簿保护:保护工作簿的结构不被修改。工作簿中的工作表不能进行移动、删除、

隐藏、取消隐藏或重新命名，也不能插入新的工作表。 • 工作表保护:保护工作表中的数据不被修改。 • 为防止他人取消保护，可以键入密码。 (3)隐藏工作表并使用密码保护工作簿

工作簿可以读写，但是某些工作表通过密码才能看得见。

(4)公式的保护

隐藏公式,用口令保护工作表 (5)行列或单元格的保护

隐藏行列或单元格,用口令保护工作表 (6)工作簿设置密码

单击“选项”菜单中的“安全性”命令可以为工作薄设置打开和修改密码，保存后，再打开该文件必须输入打开密码，如要修改数据必须输入修改密码。

工作表拆分

(1)工作表的拆分：把工作表窗口拆分为几个窗格。每个窗格中都包含了工作表的内容，便于对工作表中感兴趣部分对比观看。可以进行水平拆分、垂直拆分、水平和垂直拆分

工作表冻结

冻结窗格使您得以选择滚动工作表时始终保持可见的数据。在滚动时保持行和列标志可见。 （1）选择冻结位置

（2）选择“窗口”菜单中的“冻结窗口”命令。

Excel错误信息

1、＃＃＃＃＃!: 单元格中数值数据太长，容纳不下；

2、＃DIV/O! :除数为0，或者除数指向了一个空单元格。 3、＃VALUE! 在需要数字或逻辑值时输入了文本；

4、＃name! 使用了Excel不能识别的文本而产生的错误；删除了公式中使用的共同名称或使用了不存在以及拼写错误的名称所致。

5、＃N/A ! ，在函数或公式中没有可用数值时产生的错误信息。

6、＃REF! 单元格引用无效。比如，我们删除了有其他公式引用的单元格。

7、＃NUM! 公式或函数中某个数字有问题。例如，在需要数字参数的函数中使用了不能接受的参数，或者公式产生的数字太大或太小等。

复回归

通过给图表加入趋势线只能进行简单的单一参数的回归分析，但在许多情况下需要使用多个自变量来预测一个变量的情况,这种回归分析叫复回归，复回归必须通过数据分析工具进行。 例9、银行为了核发信用卡，收集了申请人的每月总收入、不动产、动产、每月房贷与抚养支出费用等数据，并以业务主管员的经验，主观地给予一个信用分数。为使评估信用分数能有一套公式，免得老是依赖主管评分，请使用复回归求其回归方程式。

例10.我们收集了某厂家同一车型中旧车的车龄,行驶里程与售价数据，请使用数据分析工具，求车龄,行驶里程对售价的回归方程

y = -1.5407*车龄-0.4278*里程 + 65.9959

现值

1、PV 函数

PV函数用来计算某项固定年金的现值。年金现值就是未来各期年金现在的价值的总和。

例如:今年的一万元一定比明年的一万元有价值,所以,如果在未来连续的20年间,每年年底给10000元,以现在的角度来看,其值并非200,000元要想知道到底为多少,就可使用PV函数,假设年利率4.5%.

则其现值为:PV(4.5%,20,10000,0,0)=-130079.36

投资分析思想:如果投资收益的现值大于投资的成本价值，则这项投资是有合算的，否则不合算。

语法：PV(rate,nper,pmt,fv,type)

• Rate:各期利率。

•

Nper:投资（或贷款）的付款期总数。

• Pmt:各期所应支付的金额，整个年金期间保持不变。

• Fv: 未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值

为零（一笔贷款的未来值即为零）。

• Type:用以指定各期的付款时间是在期初还是期末。

例1，某项投资20万，往后20年，每月月底可领回1500，年利率为4.5%,问这项投资的是否合算?

该项年金的现值为：PV(4.5%/12, 12*20,1500,0,0) 计算结果为：￥- 237098 。 负值表示这是一笔付款，也就是支出现金流。投资收益的现值 237098 大于实际的投资成本（20万）。因此，这项投资相对于4.5%的投资收益率是合算的。 例2 某房屋当前售价3,500,000元,预计4年后售出,扣除税金和手续费后可净得5,000,000元,问此屋是否值得购买(当前的年利率为5.2%)?

问题实质是根据未来值求现值。 Pv(5.2%,4,0,5000000,0)=-4,082,320 2、求非固定年金的现值NPV

NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的现值。 语法：NPV(rate,value1,value2, ...) Rate:为各期贴现率，是一固定值；

value1,value2,...代表1到29笔支出及收入的参数值，value1,value2,...所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。

NPV按次序使用value1,value2，来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。

例1.假设要购买一项保险年金，该保险可以在第1年末回报￥3000,第2年末回报￥5000,第3到第7年每年末领回8000元,第8年和第9年没有收益,第10年末领回20000元。此项年金的购买成本为50,000，假定投资回报率为8%。那么该项年金的是否值得购买? NPV(8%,3000,5000,8000, 8000, 8000, 8000, 8000,0,0,20000)=43,713.18 43,713.18

概念

单变量求解: 是EXCEL提供的数据分析工具,如果已知一个公式的预期结果，可以确定此公式变量输入值。

规划求解: 是EXCEL提供的数据分析工具之一,可以解决在运筹学中的线性规划问题,即实际应用中的最优化问题,如成本最低,运费最省等。

模拟运算表: 是EXCEL提供的数据预测分析工具之一,根据数学模型(公式)为一组假设数据产生预测结果,以供分析决策。

相关性分析：判断两组数据集之间的关系。使用相关系数表示两组数据集之间的相关程度。在EXCEL中可以使用相关性分析函数进行相关性分析,也可以使用数据分析工具进行相关性分析 。

相关系数: 相关性分析的返回值,用来确定两个区域中数据的变化是否相关以及相关的程度。用R表示

R>0：一个集合的较大数据与另一个集合的较大数据相对应（正相关） R

回归分析:当一个结果与一个或多个参数之间存在联系时，可以进行回归分析，回归分析的目标是得到反映变量与结果之间因果关系的方程式,通常可由一个或多个自变量来预测一个变量的值。

判定系数:是相关系数的平方值,用来确定回归方程式的可解释性，即吻合程度。范围在

0-1之间，越接近1，解释性越强，即吻合程度越高。

线性回归:如果变量与结果之间具有线性关系,我们可以用线性方程式来描述它们之间的关系,这种回归方法叫线性回归.

非线性回归:如果变量与结果之间不具有线性关系,我们必须用非线性方程式来描述它们之间的关系(如指数关系,对数关系等等),这种回归方法叫非线性回归.

单回归:当一个结果只与一个参数存在联系时，进行的回归分析称为单回归。如y=ax+b 复回归:当一个结果与多个参数存在联系时，进行的回归分析称为复回归。

年金:在某一段连续时间内，一系列的固定金额给付活动。例如汽车、房屋的分期付款就是年金的一种。

现值:在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。 未来值:在所有付款发生后的投资或贷款的价值。例如：零存整取的期末领回金额。

折旧函数

SLN（cost,salvage,life）:平均法折旧

各个使用周期所负担的使用成本前后不均匀。因为：服务能力逐年下降，而维修费用则逐年上升。这样，前几期享受较大的服务潜力，而负担较低的成本费用，后几期享受较小的服务潜力，而负担相同的成本费用。 只考虑使用年限，并没有考虑实际加班或减班的情况。

所以平均法（线性折旧法）较适合于生产情况大致相同，技术进步因素影响较少的资产 SYD（成本，残值,使用年限,期别）:年数合计法折旧

也叫加速折旧法，使前几期享受较大的服务潜力，而负担较高的成本。也可使固定资产的帐面价值较接近于市价；前期多提折旧，可以降低企业早期应税金额，将应税时间往后递延，可以获得货币的时间价值。

DB（成本，残值,使用年限,期次，第一年的月数）:固定余额递减法

也是一种加速折旧法,折旧速率介于平均法与年数合计法之间,由于算法的原因,最后一期的折旧额需要调整,才能是折旧总额等于应折旧总额。

年金函数

PMT:年金期付款函数

PMT函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。 IPMT:付款利息函数

IPMT函数基于固定利率及等额分期付款方式，返回投资或贷款的某期付款中的利息金额。 PPMT:付款本金函数

PPMT函数基于固定利率及等额分期付款方式，返回投资或贷款的某期付款中的本金金额。 CUMIPMT:累计利息函数

CUMIPMT函数基于固定利率及等额分期付款方式，返回开始期次到结束期次间累计偿还的利息金额。

CUMPRINC:累计本金函数

CUMPPRINC函数基于固定利率及等额分期付款方式，返回开始期次到结束期次间累计偿还的本金金额。

NPER:期次函数,基于固定利率及等额分期付款方式，返回偿还全部贷款的期次。 RATE:利率函数,返回年金的利率

投资函数

FV函数:固定利率的未来值

FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。 FVSCHEDULE函数:非固定利率的未来值

FVSCHEDULE函数计算基于非固定利率投资的未来值PV :现值函数,求固定年金的现值 NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的现值 XNPV:净现值函数,计算一项投资净收益的现值. IRR:定期现金流量的内部报酬率 XIRR:非定期现金流量的内部报酬率 MIRR:融资及转投资的内部报酬率

CORREL:相关性函数,返回两组数据的相关系数

必须掌握的操作

1.规划求解的数学模型与操作方法 2.模拟运算表的操作方法 3.单变量求解的操作方法

4.使用数据分析工具进行相关性分析 5.使用图表趋势线的方法进行回归分析 6.使用数据分析工具进行回归分析

7.使用财务函数进行投资分析(现值函数、未来值函数、投资回报率函数等）

问答

1.Excel在财务上有哪些应用

参考答案：可以制作财务表格，记账凭证，总账，报表，明细账，进行数据分析、筛选和统计。

2.Excel的年金函数有哪些（名称和功能 ）？答案见上面 3.Excel的现值函数有哪些（名称和功能 ）？

答案：PV函数用来计算某项固定年金的现值。年金现值就是未来各期年金现在的价值的总和。NPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的现值。 4.Excel的未来值函数有哪些（名称和功能 ）？

答案：FV函数:固定利率的未来值。FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。FVSCHEDULE函数：计算基于非固定利率投资的未来值。 5.Excel的报酬率函数有哪些（名称和功能 ）

答案：IRR:定期现金流量的内部报酬率。返回由数值代表的一组现金流的内部收益率。 XIRR:非定期现金流量的内部报酬率。返回一组现金流的内部收益率。

MIRR:融资及转投资的内部报酬率。返回某一连续期间内现金流的修正内部收益率。

7.Excel中用于固定资产折旧的函数有哪几个?各有什么特点?

答案：SLN该函数使用平均法，每期提相同的折旧额，返回一项资产每期的折旧费。 平均法折旧的缺点：各个使用周期所负担的使用成本前后不均匀。只考虑使用年限，并没有考虑实际加班或减班的情况。生产数量法：生产数量法的缺点：只单纯以产量为依据，没考虑到有形或无形的损耗。总产量通常无法估计很准确。工作时间法：工作时间法的缺点：只单纯以工作时间为依据，没考虑到有形或无形的损耗。总工作时间通常无法估计很准确。年数合计法：也叫加速折旧法，使前几期享受较大的服务潜力，而负担较高的成本。也可使固定资产的帐面价值较接近于市价；前期多提折旧，可以降低企业早期应税金额，将应税时间往后递延，可以获得货币的时间价值。固定余额递减法：依据固定资产的估计使用年数，按公式求出其折旧率，每年以固定资产的帐面价值，乘以折旧率来计算其当年的折旧额。

8.Excel在数据分析上有哪些应用?

答：有排序，高级排序，刷选，制图表，统计，筛选，排序，回归分析，规划求解。 9.EXCEL中有哪些数据保护与隐藏方法? 1.数据保护

(1)工作簿保护:工具-保护-保护工作簿。(2)工作表保护:保护工作表中的数据不被修改。 工具-保护-保护工作表。

2.数据隐藏

(1)工作簿隐藏:窗口-隐藏；

取消隐藏：窗口-取消隐藏

(2)工作表隐藏:选定工作表，格式-工作表-隐藏

取消隐藏：格式-工作表-取消隐藏，选择工作表 (3)行或列隐藏:选定行或列，格式-行/列-隐藏

取消隐藏：选择区域,格式-行/列-取消隐藏， (4)单元格隐藏:

选定单元格，格式-单元格-数字-自定义类型;;;(在单元格中不可见）; 格式-单元格-保护-隐藏;

格式-保护-工作表（在编辑框中不可见）.

取消隐藏：撤消对工作表的保护，恢复数值类型 (5)区域隐藏:选定区域，隐藏方法与单元格隐藏方法一样 取消隐藏：选择区域,方法与单元格取消隐藏方法一样 (6)公式隐藏:

选定单元格或区域，格式-单元格-保护-隐藏

工具-保护-工作表