EXCEL信息技术计算工具教程

第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组合图表1.2.3趋势线1.2.4数据透视表21.1.1数据的导入在Excel环境下进行计算，所需要的数据大致有3种来源：手工输入、自动生成和从外部导入。当数据量很小而且又没有规律的情况下，一般采用手工输入的方法；而对于那些有规律的数据，如连续的数字或字符序列可以用Excel的填充命令自动生成。当数据量比较大或有现成的数据来源如网络资源、数据库资源等可供应用，就需要采用导入的方法。从Excel菜单上的“文件”→“打开”命令处，就可以直接读入数据库文件、Web文件、XML文件、文本文件以及其他电子表格式的文件等。从外部导入数据的另一种方法是建立数据查询。根据数据源的不同，查询可以分成“Web查询”和“数据库查询”两大类。31.1.1数据的导入从Web上导入少量数据的简单方法：第一种方法是在浏览器中右击所需要的数据，从快捷菜单中执行“复制”(快捷键[Ctrl]+[C])选项，然后在Excel工作表中选中要放置数据的位置(可以选中一个或一组单元格)，从菜单或常用工具栏中执行“粘贴”命令(快捷键[Ctrl]+[V])，就可以把Web页上的数据拷贝到工作表中。第二种方法适合于导入Web页上的表格数据，在IE浏览器中打开数据所在页面，在所需要数据所在的表格处点击鼠标右键，从快捷菜单中执行“导出到MicrosoftExcel”，无论此时Excel是否打开，操作系统会自动在Excel中建立一个空白工作簿文件，将数据输入。使用这种方法导入数据，还会同时建立对源地址的查询链接，可以在需要时，执行刷新命令以更新数据。41.1.1数据的导入从Web上导入大规模数据，更规范的做法是创建Web查询。从Excel菜单上执行“数据”→“导入Web数据”→“新建Web查询”，打开创建查询对话框。51.1.2数据运算与引用Excel中的运算是通过公式或函数实现的。Excel中的公式。它是由等号打头，包括常量、对单元格或单元格区域的引用、运算符和函数等几种成分。例如在下面一行中，=60+AVERAGE(B2:B8)/SIGMA等号表示所在单元格内容是一个公式，等式后面的“60”是常量，“+”和“/”是运算符，AVERAGE()是求平均值的函数，SIGMA是用户自己给特定单元格的命名。当需要同时对一组数据进行处理或公式、函数的计算结果不只一个数据时，就需要使用数组公式或数组函数。在Excel中用大括号“{}”表示数组，如“{2,2,4;3,6,8;…}”数组公式和数组函数的使用方法与普通公式基本相同，但在输入时要首先选中返回数组结果的全部单元格，并使用[Shift]+[Ctrl]+[Enter]进行输入

