excel函数教程(编辑修改稿)

excel函数教程(编辑修改稿)内容摘要：

法解释为，如果单元格 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 确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。 例如，条件可以表示为 3 3 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 函数的逻辑函数，相信大家在实际工作中会想出更多更有用的运用。 [dvnews_page=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 图 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：返回计算机系统内部的当前日期。 语法形式为 ： TODAY（） （ 5） DATEDIF：计算两个日期之间的天数、月数或年数。 语法形式为： DATEDIF(start_date,end_date,unit) （ 6） VALUE：将代表数字的文字串转换成数字。 语法形式为： VALUE(text) （ 7） RIGHT：根据所指定的字符数返回文本串中最后一个或多。