搜索
第1章计算工具EXCEL——更有效地使用电子表软件本章总结了Excel中一些很重要但又容易为人们所忽视的使用技巧和运算工具，特别是强调了本书后续内容里经常用到的一些方法。Excel金融计算专业教程-2-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数据透视表-3-1.1.1数据的导入在Excel环境下进行计算，所需要的数据大致有3种来源：手工输入、自动生成和从外部导入。当数据量很小而且又没有规律的情况下，一般采用手工输入的方法；而对于那些有规律的数据，如连续的数字或字符序列可以用Excel的填充命令自动生成。当数据量比较大或有现成的数据来源如网络资源、数据库资源等可供应用，就需要采用导入的方法。从Excel菜单上的“文件”→“打开”命令处，就可以直接读入数据库文件、Web文件、XML文件、文本文件以及其他电子表格式的文件等。从外部导入数据的另一种方法是建立数据查询。根据数据源的不同，查询可以分成“Web查询”和“数据库查询”两大类。-4-1.1.1数据的导入从Web上导入少量数据的简单方法：第一种方法是在浏览器中右击所需要的数据，从快捷菜单中执行“复制”(快捷键[Ctrl]+[C])选项，然后在Excel工作表中选中要放置数据的位置(可以选中一个或一组单元格)，从菜单或常用工具栏中执行“粘贴”命令(快捷键[Ctrl]+[V])，就可以把Web页上的数据拷贝到工作表中。第二种方法适合于导入Web页上的表格数据，在IE浏览器中打开数据所在页面，在所需要数据所在的表格处点击鼠标右键，从快捷菜单中执行“导出到MicrosoftExcel”，无论此时Excel是否打开，操作系统会自动在Excel中建立一个空白工作簿文件，将数据输入。使用这种方法导入数据，还会同时建立对源地址的查询链接，可以在需要时，执行刷新命令以更新数据。-5-1.1.1数据的导入从Web上导入大规模数据，更规范的做法是创建Web查询。从Excel菜单上执行“数据”→“导入Web数据”→“新建Web查询”，打开创建查询对话框。-6-1.1.2数据运算与引用Excel中的运算是通过公式或函数实现的。Excel中的公式。它是由等号打头，包括常量、对单元格或单元格区域的引用、运算符和函数等几种成分。例如在下面一行中，=60+AVERAGE(B2:B8)/SIGMA等号表示所在单元格内容是一个公式，等式后面的“60”是常量，“+”和“/”是运算符，AVERAGE()是求平均值的函数，SIGMA是用户自己给特定单元格的命名。当需要同时对一组数据进行处理或公式、函数的计算结果不只一个数据时，就需要使用数组公式或数组函数。在Excel中用大括号“{}”表示数组，如“{2,2,4;3,6,8;…}”数组公式和数组函数的使用方法与普通公式基本相同，但在输入时要首先选中返回数组结果的全部单元格，并使用[Shift]+[Ctrl]+[Enter]进行输入-7-1.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。比较运算==A1A2,C2C3判断两个数据之间的比较关系，返回逻辑值，Excel中的运算类型-8-1.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(收益),……-9-1.1.2数据运算与引用Excel中引用单元格的方法分为：A1引用和R1C1引用。前者是用字母数字组合标识引用单元格的绝对位置；后者表示对当前选中单元格相对位置的引用。当需要引用位于其他工作表或其他文件中的数据时，就要使用三维引用。三维引用的基本格式是：[spreadsheet]sheet!A1其中，前面的方括号“[]”内是要引用的文件名，如果源数据在同一文件内则可省略；跟在文件名后面的是工作表的名称，如果同时引用多个工作表相同位置的数据，则可以使用冒号“:”标识工作表范围，如Sheet1:Sheet3，注意工作表名后面要跟有叹号“!”；最后是引用的单元格或单元格区域的名称，如A1、B2或C3:D6等等。-10-1.1.2数据运算与引用相对引用：公式中的相对单元格引用(例如A1)是基于单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，公式使用相对引用。绝对引用：单元格中的绝对单元格引用(格式为$A$1，即在行列标志前加“$”符号)总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。混合引用：混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式，绝对引用行采用A$1、B$1等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。-11-1.1.2数据运算与引用Excel中的绝对引用、相对引用和混合引用-12-1.1.3排序与筛选在Excel中对数据进行整理最常见的方式是排序和筛选。排序是按照某一个或某几个特定字段重新排列数据的顺序，筛选是从全体数据中选择符合某种条件的数据。Excel提供了两种对数据进行排序的方法：一种方法是选中数据表中作为排序依据所在列中的单元格，然后点击常用工具栏上的排序按钮如果需要先后按照若干列来排序数据表，则需要从菜单上执行“数据”→“排序”命令，打开排序对话框。例如，从Web下载了某股票期权的报价后，需要按照期权期限、执行价格和交易场所对报价进行排序，则对数据进行初步整理后，即可按如下操作。-13-1.1.3排序与筛选Excel中的排序-14-1.1.3排序与筛选在Excel环境下筛选数据有简单和高级两种方法。执行菜单上的“数据”→“筛选”→“自动筛选”，工作表中每列的标题行上就会出现筛选按钮“▼”，点击要作为筛选依据的那一列的筛选按钮，从中选择要显示项目的条件，其他不符合该条件的行就会被隐藏起来。可以同时在一个数据表上的多个列上进行筛选。-15-1.1.3排序与筛选当需要设定更复杂的筛选条件，可以使用高级筛选。“高级筛选”命令像“自动筛选”命令一样筛选清单，但不显示列的下拉列表，而是在工作表上单独的条件区域中键入筛选条件，它允许根据更复杂的条件进行筛选。并且，在筛选完成后，可以选择把筛选结果显示在原来位置或复制到其他区域。-16-1.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.3排序与筛选•1.2图表和数据透视表–1.2.1图表类型–1.2.2组合图表–1.2.3趋势线–1.2.4数据透视表-17-1.2.1图表类型类别类型特征第一类柱形图，折线图，散点图这类图表主要用来反映数据的变化趋势及对比。其中散点图与柱型、折线图的区别在于其横轴按数值表示，是连续的；而前者的横轴按类别表示，是离散的。条形图、圆柱图、圆锥图、棱锥图都是柱形图的变种第二类曲面图曲面图是一种真三维图表(三维柱形、圆柱、圆锥、棱锥等类型，当数据轴上只有一组数据时，本质上只是二维图)，它适合对于分析多组数据的对比与变化趋势。第三类饼图，圆环图，雷达图这三种图的基本面都是圆形的，主要用来观察数据之间的比例。第四类面积图面积图与折线图类似，但它具有堆积面积图和百分比堆积面积图两种变种，因此可以更好地反映某一(组)数据在全部数据(组)中所占比例。第五类气泡图，股价图气泡图可以看作是散点图的扩展，它用气泡大小反映数据点的另一组属性。股价图顾名思义是反映类似股市行情的图表，它在每一个数据点上可以包括：开盘价、收盘价、最高价、最低价、成交量。Excel中的主要图表类型-18-1.2.2组合图表在用图表表示多组数据之间的对比关系时，有两个常用的技巧：组合图表和次坐标轴。所谓组合图表是指在同一幅图表中使用不同的图表类型表示不同组别的数据。一个常见的例子是带有交易量的股价图。Excel允许在同一图表中对两组具有不同数量级的数据使用不同的坐标轴，在原有的数据轴之外，用不同单位或刻度使用次坐标轴表示另外一组数据。-19-1.2.3趋势线Excel图表中的另一项重要功能是绘制趋势线。这一功能是基于统计学中的回归和预测理论。-20-1.2.4数据透视表分类汇总-21-1.2.4数据透视表Excel的数据透视表将不同类别的数据组分成不同的页，就好像是将多个二维平面工作表层叠起来，从而形成了三维的数据表示结构，使用户可以通过二维表透视到三维的数据-22-1.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数据透视表-23-1.3.1函数的使用Excel内置有大约330个的函数，这些函数可以分成11大类：数据库函数，日期和时间函数，外部函数，工程函数，财务函数，信息函数，逻辑函数，查找和引用函数，数学和三角函数，统计函数以及文本和数据函数。如果要在某单元格或单元格区域内调用函数：–从菜单中选取“插入→函数……”命令；–点击编辑区左侧的“fx”标志；–点击工具栏上的自动求和标志∑；–按[Shift]+[F3]。在某些情况下，可能需要将某函数作为另一函数的参数使用，这称为嵌套函数。公式可包含多达七级的嵌套函数。许多Excel函数通过对一组或多组数据进行运算，得出一组运算结果。这类函数实际上是数组公式，必须按数组的方式输入(选中整个数组所在区域，按[Shift]+[Ctrl]+[Enter]输入)-24-1.3.2统计函数Excel中有70余个统计函数，大致可分为6类：基本统计类函数，概率分布类，数字特征类，抽样分布类，统计检验类，相关、回归与预测类。金融计算中常用的统计函数：计算平均值的AVERAGE()、计算方差的VAR()或VARP()、计算标准差的STDEV()或STDEVP()、标准正态分布NORMSDIST()和NORMSINV()；财务预测中有时会用到线性回归与预测函数LINEST()、FORECAST()、TREND()、线性方程