EXCEL2007新增的几个多条件函数.doc

EXCEL2007新增的几个多条件函数解析内容来自网络，在此感谢作者。整理成文档仅作学习之用。查原文请点击链接：/7909652.html 或者/1674104/forum/AVERAGEIF返回某个区域内满足给定条件的所有单元格的平均值（算术平均值）。其语法格式是:AVERAGEIF(range,criteria,average_range)Range是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。Criteria是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为32、32、32、apples或B4。Average_range是要计算平均值的实际单元格集。如果忽略，则使用range。 EXCEL2003时没有此新增函数公式为 方法一:数组公式 =AVERAGE(IF(B4:B11=一班,E4:E11) 方法二:=SUMIF(B4:B11,一班,E4:E11)/COUNTIF(B4:B11,一班)EXCEL2007版AVERAGEIF公式为=AVERAGEIF(B5:B12,一班,E5:E12)公式中range是指B5:B12，即“班级”这列的所有班组情况。criteria是指“一班”，即满足班级为“一班”，average_range是指E5：E12，即对应实际计算的成绩集。 AVERAGEIFS返回满足多重条件的所有单元格的平均值（算术平均值）。其语法格式是:AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)Average_range是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。Criteria_range1,criteria_range2,是计算关联条件的1至127个区域。Criteria1,criteria2,是数字、表达式、单元格引用或文本形式的1至127个条件，用于定义要对哪些单元格求平均值。例如，条件可以表示为32、32、32、apples或B4。EXCEL2003时没有此新增函数公式为方法一:数组公式=AVERAGE(IF(B4:B11=一班,IF(D4:D11=男,E4:E11)方法二:=SUMPRODUCT(B4:B11=一班)*(D4:D11=男)*F4:F11)/SUMPRODUCT(B4:B11=一班)*(D4:D11=男)EXCEL2007版AVERAGEIFS公式为=AVERAGEIFS(F4:F11,D4:D11,男,B4:B11,一班)公式中Average_range是指F4:F11，即“语文”这列的所有成绩。Criteria_range1是指D4:D11，即性别这列数据,Criteria1是指条件“男生”,Criteria_range2是指B4:B11，即“班级”这列数据,Criteria2是指条件“一班”。 SUMIFS对某一区域内满足多重条件的单元格求和。要点SUMIFS和SUMIF的参数顺序不同。具体而言，sum_range参数在SUMIFS中是第一个参数，而在SUMIF中则是第三个参数。如果要复制和编辑这些相似函数，请确保按正确顺序放置参数。语法SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2)Sum_range是要求和的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。空值和文本值会被忽略。Criteria_range1,criteria_range2,是计算关联条件的1至127个区域。Criteria1,criteria2,是数字、表达式、单元格引用或文本形式的1至127个条件，用于定义要对哪些单元格求和。例如，条件可以表示为32、32、32、apples或B4。我们可以从AVERAGEIF函数表中看到2003函数公式中有用到SUMIF,但是到了多条件AVERAGEIFS函数表中SUMIF却有点无能为力了，但是SUMIFS却是轻而易举的解决了。 EXCEL2003时没有此新增函数公式为 方法一:数组公式 =SUM(B4:B11=一班)*(D4:D11=男)*F4:F11) 注此公式如不想三键组合可用SUMPRODUCT但运算原理仍是数组 =SUMPRODUCT(B4:B11=一班)*(D4:D11=男)*F4:F11) EXCEL2007版SUMIFS公式为=SUMIFS(F4:F11,B4:B11,一班,D4:D11,男)公式中sum_range是指F4:F11，即“语文”这列的所有成绩。Criteria_range1是指D4:D11，即性别这列数据,Criteria1是指条件“男生”,Criteria_range2是指B4:B11，即“班级”这列数据,Criteria2是指条件“一班”。 趣味题: 熟悉函数三维运算的人都知道,SUMIF在三维合计运算中是一个非常不错的方法,但是SUMIF只能单条件,从而限制了此函数的更好运用,而SUMIFS就顺利的解决了此问题。 在这个工作薄中有1、2、3三个工作表，要求计算日期大于11月1日小于12月5日项目为“乙”的数值合计结果 公式为： =SUM(SUMIFS(INDIRECT(ROW($A1:$A3)&!&ADDRESS(2,MATCH(乙,1!1:1,0)&:&ADDRESS(6,MATCH(乙,1!1:1,0),INDIRECT(ROW($A1:$A3)&!A2:A6),&DATE(2007,11,1),INDIRECT(ROW($A1:$A3)&!A2:A6),32、apples或B4。我们可以从AVERAGEIF函数表中看到2003函数公式中有用到COUNTIF,但是到了多条件AVERAGEIFS函数表中COUNTIF却有点无能为力了，但是COUNTIFS却是轻而易举的解决了。 EXCEL2003时没有此新增函数公式为 方法一:数组公式 =SUM(B4:B11=一班)*(D4:D11=男) 注此公式如不想三键组合可用SUMPRODUCT但运算原理仍是数组 =SUMPRODUCT(B4:B11=一班)*(D4:D11=男) EXCEL2007版COUNTIFS公式为=COUNTIFS(B4:B11,一班,D4:D11,男)range1是指D4:D11，即性别这列数据,Criteria1是指条件“男生”,range2是指B4:B11，即“班级”这列数据,Criteria2是指条件“一班”。 IFERROR全部显示全部隐藏如果公式计算出错误则返回您指定的值；否则返回公式结果。使用IFERROR函数来捕获和处理公式（公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号(=)开始。）中的错误。语法IFERROR(value,value_if_error)Value是需要检查是否存在错误的参数。Value_if_error是公式计算出错误时要返回的值。计算得到的错误类型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。在成绩查询中我们经常需要输入准考证号或姓名查找某个学生的居体成绩,如果输入信息是错误的,会出现错误提示,我们必须用IF或其他方法处理此公式,选成多次引用,而IFERROR顺利的解决了此问题。 EXCEL2003时没有此新增函数公式为=IF(ISNA(VLOOKUP(A22,C:G,4,0),没有找到此学生,VLOOKUP(A22,C:G,4,0)EXCEL2007版IFERROR公式为=IFERROR(VLOOKUP(A22,C:G,4,0),没有找到此学生)Value是指VLOOKUP(A22,C:G,4,0)这个公式并判断有无出错，Value_if_error在前面VALUE出错是返回值即“没有找到此学生”。补充：用EXCEL函数做不同单位相同年级学生考试评比，对于评价单科EXCEL 2003所提供的函数就足够了。如：单科优秀率=COUNTIF(单位1!C3:C100,=80)单科合格率=COUNTIF(成绩表!E1:E1000,=60)总分=SUM(单位1!D4:D100)名次=RANK(M8,M$4:M$101,0)及格人数=COUNTIF(成绩表!E1:E50,=60)优秀人数，如到得80分为优秀，即把上式中的60改为80即可。如果被评价单位在一张工作表中的不同单元格中，只对不同单位（班级）的考生固定好区间，连续输入成绩只可，如上面单科合格率中的“成绩表”就是这样的。如果被评价单位在一个工作簿中的不同工作表上，就可点击不同的工作表标签名称直接引用，如上面的“单位1”。这里的E1:E50是第一个班的语文成绩，当然学生小于50，多余的行可空着，如果大于50小于60，可设为E1:E60。如果要对两门或者更多的科目进行数据收集，就要用到EXCEL 2007的