OFFICE XP培训(Excel 进阶函数篇)

1、OFFICE XP培训(Excel 进阶函数篇)Excel进阶培训-函数篇陈君(Doris)微软认证讲师陈君函数? 什么是函数？? 函数长得什么样，穿着什么衣？? 函数能帮我们做什么？? 怎样使用函数让它发挥作用？陈君函数初露尖尖角陈君函数的定义? 函数是一些预定义的公式，通过使用一些称为参数的特定数值按特定的顺序或结构进行计算。输入参数输出结果陈君函数的结构? “=”符号: 表示是公式函数? 函数名称: 表示进行什么样的操作? 括号( ): 包含函数的参数,及函数的输入值? 参数: 要在函数中使用的值和单元格陈君基本的函数函数名称 用途Sum 求和Product 求乘积Average 求算术

2、平均值Max/Min 最大/最小Count 统计数值的个数Today/Now 当前日期/当前日期和时间Int 向下舍入取整数ABS 取绝对值Round 四舍五入陈君基本函数应用举例请尝试在单元格中直接输入=today()或=now()陈君时间函数举例日期 时间 年 月 日 时 分 秒 星期Date Time Year Month Day Hour Minute Secondweekday陈君怎样正确地使用函数? 运用函数名称框选择函数? 运用粘贴函数fx? 在单元格中直接输入函数陈君输入函数时容易出现的错误? 没有输入“=”。Excel认为输入的是文本，不是公式.单元格中完整显示输入内容。不运

3、算。? 函数名称漏错字母。Excel认为给出的是无效函数名称，无法计算。显示为#NAME? 括号输少了。Excel认为你输入的公式有错误。显示为#NAME?或直接给予提醒输入公式错误的对话框? 选错了需要运算的参数区域。? Excel按照输入的数据计算，但结果不是你想要的。? 显示为#REF。表示引用了无效的单元格。陈君进一步应用函数函数名称 用途 函数类别Sumif 有条件地求和 数学与三角函数统计函数查找与引用函数逻辑函数财务函数Dcount 多条件地统计数值 数据库函数MID 提取字符 文本函数Find 查找并提取字符 文本函数Rank 排名次Vlookup 查找记录IF 判定条件PV/

4、FV 投资现值/投资未来值陈君Roundup与Rounddown? 王二小出生于1973年10月7日，于1995年7月1日入职公司。年龄计周岁，而工龄向上取。如工龄为3.24年，则计为4年。陈君统计函数RANK?RANK(number,ref,order)返回一个数字在数字列表中的排位。? Number 为需要找到排位的数字。? Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。? Order 为一数字，指明排位的方式。? 如果 order 为 0（零）或省略，降序? 如果 order 不为零，升序案例应用：员工情况表中，将员工的工资由高到低排名次陈君RANK应用案例

5、陈君统计函数Large/Small? 思考：我们需要找出排名第N位的某个数据，不做筛选/排序或Rank的情况下，该怎么实现？? 如在员工表，我们想找到年龄最大或者最小的是多少岁？方法：运用large或是small函数陈君Large/Small? Large? 返回数据集中第 k 个最大值。使用此函数可以根据相对标准来选择数值。例如，可以使用函数 LARGE 得到第一名、第二名或第三名的得分。? 语法? LARGE(array,k)? Array 为需要从中选择第 k 个最大值的数组或数据区域。? K 为返回值在数组或数据单元格区域中的位置（从大到小排）。? 说明? 如果数组为空，函数 LARG

6、E 返回错误值 #NUM!。? 如果 k 0 或 k 大于数据点的个数，函数 LARGE 返回错误值#NUM!。陈君计数函数COUNT思考题：? 怎样得出指定区域的文本单元格的个数？? 怎样得出指定区域的单元格的个数？陈君条件计数函数COUNTIF? 如上例，出仓表中“儿童口服液”出了多少笔货？陈君COUNIF函数的语法结构? 目标：计算区域中满足给定条件的单元格的个数。? 语法：COUNTIF(range,criteria)? Range 为需要计算其中满足条件的单元格数目的单元格区域。? Criteria 为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表

7、示为 32、;32;、;32; 或 ;apples;。陈君多条件计数DCOUNT应用陈君DCOUNT语法结构? 目标：返回数据库或数据清单的列中满足指定条件且包含数字的单元格个数? 语法：DCOUNT(database,field,criteria)? Database 构成数据清单或数据库的单元格区域。数据库是包含一组相关数据的数据清单，其中包含相关信息的行为记录，而包含数据的列为字段。数据清单的第一行包含着每一列的标志项。? Field 指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field 可以是文本，即两端带引号的标志项，如“使用年数” ；此外，Field 也可以

