函数-逻辑数学时间文本函数

1、复习回顾复习回顾 o名字的应用（行列交叉点查找、建立函数或公式时使用名字、事后套用定名字的应用（行列交叉点查找、建立函数或公式时使用名字、事后套用定 义的名字、整行整列定义名字）义的名字、整行整列定义名字） o函数的输入（手动、向导）函数的输入（手动、向导） oExcel帮助（向导里的超链接、帮助（向导里的超链接、F1） oIf函数（条件，正确地结果，错误的结果）函数（条件，正确地结果，错误的结果） oSum函数函数 n参数是文本、逻辑值参与计算参数是文本、逻辑值参与计算 n参数是数组或引用，只计算数值，不计算文本逻辑值等参数是数组或引用，只计算数值，不计算文本逻辑值等 oSumif函数（区域

2、，条件，满足条件需求和的区域）函数（区域，条件，满足条件需求和的区域） oAverage 不对文本和逻辑值类数据进行计算；不对文本和逻辑值类数据进行计算； oAveragea要对文本和逻辑函数进行平均值计算要对文本和逻辑函数进行平均值计算 n0，true视为1，false视为0，空文本（“”）也作为0 oCount 统计数值型数据的个数统计数值型数据的个数 oCountA统计非空单元格的个数统计非空单元格的个数 oCountBlank统计空白单元格的个数统计空白单元格的个数 oCountif统计满足条件统计满足条件Criteria的单元格个数的单元格个数 oMax与min 本节课主要内容 o

3、逻辑函数逻辑函数（IF、AND、OR、NOT、TRUE、FALSE） o 数学与三角公式数学与三角公式 o 日期及时间函数日期及时间函数 o 文本函数文本函数 5.3 逻辑函数逻辑函数 EXCEL 中的逻辑函数共有6个，分别是IF、AND、OR、NOT、TRUE和FALSE 1IF函数函数 IF函数也称为条件函数，其功能是对指定的条件判断真假， 根据参数的真假值，返回不同结果。数据处理中，经常利用IF 函数对公式和数值进行条件检测。其语法格式如下： IF (logical_test,value_if_true,value_if_false) 其中，参数logical_test是一个条件表达式，

4、可以是比较表达式和 逻辑判断表达式，其结果返回值为TRUE和FALSE； Value_if_true表示logical_test为TRUE时的显示内容； Value_if_false表示logical_test为FALSE时的显示内容。 例如：工作表C5单元格的公式为 =IF（B5=0,”,A5/B5） o 这三个函数的语法格式分别为：这三个函数的语法格式分别为： AND (logical1,logical2, ) OR (logical1,logical2,) NOT (logical) o 其中，其中，AND和和OR函数中的参数函数中的参数Logical1, logical2, 表示表示

5、待测试的条件值或表达式，其结果可为待测试的条件值或表达式，其结果可为TRUE 或或 FALSE，最，最 多不超过多不超过30个。当使用个。当使用AND函数时，只有当运算对象都为函数时，只有当运算对象都为 TRUE的情况下，运算结果才为的情况下，运算结果才为TRUE，否则为，否则为FALSE；当执；当执 行行OR运算时，只有当运算对象都为运算时，只有当运算对象都为FALSE的情况下，运算结的情况下，运算结 果才为果才为FALSE，否则为，否则为TRUE。 o NOT函数中的函数中的Logical为可以计算出为可以计算出 TRUE 或或 FALSE 的逻的逻 辑值或逻辑表达式。辑值或逻辑表达式。N

