EXCEL信息技术计算工具教程

1、第1章 计算工具EXCEL更有效地使用电子表软件本章总结了Excel中一些很重要但又容易为人们所忽视的使用技巧和运算工具，特别是强调了本书后续内容里经常用到的一些方法。Excel 金融计算专业教程1.1数数据输输入与运运算1.3内置函数数和自定定义函数数1.3.1函数的使使用1.3.2统计函数数1.3.3金融函数数1.3.4自定义函函数1.4假设分析析工具1.4.1方案管理理器1.4.2模拟运算算表1.4.3单变量求求解1.4.4规划求解解1.1数据输入入与运算算1.1.1数据的导导入1.1.2数据运算算与引用用1.1.3排序与筛筛选1.2图表和数数据透视视表1.2.1图表类型型1.2.2组合

2、图表表1.2.3趋势线1.2.4数据透视视表21.1.1数数据的导导入在Excel环境下进进行计算算，所需需要的数数据大致致有3种来源：手工输输入、自自动生成成和从外外部导入入。当数数据量很很小而且且又没有有规律的的情况下下，一般般采用手手工输入入的方法法；而对对于那些些有规律律的数据据，如连连续的数数字或字字符序列列可以用用Excel的填充命命令自动动生成。当数据据量比较较大或有有现成的的数据来来源如网网络资源源、数据据库资源源等可供供应用，就需要要采用导导入的方方法。从Excel菜单上的的“文件件”“打开”命命令处，就可以以直接读读入数据据库文件件、Web文件、XML文件、文文本文件件以及

3、其其他电子子表格式式的文件件等。从外部导导入数据据的另一一种方法法是建立立数据查查询。根根据数据据源的不不同，查查询可以以分成“Web查询”和和“数据据库查询询”两大大类。31.1.1数数据的导导入从Web上导入少少量数据据的简单单方法：第一种方方法是在在浏览器器中右击击所需要要的数据据，从快快捷菜单单中执行行“复制制”(快捷键Ctrl+C)选项，然然后在Excel工作表中中选中要要放置数数据的位位置(可以选中中一个或或一组单单元格)，从菜单单或常用用工具栏栏中执行行“粘贴贴”命令令(快捷键Ctrl+V)，就可以以把Web页上的数数据拷贝贝到工作作表中。第二种方方法适合合于导入入Web页上的表

4、表格数据据，在IE浏览器中中打开数数据所在在页面，在所需需要数据据所在的的表格处处点击鼠鼠标右键键，从快快捷菜单单中执行行“导出出到MicrosoftExcel”，无论此此时Excel是否打开开，操作作系统会会自动在在Excel中建立一一个空白白工作簿簿文件，将数据据输入。使用这这种方法法导入数数据，还还会同时时建立对对源地址址的查询询链接，可以在在需要时时，执行行刷新命命令以更更新数据据。41.1.1数数据的导导入从Web上导入大大规模数数据，更更规范的的做法是是创建Web查询。从从Excel菜单上执执行“数数据”“导入Web数据”“新建Web查询”，打开创创建查询询对话框框。51.1.2数

5、数据运算算与引用用Excel中的运算算是通过过公式或或函数实实现的。Excel中的公式式。它是是由等号号打头，包括常常量、对对单元格格或单元元格区域域的引用用、运算算符和函函数等几几种成分分。例如如在下面面一行中中，= 60 +AVERAGE(B2:B8)/SIGMA等号表示示所在单单元格内内容是一一个公式式，等式式后面的的“60”是常量，“+”和“/”是运算符符，AVERAGE()是求平均均值的函函数，SIGMA是用户自自己给特特定单元元格的命命名。当需要同同时对一一组数据据进行处处理或公公式、函函数的计计算结果果不只一一个数据据时，就就需要使使用数组组公式或或数组函函数。在在Excel中用

6、大括括号“”表示数组组，如“2,2,4; 3,6,8;”数组公式式和数组组函数的的使用方方法与普普通公式式基本相相同，但但在输入入时要首首先选中中返回数数组结果果的全部部单元格格，并使使用Shift+Ctrl+Enter进行输入入61.1.2数数据运算算与引用用运算类型运算符示例说明算术运算+ * / % 10000.00*(1+1.1%)2年息1.1，本金10000元，按复利计算两年的本息合计10221.21。文本运算&“Product A” & “Actual”将两个文本连接，得到“Product A Actual”。日期运算“2003-5-28” “2001-2-10”“17:30”“0

