注:可有其它方法,以下方法仅供参考1．统计(1)在Sheet2中利用公式统计应发工资>=4500的人数,并把数据放入H2单元格。
单击H2单元,再单击公式->其它函数->统计-countif 函数Range: F列或F1:F101Criteria: >=4500在Sheet2中利用公式统计“电子技术”60至69分(含60与69)的人数,将数据放入J2单元格。
这个因为条件就是区间所以用-countifs 函数单击J2单元,再单击公式->其它函数->统计-countifs 函数->按如下图设定Criteria_range1:指定列,如电子技术Criteria1:第一个条件如>=60Criteria_range2:指定列,如电子技术Criteria2:第二个条件如<=692．设置条件格式在Sheet3工作表后添加工作表Sheet4,将Sheet2的A到F列复制到Sheet4。
对Sheet4中的应发工资列设置条件格式,凡就是低于4000的,一律显示为红色。
选中应发工资列->开始->条件格式->突出显示单元格规则->小于->设置相应条件格式注意上面,设置选项可下拉,可自定义格式3．筛选(1)将“库存表”中除仪器名称仅为“万用表”的行外,全部复制到Sheet2中;单机数据->->在仪器名称下拉选项中将万用表前的钩去掉,确定后将剩下的全部复制即可。
(2)将Sheet2中名称仅为“电流表”与“压力表”的“库存”分别改为20与30 ,并重新计算“库存总价”(库存总价= 库存* 单价);与上述的方法类似,只要先将电流表与压力表筛出来,再修改库存与总价单机数据->->在仪器名称下拉选项中,去掉其它的钩,只要电流表与压力表前面的钩,确定后修改库存与总价即可。
(3)对Sheet4数据进行筛选操作,要求只显示“已婚”的工资在3500到4000之间(含3500与4000)的信息行。
分别进行筛选:筛选已婚:数据->筛选->单击婚否下拉菜单->选择已婚筛选工资:单击工资下拉菜单->数字筛选->介于->按下图设定4．排序与列宽设定(1)将Sheet2表“库存总价”列宽调整为10,设置“进货日期”的列宽为“自动调整列宽”,并按“库存总价”降序排列;单击“库存总价”列->右键->列宽->设定10(也可按下面方法找到列宽)选中“进货日期”列->单击开始->格式->自动调整列宽排序:随便单击“库存总价”的一个单元格(注意不要选中整列),再单击数据->排序,选择降序,也可自定义排序,根据主要关键字或添加次要关键字进行排序。
如下面题目(2)在Sheet4中,以库存量为第一关键字(降序)、单价为第二关键字(升序)对数据行排序。
单击数据->排序->按下图设定,第二关键字可通过添加条件(3)将Sheet2表内容按总销售量降序排列(不包括平均数)选中除平均数外的数据->单击排序->设定条件即可(4)对Sheet3工作表,对月份采用自定义序列“一月”、“二月”、……次序排序。
单击数据->排序->次序->自定义序列->选择“一月”、“二月”、……5．高级筛选5、1对Sheet4进行高级筛选,筛选出单价大于等于1000的或库存大于等于60的数据行。
(提示:在原有区域显示筛选结果,高级筛选的条件可以写在H与I列的任意区域)(1)先在H与I列自己建立条件:注意单价或库存文字从原文档中copy不要自己写,自己写有可能格式不一样而发生错误注:同一行表示与的关系,对角表示或的关系假设题目改为单价在大于等于500,小于等于1000之间或库存大于等于60两个单价在同一行表示与的关系,即单价在500-1000之间,与库存>=60成对角表示或的关系。
(2)高级筛选单击->数据-高级列表区域:选择整个表格条件区域:选择刚才建立好的条件5、2对Sheet2工作表启用筛选,筛选出姓“李”或姓“陈”的、且基本工资大于等于3100的数据行。
其中筛选出姓“李”或姓“陈”,要求采用自定义筛选方式。
方法同上,条件设定如下,注意1:姓李,设成注意2,上述的关系为:基本工资与姓名或姓名与基本工资也就就是说姓“李”的且基本工资大于等于3100或姓“陈”且基本工资大于等于3100数据行6．单元格格式设定在Sheet2第A列之前增加一列:“学号,0001,0002,0003,……,0100”(其中……为具体编号);增加后如果输入0001,则显示的就是1,请将单元格的格式设定为文本选定单元格->右键->设置单元格格式->选择文本对于其它单元格,从A2下拉即可7．逻辑函数(1)在Sheet3的第G列后增加一列“通过否”,利用公式给出具体通过与否的数据:如果平均成绩≥80,则给出文字“通过”,否则给出文字“未通过”(不包括引号)。
单击H2单元格->单击公式->逻辑->IF函数,按下图设定IF函数的参数对于其它单元格,从H2下拉即可(2)要求:如果“高等数学”与“大学语文”的平均分大于等于85,显示“优秀”,否则显示为空。
单击单元格->单击公式->逻辑->IF函数,按下图设定IF函数的参数注意:显示为空,需要敲一个空格(3)也可在IF函数中嵌套其它函数,如题目要求英语大于80并且体育大于70,显示通过,否则不通过,这时需要IF函数中嵌套AND函数如下8．分类汇总在Sheet4中进行分类汇总,按通过否统计学生人数(显示在“学号”列),要求先显示通过的学生人数,再显示未通过的学生人数,显示到第2级(即不显示具体的学生信息)。
（1）先进行排序,因通过的先显示,所以就是降序（2）单击有数据的任意单元格,再单击数据->选择分类汇总,按如下设定（3）显示到第2级,请单击窗口左上方的2数字最终效果:9．数据的计算与复制将Sheet3中的“工资”每人增加10%;新增加一列(随便,如G列),计算=工资*1、1,计算好后,拖动得到整列,将G列拷贝到H列(注意,粘贴时选择只保留值),最后将H列再替换工资列。
(注:将借助的列,不要的删掉)10．插入图10、1对Sheet4,删除“小计”列及右边各列,在A1单元格中输入“图书”(不包括引号)。
对星期一到星期五的数据,生成“分离型三维饼图”,要求:(1)图例项为“星期一、星期二、……、星期五”(图例项位置默认);(2)图表标题改为“图书合计”,并添加数据标签;(3)数据标签格式为值与百分比(如:1234,15%);(4)将图表置于A6:G20的区域。
插入分离型三维饼图:选中数据->单击插入->饼图->分离型三维饼图修改图标题:选中标题即可修改添加数据标签:单击布局部->数据标签->其它数据标签选项->选择值与百分比10、2对Sheet3中数据,产生二维簇状柱形图。
其中“一月”、“二月”等为水平(分类)轴标签。
“总销售量”为图例项,并要求添加对数趋势线。
图表位置置于D1:K14区域。
(1)单击空白单元格->单击插入->柱形图->二维簇状柱形图(2)在出现的空白的图上右键->选择数据->单击添加系列名称:点P1(总销售量)系列值:选择p2到p13(销售列)(3)单击水平分类轴标签下的编辑,轴标签区域选择“一月”、“二月”…、(4)单击图中的柱形->右键->添加趋势线->对数(5)图表位置置于D1:K14区域,本人只会手动移动与手动放大缩小图,请会的小伙伴补充。
最后效果10、3对Sheet2:删除B、D、F、H、J、L、M列,为周一到周五的缺勤班级数制作三维簇状柱形图,要求:(1)以星期一、星期二等为水平(分类)轴标签;(2)以“缺勤班数”为图例项;(3)图表标题使用“缺勤班数统计”(不包括引号);(4)删除网格线;(5)设置坐标轴选项使其最小值为0、0;(6)将图表放置于A4:F15的区域。
(1)选择全部数据单元格->单击插入->柱形图->三维簇状柱形图,自动以星期一、星期二等为水平(分类)轴标签,以“缺勤班数”为图例项;如需要修改可右键->选择数据进行调整(2)单击图标题,手动可修改(3)点中网络线,delete即可(4)点中左侧的坐标->双击左键->出现坐标轴选项->设定最小值最后效果11．最佳名次对Sheet4:I1中输入“名次”(不包括引号),下面的各单元格利用公式按平均成绩,从高到低填入对应的名次(说明:当平均成绩相同时,名次相同,取最佳名次)。
单击->公式->函数->全部->RANK、EQ函数Number: 平均成绩中的某个单元格Ref: 平均成绩整列(注:此时要用绝对位置,即在行列号前面加上$,不加后面会跑走)Order :0降序,非0升序12．其它(1)合并单元格:开始->合并后居中(2)设定列宽:开始->格式->列宽(3)保留小数的位数:开始->数字中找(4)增加一列:如果也想有表格的外框,可用格式刷,先点有外框的表格,再点格式刷,最后点无外框的表格(5)套用表格格式:先选中表格数据->开始->选择相应的格式即可(6)求平均:公式->自动求与->平均值(7)手动挑选求平均求出Sheet1表中每班本周平均每天缺勤人数(小数取1位)并填入相应单元格中;(本周平均缺勤人数=本周缺勤总数/5)可以手动挑选相应的单元格进行相加再除总个数即可。