61.1.2数据运算与引用运算类型运算符示例说明算术运算+–*/%^10000.00*(1+1.1%)^2年息1.1，本金10000元，按复利计算两年的本息合计10221.21。文本运算&“ProductA”&“Actual”将两个文本连接，得到“ProductAActual”。日期运算–“2003-5-28”–“2001-2-10”“17:30”–“09:15”求出两个日期之间的天数837；求出两个时间之间的间隔8:15。比较运算<><=>=<>A1>A2,C2<>C3判断两个数据之间的比较关系，返回逻辑值，Excel中的运算类型71.1.2数据运算与引用Excel中的运算函数函数类别示例逻辑函数IF(判断),NOT(非),OR(或),AND(与),TRUE(真),FALSE(假)数学函数LOG(对数),POWER(乘幂),SQRT(开平方),COS(余弦),FACT(阶乘),……统计函数VAR(方差),MEDIAN(中位数),NORMSDIST(标准正态分布),…工程函数BESSELJ(贝塞尔函数),HEX2BIN(16进制到2进制),IMAGINARY(复数的虚部系数),……金融函数NPV(净现值),FV(净现值),INTRATE(利率),YIELD(收益),……81.1.2数据运算与引用Excel中引用单元格的方法分为：A1引用和R1C1引用。前者是用字母数字组合标识引用单元格的绝对位置；后者表示对当前选中单元格相对位置的引用。当需要引用位于其他工作表或其他文件中的数据时，就要使用三维引用。三维引用的基本格式是：[spreadsheet]sheet!A1其中，前面的方括号“[]”内是要引用的文件名，如果源数据在同一文件内则可省略；跟在文件名后面的是工作表的名称，如果同时引用多个工作表相同位置的数据，则可以使用冒号“:”标识工作表范围，如Sheet1:Sheet3，注意工作表名后面要跟有叹号“!”；最后是引用的单元格或单元格区域的名称，如A1、B2或C3:D6等等。91.1.2数据运算与引用相对引用：公式中的相对单元格引用(例如A1)是基于单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，公式使用相对引用。绝对引用：单元格中的绝对单元格引用(格式为$A$1，即在行列标志前加“$”符号)总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。混合引用：混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式，绝对引用行采用A$1、B$1等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。10数数据运算算与引用用Excel中的绝对对引用、、相对引引用和混混合引用用11排序与筛筛选在Excel中对数据据进行整整理最常常见的方方式是排排序和筛筛选。排序是按按照某一一个或某某几个特特定字段段重新排排列数据据的顺序序，筛选选是从全全体数据据中选择择符合某某种条件件的数据据。Excel提供了两两种对数数据进行行排序的的方法：：一种方法法是选中中数据表表中作为为排序依依据所在在列中的的单元格格，然后后点击常常用工具具栏上的的排序按按钮如果需要要先后按按照若干干列来排排序数据据表，则则需要从从菜单上上执行““数据””→“排序”命命令，打打开排序序对话框框。例如，从从Web下载了某某股票期期权的报报价后，，需要按按照期权权期限、、执行价价格和交交易场所所对报价价进行排排序，则则对数据据进行初初步整理理后，即即可按如如下操作作。12排序与筛筛选Excel中的排序序13排排序与筛筛选在Excel环境下筛筛选数据据有简单单和高级级两种方方法。执行菜单单上的““数据””→“筛选”→“自动筛选选”，工工作表中中每列的的标题行行上就会会出现筛筛选按钮钮“▼”，点击要要作为筛筛选依据据的那一一列的筛筛选按钮钮，从中中选择要要显示项项目的条条件，其其他不符符合该条条件的行行就会被被隐藏起起来。可可以同时时在一个个数据表表上的多多个列上上进行筛筛选。14排排序与筛选选当需要设定更更复杂的筛选选条件，可以以使用高级筛筛选。“高级筛选””命令像“自自动筛选”命命令一样筛选选清单，但不不显示列的下下拉列表，而而是在工作表表上单独的条条件区域中键键入筛选条件件，它允许根根据更复杂的的条件进行筛筛选。并且，，在筛选完成成后，可以选选择把筛选结结果显示在原原来位置或复复制到其他区区域。151.2图表表和数据透视视表1.3内置函数和自自定义函数函数的使用统计函数金融函数自定义函数1.4假设分析工具具方案管理器模拟运算表单变量求解规划求解1.1数据输入与运运算数据的导入数据运算与引引用排序与筛选1.2图表和数据透透视表图表类型组合图表趋势线数据透视表16图图表类型类别类型特征第一类柱形图，折线图，散点图这类图表主要用来反映数据的变化趋势及对比。其中散点图与柱型、折线图的区别在于其横轴按数值表示，是连续的；而前者的横轴按类别表示，是离散的。条形图、圆柱图、圆锥图、棱锥图都是柱形图的变种第二类曲面图曲面图是一种真三维图表(三维柱形、圆柱、圆锥、棱锥等类型，当数据轴上只有一组数据时，本质上只是二维图)，它适合对于分析多组数据的对比与变化趋势。第三类饼图，圆环图，雷达图这三种图的基本面都是圆形的，主要用来观察数据之间的比例。第四类面积图面积图与折线图类似，但它具有堆积面积图和百分比堆积面积图两种变种，因此可以更好地反映某一(组)数据在全部数据(组)中所占比例。第五类气泡图，股价图气泡图可以看作是散点图的扩展，它用气泡大小反映数据点的另一组属性。股价图顾名思义是反映类似股市行情的图表，它在每一个数据点上可以包括：开盘价、收盘价、最高价、最低价、成交量。Excel中的主要图表表类型17组组合图表在用图表表示示多组数据之之间的对比关关系时，有两两个常用的技技巧：组合图图表和次坐标标轴。所谓组合图表表是指在同一一幅图表中使使用不同的图图表类型表示示不同组别的的数据。一个个常见的例子子是带有交易易量的股价图图。Excel允许在同一图图表中对两组组具有不同数数量级的数据据使用不同的的坐标轴，在在原有的数据据轴之外，用用不同单位或或刻度使用次次坐标轴表示示另外一组数数据。18趋趋势线Excel图表中的另一一项重要功能能是绘制趋势势线。这一功功能是基于统统计学中的回回归和预测理理论。19数数据透视表表分类汇总20数数据透视表表Excel的数据透视表表将不同类别别的数据组分分成不同的页页，就好像是是将多个二维维平面工作表表层叠起来，，从而形成了了三维的数据据表示结构，，使用户可以以通过二维表表透视到三维维的数据211.3内置置函数和自定定义函数1.3内置函数和自自定义函数函数的使用统计函数金融函数自定义函数1.4假设分析工具具方案管理器模拟运算表单变量求解规划求解1.1数据输入与运运算数据的导入数据运算与引引用排序与筛选1.2图表和数据透透视表图表类型组合图表趋势线数据透视表22函函数的使用用Excel内置有大约330个的函数，这这些函数可以以分成11大类：数据库库函数，日期期和时间函数数，外部函数数，工程函数数，财务函数数，信息函数数，逻辑函数数，查找和引引用函数，数数学和三角函函数，统计函函数以及文本本和数据函数数。如果要在某单单元格或单元元格区域内调调用函数：从菜单中选取取“插入→函数……”命令；点击编辑区左左侧的“fx”标志；点击工具栏上上的自动求和和标志∑；按[Shift]+[F3]。在某些情况下下，可能需要要将某函数作作为另一函数数的参数使用用，这称为嵌嵌套函数。公公式可包含多多达七级的嵌嵌套函数。许多Excel函数通过对一一组或多组数数据进行运算算，得出一组组运算结果。。这类函数实实际上是数组组公式，必须须按数组的方方式输入(选中整个数组组所在区域，，按[Shift]+[Ctrl]+[Enter]输入)23统计函数Excel中有70余个统计函数数，大致可分分为6类：基本统计计类函数，概概率分布类，，数字特征类类，抽样分布布类，统计检检验类，相关关、回归与预预测类。金融计算中常常用的统计函函数：计算平平均值的AVERAGE()、计算方差的的VAR()或VARP()、计算标准差差的STDEV()或STDEVP()、标准正态分分布NORMSDIST()和NORMSINV()；财务预测中有有时会用到线线性回归与预预测函数LINEST()、FORECAST()、TREND()、线性方程的的斜率函数SLOPE()、指数回归与与预测函数LOGEST()、GROWTH()；在进行数据分分析时会用到到相关系数函函数CORREL()和乘积矩相关关系数(也称为广义相相关系数)函数RSQ()。24金金融函数Excel内置有50余个金融财务务方面的函数数。第2章介绍计算货货币时间价值值的9个函数，其中中5个通用函数在在金融计算中中有着非常重重要的作用：：现值PV()、终值FV()、利率RATE()、每期现金流流量PMT()、期数NPER()。第3章介绍5个固定资产折折旧类函数：：直线折旧法法SLN()、年限总和法法SYD()、固定余额递递减法DB()、双倍余额递递减法DDB()以及用余额递递减法计算若若干期内累积积折旧额的VDB()。第4章介绍5个投资评价类类函数：净现现值NPV()、内部收益率率IRR()、修正内部收收益率MIRR()以及用于计算算非定期现金金流量净现值值和内部收益益率的函数XNPV()和XIRR()。第7章介绍关于债债券计算的26个函数。25自自定义函数数自定义函数是是指用户根据据自己的需要要，通过Excel内置的VisualBasicforApplication编写代码，设设计出实现特特定功能的函函数。在本书各章的的Excel工作簿文件里里，为了说明明一些重要或或复杂的计算算过程，使用用了一个自定定义函数dispformula()，它的功能是是显示指定单单元格或单元元格区域内的的公式。在第第10章中，设计了了两个计算期期权价格的函函数callopt()和putopt()，这些函数要要通过自定义义的方式置入入Excel。261.4假设设分析工具1.3内置函数和自自定义函数函数的使用统计函数金融函数自定义函数1.4假设分析工具具方案管理器模拟运算表单变量求解规划求解1.1数据输入与运运算数据的导入数据运算与引引用排序与筛选1.2图表和数据透透视表图表类型组合图表趋势线数据透视表27方方案管理器器方案，是保存存在工作表当当中并可以在在相互之间进进行切换的数数据。例如，对于一一组现金流量量分别使用不不同的贴现率率计算净现值值，就可以把把不同条件下下的结果保存存在若干方案案中，通过Excel的方案管理器器在不同的结结果间进行切切换，以对比比和评价各方方案之间的差差异。28模模拟运算表表敏感性分析是是指通过使用用不同的参数数，对某个或或某一组公式式的运算结果果进行综合对对比，从而了了解不同参数数在其可能的的取值范围内内，对运算结结果的影响程程度。Excel中的模拟运算算表是一个非非常理想的敏敏感性分析工工具，它充分分发挥了表格格界面和公式式运算的两个个优势，通过过对评价指标标计算公式的的引用和指定定要分析的指指标，用表格格的形式表示示出评价指标标对分析指标标的依赖程度度。如果根据据模拟运算表表的结果作图图，可以更直直观地表达出出分析结果。。根据参数的不不同，模拟运运算表有单变变量和双变量量之分。在使使用单变量模模拟运算表时时，可以将引引用同一个参参数的多个公公式并列。