第三章公式与函数的应用

第1页共6页第三章公式与函数的应用3.1创建公式一.输入公式和四则运算：必须先输入“=”二.相对引用、绝对引用和混合引用【习题132】在当前工作表中，计算“实发工资”，计算公式为：实发工资=工资+奖金-税金；然后利用“实发工资”和“税金”两列数据计算税率，计算公式为：税率=税金÷实发工资，并把所得的结果设置为保留两位小数的百分比样式。【习题133】用公式计算当前工作表中惠尔浦洗衣机年销售量占同类商品销售量的百分比，填写在H24单元格中。【习题134】根据当前工作表中的数据，在G8单元格中用公式计算7车间年产值占全厂年产值的百分比(保留3位小数点)。【习题135】在工作表中计算增长销售额，增长销售额=今年销售额-去年销售额，然后将“今年销售额”比“去年销售额”高的其“增长销售额”的数据标记为蓝色，否则标记为红色。【习题136】计算每月每种商品的销售额，并设置为货币格式，计算公式为：销售额=销售量×单价。【习题137】在C1单元格计算A1单元格的数值和B1单元格数值之和的三分之一。【习题138】计算1月销售额，再用相对引用计算2～12月销售额。三.引用其他工作表数据1.引用同一工作簿的其他工作表中的单元格：工作表名!单元格（或区域）的引用地址例：Sheet2!B1:C52.引用其他工作簿中的单元格[工作簿名称]工作表名!单元格（或区域）的引用地址‘[工作簿名称]工作表名’!单元格（或区域）的引用地址例：[Book2]Sheet3!A5:C6‘D:\[Book2]Sheet3’!A5:C6【习题140】设教师工资由基本工资、岗位津贴和课贴组成。在当前工作表F7单元格中用公式和相对引用的方式（不用函数）计算王文华的总工资收入。在当第2页共6页前工作表中已有基本工资，岗位津贴在sheet4的C6单元格，课贴在sheet3的C6单元格中。（公式中的地址不用鼠标单击单元格的方法）。【习题142】在“年级”工作表中引用C盘根目录下BOOK1.XLS中sheet1工作表中的数据，要求从sheet1工作表的A2单元格开始引用，使引用的结果填充“年级”工作表的A1：F7区域即可。准备工作：将素材文件夹下的“Book1”复制到C盘根目录下四.日期和时间的运算1.日期运算：=”2006-1-1”-“2005-1-1”2.时间运算：=”11:09:00”-“5:09:00”【习题143】在当前工作表的E4单元格中，利用时间运算求出T18次列车运行时间，E4单元格已设置为“时：分”的时间格式。（公式中不使用单元格数据的引用）【习题144】在A1单元格中求出2005年9月10日与2006年8月18日之间相差的天数。【习题146】在当前单元格中，利用日期运算求出应还书日期。（公式中不使用单元格数据的引用）【习题147】计算表中的飞行时间。【习题148】利用时间运算求出张三的马拉松成绩，单元格已设为分:秒格式，要求不用单元格引用3.2使用函数插入函数工具栏：【自动求和】按钮编辑栏：“fx”（【插入函数】按钮）输入“=”，名称框处选择函数一.求和（sum）、求平均（average）二.最大值（max）、最小值（min）输入公式的格式：=SUM(数值区域1[，数值区域2，……])=average(数值区域1[，数值区域2，……])=max(数值区域1[，数值区域2，……])第3页共6页=min(数值区域1[，数值区域2，……])【习题149】在当前单元格中，用公式和函数计算11月总产量占总年产量的百分比（当前单元格已设置为百分比格式）。【习题151】在当前工作表中，一次性地求出所有同学的各科平均分。【习题152】根据给出的数据，用函数求出（A1：G21）单元格区域中的最大值，并填写在I8单元格中。【习题160】在当前工作表中，直接输入函数公式计算学号为“02001”的学生总分，放在J3单元格。【习题168】在D2单元格用函数和公式计算总计销售额，即单价*四个季度总销售量三.计数函数Count、条件计数函数Countif输入公式的格式：=count(数值区域1[，数值区域2，……])=countif(数值区域,条件)图片1【习题153】在当前工作表中利用求和函数和计数函数的组合来求出每个同学各门功课的平均分。【习题155】在当前工作表E21单元格中，用函数统计参加英语考试的人数。【习题156】在当前工作表C15单元格中，用函数统计语文课的每周节数。【习题158】统计A2：H27这一区域中数字项的个数，并把结果放入J3单元格。【习题159】在工作表中J2单元格统计优秀率，即平均分大于等于85分的人数与总人数的百分比（当前单元格已经设为百分比格式），已知参加考试的总人数为25。要统计的数据区域统计条件第4页共6页【习题162】利用COUNTIF函数统计总评为“优秀”的学生人数，把统计结果放在H4单元格中；并在H11单元格中显示优秀率，要求以带两位小数的百分比样式显示。四.条件检测函数If、嵌套函数的应用输入公式的格式：=if(条件，符合条件是的结果，不符合条件是的结果)插入函数的操作：图片2【习题169】利用函数为学生填充总评，如果学生的平均分大于或等于90则在总评栏上显示“优秀”字样，否则什么也不显示。(要求直接输入公式，然后填充)【习题170】在当前工作表F7单元格中，用函数计算姓名为王文娟的工程师应缴税金金额。计算方法为：如果工资加奖金的金额超过1000元，税金为超过1000元部分的5%，否则，税金为0。【习题171】使用函数的嵌套对实际总产量进行评价，大于等于25000为优秀，小于25000大于等于15000为良好，其他为一般，将评价结果填写在F20单元格。（通过插入函数公式的方法）【习题172】在G3单元格中输入总评,并在G4:G10单元格区域中利用IF函数的嵌套计算每个学生的总评结果.平均分在90分以上的(含90分)为优秀,在75分到89分(含75分,89分)之间为良好,在74分(含74分)以下为中。【习题173】在工作表中，使用函数的嵌套，在平均分一列中进行计算，如果平均成绩大于或等于60分则显示“通过”，如果平均成绩小于60分则显示“不通过”。【习题175】通过函数判断，杨磊的总成绩是否全班最高分，如果是则在当前单元输入条件符合条件是的结果不符合条件是的结果第5页共6页格显示“是”，否则为“否”。【习题176】判断电视机前四个季度销售总额，并判断提成比例，如果销售总额70000,则提成为销售总额的0.05，否则为销售总额的0.03五.查找和引用：Lookup函数输入公式的格式：=LOOKUP(要查找的数值,要查找的区域,结果)插入函数的操作：图片3【习题177】使用函数在工作表中的K2单元格中放入学号为“02026”学生的平均分。【习题178】查找编号为“106”的记录的“工资总额”，并把所查到的结果放在J2单元格中。六.取整函数INT输入公式的格式：=int(数值)【习题180】在C6单元格对华盛顿气温进行取整运算。（直接输入公式）【习题181】在D6单元格用函数计算华盛顿“气温”的小数部分。七.舍入函数输入公式的格式：=ROUND(数值，要保留的小数位数)【习题183】在G2：G25区域中对C2：C25中的数值进行四舍五入保留十位数。（直接输入公式）要查找的数值要查找的区域结果第6页共6页【习题187】将D2:D9中的工资采用四舍五入的方法保留百位以上的数字，把结果放到E2：E9单元格区域中。【习题188】在G28单元格利用函数嵌套，计算英语成绩的平均值，四舍五入取两位小数。【习题189】在A1单元格利用函数，将123.566，在四舍五入的过程中取整八.时间函数（today）输入公式的格式：=today()【习题190】在当前选定单元格中输入当前系统日期，输入完毕通过编辑栏加以确认。