Ecel培训教程Ecel公式与函数应用课件

《Ecel培训教程Ecel公式与函数应用课件》由会员分享，可在线阅读，更多相关《Ecel培训教程Ecel公式与函数应用课件（105页珍藏版）》请在装配图网上搜索。

1、Ecel培培训教程教程Ecel公公式与函数式与函数应用用1.对象模型概念对象模型概念 对象：即指可以被程序员操纵的软件元素对象：即指可以被程序员操纵的软件元素一、函数基础一、函数基础2.数据录入技巧3.公式基础l公式元素公式元素l在公式中使用运算符支持下列运算符：引用运算符：l计算模式计算模式自动（默认）模式：自动（默认）模式：人工模式：人工模式：l单元格和范围引用外部引用外部引用-不同工作簿中的单元格的引用称为外部引用。远程引用远程引用-引用其它程序中的数据称为远程引用。相对引用相对引用-在创建公式时，单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置。绝对引用绝对引用-如果在复

2、制公式时不希望Excel调整引用，那么请使用绝对引用。即加入美元符号，如$C$1。l实例：l=Sheet2!A1+1l=Budget.xlssheet1!A1+1l=BudgetAnalysis.xlssheet1!A1+1l=C:MSOfficeExcel=BudgetAnalysis.xlssheet1!A1+1l引用样式A1样式R1C1样式三维引用(Firstsheet:Lastsheet!RangeReference)例如：=SUM(SHEET1:SHEET13!B3)R1C1样式示例：l准确复制公式l将公式转换成值l隐藏公式l处理循环引用l循环引用lA1引用A1；A1引用B1，B1引

3、用A1；A1引用B1，B1引用C1，C1引用A1l有目的的利用循环引用l示例产生唯一随机整数A1=IF(SUM($B$1:$B$15)15,INT(RAND()*30+1),A1)B1=COUNTIF($A$1:$A$15,A1)解递归方程X=1/(1+X)解联立方程3X+4Y=83X+8Y=20l公式中的错误4.名称l名称定义及其优点l创建单元格和范围名称1）使用“定义名称”对话框2）使用“名称框”3）自动创建l渗透到多个工作表的名称l工作薄级名称和工作表级名称l创建名称列表l使用带名称的交叉运算符l使用带名称的范围运算符l命名常数l命名公式l使用带名称的交叉运算符例如：NORTH引用B2:

4、B13JANUARY引用B2:E2=JANUARYNORTHl使用带名称的范围运算符例如：=SUM(NORTHJANUARY):(WESTMARCH)l使用命名常数5.数组公式l多单元格数组公式1.选择保存结果的范围:D2:D62.输入公式：=B2:B6*C2:C63.按“Ctrl+Shift+Enter”组合键l使用单一的数组公式的优越性l单个单元格数组公式1.选择保存结果的单元格:B82.输入公式：=SUM(B2:B6*C2:C6)3.按“Ctrl+Shift+Enter”组合键l数组常量=SUM(1,2,3,4*5,6,7,8)l命名数组常量l数组维数1.一维横向数组：1,2,3,42.

5、一维纵向数组：1;2;3;43.二维数组：1,2,3;4,5,6;7,8,9l数组转置通过函数TRANSPOSE()TRANSPOSE(1,2,3,4;5,6,7,8;9,10,11,12)=1,5,9;2,6,10;3,7,11;4,8,126.函数概述l函数的功能1.简化公式2.实现其它方法无法实现的计算功能3.提高编辑速度4.实现判断的功能5.其它函数功能l函数的结构l函数参数可以使用名字、整个行（列）、文字值、表达式、其它函数、数组l在公式中输入函数的方法1.手工输入函数2.使用“插入函数”对话框的方法l函数种类1.文本函数：是用来处理文本串的。例如改变大小写或确定文本串的长度。2.统

6、计函数：对数据区进行统计和分析。例如可统计根据提供值绘制出直线的斜率、截距等。3.逻辑函数：进行真假值的判断或复合检查，根据判断的结果返回不同的数值。4.信息函数：确定存储在单元格中数据的类型。5.财务函数：有关财务方面相关计算的函数。6.工程函数：这部分函数主要用于进行工程分析。7.数学和三角函数：这部分的函数比较常用，例如求和、求平均值等，利用这些函数可以处理简单和复杂的数学计算。8.查阅和引用函数：可以利用这些函数在表格中查找特定的数值或某一单元格的引用。9.日期和时间函数：分析和处理日期或时间值。10.DDE/外部函数：主要是关于与外部数据库的动态连接以及相关的数据查询。11.数据库函

7、数：对存储在数据库或数据清单中的数据进行分析。12.用户自定义函数l二、数学和三角函数二、数学和三角函数ABS工作表函数返回参数的绝对值ACOS工作表函数返回数字的反余弦值ACOSH工作表函数返回参数的反双曲余弦值ASIN工作表函数返回参数的反正弦值ASINH工作表函数返回参数的反双曲正弦值ATAN工作表函数返回参数的反正切值ATAN2工作表函数返回给定的X及Y坐标值的反正切值ATANH工作表函数返回参数的反双曲正切值CEILING工作表函数将参数Number沿绝对值增大的方向，舍入为最接近的整数或基数COMBIN工作表函数计算从给定数目的对象集合中提取若干对象的组合数COS工作表函数返回给定

