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

1、复习回顾,名字的应用（行列交叉点查找、建立函数或公式时使用名字、事后套用定义的名字、整行整列定义名字） 函数的输入（手动、向导） Excel帮助（向导里的超链接、F1） If函数（条件，正确地结果，错误的结果） Sum函数 参数是文本、逻辑值参与计算 参数是数组或引用，只计算数值，不计算文本逻辑值等 Sumif函数（区域，条件，满足条件需求和的区域） Average 不对文本和逻辑值类数据进行计算； Averagea要对文本和逻辑函数进行平均值计算 文本视为0，true视为1，false视为0，空文本（“”）也作为0计算 Count 统计数值型数据的个数 CountA统计非空单元格的个数 Co

2、untBlank统计空白单元格的个数 Countif统计满足条件Criteria的单元格个数 Max与min,本节课主要内容,逻辑函数（IF、AND、OR、NOT、TRUE、FALSE） 数学与三角公式 日期及时间函数 文本函数,5.3 逻辑函数,EXCEL 中的逻辑函数共有6个，分别是IF、AND、OR、NOT、TRUE和FALSE,1IF函数 IF函数也称为条件函数，其功能是对指定的条件判断真假，根据参数的真假值，返回不同结果。数据处理中，经常利用IF函数对公式和数值进行条件检测。其语法格式如下： IF (logical_test,value_if_true,value_if_false)

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

