第3章〓Excel 2019综合应用 脉络导图 3．1典型试题解答 【试题解答31】 1． 题目要求 在练习素材文件夹中，打开“试题解答31．xlsx”文件，按以下要求操作，完成后将文件保存。 (1) 删除Sheet1工作表中“平均分”所在行。 (2) 求出Sheet1工作表中每位同学的总分并填入“总分”列相应单元格中。 (3) 将Sheet1工作表中A3:B105和I3:I105区域内容复制到Sheet2工作表的A1:C103区域。 (4) 将Sheet2工作表内容按“总分”列数据降序排列。 (5) 在Sheet1工作表的“总分”列后增加一列“等级”，要求利用公式计算每位学生的等级。 要求： 如果“高等数学”和“大学语文”的平均分大于或等于85分，则显示“优秀”，否则显示为空。 说明： 显示为空也是根据公式得到的，如果修改了对应的成绩使其平均分大于或等于85分，则该单元格内容自动变为“优秀”。 (6) 在Sheet2工作表后添加工作表Sheet3，将Sheet1工作表中的数据复制到Sheet3。 (7) 对Sheet3各科成绩设置条件格式，凡是不及格(小于60分)的，一律显示为红色，加粗； 凡是大于或等于90分的，一律使用浅绿色背景色。 2． 操作步骤 (1) 删除Sheet1工作表中“平均分”所在行。 将光标定位到Sheet1工作表中第106行的左侧，右击，在弹出的快捷菜单中选择“删除”选项。 (2) 求出Sheet1工作表中每位同学的总分。 将光标移到Sheet1工作表的I4单元格中，单击编辑栏上的按钮，在弹出的“插入函数”对话框中，在“或选择类别”下拉列表中选择“数学与三角函数”选项，在“选择函数”列表框中选择SUM函数，如图31所示，单击“确定”按钮。 图31插入SUM函数 在“函数参数”对话框的Number1文本框中输入“C4:H4”，如图32所示，单击“确定”按钮。 图32设置SUM函数 将光标移到I4单元格右下角的填充柄上，按住鼠标左键向下填充到I105单元格即可。 (3) 将Sheet1工作表中A3:B105和I3:I105区域内容复制到Sheet2工作表的A1:C103区域。 选择Sheet1工作表中的A3:B105区域，然后按住Ctrl键，选择I3:I105区域，右击，在弹出的快捷菜单中选择“复制”选项(或按Ctrl+C组合键复制)。 将光标定位到Sheet2的A1单元格中，右击，在弹出的快捷菜单中选择“粘贴”选项(或按Ctrl+V组合键)。 (4) 将Sheet2工作表内容按“总分”列数据降序排列。 选择Sheet2工作表的A1:C103区域，单击“数据”→“排序和筛选”功能组中的“排序”按钮。 在弹出的对话框中，“主要关键字”选择“总分”选项，“次序”选择“降序”选项，如图33所示，单击“确定”按钮。 图33设置排序条件 (5) 在Sheet1工作表的“总分”列后增加一列“等级”，要求利用公式计算每位学生的等级。 定位至Sheet1工作表的J3单元格中，输入“等级”。 选择J4单元格，单击编辑栏上的按钮，在弹出的“插入函数”对话框中选择IF函数，单击“确定”按钮。 在“函数参数”对话框的Logical_test文本框中输入“AVERAGE(C4:D4)>=85”，在Value_if_true文本框中输入“"优秀"”，在Value_if_false文本框中输入空格(即“ ”)，如图34所示，单击“确定”按钮。生成的函数公式是“=IF(AVERAGE(C4:D4)>=85，"优秀"，" ")”。函数的嵌套还有其他方法，可选择最方便的操作步骤。 图34设置IF函数 将光标移到J4单元格右下角的填充柄上，按住鼠标左键向下拉到J105单元格即可。 (6) 在Sheet2工作表后添加工作表Sheet3，将Sheet1工作表中的数据复制到Sheet3。 将光标定位到窗口左下方工作表名称处，右击，在弹出的快捷菜单中选择“插入”选项，在弹出的“插入”对话框中选择“工作表”选项，如图35所示，单击“确定”按钮。 图35插入新工作表 注： 单击窗口左下方工作表名称右侧的“新工作表”按钮，可插入新工作表。 选择Sheet1的A1:J105区域(或在Sheet1中按Ctrl+A组合键全选)，右击，在弹出的快捷菜单中选择“复制”选项(或按Ctrl+C组合键复制)，将光标定位到Sheet3的A1单元格中，右击，在弹出的快捷菜单中选择“粘贴”选项(或按Ctrl+V组合键粘贴)。 (7) 对Sheet3各科成绩设置条件格式。 选择Sheet3工作表的C4:H105数据区域，单击“开始”→“样式”功能组中的“条件格式”下拉按钮，选择“管理规则”选项。 在弹出的对话框中，单击“新建规则”按钮，在弹出的“新建格式规则”对话框的“选择规则类型”列表中选择第2项“只为包含以下内容的单元格设置格式”选项。 在下方的设置中依次设置为“单元格值”“小于”“60”，如图36所示。 图36设置条件格式 单击“格式”按钮，在弹出的对话框的“字体”选项卡中设置“字形”为“加粗”，“颜色”为“红色”，如图37所示，单击“确定”按钮。 图37设置字体格式 继续单击“新建规则”按钮，选择第2项“只为包含以下内容的单元格设置格式”选项，设置“单元格值”“大于或等于”“90”。 单击“格式”按钮，在弹出的对话框的“填充”选项卡中设置“背景色”为“浅绿色”，如图38所示，单击“确定”按钮，弹出“条件格式规则管理器”对话框，单击“确定”按钮，结果如图39所示。 图38设置背景色 图39条件格式规则 【试题解答32】 1． 题目要求 在练习素材文件夹中，打开“试题解答32．xlsx”文件，按以下要求操作，完成后将文件保存。 (1) 将工作表Sheet1中的数据复制到Sheet2中，并将Sheet1更名为“工资表”。 (2) 在Sheet2的“叶业”所在行后增加一行： “邹萍萍，2600，700，750，150”。 (3) 在Sheet2的第F列第1个单元格中输入“应发工资”，F列其余单元格存放对应行“岗位工资”“薪级工资”“业绩津贴”“基础津贴”之和。 (4) 将Sheet2中的“姓名”和“应发工资”两列复制到Sheet3中。 (5) 在Sheet2中利用公式统计应发工资大于或等于4500元的人数，并把数据放入H2单元格。 (6) 在Sheet3工作表后添加工作表Sheet4，将Sheet2的A~F列复制到Sheet4。对Sheet4中的“应发工资”列设置条件格式，即凡是低于4000元的，一律显示为红色。 2． 操作步骤 (1) 将工作表Sheet1中的数据复制到Sheet2中，并将Sheet1更名为“工资表”。 单击Sheet1工作表，再单击“全选”按钮(或按Ctrl+A组合键)选中Sheet1中的全部内容，右击，在弹出的快捷菜单中选择“复制”选项(或按Ctrl+C组合键)，将光标移到Sheet2的A1单元格，右击，在弹出的快捷菜单中选择“粘贴”选项(或者按Ctrl+V组合键)。 单击Sheet1工作表，右击，在弹出的快捷菜单中选择“重命名”选项(或双击Sheet1)，将其修改成“工资表”。 (2) 在Sheet2的“叶业”所在行后增加一行。 选择Sheet2工作表，单击行号“6”以选中第6行，然后右击，在弹出的快捷菜单中选择“插入”选项，生成新行，然后依次在新行相应的单元格中输入“邹萍萍，2600，700，750，150”等值，如图310所示。 图310插入行 (3) 在Sheet2的第F列第1个单元格中输入“应发工资”，F列其余单元格存放对应行单元格数据之和。 将光标移到Sheet2表中，选中F1单元格，输入“应发工资”。 将光标移到Sheet2工作表的F4单元格中，单击编辑栏上的按钮，在弹出的“插入函数”对话框中选择SUM函数，单击“确定”按钮。 在“函数参数”对话框的Number1文本框中输入“B2:E2”，如图311所示，单击“确定”按钮。 图311设置SUM函数 单击单元格右下角填充柄，按住鼠标左键往下拖引用相同公式完成所有应发工资的计算。 (4) 将Sheet2中的“姓名”和“应发工资”两列复制到Sheet3中。 选择Sheet2表，将光标移动到A列同时按住Ctrl键再选择F列，右击，在弹出的快捷菜单中选择“复制”选项(或者按Ctrl+C组合键)。 选择Sheet3表，将光标定位到A1单元格，右击，在弹出的快捷菜单中选择“选择性粘贴”选项，在弹出的对话框中选择“数值”单选按钮，如图312所示，单击“确定”按钮完成复制。 图312选择性粘贴 (5) 在Sheet2中利用公式统计应发工资大于或等于4500元的人数。 选择Sheet2表，将光标定位到H2单元格，单击编辑栏上的按钮，在弹出的“插入函数”对话框中，在“或选择类别”下拉列表中选择“统计”选项，在“选择函数”列表框中选择COUNTIF函数，如图313所示，单击“确定”按钮。 在“函数参数”对话框的Range(区域)文本框中选择“F2:F102”区域，在Criteria(条件)文本框中输入“>=4500”，如图314所示，单击“确定”按钮。 (6) 在Sheet3工作表后添加工作表Sheet4，将Sheet2的A~F列复制到Sheet4。对Sheet4中的“应发工资”列设置条件格式。 图313插入COUNTIF函数 图314设置COUNTIF函数参数 单击窗口左下方工作表名称右侧“新工作表”按钮，插入新工作表，生成新的工作表Sheet4。 选择Sheet2，选中A~F列区域数据并复制到Sheet4表中。 选择“应发工资”所在列F2:E101数据区域，然后单击“开始”→“样式”功能组中的“条件格式”下拉按钮，选择“突出显示单元格规则”→“小于”选项。弹出“小于”对话框，在“为小于以下值的单元格设置格式”文本框中输入4000，在“设置为”下拉列表框中选择“红色文本”选项，单击“确定”按钮，如图315所示。 图315设置条件格式参数 【试题解答33】 1． 题目要求 在练习素材文件夹中，打开“试题解答33．xlsx”文件，按以下要求操作，完成后将文件保存。 (1) 将“库存表”中除“仪器名称”仅为“万用表”的行外，全部复制到Sheet2中。 (2) 将Sheet2中名称仅为“电流表”和“压力表”的“库存”分别改为20和30，并重新计算“库存总价”(库存总价=库存×单价)。 (3) 将“库存表”中“仪器名称”“单价”“库存”三列复制到Sheet3中，并将Sheet3设置套用表格格式为“红色，表样式浅色10”格式。 (4) 将Sheet2表“库存总价”列宽调整为10，设置“进货日期”的列宽为“自动调整列宽”，并按“库存总价”降序排列。 (5) 在Sheet2中利用公式统计库存量小于10的仪器种类数，并把数据放入H2单元格中。 (6) 在Sheet3工作表后添加工作表Sheet4，将Sheet2的A~F列复制到Sheet4。 (7) 对Sheet4进行高级筛选，筛选出单价大于或等于1000的或库存大于或等于60的数据行。 提示： 在原有区域显示筛选结果，高级筛选的条件可以写在H和I列的任意区域。 2． 操作步骤 (1) 将“库存表”中除“仪器名称”仅为“万用表”的行外，全部复制到Sheet2中。 选择“库存表”工作表，将光标移动到第1行，选中第1~3行，同时按住Ctrl键，再选中第5~102行，右击，在弹出的快捷菜单中选择“复制”选项。 将光标移到Sheet2的A1单元格，右击，在弹出的快捷菜单中选择“粘贴”选项。 (2) 将Sheet2中名称仅为“电流表”和“压力表”的“库存”分别改为20和30，并重新计算“库存总价”。 选择Sheet2工作表，将光标移到E2和E32单元格，分别将值改为20和30。 在F2单元格中输入“=D2*E2”，按Enter键。将光标移到F2单元格右下角的填充柄上，按住鼠标左键向下拖到F101单元格即可，如图316所示。 图316输入计算公式 (3) 将“库存表”中“仪器名称”“单价”“库存”三列复制到Sheet3中，并将Sheet3设置套用表格格式。 选择“库存表”中的B列，按住Ctrl键，同时选择D列和E列，右击，在弹出的快捷菜单中选择“复制”选项。 选择Sheet3工作表，将光标移到A1单元格，右击，在弹出的快捷菜单中选择“粘贴”选项。