EXCEL数据处理 第04章 函数及其应用.ppt

第4章 函数及其应用 EXCEL数据处理 电子教案 中国水利水电出版社 2 本章知识点 函数的功能与类型函数的输入与调用常用函数及其应用主要数学函数应用逻辑判断函数应用日期时间函数应用文本函数及其应用 EXCEL数据处理 电子教案 中国水利水电出版社 3 本章目录 4 1函数的基本知识4 2常用函数4 3数学函数4 4逻辑判断函数4 5日期和时间函数4 6文本函数小结习题 EXCEL数据处理 电子教案 中国水利水电出版社 4 4 1函数的基本知识 4 1 1函数的功能与类型划分函数具有比公式更加强大的数据处理功能 具体功能如下 1 使用函数可以大大简化公式 2 使用函数可以实现用公式无法进行的某些运算 3 使用函数可以减少手工编辑 提高编辑速度 4 使用函数可以实现判断功能 进行有条件的运算 在Excel中 有300多个内部函数 它们涉及到许多应用领域 例如财务 工程 统计 数据库 时间处理 数学运算等 可分为数学与三角函数 日期与时间函数 文本函数 逻辑函数 信息函数 统计函数 财务函数 数据库函数 查找与引用函数和工程函数等10大类 EXCEL数据处理 电子教案 中国水利水电出版社 5 4 1 2函数的结构 在Excel中 函数结构一般由三部分构成 分别是函数名 括号和参数表 如下所示 函数名 参数1 参数2 参数3 1 函数名 确定了函数的功能和运算规则 在形式上它一般采用大写字母 但是在具体使用过程中 用户也可以采用小写形式 系统会自动将其转换为大写字母的状态 2 括号 在Excel函数中 圆括号 不能省略 必须紧跟在函数名称后面 里面用来书写参数 3 参数表 参数表规定了函数的运算对象 顺序或结构等 EXCEL数据处理 电子教案 中国水利水电出版社 6 4 1 3函数的调用 函数的调用就是在公式或者表达式中应用函数 包括如下三种形式 在公式中直接调用函数 如果函数以公式的形式出现 只需要在函数前面加上等号 即可 如 SUM A1 A100 在表达式中调用函数 如果函数作为表达式的组成部分来使用 则需要在公式的相应位置输入函数 如 C9 SUM A1 A100 此时不用在函数前面加 函数的嵌套调用 函数也可以嵌套调用 即一个函数作为另一个函数的参数 如 IF RIGHT A2 1 1 男 女 其中公式中的IF函数中嵌套了RIGHT函数 而且将RIGHT函数返回的结果作为I函数的逻辑判断依据 对于函数的嵌套调用 作为参数的函数前面不能添加 如上面公式中内部的RIGHT函数前面就不能添加 EXCEL数据处理 电子教案 中国水利水电出版社 7 4 1 4函数的输入 在Excel的公式和表达式中调用函数 第一个要解决的问题就是函数的输入 按照函数的来源 Excel函数可以分为内置函数 扩展函数和自定义函数三类 对于内置函数 只要启动Excel 用户就可以使用它们 而对于扩展函数 必须通过单击 工具 加载宏 菜单命令加载 然后才能像内置函数那样使用 而自定义函数 需要用户先通自动录制宏或者手工编写VBA代码的方法 先定义好函数 然后就能像内置函数一样进行调用 函数输入有借助函数向导输入和手工直接输入两种方法 1 借助函数向导输入2 手工直接输入函数 EXCEL数据处理 电子教案 中国水利水电出版社 8 4 1 5使用Excel帮助理解函数 多数情况下 用户对一些不熟悉的函数常常采用插入函数的方法 其实在使用 插入函数 对话框或 函数参数 对话框的时候 用户都可以通过单击对话框下方的 有关该函数的帮助 超链接或按 F1 键 进入Excel的帮助系统 以便得到该函数的帮助 说明 Excel的帮助系统具有极其强大的功能 它比许多参考书内容更全面 讲述更清楚 实例更丰富 用户利用它能够解决在使用Excel过程中所遇到的各种问题 如Excel的新技术 疑难问题解答 专用名词术语 函数说明 函数应用实例等 一个Excel的真正用户应该会运用帮助系统 从中获取有用的资料 培养自学能力 养成 终身学习 习惯 EXCEL数据处理 电子教案 中国水利水电出版社 9 4 2常用函数 4 2 1自动求和按钮下对应的常用函数求和是最为常用的运算 所以Excel以前一直将求和做成了一个命令按钮 而从Excel2003开始 Excel自动求和按钮又提供了一个下拉菜单 其中包括了求和 求平均值 计数 求最大值 求最小值等常用函数以及可以用来打开 插入函数 的 其他函数 F 超级链接 1 SUM函数2 AVERAGE函数3 COUNT函数4 MAX函数和MIN函数 EXCEL数据处理 电子教案 中国水利水电出版社 10 4 2 2其他常用计数函数介绍 1 COUNTA函数COUNTA返回参数列表中非空值单元格的个数 2 COUNTBLANK函数COUNTBLANK返回指定参数中空白单元格的个数 3 COUNTIF函数COUNTIF函数的功能是计算指定参数中满足给定条件的单元格的个数 语法格式为 COUNTIF range criteria 其中 参数Range为需要计算其中满足条件的单元格数目的单元格区域 Criteria是指定的条件 其形式可以为数字 表达式或文本 如果是文本或表达式 则Criteria参数必须加上半角状态下的双引号 EXCEL数据处理 电子教案 中国水利水电出版社 11 4 2 3常用函数应用实例 学生成绩处理 EXCEL数据处理 电子教案 中国水利水电出版社 12 4 3数学函数 4 3 1常用数学函数的功能介绍ABS返回数字的绝对值INT将数字向下舍入到最接近的整数TRUNC将数字的小数部分截去 返回整数ROUND按指定位数四舍五入某个数字SIGN返回数字符号 PRODUCT将参数数字相乘 返回乘积值MOD返回两数相除的余数 其结果符号与除数相同SQRT返回某一正数算术平方根FACT返回数的阶乘POWER返回给定数字的乘幂 与 运算符相同 RAND返回一个大于等于0小于1的随机数RANDBETWEEN产生位于两个指定数值之间的一个随机数 EXCEL数据处理 电子教案 中国水利水电出版社 13 4 3 2数学函数应用实例一 利用随机函数快速获取虚拟数据 EXCEL数据处理 电子教案 中国水利水电出版社 14 4 3 3数学函数应用实例二 按实发工资总额计算每种面额钞票的张数 EXCEL数据处理 电子教案 中国水利水电出版社 15 4 4逻辑判断函数 4 4 1逻辑函数的功能与用法介绍EXCEL中的逻辑函数共有6个 分别是IF AND OR NOT TRUE和FALSE 另外 IS类函数作为信息判断的函数 也有逻辑判断的功能 1 IF函数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时的显示内容 EXCEL数据处理 电子教案 中国水利水电出版社 16 4 4 1逻辑函数功能与用法介绍 2 AND OR NOT函数Excel不同于其它一些计算机编程语言 它没有AND OR NOT等逻辑运算符 也就是不能进行 AANDB 英语 85 OR 数学 85 NOT超出计划 等运算 其逻辑运算只能由逻辑函数AND OR NOT来实现 这三个函数的语法格式分别为 AND logical1 logical2 OR logical1 logical2 NOT logical 其中 AND和OR函数中的参数Logical1 logical2 表示待测试的条件值或表达式 其结果可为TRUE或FALSE 最多不超过30个 当使用AND函数时 只有当运算对象都为TRUE的情况下 运算结果才为TRUE 否则为FALSE 当执行OR运算时 只有当运算对象都为FALSE的情况下 运算结果才为FALSE 否则为TRUE NOT函数中的Logical为可以计算出TRUE或FALSE的逻辑值或逻辑表达式 NOT函数的功能是对该表达式运算结果取相反值 EXCEL数据处理 电子教案 中国水利水电出版社 17 4 4 1逻辑函数功能与用法介绍 3 TRUE和FALSE函数TRUE函数和FALSE函数都是无参函数 其结果分别是返回逻辑值 TRUE 和 FALSE 其实 可以直接在单元格或公式中键入 TRUE 或 FALSE 值 而不使用此这两个函数 引入这两个函数主要目的是用于与其它电子表格程序兼容 4 IS类函数IS类函数属于信息判断函数 其功能是检验数值的类型 并且根据参数的值返回TRUE或FALSE 从而也可以起到一定的逻辑判断和条件检测功能 IS类函数总共包括用来检验数值或引用类型的九个工作表函数 分别为 ISBLANK函数 ISERR函数 ISERROR函数 ISLOGICAL函数 ISNA函数 ISNONTEXT函数 ISNUMBER函数 ISREF函数和ISTEXT函数 它们具有以下相同的语法格式 IS函数名 value 其中 IS函数名代表任一IS类函数名称 value是需要进行检验的参数 可以是 空白单元格 错误值 逻辑值 文本 数字 引用值或对于以上任意参数的名称引用 EXCEL数据处理 电子教案 中国水利水电出版社 18 4 4 2逻辑函数应用实例一 销售提成计算 问题描述 如图4 13所示为某公司销售部5月份各地办事处的销售额及其销售费用情况 请根据右边的奖金提成政策判别各个办事处的奖金提成比例 并计算奖金提成金额 图4 13销售提成数据与政策表格 EXCEL数据处理 电子教案 中国水利水电出版社 19 4 4 3逻辑函数应用实例二 考试成绩评判 EXCEL数据处理 电子教案 中国水利水电出版社 20 4 4 4逻辑函数应用实例三 制作跳水比赛计分表模板 EXCEL数据处理 电子教案 中国水利水电出版社 21 4 5日期和时间函数 4 5 1常用日期和时间函数介绍1 TODAY函数TODAY函数的功能是返回当前日期的序列号 如果在输入函数前 单元格的格式为 常规 则结果为日期格式 其语法格式为 TODAY 示例 在任意一个单元格中输入公式 TODAY 都得到电脑系统的当前日期 说明 TODAY函数是一个无参函数 但是在应用时候后面的左右括号不能漏掉 2 YEAR函数YEAR函数的功能是返回某日期对应的年份 返回值为1900到9999之间的整数 其语法格式为 YEAR serial number 其中的参数Serial number为一个日期值 其中包含要查找年份的日期 示例 公式 YEAR 2008 5 15 返回2008 EXCEL数据处理 电子教案 中国水利水电出版社 22 4 5 1常用日期和时间函数介绍 3 MONTH函数MONTH函数的功能是返回指定日期中的月份 返回值是介于1到12之间的整数 其语法格式为 MONTH serial number 参数同YEAR函数中的参数意义一样 示例 公式 MONTH 2008 5 15 返回5 4 DAY函数DAY函数的功能是返回指定日期的天数 返回值用整数1到31表示 其语法格式为 DAY serial number 参数同YEAR函数中的参数意义一样 示例 公式 DAY 2008 5 15 返回15 EXCEL数据处理 电子教案 中国水利水电出版社 23 4 5 1常用日期和时间函数介绍 5 DATE函数DATE函数返回代表特定日期的序列号 其语法格式为 DATE year month day 其中的三个参数中 Year为年份 也可以为年份的序列号 用一到四位数字表示 Month为月份 如果所输入的月份大于12 将从指定年份下一年的一月份开始往上加算 如 DATE 2008 14 2 返回代表2009年2月2日的序列号 Day代表在该月份中的天数 如果day大于该月份的最大天数 则将从指定月份的下一月的第一天开始往上累加 如 DATE 2008 5 50 将返回2008年6月19日 示例 在有些数据库中 日期的年 月 日数字可能分为三个字段来保存 当导入到Excel工作表后 就可以利用DATE函数将年 月 日合并成一个具体的日期数据 假设单元格A1是年份数字 B1是月份数字 C1是日数 则公式 DATE A1 B1 C1 就将A1的年份数字 B1的月份数字和C1的日数 合并到一起组成了一个具体的日期数据 说明 上面实例中 读者可能会想到利用连接符 将三个数字连接起来 利用公式 A1 B1 C1 单是这种方式得到的结果不是一个时间序列号 而是文本 EXCEL数据处理 电子教案 中国水利水电出版社 24 4 5 1常用日期和时间函数介绍 6 WEEKDAY函数WEEKDAY函数的功能是返回某日期为星期几 默认情况下 其值为1 星期天 到7 星期六 之间的整数 其语法格式为 WEEKDAY serial number return type 其中参数Serial number为日期序列号 可以是日期数据或是对日期数据单元格的引用 参数Return type用来确定返回值类型的数字 具体如下表4 3所示 表4 3参数Return type含义说明参数Serial number的值返回结果数字及其含义说明1或省略返回数字1到7 其中 1表示星期日 2表示星期一 7表示星期六2返回数字1到7 其中 1表示星期一 2表示星期二 7表示星期日3返回数字0到6 其中 0表示星期一 1表示星期二 6表示星期日示例 2008年5月15日是星期四 则 WEEKDAY 2008 5 15 返回 5 WEEKDAY 2008 5 15 2 返回 4 WEEKDAY 2008 5 15 3 返回 3 EXCEL数据处理 电子教案 中国水利水电出版社 25 4 5 1常用日期和时间函数介绍 7 NETWORKDAYS函数NETWORKDAYS函数的功能是返回两个日期之间完整的工作日数值 不包括周末和专门指定的假期 其语法格式为 NETWORKDAYS start date end date holidays 其中的参数 Start date表示开始日期 End date表示终止日期 holidays为节假日 可以是包含日期的单元格区域 也可以是由代表日期的序列号所构成的数组常量 示例 公式 NETWORKDAYS 2008 5 1 2008 5 10 2008 5 1 返回6 8 NOW函数NOW函数的功能是返回当前日期和时间的序列号 如果在输入函数前 单元格的格式为 常规 则结果为日期格式 其语法格式为 NOW 示例 在任意一个单元格中输入公式 NOW 都得到电脑系统的当前时间 说明 NOW函数同TODAY函数一样 也是一个无参函数 但应用时后面括号不能漏掉 9 TIME函数TIME函数的功能是返回返回某一特定时间的小数值 小数值为0 零 到0 99999999之间的数值 代表从0 00 00 12 00 00AM 到23 59 59 11 59 59PM 之间的时间 如果在输入函数前 单元格的格式为 常规 则结果将设为日期格式 其语法格式为 TIME hour minute second EXCEL数据处理 电子教案 中国水利水电出版社 26 4 5 2日期和时间函数应用实例一 生日提醒 EXCEL数据处理 电子教案 中国水利水电出版社 27 4 5 3日期和时间函数应用实例二 会议日程安排 EXCEL数据处理 电子教案 中国水利水电出版社 28 4 5 4日期和时间函数应用实例三 加班工资计算 EXCEL数据处理 电子教案 中国水利水电出版社 29 4 5 5日期和时间函数应用实例四 制作万年历 EXCEL数据处理 电子教案 中国水利水电出版社 30 4 6文本函数 4 6 1常用文本函数介绍1 TEXT函数TEXT函数的功能是将数值转换为按指定数字格式表示的文本 其语法格式为 TEXT value format text 其中包括两个参数 意义分别为 Value为数值 计算结果是数值的公式或对数值单元格的引用 format text为所要选用的文本型数字格式 即 单元格格式 对话框 数字 选项卡的 分类 列表框中显示的文本形式的数字格式 它不能包含星号 示例 如果工作表中A1单元格的数值为2986 638 则公式 TEXT A5 0 00 返回字符串 2 986 64 说明 使用 单元格格式 对话框的 数字 选项卡设置单元格格式 只会改变单元格的格式而不会影响其中的数值 而使用TEXT函数可以将数值转换为带格式的文本 而其结果将不再作为数字参与计算 EXCEL数据处理 电子教案 中国水利水电出版社 31 4 6 1常用文本函数介绍 2 VALUE函数VALUE函数的功能是将代表数字的文本字符串转换成数字 其语法格式为 VALUE text 其中参数text为带引号的文本 或对需要进行文本转换的单元格的引用 还可以是Excel中可识别的日期或时间格式 如果text不是上述格式 则VALUE函数返回错误值 VALUE 示例 公式 VALUE 1 000 返回1000 3 LOWER函数LOWER功能是将一个文本字符串中的所有大写字母转换为小写字母 其语法格式为 LOWER text 其中参数text是要转换为小写字母的文本 它可以是引用或文字串 示例 如果A1中有字符串 EXCEL 则公式 LOWER A1 返回字符串 excel 说明 LOWER函数不改变文本中的非字母字符 与LOWER函数类似 Excel还有UPPER函数和PROPER函数 其中 UPPER函数将一个文本字符串中所有小写字母转换为大写字母的 而PROPER函数将文本字符串的首字母和任何非字母字符之后的首字母转换成大写 其余字母转换成小写 它们的语法格式和LOWER函数相同 EXCEL数据处理 电子教案 中国水利水电出版社 32 4 6 1常用文本函数介绍 4 TRIM函数TRIM函数清除文本中的所有空格 除了单词之间的单个空格外 其语法格式为 TRIM text 其中参数text是需要清除其中空格的文本 示例 如果工作表A1单元格的数据为字符串 FirstQuarterEarnings 则公式 TRIM A1 返回字符串 FirstQuarterEarnings 5 LEN函数LEN函数的功能是返回文本字符串中的字符数 其语法格式为 LEN text 其中参数text为要查找其长度的文本 空格也将作为字符进行计数 示例 如果A1单元格中字符串为 电脑爱好者 则公式 LEN A1 返回6 6 CONCATENATE函数CONCATENATE函数的功能是将若干字符串合并成一个字符串 其功能与文本连接运算符 相同 其语法格式为 CONCATENATE text1 text2 其中的参数text1 text2 为1到30个将要合并成单个文本的文本项 这些文本项可以是文字串 数字或对单个单元格的引用 示例 公式 CONCATENATE 97 千米 返回 97千米 EXCEL数据处理 电子教案 中国水利水电出版社 33 4 6 1常用文本函数介绍 7 LEFT函数LEFT函数的功能是根据所指定的字符数从文本字符串左边取出第一个或前几个字符 也就是说 LEFT函数对字符串进行 左截取 其语法格式为 LEFT text num chars 其中参数Text是要提取字符的文本字符串 Num chars是指定提取的字符个数 它必须大于或等于0 如果省略参数num chars 默认值为1 如果参数num chars大于文本长度 则LEFT函数返回所有文本 示例 公式 LEFT 电脑爱好者 2 返回字符串 电脑 8 MID函数MID函数的功能是返回文本字符串中从指定位置开始的特定数目的字符 该数目由用户指定 其语法格式为 MID text start num num chars 其中参数text为要提取字符的文本字符串 start num为要提取的第一个字符的位置 文本中第一个字符的位置为1 其余依此类推 num chars为指定提取的字符个数 示例 公式 MID 电脑爱好者 3 2 返回字符串 爱好 EXCEL数据处理 电子教案 中国水利水电出版社 34 4 6 1常用文本函数介绍 9 REPLACE函数REPLACE函数的功能是根据所指定的字符数 使用其它文本字符串替换当前文本字符串中的部分文本 其语法格式为 REPLACE old text start num num chars new text 其中参数Old text为要替换其部分字符的文本 Start num为要用new text替换的old text中字符的位置 Num chars是用new text替换old text中字符的个数 New text是用于替换old text中字符的文本 示例 如果工作表中A1单元格的数据为字符串 电脑爱好者 A2单元格的数据为字符串 计算机 则公式 REPLACE A1 1 2 A2 返回字符串 计算机爱好者 EXCEL数据处理 电子教案 中国水利水电出版社 35 4 6 1常用文本函数介绍 10 SUBSTITUTE函数SUBSTITUTE函数在文本字符串中用new text代替old text 其语法格式为 SUBSTITUTE text old text new text instance num 其中参数Text为需要替换其中字符的文本 或对含有