二级Office考试函数必备.doc

.等级考试必备函数讲解一．日期与时间函数1.YEAR格式：YEAR(serial_num)功能：返回某日期的年份返回值为 1900 到 9999 之间的整数举例：=YEAR("2008年5月23日")，确认后将返回2008年5月23日的年份20082.MONTH格式：MONTH(serial_num)功能：返回以系列数表示的日期中的月份月份是介于 1（一月）和 12（十二月）之间的整数举例：=MONTH("2008年5月23日")，确认后将返回2008年5月23日的月份53.TODAY格式：TODAY( )功能：返回当前日期的系列数，系列数是 Microsoft Excel 用于日期和时间计算的日期-时间代码举例：=today()，确认后将返回今天的日期4.DAY格式：DAY(serial_num)功能：返回以系列数表示的某日期的天数，用整数 1 到 31 表示举例：=DAY("2003-12-18")，确认后显示出18说明：如果是给定的日期，请包含在英文双引号中5.NOW格式：NOW( )功能：返回当前日期和时间所对应的系列数。

举例：=NOW()，确认后即刻显示出系统日期和时间说明：如果系统日期和时间发生了改变，只要按一下F9功能键，即可让其随之改变6.HOUR格式：HOUR(serial_num)功能：返回时间值的小时数，即一个介于 0 (12:00 A.M.) 到 23 (11:00 P.M.) 之间的整数举例：=HOUR("3:30:30 AM" ) ，确认后返回第一个时间值的小时数 37.MINUTE格式：MINUTE(serial_num)功能：返回时间值中的分钟，即一个介于 0 到 59 之间的整数举例：=MINUTE(“15:30:00″) ，确认后返回308.DATE格式：DATE(year,month,day) 功能：返回代表特定日期的系列数举例：=DATE(2003,13,35)，确认后显示出2004-2-4说明：由于上述公式中，月份为13，多了一个月，顺延至2004年1月；天数为35，比2004年1月的实际天数又多了4天，故又顺延至2004年2月4日9.WEEKDAY格式：WEEKDAY(serial_num,return_type)功能：返回某日期为星期几默认情况下，其值为 1（星期天）到 7（星期六）之间的整数。

举例：=WEEKDAY(“2001/8/28″,2) ，确认后返回2(星期二)；=WEEKDAY(“2003/02/23″,3) ，确认后返回6(星期日)二．数学与三角函数1.INT格式：INT(num1)功能：将数值向下取整为最接近的整数举例：=INT(18.89)，确认后显示出18说明：在取整时，不进行四舍五入；如果输入的公式为=INT(-18.89)，则返回结果为-19　　　　2.MOD格式：MOD(num1,num2)功能：计算num1和num2相除的余数举例：= MOD (5,-4)，确认后显示-3说明：两个整数求余时，其值的符号为除数的符号如果除数为零，函数 MOD返回错误值 #DIV/0!3.SUM格式：SUM(num1,num2,…)功能：计算所有参数和举例：=SUM(A1,B2:C3) ，确认后对单个单元格A1与B2:C3区域求和说明：需要求和的参数个数不能超过30个4.SUMIF格式：SUMIF(range,criteria,sum_range)功能：对满足条件的单元格求和举例：假如A1:A36单元格存放某班学生的考试成绩，若要计算与格学生的总分，可以使用公式“=SUMIF（A1:A36，″>=60″，A1:A36）”，式中的“A1:A36”为提供逻辑判断依据的单元格引用，“>=60”为判断条件，不符合条件的数据不参与求和。

说明：第一个参数：Range为条件区域，用于条件判断的单元格区域；第二个参数：Criteria是求和条件，为确定哪些单元格将被相加求和的条件，其形式可以由数字、逻辑表达式等组成的判定条件；第三个参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用当省略第三个参数时，则条件区域就是实际求和区域5.SUMIFS格式：SUMIFS(sum_range,criteria_range, criteria,…)功能：对一组给定条件制定的单元格求和举例：如图1所示，在单元格 E1 中输入一个公式并按[Enter]键，汇总销售额在15000 到25000之间的员工销售总额公式如下 ：=SUMIFS(B2:B10,B2:B10,">=15000",B2:B10,"<=25000")图1　汇总指定销售额围的销售总额说明：(1)如果在 SUMIFS 函数中设置了多个条件，那么只对参数 sum_range 中同时满足所有条件的单元格进行求和2)与 SUMIF函数不同的是，SUMIFS 函数中的求和区域(sum_range)与条件区域(criteria_range)的大小和形状必须一致，否则公式出错。

