Excel中sumif函数的使用方法

Excel中sumif函数的使用方法（通用10篇）

篇1：Excel中sumif函数的使用方法

一、SUMIF函数介绍

SUMIF函数根据指定条件对若干单元格求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中，

二、SUMIF函数用法

sumif函数语法是：SUMIF(range，criteria，sum_range)

第一个参数：Range为条件区域，用于条件判断的单元格区域，

第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。

第三个参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用。

条件还可以使用通配符：问号 (?) 和星号 (*)，如需要求和的条件为第二个数字为2的，可表示为“?2*”,从而简化公式设置。问号匹配任意单个字符;星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 (~)

下面我们给大家举个例子来看看，如图所示：

篇2：Excel中sumif函数的使用方法

SUMIF函数的参数：range,criteria,sum_range

1)Range为条件区域，用于条件判断的单元格区域。

2)Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文 本。例如，条件可以表示为 32、“32”、“>32” 或 “apples”。条件还可以使用通配符，如需要求和的条件为第二个数字为2的，可表示为“?2*”,从而简化公式设置。

3)Sum_range 为实际求和区域，需要求和的单元格、区域或引用。

当省略第三个参数时，则条件区域就是实际求和区域。

criteria 参数中使用通配符(包括问号 (?) 和星号 (*))。问号匹配任意单个字符;星号匹配任意一串字符。如果要查找实际的问号或星号，请在该字符前键入波形符 (~)。

只有在区域中相应的单元格符合条件的情况下，sum_range 中的单元格才求和，

如果忽略了 sum_range，则对区域中的单元格求和。

Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算单元格区域内某个文本字符串或数字出现的次数，则可使用 COUNTIF 函数。如果要让公式根据某一条件返回两个数值中的某一值(例如，根据指定销售额返回销售红利)，则可使用 IF 函数。

excel sumif函数的用法实例：

实例：求报表中各栏目的总流量

结果如下图所示。

选中F2单元格，输入公式：=SUMIF(B2:B19,E2,C2:C19)，输入公式完成后，按键盘上ctrl+shift+enter组合键(必须按此ctrl+shift+enter组合键数组公式才能得到正确结果)，即可统计出办公软件栏目的总流量。

篇3：Excel中求和函数的使用

1 准备工作

如图所示, 先将一工作表中“性别”、“年龄”、“职务”和“分数”各列分别命名为“Sex”、“Age”、“Position”和“Mark”以方便后面使用用。命名的方法是:先选中一个区域 (比如D2:D21) , 然后在名称框中输入所要命名的名称 (如Position) 后按回车键即可。

2 求和函数

2.1 简单求和

最常用的求和函数就是“SUM”, 比如求全班的总成绩的公式就是“=SUM (Mark) ”或“=SUM (D2:D21) ”。这个函数最简单, 括号中最多可以使用以逗号相隔的30个参数, 这些参数可以是具体的数值、引用的单元格 (区域) 、逻辑值等。与“SUM”相类似的函数还有一个就是“COUNT”, 它的作用是求数值型数据的个数, 比如要求全班的人数就可使用“=COUNT (Mark) ”。这两个函数最大的区别就是前者是计算多个数值之和, 而后者是统计有多少个数值。

2.2 单条件求和

“SUM”与“COUNT”两个函数的功能较单一, 在统计时不能加条件, 为此微软还为我们提供了这两个函数的扩展函数“SUMIF”和“COUNTIF”, 也就是用来对含有条件的区域进行统计。

“COUNTIF”函数的格式为:“COUNTIF (条件区域, 条件) ”。基中的条件一般是一个不完整的逻辑表达式, 如要表示单元格的值等于某个数值则条件写成“32”或“"32"”的形式;要表示单元格的值大于某个数值则条写成“">32"”;要表示单元格的值是字符型则需在字符的两侧加“"”。例如要统计年龄等于20的人数可以使用“=COUNTIF (Age, 20) ”完成;要统计分数大于250的人数可以使用“=COUNTIF (Mark, ”>250") ”完成;要统计女生人数可以使用公式“=COUNTIF (Sex, "女") ”完成。

再例如要对学生按分数进行排名次, 比如对第一个学生的分数260排名, 那么就可以使用公式“=COUNTIF (Mark, ">"&E2) +1”来计算她在本班的成绩名次。其中的“=COUNTIF (Mark, ">"&E2) ”它表示在分数区域 (即E2:E21) 中大于E2的值有多少个, 假如说有0个, 那么它所对应的名次就应是1, 因此还得再这个的结果上再加1。这个公式的排名效果与“RANK”函数的排名效果完全相同, 对于其它同学的排名用自动填充即可完成。

“SUMIF”函数的格式为:“SUMIF (条件区域, 条件, 求和区域) ”, 其中的条件的表示方法与COUNTIF函数类似, 此函数表示只有在条件区域中相应的单元格符合条件的情况下才对求和区域中的单元格求和。比如要统计班干部的总分, 就可使用公式“=SUMIF (Position, "<>学生", Mark) ”。

2.3 多条件求和

要进行多条件求和, 使用“SUMIF“和“COUNTIF“往往是力不从心的, 这时我们必须借助Excel中提供的数组公式来完成。所谓数组公式就是用于建立产生多个结果或对可以存放在行或列中的一组参数进行运算的单个公式, 它的特点就是可以执行多重计算, 与普通的公式明显不同之处就是输入完公式后按“Ctrl+Shift+Enter“结束, 而并非按回车。

例如要统计女班干部人数, 先输入公式“=SUM ( (Sex="女") * (Position<>"学生") ) ”, 然后按下“Ctrl+Shift+Enter”组合键, 这时上面的公式就会变成“{=SUM ( (Sex="女") * (Position<>"学生") ) }”, 这就是数组公式。特别注意的是公式中的“{}”不能直接输入, 必须按“Ctrl+Shift+Enter”组合键来自动产生。