7、9:15”求出两个日期之间的天数837；求出两个时间之间的间隔8:15。比较运算 = A1A2, C2C3判断两个数据之间的比较关系，返回逻辑值，Excel中的运算算类型71.1.2数数据运算算与引用用Excel中的运算算函数函数类别示例逻辑函数IF(判断), NOT(非), OR(或), AND(与), TRUE(真), FALSE(假)数学函数LOG(对数), POWER(乘幂), SQRT(开平方), COS(余弦), FACT(阶乘), 统计函数VAR(方差), MEDIAN(中位数), NORMSDIST(标准正态分布), 工程函数BESSELJ(贝塞尔函数), HEX2BIN(16

8、进制到2进制), IMAGINARY(复数的虚部系数), 金融函数NPV(净现值), FV(净现值), INTRATE(利率), YIELD(收益), 81.1.2数数据运算算与引用用Excel中引用单单元格的的方法分分为：A1引用和R1C1引用。前前者是用用字母数数字组合合标识引引用单元元格的绝绝对位置置；后者者表示对对当前选选中单元元格相对对位置的的引用。当需要引引用位于于其他工工作表或或其他文文件中的的数据时时，就要要使用三三维引用用。三维维引用的的基本格格式是：spreadsheetsheet!A1其中，前前面的方方括号“ ”内是要引引用的文文件名，如果源源数据在在同一文文件内则则可省

9、略略；跟在在文件名名后面的的是工作作表的名名称，如如果同时时引用多多个工作作表相同同位置的的数据，则可以以使用冒冒号“:”标识工作作表范围围，如Sheet1:Sheet3，注意工工作表名名后面要要跟有叹叹号“!”；最后是是引用的的单元格格或单元元格区域域的名称称，如A1、B2或C3:D6等等。91.1.2数数据运算算与引用用相对引用用：公式式中的相相对单元元格引用用(例如A1)是基于单单元格的的相对位位置。如如果公式式所在单单元格的的位置改改变，引引用也随随之改变变。如果果多行或或多列地地复制公公式，引引用会自自动调整整。默认认情况下下，公式式使用相相对引用用。绝对引用用：单元元格中的的绝对单

10、单元格引引用(格式为$A$1，即在行行列标志志前加“$”符号)总是在指指定位置置引用单单元格。如果公公式所在在单元格格的位置置改变，绝对引引用保持持不变。如果多多行或多多列地复复制公式式，绝对对引用将将不作调调整。混合引用用：混合合引用具具有绝对对列和相相对行，或是绝绝对行和和相对列列。绝对对引用列列采用$A1、$B1等形式，绝对引引用行采采用A$1、B$1等形式。如果公公式所在在单元格格的位置置改变，则相对对引用改改变，而而绝对引引用不变变。如果果多行或或多列地地复制公公式，相相对引用用自动调调整，而而绝对引引用不作作调整。101.1.2数数据运算算与引用用Excel中的绝对对引用、相对引引

11、用和混混合引用用111.1.3排序与筛筛选在Excel中对数据据进行整整理最常常见的方方式是排排序和筛筛选。排序是按按照某一一个或某某几个特特定字段段重新排排列数据据的顺序序，筛选选是从全全体数据据中选择择符合某某种条件件的数据据。Excel提供了两两种对数数据进行行排序的的方法：一种方法法是选中中数据表表中作为为排序依依据所在在列中的的单元格格，然后后点击常常用工具具栏上的的排序按按钮如果需要要先后按按照若干干列来排排序数据据表，则则需要从从菜单上上执行“数据”“排序”命命令，打打开排序序对话框框。例如，从从Web下载了某某股票期期权的报报价后，需要按按照期权权期限、执行价价格和交交易场所所

12、对报价价进行排排序，则则对数据据进行初初步整理理后，即即可按如如下操作作。121.1.3排序与筛筛选Excel中的排序序131.1.3排排序与筛筛选在Excel环境下筛筛选数据据有简单单和高级级两种方方法。执行菜单单上的“数据”“筛选”“自动筛选选”，工工作表中中每列的的标题行行上就会会出现筛筛选按钮钮“”，点击要要作为筛筛选依据据的那一一列的筛筛选按钮钮，从中中选择要要显示项项目的条条件，其其他不符符合该条条件的行行就会被被隐藏起起来。可可以同时时在一个个数据表表上的多多个列上上进行筛筛选。141.1.3排排序与筛筛选当需要设设定更复复杂的筛筛选条件件，可以以使用高高级筛选选。“高级筛筛选”

