Excel进阶培训

Excel进阶培训--函数篇MichelleWang函数什么是函数？函数长得什么样，穿着什么衣？函数能帮我们做什么？怎样使用函数让它发挥作用？函数初露尖尖角函数的定义函数是一些预定义的公式，通过使用一些称为参数的特定数值按特定的顺序或结构进行计算。输入参数输出结果函数的结构“=”符号:表示是公式／函数函数名称:表示进行什么样的操作括号():包含函数的参数,及函数的输入值参数:要在函数中使用的值和单元格基本的函数函数名称用途Sum求和Product求乘积Average求算术平均值Max/Min最大/最小Count统计数值的个数Today/Now当前日期/当前日期和时间Int向下舍入取整数ABS取绝对值Round四舍五入基本函数应用举例请尝试在单元格中直接输入=today()或=now()时间函数举例日期时间年月日时分秒星期DateTimeYearMonthDayHourMinuteSecondWeekday怎样正确的使用函数运用函数名称框选择函数运用粘贴函数fx在单元格中直接输入函数没有输入“=”。Excel认为输入的是文本，不是公式.单元格中完整显示输入内容。不运算。􀂃函数名称漏错字母。Excel认为给出的是无效函数名称，无法计算。显示为#NAME?􀂃括号输少了。Excel认为你输入的公式有错误。显示为#NAME?或直接给予提醒输入公式错误的对话框􀂃选错了需要运算的参数区域。•Excel按照输入的数据计算，但结果不是你想要的。•显示为#REF。表示引用了无效的单元格。输入函数时容易出现的错误进一步应用函数Roundup与Rounddown王二小出生于1973年10月7日，于1995年7月1日入职公司。年龄计周岁，而工龄向上取。如工龄为3.24年，则计为4年统计函数RANKRANK(number,ref,order)返回一个数字在数字列表中的排位。Number为需要找到排位的数字。Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order为一数字，指明排位的方式。•如果order为0（零）或省略，降序•如果order不为零，升序案例应用：员工情况表中，将员工的工资由高到低排名次RANK应用案例统计函数Large/Small思考：我们需要找出排名第N位的某个数据，不做筛选/排序或Rank的情况下，该怎么实现？•如在员工表，我们想找到年龄最大或者最小的是多少岁？方法：运用large或是small函数Large/Small􀂃Large•返回数据集中第k个最大值。使用此函数可以根据相对标准来选择数值。例如，可以使用函数LARGE得到第一名、第二名或第三名的得分。􀂃语法•LARGE(array,k)•Array为需要从中选择第k个最大值的数组或数据区域。•K为返回值在数组或数据单元格区域中的位置（从大到小排）。􀂃说明•如果数组为空，函数LARGE返回错误值#NUM!。•如果k≤0或k大于数据点的个数，函数LARGE返回错误值#NUM!。计数函数COUNT条件计数函数COUNTIF如上例，出仓表中“儿童口服液”出了多少笔货？COUNIF函数的语法结构目标：计算区域中满足给定条件的单元格的个数。􀂃语法：COUNTIF(range,criteria)•Range为需要计算其中满足条件的单元格数目的单元格区域。•Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。多条件计数DCOUNT应用DCOUNT语法结构􀂃目标：返回数据库或数据清单的列中满足指定条件且包含数字的单元格个数􀂃语法：DCOUNT(database,field,criteria)􀂃Database构成数据清单或数据库的单元格区域。数据库是包含一组相关数据的数据清单，其中包含相关信息的行为记录，而包含数据的列为字段。数据清单的第一行包含着每一列的标志项。􀂃Field指定函数所使用的数据列。数据清单中的数据列必须在第一行具有标志项。Field可以是文本，即两端带引号的标志项，如“使用年数”；此外，Field也可以是代表数据清单中数据列位置的数字：1表示第一列，2表示第二列，等等。参数field为可选项，如果省略，函数DCOUNT返回数据库中满足条件criteria的所有记录数。􀂃Criteria包含指定条件的单元格区域。可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。Dcount函数的field参数省略前后的对照省略掉dcount函数的field参数Dcount函数的field参数省略前后的对照以“出仓单价”列字段作为dcount函数的field参数数学与三角函数（SUMIF）SUMIF(range,criteria,sum_range)根据指定条件对若干单元格求和。􀂃Range为用于条件判断的单元格区域。􀂃Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。􀂃Sum_range是需要求和的实际单元格。应用案例：以“出仓单”为数据清单，求出某种产品的销售金额的总和。条件求和SUMIF应用案例如需求出“灵芝皇胶囊”的价值总金额,怎样实现?多条件求和：DSUMDSUM􀂃返回列表或数据库的列中满足指定条件的数字之和。􀂃语法􀂃DSUM(database,field,criteria)•Database构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含着每一列的标志项。•Field指定函数所使用的数据列。列表中的数据列必须在第一行具有标志项。Field可以是文本，即两端带引号的标志项，如“使用年数”或“产量”；此外，Field也可以是代表列表中数据列位置的数字：1表示第一列，2表示第二列，等等。•Criteria为一组包含给定条件的单元格区域。可以为参数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为空，则本参数返回0􀂃Value_if_falselogical_test为FALSE时返回的值IF函数示例：一次判断IF函数应用:一次判断后再计算IF函数应用:一次判断后再计算IF函数应用:两次判断后再计算IF函数逻辑推理过程IF函数应用:两次判断后再计算IF函数多重嵌套的输入方法在函数参数框中填写前面两个参数后，将光标移到Value_if_false栏里，鼠标指向函数名称框，选择IF函数IF多重嵌套应用案例：计算应纳税金IF应用案例：两个条件中满足一个即可IF应用案例：同时满足两个条件IF应用案例：同时满足两个条件IF练习：自动判决小学数学试卷时间函数与IF结合举例:计电话费时间函数与IF结合举例:计电话费查找与引用函数LOOKUP数据源表LOOKUP查询结果LOOKUP语法LOOKUP向量形式􀂃目标：从单行或单列中查找一个值􀂃LOOKUP(lookup_value,lookup_vector,result_vector)􀂃Lookup_value为函数LOOKUP在第一个向量中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。􀂃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_vector中的最小值，函数LOOKUP返回错误值#N/A。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。在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；否则，函数VLOOKUP不能返回正确的数值。如果Range_lookup为FALSE，Table_array不必进行排序。应用案例：在“员工表”中，按“员工卡号”查“基础工资”。查找与引用函数（Vlookup）Vlookup函数应用案例AVlookup函数应用案例BVlookup函数跨工作簿查询文本函数－大小写字母转换文本函数—计算和截取字符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函数综合应用结果出错为哪般？掌握规律，举一反三函数衍生的一些规律