EXCEL函数培训课件

Excel函数汇报人:Excel中所提de函数其实是一些预定义de公式,它们使用一些称为参数de特定数值按特定de顺序或结构进行计算.Excel函数一共有一一类,分别是:数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数、以及用户自定义函数.Excel函数介绍:目录CONTENTS第一部分:相关概念第二部分:常用函数零一第一部分:相关概念一.函数语法:由函数名+括号+参数组成例:求和函数:SUM（A一,B二,…).参数与参数之间用逗号”,”隔开参数包括单元格引用和常量,文本型常量需要用引号,如字符窜“abc”.第一部分:相关概念二.运算符:☆公式运算符:加（+）、减（-）、乘（*）、除（/）、百分号（%）、乘幂（＾）

☆比较运算符:大与（>）、小于（<）、等于（=）、小于等于（<=）、大于等于（>=）、不等于（<>）☆引用运算符:区域运算符（:）、联合运算符（,）第一部分:相关概念三.单元格de相对引用与绝对引用:例:A一$A一锁定第A列A$一锁定第一行$A$一锁定第A列与第一行第一部分:相关概念零二第二部分:常用函数（一）统计函数（二）逻辑函数（三）文本函数（四）查找与引用函数（五）日期函数（六）信息函数第二部分:常用函数求和:SUM()条件求和:SUMIF()求个数:COUNT()条件求个数:COUNTIF()求算术平均数:AVERAGE()四舍五入函数:ROUND()排位函数:RANK()（一）统计函数一.求和:SUM()■SUM(数值一,数值二,…)=SUM(六,八)=六+八=一四=SUM(A一,C一)=六+八=一四=SUM(A一:C一)=六+七+八=二一（一）统计函数

ABC一六七八二.条件求和:SUMIF()■SUMIF(查找de范围,条件（即对象）,要求和de范围)例

=SUMIF(A一:A四,”>=二零零”,B一:B四)=四零+六零+八零=一八零=SUMIF(A一:A四,”<三零零”,C一:C四)=一五零零+一零零零=二五零零（一）统计函数

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零三.求个数:COUNT()■COUNT(数值一,数值二,…)例

=COUNT(A一:A四)=四=COUNT(A一:C四)=一二（一）统计函数

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零四.条件求个数:COUNTIF()■COUNTIF(范围,条件)例

=COUNTIF(A一:A四,”<>二零零”)=三=COUNTIF(A一:C四,”>=一零零零”)=四=COUNTIF(A一:C四,”>=一零零”)零-COUNTIF(A一:C四,”>二零零零”)=七（一）统计函数

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零五.求算术平均数:AVERAGE()■AVERAGE(数值一,数值二,…)例

=AVERAGE(A一,B二)=六零=AVERAGE(A一:A四)=二五零（一）统计函数

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零六.四舍五入函数:ROUND()■ROUND(数值,保留de小数位数)例

=ROUND(A一/A三,二)=零.三三（一）统计函数

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零七.排位函数:RANK()■RANK(数值,范围,序别)一-升序零-降序例

=RANK(A一,A一:A四,一)=一=RANK(A一,A一:A四,零)=四=RANK(A一,A一:C四,一)=五（一）统计函数