8、角度的余弦值COSH工作表函数返回参数的双曲余弦值COUNTIF工作表函数计算给定区域内满足特定条件的单元格的数目DEGREES工作表函数将弧度转换为度EVEN工作表函数返回沿绝对值增大方向取整后最接近的偶数EXP工作表函数返回e的n次幂常数e等于2.71828182845904，是自然对数的底数FACT工作表函数返回数的阶乘，一个数的阶乘等于1*2*3*.*该FACTDOUBLE工作表函数返回参数Number的半阶乘FLOOR工作表函数将参数Number沿绝对值减小的方向去尾舍入，使其等于最接近的significance的倍数GCD工作表函数返回两个或多个整数的最大公约数INT工作表函数返回

9、实数舍入后的整数值LCM工作表函数返回整数的最小公倍数LN工作表函数返回一个数的自然对数自然对数以常数项e（2.71828182845904）为底LOG工作表函数按所指定的底数，返回一个数的对数LOG10工作表函数返回以10为底的对数MDETERM工作表函数返回一个数组的矩阵行列式的值MINVERSE工作表函数返回数组矩阵的逆距阵MMULT工作表函数返回两数组的矩阵乘积结果MOD工作表函数返回两数相除的余数结果的正负号与除数相同MROUND工作表函数返回参数按指定基数舍入后的数值MULTINOMIAL工作表函数返回参数和的阶乘与各参数阶乘乘积的比值ODD工作表函数返回对指定数值进行舍入后的奇数

10、PI工作表函数返回数字3.14159265358979，即数学常数pi，精确到小数点后15位POWER工作表函数返回给定数字的乘幂PRODUCT工作表函数将所有以参数形式给出的数字相乘，并返回乘积值QUOTIENT工作表函数回商的整数部分，该函数可用于舍掉商的小数部分RADIANS工作表函数将角度转换为弧度RAND工作表函数返回大于等于0小于1的均匀分布随机数RANDBETWEEN工作表函数返回位于两个指定数之间的一个随机数ROMAN工作表函数将阿拉伯数字转换为文本形式的罗马数字ROUND工作表函数返回某个数字按指定位数舍入后的数字ROUNDDOWN工作表函数靠近零值，向下（绝对值减小的方向）

11、舍入数字ROUNDUP工作表函数远离零值，向上（绝对值增大的方向）舍入数字SERIESSUM工作表函数返回基于以下公式的幂级数之和：SIGN工作表函数返回数字的符号当数字为正数时返回1，为零时返回0，为负数时返回-1SIN工作表函数返回给定角度的正弦值SINH工作表函数返回某一数字的双曲正弦值SQRT工作表函数返回正平方根SQRTPI工作表函数返回某数与pi的乘积的平方根SUBTOTAL工作表函数返回数据清单或数据库中的分类汇总SUM工作表函数返回某一单元格区域中所有数字之和SUMIF工作表函数根据指定条件对若干单元格求和SUMPRODUCT工作表函数在给定的几组数组中，将数组间对应的元素相乘

