EXCEL2010函数教案ppt课件.ppt

,EXCEL2010函数,一、为什么要学习函数,二、成为函数高手的捷径,积极的心态,循序渐进,EXCEL水平划分,4,三、函数的基本概念,公式,对数据执行操作和运算的等式,函数,预定义的计算公式使用一些称为参数的特定数值按特定的顺序或结构进行计算,公式：=(A1+A2+A3+A100)/100,函数：=AVERAGE(A1：A100),四、函数的构成,构成：等号+函数名+括号+参数,=F(X),等号,括号,函数名,参数,参数形式：,=SUM(A1,C3:C6,E5/3,SUM(F1:F9),365),单元格引用,表达式,函数,常量,参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。参数不仅仅是常量、公式或函数，还可以是数组、单元格引用等,课程目录,一、运算符,二、常见运算产生错误的原因,一、“#VALUE!”,四、“#REF!”,二、“#DIV/O!”,三、“#NAME！”,当使用错误的参数或运算对象类型时，将产生错误值“#VALUE!”。,删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。,当公式被零除时，将会产生错误值“#DIV/O!”。,公式错误，无法自动更正,当数值对函数或公式不可用时，出现该错误,五、“#NA!”,逻辑函数,TRUE/FALSE:用来返回参数中的逻辑值。可以直接在单元格中输入TRUE或者FALSE，通常不单独使用。引申为是和否、条件成立和不成立、真和假、在公式中还可以转换为0和1。在=-*/运算中false相当于0，true相当于1,逻辑函数,AND:其逻辑表达式为：AND(Logical1,Logical2)Logical1、Logical2.是1到255个结果为TURE或FALSE的检测条件，检测内容可以使逻辑值、数组或引用当所有检测条件为真时，AND函数结果为TURE只要有一个条件为假时，AND函数结果即为FALSE,OR:其逻辑表达式为：OR(Logical1,Logical2)Logical1、Logical2.是1到255个结果为TURE或FALSE的检测条件，检测内容可以使逻辑值、数组或引用只要一个逻辑值为真，OR函数即为TURE只有所有逻辑值都为假时，OR函数才为FALSE,AND/OR通常与IF嵌套使用,逻辑函数,IF:判断真假函数,用途:根据指定的条件来判断其“真”（TRUE）、“假”（FALSE），根据逻辑计算的真假值，从而返回相应的内容。语法：IF(logical_test,value_if_true,value_if_false)提示：在IF函数书写时，如果有文本出现，必须使用英文状态双引号表示引用。该规则同样适用于Excel所有函数IF嵌套函数使用所谓嵌套函数，就是指在某些情况下，可能需要将某函数作为另一函数的参数使用,SUMIF:带条件的求和函数,用途:根据一定的条件进行求和。语法：SUMIF（条件区域，条件，求和区域）例如：分别统计金卡和普卡的消费金额,统计函数,13,SUMIFS:带条件的求和函数,用途:根据一定的条件进行求和。语法：SUMIF（求和区域，条件区域，条件.）例如：分别统计一期和二期金卡的消费金额,统计函数,14,统计函数,COUNT:统计数字个数,用途:统计数值型的数字计算进去；但是错误值、空值、逻辑值、文字则被忽略语法：COUNT(value1,value2,.),COUNTA:统计非空单元格个数,用途:对“（）”内参数的个数进行统计，参数可以是单元格、单元格区域、数字、字符等，统计只按个数统计，内容不影响语法：COUNTA(value1,value2,.),统计函数,COUNTIF:条件统计,用途:用来对搜索指定条件的单元格的个数，即只求和被满足条件的单元格个数。语法：COUNTIF(range,criteria)Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。,COUNTIFS:满足多重条件的单元格数目,用途:计算某个区域中多重条件的单元格的数目语法：COUNTIFS（条件区域，条件.）例如：统计一期消费金额大于1000的笔数,统计函数,17,统计函数,AVERAGEIF:条件求平均值,用途:求某个区域内满足给定条件指定的单元格的平均值（算术平均值）语法：=averageif(range,criteria,average_range)=Averageif（条件区，条件，平均值区域）参数Range表示：条件区第二个参数条件所在的范围。参数Criteria表示：条件是用来定义计算平均值的单元格。参数Average_range：平均值区域参与计算平均值的单元格。（这参数可以省略，当条件区和平均值区域一致时）,统计函数,AVERAGEIFS:多条件计算平均数,用途:回满足多重条件的所有单元格的平均值（算术平均值）语法：averageifs(average_range,criteria_range1,criteria1,crileria_range2,criteria2,.)参数average_range表示：求平均值区域参与计算平均值的单元格。参数criteria_range,criteria_range2，.表示：条件区criteria条件所在的范围。参数criteria1,criteria2，.表示：条件是用来定义计算平均值的单元格。（形式可以是数字、表达式、单元格引用或文本的条件。,统计函数,RANK:排名统计,用途:计算一个数字在一组数字列表中的排位语法：RANK(number,ref,order)1、Number为需要找到排位的数字。2、Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。3、Order为一数字，指明排位的方式。如果order为0（零）或省略，MicrosoftExcel对数字的排位是按照降序方式进行排列。如果order不为零，MicrosoftExcel对数字的排位则按照升序进行排列。,MATCH:匹配函数,用途:返回指定数值在指定数组区域中的位置。语法：MATCH(lookup_value,lookup_array,match_type)lookup_value：需要在数据表（lookup_array）中查找的值。可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。可以包含通配符、星号(*)和问号(?)。星号可以匹配任何字符序列；问号可以匹配单个字符。lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据，引用的查找区域是一维数组。match_type：表示查询的指定方式，用数字-1、0或者1表示，match_type省略相当于match_type为1的情况。,查找函数,21,VLOOKUP:寻找并参照函数,用途:给定一个查找目标，它就能指定的查找区域中查找并且返回想要查找到的值。语法：VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊查找)相似函数：HLOOKUP备注：VLOOKUP进行纵向查找，HLOOKUP进行横向查找。,查找函数,22,INDEX,用途:返回数据表区域的值语法：=index（数据表区域，行数，列数）备注：通常与MATCH匹配试用,查找函数,23,SEARCH:寻找函数,用途:返回一个指定字符或文本字符串在字符串中第一次出现的位置,从左到右查找，忽略英文字母的大小写语法：search（find_text，within_text，start_num）=search（要查找的字符，字符所在的文本，从第几个字符开始查找）第三个参数可以省略（默认从第一个开始查找）。search函数的参数find_text可以使用通配符“*”，“？”。如果参数find_text就是问号或星号，则必须在这两个符号前加上“”符号,查找函数,24,INDIRECT,用途:引用，并显示其内容语法：=INDIRECT(ref_text,a1)引用的两中形式。一种加引号，一种不加引号。=INDIRECT(A1)加引号，文本引用=INDIRECT(A1)不加引号，地址引用,引用函数,25,ROW,用途:返回所选择的某一个单元格的行数语法：=row(reference)如果省略reference，则默认返回row函数所在单元格的行数,引用函数,COLUMN,用途:返回所选择的某一个单元格的列数语法：=COLUMN(reference)如果省略reference，则默认返回column函数所在单元格的列数,26,DATEDIF,用途:计算两个日期之间的天数、月数或年数。这个函数是一个特殊函数，在函数清单中找不到，在帮助信息中也找不到。语法：DATEDIF(开始日期,结束日期,单位)单位意义“Y”时间段中的总年数“M”时间段中的总月数“D”时间段中的总天数“MD”两日期中天数的差，忽略日期数据中的年和月“YM”两日期中月数的差，忽略日期数据中的年和日“YD”两日期中天数的差，忽略日期数据中的年,日期函数,27,EOMONTH,用途:返回指定日期往前或往后几个月的特定月份的月底日期。例：2007年4月12日之后3个月的月末日期：=EOMONTH(“2007-4-12”,3)，为2007-7-312007年4月12日之前5个月的月末日期：=EDATE(“2007-4-12”,-5)，为2006-11-30计算应付账款的到期日：如果一笔应付款的到期日为自交易日起满3个月后的下一个月的5号，比如交易日为2006年11月20日，满3个月后下个月5号就是2007-3-5：=EOMONTH(2006-11-20,3-(DAY(A1)=5)+5,日期函数,28,EDATE,用途:返回指定日期往前或往后几个月的日期。例：2007年4月12日之后3个月的日期：=EDATE(“2007-4-12”,3)，为2007-7-122007年4月12日之前3个月的日期：=EDATE(“2007-4-12”,-3)，为2007-1-12计算应付账款的到期日：如果一笔应付款的到期日为自交易日起3个月的那一天，比如交易日为2006年11月30日，满3个月后为2007-2-28：=EDATE(“2006-11-30”,3)，为2007-2-28,日期函数,29,字符函数,Mid:截取字符串函数,用途:Mid返回文本串中从指定位置开始的特定数目的字符，该数目可以由用户指定.语法：Mid(文本串，开始位置，返回字符的个数)参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。例如：A1单元格有身份证号码使用Mid(A1,7,4)得到结果”1982”,30,字符函数,Left/Right:截取字符串函数,用途:根据指定的字符数返回文本串中的第一个或前几个字符。语法：Left(文本，返回的字符数)Right(文本，返回的字符数)参数：返回的字符数必须大于或等于0实例：返回客户姓名中的姓氏A1单元格字符串“张三”,使用Left(A1,1)得到结果“张”,31,字符函数,LEN:计算字符串长度,用途:LEN返回文本的字符数语法：LEN(文本)参数：此函数用于双字节字符，且空格也将作为字符进行统计。例如：检查身份证号码是否输入了18位。A1单元格有身份证号码使用LEN(A1)得到结果18类似的函数LENB，返回单字节字符。例如：LENB(“ABC”)返回3，LENB(“张三”)返回4,32,字符函数,SUBSTITUTE:替换指定文本,用途:在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定文本，可以使用SUBSTITUTE；如果需要在某一字符串中替换指定位置处的任意文本，就应该使用函数REPLACE。语法：SUBSTITUTE(需要替换其部分字符的文本，需被替换旧文本，用于替换的文本，第几次出现的旧文本)参数：如果不输入第四个参数将替换所有旧文本。例如：A1单元格有客户编码”G000G001”,将第二个”G”替换成”0”.使用SUBSTITUTE(A1,G,“0,2)得到结果”G0000001”使用SUBSTITUTE(A1,G,“0)得到结果”00000001”,33,字符函数,TEXT:,用途:将数值转换为按指定数字格式表示的文本语法：TEXT(value,format_text)例：TEXT(12345.6687,￥0.00)=￥12345.6720150601=-TEXT(20150601,0!/00!/00),34,求余函数,MOD:,用途:两个数值表达式作除法运算后的余数语法：mod(nExp1,nExp2)例：MOD(7，2)=1,35,数组公式,数组公式在计算时，把多个数据做为一个整体进行计算，其结果是返回多个数值。编辑栏可以看到以“”括起来的公式就是数组公式。普通公式则只是返回单个数值，即使在引用时引用了多个数值，但实际需要返回的只是其中的一个。,36,数组求和,数组求和：给定的机组数组中，将数组间对应的元素相乘，并返回乘积之和。,37,常用数组,用途：VLOOKUP从右至左匹配使用,IF1,0,比如这个公式=VLOOKUP(U3,IF(1,0,S3:S15,Q3:Q15),2,0)1,0常数数组1和0在逻辑中不为0的值都是真值,为0的值是假值因此在IF函数中1返回真值的部分S3:S15,0返回假值的部份Q3:Q15,形成一个新的数组,这个数组由S3:S15和Q3:Q15各单元格实际内容组成IF(1，S3:S15,Q3:Q15),返回S3：S15；IF(0，S3:S15,Q3:Q15),返回Q3：Q15。IF(1,0,S3:S15,Q3:Q15)，返回区间(S3:S15&Q3:Q15)这个公式的作用是让排在序列前面的Q列,转成第二列即返回值列,让S列的内容成为待查找的首列,38,数组统计之不重复项个数,=SU