在计算机中, 逻辑真用1来表示逻辑假用0来表示, 所以在这个公式中, 两个并列条件必须用“*”来连接, 也就是说只有当两个条件同时满足时, 它们的乘积才会是1, 然后再对1的个数求和, 即可得到既是女生又是班干部的人数。再例如, 要求女班干部分数之和, 就可使用公式“{=SUM ( (Sex="女") * (Position<>"学生") *Mark}”, 表示当性别是女、职务是班干部这两个条件同时满足时记下她的成绩, 然后再对满足条件的这些成绩进行统计。根据此意也可以使用“{=SUM (IF ( (Sex="女") * (Position<>"学生") , Mark) ) }”, 表示如果性别是女且职务是班干部时, 返回她的成绩, 否则返回零, 然后再对这些成绩求和。

求女生和班干部人数之和。由于条件是“或”的关系 (在数组公式中逻辑“或”用“+”来表示) , 所以公式可写成“{=SUM (IF ( (Sex="女") + (Position<>"学生") , Mark) ) }“;求女班干部和小于20岁的女生分数之和, 相应的公式可写成“{=SUM ( (Sex="女") * (IF ( (Position<>"学生") + (Age<20) , 1) ) *Mark) }”, 在此公式中, IF函数表示当职务是班干部或者年龄小于20岁这两个条件任一个条件满足时就返回1, 否则返回0, 当IF函数返回值为1且学生的性别是女时记下她的分数, 然后再对这些分数求和。

总之, 如果条件是并列的即逻辑“与”, 各条件之间就用“*”连接, 如果条件是逻辑“或”, 各条件就用“+”连接。

如果要统计该班级职务共有多少种, 这时需用“{=SUM (1/COUN-TIF (Position, Position) ) }”公式来统计。其中的“1/COUNTIF (Position, Position) ”表示:查找每类职务各有多少人, 有N人, 就使这类职务每个人的值是1/N。比如职务是组长的共有3人, 那么每一个人的值就让它等于1/3, 然后再对这3个1/3相加, 也就是让这个职务总和是1。然后再用SUM对这多个1进行统计, 就可得出职务种类数。

3 结束语

通过以上对SUM和COUNT两个求和函数的不断变型的介绍, 就会发现Excel的功能十分强大, 如何用好它使其为我们服务、提高工作效率, 这才是王道。

摘要：Excel电子表格软件最大的好处就是统计方便, 如果能使用好其中的函数, 那么就会事半功倍, 但是函数作为电子表格的精髓却常常令使用者望而生畏。文章主要介绍了日常办公中最为常用的两个求和函数的使用方法。

关键词：求和,函数,公式

参考文献

[1]宋燕福, 高加琼.在Excel中求和函数的研究与应用[J].四川职业技术学院学报, 2011, 06.[1]宋燕福, 高加琼.在Excel中求和函数的研究与应用[J].四川职业技术学院学报, 2011, 06.

篇4：Excel中sumif函数的使用方法

关键词:ExcelCONCATENATE函数MIDB函数 IF函数

1引言

Excel是一款得到广泛应用的优秀电子表格软件,在Excel的“分析工具库”中包括了许多非常实用的函数,利用这些函数可以帮助我们方便地解决许多Excel实际应用中的难题。但它的疑难和奥妙也一直困扰着无数用户,致使许多用户尽管使用了多年仍不能熟练掌握该软件的某些具体功能,下面详细介绍Excel的几个函数的使用方法和技巧,希望能对使用者有所帮助。

2利用身份证号码获取公民出生日期

在单位上整理人事信息时,常常即需要输入身份证号码,又要输入出生年月日,这样即工作量较大而且易出现错误。由于身份证号码内含有相关信息,我们可以使用Excel函数从身份证号号码中直接提取出生年月日,这样即减少工作量又可提高准确度。

从图1中我们可以发现我国现行的身份证号码的规律,从左起第7—14个字符代表了公民的出生年月日,我们可以使用MIDB函数从身份证号码的特定位置,提取出生年月日,再使用CONCATENATE函数将所提出来的文字合并起来,就可以得到相对应的出生年月日。

在图2中是已经制作好的一张人员信息表,所需的三个MIDB函数操作对象存放在D3单元格中,分别从左起第7个字符开始提取,即第7个字符开始连续取4个字符即得到“年”,第11个字符开始连续取2个字符得到“月”,第13个字符开始连续取2个字符得到“日”,最后由CONCATENATE函数将所有字符合并起来。即得到我们所需要的信息。

从以上分析可以得出公式:=CONCATENATE(MIDB(D3,7,4),"年", MIDB(D3,11,2),"月", MIDB(D3,13,2),"日")

CONCATENATE是将若干个文字项合并至一个文字项中的函数。其语法格式是:CONCATENATE(text1,text2……)。

MIDB从一个文本字符串的指定位置开始,返回指定个数的子字符串。其语法格式是:MIDB(text,start_num, mum_bytex)

用户要注意的是:CONCATENATE函数和MIDB函数的操作对象均为文本,所以存放身份证号码的单元格必须设置为文本格式,然后再输入身份证号。

3用IF函数计算成绩等级

在教师的日常工作中,会遇到要将学生的考试成绩按实际考试分数转换成相应成绩等级的情况。利用IF函数就能够迅速完成对学生的考试分数转换成相应成绩等级的工作。

成绩等级评定一般的标准为:考试分数大于或等于85分为优,分数大于或等于80分为良,分数大于或等于70分为中,分数大于或等于60分为及格,低于60分为不及格,没有参加考试的不划等级。

从图3中可以看出分数存放在D列,计算结果存入E列。用Excel的IF函数计算公式如下:

=IF(D3>=85,"优",IF(D3>=80,"良",IF(D3>=70,"中",IF(D3>=60,"及格",IF(ISNUMBER(D3),"不及格",IF(ISBLANK(D3),"未考试"))))))

上面的公式中主要是利用IF条件函数的嵌套。IF函数是根据对指定条件的逻辑判断的真假结果,返回相对应的内容的条件函数。利用此函数逻辑计算出真假值,会返回不同的结果,从而可达到筛选数据的目的。其语法格式是:IF(logical_test,value_if_true,value_if_false), logical_test:表示计算结果为TRUE或者FALSE的任意值或表达式,该参数可使用任何比较运算符 ;value_if_true:表示logical_test为TRUE(也就是真或正确)时返回的值;value_if_false:表示logical_test为FALSE(也就是假或错误)时返回的值。

公式中的第二个IF语句是第一个IF语句的参数,第三个IF语句则是第二个IF语句的参数,以此类推。如果第一个逻辑判断表达式D3>=85为TRUE(真),则E3单元格被填入“优”;如果第一个逻辑判断表达式D3>=85不成立,则计算第二个IF语句IF(D3>=70,以此类推直到计算结束。公式中ISNUMBER函数在D3为空时返回FALSE(假),接着执行最后一个IF語句,否则在D3单元格中填入“不及格”。ISBLANK函数D3为空时返回TRUE(真),D3单元格被填入“未考试”。使用ISNUMBER函数和ISBLANK函数,可防止某个未参加考试的学生(成绩为空时)给他评定为“不及格”的情况。

4结束语

本文通过实例,详细介绍了CONCATENATE函数、MIDB函数以及IF函数的使用技巧。对使用者熟练掌握理解这几个函数有所帮助。

参考文献:

[1]施威铭研究室著,完全掌握OFFICE XP中文版标准教程,中国青年出版社,2002年。

[2][美]John Walkenback著,尚红昕,刘玲等译,Excel应用技巧宝典,电子工业出版社,2006年。

篇5：sumif函数的使用方法

1 SUMIF函数的一个怪异用途(excel)

Excel表格中的SUMIF函数在条件求和时经常使用，我们一般习惯于于根据某一列计算另一列的数字之和。

例1：假设置A列是部门，B列是姓名，C列是工资，可以用下面的公式算出所有财务部员工的工资总和：

=SUM(A1：A100，“财务部”,C1:C100)

我们今天说的不是这个正规用法，而是给大家介绍一种看上去和SUMIF毫无一毛钱关系的例子。

例2 如下图中，是一些不规则的数据，我们只关注填充颜色的区域，现在我们要计算所有A下面数字之和，看上去这个题没有好的思路，其实这个题恰好可以用SUMIF函数轻松解决。

=SUMIF(B2:E7,B12,B3:E8)

我们分析一下这个公式，第二个参数是条件我们可以了解，第一个参数和第二个参数有什么关系呢?大家仔细看会发现第三个参数正好是第一个参数的区域向下偏移一个单位。

总结，SUMIF函数并不是只对规则区域求和，还可以对不规则的排列进行条件求和

SUMIF入门篇

SUMIF作为Excel中一个条件求和函数，在实际工作中发挥着强大的作用，虽然在以后被SUMIFS所取代，但它依旧是一个EXCEL函数的经典。特别是高级用法，依旧适用于后面的版本。本文由兰色幻想原创，时请注明转自“Excel精英培训”。

SUM是求和，IF是如果。如果什么。。就求和，其实就是按条件求和。它的结构如下：

SUMIF(条件判断区域,条件，求和区域)

说明：

1 条件判断区域。即然是条件求和，一是要有条件，二是要有判断的区域，SUMIF的第二个参数就是求和的条件，第一个参数呢就是拿条件来这个区域进行对比的区域。第一个参数必须是单元格引用。

2、条件。按条件求和就得有条件，该条件可以是字符串(“ABC”，可以用大于等对比符号连接起来的条件表达式(“>100”)，也可以使用通配符来表示匹配求和(“AB*C”)。

3、求和区域。求和函数可以没有求和的区域算什么求和函数，该参数为单元格区域引用。如C2：C100。

4、如果求和区域和判断区域是一个区域该怎么办呢?如在C2：C100中计算大于100的数字之和。在这种情况下SUMIF函数由三个参数变换为两个参数。即：

SUMIF(求和区域，条件)

例1;如下图所示。要求根据左表中的商家明细表，生成右侧的汇总表，汇总出商家的总进货量和总金额。

公式：

=SUMIF($A$2:$A$8,$F3,C$2:C$8)

公式说明：

$A$2:$A$8是判断区域，拿F3的商家名称在这个区域进行判断是否相同，如果上同，就把C2：C8区域的金额进行求和。

如果只按C列进判断求和呢?比如，计算C列金额大于200的金额之和。公式可以变换为：

SUMIF(C$2:C$8,“>200”)

关于入门级的SUMIF函数用法就介绍到这里了，进一步的用法将在“初级篇”中进行详细介绍，包括条件参数的组合、条件参数的模糊设置等。敬请关注。

二、技巧篇

我们在学习了入门篇后，在常用技巧篇中将学习SUMIF常用的一些技巧。

谈到隔列求和，可能就会想到用数组公式，其实只需要用SUMIF函数就可以轻松实现。

例：计算费用表中的计划和实际合计数。

公式;=SUMIF(B$2:G$2,B$2,B3:G3)

提示;SUMIF函数不只是左右示和，还可以上下求和。

三进阶篇

SUMIF函数一般情况下只能进行一个条件的求和，但在二般情况下却可以实现对多个条件的求和。什么是二般情况下呢?

SUMIF函数的第二个参数是“求和的条件”，这个参数可以用数组形式。比如

{“A”,“B”} {“>5”,“<10”}

条件使用数组形式后，SUMIF会分别根据两个条件计算出结果，然后再用SUM计算出结果。

一、对多个商品进行求和

例：如下图所示。要求计算商品名称为手机和冰箱的销售之和。

公式：=SUM(SUMIF(A1:A9,{“手机”,“冰箱”},C2:C9))

公式说明：

SUMIF(A1:A9,{“手机”,“冰箱”},C2:C9) 会分别计算出手机的销量(16)和与冰箱的销售量(14)。返回一个数组，即：{16,14}，

SUM:对SUMIF返回的两个销售量进行二次求和。

二、按数字区间求和

例：如图一所示。要求计算销售数量大于等5，小于10的销售数字之和。

公式：=SUM(SUMIF(C2:C9,{“<10”,“<5”})*{1,-1})

公式说明：

SUM(SUMIF(C2:C9,{“<10”,“<5”})：因为大于等5，小于10的区间和可以用<10的和减去<5的和。所以先分别计算出这两个区间的和{21,10}

篇6：Excel中sumif函数的使用方法

(1) 功能:计算包含数字的单元格个数以及参数列表中数字的个数。 (2) 语法:COUNT (value1, [value2], ...) 。value1必需。要计算其中数字的个数的第一个项、单元格引用或区域。value2, ...可选。要计算其中数字的个数的其他项、单元格引用或区域, 最多可包含255个。 (3) 说明:参数可以包含或引用各种类型的数据, 但只有数字类型的数据才被计算在内。这里的数字类型的数据包含数字、日期、逻辑值和直接键入到参数列表中代表数字的文本被计算在内。如果参数为错误值, 则不会被计算在内。如图1所示。

上述例子中只有日期“2015/12/3”与数值“5.5”进入计数当中, 若要计算逻辑值、文本值或错误值的个数, 就要使用COUNTA函数。

2 COUNTA函数

(1) 功能:计算区域中不为空的单元格的个数。

(2) 语法:COUNTA (value1, [value2], ...)

value1必需。表示要计数的值的第一个参数。

value2, ...可选。表示要计数的值的其他参数, 最多可包含255个参数。

(3) 说明:COUNTA函数可对包含任何数据类型的单元格进行计数, 包括逻辑值、错误值和空文本 ("") 。COUNTA函数不会对空单元格进行计数。如图2所示。

上述例子中只有A3空单元格没有进入计数当中, 其他各种数值的单元格都进入了计数。

3 COUNTIF函数

(1) 功能:计算区域中满足单个指定条件的单元格个数。

(2) 语法:COUNTIF (range, criteria) 。

range必需。要对其进行计数的一个或多个单元格, 其中包括数字或名称、数组或包含数字的引用。空值和文本值将被忽略。

criteria必需。用于定义满足的单个条件, 条件可以为数字、表达式、单元格引用或文本字符串, 。例如, 条件可以表示为32、“>32”、B4、“苹果”。如图3所示。

x上述公式中, “财务科”也可以换为单元格名称B3, 完整公式为=countif (b2:b9, b3) , 结果也是一样。

(3) 说明:条件不区分大小写;例如, 字符串“apples“和字符串“APPLES”将匹配相同的单元格。

在条件中可以使用通配符, 即问号 (?) 和星号 (*) 。问号匹配任意单个字符, 星号匹配任意一系列字符。若要查找实际的问号或星号, 请在该字符前键入波形符 (~) 。如图4所示:

4 结语

由上述例子我们可以看出, 对全是数字型数据计数使用count函数, 有多种类型的数据使用counta函数, 对有单个条件进行计数选择countif函数。

摘要：随着办公自动化的普及, excel的运用越来越频繁, 函数作为excel处理数据的一项重要手段, 功能是十分强大的。而计数函数为函数中最基本、最常用的函数之一, 多个计数函数之间有什么区别, 如何选择, 笔者就这个问题提出了自己的观点。Microsoft Excel经常使用到计数函数, 而Excel中的计数函数包括了count、counta以及countif, 这三个函数有什么区别, 我们在计数的时候选择哪一个函数?下面我们来分别看看他们的功能与语法。

关键词：excel,函数,计数

参考文献

篇7：EXCEL中查找函数的妙用

下面介绍几个在实际工作中可能会经常用到的查找函数：MATCH()、LOOKUP()、HLOOKUP()、VLOOKUP(),这些查找函数不仅仅具有查对的功能，同时还能根据查找的结果和参数的设定得到我们需要的数值。特别是这几个函数的配合使用，并以两个逻辑函数IF()和ISERROR()辅助,我们就可以在两个或多个有一定关联的工作簿中动态生成新的数据列。

1．MATCH()

格式：MATCH(lookup_value,lookup_array,match_type)

从英文含义我们可以看出，它是一个匹配的函数，主要功能是按照match_type参数所指定的查找方式，在lookup_array参数所代表的数据组中查找lookup_value参数代表的数值,如果查找成功则返回lookup_value在lookup_array中的位置,不成功返回错误信息#N/A。

EXCEL在“编辑”菜单中提供的“查找”功能只能进行单一数值的查找，而MATCH()函数可以实现批量数值的查找，所需要的只是在需要查找的首个单元格中定义好该函数，然后拖动该单元格直至到需要的范围，所有需要查对单元格的查找结果会自动完成。

参数解释：

lookup_value——需要查找的值，可以是实际的数字、字符串或逻辑值，也可以是某一单元格的引用，通常使用单元格的引用；

lookup_array——查找范围，可以是定义的数组，也可以是自定义的多个连续单元格的引用，这个引用我们可以通过两种方式实现：

 直接输入连续单元格的地址，通常使用绝对引用地址，如$B$2:$B$50；

 选定一个连续的单元格区域后，通过“插入”菜单中“定义”项的“名称”给这段单元格区域定义一个名称，如NAME1,这样在以后所有使用该单元格区域的时候都可以用定义的名称代表。

match_type，查找的方式，其值为-1、0、1，默认为1。其中-1是在lookup_array查找大于或等于lookup_value的最小数值，要求lookup_array必须按降序排列；0是在lookup_array查找等于lookup_value的第一个数值，不需要排序；1是在lookup_array查找小于或等于lookup_value的最大数值，要求lookup_array必须按升序排列。

函数举例：

例1：有两个工作簿 BOOK1和BOOK2，它们的SHEET1中都具有名为“卡片号”的列，列标为B（假设BOOK1中该列包含数据的单元格为B2:B80，BOOK2中包含数据的单元格为B2:B1000），想查对一下BOOK1中的卡片号是否能够在BOOK2中的卡片号中全部查找到。

按照以上的需求我们可以进行如下的操作：

(1)定义引用的名称，拖动鼠标选中BOOK2中B2:B1000单元格，通过“插入”菜单的“定义”“名称”项，给其定义为NAME1；

(2)在BOOK1中插入一空列，假定列标为H，并在H2单元格中输入如下的公式：

=MATCH(B2,“BOOK2.XLS”!NAME1,0)；

(3)选中H2单元格，将鼠标指针移动至该单元格右下角的黑点处，此时指针变为小黑十字形状，按住左键，拖动鼠标至H80单元格，此时H2到H80单元格会自动显示出B2至B80在BOOK2中定义的NAME1所代表的单元格区域的位置（如果找到），或是#N/A（未找到）；

(4)通过“数据”菜单的“筛选”“自动筛选”功能项，可以查看所有匹配的项（H列的数值大于0），或是所有不匹配的项（H列的数值为#N/A）。

以上1、2步骤可以合并为直接在H2单元格中输入如下的函数：

=MATCH(B2,[BOOK2.XLS]SHEET1!$B$2:$B$1000,0)

需要注意的是，一定要使用$B$2:$B$1000，不能使用B2:B1000，否则在拖动鼠标时，其单元格范围会随着发生变化，产生错误的结果。另外在引用外部工作簿时，如果定义了引用名称，书写格式为：“工作簿名称”！引用名称，如“BOOK2.XLS”!NAME1；如果使用表格区域引用，格式为：[工作簿名称]工作表名称！表格区域，如[BOOK2.XLS]SHEET1!$B$2:$B$1000。

2．LOOKUP()、HLOOKUP()、VLOOKUP()

LOOKUP()、HLOOKUP()、VLOOKUP()函数的功能都是在数组或表格中查找指定的数值，并按照函数参数设定得值返回表格或数组当前列（行）中指定行（列）处的数值。

由于LOOKUP()函数在单行（列）区域查找数值，并返回第二个单行（列）区域中相同位置的数值，或是在数组的第一行（列）中查找数值，返回最后一行（列）相同位置处的数值，其适用范围具有比较大的局限性，在实际的应用中，通常使用更加灵活的HLOOKUP()和VLOOKUP()函数。

HLOOKUP()和VLOOKUP()的作用类似，其区别是HLOOKUP()在表格或数组的首行查找数值，返回表格或数组当前列中指定行的数值，而VLOOKUP()是在表格或数组的首列查找数值，并返回表格或数组当前行中指定列的数值。这里所说的表格是按单元格地址设定的一个表格区域，如A2:E8。

HLOOKUP()函数的格式如下：

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

参数解释：

lookup_value——需要在表格或数组第一行中查找的数值，可以是数值、字符串或引用；

table_array——需要在其中查找数值的表格区域、数组或是表格区域的引用；

row_index_num——为 table_array 中待返回的匹配值的行序号；

range_lookup——为一逻辑值，为TRUE或省略该值时，要求table_array第一行的数据必须升序排列，否则会得到错误的结果，同时表示待查找内容与查找内容近似匹配就可以了，如果不能精确匹配的话，则函数返回小于lookup_value的最大数值；如果为FALSE，不需要table_array的数值进行排序，并要求精确匹配，如果没有找到则函数返回#N/A。

VLOOKUP（）函数的格式如下：

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

函数的参数中除了col_index_num表示table_array 中待返回的匹配值的列序号外，其他参数的意义和HLOOKUP()相同。

函数举例：

例2：在BOOK2中，需要根据每一行中的资产类别（列标为D），自动的计算出该资产的折旧年限。

我们可以进行如下的操作：

(1)由于在现有的表格当中不存在资产类别和折旧年限的对应关系，因此首先要根据资产分类的情况构造出一个数组，数组的第一行为资产的类别名称，第二行为资产所对应的折旧年限，{“类别1”，“类别2”，“类别3”，“类别4”，“类别5”；4，6，7，8，10}；

(2)在BOOK2的SHEET1中插入一空列，列标为K，在K2单元格中输入如下的公式：

=HLOOKUP(D2, {“类别1”，“类别2”，“类别3”，“类别4”，“类别5”；4，6，7，8，10},2,FALSE),

(3)选中K2单元格，将鼠标指针移动至该单元格右下角的黑点处，指针变为小黑十字形状时，按住左键，拖动鼠标至K80单元格,折旧年限会自动地显示出来。

例3：按照BOOK1 中SHEET1的卡片号（列标为B）从BOOK2中SHEET1的型号列（列标为E）获取与该卡片号对应的内容，从而在BOOK1的SHEET1中自动的生成一个新列（列标为U）。

解决方法

(1)由于是在列中查找匹配的值，需要返回当前行制定列的值，因此适宜使用VLOOKUP（）函数，确定需要查找的数值为从BOOK1/SHEET1的B2到B80，待查找的范围为BOOK2/SHEET1的B2：E1000,返回匹配值的列序号为4。

(2)在BOOK1/SHEET1的U2单元格，输入如下的公式：

=VLOOKUP（B2,[BOOK2.XLS]SHEET1!$B$2:$E$1000,4,FALSE）

(3)选中U2单元格，将鼠标指针移动至该单元格右下角的黑点处，指针变为小黑十字形状时，按住左键，拖动鼠标至U80单元格,对应的型号会在U列中显示出来。

3．IF（）

格式：IF(logical_test,value_if_true,value_if_false)

IF函数的作用是根据逻辑判断的结果返回相应的值

参数解释：

logical_test——逻辑表达式，

value_if_true——结果为TRUE时，函数返回的值;

value_if_false——结果为FALSE时，函数返回的值;

value_if_true和 value_if_false都可以是其他的公式，也可以是IF函数的嵌套进行多级判断，嵌套不能超过7层。

当逻辑表达式包含有函数的时候，可以使用ISERROR（）函数来判断函数返回的结果是否是错误值（包括#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。

ISERROR()函数的格式为ISERROR(value),其中参数value为要检测的值。

函数举例：

例4：在例1的基础上，如果找到卡片号，则在BOOK1的SHEET1的N列的相应行处显示“相符”，否则显示“未发现”。

解决办法：

在BOOK1的SHEET1的N2单元格处输入如下的公式：

=IF(ISERROR(MATCH(B2,“BOOK1.XLS”!NAME1,0))=FALSE,“相符”，“未发现”)

在实际的工作中，可能会先判断一下某个单元格中的数值（数字、文本或引用）在另外的一个工作簿中是否存在相应的匹配数值，如果找到匹配值则进行下一步的计算，如果没有可能要以某些值代替。在这种情况下，我们就可以配合使用多个函数来实现这个目的。下面我们通过一个例子进行说明。

例5：从BOOK2/SHEET1中查找与BOOK1/SHEET1的卡片号相匹配的行，并将该行中“购置日期”列（列标为F）的数值显示在BOOK1/SHEET1的G列中相应的单元格中，要求如果没有找到匹配的数值，则在BOOK1/SHEET1的G列的相应单元格中填写“1996/1/1”。

我们可以进行如下的操作：

(1)在G2单元格中输入如下的公式：

=IF(ISERROR(MATCH(B2,[BOOK2.XLS]SHEET1!$B$2:$B$1000,0))=FALSE,VLOOKUP(B2,[BOOK2.XLS]SHEET1!$B$2:$F$1000,5,FALSE),“1996/1/1”)

(2)选中G2单元格，将鼠标指针移动至该单元格右下角的黑点处，指针变为小黑十字形状时，按住左键，拖动鼠标至G80单元格,对应的购置日期会在G列中显示出来。

在上述公式中, ISERROR(MATCH(B2,[BOOK2.XLS]SHEET1!$B$2:$B$1000,0))作为一个逻辑表达式，判断是否在BOOK2/SHEET1的B列中找到匹配的数值，如果结果为FALSE,表明找到匹配的值，进而IF（）函数计算VLOOKUP(B2,[BOOK2.XLS]SHEET1!$B$2:$F$1000,5,FALSE)，也就是将BOOK2/SHEET1中相匹配的行中F列的内容赋值给G2；若为TRUE,表示没有找到,G2的值为1996/1/1,以此类推。

姓 名：孟海江

工作单位：沧州供电公司

通信地址：河北省沧州市北环西路8号沧州供电公司信息中心

邮政编码：061001

联系电话：0317-2195487 , 13513176176

电子信箱：czdl_mhj@sohu.com czm_hj@tom.com

篇8：Excel中sumif函数的使用方法

●代替表面简单实则费事的“筛选”

每年进行两次的河北省学业水平考试, 成绩一旦下发, 学校要做的不仅是及时告知学生本人成绩, 更重要的是分析出每个班各科的得A、B、C及D的比率, 以便根据学生的通过情况安排接下来的课表。要分析出每个班各科目A、B、C、D四个等级各有多少人, 我们首先想到的就是利用筛选功能。但如果使用筛选功能, 每个班10个科目, 每个科目4个等级, 一个班就要进行40次筛选。

如果我们使用IF函数来做, 就精简得多了。首先, 将全部成绩按照班级进行升序排序。然后在每个科目后面插入3列空白列 (如图1) 。

将政治成绩复制到本列后面新增加的3列空白列中, 各科成绩依次进行同样的复制, 这样4列一模一样的数据显示一科成绩 (如图2) 。

在当前工作表后面插入一个工作表, 命名为“计算”。将第一个工作表“chj”中前3列的数据复制到“计算”工作表中, 再将第一个工作表“chj”中的第一行即表格的字段名复制到“计算”工作表中, 也就是除了考试等级外都原位不动地复制到“计算”工作表中。这样是为了后面的操作不会错位。然后对字段名进行简单的修改 (如图3) 。

在D 2单元格输 入公式“=IF (chj!D2="A", 1, 0) ”, 并向下拉动填充柄。在E3单元格输入公式“=IF (chj!D2="B", 1, 0) ”, 依次类推。这样在“政治A”列中, 考生的成绩为A的显示为1, 不为A的显示为0。同理, 在“政治B”列中, 考生的成绩为B的则显示为1, 不为B的则显示为0, 各列显示如图4所示。然后再按照班级字段进行“求和”的分类汇总即可求出各班各科各等级的人数。

●化繁为简, 用IF函数完成计数工作

一个考生的成绩排在年级前60%的可以算作优秀, 每个班的优秀数是每次考试成绩出来后大家关心的问题。在成绩文件里, 我们在后面插入两个工作表, 一个叫“年级排名”, 另一个叫“前60%人数”。将工作表1“成绩”中的考生号列、姓名列、字段名行原位不动复制到工作表2“年级排名”中和“前60%人数”中。在工作表2“年级排名”中的D2单元格输入公式“=RANK (成绩!D2, 成绩!D$2:D$933) ”, 并纵向拖动D2单元格的填充柄, 这样就得出每个考生每科的年级排名。然后横向拖动D2单元格的填充柄, 得出每个考生各科的年级排名 (如图5) 。

在工作表3“前60%人数”的D2单元格输入公式“=IF (年级排名!D2<=932*0.6, 1, 0) ”其中932为年级参考人数。这样成绩在前60%的考生, 就会标记为数值“1”, 不在前60%的考生, 会标记为数值“0”。然后分别向下、向右拖动填充柄, 就会给每个考生的每科成绩做好标记 (如图6) 。

再按照“班级”分类, “求和”的汇总方式, 对各科进行分类汇总, 就会得出各班名次在前60%的人数分别为多少 (如图7) 。

篇9：Excel中sumif函数的使用方法

关键词：学籍管理 Excel函数 应用

学籍管理是每个学校都必须进行的一项繁琐事务。随着计算机的广泛应用和Office办公自动化软件的使用，目前学生的学籍信息一般都使用电子文档存储，以便上交到上级管理部门审核。通常，学籍管理员会根据学籍信息特点和相关要求使用 Excel软件输入和存储管理信息，日常的学籍管理工作，如注册、修改、添加以及提取等，需要经过多次重复操作才能完成。如何让这些工作变得更轻松、省时？可以运用Excel提供的内置函数来帮助提高工作效率。

Excel软件最突出的特点就是具有数据自动处理能力，利用公式计算、公式的复制以及公式的重新计算功能，可以使复杂的事情简单化，特别是Excel提供的内置函数能更方便快捷地完成相同的工作。函数是Excel中预定义好的公式，共有200多个。在学籍管理的日常事务中，灵活选用其中内置的函数，实现对学籍信息的输入和日常处理，可以达到事半功倍的效果。

下面笔者从学籍注册、技能抽考及成绩评定这三个方面介绍Excel部分函数的功能与应用。

一、学籍注册

图1是从各班级收集的纸质数据表。

图1

根据Excel软件所具有的数据处理功能，从上图分析得出，学生的“身份证号”字段中包含了“性别”和“出生年月日”的信息，这样“性别” 和“出生年月日”两列的数据就不用再输入。

首先，新建Excel工作簿，在Sheet1工作表中创建一个电子注册表，在表中输入标题行内容：班级学号、姓名、身份证号、性别、出生日期、专业、班级等。在数据区域输入如图2所示的数据即可，其中A列、F列和G列剩余的数据采用填充柄的复制功能完成。

图2

第二，利用Mid函数（字符串截取函数）从“身份证号”中计算出“出生年月日”。Mid函数的功能是返回文本字符串中从指定位置开始的特定数目的字符。身份证号中第7位至第14位这8个数据代表学生的出生年月日。

例如：计算图2的1号同学的“出生年月日”，在E2结果单元格中，输入：

=MID（C2，7，8）

注：C2为引用身份证号码（数字字符）所在单元格，E列其余的学生的信息依然使用填充柄的复制功能完成。

第三，利用Mod函数（取余函数）计算出第17位数是奇数还是偶数。Mod函数的功能是返回两数相除的余数。身份证号的第17位代表了一个人的性别是“男”还是“女”，分别用奇数、偶数表示，奇数1、3、5、7、9表示男性，偶数0、2、4、6、8表示女性。即：

=MOD（第17位数，2）

第四，利用函数嵌套从“身份证号”中计算出“性别”。函数的嵌套是指一个函数中套入了另一个函数，函数的值作为另一个函数的参数。函数嵌套的层数最多可以达到7层。

由此得出：先用Mid取中间字符串函数，取出第17位的数字字符；再用数学函数Mod取余函数，计算该数字的奇偶性；最后是逻辑函数If条件函数，判断出是“男”性还是“女”性。

例如：计算图2的1号同学的性别，在D2结果单元格中，输入：

=IF（MOD（MID（C2，17，1），2）=0，"女"，"男"）

二、技能抽考

每年的技能抽考是湖北省检测职业院校学生学习情况的规定项目。出于公平公正的原则，笔者采用随机抽测学号的方式进行。例如一个专业注册60人，抽测10%，即随机抽取6人的学号。在Excel中随机函数Rand（ ）可实现这一功能。使用Rand（ ）1次，然后对生成的数进行增倍、取整等处理，即可抽出一个学号，再使用填充柄的复制功能，6单元格即可搞定。

随机函数Rand（ ）的功能是：返回大于等于0及小于1的均匀分布随机数，即产生出[0，1）之间的数：0或者一些小数。从抽测的学号分析，单独使用的Rand（ ）函数所产生的是[0，1）之间的数，不符合“学号”要求（以“学号”为1、2、……60整数为例，没有0），故采用以下方法解决此问题。

首先，随机函数乘以合适的数值型系数A，随机数将产生于0到A区间，即[0，A）。如Rand（ ）*60 函数，将产生大于等于0且小于60的一个随机数。因此通过乘以不同大小的系数，可以产生不同区间的随机数。

然后，配合取整函数，保证随机数为整数型数据项。例如Int（Rand（ ）*60）函数功能将产生0至60间的随机整数，即[0，60）的随机整数。包含了0，而不包含60。由于学号是从1到60号，在上面的基础上加上数字1即可满足抽取的要求。

举例：在“抽测学号”第一个结果单元格中，输入：

=INT（RAND（ ）*60）+1

三、成绩评定

近几年来，湖北省要求职业院校的学生取得英语、计算机等级证才能获得毕业证。如果学生英语、计算机两者有一项成绩不合格（无等级证），则不颁发毕业证。根据这一情况，可以把符合条件的标记为“发”，不符合条件的不作标记。

现假设学生英语、计算机考试状态如图3所示。

图3

从上表分析可知，每一种成绩有三种状态：“优秀”“合格”和“不合格”。

又已知两门成绩中只要有一门标记为“不合格”，即不发毕业证，其余情况均发。可以先用OR（ ）逻辑或函数来判断。OR（ ）函数的功能是任一参数为TRUE时，则结果为TRUE。再用IF嵌套。

举例：在J2单元格中，输入：

=IF（（OR（H2="不合格"，I2="不合格"）），""，"发" ）

计算结果如图4所示。

图4

综上所述，我们不难发现，Excel的内置函数具有强大功能。只要灵活运用Excel函数，发挥其作用，学籍管理工作效率将会大幅提高。

参考文献：

[1]叶亚平.计算机应用基础[M].北京：原子能出版社，2009.

[2]周和平.对口招生考试教程（计算机应用类）[M].北京：国防科技大学出版社，2007.

（作者单位：湖北工业大学）endprint

摘 要：本文以学籍注册、技能抽考及成绩评定三项学籍管理日常事务为例，探讨了如何通过灵活选用Excel内置的函数，实现对学籍信息高效管理。

关键词：学籍管理 Excel函数 应用

学籍管理是每个学校都必须进行的一项繁琐事务。随着计算机的广泛应用和Office办公自动化软件的使用，目前学生的学籍信息一般都使用电子文档存储，以便上交到上级管理部门审核。通常，学籍管理员会根据学籍信息特点和相关要求使用 Excel软件输入和存储管理信息，日常的学籍管理工作，如注册、修改、添加以及提取等，需要经过多次重复操作才能完成。如何让这些工作变得更轻松、省时？可以运用Excel提供的内置函数来帮助提高工作效率。

Excel软件最突出的特点就是具有数据自动处理能力，利用公式计算、公式的复制以及公式的重新计算功能，可以使复杂的事情简单化，特别是Excel提供的内置函数能更方便快捷地完成相同的工作。函数是Excel中预定义好的公式，共有200多个。在学籍管理的日常事务中，灵活选用其中内置的函数，实现对学籍信息的输入和日常处理，可以达到事半功倍的效果。

下面笔者从学籍注册、技能抽考及成绩评定这三个方面介绍Excel部分函数的功能与应用。

一、学籍注册

图1是从各班级收集的纸质数据表。

图1

根据Excel软件所具有的数据处理功能，从上图分析得出，学生的“身份证号”字段中包含了“性别”和“出生年月日”的信息，这样“性别” 和“出生年月日”两列的数据就不用再输入。

首先，新建Excel工作簿，在Sheet1工作表中创建一个电子注册表，在表中输入标题行内容：班级学号、姓名、身份证号、性别、出生日期、专业、班级等。在数据区域输入如图2所示的数据即可，其中A列、F列和G列剩余的数据采用填充柄的复制功能完成。

图2

第二，利用Mid函数（字符串截取函数）从“身份证号”中计算出“出生年月日”。Mid函数的功能是返回文本字符串中从指定位置开始的特定数目的字符。身份证号中第7位至第14位这8个数据代表学生的出生年月日。

例如：计算图2的1号同学的“出生年月日”，在E2结果单元格中，输入：

=MID（C2，7，8）

注：C2为引用身份证号码（数字字符）所在单元格，E列其余的学生的信息依然使用填充柄的复制功能完成。

第三，利用Mod函数（取余函数）计算出第17位数是奇数还是偶数。Mod函数的功能是返回两数相除的余数。身份证号的第17位代表了一个人的性别是“男”还是“女”，分别用奇数、偶数表示，奇数1、3、5、7、9表示男性，偶数0、2、4、6、8表示女性。即：

=MOD（第17位数，2）

第四，利用函数嵌套从“身份证号”中计算出“性别”。函数的嵌套是指一个函数中套入了另一个函数，函数的值作为另一个函数的参数。函数嵌套的层数最多可以达到7层。

由此得出：先用Mid取中间字符串函数，取出第17位的数字字符；再用数学函数Mod取余函数，计算该数字的奇偶性；最后是逻辑函数If条件函数，判断出是“男”性还是“女”性。

例如：计算图2的1号同学的性别，在D2结果单元格中，输入：

=IF（MOD（MID（C2，17，1），2）=0，"女"，"男"）

二、技能抽考

每年的技能抽考是湖北省检测职业院校学生学习情况的规定项目。出于公平公正的原则，笔者采用随机抽测学号的方式进行。例如一个专业注册60人，抽测10%，即随机抽取6人的学号。在Excel中随机函数Rand（ ）可实现这一功能。使用Rand（ ）1次，然后对生成的数进行增倍、取整等处理，即可抽出一个学号，再使用填充柄的复制功能，6单元格即可搞定。

随机函数Rand（ ）的功能是：返回大于等于0及小于1的均匀分布随机数，即产生出[0，1）之间的数：0或者一些小数。从抽测的学号分析，单独使用的Rand（ ）函数所产生的是[0，1）之间的数，不符合“学号”要求（以“学号”为1、2、……60整数为例，没有0），故采用以下方法解决此问题。

首先，随机函数乘以合适的数值型系数A，随机数将产生于0到A区间，即[0，A）。如Rand（ ）*60 函数，将产生大于等于0且小于60的一个随机数。因此通过乘以不同大小的系数，可以产生不同区间的随机数。

然后，配合取整函数，保证随机数为整数型数据项。例如Int（Rand（ ）*60）函数功能将产生0至60间的随机整数，即[0，60）的随机整数。包含了0，而不包含60。由于学号是从1到60号，在上面的基础上加上数字1即可满足抽取的要求。

举例：在“抽测学号”第一个结果单元格中，输入：

=INT（RAND（ ）*60）+1

三、成绩评定

近几年来，湖北省要求职业院校的学生取得英语、计算机等级证才能获得毕业证。如果学生英语、计算机两者有一项成绩不合格（无等级证），则不颁发毕业证。根据这一情况，可以把符合条件的标记为“发”，不符合条件的不作标记。

现假设学生英语、计算机考试状态如图3所示。

图3

从上表分析可知，每一种成绩有三种状态：“优秀”“合格”和“不合格”。

又已知两门成绩中只要有一门标记为“不合格”，即不发毕业证，其余情况均发。可以先用OR（ ）逻辑或函数来判断。OR（ ）函数的功能是任一参数为TRUE时，则结果为TRUE。再用IF嵌套。

举例：在J2单元格中，输入：

=IF（（OR（H2="不合格"，I2="不合格"）），""，"发" ）

计算结果如图4所示。

图4

综上所述，我们不难发现，Excel的内置函数具有强大功能。只要灵活运用Excel函数，发挥其作用，学籍管理工作效率将会大幅提高。

参考文献：

[1]叶亚平.计算机应用基础[M].北京：原子能出版社，2009.

[2]周和平.对口招生考试教程（计算机应用类）[M].北京：国防科技大学出版社，2007.

（作者单位：湖北工业大学）endprint

摘 要：本文以学籍注册、技能抽考及成绩评定三项学籍管理日常事务为例，探讨了如何通过灵活选用Excel内置的函数，实现对学籍信息高效管理。

关键词：学籍管理 Excel函数 应用

学籍管理是每个学校都必须进行的一项繁琐事务。随着计算机的广泛应用和Office办公自动化软件的使用，目前学生的学籍信息一般都使用电子文档存储，以便上交到上级管理部门审核。通常，学籍管理员会根据学籍信息特点和相关要求使用 Excel软件输入和存储管理信息，日常的学籍管理工作，如注册、修改、添加以及提取等，需要经过多次重复操作才能完成。如何让这些工作变得更轻松、省时？可以运用Excel提供的内置函数来帮助提高工作效率。

Excel软件最突出的特点就是具有数据自动处理能力，利用公式计算、公式的复制以及公式的重新计算功能，可以使复杂的事情简单化，特别是Excel提供的内置函数能更方便快捷地完成相同的工作。函数是Excel中预定义好的公式，共有200多个。在学籍管理的日常事务中，灵活选用其中内置的函数，实现对学籍信息的输入和日常处理，可以达到事半功倍的效果。

下面笔者从学籍注册、技能抽考及成绩评定这三个方面介绍Excel部分函数的功能与应用。

一、学籍注册

图1是从各班级收集的纸质数据表。

图1

根据Excel软件所具有的数据处理功能，从上图分析得出，学生的“身份证号”字段中包含了“性别”和“出生年月日”的信息，这样“性别” 和“出生年月日”两列的数据就不用再输入。

首先，新建Excel工作簿，在Sheet1工作表中创建一个电子注册表，在表中输入标题行内容：班级学号、姓名、身份证号、性别、出生日期、专业、班级等。在数据区域输入如图2所示的数据即可，其中A列、F列和G列剩余的数据采用填充柄的复制功能完成。

图2

第二，利用Mid函数（字符串截取函数）从“身份证号”中计算出“出生年月日”。Mid函数的功能是返回文本字符串中从指定位置开始的特定数目的字符。身份证号中第7位至第14位这8个数据代表学生的出生年月日。

例如：计算图2的1号同学的“出生年月日”，在E2结果单元格中，输入：

=MID（C2，7，8）

注：C2为引用身份证号码（数字字符）所在单元格，E列其余的学生的信息依然使用填充柄的复制功能完成。

第三，利用Mod函数（取余函数）计算出第17位数是奇数还是偶数。Mod函数的功能是返回两数相除的余数。身份证号的第17位代表了一个人的性别是“男”还是“女”，分别用奇数、偶数表示，奇数1、3、5、7、9表示男性，偶数0、2、4、6、8表示女性。即：

=MOD（第17位数，2）

第四，利用函数嵌套从“身份证号”中计算出“性别”。函数的嵌套是指一个函数中套入了另一个函数，函数的值作为另一个函数的参数。函数嵌套的层数最多可以达到7层。

由此得出：先用Mid取中间字符串函数，取出第17位的数字字符；再用数学函数Mod取余函数，计算该数字的奇偶性；最后是逻辑函数If条件函数，判断出是“男”性还是“女”性。

例如：计算图2的1号同学的性别，在D2结果单元格中，输入：

=IF（MOD（MID（C2，17，1），2）=0，"女"，"男"）

二、技能抽考

每年的技能抽考是湖北省检测职业院校学生学习情况的规定项目。出于公平公正的原则，笔者采用随机抽测学号的方式进行。例如一个专业注册60人，抽测10%，即随机抽取6人的学号。在Excel中随机函数Rand（ ）可实现这一功能。使用Rand（ ）1次，然后对生成的数进行增倍、取整等处理，即可抽出一个学号，再使用填充柄的复制功能，6单元格即可搞定。

随机函数Rand（ ）的功能是：返回大于等于0及小于1的均匀分布随机数，即产生出[0，1）之间的数：0或者一些小数。从抽测的学号分析，单独使用的Rand（ ）函数所产生的是[0，1）之间的数，不符合“学号”要求（以“学号”为1、2、……60整数为例，没有0），故采用以下方法解决此问题。

首先，随机函数乘以合适的数值型系数A，随机数将产生于0到A区间，即[0，A）。如Rand（ ）*60 函数，将产生大于等于0且小于60的一个随机数。因此通过乘以不同大小的系数，可以产生不同区间的随机数。

然后，配合取整函数，保证随机数为整数型数据项。例如Int（Rand（ ）*60）函数功能将产生0至60间的随机整数，即[0，60）的随机整数。包含了0，而不包含60。由于学号是从1到60号，在上面的基础上加上数字1即可满足抽取的要求。

举例：在“抽测学号”第一个结果单元格中，输入：

=INT（RAND（ ）*60）+1

三、成绩评定

近几年来，湖北省要求职业院校的学生取得英语、计算机等级证才能获得毕业证。如果学生英语、计算机两者有一项成绩不合格（无等级证），则不颁发毕业证。根据这一情况，可以把符合条件的标记为“发”，不符合条件的不作标记。

现假设学生英语、计算机考试状态如图3所示。

图3

从上表分析可知，每一种成绩有三种状态：“优秀”“合格”和“不合格”。

又已知两门成绩中只要有一门标记为“不合格”，即不发毕业证，其余情况均发。可以先用OR（ ）逻辑或函数来判断。OR（ ）函数的功能是任一参数为TRUE时，则结果为TRUE。再用IF嵌套。

举例：在J2单元格中，输入：

=IF（（OR（H2="不合格"，I2="不合格"）），""，"发" ）

计算结果如图4所示。

图4

综上所述，我们不难发现，Excel的内置函数具有强大功能。只要灵活运用Excel函数，发挥其作用，学籍管理工作效率将会大幅提高。

参考文献：

[1]叶亚平.计算机应用基础[M].北京：原子能出版社，2009.

[2]周和平.对口招生考试教程（计算机应用类）[M].北京：国防科技大学出版社，2007.

篇10：Excel中sumif函数的使用方法

1. 计算两个日期之间的时间差

在我们的生活和工作中经常会遇到计算两个日期之间的天数, 例如医院统计病人的住院天数、旅店统计客人的住宿天数、但是在单位统计职工工作时间时, 一般不会简单的用职工工作时间的实际天数作为显示结果, 而一般会用工作了“×年×个月×天”的格式表示, 这时都可以使用DATEDIF () 函数来帮忙, 利用此函数, 可以方便地计算两个日期之间的天数、月数和年数。此函数是一个隐藏函数, 如果你在Excel软件中直接点击工具栏上“粘贴函数”按钮时, 在函数列表中是找不到这个函数的, 但你只要在单元格中直接输入DATEDIF函数名称及参数就可以正常使用。

DATEDIF函数带有3个参数, 其格式如下:

=DATEDIF (start_date, end_date, units)

start_date和end_date参数分别是开始日期和终止日期, 而units则是含有1个或2个字符的字符串, 用以说明返回日期差的形式。units的参数类型对应的Datedif返回值。

“y”两日期之间间隔的年数 (非四舍五入)

“m”两日期之间间隔的总月数 (非四舍五入)

“d”两日期之间间隔的总天数 (非四舍五入)

“md”两个日期相减后, 其差不足一个月的部分的天数

“ym”两个日期相减后, 其差不足一年的部分的月数

“yd”两个日期相减后, 其差不足一年的部分的天数

为了让大家更清楚地了解DATEDIF () 函数的用法, 我们以一个实例来进行说明, 下图是一份制作好的职工工作时间统计表, 在这份工作表中使用了Unit所有的参数类型, 大家可以从中看到使用不同参数后所得到的不同结果。注意红圈当中的公式。

具体公式是

J2=DATEDIF (B2, C2, "Y") &"年"&DATEDIF (B2, C2, "YM") &"个月"&DATEDIF (B2, C2, "MD") &"天"。D3至J7单元格的结果均由所在列第二行的单元格拖动填充柄向下复制得到。

我们在实际使用中并不需要计算这么多结果, 只需计算我们需要的结果便可, 上图是为了说明函数用法所以详细列出。下面是DATEDIF函数的具体应用:

计算工龄

如果精确到“年”:假如日期数据在B3单元格。C3单元格的公式为“= (DATEDIF (B3, TODAY () , "y") ) +1。”注意:工龄两头算, 所以加“1”。

公式中用到了TODAY () 函数, 该函数的作用是返回计算机当前的日期 (注:本例及以下所有实例中的当前日期都是2010年9月18日) 。不难理解, 此公式算出的工龄是截止到计算的当天。需要说明的是TODAY () 函数的返回值是跟随电脑的系统时间而动态变化的, 因此在数据表中显示的计算结果也是变化的。

如果精确到“天”:D3单元格的公式为

2、计算两个日期之间工作日的天数

我们把每个星期的星期一到星期五称为工作日, 假如项目经理2010年7月7日接到一个新项目, 要求2010年10月8日完成, 他需要计算一下这两个日期之间有多少个工作日, 以便安排工作的进度。

为了解决这个问题, 需要用到一个名为NETWORKDAYS的函数, 由于这个函数位于分析工具库中, 因此需要安装分析工具库之后才能使用, 因此我们先来安装一下。

进入工作表之后, 选择菜单“工具→加载宏”命令, 打开“加载宏”对话框, 勾选“加载宏”下方列表框内的“分析工具库”复选框, 单击“确定”按钮完成安装。

返回Excel编辑窗口后, 在如下图所示的工作表的C2单元格中输入公式:=NETWORKDAYS (A2, B2)

回车后, 可以看到得出的答案为68, 即这期间还有68个工作日

说明:NETWORKDAYS函数的语法为NETWORKDAYS (start_d ate, end_date, holidays) 。其中的start_date位置放置计算的起点日期参数, end_date位置放置终点日期参数。如果交换这个放置顺序, 得出的答案为负值。holidays位置放置除了周末外还需要排除在工作日以外的日期。

这里可以和上面DATEDIF函数计算两个日期间隔天数的计算结果比较一下, 起点日期和终点日期都一样, 但是计算结果却差别很大, 这是因为用NETWORKDAYS函数计算的两个日期之间的差是不包含周末和法定休息日。

NETWORKDAYS函数还可以处理更复杂的情况, 假如除了星期六和星期日外, 期间还有一些国家法定休息日和单位自己安排的节假日, 实际的工作日数还应该把这些日期排除在外。因此必须放置参数如A5:A8的位置通知Excel。如图所示, 可以在C2单元格中输入公式:

回车后可以看到得出的答案为64, Excel已经把这四天排除在工作日之外。 (注:我这里只所以把10月2、3日写成10月4、5日是因为10月2、3日正好是周六、日, EXCEL已经把周六、日减过了, 所以正好是周六、日的话EXCEL不会再给你减去的。所以如果你标注的法定休息日正好是周六、日的话一定要将日期往后错, 错开周六、日, 这样才能得到准确的结果)

3、计算若干年、月、日以后的日期

如果我们只是计算若干年、月、日以后的日期 (并不考虑休息日) 那就要用到另外一个函数DATE函数, 此函数是我们经常用到的函数, 在此我们只是拓展他的用法。

计算若干年、月、日以后的日期在实际任务也可能常常遇到。例如, 计划用1年2个月零15天 (注:此日期包含休息日在内) 来完成一本图书的编写工作, 开工日期为2010年7月28日, 那么什么时候能完成图书的编写呢?

如图所示, B1单元格为开工日期, 选中B7单元格, 输入公式:=DATE (YEAR (B1) +B3, MONTH (B1) +B4, DAY (B1) +B5)

回车后可以看到得出的计算结果为“2011-10-13”, 也就是说, 将在2011年10月13日完成图书编写工作, 现在就可以提前通知好友准备庆祝活动了哦。