《函数与应用》PPT课件.ppt

第七章 函数与应用,佟强对外经济贸易大学信息学院2006年11月,函数,Excel函数一共11类，分别是数据库工作函数、日期与时间函数、工程函数、信息函数、财务函数、逻辑函数、统计函数、查找和引用函数、文本函数、数学和三角函数和用户自定义函数。函数的构成：函数名：不区分大小写。参数表：用逗号分隔的参数1，参数2，参数N（N30）构成；参数可以是常数、单元格地址、单元格区域、单元格区域的名称或函数。返回值：根据参数表给出的参数计算得到的结果。,函数嵌套,函数嵌套是指一个函数可以作为另一个函数的参数使用。例如：ROUND(AVERAGE(A2:C2),0)AVERAGE的返回值作为ROUND的参数；Excel函数嵌套最多可嵌套七级。,函数的输入,单击x插入菜单函数快捷键：Shift+F3,Excel函数,数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数,绝对值函数ABS,ABS(number)返回数字的绝对值（Absolute Value）number 需要计算其绝对值的实数例子：ABS(5.8)=5.8 ABS(-4.3)=4.3,取整函数INT,INT(number)将数字向下舍入到最接近的整数（Integer）number 需要进行向下舍入取整的实数例子：INT(8.9)将 8.9 向下舍入到最接近的整数(8)INT(-8.9)将-8.9 向下舍入到最接近的整数(-9)A2-INT(A2)返回单元格 A2 中正实数的小数部分,截取函数TRUNC,TRUNC(number,num_digits)如果省略num_digits，截取（Truncate）number的整数部分，否则保留指定位数的小数number 需要截尾取整的数字num_digits 小数位数，默认值为 0 例子：TRUNC(3.6)=3TRUNC(-3.6)=-3TRUNC(5.627,1)=5.6TRUNC(5.627,2)=5.62,求余函数MOD,MOD(number,divisor)（Modul）返回两数相除的余数，结果的正负号与除数相同，符号不同取互补数。number 被除数divisor 除数例子：MOD(7,4)=3 MOD(-7,-4)=3MOD(7,-4)=-1MOD(-7,4)=1 如果 divisor 为零，函数 MOD 返回错误值#DIV/0!,正弦SIN、余弦COS,SIN(x)：返回x的正弦（Sine）值，其中x用弧度表示。COS(x)：返回x的余弦(Cosine)值，其中x用弧度表示。例子：用图形描述正弦和余弦的曲线,平方根函数SQRT,SQRT(number)返回number的平方根（Square root）例子：SQRT(4)=2 说明：如果参数 Number 为负值，函数 SQRT 返回错误值#NUM!,随机函数RAND,RAND()返回大于等于 0 及小于 1 的均匀分布随机（Random）数，每次计算工作表时都将返回一个新的数值。若要生成 a 与 b 之间的随机实数，请使用：RAND()*(b-a)+a例子：产生1-20之间的整数：INT(RAND()*20)+1,对数函数,自然对数函数LN(b)：返回一个数的自然对数（Logarithm），自然对数以常数项 为底对数函数LOG(b,a)：返回以a为底，b的对数，即logab常用对数函数LOG10(b)：返回以10为底，b的对数，即log10b,指数函数EXP,EXP(n)：Exponent返回 e 的 n 次幂,求幂函数,POWER(a,b)：返回a的b次幂（Power），等价于ab例子：POWER(2,3)=8POWER(2,10)=1024POWER(2,20)=1,048,576POWER(2,30)=1,073,741,824,Excel函数,数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数,中位数函数MEDIAN,MEDIAN(number1,number2,.)返回给定数值集合的中值（Median），如果参加统计的数据的个数为偶数（Even），返回位于中间的两个数的平均值。例子：MEDIAN(1,5,2)=2MEDIAN(2,1,4,3)=2.5（2和3的平均数）说明：参数应为数字，或者是包含数字的名称、数组或引用；如果数组或引用参数包含文本、逻辑值或空白单元格，则这些值将被忽略；但包含零值的单元格将计算在内。,众数函数MODE,众数（Mode）：一组中出现最频繁的数或数列。MODE(number1,number2,.)返回在某一数组或数据区域中出现频率最多的数值 如果数据集合中不含有重复的数据，则 MODE 数返回错误值#N/A 例子：MODE(2,5,6,5,8,6,11,6,8)=6MODE(2,6,5,6,2,6,5,5)=6,众数、中位数和平均数,在一组数值中，众数（Mode）是出现频率最高的数值，而中位数（Median）是位于中间的值，平均数（Average）是平均后的值，所有这些求中函数都不能单独地完全描绘所有数据。假设数据分布在三个区域中，其中一半分布在一个较小数值区中，另外一半分布在两个较大数值区中。函数 AVERAGE 和函数 MEDIAN 可能会返回位于数据点稀疏处的中间值；而函数 MODE 则会返回位于数据点密集处的较小值。,频数函数FREQUENCY,FREQUENCY(data_array,bins_array)以一列垂直数组返回某个区域中数据的频率（Frequency）分布data_array 为一数组或对一组数值的引用，用来计算频率。bins_array 为间隔的数组或对间隔的引用，该间隔用于对 data_array 中的数值进行分组。bins_array要求从小到大存放，每个数据表示一个分段点，统计的结果包含该数据以及小于该数据的个数计算结果是一个数组，用“Ctrl+Shift+Enter”输入例子：统计“职工情况表”不同年龄段的人数。,标准差函数STDEV,标准差（Standard Deviation）：反映一组数据于数据的平均值的离散程度。如果标准差比较小，说明数据于平均值的离散程度就比较小，平均值能够反映数据的均值，具有统计意义。如果标准差比较大，说明数据与平均值的离散程度就比较大（数据中含有非常大或非常小的数），平均值在一定程度上失去数据“均值”的意义。,标准差函数STDEV,若有共n个数，则：平均值标准差,线型趋势值函数FORECAST,FORECAST(x,known_ys,known_xs)已知的数值为已有的 x 值和 y 值，再利用线性回归对新值进行预测（Forecast）x 为需要进行预测的数据点known_ys 为因变量数组或数据区域known_xs 为自变量数组或数据区域例子：城镇居民家庭平均每人全年消费性支出表,Excel函数,数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数,逻辑与函数AND,AND(logical1,logical2,.)所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。说明：参数必须是逻辑值 TRUE 或 FALSE,或者包含逻辑值的数组或引用。如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。如果指定的单元格区域内包括非逻辑值，则 AND 将返回错误值#VALUE!。,逻辑或函数OR,OR(logical1,logical2,.)任何一个参数逻辑值为 TRUE，即返回 TRUE；所有的参数均为FALSE，才返回FALSE。,逻辑非函数NOT,NOT(logical)函数值取参数的反值。参数值为TRUE则函数值为FALSE；反之，参数值为FALSE则函数值为TRUE。例子：工资补助表,Excel函数,数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数,数据库函数的格式,数据库函数的格式：DatabaseFunction(database,field,criteria)其中：database是数据清单区field用于用于指明要统计的列，field可以是：字段（Field）名所在的单元格地址；带英文双引号的“字段名”；字段在数据清单中的位置，“1”表示第1列，等等。Criteria：条件区，约定与高级筛选一样。,条件区,条件区的第一行是字段名或者是数据清单中字段名所在单元格地址的引用，或为空白（公式作为筛选条件）；从第二行开始设置筛选条件：筛选条件出现在不同的行表示“或”关系，筛选条件出现在相同行表示“与”关系；在条件中允许出现“*”代表任意一个字符串，“?”代表任意一个字符；,求和函数DSUM,DSUM(database,field,criteria)功能：对database区的field字段求满足条件区条件的数据的累加和说明：DSUM和SUM不同，SUM求任意数据的累加和，DSUM求数据清单中一列数据中满足条件的数据累加和；DSUM包含了SUMIF的功能。SUMIF只能对给定的一个条件求数据的累加和，而DSUM可以对给定的多个条件求指定的列数据的累加和。,其他数据库函数,平均值函数DAVERAGE最大值函数DMAX最小值函数DMIN统计数值型数据的个数DCOUNT省略field字段，对数据清单区求满足条件的个数统计个数DCOUNTA省略field字段，对数据清单区求满足条件的个数,Excel函数,数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数,偿还函数PMT,PMT(rate,nper,pv,fv,type)功能：基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。rate 贷款利率。nper 该项贷款的付款总数。pv 现值，或一系列未来付款的当前值的累积和，也称为本金。fv 为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。type 0或者省略表示期末，1表示期初。例子：PMT(5.76%/12,24,200000)=￥-8,842.51,可贷款函数PV,PV(rate,nper,pmt,fv,type)返回现值。例子：月偿还能力200万，利率6%，贷12个月，求可贷款金额：PV(6%/12,12,200)=￥-2,323.79,未来值函数,FV(rate,nper,pmt,pv,type)功能：基于固定利率及等额分期付款方式，返回某项投资的未来值。,返回投资的总期数函数NPER,NPER(rate,pmt,pv,fv,type)功能：基于固定利率及等额分期付款方式，返回某项投资的总期数。,利率函数RATE,RATE(nper,pmt,pv,fv,type,guess)返回年金的各期利率。函数 RATE 通过迭代法计算得出，并且可能无解或有多个解。如果在进行 20 次迭代计算后，函数 RATE 的相邻两次结果没有收敛于 0.0000001，函数 RATE 将返回错误值#NUM!。,Excel函数,数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数,日期函数,DAY(serial_number):一个月中的哪一天MONTH(serial_number)：月份YEAR(serial_number)：年例子：DAY(2008/10/1)=1MONTH(2008年10月1日)=10YEAR(2008-10-1)=2008,日期函数,NOW()：返回当前计算机系统的日期和时间。TODAY()：返回计算机系统的日期。例子：NOW()=2023/7/10 16:41TODAY()=2023/7/10建立日期函数DATE(year,month,day)DATE(2008,8,8)=2008-8-8,Excel函数,数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数,按列查找函数VLOOKUP,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)lookup_value：查找值table_array：数据区col_index_num：为 数据区中待返回的匹配值的列序号range_lookup：如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值#N/A。,按列查找函数VLOOKUP,如果 range_lookup 为 TRUE，则 table_array 的第一列中的数值必须按升序排列；如果 range_lookup 为 FALSE，table_array 不必进行排序。通过在“数据”菜单中的“排序”中选择“升序”，可将数值按升序排列。Table_array 的第一列中的数值可以为文本、数字或逻辑值。文本不区分大小写。,按行查找函数HLOOKUP,HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)在数据区的首行查找指定的数值，并由此返回数据区当前列中指定行处的数值。,数组转置函数TRANSPOSE,TRANSPOSE(array)返回转置单元格区域，即将一行单元格区域转置成一列单元格区域。array 为需要进行转置的数组或工作表中的单元格区域。,Excel函数,数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数,文本函数（1）,字符串长度函数LEN(S)、LENB(S)截取子串函数MID(S,m,n)截取左子串函数LEFT(S,n)截取右子串函数RIGHT(S,n)删除首尾空格的函数TRIM(S)数值转文本函数TEXT(value,format_text)四舍五入转换文本函数FIXED(number,decimals,no_commas)文本转数值函数VALUE(S),文本函数（2）,大小写字母转换函数LOWER(S)UPPER(S)替换函数REPLACE(S1,m,n,S2)比较函数EXACT(S1,S2)查找函数FIND(sub,S,n)搜索函数SEARCH(sub,S,n)重复文本函数(S,n),本章小结,数学与三角函数统计函数逻辑函数数据库函数财务函数日期函数查找和引用函数文本函数,本章作业,预习第八章 数据分析作业教材P120 习题教材P171177 上机实验（函数应用）,