三．统计函数1.AVERAGE格式：AVERAGE (num1,num2,…)功能：求出所有参数的算术平均值举例：= AVERAGE (A1,B2:C3) ，确认后 表示对单个单元格A1与B2:C3区域求平均说明：需要求和的参数个数不能超过30个2.COUNT格式：COUNT(num1,num2,…)功能：计算参数中数值型数据的个数举例：=COUNT(A1：D5) ，确认后会对A1：D5区域统计包含数字值的单元格个数说明：COUNT函数是对“（）”含数字值的参数的个数进行统计，参数可以是单元格、单元格区域、数字、字符等，对于含数字值的参数只按个数统计，数字值容不影响3.COUNTIFCOUNTIF(num1,num2,…)功能：计算参数中满足条件的数值型数据的个数举例：=COUNTIF(B1:B13,">=80")，确认后，即可统计出B1至B13单元格区域中，数值大于等于80的单元格数目说明：允许引用的单元格区域中有空白单元格出现4.COUNTIFS格式：COUNTIFS(num1,num2,…)功能：计算参数中满足条件的数值型数据的个数举例：如图2所示，求9月份上半月发货平台的发货单数。

COUNTIFS(A2:A13,"发货平台",B2:B13,"<2014-9-16")图2 COUNTIS函数举例说明：COUNTIFS函数的参数——条件，它的形式可以为数字、表达式或文本当它是文本和表达式时，注意要使用双引号且引号在英文状态下输入5.MAX格式：MAX(num1,num2,…)功能：计算所有参数最大值举例：如果 A1:A5 包含数字 10、7、9、27 和 2，则=MAX(A1:A5,30) 等于30说明：参数可以是数字或者是包含数字的名称、数组或引用6.MIN格式：MIN(num1,num2,…)功能：计算所有参数最小值举例：如果 A1:A5 包含数字 10、7、9、27 和 2，则=MIN(A1:A5,30) 等于2说明：参数可以是数字或者是包含数字的名称、数组或引用7.RANK格式：RANK（num1,list）功能：计算数字num1在列表list中的排位举例：=RANK(A2,$A$2:$A$24)其中A2是需要确定位次的数据，$A$2:$A$24表示数据围，括号里的容即表示A2单元格数据在A2:A24这个数据区域的排名情况说明：在输入数据围的时候，一定要用 $符号，否则排出来的名次会是错误的。

四．查找与引用函数1.INDEX格式：INDEX(array,row_num,column_num)　功能：返回列表或数组中的元素值，此元素由行序号和列序号的索引值进行确定举例：如图3所示，在F8单元格中输入公式：=INDEX(A1:D11,4,3)，确认后则显示出A1至D11单元格区域中，第4行和第3列交叉处的单元格（即C4）中的容图3图2 INDEX函数举例说明：此处的行序号参数（row_num）和列序号参数（column_num）是相对于所引用的单元格区域而言的，不是Excel工作表中的行或列序号2.MATCH格式：MATCH(lookup_value,lookup_array,match_type)功能：返回在指定方式下与指定数值匹配的数组中元素的相应位置举例：如图4所示，在B7单元格中输入公式：“=MATCH(100，B2：B5，0)”， 确认后则显示“3”说明：Lookup_array只能为一列或一行；match-type表示查询的指定方式，1-查找小于或等于指定容的最大值，而且指定区域必须按升序排列，0-查找等于指定容的第一个数值，-1-查找大于或等于指定容的最小值，而且指定区域必须降序排列。

图4 MATCH函数举例3.LOOKUP格式：LOOKUP(lookup_value,lookup_vector,result_vector)功能：用于在查找围中查询指定的值，并返回另一个围中对应位置的值举例：如图5所示，在频率列中查找 4.19，然后返回颜色列中同一行的值（橙色）在C2单元格中输入 =LOOKUP(4.19,A2:A6,B2:B6)，确认后返回“橙色”图5 LOOKUP函数举例说明：如果 LOOKUP 找不到 lookup_value，它会匹配 lookup_vector 中小于或等于 lookup_value 的最大值如果 lookup_value 小于 lookup_vector 中的最小值，则 LOOKUP 会返回 #N/A 错误值4.VLOOKUP格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)功能：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值，默认情况下，表是以升序排序的举例：如图6所示，要求根据表二中的，查找所对应的年龄公式：G3 =VLOOKUP(F3,$B$2:$D$8,3,0) 。

图6 VLOOKUP函数举例说明：给定的第二个参数查找围要符合以下条件才不会出错：（1）查找目标一定要在该区域的第一列2） 该区域中一定要包含要返回值所在的列 第3个参数是一个整数值，它是“返回值”在第二个参数给定的区域中的列数最后一个参数是决定函数精确和模糊查找的关键，精确即完全一样，模糊即包含的意思0或FALSE就表示精确查找，而值为1 或TRUE时则表示模糊在使用VLOOKUP时千万不要把这个参数给漏掉了，如果缺少这个参数默为值为模糊查找，我们就无法精确查找到结果了五．文本函数1.LEFT格式：LEFT(text,num_chars)功能：从一个文本字符串的第一个字符开始返回指定个数的字符举例：假定A38单元格中保存了“我喜欢天极网”的字符串，我们在C38单元格中输入公式：=LEFT(A38,3)，确认后即显示出“我喜欢”的字符说明：此函数名的英文意思为“左”，即从左边截取，Excel很多函数都取其英文的意思　　　2.RIGHT格式：RIGHT(text,num_chars)。