6、OT函数的功能是对该表达式运算函数的功能是对该表达式运算结果取结果取 相反值。相反值。 2 2、 ANDAND、OROR、 NOTNOT函数函数 o 假设在一个工作表的假设在一个工作表的A5、B5、C5单元格中的单元格中的 数值分别为数值分别为40、50、60，则有公式，则有公式 =AND(A5=60、B5=60、C5=60) o =FALSE， o =OR(A5=60、B5=60、C5=60) o =TRUE o =NOT(AND(A5B5,B5C5) o =FALSE 使用使用AND与与OR函数时，函数时，说明说明 o 参数必须是逻辑值，或者包含逻辑值的数组或引用 o 如果指定的单元格区域

7、包括非逻辑值，则AND函数将会返 回错误值”#VALUE!”。 o 在Excel中只有数字0被视为false,非0数字都被视为true。 o 逻辑函数一般需要结合IF、SUM、COUNT等需要判断条 件的函数使用。 TRUE函数和FALSE函数都是无参函数，其结果分别是返回逻辑值TRUE和FALSE。 3、TRUE和和FALSE函数函数 5.3 逻辑函数逻辑函数 o 案例案例 n 某单位有某单位有3000名名 职工，要按其工资职工，要按其工资 缴纳个人收入所得缴纳个人收入所得 税，税率与工资的税，税率与工资的 关系如表所示。关系如表所示。 n该单位的职工表如下该单位的职工表如下 所示，用所示，

8、用IF函数求出函数求出 各职工的税率！各职工的税率！ 工 资税 率 3000以上税率=30% 25003000税率=25% 20002500税率=20% 15002000税率=15% 1500以下税率=10% 5.3 逻辑函数逻辑函数 在在E4中输入编中输入编 辑栏中的公式！辑栏中的公式！ 见见1 逻辑函数逻辑函数.xls 练习一：销售提成计算练习一：销售提成计算 o 问题描述：如图所示为某公司销售部如图所示为某公司销售部5月份各地办事月份各地办事 处的销售额及其销售费用情况，请根据右边的奖金提处的销售额及其销售费用情况，请根据右边的奖金提 成政策判别各个办事处的奖金提成比例，并计算奖金成政策

9、判别各个办事处的奖金提成比例，并计算奖金 提成金额。（见练习提成金额。（见练习-逻辑函数逻辑函数.xls） 销售提成数据与政策表格销售提成数据与政策表格 练习二：考试成绩评判练习二：考试成绩评判 （见练习（见练习-逻辑函数逻辑函数.xls） 练习三 o 案例：如图为跳水比赛的某一单轮成绩计算表，按照跳水案例：如图为跳水比赛的某一单轮成绩计算表，按照跳水 比赛计分规则：跳水比赛要进行六轮，每轮分别打分，最比赛计分规则：跳水比赛要进行六轮，每轮分别打分，最 后按照六轮成绩累加的结果进行排名。每轮成绩的计算，后按照六轮成绩累加的结果进行排名。每轮成绩的计算， 需要八位裁判分别打分（满分为需要八位裁判

10、分别打分（满分为10分），然后根据八位裁分），然后根据八位裁 判打分的总分，减去最高分和最低分，最后在结果上难度判打分的总分，减去最高分和最低分，最后在结果上难度 系数乘系数乘3之后，得到本轮的选手成绩（这里还乘上之后，得到本轮的选手成绩（这里还乘上3，是因，是因 为难度系数一般在为难度系数一般在3.3左右，乘上左右，乘上3可以使得选手成绩在可以使得选手成绩在 100左右）。左右）。 o 见见:练习逻辑函数练习逻辑函数.xls oABS(n) 返回数字的绝对值返回数字的绝对值 oINT(n) 将数字向下舍入到最接近的整数将数字向下舍入到最接近的整数 oTRUNC(n,n_d) 将数字的小数部分

11、截去，返回整数将数字的小数部分截去，返回整数(不四舍五入不四舍五入) oROUND 按指定位数四舍五入某个数字按指定位数四舍五入某个数字 oSIGN 返回数字符号。返回数字符号。 oPRODUCT(n1,n2,) 将参数数字相乘，返回乘积值将参数数字相乘，返回乘积值 oMOD(n,div)返回两数相除的余数，其结果符号与除数相同返回两数相除的余数，其结果符号与除数相同 oSQRT(n) 返回某一正数算术平方根返回某一正数算术平方根 oFACT(n) 返回数的阶乘返回数的阶乘 oPOWER(x,n)返回给定数字的乘幂（与返回给定数字的乘幂（与“”运算符相同）运算符相同） oRAND() 返回一个

12、大于等于返回一个大于等于0 小于小于1 的随机数的随机数 oRANDBETWEEN（bottom，top）产生位于两个指定数值之间的一产生位于两个指定数值之间的一 个随机数个随机数 oMINVERSE(array) 返回矩阵的逆返回矩阵的逆 oMMULT(array1, array2) 返回两个或多个矩阵的乘积返回两个或多个矩阵的乘积 5.4 数学和三角函数数学和三角函数 练习（交作业）练习（交作业） o若某学生想做一个课程成绩表，根据计算方法，表若某学生想做一个课程成绩表，根据计算方法，表 格设置如下，其中平时作业总共格设置如下，其中平时作业总共5次，每次满分次，每次满分20分分 平时成绩为

13、平时成绩为5次之和；期中考试，期末上机成绩和期末次之和；期中考试，期末上机成绩和期末 笔试成绩总分为笔试成绩总分为100分，学期总成绩通过如下结构公分，学期总成绩通过如下结构公 式加权计算求出：式加权计算求出： 学期总成绩学期总成绩=平时成绩平时成绩*15%+期中考试期中考试*25%+（上机成绩（上机成绩*30%+笔试成绩笔试成绩*70%）*60% 本题的公式不算复杂，就是数据输入量比较大，如果不是进行真正的本题的公式不算复杂，就是数据输入量比较大，如果不是进行真正的 班级成绩处理而仅仅是练习，请设计如下形式的表格，并利用随机函班级成绩处理而仅仅是练习，请设计如下形式的表格，并利用随机函 数产

14、生相关数据（数产生相关数据（2020个学生即可）个学生即可） 学号姓名 平时成绩 期中 考试 期末成绩 学期 总成 绩 第1次第2次第3次第4次第5次上机 成绩 笔试 成绩 作业要求作业要求 o 建立工作表框架，制作表格框架，注意表格中边框和建立工作表框架，制作表格框架，注意表格中边框和 底纹的设置效果，以及某些单元格的合并居中效果的底纹的设置效果，以及某些单元格的合并居中效果的 设置，学生个数下面只设置到设置，学生个数下面只设置到20行。行。 o 学号的输入。采用自定义的方法输入，形如学号的输入。采用自定义的方法输入，形如PKM- 001，PKM-002。 o 姓名的输入。作为练习，姓名按实

15、际输入也没有意义。姓名的输入。作为练习，姓名按实际输入也没有意义。 此处采用此处采用“学生学生1、学生、学生2、”的命名方式。的命名方式。 o 平时作业评分都在平时作业评分都在10-20分之间分之间 o 期中考试都及格期中考试都及格 o 期末考试成绩在期末考试成绩在45-100之间之间 o 所有的分数均取整数所有的分数均取整数 案例及练习：按实发工资总额计算每种面额钞票的张数案例及练习：按实发工资总额计算每种面额钞票的张数 问题描述：问题描述：在工资数据处理中，每位职工的实发工资已求出，需要进行工资发放。现 在想在发给每个职工钞票张数最少的情况下（没有拖欠工资情况），根据所有职工实 发工资的总

16、数，计算出财务部门应该准备的每种面额的总张数，做成如下图形式。 见见2 数学与三角函数数学与三角函数-案例及练习案例及练习.xls 5.5日期及时间函数日期及时间函数 1YEAR，MONTH，DAY函数函数 n用法用法 o YEAR(serial_number) o MONTH(serial_number) o DAY(serial_number) o 其中的其中的serial_number是一个日期或数字。是一个日期或数字。 n功能功能 o YEAR函数返回某日期的年份。函数返回某日期的年份。 o MONTH函数返回以系列数表示的日期中的月份。函数返回以系列数表示的日期中的月份。 o DAY

17、函数返回以系列数表示的某日期的天数，用整数函数返回以系列数表示的某日期的天数，用整数 131表示。表示。 示例：公式示例：公式“=YEAR(“2008/5/15”)”返回返回2008 5.5日期及时间函数日期及时间函数 2TODAY、NOW函数函数 n 用法用法 o TODAY( ) o NOW( ) n 功能功能 o TODAY函数返回系统的当前日期。函数返回系统的当前日期。 o NOW函数计算当前日期和时间。函数计算当前日期和时间。 示例：在任意一个单元格中输入公式示例：在任意一个单元格中输入公式 “=TODAY（）（）”，都得到电脑系统的当前，都得到电脑系统的当前 日期。日期。 说明：T

18、ODAY函数是一个无参函数，但是在应函数是一个无参函数，但是在应 用时候后面的左右括号不能漏掉。用时候后面的左右括号不能漏掉。 oDATE函数返回代表特定日期的序列号。其语法格式为：函数返回代表特定日期的序列号。其语法格式为： DATE(year,month,day) 其中的三个参数中：其中的三个参数中： oYear为年份，也可以为年份的序列号，用一到四位数字表示。为年份，也可以为年份的序列号，用一到四位数字表示。 oMonth为月份，如果所输入的月份大于为月份，如果所输入的月份大于12，将从指定年份下一年的，将从指定年份下一年的 一月份开始往上加算。如：一月份开始往上加算。如：“DATE(2

19、008,14,2) ”返回代表返回代表 2009年年2月月2日的序列号。日的序列号。 oDay代表在该月份中的天数，如果代表在该月份中的天数，如果 day 大于该月份的最大天数，则大于该月份的最大天数，则 将从指定月份的下一月的第一天开始往上累加。如：将从指定月份的下一月的第一天开始往上累加。如： “DATE(2008,5,50)” 将返回将返回2008 年年6月月19日。日。 示例：在有些数据库中，日期的年、月、日数字可能分为三个字段来保示例：在有些数据库中，日期的年、月、日数字可能分为三个字段来保 存，当导入到存，当导入到ExcelExcel工作表后，就可以利用工作表后，就可以利用DATE

20、DATE函数将年、月、日合函数将年、月、日合 并成一个具体的日期数据。假设单元格并成一个具体的日期数据。假设单元格A1A1是年份数字，是年份数字，B1B1是月份数字，是月份数字， C1C1是日数，则公式是日数，则公式“=DATE=DATE（A1A1，B1B1，C1C1）”就将就将A1A1的年份数字，的年份数字，B1B1 的月份数字和的月份数字和C1C1的日数，合并到一起组成了一个具体的日期数据。的日数，合并到一起组成了一个具体的日期数据。 5.5日期及时间函数日期及时间函数 5.5日期及时间函数日期及时间函数 4、WEEKDAY n用法用法 o WEEKDAY(serial_number, r

21、eturn_type) o serial_number代表要查找的日期，或日期的系列数，以代表要查找的日期，或日期的系列数，以 了解日期为星期几；了解日期为星期几；return_type确定返回值类型数字。确定返回值类型数字。 n功能功能 o WEEKDAY计算给定的日期是星期几计算给定的日期是星期几 Return_type返回的数字 1或省略数字1（星期天）到数字7（星期六） 2数字1（星期一）到数字7（星期日） 3数字0（星期一）到数字6（星期日） 示例：2008年5月15日是星期四，则： “=WEEKDAY(”2008/5/15“) ”返回“5”； “=WEEKDAY(”2008/5/1

22、5“,2) ”返回“4”； “=WEEKDAY(2008/5/15,3) ”返回“3” 5.5日期及时间函数日期及时间函数 4、NETWORKDAYS函数 n用法 o NETWORKDAYS(start_date, end_date, holidays) n其中 o start_date表示代表开始日期，end_date为终止日期， holidays表示不在工作日历中的一个或多个日期所构成的可 选区域，如元旦节、五一节、春节。 示例：公式示例：公式“=NETWORKDAYS(2008/5/1, 2008/5/10,2008/5/1)”返回返回6。 5.5日期及时间函数日期及时间函数 o 案例案

23、例 n某建筑公司按天数计算每个民工的报酬，民工每个星期工作某建筑公司按天数计算每个民工的报酬，民工每个星期工作5天，工天，工 作时间和请假时间如图的作时间和请假时间如图的AH列所示，列所示，I列是每天的报酬，用日期函列是每天的报酬，用日期函 数计算每个民工的工作天数和总工资。数计算每个民工的工作天数和总工资。 见见3 时间日期函数时间日期函数.xls o 案例：某公司为体现人文关怀，想在员工生日当天为其庆案例：某公司为体现人文关怀，想在员工生日当天为其庆 祝生日并送上礼物。为了能够事先知道当天有哪些人过生祝生日并送上礼物。为了能够事先知道当天有哪些人过生 日，公司根据人事档案制作了一个生日提醒

24、表格。日，公司根据人事档案制作了一个生日提醒表格。 5.5日期及时间函数日期及时间函数 见见3 时间日期函数时间日期函数.xls 问题描述：如图所示，问题描述：如图所示，A2A2：G6G6单元格区域是某公司一个部门单元格区域是某公司一个部门 的加班记录表，表中已经记录了每个人的加班日期以及加班的加班记录表，表中已经记录了每个人的加班日期以及加班 的开始时间和结束时间。加班工资按小时计算，为了方便计的开始时间和结束时间。加班工资按小时计算，为了方便计 算，若加班时间超过半个小时的就按一个小时计算，但是低算，若加班时间超过半个小时的就按一个小时计算，但是低 于半小时的部分不再计算；一般工作日的加班

25、工资标准为于半小时的部分不再计算；一般工作日的加班工资标准为2020 元元/ /小时，节假日为小时，节假日为4040元元/ /小时。小时。 根据以上原则，请通过公式运算将下图中的根据以上原则，请通过公式运算将下图中的E E、F F、G G列进行内列进行内 容填充；并计算每个员工的总加班时间、节假日加班时间以容填充；并计算每个员工的总加班时间、节假日加班时间以 及加班工资。及加班工资。 练习：加班工资计算练习：加班工资计算 文本函数文本函数(补充补充) o1TEXT函数函数 oTEXT函数的功能是将数值转换为按指定数字格式表示的文本。其语法函数的功能是将数值转换为按指定数字格式表示的文本。其语法

26、 格式为：格式为： oTEXT（value,format_text） o其中包括两个参数，意义分别为：其中包括两个参数，意义分别为： oValue为数值、计算结果是数值的公式或对数值单元格的引用；为数值、计算结果是数值的公式或对数值单元格的引用； oformat_text为所要选用的文本型数字格式，即为所要选用的文本型数字格式，即“单元格格式单元格格式”对话对话 框框“数字数字”选项卡的选项卡的“分类分类”列表框中显示的文本形式的数字格式，它列表框中显示的文本形式的数字格式，它 不能包含星号不能包含星号“*”。 o示例：如果工作表中示例：如果工作表中A1单元格的数值为单元格的数值为2986.6

27、38，则公式，则公式 “=TEXT(A1,#，#0.00)”返回字符串返回字符串“2，986.64”。 o说明：使用使用“单元格格式单元格格式”对话框的对话框的“数字数字”选项卡设置单元格格式，选项卡设置单元格格式， 只会改变单元格的格式而不会影响其中的数值。而使用只会改变单元格的格式而不会影响其中的数值。而使用TEXT函数可以函数可以 将数值转换为带格式的文本，而其结果将不再作为数字参与计算。将数值转换为带格式的文本，而其结果将不再作为数字参与计算。 VALUE函数的功能是将代表数字的文本字符串转换成数字。其语法格式为：函数的功能是将代表数字的文本字符串转换成数字。其语法格式为： VALUE

28、（text） 其中参数其中参数text为带引号的文本，或对需要进行文本转换的单元格的引用，还可以为带引号的文本，或对需要进行文本转换的单元格的引用，还可以 是是Excel 中可识别的日期或时间格式。中可识别的日期或时间格式。 如果如果text不是上述格式，则不是上述格式，则VALUE函数返回错误值函数返回错误值“ #VALUE!”。 示例：公式示例：公式“=VALUE (￥1,000)”返回返回1000。 oLOWER功能是将一个文本字符串中的所有大写字母转换为小写字母。其语法格功能是将一个文本字符串中的所有大写字母转换为小写字母。其语法格 式为：式为： LOWER（text） 其中参数其中参

29、数text是要转换为小写字母的文本，它可以是引用或文字串。是要转换为小写字母的文本，它可以是引用或文字串。 示例：如果示例：如果A1中有字符串中有字符串“EXCEL”，则公式，则公式“=LOWER(A1)”返回字符串返回字符串 “excel”。 说明：LOWER函数不改变文本中的非字母字符。与函数不改变文本中的非字母字符。与LOWER函数类似，函数类似，Excel 还有还有UPPER函数和函数和PROPER函数，其中：函数，其中：UPPER函数将一个文本字符串中所函数将一个文本字符串中所 有小写字母转换为大写字母的，而有小写字母转换为大写字母的，而PROPER函数将文本字符串的首字母和任何函数

30、将文本字符串的首字母和任何 非字母字符之后的首字母转换成大写、其余字母转换成小写，它们的语法格式和非字母字符之后的首字母转换成大写、其余字母转换成小写，它们的语法格式和 LOWER函数相同。函数相同。 oTRIM函数清除文本中的所有空格（除了单词之间的单个空格外）。其语法格式为：函数清除文本中的所有空格（除了单词之间的单个空格外）。其语法格式为： oTRIM(text) o其中参数其中参数text是需要清除其中空格的文本。是需要清除其中空格的文本。 o示例：如果工作表示例：如果工作表A1单元格的数据为字符串单元格的数据为字符串“ First Quarter Earnings ”， 则公式则公式

31、“=TRIM(A1)”返回字符串返回字符串“First Quarter Earnings”。 oLEN函数的功能是返回文本字符串中的字符数。其语法格式为：函数的功能是返回文本字符串中的字符数。其语法格式为：LEN(text) o其中参数其中参数text为要查找其长度的文本，空格也将作为字符进行计数。为要查找其长度的文本，空格也将作为字符进行计数。 o示例：如果示例：如果A1单元格中字符串为单元格中字符串为“电脑电脑 爱好者爱好者”，则公式，则公式“=LEN(A1)”返回返回6。 oCONCATENATE函数的功能是将若干字符串合并成一个字符串，其功能与文本函数的功能是将若干字符串合并成一个字符

32、串，其功能与文本 连接运算符连接运算符“&”相同。其语法格式为：相同。其语法格式为：CONCATENATE(text1,text2,) o其中的参数其中的参数text1,text2,为为1到到30个将要合并成单个文本的文本项，这些文本个将要合并成单个文本的文本项，这些文本 项可以是文字串、数字或对单个单元格的引用。项可以是文字串、数字或对单个单元格的引用。 o示例：公式示例：公式“=CONCATENATE(97,千米千米)”返回返回“97千米千米”。 oLEFT函数的功能是根据所指定的字符数从文本字符串左边取出第一个或前几函数的功能是根据所指定的字符数从文本字符串左边取出第一个或前几 个字符。

33、也就是说，个字符。也就是说，LEFT函数对字符串进行函数对字符串进行“左截取左截取”，其语法格式为：，其语法格式为： oLEFT（text,num_chars） o其中参数其中参数Text是要提取字符的文本字符串；是要提取字符的文本字符串； Num_chars是指定提取的字是指定提取的字 符个数，它必须大于或等于符个数，它必须大于或等于0。如果省略参数。如果省略参数 num_chars，默认值为，默认值为 1。 如果参数如果参数 num_chars 大于文本长度，则大于文本长度，则LEFT函数返回所有文本。函数返回所有文本。 o示例：公式示例：公式“=LEFT(“电脑爱好者电脑爱好者”,2)”

34、返回字符串返回字符串“电脑电脑”。 oMID函数的功能是返回文本字符串中从指定位置开始的特定数目的字符，该函数的功能是返回文本字符串中从指定位置开始的特定数目的字符，该 数目由用户指定。其语法格式为：数目由用户指定。其语法格式为： oMID(text,start_num,num_chars) o其中参数其中参数text为要提取字符的文本字符串；为要提取字符的文本字符串；start_num为要提取的第一个为要提取的第一个 字符的位置（文本中第一个字符的位置为字符的位置（文本中第一个字符的位置为 1，其余依此类推）；，其余依此类推）； num_chars为指定提取的字符个数。为指定提取的字符个数。 o示例：公式示例：公式“=MID(“电脑爱好者电脑爱好者”,3,2)”返回字符串返回字符串“爱好爱好”。 o REPLACE函数的功能是根据所指定的字符数，使用其它文函数的功能是根据所指定的字符数，使用其它文 本字符串替换当前文本字符串中的部分文本。其语法格式为：本字符串替换当前文本字符串中的部分文本。其语法格式为： o REP