4、或表达式，其结果可为TRUE 或 FALSE，最多不超过30个。当使用AND函数时，只有当运算对象都为TRUE的情况下，运算结果才为TRUE，否则为FALSE；当执行OR运算时，只有当运算对象都为FALSE的情况下，运算结果才为FALSE，否则为TRUE。 NOT函数中的Logical为可以计算出 TRUE 或 FALSE 的逻辑值或逻辑表达式。NOT函数的功能是对该表达式运算结果取相反值。,2、 AND、OR、 NOT函数,假设在一个工作表的A5、B5、C5单元格中的数值分别为40、50、60，则有公式=AND(A5=60、B5=60、C5=60) =FALSE， =OR(A5=60、B5=

5、60、C5=60) =TRUE =NOT(AND(A5B5,B5C5) =FALSE,使用AND与OR函数时，说明,参数必须是逻辑值，或者包含逻辑值的数组或引用 如果指定的单元格区域包括非逻辑值，则AND函数将会返回错误值”#VALUE!”。 在Excel中只有数字0被视为false,非0数字都被视为true。 逻辑函数一般需要结合IF、SUM、COUNT等需要判断条件的函数使用。,TRUE函数和FALSE函数都是无参函数，其结果分别是返回逻辑值TRUE和FALSE。,3、TRUE和FALSE函数,5.3 逻辑函数,案例 某单位有3000名职工，要按其工资缴纳个人收入所得税，税率与工资的关系如

6、表所示。 该单位的职工表如下所示，用IF函数求出各职工的税率！,5.3 逻辑函数,在E4中输入编辑栏中的公式！,见1 逻辑函数.xls,练习一：销售提成计算,问题描述：如图所示为某公司销售部5月份各地办事处的销售额及其销售费用情况，请根据右边的奖金提成政策判别各个办事处的奖金提成比例，并计算奖金提成金额。（见练习-逻辑函数.xls） 销售提成数据与政策表格,练习二：考试成绩评判,（见练习-逻辑函数.xls）,练习三,案例：如图为跳水比赛的某一单轮成绩计算表，按照跳水比赛计分规则：跳水比赛要进行六轮，每轮分别打分，最后按照六轮成绩累加的结果进行排名。每轮成绩的计算，需要八位裁判分别打分（满分为1

7、0分），然后根据八位裁判打分的总分，减去最高分和最低分，最后在结果上难度系数乘3之后，得到本轮的选手成绩（这里还乘上3，是因为难度系数一般在3.3左右，乘上3可以使得选手成绩在100左右）。 见:练习逻辑函数.xls,ABS(n) 返回数字的绝对值 INT(n) 将数字向下舍入到最接近的整数 TRUNC(n,n_d) 将数字的小数部分截去，返回整数(不四舍五入) ROUND 按指定位数四舍五入某个数字 SIGN 返回数字符号。 PRODUCT(n1,n2,)将参数数字相乘，返回乘积值 MOD(n,div)返回两数相除的余数，其结果符号与除数相同 SQRT(n)返回某一正数算术平方根 FACT(

8、n)返回数的阶乘 POWER(x,n)返回给定数字的乘幂（与“”运算符相同） RAND() 返回一个大于等于0 小于1 的随机数 RANDBETWEEN（bottom，top）产生位于两个指定数值之间的一个随机数 MINVERSE(array) 返回矩阵的逆 MMULT(array1, array2) 返回两个或多个矩阵的乘积,5.4 数学和三角函数,练习（交作业）,若某学生想做一个课程成绩表，根据计算方法，表格设置如下，其中平时作业总共5次，每次满分20分平时成绩为5次之和；期中考试，期末上机成绩和期末笔试成绩总分为100分，学期总成绩通过如下结构公式加权计算求出： 学期总成绩=平时成绩*1

9、5%+期中考试*25%+（上机成绩*30%+笔试成绩*70%）*60% 本题的公式不算复杂，就是数据输入量比较大，如果不是进行真正的班级成绩处理而仅仅是练习，请设计如下形式的表格，并利用随机函数产生相关数据（20个学生即可）,作业要求,建立工作表框架，制作表格框架，注意表格中边框和底纹的设置效果，以及某些单元格的合并居中效果的设置，学生个数下面只设置到20行。 学号的输入。采用自定义的方法输入，形如PKM-001，PKM-002。 姓名的输入。作为练习，姓名按实际输入也没有意义。此处采用“学生1、学生2、”的命名方式。 平时作业评分都在10-20分之间 期中考试都及格 期末考试成绩在45-10

10、0之间 所有的分数均取整数,案例及练习：按实发工资总额计算每种面额钞票的张数,问题描述：在工资数据处理中，每位职工的实发工资已求出，需要进行工资发放。现在想在发给每个职工钞票张数最少的情况下（没有拖欠工资情况），根据所有职工实发工资的总数，计算出财务部门应该准备的每种面额的总张数，做成如下图形式。,见2 数学与三角函数-案例及练习.xls,5.5日期及时间函数,1YEAR，MONTH，DAY函数 用法 YEAR(serial_number) MONTH(serial_number) DAY(serial_number) 其中的serial_number是一个日期或数字。 功能 YEAR函数返回

11、某日期的年份。 MONTH函数返回以系列数表示的日期中的月份。 DAY函数返回以系列数表示的某日期的天数，用整数131表示。 示例：公式“=YEAR(“2008/5/15”)”返回2008,5.5日期及时间函数,2TODAY、NOW函数 用法 TODAY( ) NOW( ) 功能 TODAY函数返回系统的当前日期。 NOW函数计算当前日期和时间。 示例：在任意一个单元格中输入公式“=TODAY（）”，都得到电脑系统的当前日期。 说明：TODAY函数是一个无参函数，但是在应用时候后面的左右括号不能漏掉。,3、 DATE函数 DATE函数返回代表特定日期的序列号。其语法格式为：DATE(year,

12、month,day) 其中的三个参数中： Year为年份，也可以为年份的序列号，用一到四位数字表示。 Month为月份，如果所输入的月份大于12，将从指定年份下一年的一月份开始往上加算。如：“DATE(2008,14,2) ”返回代表 2009年2月2日的序列号。 Day代表在该月份中的天数，如果 day 大于该月份的最大天数，则将从指定月份的下一月的第一天开始往上累加。如：“DATE(2008,5,50)” 将返回2008 年6月19日。 示例：在有些数据库中，日期的年、月、日数字可能分为三个字段来保存，当导入到Excel工作表后，就可以利用DATE函数将年、月、日合并成一个具体的日期数据。

13、假设单元格A1是年份数字，B1是月份数字，C1是日数，则公式“=DATE（A1，B1，C1）”就将A1的年份数字，B1的月份数字和C1的日数，合并到一起组成了一个具体的日期数据。,5.5日期及时间函数,5.5日期及时间函数,4、WEEKDAY 用法 WEEKDAY(serial_number, return_type) serial_number代表要查找的日期，或日期的系列数，以了解日期为星期几；return_type确定返回值类型数字。 功能 WEEKDAY计算给定的日期是星期几,示例：2008年5月15日是星期四，则： “=WEEKDAY(”2008/5/15“) ”返回“5”； “=W

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

15、的报酬，民工每个星期工作5天，工作时间和请假时间如图的AH列所示，I列是每天的报酬，用日期函数计算每个民工的工作天数和总工资。,见3 时间日期函数.xls,案例：某公司为体现人文关怀，想在员工生日当天为其庆祝生日并送上礼物。为了能够事先知道当天有哪些人过生日，公司根据人事档案制作了一个生日提醒表格。,5.5日期及时间函数,见3 时间日期函数.xls,问题描述：如图所示，A2：G6单元格区域是某公司一个部门的加班记录表，表中已经记录了每个人的加班日期以及加班的开始时间和结束时间。加班工资按小时计算，为了方便计算，若加班时间超过半个小时的就按一个小时计算，但是低于半小时的部分不再计算；一般工作日的

16、加班工资标准为20元/小时，节假日为40元/小时。 根据以上原则，请通过公式运算将下图中的E、F、G列进行内容填充；并计算每个员工的总加班时间、节假日加班时间以及加班工资。,练习：加班工资计算,文本函数(补充),常用文本函数介绍 1TEXT函数 TEXT函数的功能是将数值转换为按指定数字格式表示的文本。其语法格式为： TEXT（value,format_text） 其中包括两个参数，意义分别为： Value为数值、计算结果是数值的公式或对数值单元格的引用； format_text为所要选用的文本型数字格式，即“单元格格式”对话框“数字”选项卡的“分类”列表框中显示的文本形式的数字格式，它不能包

17、含星号“*”。 示例：如果工作表中A1单元格的数值为2986.638，则公式“=TEXT(A1,#，#0.00)”返回字符串“2，986.64”。 说明：使用“单元格格式”对话框的“数字”选项卡设置单元格格式，只会改变单元格的格式而不会影响其中的数值。而使用TEXT函数可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。,常用文本函数介绍,2VALUE函数 VALUE函数的功能是将代表数字的文本字符串转换成数字。其语法格式为： VALUE（text） 其中参数text为带引号的文本，或对需要进行文本转换的单元格的引用，还可以是Excel 中可识别的日期或时间格式。 如果text不是上

18、述格式，则VALUE函数返回错误值“ #VALUE!”。 示例：公式“=VALUE (￥1,000)”返回1000。 3LOWER函数 LOWER功能是将一个文本字符串中的所有大写字母转换为小写字母。其语法格式为： LOWER（text） 其中参数text是要转换为小写字母的文本，它可以是引用或文字串。 示例：如果A1中有字符串“EXCEL”，则公式“=LOWER(A1)”返回字符串“excel”。 说明：LOWER函数不改变文本中的非字母字符。与LOWER函数类似，Excel还有UPPER函数和PROPER函数，其中：UPPER函数将一个文本字符串中所有小写字母转换为大写字母的，而PROPER函数将