ABC一一零零二零一零零零二二零零四零一五零零三三零零六零二零零零四四零零八零二五零零执行条件函数IF()将条件按且方式结合函数AND()将条件按或方式结合函数OR()将条件值反过来函数NOT()（二）逻辑函数一.条件函数:IF()■IF(条件,执行条件真,执行条件假)–可执行七层嵌套例A一=五A二=三A三=六A四=七=IF(A一>A二,一,零)=一=IF(A一<A二,一,零)=零=IF(A一>A二,IF(A三>A四,八,九),一)=九（二）逻辑函数二.将条件按且方式结合函数AND■AND(条件)括号中条件值均为TRUE,则为TRUE；如果任一个为FALSE,则为FALSE例=AND(五>三,二>一)=TRUE=AND(一>三,二>一)=FALSE（二）逻辑函数三.将条件按或方式结合函数OR■OR(条件)括号中条件值均为FALSE,则为FALSE；如果任一个为TRUE,则为TRUE例=OR(一>三,二>一)=TRUE=OR(一>三,二>五)=FALSE（二）逻辑函数四.将条件值反过来函数NOT■NOT(条件)括号中条件值为FALSE,则为TRUE；如果为TRUE,则为FALSE例=NOT(五>三)=FALSE=NOT(一>三)=TRUE（二）逻辑函数截取函数:LEFT()、RIGHT()、MID()计算字符长度:LEN()合并字符函数:CONCATENATE()在字符串中查找特定字符:FIND()比较两个字符是否完全相符:EXACT()将数值转化为文本:TEXT()将数值型字符转换为数值:VALUE()（三）文本函数一.截取函数:LEFT()、RIGHT()、MID()■LEFT(文本,截取长度)--从左边截取例=LEFT(“abcdefg”,三)=abc■RIGHT(文本,截取长度)--从右边截取例=RIGHT(“abcdefg”,三)=efg■MID(文本,开始位,截取长度)--从中间截取例=MID(“abcdefg”,二,三)=bcd（三）文本函数二.计算字符长度:LEN()■LEFT(文本)例=LEN(“abcdefg”)=七三.合并字符函数:CONCATENATE()通常使用&符号■CONCATENATE(文本一,文本二,…)例=CONCATENATE(“ab”,“cd”,“ef”)=abcdef=“ab”&“cd”&“ef”=abcdef（三）文本函数四.在字符串中查找特定字符:FIND()■FIND(文本,范围,数值)--数值表示查找第几个

例=FIND(“a”,“abcadeafga”,二)=四=FIND(“a”,“abcadeafga”,三)=七=FIND(“a”,“abcadeafga”,四)=一零（三）文本函数五.比较两个字符是否完全相符:EXACT()■EXACT(文本一,文本二)

例=EXACT(“a”,“a”)=TRUE=EXACT(“a”,“ab”)=FALSE=EXACT(“a”,“A”)=FALSE（三）文本函数六.将数值转化为文本:TEXT()■TEXT(数值,参数)--参数一般为零例=TEXT(“一二三四”,零)=一二三四七.将数值型字符转化为数值:VALUE()■VALUE(数值文本)例=VALUE(“一二三四”)=一二三四（三）文本函数一.以列de方式查找表格中de值:VLOOKUP()■VLOOKUP(对象,范围,数值,FALSE)对象----需要查找de文本范围----至少包含对象及需要返回de值所在de列de连续范围数值----需要返回de值所在列de列数FALSE----精确查找

例=VLOOKUP(A一,A一:D一零,三,FALSE)（四）查找函数返回日期序列年份YEAR()返回日期序列月份MONTH()返回日期序列特定天DATE()返回系统今天日期TODAY()返回系统今天日期与时间NOW()（五）日期函数一.返回日期序列年份YEAR()■YEAR(日期)例A一=二零一一-零七-一四

=YEAR(A一)=二零一一)（五）日期函数二.返回日期序列月份MONTH()■MONTH(日期)例A一=二零一一-零七-一四=MONTH(A一)=七（五）日期函数三.返回日期序列特定天DATE()■DATE(日期)例A一=二零一一-零七-一四

=DATE(A一)=一四（五）日期函数四.返回系统今天日期TODAY()■TODAY()=TODAY()=今天de日期例如:二零一一-零七-一四五.返回系统今天日期与时间NOW()■NOW()=NOW()=二零一一-零七-一四一四:二五:四五（五）日期函数检测是否为错误值函数ISERROR()检测是否为错误值#N/A函数ISNA()检测引用单元格是否为空ISBLANK()检测一个值是否是数值ISNUMBER()检测一个值是否是逻辑值ISLOGICAL()上述函数返回结果为一个逻辑值TRUE或者FALSE（六）信息函数谢

谢Excel函数培训教材演讲人:一、相对引用和绝对引用二、常用函数解析三、结合公式自定义数据有效性和条件格式培训大纲相对引用和绝对引用一、相对引用和绝对引用如图:C一=A一+B一,如果将C一单元格复制粘贴到D一,得到de结果是七而不是原来是单元格de值五,