8、是代表数据清单中数据列位置的数字：1 表示第一列，2 表示第二列，等等。参数 field 为可选项，如果省略，函数 DCOUNT 返回数据库中满足条件criteria 的所有记录数。? Criteria 包含指定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。陈君Dcount函数的field参数省略前后的对照? 省略掉dcount函数的field参数陈君Dcount函数的field参数省略前后的对照? 以“出仓单价”列字段作为dcount函数的field参数陈君数学与三角函数（SUMIF）SUMIF(range,crite

9、ria,sum_range)根据指定条件对若干单元格求和。? Range 为用于条件判断的单元格区域。? Criteria 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。? Sum_range 是需要求和的实际单元格。应用案例：以“出仓单”为数据清单，求出某种产品的销售金额的总和。陈君条件求和SUMIF应用案例如需求出“灵芝皇胶囊”的价值总金额,怎样实现?陈君多条件求和：DSUM? DSUM? 返回列表或数据库的列中满足指定条件的数字之和。? 语法? DSUM(database,field,criteria)? Database 构成列表或数据库的单元格区域。数据库是包含

10、一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含着每一列的标志项。? Field 指定函数所使用的数据列。列表中的数据列必须在第一行具有标志项。Field 可以是文本，即两端带引号的标志项，如“使用年数”或“产量”；此外，Field 也可以是代表列表中数据列位置的数字：1 表示第一列，2 表示第二列，等等。? Criteria 为一组包含给定条件的单元格区域。可以为参数 criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。陈君多条件求和：DSUM请问dsum函数的参数field能否省略陈君逻辑函数IF? IF(logi

11、cal_test,value_if_true,value_if_false)? 执行真假值判断，根据逻辑计算的真假值，返回不同结果。? 可以使用函数 IF 对数值和公式进行条件检测。? Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。? Value_if_true logical_test 为 TRUE 时返回的值。如果 logical_test 为TRUE 而 value_if_true 为空，则本参数返回 0? Value_if_false logical_test 为 FALSE 时返回的值陈君IF函数示例：一次判断案例数据表：IF函数陈君IF函数应

12、用: 一次判断后再计算月销售额 提成标准10000 0.05销售代表 月销售额 销售提成Kiki 15060Rose 14800John 12350Jane 21010Coco 9500Lily 16030Mimi 18530Jack 8200Grace 13700Ana 25080陈君IF函数应用: 一次判断后再计算陈君IF函数应用: 两次判断后再计算月销售额 提成标准15000 0.08销售代表 月销售额 销售提成Kiki 15060Rose 14800John 12350Jane 21010Coco 9500Lily 16030Mimi 18530Jack 8200Grace 13700

13、Ana 25080陈君IF函数逻辑推理过程结果单元格第一次判断15000月销售额0.08月销售额0.05TrueTrue月销售额 提成标准15000 0.08falsefalse陈君IF函数应用: 两次判断后再计算陈君IF函数多重嵌套的输入方法? 在函数参数框中填写前面两个参数后，将光标移到Value_if_false栏里，鼠标指向函数名称框，选择IF函数陈君IF多重嵌套应用案例：计算应纳税金陈君IF应用案例：两个条件中满足一个即可目标：选出职务工资大于2500的高薪人员和小于1000的低薪人员陈君IF应用案例：同时满足两个条件目标：判断出职务工资在1500至2000之间的员工陈君IF应用案例

14、：同时满足两个条件陈君IF练习：自动判决小学数学试卷陈君时间函数与IF结合举例:计电话费陈君时间函数与IF结合举例:计电话费陈君查找与引用函数LOOKUP? 数据源表员工卡号 姓名 出生 年月 年龄 家庭通信地址 家庭电话区号 电话号码 学历LD001 林海 1980-811-01 23 上海建国门外大街22号103室 200033 021 87654788 大学本科LD005 王卫平 1952-03-06 52 黄冈朝阳区牛王庙 700027 0713 6733826 中专LD006 任水滨 1970-11-01 33 天津朝阳区惠新西里军退办公楼300029 022 28667755 硕士

15、研究生LD010 王川 1960-11-01 43 北京车道沟1号滨河大厦12层 100086 010 87547288 大专LD015 王小明 1953-10-01 50 武汉市延安路1536号 700001 027 87612341 大专LD020 刘晋生 1952-11-01 51 上海市友谊宾馆60122室 200080 021 65544988 硕士研究生LD003 刘学燕 1957-04-01 47 广州安定门外大街68号码层 503311 020 87876655 大学本科LD004 黄璐京 1961-01-01 43 武汉北京朝阳区亮马河桥路52号 700016 027 618

16、87722 中专LD007 张晓寰 1961-12-01 42 广州朝阳区惠新东街15号 100028 020 36774421 大专LD011 连威 1981-05-01 23 北京车道沟1号滨河大厦12层 100086 010 66343321 大专LD012 刘琳 1946-10-01 57 广州东山区学院南路86号 500081 020 66578233 中专LD017 庄凤仪 1961-11-01 42 广州深南中路统建大楼3栋18层 518031 020 85236788 大学本科LD018 沈奇峰 1980-01-01 24 广州市深南中路电子大厦901室 518321 020

