《excel进阶培训》由会员分享，可在线阅读，更多相关《excel进阶培训（58页珍藏版）》请在金锄头文库上搜索。
1、Excel进阶培训 -函数篇,Michelle Wang,函数,什么是函数？ 函数长得什么样，穿着什么衣？ 函数能帮我们做什么？ 怎样使用函数让它发挥作用？,函数初露尖尖角,函数的定义,函数是一些预定义的公式，通过使用一些称为参数的特定数值按特定的顺序或结构进行计算。,输入参数,输出结果,函数的结构,“=”符号: 表示是公式函数 函数名称: 表示进行什么样的操作 括号( ): 包含函数的参数,及函数的输入值 参数: 要在函数中使用的值和单元格,基本的函数,基本函数应用举例,请尝试在单元格中直接输入=today()或=now(),时间函数举例,怎样正确的使用函数,运用函数名称框选择函数 运用粘贴
2、函数fx 在单元格中直接输入函数,没有输入“=”。Excel认为输入的是文本，不是公式.单元格中完整显示输入内容。不运算。 函数名称漏错字母。Excel认为给出的是无效函数名称，无法计算。显示为#NAME? 括号输少了。Excel认为你输入的公式有错误。显示为#NAME?或直接给予提醒输入公式错误的对话框 选错了需要运算的参数区域。 Excel按照输入的数据计算，但结果不是你想要的。 显示为#REF。表示引用了无效的单元格。,输入函数时容易出现的错误,进一步应用函数,Roundup与Rounddown,王二小出生于1973年10月7日，于1995年7月1日入职公司。年龄计周岁，而工龄向上取。如
3、工龄为3.24年，则计为4年,统计函数RANK,RANK (number,ref,order) 返回一个数字在数字列表中的排位。 Number为需要找到排位的数字。 Ref为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。 Order为一数字，指明排位的方式。 如果order 为0（零）或省略，降序 如果order 不为零，升序,案例应用：员工情况表中，将员工的工资由高到低排名次,RANK应用案例,统计函数Large/Small,思考：我们需要找出排名第N位的某个数据，不做筛选/排序或Rank的情况下，该怎么实现？ 如在员工表，我们想找到年龄最大或者最小的是多少岁？ 方法：运
4、用large或是small函数,Large/Small,Large 返回数据集中第k 个最大值。使用此函数可以根据相对标准来选择数值。例如，可以使用函数LARGE 得到第一名、第二名或第三名的得分。 语法 LARGE(array,k) Array为需要从中选择第k 个最大值的数组或数据区域。 K为返回值在数组或数据单元格区域中的位置（从大到小排）。 说明 如果数组为空，函数LARGE 返回错误值#NUM!。 如果k 0 或k 大于数据点的个数，函数LARGE 返回错误值#NUM!。,计数函数COUNT,条件计数函数COUNTIF,如上例，出仓表中“儿童口服液”出了多少笔货？,COUNIF函数的
5、语法结构,目标：计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(range,criteria) Range为需要计算其中满足条件的单元格数目的单元格区域。 Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、“32“、“32“ 或“apples“。,多条件计数DCOUNT应用,DCOUNT语法结构,目标：返回数据库或数据清单的列中满足指定条件且包含数字的单元格个数 语法：DCOUNT(database,field,criteria) Database构成数据清单或数据库的单元格区域。数据库是包含一组相关数据的数据清单，其中
6、包含相关信息的行为记录，而包含数据的列为字段。数据清单的第一行包含着每一列的标志项。 Field指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field 可以是文本，即两端带引号的标志项，如“使用年数”；此外，Field 也可以是代表数据清单中数据列位置的数字：1 表示第一列，2 表示第二列，等等。参数field 为可选项，如果省略，函数DCOUNT 返回数据库中满足条件criteria 的所有记录数。 Criteria包含指定条件的单元格区域。可以为参数criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。,Dcount函数的field
7、参数省略前后的对照,省略掉dcount函数的field参数,Dcount函数的field参数省略前后的对照,以“出仓单价”列字段作为dcount函数的field参数,数学与三角函数（SUMIF）,SUMIF(range,criteria,sum_range) 根据指定条件对若干单元格求和。 Range为用于条件判断的单元格区域。 Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。 Sum_range是需要求和的实际单元格。 应用案例：以“出仓单”为数据清单，求出某种产品的销售金额的总和。,条件求和SUMIF应用案例,如需求出“灵芝皇胶囊”的价值总金额,怎样实现
8、?,多条件求和：DSUM,DSUM 返回列表或数据库的列中满足指定条件的数字之和。 语法 DSUM(database,field,criteria) Database构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含着每一列的标志项。 Field指定函数所使用的数据列。列表中的数据列必须在第一行具有标志项。Field 可以是文本，即两端带引号的标志项，如“使用年数”或“产量”；此外，Field 也可以是代表列表中数据列位置的数字：1 表示第一列，2 表示第二列，等等。 Criteria为一组包含给定条件的单元格区域。
9、可以为参数criteria 指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。,多条件求和：DSUM,请问dsum函数的参数field能否省略,逻辑函数IF,IF(logical_test,value_if_true,value_if_false) 执行真假值判断，根据逻辑计算的真假值，返回不同结果。 可以使用函数IF 对数值和公式进行条件检测。 Logical_test表示计算结果为TRUE 或FALSE 的任意值或表达式。 Value_if_truelogical_test 为TRUE 时返回的值。如果logical_test 为TRUE 而value_if_true
10、 为空，则本参数返回0 Value_if_falselogical_test 为FALSE 时返回的值,IF函数示例：一次判断,IF函数应用: 一次判断后再计算,IF函数应用: 一次判断后再计算,IF函数应用: 两次判断后再计算,IF函数逻辑推理过程,IF函数应用: 两次判断后再计算,IF函数多重嵌套的输入方法,在函数参数框中填写前面两个参数后，将光标移到Value_if_false栏里，鼠标指向函数名称框，选择IF函数,IF多重嵌套应用案例：计算应纳税金,IF应用案例：两个条件中满足一个即可,IF应用案例：同时满足两个条件,IF应用案例：同时满足两个条件,IF练习：自动判决小学数学试卷,时间
11、函数与IF结合举例:计电话费,时间函数与IF结合举例:计电话费,查找与引用函数LOOKUP,数据源表,LOOKUP查询结果,LOOKUP语法,LOOKUP向量形式 目标：从单行或单列中查找一个值 LOOKUP(lookup_value,lookup_vector,result_vector) Lookup_value为函数LOOKUP 在第一个向量中所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。 Lookup_vector为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。 Lookup_vector 的数值必须按升
12、序排序：.、-2、-1、A-Z、FALSE、TRUE；否则，函数LOOKUP 不能返回正确的结果。文本不区分大小写。 Result_vector只包含一行或一列的区域，其大小必须与lookup_vector 相同。 说明 如果函数LOOKUP 找不到lookup_value，则查找lookup_vector 中小于或等于lookup_value 的最大数值。 如果lookup_value 小于lookup_vector 中的最小值，函数LOOKUP 返回错误值#N/A。,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 在
13、表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。在VLOOKUP 中的V 代表垂直。 Lookup_value为需要在数组第一列中查找的数值。 Table_array为需要在其中查找数据的数据表。 Col_index_num为table_array 中待返回的匹配值的列序号。 Range_lookup为一逻辑值， 为TRUE 或省略，精确匹配 为FALSE，近似匹配。如果找不到，则返回错误值#N/A。 如果Range_lookup 为TRUE，则Table_array 的第一列中的数值必须按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRUE；否则，函
14、数VLOOKUP 不能返回正确的数值。如果Range_lookup 为FALSE，Table_array 不必进行排序。 应用案例：在“员工表”中，按“员工卡号”查“基础工资”。,查找与引用函数（Vlookup）,Vlookup函数应用案例A,Vlookup函数应用案例B,Vlookup函数跨工作簿查询,文本函数大小写字母转换,文本函数计算和截取字符,Len/Left/Right/Mid举例,目标:找出一个字符串在另一个字符串中出现的起始位置. 语法: Find(find_text,within_text,start_num) Search(find_text,within_text,start_num) 区别:Seach不区分大小写,可以使用通配符(?)和(*);Find则区分大小写,不允许使用通配符 结果是指在within_text中含有find_text起始位置的数,start_num指明开始查找的位置.如果省略, start_num的默认值为1,文本函数查找文字串的位置,FIND应用举例,MID/FIND/lEN函数综合应用,结果出错为哪般？,掌握规律，举一反三,函数衍生的一些规律,Thank you!,
