EXCEL常用函数教程PPT讲课课件.ppt

Excel常用函数应用教程,EXCEL常用函数应用教程,EXCEL函数的基本知识 逻辑函数的应用 文本函数的应用 日期与时间函数 数学与统计函数 查询与引用函数 数据库函数的应用,Unit 1 EXCEL函数的基本知识,什么是函数 函数的参数和返回值 函数的地址符 函数的套用,Excel中所提的函数其实是一些预定义的公式，它们使用一 些称为参数的特定数值按特定的顺序或结构进行计算。 简单点说，函数是一组功能模块，使用函数能帮助你实现某个 功能。 举例：Sum函数可以实现数值的加法运算，max函数可以返回一组数的最大值。,1.什么是函数,2. 函数的参数及返回值,1.参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑数值、数组、 形如 #N/A 的错误值或单元格引用。函数的参数使用逗号隔开如 sum(A1,2,B2)，其中，A1,2,B2都是sum函数的参数，用逗号隔开 另外要注意的是，函数使用的参数所用的符号，如逗号，引号要全部在 英文输入状态下输入(半角符号)。 数字：1、2、100、10000 文本：astron、412398784737847（以文本格式存储的数字） 逻辑值：TRUE(真)或非零（一般用数字1表示）、FALSE(假)或0 比如13这个不等式的值为假。 单元格：比如A47、B16 数组：同一种格式存储的单元格区域,2.大部分函数都有一个返回值，返回你通过函数运算得出的结果,返回值可 以是一个数字，一个逻辑值，一个地址。另外要注意的是，要想要函数返 回正确的值，使用公式的单元格数值格式（快捷键ctrl+1）一定要正确。 举例： sum(1,2,3),我们的目的是得出1+2+3相加的和，sum函数返回的值是6 另外值得注意的是，对于函数的返回值如数值，它等同一个数值，而不要 把它认为是一个函数。它可以如其它数值一样使用加（+）、减（-）、 乘（*）、除（/）。 如上例中，可以A1=10-sum(1,2,3)，那么A1=4。在这里，因为sum（）函数 的返回值是一个数值，可以直接把它当数值用。,2. 函数的参数及返回值,3. 函数的地址符,在我们使用函数时，会引用一些地址作为参数，比如A47、B46等 EXCEL中有专门的地址引用符$。使用是$引用地址时，是绝对引用， 不使用$时，是相对引用。 举例： A47：相对引用，引用A47这个单元格。当使用这种类型的地址时， 我们使用拖拉功能将公式引用到其它单元格时，这个相对 地址会发生变化,3. 函数的地址符,$A$47:绝对引用。无论将公式引用到哪一个单元格，这个地址不会 改变，永远定格在A47这个单元格。 $A47和A$47:混合引用。只定格单元格的行或列，相对来说有点复杂， 不常用。 另外在引用地址时，冒号（：）在与单元格一起引用时，代表一片区域， 如 (A1:A47)代表的是从A1一直到A47的长条形区域，总共47个单元格， 如（A1:B3)代表以A1和B3为对角的一个方形区域，共9个单元格。 注：在引用地址符时，我们可以使用F4鍵在这些地址间进行切换,4. 函数的套用,函数的套用，也就是函数的组合使用，单个函数已经很厉害了， 组合起来的函数威力会更强大。 举例： IF(SUM(1,2,3)5,“100”,“1000”) 在这个函数中，逻辑函数IF套用了SUM函数，它把SUM函数返回 的结果作为参数，如果1，2，3之和小于5，返回100，反之，返 回1000.,Unit 2 逻辑函数的应用,IF函数 AND函数 OR函数 NOT函数,1. IF函数,IF在英文里是“假如，如果”的意思，顾名思义就是如果满足某个条件（为真） 则返回前面的一个值，不满足条件（为假），返回后面的一个值。 语法：IF(逻辑值，返回值1，返回值2)，其中逻辑值类型为真或假，另外返 回值是文本格式时，要用双引号，如果为数字，可以不用双引号。 举例：如何判断考试成绩是否及格？ IF(A184,“优秀”,“及格”)，这是IF函数 套用IF函数，如果A1小于60，返回不及格，另外如果大于等于85，则返回 优秀，否则返回及格。,2. AND函数,AND在英文里是“和，与”的意思,只有当所有条件都满足时，才返回 ture(真)，否则返回false(假),一般要与其它函数套用，比如IF函数。 语法：AND(条件1，条件2，条件30) 返回值：TURE或FALSE 举例： 1.and(A160,B280),只有A160,B280这三个条件 都满足时，才返回TRUE,否则FALSE. 2.IF（and(A160,B280),1，2），三个条件都满足时，返回1， 否则返回2.,3. OR函数,OR在英文里是“或，或者”的意思，只要满足任一条件就 返回ture(真)，否则返回false(假),一般要与其它函数套 用，比如IF函数。 语法：OR(条件1，条件2，条件30) 举例： or(A160,B280),当A160,B280 这三个条件有一个满足时，返回TRUE,否则FALSE.,4. not函数,not在英文里是“ 不，非”的意思，返回相反的值一般要 与其它函数套用，比如IF函数。 语法：not(条件) 举例： not(A160),当A160时，返回FALSE,否则TRUE.,Unit 3 文本函数的应用,比较函数excat与等号= 联结函数concatenate与联结号& Lower、upper、proper、trim函数 left、right、mid函数,1.比较函数excat与等号=,Excat是一个比较函数，等号=与之有相同的功能。返回值为逻辑值， 对于返回值为逻辑值的函数，一般与if等函数套用。 语法：excat(A1,B1) 举例： 1.excat(A1,B1),当A1与B1相同时，返回TRUE,否则FALSE. 与if函数套用，if(excat(A1,B1),”相同”，“不相同”)。 2.使用等号比较时，如单元格C1=（A1=B1），如果A1与B1 相同，C1=TRUE,否则C1=FALSE 与if函数套用，if(A1=B1,“相同”，“不相同”)。,2.联结函数concatenate与联结号&,concatenate是一个联结函数，联结号&与之有相同的功 能。 语法：concatenate(字符1,字符2，)，单独的文本要 使用双引号 举例： 1.concatenate(A1,B1，“tron”),如果A1=A，B1=s， 那么函数就会返回Astron 2. 如果C1=A1&B1&“tron”，那么C1=Astron,3. Lower、upper、proper、trim函数,Lower函数是将文本中的字母转换成小写字母。 Upper函数是将文本中的字母转换成大写字母 Proper函数是将文本中的字符串首字母转换成大写字母，其余字符转换成小写字母。 Trim函数是去除字符串中多余空白字符。 语法： XXXX(字符串），其中XXXX代表以上所有函数，它们的语法规则一样，字符串代表内容是字符的地址引用比如A1,B2,也可以是单独的字符串（要使用双引号如“astron”）,3. Lower、upper、proper、trim函数,举例： 假如A1=ASTRON,B1=astron,C1=dongguan astron, D1=DongGuan Astron lower(A1)=astron Upper(B1)=ASTRON Proper(C1)=Dongguan Astron Trim(D1)=DongGuan Astron,4.left、right、mid函数,Left函数是自字符串左边开始提取字符串中的字符 Right函数是自字符串左边开始提取字符串中的字符 Mid函数是从字符串指定位置开始提取字符串中的字符 语法： 1.Left(字符串，数值)，其中数值为要提取的字符数 2.Right(字符串，数值)，其中数值为要提取的字符数 3.Mid(字符串，数值1，数值2)，其中数值1代表要提取 的第一个字符的位置，数值2代表要提取的字符数。,4. left、right、mid函数,举例： 假如A1=Astron Left(A1,2)=As Right(A1,2)=on Mid(A1,2,2)=st,Unit 4 日期与时间函数,now、today函数 year、month、day、hour函数 dateif函数,1. now、today函数,now函数是提取当前系统日期和时间 Today函数是提取当前系统日期 语法： Now()参数为空 Today()参数为空 举例： 假定系统当前时间为 2013/03/30 9:48 1.如果A1=now(),那么A1= 2013/03/30 9:48 2.如果B1=today(),那么B1= 2013/03/30,2. year、month、day、hour函数,year函数作用是提取日期中的年份 month函数作用是提取日期中的月份 Day函数作用是提取日期中的日数 Hour函数作用是提取日期中的小时数 语法： XXX(A1)，其中XXX代表上面所有函数，它们语法规则一样，A1代表包含日期与 时间的单元格。 举例： 假定系统当前时间为 A1=2011/11/17 9:58 1. B1=year(A1),那么B1= 2011 2. C1=month(A1),那么C1=11 3. D1=day(A1)，那么D1=17 4. E1=hour(A1),那么E1=9,3. dateif函数,dateif函数作用是计算两个日期之间相差的年数、月数、日数，这个函数比较适用于统计出生年月 语法： Dateif(起始日期，结束日期，参数类型) 其中参数类型包括：“Y” 时间段中的整年数 ，“M” 时间段中的整月数，“D” 时间段中 的天数 举例： 假定系统当前日期为 2011/11/17 单元格A1中日期为2000/01/01,下面例子分别计算起始 日期与当前日期间相差的年数，月数，日数。 1.如果B1=DATEDIF(A1,TODAY(),“y“),那么B1=11 2.如果B1=DATEDIF(A1,TODAY(),“m“),那么B1=142 3.如果B1=DATEDIF(A1,TODAY(),“d“),那么B1=4338,Unit 5 数学与统计函数,Sum、average、abs、mod函数 sumif、countif函数 max、min、small、large、rank函数 value、isodd、iseven函数,1.Sum、average、abs、mod、power函数,Sum函数作用是求和 Average函数作用是求平均数 Abs函数作用是求绝对值 Mod函数作用是求余 Power函数作用是求n次方 语法： Sum(数值1，数值2) 其中数值可以直接是数字，也可以是包含数字的单元格，也可以是一片单元格区域。 Average语法规则同上 Abs（数值） Mod(数值1，数值2)，其中数值1是被除数，数值2是除数 Power(数值1，数值2)，其中数值1是数，数值2是多少次方 以上函数返回值皆为数字,1.Sum、average、abs、mod函数,举例： 假设 A1=1，B1=2,C1=-2 1.sum(A1,B1)=3 2.average(A1,B1)=1.5 3.abs(A1)=1,abs(C1)=2 4.mod(B1,A1)=0,mod(A1,B1)=1 5.power(B1,2)=4,另外（数字6上面的符号）也有这个功能，比如B13=8 另外要注意的是，想要减或者求负数，直接在单元格前加-号就可以了 比如上面例子中sum(A1,-B1)=-1,2.sumif、countif函数,Sumif函数作用是对区域内符合条件的单元格求和 Countif函数作用是统计区域内符合条件的单元格的数量 语法： Sumif(条件区域，条件，要计算和的区域) Countif(区域，条件) 举例：,2.sumif、countif函数,假设有如下表格 1.假如我们要计算1班所有人的手工课的总分，可以使用如下函数 SUMIF(A2:A5,“1”,G2:G5),在函数里面，前一区域A2:A5代表查寻条件“1”的查找区 域，中间的“1”表示查找的条件(要使用双引号)，后一区域G2:G5代表要加总的数字 区域 2.假如我们要统计手工课大于90分的人数，可以使用countif(G2:G5,”90”),前面代表 要查找的区域，后面表示查找条件，记住要使用双引号,3. max、min、small、large、rank函数,Max求一组数据的最大值 Min求一组数据的最小值 Small求一组数据的第N小的值 Large求一组数据的第N大的值 Rank作用是将一组数组排序 语法： Max(数字1，数字n)，参数可以是单独的数字，也可以是包含数字的单元格。 MIN函数语法同上。 Small(单元格区域，数字N)，其中前者为包含数字的单元格区域，后者为要查找的第N小的数字。,3. max、min、small、large、rank函数,large(单元格区域，数字N)，其中前者为包含数字的单元格区域，后者为要查找的第N大的数字。 Rank(需排序的数字，需排序的数组，排序类型)，其中排序类型为0时，按降序排列，不为0时，按升序排列。 举例： 假设A1,A2,A3,A4,A5分别为12，9，7，7，21 Max(A1:A5)=21 Min(A1:A5)=7 Small(A1:A5,3)=9 Large(A1:A5,5)=7,3. max、min、small、large、rank函数,举例 假设A1,A2,A3,A4,A5分别为12，9，7，7，21, 使用Rank（）函数 对这个数组进行排序，按降序(从大到小)排列,其中B列是排序后的序号 B1=RANK(A1,$A$1:$A$5,0),其中A1表示要排序的数字，A1:A5表示排序 的数组，0表示按降序排列，另外要注意的是，引用排序的数组时，我 们用了绝对地址引用$A$1:$A$5(这个可以用F4鍵在地圵符间进行切换), 这样我们在引用公式到B2,B3单元格时，这个地址区域不会变。,4.value函数,Value函数作用是将文本类型的数字转换成数字类型，常用于出生日期，银行卡等数据类型的转换。 语法： Value(文本)，其中文本必须为数字，只是存储类型为文本格式 举例： 假设A1=20111121，A2=5 如果B1=2011-VALUE(MID(A1,3,2)，那么B1=2000.我们知道，MID函数 的作用是取文本中的字符（第三章第4小节），在这里，我们从A1单元 格的第3个字符开始取，取2个字符，得到字符11，然后将用value函数 将其转换成数字类型11。最后与2011相减得2000,Unit 6 查询与引用函数,Vlookup,hlookup函数 Match,index函数,1.vlookup,hlookup函数,Vlookup函数用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。 Hlookup函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。 语法： Vlookup(要查找的值，查找的区域，要返回列的序号，查找类型) 其中要注意的问题： 1.要查找的值一定要位于查找区域的首列 2.查找的区域中不要有隐藏的行或列，否则返回的值会出错 3.查找类型为0或false（假）时，为精准匹配，当省略或为true时，为近似匹 配（没有找到要查找的值时，返回最接近于“查找值”的值），这里我们常 常使用精准匹配，保证查找的准确性。 Hlookup函数语法与Vlookup函数语法相同。,1.vlookup,hlookup函数,举例： 表1（sheet1） 表2（sheet2） 如果我们要在表2中根据姓名导出手工课的成绩，可以使用如下函数 B2=VLOOKUP(A2,表1的$A$2:表1的$F$5,6,0),填写参数时，可以在公式输入栏里选择Vlookup函数后直接用鼠标点击输入 1.第一个参数代表我们要查找的值（根据姓名查找手工成绩）要注意的是，查找值有相同的（有同名 的），返回第一个值所对应的返回值 2.第二个参数代表包含查找值的查找区域，这个查找区域可以在其他工作表上，也可以在同一张工作表上 ,在本例中我们的查找区域位于工作表一。查找区域使用绝对引用$,固定的查找区域我们要使用绝对引 用，不然将公式引用到其它单元格时，查找区域也会变动，。另外在上面例子中我们的查找值位于查 找区域的第一列（必须的）,1.vlookup,hlookup函数,举例： B2=VLOOKUP(A2,表1的$A$2:表1的$F$5,6,0), 3.第三个参数6代表我们要返回的值的列号（手工成绩位于第6列） 4.第四个参数0代表包含查找类型为精准查找，一般我们要使用精准查 找，保证查找值的准确性 Hlookup函数使用与Vlookup函数相似，只是将行列互换而已。具体区别大 家可以自己体验一下。,2.Match,index函数,Match函数查找某值在数组中的位置。 Index函数作用是返回数组中某行某列的值 语法： 1.Match(要查找的值，数组，查找类型)，其中要查找的值可以是单独输入的值 （要使用双引号），也可以是包含查找值的单元格。数组包含查找值的一组单元 格。查找类型包含3种，为-1时,参数中的数组须降序排列；为1时，数组须升序 排列；为0时，可以是无序排列。 2.index(数组，行，列),其中的行列是数组的相对的行列值，不是工作表的行列,2.Match,index函数,举例： 在上面的数组中 1.A8=MATCH(4,A2:A6,0),其中，第一个参数4代表我们要在数组（A2:A4） 中查找4这个值,最后一个参数0代表我们查找的数组是无序排列。 2.B8=INDEX(A2:B6,3,2)，其中，第1个参数表示数组的区域，第2，3个参 数表示这个查找区域的行和列。,Unit 7 数据库函数的应用,Dget,Dcount函数 Dmax,Dmin,Dsum,Daverage函数,1. Dget,Dcount函数,D函数是数据库类型函数，与其它类型函数不同的是前面多了一个字 母D（数据库database的首字母），数据库函数的使用功能更强大，可 以在查找引用中使用更加复杂的查找关系。 1.Dget函数提取满足条件的单个记录，有多个值符合条件时返回错误 2.Dcount函数返回满足条件的单元格的个数 语法： D函数的语法格式都一样 D（数据库区域，函数要使用的数据列，查找条件），其中第一个参数表示构成数据库 的单元格区域（如下例中的A3:F10），第二个参数表示函数要使用的数据列，可以是用 双引号引用的文本，也可以是列号（如下例中的“语文”，1），第三个参数代表包含给 定条件的单元格区域，它至少包含一个列标志和列标志下方用于设定条件的单元格（如 下例中的B1:B2，B1:C2）。,1. Dget,Dcount函数,举例： 1.Dcount()，在上表中，如果我们想要统计语文89的人数数量，可以在B1中设置列标志 （语文），在B2中设置给定条件（89）,在这里我们在A12单元格求结果 A12=DCO