Office365里新增的几个Excel函数

第一篇：Office365里新增的几个Excel函数

Office365里新增的几个Excel函数

Office365里新增的几个Excel函数

先来看这个：

多个条件的判断：

按分数来判断是不是合格，这个想必大家经常会遇到吧。使用IF函数判断，那公式必定是多个IF，眼花缭乱的：

=IF(D2>89,“优秀”,IF(D2>79,“良好”,IF(D2>69,“一般”,IF(D2>59,“合格”,“不合格”))))现在，只需这样：

=IFS(D2>89,“优秀”,D2>79,“良好”,D2>69,“一般”,D2>59,“合格”,1=1,“不合格”)

只要一个IFS函数，参数直接就是一组一组的判断条件和相应要返回的结果。多条件判断，再也不用晕头转向了。

除此之外，还可以使用SWITCH函数对多个条件进行判断。比如下面的这个公式：

=SWITCH(A1,1,“OK”,15,“YES”,18,“NO”,“")

如果A1等于1，返回结果”OK“。如果A1等于15，返回结果”YES“，如果A1等于18，返回结果”NO“，否则返回空文本”“。

按条件计算最小值

这也是一个经常遇到的问题，就像下面这个图中，要计算班级为乙班，并且性别为女性的最低分数：在之前，如果我们要计算符合某个条件的最小值，需要使用数组公式来实现。模式化的数组公式是这样的：

{=MIN(IF((B3:B12=”乙班“)*(D3:D12=”女“),E3:E12))}

注意，这个可是数组公式，需要按Ctrl+Shift+回车来结束公式编辑的。现在，只需这样：

=MINIFS(G3:G12,D3:D12,”乙班“,F3:F12,”女“)公式的用法类似于SUMIFS函数，第一个参数是需要计算的数据区域，后面是成对的区域/条件。

如果要计算符合条件的最大值，只要把公式中的MINIFS换成MAXIFS就OK了。

下面这个问题，相信大家也遇到过，就是按条件提取名单。

比方说下图中，就是提取班组为“乙班”的所有人员名单，并且在姓名中间用逗号隔开。在之前，解决这个问题必须要使用VBA代码完成，这对于大多数普通用户，门槛还是太高了。

现在，只要用下面这个数组公式，就OK了。

{=TEXTJOIN(”,“,TRUE,IF(D3:D12=”乙班“,E3:E12,”“))}

其中第一个参数是指定的间隔符号，后面这个True，表示忽略空单元格，如果使用FALSE，就是表示包括空单元格。

要简化公式的话，这里的逻辑值也可以省略参数，只使用逗号占位：

{=TEXTJOIN(”,“，IF(D3:D12=”乙班“,E3:E12,”“))}

要连接各个单元格的内容，现在也变得如此简单。

只需要输入下面这个数组公式，就可以快速的将D3:F12单元格区域的内容连接到一起，并且使用顿号隔开。

{=CONCAT(D3:F12&”、")}之前对于这种问题，大家会使用PHONETIC函数来连接。但是PHONETIC函数比较挑剔，对于公式返回的结果就无能为力了，而且不支持对内存数组进行连接。现在有了CONCAT函数，一切变得那么简单。

第二篇：EXCEL2007新增的几个多条件函数

EXCEL2007新增的几个多条件函数解析

内容来自网络，在此感谢作者。整理成文档仅作学习之用。查原文请点击链接：http://softbbs.pconline.com.cn/7909652.html 或者 http://my.pconline.com.cn/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“),”<“&DATE(2007,12,5)))”COUNTIFS计算某个区域中满足多重条件的单元格数目。语法COUNTIFS(range1, criteria1,range2, criteria2„)Range1, range2, „ 是计算关联条件的 1 至 127 个区域。每个区域中的单元格必须是数字或包含数字的名称、数组或引用。空值和文本值会被忽略。

Criteria1, criteria2, „ 是数字、表达式、单元格引用或文本形式的 1 至 127 个条件，用于定义要对哪些单元格进行计算。例如，条件可以表示为

32、“"32”“、”“>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的COUNTIFS函数。如： 全科合格率:=COUNTIFS(E1:E40,“>=60”,F1:F40,“>=60”,G1:G40,“>=60”)，这里指第一个班的所有考生中三科成绩都在60分以上的人数，这里把第一个班设为第1行到第40行，E1:E40为该班语文成绩，F1:F40为该班数学成绩，G1:G40为该班英语成绩，如果科目更多可设为H1：H40，等等。如果要统计全科优秀率可把上式中的60用相关的数字替换即可。

第三篇：统计学生成绩时用Excel里函数

Excel统计学生成绩时的四个难题及解决

一、前言

对于教师而言，经常需要用Excel进行学生成绩统计，会被一些常见问题难住。碰到的难题主要有：如何统计不同分数段人数、如何在保持学号不变前提下进行排名、如何将百分制转换成不同分数段与如何用红色显示不及格的分数等，本文着重对这些问题的解决方法与技巧加以分析和讨论。

本文假设读者已对Excel的基本操作已经有一定基础，已经掌握如何进行求和、求平均和如何使用自动填充柄进行复制公式等操作，本文对这些一般性操作不做详细介绍，仅对一些难度较大的操作技巧进行讨论。

二、Excel统计学生成绩时的四个难题

这里，假设学号、姓名、成绩等列及行都已经事先输好，需要让Excel统计其他的相关数据结果。这时，成绩统计中主要难解决的问题如下：

问题1：如何统计不同分数段的学生人数? 问题2：如何在保持学号顺序不变的前提下进行学生成绩名次排定? 问题3：如何将百分制转换成不同的等级分? 问题4：如何使不及格的分数以红色显示?

三、解决统计学生成绩时的四个难题的方法

下面，针对上面提出的四个难题分别讨论解决的方法与技巧。

1、统计不同分数段的学生人数

统计不同分数段的学生人数是非常常见的需求，其所需结果如图1中A16~E16所示。这里，假设需要统计90~100、80~89、70~79、60~69及低于60分五个不同分数段的人数。

通常，统计不同分数段最好的方法是利用COUNTIF(X，Y)函数。其中有两个参数，第一个参数X为统计的范围，一般最好用绝对引用;第二个参数Y为统计条件，要加引号。

对于小于60分的人数只要用一个COUNTIF()函数，如在E16单元格中输入公式：=COUNTIF($C$2:$C$13,“<60”)。

对于其他在两个分数之间的分数段的人数统计，需要用两个COUNTIF()函数相减。如在A16单元格中输入公式：=COUNTIF($C$2:$C$13,“<=100”)-COUNTIF($C$2:$C$13,“<90”)，即用小于等于100的人数减去小于90的人数。

如果要统计80~89、70~79与60~69分数段的人数，只要利用自动填充柄将该公式复制到右边三个单元格，再把“<=100”与“<90”作相应的修改，就可以得到正确的结果。

2.保持学号顺序不变的前提下进行成绩排名

学生成绩排定在学生成绩统计中经常用到。特别要强调的是，这里所谈的方法不是一般的排序，因为那样会使学生的学号顺序发生变化。这里所需要的是在保持学号顺序不变的情况下进行学生成绩名次排定的功能，其所需结果如图1中F2~F13所示。

要进行保持学号顺序不变的情况下进行学生成绩名次的排定，最好使用RANK(X，Y，Z)函数。其中有三个参数，第一个参数X为某个学生的成绩所在单元格;第二个参数Y为整个班级成绩所在的区域;第三个参数Z是可选的，表示统计方式，若省写或写0，则成绩高的名次靠前，一般都使用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。

为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在F2单元格中输入公式：=RANK(C2,$C$2:$C$13,0)，然后，利用自动填充柄将其复制到下方的几个单元格。注意，这里$C$2:$C$13用的是绝对地址，是为了保证公式在复制时此处不变，因为作为第二个参数，这里都是指整个班级成绩所在的区域，这个区域是相同的。

3、将百分制转换成不同的等级分

将百分制转换成不同的等级分有多种不同的划分方法，其所需结果如图1中“等级1”与“等级2”列所示。这里，“等级1”列是将百分制的分数转换成A(90~100)、B(80~89)、C(70~79)、D(60~69)与E(低于60)五个等级;“等级2”列是将百分制的分数转换成优(90~100)、良(75~89)、中(60~74)与不及格(低于60)四个等级。具体使用哪种等级划分方法可根据实际情况自己确定。

在百分制转换成不同的等级分时，一般使用IF(X，Y，Z)函数。其中有三个参数，第一个参数X为条件，不能加引号;第二个参数为条件成立时的结果，如果是显示某个值，则要加引号;第三个参数为条件不成立时的结果，如果是显示某个值，同样要加引号。该函数可以嵌套，即在第二个或第三个参数处可以再写一个IF函数。

为了得到“等级1”列所要的等级结果，可以在D2单元格中输入公式：=IF(C2>=90,“A”,IF(C2>=80,“B”,IF(C2>=70,“C”,IF(C2>=60,“D”,“E”))))，然后，利用自动填充柄将其复制到下方的几个单元格。

为了得到“等级2”列所要的等级结果，可以在E2单元格中输入公式：=IF(C2>=90,“优”,IF(C2>=75,“良”,IF(C2>=60,“中”,“不及格”)))，然后，利用自动填充柄将其复制到下方的几个单元格。

4、使不及格的分数以红色显示

统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分(如第12行)。

使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。该命令会弹出一个对话框，其中要求确认条件与相应的格式。

对于“成绩”列，可先选中C2：C13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

对于“等级1”列，可先选中D2：D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。“等级2”列类似。

对于其他的一些统计计算要求，如怎样计算各分数段的百分比、如何计算机优良率与合格率等功能，应该比较简单，本文此处不赘述。