excel函数应用之函数简介(编辑修改稿)

excel函数应用之函数简介(编辑修改稿)内容摘要：

中根据不同的成绩区分合格与不合格。 现在我们就以某班级的英语成绩为例具体说明用法。 图 6 某班级的成绩如图 6 所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。 如果各科平均分超过 60 分则认为是合格的，否则记作不合格。 根据这一规则，我们在综合评定中写公 式（以单元格 B12 为例）： =IF(B1160,合格 ,不合格 ) 语法解释为，如果单元格 B11 的值大于 60，则执行第二个参数即在单元格 B12中显示合格字样，否则执行第三个参数即在单元格 B12 中显示不合格字样。 在综合评定栏中可以看到由于 C 列的同学各科平均分为 54 分，综合评定为不合格。 其余均为合格。 多层嵌套函数的应用 在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常是有多个等级的，比如优、良、中、及格、不及格等。 有办法一次性区分吗。 可以使用多层嵌套的办法来 实现。 仍以上例为例，我们设定综合评定的规则为当各科平均分超过 90 时，评定为优秀。 如图 7 所示。 图 7 说明：为了解释起来比较方便，我们在这里仅做两重嵌套的示例，您可以按照实际情况进行更多重的嵌套，但请注意 Excel的 IF 函数最多允许七重嵌套。 根据这一规则，我们在综合评定中写公式（以单元格 F12 为例）： =IF(F1160,IF(AND(F1190),优秀 ,合格 ),不合格 ) 语法解释为，如果单元格 F11 的值大于 60，则执行第二个参数，在这里为嵌套函数，继续判断单元格 F11 的值是否大 于 90（为了让大家体会一下 AND 函数的应用，写成 AND(F1190)，实际上可以仅写 F1190），如果满足在单元格 F12 中显示优秀字样，不满足显示合格字样，如果 F11 的值以上条件都不满足，则执行第三个参数即在单元格 F12 中显示不合格字样。 在综合评定栏中可以看到由于 F 列的同学各科平均分为 92 分，综合评定为优秀。 （三）根据条件计算值 在了解了 IF 函数的使用方法后，我们再来看看与之类似的 Excel提供的可根据某一条件来分析数据的其他函数。 例如，如果要计算单元格区域中某个文本串或数字出现的次数，则可使 用 COUNTIF 工作表函数。 如果要根据单元格区域中的某一文本串或数字求和，则可使用 SUMIF 工作表函数。 关于 SUMIF 函数在数学与三角函数中以做了较为详细的介绍。 这里重点介绍 COUNTIF 的应用。 COUNTIF 可以用来计算给定区域内满足特定条件的单元格的数目。 比如在成绩表中计算每位学生取得优秀成绩的课程数。 在工资表中求出所有基本工资在 2020 元以上的员工数。 语法形式为 COUNTIF(range,criteria)。 其中 Range 为需要计算其中满足条件的单元格数目的单元格区域。 Criteria 确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 例如，条件可以表示为 33 3 apples。 成绩表 这里仍以上述成绩表的例子说明一些应用方法。 我们需要计算的是：每位学生取得优秀成绩的课程数。 规则为成绩大于 90 分记做优秀。 如图 8 所示 图 8 根据这一规则，我们在优秀门数中写公式（以单元格 B13 为例）： =COUNTIF(B4:B10,90) 语法解释为，计算 B4 到 B10 这个范围，即 jarry 的各科成绩中有多少个数值大于 90 的单元格。 在优秀门 数栏中可以看到 jarry 的优秀门数为两门。 其他人也可以依次看到。 销售业绩表 销售业绩表可能是综合运用 IF、 SUMIF、 COUNTIF 非常典型的示例。 比如，可能希望计算销售人员的订单数，然后汇总每个销售人员的销售额，并且根据总发货量决定每次销售应获得的奖金。 原始数据表如图 9 所示（原始数据是以流水单形式列出的，即按订单号排列） 图 9 原始数据表 按销售人员汇总表如图 10 所示 图 10 销售人员汇总表 如图 10 所示的表完全是利用函数计算的方法自动汇总的数据。 首先建立一个按照销售人员汇总的 表单样式，如图所示。 然后分别计算订单数、订单总额、销售奖金。 （ 1） 订单数 用 COUNTIF 计算销售人员的订单数。 以销售人员 ANNIE 的订单数公式为例。 公式： =COUNTIF($C$2:$C$13,A17) 语法解释为计算单元格 A17(即销售人员 ANNIE)在 销售人员 清单 $C$2:$C$13的范围内（即图 9 所示的原始数据表）出现的次数。 这个出现的次数即可认为是该销售人员 ANNIE 的订单数。 （ 2） 订单总额 用 SUMIF 汇总每个销售人员的销售额。 以销售人员 ANNIE 的订单总 额公式为例。 公式： =SUMIF($C$2:$C$13,A17,$B$2:$B$13) 此公式在 销售人员 清单 $C$2:$C$13 中检查单元格 A17 中的文本（即销售人员 ANNIE），然后计算 订单金额 列（ $B$2:$B$13）中相应量的和。 这个相应量的和就是销售人员 ANNIE 的订单总额。 （ 3） 销售奖金 用 IF 根据订单总额决定每次销售应获得的奖金。 假定公司的销售奖金规则为当订单总额超过 5 万元时，奖励幅度为百分之十五，否则为百分之十。 根据这一规则仍以销售人员 ANNIE 为例说明。 公式为： =IF(C1750000,10%,15%)*C17 如果订单总额小于 50000 则奖金为 10%；如果订单总额大于等于 50000，则奖金为 15%。 至此，我们已完全了解了 EXCEL 函数的逻辑函数，相信大家在实际工作中会想出更多更有用的运用。 所谓文本函数，就是可以在公式中处理文字串的函数。 例如，可以改变大小写或确定文字串的长度；可以替换某些字符或者去除某些字符等。 而日期和时间函数则可以在公式中分析和处理日期值和时间值。 关于这两类函数的列表参看附表，这里仅对一些常用的函数做简要介绍。 一、文本 函数 （一）大小写转换 LOWER将一个文字串中的所有大写字母转换为小写字母。 UPPER将文本转换成大写形式。 PROPER将文字串的首字母及任何非字母字符之后的首字母转换成大写。 将其余的字母转换成小写。 这三种函数的基本语法形式均为 函数名（ text）。 示例说明： 已有字符串为： pLease ComE Here! 可以看到由于输入的不规范，这句话大小写乱用了。 通过以上三个函数可以将文本转换显示样式，使得文本变得规范。 参见图 1 Lower（ pLease ComE Here!） = please e here! upper（ pLease ComE Here!） = PLEASE COME HERE! proper（ pLease ComE Here!） = Please Come Here! 图 1 （二）取出字符串中的部分字符 您可以使用 Mid、 Left、 Right等函数从长字符串内获取一部分字符。 具体语法格式为 LEFT函数： LEFT(text,num_chars)其中 Text是包含要提取字符的文本串。 Num_chars指定要由 LEFT 所提取的字符数。 MID函数： MID(text,start_num,num_chars)其中 Text是包含要提取字符的文本串。 Start_num是文本中要提 取的第一个字符的位置。 RIGHT函数： RIGHT(text,num_chars)其中 Text是包含要提取字符的文本串。 Num_chars指定希望 RIGHT 提取的字符数。 比如，从字符串 This is an apple.分别取出字符 This、 apple、 is的具体函数写法为。 LEFT(This is an apple,4)=This RIGHT(This is an apple,5)=apple MID(This is an apple,6,2)=is Excel 函数应用回顾 ● Excel 函数应用之逻辑函数 ● Excel 函数应用之数学和三角函数 ● Excel 函数应用之函数简介 图 2 （三）去除字符串的空白 在字符串形态中，空白也是一个有效的字符，但是如果字符串中出现空白字符时，容易在判断或对比数据是发生错误，在 Excel中您可以使用 Trim函数清除字符串中的空白。 语法形式为： TRIM(text)其中 Text为需要清除其中空格的文本。 需要注意的是， Trim函数不会清除 单词之间的单个空格，如果连这部分空格都需清除的话，建议使用替换功能。 比如，从字符串 My name is Mary中清除空格的函数写法为： TRIM(My name is Mary)=My name is Mary 参见图 3 图 3 （四）字符串的比较 在数据表中经常会比对不同的字符串，此 时您可以使用 EXACT函数来比较两个字符串是否相同。 该函数测试两个字符串是否完全相同。 如果它们完全相同，则返回 TRUE；否则，返回 FALSE。 函数 EXACT 能区分大小写，但忽略格式上的差异。 利用函数 EXACT 可以测试输入文档内的文字。 语法形式为：EXACT(text1,text2)Text1为待比较的第一个字符串。 Text2为待比较的第二个字符串。 举例说明：参见图 4 EXACT(China,china)=False 图 4 二、日期与时间函数 在数据表的处理过程中，日期与时间的函数是相当重要的处理依据。 而 Excel在这方面也提供了相当丰富的函数供大家使用。 （一）取出当前系统时间 /日期信息 用于取出当前系统时间 /日期信息的函数主要有 NOW、 TODAY。 语法形式均为 函数名（）。 （二）取得日期 /时间的部分字段值 如果需要单独的年份、月份、日数或小时的数据时，可以 使用 HOUR、 DAY、 MONTH、 YEAR函数直接从日期 /时间中取出需要的数据。 具体示例参看图 5。 比如，需要返回 2020530 12:30 PM的年份、月份、日数及小时数，可以分别采用相应函数实现。 YEAR(E5)=2020 MONTH(E5)=5 DAY(E5)=30 HOUR(E5)=12 图 5 此外还有更多有用的日期 /时间函数，可以查阅附表。 下面我们将以一个具体的示例来说明 Excel的文本函数与日期函数的用途。 三、示例：做一个美观简洁的人事资料分析表 示例说明 在如图 6所示的某公司人事资料表中，除了编号、员工姓名、身份证号码以及参加工作时间为手工添入外，其余各项均为用函数计算所得。 图 6 在此例中我们将详细说明如何通过函数求出： （ 1）自动从身份证号码中提取出生年月、性别信息。 （ 2）自动从参加工作时间中提取工龄信息。 身份证号码相关知识 在了解如何实现自动从身份证号码中提取出生年月、性别信息之前，首先需要了解身份证号码所代表的含义。 我们知道，当今的身份证号码有 15/18位之分。 早期签发的身份证号码是 15位的，现在签发的身份证由于年份的扩展（由两位变为四位）和末尾加了效验码，就成了 18位。 这两种身份证号码将在相当长的一段时期内共存。 两 种身份证号码的含义如下： （ 1） 15位的身份证号码： 1~6位为地区代码， 7~8位为出生年份 (2位 )， 9~10位为出生月份， 11~12位为出生日期，第 13~15位为顺序号，并能够判断性别，奇数为男，偶数为女。 （ 2） 18位的身份证号码： 1~6位为地区代码， 7~10位为出生年份 (4位 )， 11~12位为出生月份， 13~14位为出生日期，第 15~17位为顺序号，并能够判断性别，奇数为男，偶数为女。 18位为效验位。 应用函数 在此例中为了实现数据的自动提取，应用了如下几个 Excel函数。 （ 1） IF函 数：根据逻辑表达式测试的结果，返回相应的值。 IF函数允许嵌套。 语法形式为： IF（ logical_test, value_if_true,value_if_false） （ 2） CONCATENATE：将若干个文字项合并至一个文字项中。 语法形式为： CONCATENATE(text1,text2„„) （ 3） MID：从文本字符串中指定的起始位置起，返回指定长度的字符。 语法形式为： MID(text,start_num,num_chars) （ 4） TODAY：返回计算机系统内部的当前日。