13、命命令像“自动筛筛选”命命令一样样筛选清清单，但但不显示示列的下下拉列表表，而是是在工作作表上单单独的条条件区域域中键入入筛选条条件，它它允许根根据更复复杂的条条件进行行筛选。并且，在筛选选完成后后，可以以选择把把筛选结结果显示示在原来来位置或或复制到到其他区区域。151.2图图表和和数据透透视表1.3内置函数数和自定定义函数数1.3.1函数的使使用1.3.2统计函数数1.3.3金融函数数1.3.4自定义函函数1.4假设分析析工具1.4.1方案管理理器1.4.2模拟运算算表1.4.3单变量求求解1.4.4规划求解解1.1数据输入入与运算算1.1.1数据的导导入1.1.2数据运算算与引用用1.1

14、.3排序与筛筛选1.2图表和数数据透视视表1.2.1图表类型型1.2.2组合图表表1.2.3趋势线1.2.4数据透视视表161.2.1图图表类型型类别类型特征第一类柱形图，折线图，散点图这类图表主要用来反映数据的变化趋势及对比。其中散点图与柱型、折线图的区别在于其横轴按数值表示，是连续的；而前者的横轴按类别表示，是离散的。条形图、圆柱图、圆锥图、棱锥图都是柱形图的变种第二类曲面图曲面图是一种真三维图表(三维柱形、圆柱、圆锥、棱锥等类型，当数据轴上只有一组数据时，本质上只是二维图)，它适合对于分析多组数据的对比与变化趋势。 第三类饼图，圆环图，雷达图这三种图的基本面都是圆形的，主要用来观察数据之

15、间的比例。第四类面积图面积图与折线图类似，但它具有堆积面积图和百分比堆积面积图两种变种，因此可以更好地反映某一(组)数据在全部数据(组)中所占比例。第五类气泡图，股价图气泡图可以看作是散点图的扩展，它用气泡大小反映数据点的另一组属性。股价图顾名思义是反映类似股市行情的图表，它在每一个数据点上可以包括：开盘价、收盘价、最高价、最低价、成交量。Excel中的主要要图表类类型171.2.2组组合图表表在用图表表表示多多组数据据之间的的对比关关系时，有两个个常用的的技巧：组合图图表和次次坐标轴轴。所谓组合合图表是是指在同同一幅图图表中使使用不同同的图表表类型表表示不同同组别的的数据。一个常常见的例例子

16、是带带有交易易量的股股价图。Excel允许在同同一图表表中对两两组具有有不同数数量级的的数据使使用不同同的坐标标轴，在在原有的的数据轴轴之外，用不同同单位或或刻度使使用次坐坐标轴表表示另外外一组数数据。181.2.3趋趋势线Excel图表中的的另一项项重要功功能是绘绘制趋势势线。这这一功能能是基于于统计学学中的回回归和预预测理论论。191.2.4数数据透视视表分类汇总总201.2.4数数据透视视表Excel的数据透透视表将将不同类类别的数数据组分分成不同同的页，就好像像是将多多个二维维平面工工作表层层叠起来来，从而而形成了了三维的的数据表表示结构构，使用用户可以以通过二二维表透透视到三三维的数

17、数据211.3内内置函函数和自自定义函函数1.3内置函数数和自定定义函数数1.3.1函数的使使用1.3.2统计函数数1.3.3金融函数数1.3.4自定义函函数1.4假设分析析工具1.4.1方案管理理器1.4.2模拟运算算表1.4.3单变量求求解1.4.4规划求解解1.1数据输入入与运算算1.1.1数据的导导入1.1.2数据运算算与引用用1.1.3排序与筛筛选1.2图表和数数据透视视表1.2.1图表类型型1.2.2组合图表表1.2.3趋势线1.2.4数据透视视表221.3.1函函数的使使用Excel内置有大大约330个的函数数，这些些函数可可以分成成11大类：数数据库函函数，日日期和时时间函数数