12、，并返回乘积之和SUMSQ工作表函数返回所有参数的平方和SUMX2MY2工作表函数返回两数组中对应数值的平方差之和SUMX2PY2工作表函数返回两数组中对应数值的平方和之和，平方和加总在统计计算中经常使用SUMXMY2工作表函数返回两数组中对应数值之差的平方和TAN工作表函数返回给定角度的正切值TANH工作表函数返回某一数字的双曲正切值TRUNC工作表函数将数字的小数部分截去，返回整数l示例计算空单元格：=COUNTBLANK(DATA)非空单元格计数：=COUNTA(DATA)数字单元格计数：=COUNT(DATA)非文本单元格计数：=SUM(IF(ISNONTEXT(DATA),1)COU

13、NTIF(Range,Search_condition)出现频率最高项目的计数：（数字值）=COUNTIF(DATA,MODE(DATA)（文本值）=MAX(COUNTIF(DATA,DATA)SUMIF根据范围：=SUMIF(Difference,”=“&Today(),Amount)搜索条件AND：*=SUM(Differenc0)*(Office=“Oregon”)*Amount)OR：+=SUM(IF(Differenc=60“)/count(b2:b6)”，然后单击编辑栏中的对号，及格率就会显示出来，这个及格率看起来是不是有些别扭-“0.6”？我们可以单击鼠标右键，在弹出的菜单中选“

14、单元格格式”，将其中的数字设为百分比即可。三、逻辑函数AND函数：所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSEOR函数：OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE。它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真。NOT函数：NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用NOT函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSE。T

15、RUE、FALSE函数：TRUE、FALSE函数用来返回参数的逻辑值，由于可以直接在单元格或公式中键入值TRUE或者FALSE。因此这两个函数通常可以不使用。IF函数：IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数IF对数值和公式进行条件检测。它的语法为IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。本参数可使用任何比较运算符。Value_if_true显示在logical_test为TRUE时

16、返回的值，Value_if_true也可以是其他公式。Value_if_falselogical_test为FALSE时返回的值。Value_if_false也可以是其他公式。l示例B2=50，C2=AND(B230,B260)=TRUEB1-B3单元格中的值为TRUE、FALSE、TRUE，B4=AND(B1:B3)=FALSEB5=OR(B1:B3)=TRUENOT(2+2=4)=FALSEl应用实例销售业绩表l条件显示序序序序有时候为了表达清楚需要对满足某种条件的单元格附加某种显示，如右图效果，需要根据已知成绩显示红色框内的效果。首先在已知的数据表中选中将要显示“及格”等之类字符的第一个

17、空单元格，直接输入：=if(B2=60,不及格，if(B2=70,及格,if(B2=WEEKDAY(DATE(A1,A2,1)*7如确定2004年10月份的第一个星期日的日期l时间值舍入舍入到小时：=ROUND(A1*24,0)/24舍入到分钟：=ROUND(A1*1440,0)/1440l在时间中加小时、分钟和秒针=A1+TIME(2,30,45)l应用实例做一个美观简洁的人事资料分析表在如图所示的某公司人事资料表中，除了编号、员工姓名、身份证号码以及参加工作时间为手工添入外，其余各项均为用函数计算所得。身份证号码相关知识15位的身份证号码：16位为地区代码，78位为出生年份(2位)，910

18、位为出生月份，1112位为出生日期，第1315位为顺序号，并能够判断性别，奇数为男，偶数为女。（1）根据身份证号码求性别=IF(VALUE(RIGHT(E4,3)/2=INT(VALUE(RIGHT(E4,3)/2),“女”,“男”)（2）根据身份证号码求出生日期=CONCATENATE(19,MID(E4,7,2),/,MID(E4,9,2),/,MID(E4,11,2)（3）根据参加工作时间求年资（即工龄）=CONCATENATE(DATEDIF(F4,TODAY(),y),年,DATEDIF(F4,TODAY(),ym),个月)（4）其他说明在这张人事资料表中我们还发现，创建日期：31-

19、05-2001时显示在同一个单元格中的。具体公式写法为：=创建日期：&TEXT(TODAY(),dd-mm-yyyy)六、查询与引用函数ADDRESS用于按照给定的行号和列标，建立文本类型的单元格地址。其语法形式为：ADDRESS(row_num,column_num,abs_num,a1,sheet_text)AREAS用于返回引用中包含的区域个数。其中区域表示连续的单元格组或某个单元格。其语法形式为AREAS(reference)COLUMN用于返回给定引用的列标。语法形式为：COLUMN(reference)COLUMNS用于返回数组或引用的列数。其语法形式为COLUMNS(array)

20、ROW用于返回给定引用的行号。语法形式为：ROW(reference)ROWS用于返回引用或数组的行数。其语法形式为ROWS(array)INDEX用于返回表格或区域中的数值或对数值的引用。函数INDEX()有两种形式：数组和引用。数组形式通常返回数值或数值数组；引用形式通常返回引用。（1）INDEX(array,row_num,column_num)返回数组中指定单元格或单元格数组的数值。（2）INDEX(reference,row_num,column_num,area_num)返回引用中指定单元格或单元格区域的引用。INDIRECT用于返回由文字串指定的引用。当需要更改公式中单元格的引用

21、，而不更改公式本身，使用函数INDIRECT。其语法形式为：INDIRECT(ref_text,a1)OFFSET函数用于以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或者单元格区域，并可以指定返回的行数或者列数。其基本语法形式为：OFFSET(reference,rows,cols,height,width)。LOOKUP用于返回向量（单行区域或单列区域）或数组中的数值。函数LOOKUP有两种语法形式：向量和数组。（1）向量形式：函数LOOKUP的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值。其基本语法形式为

22、：LOOKUP(lookup_value,lookup_vector,result_vector)HLOOKUP用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。语法形式为：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)MATCH函数有两方面的功能，两种操作都返回一个位

23、置值。一是确定区域中的一个值在一列中的准确位置，这种精确的查询与列表是否排序无关。二是确定一个给定值位于已排序列表中的位置，这不需要准确的匹配.语法结构为：MATCH(lookup_value,lookup_array,match_type)HYPERLINK也就是创建快捷方式，以打开文档或网络驱动器，甚至INTERNET地址。通俗地讲，就是在某个单元格中输入此函数之后，可以到您想去的任何位置。CHOOSE可以使用index_num返回数值参数清单中的数值。使用函数CHOOSE可以基于索引号返回多达29个待选数值中的任一数值。语法形式为：CHOOSE(index_num,value1,valu

24、e2,.)TRANSPOSE用于返回区域的转置。函数TRANSPOSE必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与array的列数和行数相同。使用函数TRANSPOSE可以改变工作表或宏表中数组的垂直或水平走向。语法形式为TRANSPOSE(array)l示例lADDRESS(4,5,1,FALSE,Book1Sheet1)=Book1Sheet1!R4C5“l计算单元格总数：=ROWS(DATA)*COLUMNS(DATA)七、统计函数七、统计函数AVEDEV返回一组数据与其均值的绝对偏差的平均值，即离散度。AVEDEV(number1,number2,.)AVERAGE返

25、回参数算术平均值。AVERAGE(number1,number2,.)AVERAGEA计算参数清单中数值的平均值（算数平均值）。不仅数字，而且文本和逻辑值（如TRUE和FALSE）也将计算在内。VERAGEA(value1,value2,.)BETADIST返回Beta分布累积函数的函数值。Beta分布累积函数通常用于研究样本集合中某些事物的发生和变化情况。BETADIST(x,alpha,beta,A,B)BETAINV返回beta分布累积函数的逆函数值。即，如果probability=BETADIST(x,.)，则BETAINV(probability,.)=x。beta分布累积函数可用于

26、项目设计，在给定期望的完成时间和变化参数后，模拟可能的完成时间。BETAINV(probability,alpha,beta,A,B)BINOMDIST返回一元二项式分布的概率值。BINOMDIST(number_s,trials,probability_s,cumulative)CHIDIST返回2分布的单尾概率。2分布与2检验相关。使用2检验可以比较观察值和期望值。CHIDIST(x,degrees_freedom)CHIINV返回2分布单尾概率的逆函数。CHIINV(probability,degrees_freedom)CHITEST返回独立性检验值。函数CHITEST返回2分布的统计

27、值及相应的自由度。CHITEST(actual_range,expected_range)CONFIDENCE返回总体平均值的置信区间。置信区间是样本平均值任意一侧的区域。CONFIDENCE(alpha,standard_dev,size)CORREL返回单元格区域array1和array2之间的相关系数。使用相关系数可以确定两种属性之间的关系。CORREL(array1,array2)COUNT返回参数的个数。利用函数COUNT可以计算数组或单元格区域中数字项的个数。COUNT(value1,value2,.)COUNTA返回参数组中非空值的数目。利用函数COUNTA可以计算数组或单元格区

28、域中数据项的个数。COUNTA(value1,value2,.)COVAR返回协方差，即每对数据点的偏差乘积的平均数，利用协方差可以决定两个数据集之间的关系。COVAR(array1,array2)CRITBINOM返回使累积二项式分布大于等于临界值的最小值。此函数可以用于质量检验。CRITBINOM(trials,probability_s,alpha)DEVSQ返回数据点与各自样本均值偏差的平方和。DEVSQ(number1,number2,.)EXPONDIST返回指数分布。使用函数EXPONDIST可以建立事件之间的时间间隔模型。EXPONDIST(x,lambda,cumulativ

29、e)FDIST返回F概率分布。使用此函数可以确定两个数据系列是否存在变化程度上的不同。FDIST(x,degrees_freedom1,degrees_freedom2)FINV返回F概率分布的逆函数值。FINV(probability,degrees_freedom1,degrees_freedom2)FISHER返回点x的Fisher变换。该变换生成一个近似正态分布而非偏斜的函数。FISHER(x)FISHERINV返回Fisher变换的逆函数值。使用此变换可以分析数据区域或数组之间的相关性。FISHERINV(y)FORECAST根据给定的数据计算或预测未来值。FORECAST(x,kn

30、own_ys,known_xs)FREQUENCY以一列垂直数组返回某个区域中数据的频率分布。FREQUENCY(data_array,bins_array)FTEST返回F检验的结果。F检验返回的是当数组1和数组2的方差无明显差异时的单尾概率。可以使用此函数来判断两个样本的方差是否不同。FTEST(array1,array2)GAMMADIST返回伽玛分布。可以使用此函数来研究具有偏态分布的变量。伽玛分布通常用于排队分析。GAMMADIST(x,alpha,beta,cumulative)GAMMAINV返回伽玛分布的累积函数的逆函数。GAMMAINV(probability,alpha,b

31、eta)GAMMALN返回伽玛函数的自然对数，(x)。GAMMALN(x)GEOMEAN返回正数数组或数据区域的几何平均值。GEOMEAN(number1,number2,.)GROWTH根据给定的数据预测指数增长值。GROWTH(known_ys,known_xs,new_xs,const)HARMEAN返回数据集合的调和平均值。调和平均值与倒数的算术平均值互为倒数。HARMEAN(number1,number2,.)HYPGEOMDIST返回超几何分布。HYPGEOMDIST(sample_s,number_sample,population_s,number_population)INT

32、ERCEPT利用已知的x值与y值计算直线与y轴的截距。INTERCEPT(known_ys,known_xs)KURT返回数据集的峰值。KURT(number1,number2,.)LARGE返回数据集里第k个最大值。使用此函数可以根据相对标准来选择数值。LARGE(array,k)LINEST使用最小二乘法计算对已知数据进行最佳直线拟合，并返回描述此直线的数组。LINEST(known_ys,known_xs,const,stats)LOGEST在回归分析中，计算最符合观测数据组的指数回归拟合曲线，并返回描述该曲线的数组。LOGEST(known_ys,known_xs,const,stat

33、s)LOGINV返回x的对数正态分布累积函数的逆函数。LOGINV(probability,mean,standard_dev)LOGNORMDIST返回x的对数正态分布的累积函数。LOGNORMDIST(x,mean,standard_dev)MAX返回数据集中的最大数值。MAX(number1,number2,.)MAXA返回参数清单中的最大数值。MAXA(value1,value2,.)MEDIAN返回给定数值集合的中位数。中位数是在一组数据中居于中间的数。MEDIAN(number1,number2,.)MIN返回给定参数表中的最小值。MIN(number1,number2,.)MIN

34、A返回参数清单中的最小数值。MINA(value1,value2,.)MODE返回在某一数组或数据区域中出现频率最多的数值。MODE(number1,number2,.)NEGBINOMDIST返回负二项式分布。NEGBINOMDIST(number_f,number_s,probability_s)NORMDIST返回给定平均值和标准偏差的正态分布的累积函数。NORMDIST(x,mean,standard_dev,cumulative)NORMINV返回给定平均值和标准偏差的正态分布的累积函数的逆函数。NORMINV(probability,mean,standard_dev)NORMSD

35、IST返回标准正态分布的累积函数，该分布的平均值为0，标准偏差为1。NORMSDIST(z)NORMSINV返回标准正态分布累积函数的逆函数。该分布的平均值为0，标准偏差为1。NORMSINV(probability)PEARSON返回Pearson（皮尔生）乘积矩相关系数，r，这是一个范围在-1.0到1.0之间（包括-1.0和1.0在内）的无量纲指数，反映了两个数据集合之间的线性相关程度。PEARSON(array1,array2)PERCENTILE返回数值区域的K百分比数值点。可以使用此函数来建立接受阀值。例如，可以确定得分排名在90个百分点以上的检测侯选人。PERCENTILE(arr

36、ay,k)PERCENTRANK返回特定数值在一个数据集中的百分比排位。此函数可用于查看特定数据在数据集中所处的位置。例如，可以使用函数PERCENTRANK计算某个特定的能力测试得分在所有的能力测试得分中的位置。PERCENTRANK(array,x,significance)PERMUT返回从给定数目的对象集合中选取的若干对象的排列数。排列可以为有内部顺序的对象或为事件的任意集合或子集。排列与组合不同，组合的内部顺序无意义。此函数可用于彩票计算中的概率。PERMUT(number,number_chosen)POISSON返回泊松分布。泊松分布通常用于预测一段时间内事件发生的次数，比如一分

37、钟内通过收费站的轿车的数量。POISSON(x,mean,cumulative)PROB返回一概率事件组中落在指定区域内的事件所对应的概率之和。如果没有给出upper_limit，则返回x_range内值等于lower_limit的概率。PROB(x_range,prob_range,lower_limit,upper_limit)QUARTILE返回数据集的四分位数。四分位数通常用于在销售额和测量值数据集中对总体进行分组。例如，可以使用函数QUARTILE求得总体中前25%的收入值。QUARTILE(array,quart)RANK返回一个数值在一组数值中的排位。数值的排位是与数据清单中其他

38、数值的相对大小（如果数据清单已经排过序了，则数值的排位就是它当前的位置）。RANK(number,ref,order)RSQ返回根据known_ys和known_xs中数据点计算得出的Pearson乘积矩相关系数的平方。有关详细信息，请参阅函数REARSON。R平方值可以解释为y方差与x方差的比例。RSQ(known_ys,known_xs)SKEW返回分布的偏斜度。偏斜度反映以平均值为中心的分布的不对称程度。正偏斜度表示不对称边的分布更趋向正值。负偏斜度表示不对称边的分布更趋向负值。SKEW(number1,number2,.)SLOPE返回根据known_ys和known_xs中的数据点拟

39、合的线性回归直线的斜率。斜率为直线上任意两点的重直距离与水平距离的比值，也就是回归直线的变化率。SLOPE(known_ys,known_xs)SMALL返回数据集中第k个最小值。使用此函数可以返回数据集中特定位置上的数值。SMALL(array,k)STANDARDIZE返回以mean为平均值，以standard-dev为标准偏差的分布的正态化数值。STANDARDIZE(x,mean,standard_dev)STDEV估算样本的标准偏差。标准偏差反映相对于平均值（mean）的离散程度。STDEV(number1,number2,.)STDEVA估算基于给定样本的标准偏差。标准偏差反映数值

40、相对于平均值（mean）的离散程度。文本值和逻辑值（如TRUE或FALSE）也将计算在内。STDEVA(value1,value2,.)STDEVP返回以参数形式给出的整个样本总体的标准偏差。标准偏差反映相对于平均值（mean）的离散程度。STDEVP(number1,number2,.)STDEVPA计算样本总体的标准偏差。标准偏差反映数值相对于平均值（mean）的离散程度。STDEVPA(value1,value2,.)STEYX返回通过线性回归法计算y预测值时所产生的标准误差。标准误差用来度量根据单个x变量计算出的y预测值的误差量。STEYX(known_ys,known_xs)TDIS

41、T返回学生t-分布的百分点（概率），t分布中数值(x)是t的计算值（将计算其百分点）。t分布用于小样本数据集合的假设检验。使用此函数可以代替t分布的临界值表。TDIST(x,degrees_freedom,tails)TINV返回作为概率和自由度函数的学生t分布的t值。TINV(probability,degrees_freedom)TREND返回一条线性回归拟合线的一组纵坐标值（y值）。即找到适合给定的数组known_ys和known_xs的直线（用最小二乘法），并返回指定数组new_xs值在直线上对应的y值。TREND(known_ys,known_xs,new_xs,const)TRIM

42、MEAN返回数据集的内部平均值。函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。TRIMMEAN(array,percent)TTEST返回与学生氏-t检验相关的概率。可以使用函数TTEST判断两个样本是否可能来自两个具有相同均值的总体。TTEST(array1,array2,tails,type)VAR估算样本方差。VAR(number1,number2,.)VARA估算基于给定样本的方差。不仅数字，文本值和逻辑值（如TRUE和FALSE）也将计算在内。VARA(value1,value2,.)VARP计

43、算样本总体的方差。VARP(number1,number2,.)VARPA计算样本总体的方差。不仅数字，文本值和逻辑值（如TRUE和FALSE）也将计算在内。VARPA(value1,value2,.)WEIBULL返回韦伯分布。使用此函数可以进行可靠性分析，比如计算设备的平均故障时间。WEIBULL(x,alpha,beta,cumulative)ZTEST返回z检验的双尾P值。Z检验根据数据集或数组生成x的标准得分，并返回正态分布的双尾概率。可以使用此函数返回从某总体中抽取特定观测值的似然估计。ZTEST(array,x,sigma)八、财务函数八、财务函数ACCRINT返回定期付息有价证

44、券的应计利息。ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)ACCRINTM返回到期一次性付息有价证券的应计利息。ACCRINTM(issue,maturity,rate,par,basis)AMORDEGRC返回每个会计期间的折旧值。此函数是为法国会计系统提供的。AMORDEGRC(cost,date_purchased,first_period,salvage,period,rate,basis)AMORLINC返回每个会计期间的折旧值，该函数为法国会计系统提供。AMORLINC(cost,date_pur

45、chased,first_period,salvage,period,rate,basis)COUPDAYBS返回当前付息期内截止到成交日的天数。COUPDAYBS(settlement,maturity,frequency,basis)COUPDAYS返回成交日所在的付息期的天数。COUPDAYS(settlement,maturity,frequency,basis)COUPDAYSNC返回从成交日到下一付息日之间的天数。COUPDAYSNC(settlement,maturity,frequency,basis)COUPNCD返回成交日过后的下一付息日的日期。COUPNCD(settle

46、ment,maturity,frequency,basis)COUPNUM返回成交日和到期日之间的利息应付次数，向上取整到最近的整数。COUPNUM(settlement,maturity,frequency,basis)COUPPCD返回成交日之前的上一付息日的日期。COUPPCD(settlement,maturity,frequency,basis)CUMIPMT返回一笔贷款在给定的start-period到end-period期间累计偿还的利息数额。CUMIPMT(rate,nper,pv,start_period,end_period,type)CUMPRINC返回一笔贷款在给定的s

47、tart-period到end-period期间累计偿还的本金数额。CUMPRINC(rate,nper,pv,start_period,end_period,type)DB使用固定余额递减法，计算一笔资产在给定期间内的折旧值。DB(cost,salvage,life,period,month)DDB使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。DDB(cost,salvage,life,period,factor)DISC返回有价证券的贴现率。DISC(settlement,maturity,pr,redemption,basis)DOLLARDE将按分数表示的价格转换

48、为按小数表示的价格，如证券价格，转换为小数表示的数字。DOLLARDE(fractional_dollar,fraction)DOLLARFR将按小数表示的价格转换为按分数表示的价格。如证券价格，转换为分数型数字。DOLLARFR(decimal_dollar,fraction)DURATION返回假设面值$100的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏感程度。DURATION(settlement,maturity,couponyld,frequency,basis)EFFECT利用给定的名义年利率和一年中的复利期次，计算实际年

49、利率。EFFECT(nominal_rate,npery)FV基于固定利率及等额分期付款方式，返回某项投资的未来值。FV(rate,nper,pmt,pv,type)FVSCHEDULE基于一系列复利返回本金的未来值。函数FVSCHDULE用于计算某项投资在变动或可调利率下的未来值。FVSCHEDULE(principal,schedule)INTRATE返回一次性付息证券的利率。INTRATE(settlement,maturity,investment,redemption,basis)IPMT基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。IPMT(rate,

50、per,nper,pv,fv,type)IRR返回由数值代表的一组现金流的内部收益率。IRR(values,guess)ISPMT计算特定投资期内要支付的利息。ISPMT(rate,per,nper,pv)MDURATION返回假设面值$100的有价证券的Macauley修正期限。MDURATION(settlement,maturity,coupon,yld,frequency,basis)MIRR返回某一连续期间内现金流的修正内部收益率。MIRR(values,finance_rate,reinvest_rate)NOMINAL基于给定的实际利率和年复利期数，返回名义年利率。NOMINAL

51、(effect_rate,npery)NPER基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。NPER(rate,pmt,pv,fv,type)NPV通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。NPV(rate,value1,value2,.)ODDFPRICE返回首期付息日不固定的面值$100的有价证券的价格ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yld,redemption,frequency,basis)ODDFYIELD返回首期付息日不固定的有价证券（长期或短期）的收益

52、率。ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,redemption,frequency,basis)ODDLPRICE返回末期付息日不固定的面值$100的有价证券（长期或短期）的价格。ODDLPRICE(settlement,maturity,last_interest,rate,yld,redemption,frequency,basis)ODDLYIELD返回末期付息日不固定的有价证券（长期或短期）的收益率。ODDLYIELD(settlement,maturity,last_interest,rate,pr,rede

53、mption,frequency,basis)PMT基于固定利率及等额分期付款方式，返回贷款的每期付款额。PMT(rate,nper,pv,fv,type)PPMT基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。PPMT(rate,per,nper,pv,fv,type)PRICE返回定期付息的面值$100的有价证券的价格。PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)PRICEDISC返回折价发行的面值$100的有价证券的价格。PRICEDISC(settlement,maturity,dis

54、count,redemption,basis)PRICEMAT返回到期付息的面值$100的有价证券的价格。PRICEMAT(settlement,maturity,issue,rate,yld,basis)PV返回投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。PV(rate,nper,pmt,fv,type)RATE返回年金的各期利率。函数RATE通过迭代法计算得出，并且可能无解或有多个解。RATE(nper,pmt,pv,fv,type,guess)RECEIVED返回一次性付息的有价证券到期收回的金额。RECEIVED(settlement,m

55、aturity,investment,discount,basis)SLN返回某项资产在一个期间中的线性折旧值。SLN(cost,salvage,life)SYD返回某项资产按年限总和折旧法计算的指定期间的折旧值。SYD(cost,salvage,life,per)TBILLEQ返回国库券的等效收益率。TBILLEQ(settlement,maturity,discount)TBILLPRICE返回面值$100的国库券的价格。TBILLPRICE(settlement,maturity,discount)TBILLYIELD返回国库券的收益率。TBILLYIELD(settlement,mat

56、urity,pr)VDB使用双倍余额递减法或其他指定的方法，返回指定的任何期间内（包括部分期间）的资产折旧值。函数VDB代表可变余额递减法。VDB(cost,salvage,life,start_period,end_period,factor,no_switch)XIRR返回一组现金流的内部收益率，这些现金流不一定定期发生。若要计算一组定期现金流的内部收益率，请使用函数IRR。XIRR(values,dates,guess)XNPV返回一组现金流的净现值，这些现金流不一定定期发生。若要计算一组定期现金流的净现值，请使用函数NPV。XNPV(rate,values,dates)YIELD返回定

57、期付息有价证券的收益率，函数YIELD用于计算债券收益率。YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)YIELDDISC返回折价发行的有价证券的年收益率。YIELDDISC(settlement,maturity,pr,redemption,basis)YIELDMAT返回到期付息的有价证券的年收益率。YIELDMAT(settlement,maturity,issue,rate,pr,basis)l财务函数常用参数的含义未来值未来值(fv)-在所有付款发生后的投资或贷款的价值。期间数期间数(nper)-为总投资（或

58、贷款）期，即该项投资（或贷款）的付款期总数。付款付款(pmt)-对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。现值现值(pv)-在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。利率利率(rate)-投资或贷款的利率或贴现率。类型类型(type)-付款期间内进行支付的间隔，如在月初或月末，用0或1表示。日计数基准类型（日计数基准类型（basis）-为日计数基准类型。Basis为0或省略代表US(NASD)30/360，为1代表实际天数/实际天数，为2代表实际天数/360，为3代表实际天数/365，为4代表欧洲3

59、0/360。l示例求某项投资的未来值求某项投资的未来值FV假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入2000元，如果按年利2.25%，按月计息（月利为2.25%/12），那么两年以后该账户的存款额会是多少呢？公式写为：FV(2.25%/12,24,-2000,0,1)求投资的净现值求投资的净现值NPV假设开一家电器经销店。初期投资￥200,000，而希望未来五年中各年的收入分别为￥20,000、￥40,000、￥50,000、￥80,000和￥120,000。假定每年的贴现率是8%（相当于通贷膨胀率或竞争投资的利率）。则投资的净现值的公式是：=NPV(A2,A4:A8)

60、+A3假设该电器店的营业到第六年时，要重新装修门面，估计要付出￥40,000，则六年后书店投资的净现值为：=NPV(A2,A4:A8,A9)+A3如果期初投资的付款发生在期末，则投资的净现值的公式是：=NPV(A2,A3:A8)A2=8%；A3=-200,000；A4=20,000；A5=40,000；A6=50,000；A7=80,000；A8=120,000；A9=-40,000求贷款分期偿还额求贷款分期偿还额PMT需要10个月付清的年利率为8%的￥10,000贷款的月支额为：PMT（8%/12,10,10000）计算结果为：-￥1,037.03。求某项投资的现值求某项投资的现值PV假设要

61、购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600。此项年金的购买成本为80,000，假定投资回报率为8%。那么该项年金的现值为：PV(0.08/12,12*20,600,0)计算结果为：￥-71,732.58。负值表示这是一笔付款，也就是支出现金流。年金（￥-71,732.58）的现值小于实际支付的（￥80,000）。因此，这不是一项合算的投资。返回内部收益率的函数返回内部收益率的函数-IRR如果要开办一家服装商店，预计投资为￥110,000，并预期为今后五年的净收益为：￥15,000、￥21,000、￥28,000、￥36,000和￥45,000。分别求出投资两年、四年以及五年

62、后的内部收益率。计算此项投资四年后的内部收益率IRR（B1：B5）为-3.27%；计算此项投资五年后的内部收益率IRR（B1：B6）为8.35%;计算两年后的内部收益率时必须在函数中包含guess，即IRR（B1：B3，-10%）为-48.96%。用用RATE函数计算某项投资的实际赢利函数计算某项投资的实际赢利某承包人建议你贷给他30000元，用作公共工程建设资金，并同意每年付给你9000元，共付五年，以此作为这笔贷款的最低回报。那么你如何去决策这笔投资？如何知道这项投资的回报率呢？即公式为=RATE(5,9000,-30000)确定后计算结果为15.24。求定期付息有价证券的应计利息的函数求

63、定期付息有价证券的应计利息的函数ACCRINT某国库券的交易情况为：发行日为2008年3月1日；起息日为2008年8月31日；成交日为2008年5月1日，息票利率为10.0%；票面价值为￥1,000；按半年期付息；日计数基准为30/360，那么应计利息为：求本金数额求本金数额CUMPRINC一笔住房抵押贷款的交易情况如下：年利率为9.00%；期限为30年；现值为￥125，000。由上述已知条件可以计算出：r=9.00%/12=0.0075，np=30*12=360。那么该笔贷款在第下半年偿还的全部本金之中（第7期到第12期）为：=CUMPRINC(A2/12,A3*12,A4,7,12,0)计

64、算结果为：-436.568194。该笔贷款在第一个月偿还的本金为：=CUMPRINC(A2/12,A3*12,A4,1,1,0)计算结果为：-68.27827118。求有价证券的贴现率求有价证券的贴现率DISC某债券的交易情况如下：成交日为99年3月18日，到期日为99年8月7日，价格为￥48.834，清偿价格为￥52，日计数基准为实际天数/360。那么该债券的贴现率为：DISC(99/3/18,99/8/7,48.834,52,2)计算结果为：0.154355363。九、数据库函数l数据库函数的参数含义数据库函数的参数含义该类函数的语法形式为函数名称(database,field,crite

65、ria)。Database为构成数据清单或数据库的单元格区域。数据库是包含一组相关数据的数据清单，其中包含相关信息的行为记录，而包含数据的列为字段。数据清单的第一行包含着每一列的标志项。Field为指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field可以是文本，即两端带引号的标志项，如“使用年数”或“产量”；此外，Field也可以是代表数据清单中数据列位置的数字：1表示第一列，2表示第二列，等等。Criteria为一组包含给定条件的单元格区域。可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。注意：数据库函数不能使用数组l

66、数据库和列表l基本列表操作l自动筛选l高级筛选高级筛选-条件的建立条件的建立l有关概念条件条件是指所指定的限制查询或筛选的结果集中包含哪些记录的条件。例如，前面示例中条件选择“高度”字段的值大于10的记录：高度10。清单清单是指包含相关数据的一系列工作表行，例如，发票数据库或一组客户名称和电话号码。清单的第一行具有列标志。l建立条件区域的基本要求（1）在可用作条件区域的数据清单上插入至少三个空白行。（2）条件区域必须具有列标志（条件区域的第一行必须包含某些或全部列表字段名）。（3）条件区域的另一行必须包含筛选条件。（3）请确保在条件值与数据清单之间至少留了一个空白行。l创建分类汇总创建分类汇总l数据有效性可以设定一定的规则对输入的数据进行验证，只有符合条件的数据才允许输入。l示例某果园的果树的高度、使用年数、产量与利润的统计数据表如图所示，（1）有多少种苹果树的树高在1016英尺之间（2）苹果树与梨树的最大利润值是多少（3）高度大于10英尺的苹果树的最小利润是多少（4）苹果树的总利润（5）高度大于10英尺的苹果树的平均产量（6）果园中所有树种的平均使用年数（7）求苹果树和梨树产量的估算