电子表格学习资料PPT课件

第3章公式 名字与函数 Excel数据处理与统计初步第4版 3 4函数简介 1 函数的概念函数是能够完成特定功能的程序 在Excel中 它是系统预定义的一些公式 它们使用一些称为参数的特定数值按特定的顺序或结构进行计算 然后把计算的结果存放在某个单元格中 在大多数情况下 函数的计算结果是数值 当然 它也可以返回文本 引用 逻辑值 数组或工作表的信息 3 4函数简介 2 Excel函数分类 3 4函数简介 3 函数调用函数的语法函数名 参数1 参数2 参数3 在公式中调用函数 3 7逻辑函数 1 Excel的比较运算符比较运算又称关系运算 就是人们常说的比较式 比较运算只有两种不同的结果 要么 正确 要么 错误 不可能有第三种结果 3 7逻辑函数 2AND NOT OR TRUE FALSE函数用法AND x1 x2 x30 OR x1 x2 x30 NOT logical 案例AND TRUE TRUE TRUE OR TRUE TRUE TRUEAND TRUE FALSE TRUE TRUE FALSE OR TRUE FALSE TRUE TRUE TRUE 如果B1 B2 B3单元格中的值为TRUE FALSE TRUE 则AND B1 B3 FALSE 但OR B1 B3 TRUE AND 2 2 3 5 TRUE 3 8数学和三角函数 1 概述Excel提供了许多数学和三角函数 它们能够完成大多数数学和三角运算 这些函数可以在公式中直接引用 然后将公式的计算结果返回到输入公式的单元格中 2 常见的数学函数MOD TRUNC ABS SQRT SIN ASIN n1 ACOS n1 ATAN n2 EXP n POWER x n LN n FACT n LOG n base MINVERSE array MMULT array1 array2 3 8数学和三角函数 1 RAND 随机函数 产生一个大于等于0小于1均匀分布的随机数 每次计算工作表时 都将返回一个新的数值 例如 RAND b a a RAND 1002 INT x 取整函数 返回实数x舍入后的整数值 例如 INT 8 9 8 INT 8 9 9 3 8数学和三角函数 3 ROUND number n 四舍五入函数 返回某个数字按指定位数取整后的数字 例如 ROUND 2 15 1 2 2ROUND 1 475 2 1 48ROUND 21 5 0 22ROUND 21 5 1 20ROUND 21 5 2 0 3 8数学和三角函数 3 数学函数应用案例 例1 用随机函数产生大量的实验数据 计算排名 问题 有一张工资工作表 结构如图所示 现以此表为例说明随机函数和数组的结合使用 3 8数学和三角函数 产生日期 1 在B2单元格中输入参加工作的最早时间 即在B2单元格中输入 1970 1 1 2 选中要产生日期的单元格区域 如B3 B8 3 输入公式 B2 RAND 1825 4 按Ctrl Enter键 5 将B3 B8格式化为需要的日期格式 产生加班时间 计算加班工资 1 选中C2 C8单元格区域 2 输入公式 INT RAND 600 3 按Ctrl Enter键 3 8数学和三角函数 4 一个月共10个小时加班 共600分钟 每小时加班工资10元 在E2中输入公式 ROUND C2 60 0 10 其中ROUND函数将加班时间四舍五入到个位 3 8数学和三角函数 产生基本工资假设基本工资在800 3000这个范围内 其产生方法如下 1 选择D2 D8单元格区域 2 输入公式 800 int RAND 2200 3 按Ctrl Enter键 产生其它数据假设图中的其他数据都在1000以内 其产生方法如下 1 选择E2 H8单元格区域 2 输入公式 int RAND 1000 3 按Ctrl Enter键 3 9日期及时间函数 1 Excel处理日期的方式MicrosoftExcel将日期存储为序列号 称为序列值 即一系列连续的数字编号 每一个数字编号代表一个日期 在默认情况下 数字1代表1900年1月1日 2代表1900年1月2日 39 448代表2008年1月1日 因为从1900年1月1日到2008年1月1日正好39 448天 Excel将时间存储为小数 因为时间被看作天的一部分 时间也被存为序列号 此序列号以秒为单位递增 因为一天共有 24 60 60 86400秒 所以1 86400代表的时间是 00 00 01 2 86400代表的时间是 00 00 02 日期和时间都是数值 因此它们也可以进行各种运算 如果要计算两个日期之间的差值 可以用一个日期减去另一个日期 3 9日期及时间函数 2 DATE函数用法DATE year month day 功能DATE函数利用所给的参数 构造一个日期序列数例如DATE 2005 3 21 的结果是2005 3 21 3 9日期及时间函数 3 YEAR MONTH DAY函数用法YEAR serial number MONTH serial number DAY serial number 其中的serial number是一个日期或数字 功能YEAR函数返回某日期的年份 MONTH函数返回以系列数表示的日期中的月份 DAY函数返回以系列数表示的某日期的天数 用整数1 31表示 3 9日期及时间函数 4 TODAY NOW函数用法TODAY NOW 功能TODAY函数返回系统的当前日期 NOW函数计算当前日期和时间 3 9日期及时间函数 5 WEEKDAY用法WEEKDAY serial number return type 其中 serial number代表要查找的日期 或日期的系列数 以了解该日期为星期几 return type确定返回值类型的数字 功能WEEKDAY计算给定的日期是星期几 3 9日期及时间函数 6 NETWORKDAYS函数用法NETWORKDAYS start date end date holidays 其中start date表示代表开始日期 end date为终止日期 holidays表示不在工作日历中的一个或多个日期所构成的可选区域 如元旦节 五一节 春节 3 9日期及时间函数 案例 例 某公司将每个员工的加班时间记录在Excel的工作表中 便于计算加班工资 加班工资按小时计算 若加班时间不足1小时 但超过半小时由按1计时计算 不足半小时则不算加班时间 平时的加班工资每小时10元 节假时则加倍为20元每小时 假设员工的加班时间如图的A1 C11所示 现要计算加班时长 D列 判断加班时间是星期几和节假日 E F列 计算工龄 J列 3 9日期及时间函数 1 在D2输入公式 INT C2 B2 24 60 30 60 将此公式向下填充复制 2 在E2输入公式 WEEKDAY B2 将此公式向下填充复制 3 在F2输入公式 IF OR E3 1 E3 7 是 若加班时间是节日 可直接在对应单元格中输入节日名称将此公式向下填充复制 3 9日期及时间函数 4 在J2输入公式 YEAR NOW YEAR I2 将此公式向下填充复制weekday返回值类型如下 1或者省略 数字1 星期天 到数字7 星期六 2 数字1 星期一 到数字7 星期天 3 数字0 星期一 到数字6 星期天 3 10字符函数 1 概述Excel提供了接近30个文本 字符方面的函数 用这些函数对工作表中的文本数据进行查找 替换 取子串 数字与文本的转换 以及数据的格式化操作等都有十分重要的作用 2 常用文本类函数RIGHT text n 该函数从text文本的右边取出n个字符 LEFT text n 该函数从text文本的左边取出n个字符 3 10字符函数 MID text n m 该函数从text文本的第n个字符起 取出m个字符 REPLACE text1 n m text2 该函数用text2替换text1文本的第n个位置开始的m个字符 SEARCH text1 text2 n 该函数从text1的第n个字符位置开始查找text1中是否包含text2 若找就返回text2在text1中的起始位置 若没有找到则返回错误值 value 3 10字符函数 TEXT value format text 该函数指定的格式码format text将数值型数据Value格式化为文本 其中value可为数字 结果为数字的计算公式 包含数字的单元格引用 format text可为任何数字格式 有关格式码请参考第2章的相关内容 VALUE text 将数字形式的文本转换成数字 比如 VALUE 32 32 VALUE 09 9 3 10字符函数 案例 例 文本函数应用举例某银行要建立下图3 所示的用户资料表 其中的出生日期从身份证号码中提取 如果是男性 其尊称就是 X先生 如果是女性 其尊称就是 X女士 X是用户的姓氏 存款帐号是出生日期 去掉出生年的前两位数字 存款密码是身份证号码的最后6位数字 最后要找出解放西路的用户姓名 以便给他们发新的银行卡 3 10字符函数 用文本类函数产生E2 I9区域的数据 3 10字符函数 1 产生出生日期在E2单元格输入公式 然后将它向下复制此公式就能够生成所有人的出生日期 MID D2 7 4 年 VALUE MID D2 11 2 月 MID D2 13 2 日 2 生成用户的尊称在F2单元格输入下面的公式 并将此公式向下复制 生成所有人的尊称 LEFT A2 1 IF B2 女 女士 先生 3 生成存款帐号在G2单元格输入下面的公式