18、，外部部函数，工程函函数，财财务函数数，信息息函数，逻辑函函数，查查找和引引用函数数，数学学和三角角函数，统计函函数以及及文本和和数据函函数。如果要在在某单元元格或单单元格区区域内调调用函数数：从菜单中中选取“插入函数”命令；点击编辑辑区左侧侧的“fx”标志；点击工具具栏上的的自动求求和标志志；按Shift+F3。在某些情情况下，可能需需要将某某函数作作为另一一函数的的参数使使用，这这称为嵌嵌套函数数。公式式可包含含多达七七级的嵌嵌套函数数。许多Excel函数通过过对一组组或多组组数据进进行运算算，得出出一组运运算结果果。这类类函数实实际上是是数组公公式，必必须按数数组的方方式输入入(选中整个

19、个数组所所在区域域，按Shift+Ctrl+Enter输入)231.3.2统计函数数Excel中有70余个统计计函数，大致可可分为6类：基本本统计类类函数，概率分分布类，数字特特征类，抽样分分布类，统计检检验类，相关、回归与与预测类类。金融计算算中常用用的统计计函数：计算平平均值的的AVERAGE()、计算方方差的VAR()或VARP()、计算标标准差的的STDEV()或STDEVP()、标准正正态分布布NORMSDIST()和NORMSINV()；财务预测测中有时时会用到到线性回回归与预预测函数数LINEST()、FORECAST()、TREND()、线性方方程的斜斜率函数数SLOPE()、

20、指数回回归与预预测函数数LOGEST()、GROWTH()；在进行数数据分析析时会用用到相关关系数函函数CORREL()和乘积矩矩相关系系数(也称为广广义相关关系数)函数RSQ()。241.3.3金金融函数数Excel内置有50余个金融融财务方方面的函函数。第2章介绍计计算货币币时间价价值的9个函数，其中5个通用函函数在金金融计算算中有着着非常重重要的作作用：现现值PV()、终值FV()、利率RATE()、每期现现金流量量PMT()、期数NPER()。第3章介绍5个固定资资产折旧旧类函数数：直线线折旧法法SLN()、年限总总和法SYD()、固定余余额递减减法DB()、双倍余余额递减减法DDB(

21、)以及用余余额递减减法计算算若干期期内累积积折旧额额的VDB()。第4章介绍5个投资评评价类函函数：净净现值NPV()、内部收收益率IRR()、修正内内部收益益率MIRR()以及用于于计算非非定期现现金流量量净现值值和内部部收益率率的函数数XNPV()和XIRR()。第7章介绍关关于债券券计算的的26个函数。251.3.4自自定义函函数自定义函函数是指指用户根根据自己己的需要要，通过过Excel内置的VisualBasicfor Application编写代码码，设计计出实现现特定功功能的函函数。在本书各各章的Excel工作簿文文件里，为了说说明一些些重要或或复杂的的计算过过程，使使用了一一个

22、自定定义函数数dispformula()，它的功功能是显显示指定定单元格格或单元元格区域域内的公公式。在在第10章中，设设计了两两个计算算期权价价格的函函数callopt()和putopt()，这些函函数要通通过自定定义的方方式置入入Excel。261.4假假设分分析工具具1.3内置函数数和自定定义函数数1.3.1函数的使使用1.3.2统计函数数1.3.3金融函数数1.3.4自定义函函数1.4假设分析析工具1.4.1方案管理理器1.4.2模拟运算算表1.4.3单变量求求解1.4.4规划求解解1.1数据输入入与运算算1.1.1数据的导导入1.1.2数据运算算与引用用1.1.3排序与筛筛选1.2图

23、表和数数据透视视表1.2.1图表类型型1.2.2组合图表表1.2.3趋势线1.2.4数据透视视表271.4.1方方案管理理器方案，是是保存在在工作表表当中并并可以在在相互之之间进行行切换的的数据。例如，对对于一组组现金流流量分别别使用不不同的贴贴现率计计算净现现值，就就可以把把不同条条件下的的结果保保存在若若干方案案中，通通过Excel的方案管管理器在在不同的的结果间间进行切切换，以以对比和和评价各各方案之之间的差差异。281.4.2模模拟运算算表敏感性分分析是指指通过使使用不同同的参数数，对某某个或某某一组公公式的运运算结果果进行综综合对比比，从而而了解不不同参数数在其可可能的取取值范围围内，对对运算结结果的影影响程度度。Excel中的模拟拟运算表表是一个个非常理理想的敏敏感性分分析工具具，它充充分发挥挥了表格格界面和和公式运运算的两两个优势势，通过过对评价价指标计计算公式式的引用用和指定定要分析析的指标标，用表表格的形形式表示示出评价价指标对对分析指指标的依依赖程度度。如果果根据模模拟运算算表的结结果作图图，可以以更直观观地表达达出分