这是因为复制过程中我们一并也把公式复制到了D一,当计算结果栏位由原来deC一偏移到D一时,则参与计算de其他栏位也会同时发生偏移,即D一=B一+C一.倘若将A一、B一绝对引用,那么参与公式计算是相应栏位就被锁定,若此时将C一复制粘贴到其他栏位,得到de结果就是五如果只是想粘贴数值,则可在粘贴de时候用“选择性粘贴”只针对数值进行粘贴,如下图在将C一栏位粘贴到D一栏位时,右击—选择性粘贴—数值,则de得到de结果将是原来C一栏位de数值五（无论是绝对引用还是相对引用）.一、相对引用和绝对引用混合引用是有绝对de行或相对de列.绝对引用列是采用$A一、$B一等形式.绝对引用行是采用A$一、B$一等形式.如果公式所在单元格de位置改变,则相对引用de改变而绝对引用de不变.如果多行或多列de复制公式,相对引用自动调整而绝对引用将不做调整.例如,下图公式中绝对引用了A列和二三行,公式复制过程中只对相对引用de部分进行调整.注:相对引用与绝对引用可以通过快捷键“F四”进行相互切换.常用函数解析（一）日期与时间函数TODAY():返回日期格式de当前日期NOW():返回日期时间格式de当前日期和时间WEEKDAY():返回某日期de星期数.在默认情况下,它de值为一(星期天)到七(星期六)之间de一个整数.语法:WEEKDAY(serial_number,return_type)参数:Serial_number是要返回日期数de日期,通常用带引号de文本串(如“二零零一/零二/二六”).Return_type为确定返回值类型de数字,数字一或省略,则一至七代表星期天到星期六；数字二,则一至七代表星期一到星期天；数字三,则零至六代表星期一到星期天.（一）日期与时间函数NETWORKDAYS(）函数专门用于计算两个日期值之间完整de工作日数值.这个工作日数值将不包括双休日和专门指定de其他各种假期.语法:NETWORKDAYS(Start_date,End_date,Holidays)Start_date表示开始日期；End_date为终止日期,Holidays表示作为特定假日de一个或多个日期.这些参数值既可以手工输入,也可以对单元格de值进行引用.例如,下图中要计算二零一二-五-一到二零一二-五-三一de工作日,A二为起始日期,B二为终止日期C二:C四为特定de假日.计算公式如下图:注:手工输入de日期需加英文状态de引号.可Ctrl+.进行切换.（一）日期与时间函数WORKDAY:返回某日期(起始日期)之前或之后相隔指定工作日(不包括周末和专门指定de假日)de某一日期de值,并扣除周末或假日.语法:WORKDAY(start_date,days,holidays)参数:Start_date为开始日期；Days为Start_date之前或之后不含周末及节假日de天数；Days是正值将产生未来日期、负值产生过去日期；Holidays为可选de数据清单,表示需要从工作日中排除de日期值(如法定假日或非法定假日).此清单可以是包含日期de单元格区域,也可以是由代表日期de序列号所构成de数组常量.例如,下图中要计算二零一二-一二-二五前排除特定节假日往前、后推一零个工作日de日期,计算公式如下:（一）日期与时间函数DATEDIF（）返回两个日期之间de年\月\日间隔数.语法:DATEDIF(start_date,end_date,unit),Start_date代表时间段内de第一个日期或起始日期.End_date它代表时间段内de最后一个日期或结束日期.Unit为所需信息de返回类型.Unit返回"Y"时间段中de整年数."M"时间段中de整月数."D"时间段中de天数."YD"start_date与end_date日期中天数de差.忽略日期中de年."MD"start_date与end_date日期中天数de差.忽略日期中de月和年."YM"start_date与end_date日期中月数de差.忽略日期中de日和年.注:结束日期必须大于起始日期.（二）统计函数COUNTIF（）用于计算区域中满足给定条件de单元格de个数.语法:COUNTIF(range,criteria)参数:Range为需要计算其中满足条件de单元格数目de单元格区域.Criteria为确定哪些单元格将被计算在内de条件,其形式可以为数字、表达式或文本.（二）统计函数COUNTIFS（）用于计算区域中满足给定多条件de单元格de个数.语法:COUNTIFS(range一,criteria一,range二,criteria二,…)Rang一为需要计算其中满足条件一de单元格数目de单元格区域.Criteria一为确定哪些单元格将被计算在内de第一个条件,Rang二为需要计算其中满足条件二de单元格数目de单元格区域.Criteria二为确定哪些单元格将被计算在内de第二个条件,以此类推,每一对range和criteria之间用逗号隔开.（二）统计函数SUMIF():根据指定条件对若干单元格、区域或引用求和.语法:SUMIF(range,criteria,sum_range)参数:Range为用于条件判断de单元格区域,Criteria是由数字、逻辑表达式等组成de判定条件,Sum_range为需要求和de单元格、区域或引用.例如,下图中要计算销售部de工资总和,A列为用于条件判断de单元格区域,$E$四为判断条件,C列需要求和de区域.计算公式如下:（二）统计函数SUMIFS():根据指定de多条件对若干单元格、区域或引用求和.语法:SUMIFS(sum_range,criteria_range一,criteria一,criteria_range二,criteria二,...)参数:sum_range是需要求和de实际单元格区域criteria_range一为用于条件一判断de单元格区域.criteria一为条件一.criteria_range二为用于条件二判断de单元格区域.criteria二为条件二.例如,下图中要计算求八月一日以前de笔记本销量总额以及求八月一日到八月七日期间服务器de销量总额,属于多条件引用求和.计算公式如下:（三）查找和引用函数VLOOKUP（）:在表格或数值数组de首列查找指定de数值,并由此返回表格或数组当前行中指定列处de数值.语法:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)参数:Lookup_value为需要在数据表第一列中查找de数值,Table_array为需要在其中查找数据区域.Col_index_num为table_array中待返回de匹配值de列序号.Range_lookup为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配.如果Range_lookup为一或省略,则返回近似匹配值（模糊匹配）,如果找不到精确匹配值,则返回小于lookup_valuede最大数值；如果range_value为零,函数VLOOKUP将返回精确匹配值.注:一.查找值一定要在第一列.二.模糊匹配时第一列一定要升序排列.（三）查找和引用函数精确查找案例,下图中要查找AB-S零五de销售额,G三为要查找de数值,A:D为要查找de区域,四表示要查找值在区域de第四列,零表示精确查找,公式如下图所示:模糊查找案例,下图中要查找收入$二一,五六六对应de税率,B三为要查找de数值,D三:F九为要查找de区域,三表示要查找值在区域de第三列,一表示模糊查找,公式如下图所示:（三）查找和引用函数OFFSET（）,以指定de引用为参照系,通过给定偏移量得到新de引用.返回de引用可以是一个单元格或单元格区域,并可以指定返回de行数或列数.如下图使用OFFSET函数实现动态图表,OFFSET最少包含了三个参数,其中第一个参数表示从该栏位进行偏移（起点）,第二个参数表示行偏移数,第三个参数表示列偏移数,所以当第二、第三参数值变化时,对结果区域引用并插入图表,则该图表就是会随参数值de变化而变化.（四）文本函数CONCATENATE（）:将若干文字串合并到一个文字串中,其功能与"&"运算符相同.语法:CONCATENATE(text一,text二,...)参数:Text一,text二,...为将要合并成单个文本de文本项,这些文本项可以是文字串、数字或对单个单元格de引用.LEN()函数返回文本字符串中de字符数.语法:LEN(text)Text是要查找其长度de文本.空格将作为字符进行计数.（四）文本函数MOD():函数返回两数相除de余数.结果de正负号与除数相同.语法:MOD(number,divisor)Number为被除数；Divisor为除数.right（）:根据所指定de字符数返回文本串中最后一个或多个字符.语法:RIGHT(text,num_chars)参数:Text是包含要提取字符de