《函数与应用精选PPT演示文稿》由会员分享，可在线阅读，更多相关《函数与应用精选PPT演示文稿（51页珍藏版）》请在金锄头文库上搜索。
1、第七章函数与应用佟强对外经济贸易大学信息学院2006年11月1 1函数ExcelExcel函数一共函数一共1111类，分别是数据库工作函数、日类，分别是数据库工作函数、日期与时间函数、工程函数、信息函数、财务函数、期与时间函数、工程函数、信息函数、财务函数、逻辑函数、统计函数、查找和引用函数、文本函逻辑函数、统计函数、查找和引用函数、文本函数、数学和三角函数和用户自定义函数。数、数学和三角函数和用户自定义函数。函数的构成：函数的构成：l l函数名：不区分大小写。函数名：不区分大小写。l l参数表：用逗号分隔的参数参数表：用逗号分隔的参数1 1，参数，参数2 2，参数，参数N N（N30N30）
2、构成；参数可以是常数、单元格地址、单）构成；参数可以是常数、单元格地址、单元格区域、单元格区域的名称或函数。元格区域、单元格区域的名称或函数。l l返回值：根据参数表给出的参数计算得到的结果。返回值：根据参数表给出的参数计算得到的结果。2 2函数嵌套函数嵌套是指一个函数可以作为另一个函数的参数使用。例如：ROUND(AVERAGE(A2:C2),0)l lAVERAGEAVERAGE的返回值作为的返回值作为ROUNDROUND的参数；的参数；l lExcelExcel函数嵌套最多可嵌套七级。函数嵌套最多可嵌套七级。3 3函数的输入单击x插入菜单函数快捷键：Shift+F34 4Excel函数数
3、学与三角函数数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数5 5绝对值函数ABSABS(number)l l返回数字的绝对值（返回数字的绝对值（AbsoluteAbsolute ValueValue）numbernumber需要计算其绝对值的实数需要计算其绝对值的实数l l例子：例子：ABS(5.8)=5.8ABS(-4.3)=4.3ABS(5.8)=5.8ABS(-4.3)=4.36 6取整函数INTINT(number)l l将数字向下舍入到最接近的整数（将数字向下舍入到最接近的整数（IntegerInteger）l lnumbernumber需要进行向下舍
4、入取整的实数需要进行向下舍入取整的实数l l例子：例子：INT(8.9)INT(8.9)将将8.98.9向下舍入到最接近的整数向下舍入到最接近的整数(8)(8)INT(-8.9)INT(-8.9)将将-8.9-8.9向下舍入到最接近的整数向下舍入到最接近的整数(-9)(-9)A2-INT(A2)A2-INT(A2)返回单元格返回单元格A2A2中正实数的小数部分中正实数的小数部分 7 7截取函数TRUNCTRUNCTRUNC( (numbernumber,num_digits),num_digits)l l如果省略如果省略num_digitsnum_digits，截取（，截取（TruncateT
5、runcate）numbernumber的整的整数部分，否则保留指定位数的小数数部分，否则保留指定位数的小数l lnumbernumber需要截尾取整的数字需要截尾取整的数字l lnum_digitsnum_digits小数位数，默认值为小数位数，默认值为00l l例子：例子：TRUNC(3.6)=3TRUNC(3.6)=3TRUNC(-3.6)=-3TRUNC(-3.6)=-3TRUNC(5.627,1)=5.6TRUNC(5.627,1)=5.6TRUNC(5.627,2)=5.62TRUNC(5.627,2)=5.628 8求余函数MODMODMOD( (numbernumber, ,d
6、ivisordivisor) )（ModulModul）返回两数相除的余数，结果的正负号与除数相同，符号不返回两数相除的余数，结果的正负号与除数相同，符号不同取互补数。同取互补数。 numbernumber被除数被除数divisordivisor除数除数例子：例子：MOD(7,4)=3MOD(7,4)=3MOD(-7,-4)=3MOD(-7,-4)=3MOD(7,-4)=-1MOD(7,-4)=-1MOD(-7,4)=1MOD(-7,4)=1如果如果divisordivisor为零，函数为零，函数MODMOD返回错误值返回错误值#DIV/0!#DIV/0!9 9正弦SIN、余弦COSSIN(x
7、)：返回x的正弦（Sine）值，其中x用弧度表示。COS(x)：返回x的余弦(Cosine)值，其中x用弧度表示。例子：用图形描述正弦和余弦的曲线1010平方根函数SQRTSQRT(number)返回number的平方根（Square root）例子：SQRT(4)=2说明：如果参数Number为负值，函数SQRT返回错误值#NUM!1111随机函数RANDRAND()返回大于等于0及小于1的均匀分布随机（Random）数，每次计算工作表时都将返回一个新的数值。若要生成a与b之间的随机实数，请使用：RAND()*(b-a)+a例子：产生1-20之间的整数：INT(RAND()*20)+1121
8、2对数函数自然对数函数自然对数函数LN(b)LN(b)：返回一个数的自然对数：返回一个数的自然对数（Logarithm），自然对数以常数项，自然对数以常数项ee(2.71828182845904)(2.71828182845904)为底为底对数函数对数函数LOG(b,a)LOG(b,a)：返回以：返回以a a为底，为底，b b的对数，的对数，即即logloga ab b常用对数函数常用对数函数LOG10(b)LOG10(b)：返回以：返回以1010为底，为底，b b的对的对数，即数，即loglog1010b b1313指数函数EXPEXP(n)：Exponent返回e的n次幂1414求幂函数P
9、OWER(a,b)POWER(a,b)：返回：返回a a的的b b次幂（次幂（PowerPower），等价），等价于于abab例子：例子：POWER(2,3)=8POWER(2,3)=8POWER(2,10)=1024POWER(2,10)=1024POWER(2,20)=1,048,576POWER(2,20)=1,048,576POWER(2,30)=1,073,741,824POWER(2,30)=1,073,741,8241515Excel函数数学与三角函数统计函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数1616中位数函数MEDIANMEDIANMEDIAN(
10、(number1number1,number2,.),number2,.)返回给定数值集合的中值（返回给定数值集合的中值（MedianMedian），如果参加），如果参加统计的数据的个数为偶数（统计的数据的个数为偶数（EvenEven），返回位于中），返回位于中间的两个数的平均值。间的两个数的平均值。例子：例子：MEDIAN(1,5,2)=2MEDIAN(1,5,2)=2MEDIAN(2,1,4,3)=2.5MEDIAN(2,1,4,3)=2.5（2 2和和3 3的平均数）的平均数）说明：说明：l l参数应为数字，或者是包含数字的名称、数组或引用；参数应为数字，或者是包含数字的名称、数组或引用
11、；l l如果数组或引用参数包含文本、逻辑值或空白单元格，如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。则这些值将被忽略；但包含零值的单元格将计算在内。 1717众数函数MODE众数（众数（ModeMode）：一组中出现最频繁的数或数列。）：一组中出现最频繁的数或数列。MODE(MODE(number1number1,number2,.),number2,.)返回在某一数组或数据区域中出现频率最多的数返回在某一数组或数据区域中出现频率最多的数值值 如果数据集合中不含有重复的数据，则MODE数返回错误值#N/A例子：MODE(2,5,6,5,8,6
12、,11,6,8)=6MODE(2,6,5,6,2,6,5,5)=61818众数、中位数和平均数在一组数值中，众数（在一组数值中，众数（ModeMode）是出现频率最高的）是出现频率最高的数值，而中位数（数值，而中位数（MedianMedian）是位于中间的值，平）是位于中间的值，平均数（均数（AverageAverage）是平均后的值，所有这些求中）是平均后的值，所有这些求中函数都不能单独地完全描绘所有数据。函数都不能单独地完全描绘所有数据。假设数据分布在三个区域中，其中一半分布在一假设数据分布在三个区域中，其中一半分布在一个较小数值区中，另外一半分布在两个较大数值个较小数值区中，另外一半分布
13、在两个较大数值区中。函数区中。函数AVERAGEAVERAGE和函数和函数MEDIANMEDIAN可能会可能会返回位于数据点稀疏处的中间值；而函数返回位于数据点稀疏处的中间值；而函数MODEMODE则会返回位于数据点密集处的较小值。则会返回位于数据点密集处的较小值。1919频数函数FREQUENCYFREQUENCYFREQUENCY( (data_arraydata_array, ,bins_arraybins_array) )以一列垂直数组返回某个区域中数据的频率（以一列垂直数组返回某个区域中数据的频率（FrequencyFrequency）分布）分布data_arraydata_arra
14、y为一数组或对一组数值的引用，用来计算频为一数组或对一组数值的引用，用来计算频率。率。bins_arraybins_array为间隔的数组或对间隔的引用，该间隔用于为间隔的数组或对间隔的引用，该间隔用于对对data_arraydata_array中的数值进行分组。中的数值进行分组。bins_arraybins_array要求从小到大存放，每个数据表示一个分段点，要求从小到大存放，每个数据表示一个分段点，统计的结果包含该数据以及小于该数据的个数统计的结果包含该数据以及小于该数据的个数计算结果是一个数组，用计算结果是一个数组，用“Ctrl+Shift+Enter”“Ctrl+Shift+Enter
15、”输入输入例子：统计例子：统计“ “职工情况表职工情况表” ”不同年龄段的人数。不同年龄段的人数。2020标准差函数STDEV标准差（Standard Deviation）：反映一组数据于数据的平均值的离散程度。如果标准差比较小，说明数据于平均值的离散程度就比较小，平均值能够反映数据的均值，具有统计意义。如果标准差比较大，说明数据与平均值的离散程度就比较大（数据中含有非常大或非常小的数），平均值在一定程度上失去数据“均值”的意义。2121标准差函数STDEV若有共若有共n n个数个数，则：，则：l l平均值平均值l l标准差标准差2222线型趋势值函数FORECASTFORECASTFOREC
16、AST( (x x, ,known_ysknown_ys, ,known_xsknown_xs) )已知的数值为已有的已知的数值为已有的xx值和值和yy值，再利用线性回值，再利用线性回归对新值进行预测（归对新值进行预测（Forecast）xx为需要进行预测的数据点为需要进行预测的数据点known_ysknown_ys为因变量数组或数据区域为因变量数组或数据区域known_xsknown_xs为自变量数组或数据区域为自变量数组或数据区域例子：城镇居民家庭平均每人全年消费性支出表例子：城镇居民家庭平均每人全年消费性支出表2323Excel函数数学与三角函数统计函数逻辑函数逻辑函数数据库函数财务函数
17、日期函数查找和引用函数文本函数2424逻辑与函数ANDAND(logical1,logical2,.)AND(logical1,logical2,.)所有参数的逻辑值为真时，返回所有参数的逻辑值为真时，返回TRUETRUE；只要一；只要一个参数的逻辑值为假，即返回个参数的逻辑值为假，即返回FALSEFALSE。说明：说明：l l参数必须是逻辑值参数必须是逻辑值TRUETRUE或或FALSE,FALSE,或者包含逻辑值或者包含逻辑值的数组或引用。的数组或引用。 l l如果数组或引用参数中包含文本或空白单元格，则这如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。些值将被忽略。 l l如
18、果指定的单元格区域内包括非逻辑值，则如果指定的单元格区域内包括非逻辑值，则ANDAND将返将返回错误值回错误值#VALUE!#VALUE!。2525逻辑或函数OROR(logical1,logical2,.)OR(logical1,logical2,.)任何一个参数逻辑值为任何一个参数逻辑值为TRUETRUE，即返回，即返回TRUETRUE；所有的参数均为所有的参数均为FALSEFALSE，才返回，才返回FALSEFALSE。 2626逻辑非函数NOTNOT(logical)函数值取参数的反值。参数值为TRUE则函数值为FALSE；反之，参数值为FALSE则函数值为TRUE。例子：工资补助表2
19、727Excel函数数学与三角函数统计函数逻辑函数数据库函数数据库函数财务函数日期函数查找和引用函数文本函数2828数据库函数的格式数据库函数的格式：数据库函数的格式：DatabaseFunction(DatabaseFunction(database,field,criteria)其中：其中：databasedatabase是数据清单区是数据清单区fieldfield用于用于指明要统计的列，用于用于指明要统计的列，fieldfield可以是：可以是：l l字段（字段（FieldField）名所在的单元格地址；）名所在的单元格地址；l l带英文双引号的带英文双引号的“ “字段名字段名” ”；l
20、 l字段在数据清单中的位置，字段在数据清单中的位置，“1”“1”表示第表示第1 1列，等等。列，等等。CriteriaCriteria：条件区，约定与高级筛选一样。：条件区，约定与高级筛选一样。2929条件区条件区的第一行是字段名或者是数据清单中字段条件区的第一行是字段名或者是数据清单中字段名所在单元格地址的引用，或为空白（公式作为名所在单元格地址的引用，或为空白（公式作为筛选条件）；筛选条件）；从第二行开始设置筛选条件：筛选条件出现在不从第二行开始设置筛选条件：筛选条件出现在不同的行表示同的行表示“ “或或” ”关系，筛选条件出现在相同行表示关系，筛选条件出现在相同行表示“ “与与” ”关系
21、；关系；在条件中允许出现在条件中允许出现“*”“*”代表任意一个字符串，代表任意一个字符串，“?”“?”代代表任意一个字符；表任意一个字符；3030求和函数DSUMDSUM(database,field,criteria)功能：对database区的field字段求满足条件区条件的数据的累加和说明：l lDSUMDSUM和和SUMSUM不同，不同，SUMSUM求任意数据的累加和，求任意数据的累加和，DSUMDSUM求数据清单中一列数据中满足条件的数求数据清单中一列数据中满足条件的数据累加和；据累加和；l lDSUMDSUM包含了包含了SUMIFSUMIF的功能。的功能。SUMIFSUMIF只能
22、对给只能对给定的一个条件求数据的累加和，而定的一个条件求数据的累加和，而DSUMDSUM可以可以对给定的多个条件求指定的列数据的累加和。对给定的多个条件求指定的列数据的累加和。3131其他数据库函数平均值函数平均值函数DAVERAGEDAVERAGE最大值函数最大值函数DMAXDMAX最小值函数最小值函数DMINDMIN统计数值型数据的个数统计数值型数据的个数DCOUNTDCOUNTl l省略省略fieldfield字段，对数据清单区求满足条件的个数字段，对数据清单区求满足条件的个数统计个数统计个数DCOUNTADCOUNTAl l省略省略fieldfield字段，对数据清单区求满足条件的个数
23、字段，对数据清单区求满足条件的个数3232Excel函数数学与三角函数统计函数逻辑函数数据库函数财务函数财务函数日期函数查找和引用函数文本函数3333偿还函数PMTPMT(rate,nper,pv,fv,type)PMT(rate,nper,pv,fv,type)功能：基于固定利率及等额分期付款方式，返回功能：基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。投资或贷款的每期付款额。l lraterate贷款利率。贷款利率。l lnpernper该项贷款的付款总数。该项贷款的付款总数。l lpvpv现值，或一系列未来付款的当前值的累积和，也现值，或一系列未来付款的当前值的累积和，也称
24、为本金。称为本金。l lfvfv为未来值，或在最后一次付款后希望得到的现金余为未来值，或在最后一次付款后希望得到的现金余额，如果省略额，如果省略fvfv，则假设其值为零，也就是一笔贷款，则假设其值为零，也就是一笔贷款的未来值为零。的未来值为零。l ltype0type0或者省略表示期末，或者省略表示期末，1 1表示期初。表示期初。例子：例子：PMT(5.76%/12,24,200000)=PMT(5.76%/12,24,200000)=￥-8,842.51-8,842.513434可贷款函数PVPV(rate,nper,pmt,fv,type)返回现值。例子：月偿还能力200万，利率6%，贷1
25、2个月，求可贷款金额：PV(6%/12,12,200)=￥-2,323.793535未来值函数FV(rate,nper,pmt,pv,type)功能：基于固定利率及等额分期付款方式，返回某项投资的未来值。3636返回投资的总期数函数NPERNPER(rate,pmt,pv,fv,type)功能：基于固定利率及等额分期付款方式，返回某项投资的总期数。3737利率函数RATERATE(nper,pmt,pv,fv,type,guess)返回年金的各期利率。函数RATE通过迭代法计算得出，并且可能无解或有多个解。如果在进行20次迭代计算后，函数RATE的相邻两次结果没有收敛于0.0000001，函数
26、RATE将返回错误值#NUM!。3838Excel函数数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数日期函数查找和引用函数文本函数3939日期函数DAY(serial_number):一个月中的哪一天MONTH(serial_number)：月份YEAR(serial_number)：年例子：l lDAY(2008/10/1)=1DAY(2008/10/1)=1l lMONTH(2008MONTH(2008年年1010月月1 1日日)=10)=10l lYEAR(2008-10-1)=2008YEAR(2008-10-1)=20084040日期函数NOW()：返回当前计算机系统的日期
27、和时间。TODAY()：返回计算机系统的日期。例子：NOW()=7/27/202405:37TODAY()=7/27/2024建立日期函数DATE(year,month,day)DATE(2008,8,8)=2008-8-84141Excel函数数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数查找和引用函数文本函数4242按列查找函数VLOOKUPVLOOKUP(lookup_value,table_array,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)col_index_num,range_l
28、ookup)lookup_valuelookup_value：查找值：查找值table_arraytable_array：数据区：数据区col_index_numcol_index_num：为：为 数据区中数据区中数据区中数据区中待返回的匹配值的列序号待返回的匹配值的列序号range_lookuprange_lookup：如果为：如果为TRUETRUE或省略，则返回近似匹配或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于值，也就是说，如果找不到精确匹配值，则返回小于 lookup_valuelookup_value的最大数值；如果的最大数值；如果range_valueran
29、ge_value为为FALSEFALSE，函数，函数VLOOKUPVLOOKUP将返回精确匹配值。如果找不到，则将返回精确匹配值。如果找不到，则返回错误值返回错误值#N/A#N/A。4343按列查找函数VLOOKUP如果如果range_lookuprange_lookup为为TRUETRUE，则，则table_arraytable_array的的第一列中的数值必须按升序排列；如果第一列中的数值必须按升序排列；如果 range_lookuprange_lookup为为FALSEFALSE，table_arraytable_array不必进行不必进行排序。排序。通过在通过在“ “数据数据” ”菜单
30、中的菜单中的“ “排序排序” ”中选择中选择“ “升序升序” ”，可将，可将数值按升序排列。数值按升序排列。Table_arrayTable_array的第一列中的数值可以为文本、数字的第一列中的数值可以为文本、数字或逻辑值。或逻辑值。文本不区分大小写。文本不区分大小写。4444按行查找函数HLOOKUPHLOOKUP(lookup_value,table_array,row_index_num,range_lookup)在数据区的首行查找指定的数值，并由此返回数据区当前列中指定行处的数值。4545数组转置函数TRANSPOSETRANSPOSE(array)返回转置单元格区域，即将一行单元格
31、区域转置成一列单元格区域。array为需要进行转置的数组或工作表中的单元格区域。4646Excel函数数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数文本函数4747文本函数（1）字符串长度函数字符串长度函数LEN(S)LEN(S)、LENB(S)LENB(S)截取子串函数截取子串函数MID(S,m,n)MID(S,m,n)截取左子串函数截取左子串函数LEFT(S,n)LEFT(S,n)截取右子串函数截取右子串函数RIGHT(S,n)RIGHT(S,n)删除首尾空格的函数删除首尾空格的函数TRIM(S)TRIM(S)数值转文本函数数值转文本函数TEXT(value
32、,format_text)TEXT(value,format_text)四舍五入转换文本函数四舍五入转换文本函数FIXEDFIXED( (numbernumber,decimals,no_commas),decimals,no_commas)文本转数值函数文本转数值函数VALUE(S)VALUE(S)4848文本函数（2）大小写字母转换函数大小写字母转换函数LOWER(S)UPPER(S)LOWER(S)UPPER(S)替换函数替换函数REPLACE(S1,m,n,S2)REPLACE(S1,m,n,S2)比较函数比较函数EXACT(S1,S2)EXACT(S1,S2)查找函数查找函数FIND(sub,S,n)FIND(sub,S,n)搜索函数搜索函数SEARCH(sub,S,n)SEARCH(sub,S,n)重复文本函数重复文本函数(S,n)(S,n)4949本章小结数学与三角函数数学与三角函数统计函数统计函数逻辑函数逻辑函数数据库函数数据库函数财务函数财务函数日期函数日期函数查找和引用函数查找和引用函数文本函数文本函数5050本章作业预习l l第八章第八章 数据分析数据分析作业l l教材教材P120P120习题习题l l教材教材P171P171177177上机实验（函数应用）上机实验（函数应用）5151