17、86537866 中专LD002 陈鹏 1974-11-01 29 珠海建国门外大街1号518室 500004 0756 7654656 大专LD013 沈克 1977-04-01 27 北京东直门内海运仓2号 100702 010 23343221 大学本科LD014 艾芳 1973-12-01 30 东莞王府井大街277号 500746 0759 6656438 大学本科LD016 胡海涛 1959-10-01 45 上海市红旗路1号 262723 021 28865436 大专LD019 金星 1980-06-01 24 北京市西藏中路237号100021 010 27789655 大专

18、LD008 杨宝春 1955-05-01 49 东莞菜市口铁门胡同17号 100332 0759 7656577 中专LD009 许东东 1956-07-01 48 上海宣武门西大街后河沿6号 200053 021 65668863 大学本科陈君LOOKUP查询结果陈君LOOKUP语法LOOKUP向量形式? 目标：从单行或单列中查找一个值? LOOKUP(lookup_value,lookup_vector,result_vector)? Lookup_value 为函数 LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。?

19、Lookup_vector 为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。 Lookup_vector 的数值必须按升序排序：.、-2、-1、A-Z、FALSE、TRUE；否则，函数 LOOKUP 不能返回正确的结果。文本不区分大小写。? Result_vector 只包含一行或一列的区域，其大小必须与lookup_vector 相同。? 说明? 如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。? 如果 lookup_value 小于 lookup_vect

20、or 中的最小值，函数 LOOKUP 返回错误值 #N/A。陈君查找与引用函数（Vlookup）? VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。在 VLOOKUP 中的 V 代表垂直。? Lookup_value 为需要在数组第一列中查找的数值。? Table_array 为需要在其中查找数据的数据表。? Col_index_num 为 table_array 中待返回的匹配值的列序号。? Range_lookup 为一逻辑值，? 为TR

21、UE 或省略，精确匹配? 为 FALSE，近似匹配。如果找不到，则返回错误值 #N/A。? 如果 Range_lookup 为 TRUE，则 Table_array 的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否则，函数VLOOKUP 不能返回正确的数值。如果 Range_lookup 为 FALSE，Table_array 不必进行排序。应用案例：在“员工表”中，按“员工卡号”查“基础工资”。陈君Vlookup函数应用案例A陈君Vlookup函数应用案例B陈君Vlookup函数跨工作簿查询陈君文本函数大小写字母转换目标：用于大小写字母使用这些函数时，

22、数和其它非字母字符仍保持不变函数 语法 实现目标Upper Upper(text) 将全部字母转换成大写Lower lower(text) 将全部字母转换成小写proper proper(text) 将一个字的第一个字母转换成大写，而将其余字母转换成小写John D. Smithjohn d. smithJOHN D. SMITHjohn d. smithproper结果lower结果upper结果原文举例陈君文本函数计算和截取字符目标：Len、Right、Left、Mid函数经常一起使用，将一行文本分割为单独的文字串。函数 语法 实现目标Len Len (text) 文字串的长度Right

23、Right(text, num_chars) 一串文字的最右一些字符Left Left(text, num_chars) 一串文字的最左一些字符mid Mid(text,start_num,num_chars)从一串文字串中提取一串文字,提出的起始点由start_num决定，提取的位数由num_chars决定陈君Len/Left/Right/Mid举例陈君文本函数查找文字串的位置? 目标:找出一个字符串在另一个字符串中出现的起始位置.? 语法:? Find(find_text,within_text,start_num)? Search(find_text,within_text,start_

24、num)? 区别:Seach不区分大小写,可以使用通配符(?)和(*);Find则区分大小写,不允许使用通配符? 结果是指在within_text中含有find_text起始位置的数,start_num指明开始查找的位置.如果省略, start_num的默认值为1陈君FIND应用举例陈君MID/FIND/lEN函数综合应用电话010-512492550757-5050386010-32822668010-465182050759-4632764020-49302015010-42244886010-30156236022-3183766区号010075701001007590200100100

25、22=MID(E4,1,FIND(;-;,E4)-1)=MID(E4,FIND(;-;,E4)+1,LEN(E4)-FIND(;-;,E4)电话号码512492555050386328226684651820546327644930201542244886301562363183766陈君结果出错为哪般？错误表现 错误原因#NUM! 公式或函数中某个数字有问题#DIV/0! 当公式被零除时#NAME? 公式中函数中使用了不能识别的文本#VALUE? 使用错误的参数或运算对象#REF! 单元格引用无效#N/A 函数中没有可用数值# 单元格列宽不够或日期时间产生负值陈君掌握规律，举一反三? 函数衍生的一些规律前缀加D 基本函数 后缀加A 后缀加ifDsum Sum SumifDaverage Average Average