EXCEL函数培训课件

《Excel函数的基础及应用》第一讲2010-04-10操作函数图表分析VBAEXCEL基础知识讲解实例操作Excel基础及函数的应用函数基础及应用1Excel基础及函数的应用EXCEL的基本操作EXCEL工作簿及工作环境一、操作2EXCEL中表格的美化及数据的处理31、工作簿与工作表的关系2、工具的用途3、工作环境的设定1EXCEL工作簿及工作环境1、数据录入技巧2、数据、表格格式化3、自动求和自动填充EXCEL的基本操作24、单元格的编辑和置换5、复制、选择性粘贴6、查找、替换的强大功能7、页面设置、打印的方式8、学会盲打和使用快捷键1、合理的使用格式工具2、常规运算符：“+”“-”“*”“/”3、比较运算符：“”“”“=”“”4、使用公式对数值进行计算5、绝对、相对引用EXCEL中表格的美化及数据的处理3《Excel函数的基础及应用》第二讲2010-051、函数的定义2、函数的应用3、案例的分析Excel基础及函数的应用函数二、函数§运用基本函数实现对数据、单元格的操作§运用函数Count、Countif等进行复杂数量统计§运用函数Sumif、Sumproduct、数组公式等进行有条件的求和§运用函数Vlookup、Hlookup进行高级数据查询§Datedif、Mid等主要日期和文本函数的应用§函数嵌套的计算、引用函数与查询函数结合使用学习目标Excel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。例如，SUM函数对单元格或单元格区域进行加法运算。什么是函数？Excel基础及函数的应用函数的定义逻辑函数:AND、OR、NOT、TRUE、FALSE、IF函数Excel基础及函数的应用认识逻辑函数这三个函数都用来返回参数逻辑值AND、OR、NOT表达式：AND(logical1,logical2,...)OR(logical1,logical2,...)NOT(logical1,logical2,...)EXCEL逻辑函数AND函数：所有参数的逻辑值为真时返回TRUE；只要一个参数的逻辑值为假即返回FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。EXCEL逻辑函数定义OR函数：OR函数指在其参数组中，任何一个参数逻辑值为TRUE，即返回TRUE,它与AND函数的区别在于，AND函数要求所有函数逻辑值均为真，结果方为真。而OR函数仅需其中任何一个为真即可为真EXCEL逻辑函数定义NOT函数：NOT函数用于对参数值求反。当要确保一个值不等于某一特定值时，可以使用NOT函数。简言之，就是当参数值为TRUE时，NOT函数返回的结果恰与之相反，结果为FALSEEXCEL逻辑函数定义TRUE、FALSE这两个函数用来返回参数的逻辑值，可以直接在单元格或公式中键入值TRUE或者FALSE。EXCEL逻辑函数定义IF函数IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数IF对数值和公式进行条件检测语法：IF(logical_test,value_if_true,value_if_false)。说明：Logical_test表示计算结果为TRUE或FALSE的任意值或表达式。本参数可使用任何比较运算符EXCEL逻辑函数例：Excel基础及函数的应用假如单元格A1、B1、C1中是各科成绩，如果有一科成绩不到60分，则不录取。公式为？=IF（OR（A160，B160，C160)”不录取”,”录取“）♂常用函数♂重要函数Excel基础及函数的应用IfSUM--SUMIFCount--countaCOUNTIFAverage（求平均值）Left-Right-MidMax-MinRANK(排位)FREQUENCY(频率分布）Excel基础及函数的应用常用函数表达式IF(logical_test,value_if_true,value_if_false)IF(逻辑条件,真值,假值)说明逻辑条件:运算结果为True或者False的表达式或值真值:Logical_test为True时返回的值假值:Logical_test为False时返回的值简言之，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果IF函数Excel基础及函数的应用IF函数公式F1=IF(SUM(A1:A5),SUM(A1:A5),“”)解析：如果SUM(A1:E1)不等于零，则在F1单元格中显示SUM(A1:E1)的结果，否则显示字符串。几点说明：（1）SUM(C5:F5)不等于零的正规写法是SUM(C5:F5)0，在EXCEL中可以省略0；（2）表示字符串的内容为空，因此执行的结果是在单元格中不显示任何字符。ABCDEF11223344245例解一EXCEL函数示例IF函数公式E61=IF(D61=300,预算内,超出预算)例解二EXCEL函数示例IF函数方法一：G26=IF(F26=180,IF(F26=240,“优秀”，“合格”）,“不合格”）例解三EXCEL函数示例IF函数方法二：G26=IF(F26=240,“优秀”，IF(F26=180,“合格”，“不合格”))嵌套使用：If函数可以嵌套使用七层,利用True、False构造复杂的关系！IF函数EXCEL函数实例操作If函数Excel基础及函数的应用sum函数语法:SUM(number1,number2,...)参数:number1，number2，...为1到30个需要求和的参数。定义：返回某一单元格区域中数字、逻辑值及数字的文本表达式之和Excel基础及函数的应用说明：直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算。如果参数为数组或引用，只有其中的数字将被计算。数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。如果参数为错误值或为不能转换成数字的文本，将会导致错误。Excel基础及函数的应用示例：SUM(5,3)等于8。SUM(“6,3,TRUE)等于10，因为文本值被转换成数字，而逻辑值TRUE被转换成数字1。不同于前例，如果A1包含3，而B1包含TRUE，则：SUM(A1,B1,2)等于2，因为对非数值型的值的引用不能被转换成数值。如果单元格A2：E2包含5，15，30，40和50，则：SUM(A2:C2)等于50；SUM(B2:E2,15)等于150。Excel基础及函数的应用定义：根据指定条件对若干单元格求和sumif函数语法SUMIF(range,criteria,sum_range)1）range为用于条件判断的单元格区域。2）criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、“32”、“32”或“李先春。3）sum_range是需要求和的实际单元格。Excel基础及函数的应用求出上图中李先春销量总和？例解一=sumif(B2:E7,”李先春“，C2:E7)结果=488Excel基础及函数的应用求出上图中大于10的数值总和？例解二sumif(C3:C7,=10)结果=33Excel基础及函数的应用实例操作sumif函数Excel基础及函数的应用Count表达式：Count(value1,value2,……)说明:计算连续或不连续区域的数字个数。参数value代表1-30个各种不同类型的数据，或单元格引用，但只对数字型数据进行计算。Excel基础及函数的应用COUNT已知：A1=0A2=2A3=“”A4=“#REEF”A5=Count(A1,A2,A3,A4,2)结果A6=3Excel基础及函数的应用CountA表达式：CountA(value1,value2,……)说明:计算参数表中任意类型数据的个数。或单元格区域中非空的单元格个数。参数value代表1-30个各种不同类型的数据，或单元格引用。Excel基础及函数的应用COUNTA已知：A1=0A2=2A3=“”A4=“#REEF”A5=“总量”A6=Count(A1,A2,A3,A4,A5,2)结果A6=6Excel基础及函数的应用COUNT与COUNTA的区别count只统计数字型单元格的个数,对于文字,错误值,逻辑值True不能统计,而counta可以,但对于空白单元格都不能统计.Excel基础及函数的应用实例操作Count函数Counta函数Excel基础及函数的应用countif函数语法：COUNTIF(range,criteria)说明：Range为需要计算其中满足条件的单元格数目的单元格区域，Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本（条件可以表示为79、“79”、“79”、“李先春”或B4）Excel基础及函数的应用上图中求出数据1中A开头的个数？例解一=countif(B2:B6,”A*”)结果=3Excel基础及函数的应用Left函数表达式：Left(Text,num_chars)说明Text:文本字符串num_chars:指定要提取的字符数注意:num_chars必须大于等于0如果n_cs大于文本长度,返回所有文本如果省略num_chars,默认为1Excel基础及函数的应用功能:从字符串左端取指定个数字符已知:A1=012141002A2=Left(A1,2)结果:A2=01例解一Left函数Excel基础及函数的应用right函数功能:从字符串右端取指定个数字符表达式right(Text,num_chars)说明Text:文本字符串num_chars:指定要提取的字符数Excel基础及函数的应用已知:A1=012141002A2=right(A1,2)结果:A2=02例解一right函数Excel基础及函数的应用Right函数与Left函数用法相同,只是意义不同.Excel基础及函数的应用mid函数表达式：Mid(Text，start_num，num_chars)Text:文本字符串start_num:提取的第一个字符的位置num_chars:返回的字符的个数,不能是负数（注：start_num大于文本总长度,返回空，start_num+num_chars大于文本总长度,返回到文本末的字符串，start_num小于1,返回错误值#value!）Excel基础及函数的应用已知：A1=002141001A2=mid(A1,3,3)结果A2=214mid函数功能：从一个文本字符串的指定位置开始，截取指定数目的字符。字符截取Excel基础及函数的应用Left-Right-Mid实例操作Excel基础及函数的应用Max-Min函数表达式：Max(number1,number2,......)MAX求出一组数中的最大值。数据计算MIN求出一组数中的最小值。数据计算Excel基础及函数的应用已知：A1=6,A2=4,A3=8如果：A4=Max(A1,A2,A3,2)结果：A4=8Max-Min函数如果：A4=MIN(A1,A2,A3,2)结果：A4=2Excel基础及函数的应用学会使用嵌套的关系联结符&对有价值数据的提取和组合实例操作Excel基础及函数的应用Max-Min函数Rank函数Excel基础及函数的应用表达式：rank(Number,ref,order)使用功能：求指定数字在一列数字中的排位（排名次）说明：Number代表需要求排位的数据（通常使用单元格的相对引用），ref代表Number所在的一组数据（通常使用单元格区域的绝对引用）Order为一数字，指明排位的方式（0或省略为降序，大于0则为升序）。求A2在$A$