第3章Excel2010典型试题解析要点.pdf

第3章 Excel2010 典型试题解析 3.1 Excel一级典型试题解析 3.1.1 欧洲 10 个国家月失业人口统计表 1.求出 Sheet1表中每个月的合计数并填入相应单元格中； 2.将 Sheet1复制到 Sheet2中； 3.求出 Sheet2表中每个国家的月平均失业人数（小数取2 位）填入相应单元格中； 4.将 Sheet1表的 A3：A15 和 L3： L15 区域的各单元格“水平居中”及“垂直居中”； 5.在 Sheet2表的月平均后增加一行“平均情况”（A17 单元格），该行各对应单元格内容为： 如果月平均失业人数5 万，则显示“高” ，否则显示“低” （不包括引号） 。要求利用公式。 6.在 Sheet2工作表后添加Sheet3，将 Sheet1 的第 3 行到第 15 行复制到Sheet3中 A1 开始的区域。 7.对 Sheet3的 B2：K13 区域，设置条件格式： 对于数值小于1 的单元格，使用红、绿、蓝颜色成分为100、255、100 的背景色填充；对于数值大于7 的， 使用红色加粗效果。 操作步骤如下： 1. 步骤 1：单击 L4 单元格，如图3-1 所示，切换到“开始”选项卡，单击“编辑”选项组的【自动求和】按 钮，如图3-2 所示， L4 单元格会自动生成求和公式，如图3-3 所示，检查求和区域正确后，按回车确认，得到 一月份的“合计”值。 图 3-1 欧洲 10 个国家月失业人口统计表 图 3-2 “编辑”工具栏 图 3-3 “自动求和”区域及公式 步骤 2：将鼠标移到L4 单元格右下角，鼠标指针变为实心十字形（填充柄），如图3-4 所示，拖曳至L15，完 成自动填充。 图 3-4 鼠标指针变为实心十字形（填充柄） 2. 选中 Sheet1内所有内容，单击鼠标右键，选择“复制”命令，切换到Sheet2，单击 A1 单元格，然后单击鼠标 右键，选择“粘贴”命令，如图3-5 所示。 图 3-5 粘贴选项 3. 步骤 1：单击 B16 单元格，切换到“开始”选项卡，单击“编辑”选项组的【自动求和】按钮的下拉箭头， 在弹出的下拉菜单中选择“平均值(A) ”, 如图3-6 所示， B16 单元格会有公式生成，如图3-7 所示，检查求平 均值区域正确后，按回车确认（也可以直接在B16 单元格输入“ =AVERAGE(B4:B15) ” ） ，拖曳 B16 单元格的填 充柄至 K16，完成自动填充。 图 3-6 “自动求和”的下拉箭头 图 3-7 生成求平均值公式 步骤 2： 选择区域B16:K16 ， 在该区域中单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式” （如图3-8 所示），打开“设置单元格格式”对话框：在“数字”选项卡中，选择“分类”为“数值”；设置“小数位数” 为“ 2” ，如图3-9 所示。 图 3-8 单元格右键菜单 图 3-9 设置小数位数 4. 切换到 Sheet1，选择区域A3:A15 ，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”，打开“设置 单元格格式”对话框：在“对齐”选项卡中，设置水平和垂直对齐方式，如图3-10 所示；选择L3:L15 区域重复 以上操作。 图 3-10 “对齐 ”选项卡 5. 步骤 1：切换到Sheet2，单击 A17 单元格，输入“平均情况”；再单击 B17 单元格，切换到“公式”选项卡， 单击“函数库”选项组的【插入函数】按钮（或者直接单击编辑栏的【插入函数】按钮，可以直接插入函数） ， 如图3-11 所示。 图 3-11 插入函数 步骤 2：在“插入函数”对话框中选择的“IF ”函数（一般可以在“常用函数”类别中找到），单击【确定】 按钮（如图3-12 所示），打开IF 函数参数对话框。 图 3-12 选择“ IF ”函数 步骤3：在“函数参数”对话框的第一行输入“B165”，第二行输入“高”，第三行输入“低”（注意：在 输入后会自动添加西文的双引号）如图3-13 所示。单击【确定】按钮。拖曳B17 单元格的填充柄至K17，完成 自动填充。 图 3-13 “IF ”函数参数设置 6. 鼠标单击屏幕下方【插入工作表】按钮，如图3-14 所示，在Sheet2工作表后添加Sheet3。切换到 Sheet1，选 中第 3 行到第 15 行，单击鼠标右键，选择“复制”命令；再切换到Sheet3，单击 A1 单元格，然后单击鼠标右 键，选择“粘贴”命令。 图 3-14 【插入工作表】按钮 7. 步骤 1：选中 Sheet3 工作表中的 B2 ：K13单元格区域，切换到“开始”选项卡，单击“样式”选项组的【条 件格式】按钮，选择“突出显示单元格规则”“小于”命令（如图 3-15 所示）。 图 3-15 【条件格式】按钮 步骤 2：在“小于”对话框中的文本框中，输入“1”；在“设置为”的下拉框选择“自定义格式”（如图3-16 所示），打开“设置单元格格式”对话框，在该对话框的“填充”选项卡中，单击【其他颜色】按钮（如图 3-17 所示），打开“颜色”对话框。 图 3-16 “条件格式”“小于”对话框 图 3-17 “自定义格式”对话框 步骤3：在“颜色”对话框中的“自定义”选项卡下，选择“颜色模式”为“RGB ”；红、绿、蓝颜色分别设 置为 100、255、100，如图 3-18 所示。连续单击【确定】按钮。 图 3-18 背景色设置 步骤 4：继续选中B2：K13单元格区域，切换到“开始”选项卡，单击“样式”选项组的【条件格式】按钮， 选择“突出显示单元格规则”“大于”命令，打开“大于”对话框，在文本框中输入“7”，“设置为”选择 “自定义格式”（如图3-19 所示），打开“设置单元格格式”对话框。 图 3-19 “条件格式”“大于”对话框 步骤 5：在“设置单元格格式”对话框的“字体”选项卡中，设置“颜色”为“红色”，“字形”为“加粗”， 如图 3-20所示，确定所做设置。单击快速访问工具栏的【保存】按钮保存结果。 图 3-20 字体设置 3.1.2 全年销售量统计表 1.求出 Sheet1表中每项产品全年平均销售量，并填入“平均”行相应单元格中（小数取2 位） ； 2.将 Sheet1复制到 Sheet2，求出 Sheet2中每月总销售量并填入“总销售量”列相应单元格中； 3.将 Sheet2表内容按总销售量降序排列(不包括平均数 )； 4.将 Sheet1表套用表格格式为“表样式中等深浅3” （不包括文字“2012 年全年销售量统计表” ） ； 5.在 Sheet2工作表总销量右侧增加一列，在Q3 填入“超过85 的产品数”，并统计各月销量超过85（含 85） 的产品品种数填入Q 列相应单元格。 6.在 Sheet2工作表后添加工作表Sheet3，将 Sheet2的 A3：A15 及 P3：P15 的单元格内容复制到Sheet3。 7.对 Sheet3工作表，对月份采用自定义序列“一月”、 “二月”、次序排序。 8.对 Sheet3中数据，产生二维簇状柱形图。其中“一月”、 “二月”等为水平（分类）轴标签。“总销售量”为 图例项，并要求添加对数趋势线。图表位置置于D1：K14 区域。 操作步骤如下： 1. 参考 3.1.1 中的题 3。 2. 步骤 1：参考 3.1.1 中的题 2。 步骤 2：参考 3.1.1 中的题 1。 3. 步骤 1：选择 Sheet2 区域 A3:P15，切换到“数据”选项卡，单击“排序和筛选”选项组的【排序】按钮（如 图 3-21 所示），打开“排序”对话框。 图 3-21 【排序】按钮 步骤 2：在“排序”对话框中：设置“主要关键字”为“总销售量”， “排序依据”为“数值”， “次序”为“降 序” ，如图3-22 所示，单击【确定】按钮。 图 3-22 排序对话框 4. 切换到Sheet1，选中A2： P16 区域，切换到“开始”选项卡，单击“样式”选项组的【套用表格格式】下拉 箭头，在弹出的下拉列表框中选择“表样式中等深浅3” ，如图3-23 所示；在显示的“套用表格式”对话框中， 检查“表数据的来源”区域是否正确，单击【确定】按钮，如图3-24 所示。 图 3-23 “套用表格格式”命令 图 3-24 “套用表格式”对话框 5. 步骤 1：切换到Sheet2，选中Q3：Q16 区域，切换到“开始”选项卡，单击“字体”选项组的【边框】下拉 箭头，在弹出的下拉菜单中选择“所有框线”，如图3-25 所示。 图 3-25 给单元格添加边框 步骤 2：在 Q3 单元格输入 “超过 85 的产品数”；单击 Q4 单元格， 单击编辑栏的 【插入函数】，插入 “统计” 函数中的COUNTIF 函数，并设置参数如图3-26 所示。单击【确定】按钮。拖曳Q4 单元格的填充柄至Q15， 完成自动填充。 图 3-26 COUNTIF函数参数设置 6. 步骤 1：鼠标单击屏幕下方【插入工作表】按钮，会在 Sheet2工作表后添加Sheet3。 步骤 2：切换到 Sheet2，选中 A3：A15，按下 Ctrl 同时选中 P3：P15，在选中区域单击鼠标右键，选择“复制” 命令；切换到Sheet3，单击 A1 单元格，然后单击鼠标右键，选择“粘贴”命令。 7. 步骤 1：选择 Sheet3 区域 A1:B13 ，切换到“数据”选项卡，单击“排序和筛选”选项组的【排序】按钮，在 “排序”对话框：设置“主要关键字”为“月份”， “排序依据”为“数值” ， “次序”为“自定义序列” ，如图3-27 所示。 图 3-27 “排序”对话框 步骤 2：在“自定义序列”中选择“一月，二月，三月”序列，如图3-28 所示，单击【确定】按钮返回到 “排序”对话框，在单击【确定】完成设置。 图 3-28 “自定义序列”对话框 8. 步骤 1：在 Sheet3中，单击A1:B13 区域的任意一个单元格，切换到“插入”选项卡，单击“图表”选项组的 【柱形图】 下拉箭头， 在弹出的下拉列表中选择“二维柱形图” 中的“簇状柱形图” ，如图3-29 所示， 生成图表。 （提示：此时同时打开了“图表工具设计”选项卡、“图表工具布局”选项卡、“图表工具格式” 选项卡。） 图 3-29 “图表”工具栏 步骤 2：单击“图表工具布局”选项卡的【趋势线】下拉箭头（如图3-30 所示），在弹出的下拉列表中 选择“其他趋势线选项”（如图3-31 所示），打开“设置趋势线格式”对话框。 图 3-30 “图表工具”栏 图 3-31 “趋势线”选项 步骤 3：在“设置趋势线格式”对话框中：设置“趋势线选项”为“对数”，如图3-32 所示。 图 3-32 “设置趋势线格式”对话框 步骤 4：单击【关闭】按钮，生成如图3-33 所示图表；选中图表后，拖曳鼠标将图表位置调整到区域D1：K14。 单击快速访问工具栏的【保存】按钮保存结果。 图 3-33 图表“总销售量”完成图 3.1.3 出勤表 1.求出 Sheet1 表中每班本周平均每天缺勤人数（小数取1 位）并填入相应单元格中；（本周平均缺勤人数=本 周缺勤总数 /5） 2.求出 Sheet1表中每天实际出勤人数并填入相应单元格中； 3.在 Sheet1表后插入 Sheet2，将 Sheet1表复制到Sheet2，并重命名Sheet2 为“考勤表”； 4.在 Sheet1表的第 1 行前插入标题行“年级周考勤表”，设置为“隶书，字号22，加粗、合并A1 至 M1 单元 格及水平居中” 。 5.Sheet1 的最后增加一行，班级列中文字为“缺勤班数”，利用公式在该行每天的“本日缺勤人数”中统计有 缺勤的班级数（例如C104 的统计值为71）。 6.在 “考勤表”工作表后添加工作表Sheet2，将 Sheet1 的第 2 行（ “星期一”等所在行）和第104 行（ “缺勤 班数”所在行）复制到Sheet2。 7.对 Sheet2：删除 B、 D、 F、H、J、L、M 列，为周一到周五的缺勤班级数制作三维簇状柱形图，要求： （1）以星期一、星期二等为水平（分类）轴标签； （2）以“缺勤班数”为图例项； （3）图表标题使用“缺勤班数统计”（不包括引号） ； （4）删除网格线； （5）设置坐标轴选项使其最小值为0.0； （6）将图表放置于A4 ：F15 的区域。 操作步骤如下： 1. 步骤 1：在 Sheet1中，单击M3 单元格，输入“=(C3+E3+G3+I3+K3)/5 ” ，按回车确定。 步骤 2：参考 3.1.1 中的题 3 的“ 步骤 2” ，设置 M3 的小数位数。 步骤 3：双击 M3 单元格的填充柄，填充该列的数据。 2. 在 Sheet1中，考虑到：“实际出勤人数”=“应出勤人数” -“本日缺勤人数” ，单击 D3 单元格， 输入“=B3-C3 ” ， 确定，双击D3 单元格的填充柄填充该列的数据；采用相同方法，单击F3 单元格，输入“=B3-E3” ，确定，自动 填充 F4：F102；单击 H3 单元格， 输入“=B3-G3 ” ，确定， 自动填充H4：H102；单击 J3 单元格， 输入“=B3-I3 ” ， 确定，自动填充J4：J102；单击 L3 单元格，输入“=B3-K3 ” ，确定，自动填充L4：L102。 3. 鼠标单击屏幕下方 【插入工作表】 按钮， 在 Sheet1工作表后添加Sheet2， 复制 Sheet1内所有内容， 到 Sheet2， 鼠标右键单击Sheet2 标签，在弹出的快捷菜单中选择“重命名”，输入“考勤表” ，按回车确定。 4. 步骤 1：在 Sheet1中，在行号1 处，单击鼠标右键，选择“插入”命令，如图3-34 所示，完成插入一行。 图 3-34 插入行 步骤 2：单击 A1 单元格，输入“年级周考勤表”。选中区域A1:M1 ，单击鼠标右键，选择“设置单元格格式”， 打开“设置单元格格式”对话框，在“字体”选项卡中设置字体为“隶书”，字号为“ 22” ，字形为“加粗” ，如 图 3-35 所示。 图 3-35 “字体”选项卡 步骤 3：选择“对齐”选项卡，合并单元格，设置水平对齐为“居中”，如图3-36 所示，单击【确定】按钮。 图 3-36 “对齐”选项卡 5. 步骤 1：在 Sheet1 的最后一行后面，选中A103：M103 区域，切换到“开始”选项卡，单击“字体”选项组 的【边框】按钮的下拉选项，选择“所有框线”。 步骤 2：在 A103 单元格，输入“缺勤班数”，单击C104 单元格，单击编辑栏的【插入函数】按钮，插入 COUNTIF 函数，设置“函数参数”对话框参数如图3-37 所示。单击【确定】按钮。复制C103 单元格，粘贴到 E103 单元格、 G103 单元格、 I103 单元格、 K103 单元格。 图 3-37 COUNTIF函数“统计有缺勤的班级数”参数设定 6. 步骤 1：鼠标单击屏幕下方【插入工作表】按钮，在“考勤表”工作表后添加工作表，命名为Sheet2。 步骤 2：选中工作表Sheet1的第 2 行，按“ CTRL ”键的同时选中第104 行，单击鼠标右键，选择“复制”命 令；切换到Sheet2，单击 A1 单元格，然后单击鼠标右键，选择“粘贴”命令。 7. 步骤 1：在工作表Sheet2 中，同时选中B、D、F、H、J、L、M 列，单击鼠标右键，选择“删除”命令，结果 如图3-38 所示。 图 3-38 Sheet2工作表的数据 步骤 2：鼠标单击任一有数据的单元格，切换到“插入”选项卡，单击“图表”选项组的【柱形图】按钮，在 弹出的下拉列表中选择“三维柱形图”中的“簇状柱形图”。 步骤 3：在生成的图表中，检查水平（分类）轴标签为“星期一、星期二等”， “缺勤班数”为图例项；单击图 表标题，将其改为“缺勤班数统计”；在网格线上单击鼠标右键，选择“删除”，如图3-39 所示。 图 3-39 删除网格线 步骤 4：鼠标双击纵坐标数值，弹出“设置坐标轴格式”对话框，设置“最小值”为“固定”，文本框中输入 “0.0 ”，如图3-40 所示。 图 3-40 “设置坐标轴格式”对话框 步骤 5：单击【关闭】按钮。选中图片后拖曳鼠标，将图表位置调整到区域A4：F15，完成效果如图3-41 所 示。单击快速访问工具栏的【保存】按钮保存结果。 图 3-41 完成的“缺勤班数统计”图表 3.1.4 电线库存表 1.在 Sheet1表后插入工作表Sheet2和 Sheet3，并将 Sheet1 复制到 Sheet2 和 Sheet3 中； 2.将 Sheet2第 2、4、6、8、 10 行以及 A 列和 C 列删除； 3.在 Sheet3 第 E 列的第一个单元格输入“总价”，并求出对应行相应总价，保留两位小数；（总价 =库存量 *单 价） 4.对 Sheet3表设置套用表格格式为“表样式浅色15”格式，各单元格内容水平对齐方式为“居中”，各列数据 以“自动调整列宽”方式显示，各行数据以“自动调整行高”方式显示。 5.在 Sheet3中利用公式统计单价25 的货物的总价和，并放入G2 单元格。 6.在 Sheet3工作表后添加工作表Sheet4，将 Sheet1的 A 到 D 列复制到Sheet4。 7.在 Sheet4中，以库存量为第一关键字（降序）、单价为第二关键字（升序）对数据进行排序。 操作步骤如下： 1.参考 3.1.3 中的题 3。 2. 切换到 Sheet2，同时选中第2、4、6、8、10 行，单击鼠标右键，选择“删除”命令；再同时选中第A 列和 C 列，单击鼠标右键，选择“删除”命令。 3. 步骤 1：切换到Sheet3，在 E1 单元格输入“总价” ，单击 E2 单元格，输入公式“=C2*D2 ” ，确定。 步骤 2：参考 3.1.1 中的题 3 的“ 步骤 2” ，双击 E2 单元格的填充柄，填充该列的数据。 4. 步骤 1：参考 3.1.2 中的题 4。 步骤2：选中所有单元格，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”，打开“设置单元格 格式”对话框。在其“对齐”选项卡中设置“水平对齐”为“居中”；切换到“开始”选项卡，单击“单元格” 选项组的【格式】按钮，在弹出的下拉菜单中，先选择“自动调整列宽”，再选择“自动调整行高”，如图3-42 所示。 图 3-42 “格式”命令选项 5. 步骤 1：在 Sheet3 中，单击G2 单元格，单击编辑栏的【插入函数】按钮，插入SUMIF 函数，如图3-43 所示，单击【确定按钮，打开SUMIF 函数参数对话框。 图 3-43 插入 SUMIF 函数 步骤 2：设置“函数参数”对话框的参数如图3-44 所示，单击【确定】按钮。 图 3-44 SUMIF 函数参数设置 6. 鼠标单击屏幕下方【插入工作表】按钮，在 Sheet3 工作表后添加Sheet4，切换到 Sheet1，选中 A 到 D 列， 复制；切换到Sheet4，在 A1 单元格点右键，选择“粘贴”。 7. 在 Sheet4中，单击有数据的任意一个单元格，切换到“数据”选项卡，单击“排序和筛选”选项组的【排序】 按钮，打开“排序”对话框：设置“主要关键字”为“库存量”， “排序依据”为“数值”， “次序”为“降序” ； 继续单击“添加条件”按钮，设置“次要关键字”为“单价”， “排序依据”为“数值”， “次序”为“升序” ，如 图 3-45 所示。单击【确定】完成排序。单击快速访问工具栏的【保存】按钮保存结果。 图 3-45 “排序”对话框参数设置 3.1.5 车间工资表 1.将 Sheet1表中内容复制到Sheet2表，并将Sheet2更名为“工资表” ； 2.求出“工资表”中“应发工资”和“实发工资”数据并填入相应单元格中 （应发工资 =基本工资 +岗位津贴 +工龄津贴 +奖励工资） （实发工资 =应发工资 -应扣工资）； 3.求出“工资表”中除“编号”和“姓名”外其他栏目的平均数（小数取2 位） ，并填入相应单元格中； 4.将“工资表”中每个职工内容按应发工资升序排列（不包括平均数所在行），并将应发工资最低的职工的所 有内容的字体颜色用蓝色表示； 5.在“工资表”中利用公式统计4000实发工资4100 的人数，并放入K2 单元格，并设置K2 单元格格式为 “常规”。 6.在“工资表”后添加工作表Sheet2，将“工资表”中A列到 I 列的内容复制到Sheet2。 7.对 Sheet2工作表启用筛选，筛选出姓“李”或姓“陈”的、且基本工资大于等于3100 的数据行。其中筛选 出姓“李”或姓“陈” ，要求采用自定义筛选方式。 操作步骤如下： 1. 复制 Sheet1内所有内容到Sheet2；右键单击Sheet2标签，选择“重命名” ，输入“工资表” ，按回车确定。 2. 切换到“工资表” ，单击 G3 单元格，输入公式“=C3+D3+E3+F3 ” ，按回车确定，拖曳G3 单元格的填充柄至 G102，完成自动填充；单击I3 单元格，输入公式“=G3-H3 ” ，按回车确定，拖曳I3 单元格的填充柄至I102，完 成自动填充。 3.参考 3.1.1 中的题 3。 4. 步骤 1：在“工资表”中，选中A2：I102 区域，切换到“数据”选项卡，单击“排序和筛选”选项组的【排 序】按钮，打开“排序”对话框：设置“主要关键字”为“应发工资”， “排序依据”为“数值” ， “次序”为“升 序” ，确定。 步骤 2：选择 A3 ：I3 区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”，打开“设置单元格 格式”对话框：在“字体”选项卡中，设置“颜色”为“蓝色”，如图3-46 所示 , 单击【确定】按钮。 图 3-46 设置字体颜色 5. 步骤 1：在“工资表”中，单击K2 单元格，单击编辑栏的【插入函数】按钮，插入函数COUNTIFS 函数， 如图3-47 所示 , 单击【确定】按钮打开函数参数对话框。 图 3-47 插入 COUNTIFS函数 步骤 2：在“函数参数”对话框中设置如图3-48 所示的参数，单击【确定】按钮；在K2 单元格上单击鼠标右 键，在弹出的快捷菜单中选择“设置单元格格式”，打开“设置单元格格式”对话框，在“数字”选项卡中设置 分类为“常规” ，单击【确定】按钮完成设置。 图 3-48 COUNTIFS函数“统计4000 实发工资4100 的人数”参数设置 6.参考 3.1.4 中的题 6。 7. 步骤 1：在 Sheet2中，单击任意一个有数据的单元格，切换到“数据”选项卡，单击“排序和筛选”选项组的 【筛选】按钮，如图3-49 所示。（此时各字段均出现了筛选下拉按钮。） 图 3-49 筛选命令 步骤 2：单击“姓名”列的筛选下拉按钮，在弹出的菜单中选择“文本筛选”下的“自定义筛选”（如图3-50 所示），打开“自定义自动筛选”对话框。 图 3-50 对姓名选择“自定义筛选” 步骤 3：在“自定义自动筛选方式”对话框，设置参数如图3-51 所示。单击【确定】按钮。 图 3-51 “自定义筛选”参数设置 步骤 4：单击“基本工资”列的筛选下拉按钮，在弹出的菜单中选择“数字筛选”下的“大于或等于”（如图 3-52 所示），打开“自定义自动筛选”对话框。 图 3-52 “基本工资”列筛选 步骤 5：在“自定义自动筛选方式”对话框设置参数如图3-53 所示。单击【确定】按钮。单击快速访问工具 栏的【保存】按钮保存结果。 图 3-53 “基本工资”筛选参数设置 3.1.6 仪器库存表 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 列的任意区域） 操作步骤如下： 1. 将“库存表”中所有数据复制到Sheet2中；在 Sheet2中鼠标点在行号“4”上（ “万用表”所在行） ，右键单击， 在弹出的快捷菜单中选择“删除”命令。 2. 在 Sheet2中，单击单元格E2，输入 20，单击单元格F2，输入公式“ =D2*E2 ” ，确定；单击单元格E32，输入 30，单击单元格F32，输入公式“=D32*E32 ” ，确定。 3. 步骤 1：在库存表中，单击列号“B”，按住Ctrl 键，再单击列号“D”和“ E”，选中这三列，复制到Sheet3 中。 步骤 2：参考 3.1.2 中的题 4。 4. 步骤 1：切换到Sheet2，单击列号“F”，选中该列，切换到“开始”选项卡，单击“单元格 “选项组的【格式】 按钮，选择“列宽” （如图3-54 所示）；在打开的“列宽”对话框中输入“10”，如图3-55 所示，单击【确定】 按钮。 图 3-54 设置列宽 图 3-55 设置列宽的值 步骤2：单击列号“ C”选中该列，切换到“开始”选项卡，单击“单元格”选项组的【格式】下拉箭头，在 弹出的下拉菜单中选择“自动调整列宽”，确定。 步骤3：鼠标单击“ F”列（要排序的列）任意一个有数据的单元格，切换到“开始”选项卡，单击“编辑” 选项组的 【排序和筛选】 下拉箭头， 在弹出的下拉菜单中选择“降序”，如图3-56 所示（也可以用 【排序】 完成） 。 图 3-56 快捷排序 5. 在 Sheet2 中，单击H2 单元格，单击编辑栏的【插入函数】按钮，插入 COUNTIF 函数。在打开“函数参 数”对话框，设置如图3-57 所示的参数，单击【确定】按钮。 图 3-57 COUNTIF函数参数设置 6.参考 3.1.4 中的题 6。 7. 步骤 1： 在 Sheet4 的 H和 I 列空白区域创建筛选条件，在H2：I4 单元格区域中输入筛选条件，如图3-58 所 示。 图 3-58 创建筛选条件 步骤 2：单击 Sheet4 工作表中 A1：F101 单元格区域中的任一单元格，切换到“数据”选项卡，单击“排序 和筛选”选项组的【高级】按钮（如图3-59 所示），打开“高级筛选”对话框。 图 3-59 选择“高级筛选”命令 步骤 3：在“高级筛选” 对话框中，“列表区域” 的文本框中已自动填入数据清单所在的单元格区域，如图3-60 所示。 图 3-60 设置“列表区域” 步骤 4：将光标定位在“条件区域”文本框内，用鼠标拖选前面创建的筛选条件单元格区域H2：I4 ，“条件区 域”文本框内会自动填入该区域的地址，如图3-61 所示，再单击【确定】按钮，完成高级筛选。单击快速访问 工具栏的【保存】按钮保存结果。 图 3-61 设置“条件区域” 3.1.7 岗位工资表 1.将工作表“ Sheet1”复制到Sheet2中，并将 Sheet1 更名为“工资表” ； 2.在 Sheet2的“叶业”所在行后增加一行：“邹萍萍， 2600，700，750， 150” ； 3.在 Sheet2的第 F 列第 1 个单元格中输入 “应发工资” ，F 列其余单元格存放对应行“岗位工资” 、 “薪级工资”、 “业绩津贴”和“基础津贴”之和； 4.将 Sheet2中“姓名”和“应发工资”两列复制到Sheet3中，并将Sheet3 设置自动套用格式为“表样式中等 深浅 22”格式； 5.在 Sheet2中利用公式统计应发工资4500 的人数，并把数据放入H2 单元格。 6.在 Sheet3工作表后添加工作表Sheet4，将 Sheet2 的 A 到 F 列复制到Sheet4。对 Sheet4 中的应发工资列设置 条件格式，凡是低于4000 的，一律显示为红色。 操作步骤如下： 1.参考 3.1.5 中的题 1。 2. 切换到 Sheet2，在“叶业” 所在行的下一行行号“6”上单击鼠标右键，选择“插入” 命令，依次在区域A6:E6 各单元格输入“邹萍萍，2600，700，750，150” 。 3. 在 Sheet2中，单击F1 单元格，输入“应发工资”，单击 F2 单元格，输入“=B2+C2+D2+E2 ” ，确定，双击F2 单元格的填充柄，填充该列的数据。 4. 步骤 1：在 Sheet2中，选中A 列和 F 列， “复制”， “粘贴”到Sheet3。 步骤 2：参考 3.1.2 中的题 4。 5. 切换到 Sheet2中，单击 H2 单元格，单击编辑栏的【插入函数】按钮，插入 COUNTIF 函数，在打开的“函 数参数”对话框，设置参数如图3-62 所示，单击【确定】按钮。 图 3-62 “统计应发工资4500 的人数”参数设置 6. 步骤 1：参考 3.1.4 中的题 6。 步骤 2：在 Sheet4中，选中F 列，切换到“开始”选项卡，单击“样式”选项组的【条件格式】下拉箭头，在 弹出的选择“突出显示单元格规则”下的“小于” 命令，打开“小于”对话框； 在该对话框的文本框输入“4000” ， 并选择“设置为”的下拉框为“自定义格式”，打开“设置单元格格式”对话框，在其“字体”选项卡中，设置 “颜色”为“红色”（参照图3-15、图 3-16、图 3-20），确定所做的设置。单击快速访问工具栏的【保存】 按钮保存结果。 3.1.8 专业课成绩表 1.在 Sheet1表后插入工作表Sheet2和 Sheet3，并将 Sheet1 复制到 Sheet2 中； 2.在 Sheet2中，将学号为“ 131973”的学生的“微机接口”成绩改为75 分，并在 G 列右边增加1 列“平均成 绩” ，求出相应平均值，保留且显示两位小数； 3.将 Sheet2中“微机接口”成绩低于60 分的学生复制到Sheet3表中（连标题行） ； 4.对 Sheet3中内容按“平均成绩”降序排列； 5.在 Sheet2中利用公式统计“电子技术”60 至 69 分（含 60 和 69）的人数，将数据放入J2 单元格。 6.在 Sheet3工作表后添加工作表Sheet4，将 Sheet2的 A 到 H 列复制到Sheet4。 7.对 Sheet4：I1 中输入“名次”（不包括引号） ，下面的各单元格利用公式按平均成绩，从高到低填入对应的名 次（说明：当平均成绩相同时，名次相同，取最佳名次）。 操作步骤如下： 1. 参考 3.1.3 中的题 3。 2. 步骤 1：在 Sheet2中，单击 A1 单元格，切换到“开始”选项卡，单击“编辑“选项组的【查找和选择】按钮， 在弹出的下拉菜单中选择“查找”（如图3-63 所示），打开“查找和替换”对话框。 图 3-63 “查找”命令 步骤2：在“查找和替换”对话框输入“131973” ，单击【查找下一个】按钮，光标会停在找到数据的单元格 上，如图3-64 所示。 图 3-64 “查找和替换”对话框 步骤 3：关闭该对话框。单击G81单元格，输入“75”；单击 H1单元格，输入“平均成绩”，单击 H2单元格， 输入“ =AVERAGE(C2:G2) ” ，确定（也可以选择【自动求和】按钮下拉选项中的“平均值(A) ”来完成）。 步骤 4：在 H2单元格上单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式” ，打开“设置单元格格式” 对话框，在其“数字”选项卡中，选择“数值”，将“小数位数”设为“2” ，单击【确定】按钮。双击H2单元格 的填充柄，填充该列的数据。 3. 步骤 1：参考 3.1.5中的题 7 步骤 1、4、5 进行参数设置，如图3-65 所示，确定。 步骤 2：将筛选出来数据（包括标题行）全部选中，复制到Sheet3 中。 图 3-65 筛选“微机接口”成绩低于60 分的数据 4.参考 3.1.6 中的题 4 的“ 步骤 3” 。 5. 步骤 1：切换到 Sheet2，首先单击任意一个单元格，切换到 “数据” 选项卡， 单击“排序和筛选”选项组的 【筛 选】按钮，取消筛选。 步骤 2：单击 J2 单元格，单击编辑栏的【插入函数】按钮，插入 COUNTIFS 函数，设置其“函数参数”对 话框的参数如图3-66 所示，单击【确定】按钮。 图 3-66 COUNTIFS函数参数设置 6.参考 3.1.4 中的题 6。 7. 步骤 1：在 Sheet4中，单击I1 单元格，输入“名次” ；单击 I2 单元格，单击编辑栏的【插入函数】按钮， 插入 RANK EQ 函数，如图3-67 所示。 图 3-67 插入 RANK.EQ函数 步骤 2：单击【确定】按钮，打开“函数参数”对话框，设置其参数如图3-68 所示（注意“ Ref”参数处的单 元格必须是绝对引用），单击【确定】按钮。双击I2 单元格的填充柄，填充该列的数据。单击快速访问工具栏 的【保存】按钮保存结果。 图 3-68 RANK.EQ函数参数设置 3.1.9 计算机书籍销售周报表 1.将 Sheet1复制到 Sheet2中，并将Sheet1 更名为“销售报表” ； 2.在 Sheet2第 6 行后增加一行： “计算机病毒，50，80， 40，20，45” ； 3.在 Sheet2的 G2 单元格输入“小计” ，A126 单元格输入“合计” ，求出第G 列和第 126 行有关统计值（G126 单元格不计算） ； 4.将 Sheet2复制到 Sheet3，在 Sheet3中对各种书按小计值降序排列（“合计”行位置不变） ； 5.在 Sheet2中利用公式统计周销售量在650 以上（含650）的图书种类，并把数据放入J2 单元格。 6.在 Sheet3工作表后添加工作表Sheet4，将 Sheet2中第 2 行和“合计”行复制到Sheet4。 7.对 Sheet4，删除“小计”列及右边各列，在A1 单元格中输入“图书” （不包括引号） 。对星期一到星期五 的数据，生成“分离型三维饼图”，要求： （1）图例项为“星期一、星期二、星期五”（图例项位置默认） ； （2）图表标题改为“图书合计”，并添加数据标签； （3）数据标签格式为值和百分比（如：1234,15%） ； （4）将图表置于A6： G20 的区域。 操作步骤如下： 1.参考 3.1.5 中的题 1。 2. 切换到 Sheet2，在第 6 行的下一行行号“7”上单击鼠标右键，选择“插入”命令，依次在区域A7:E7 各单元 格输入“计算机病毒，50，80，40， 20，45” 。 3. 步骤 1：在 Sheet2 中，单击 G2 单元格，输入“小计” ；单击 G3 单元格，输入“=SUM(B3:F3) ” ，按回车确定 （也可以使用【自动求和】按钮完成），双击 G3 单元格的填充柄，填充该列的数据。 步骤 2：单击 A126 单元格，输入“合计” ；单击 B126 单元格，输入“= SUM (B3:B125) ” ，按回车确定，拖曳 B126 单元格的填充柄至F126，完成自动填充。 4. 步骤 1：参考 3.1.1 中的题 2，完成复制表。 步骤 2：参考 3.1.6中的题 4 中的“步骤3”，完成排序。 5. 切换到 Sheet2，单击 J2 单元格，单击编辑栏的【插入函数】，插入 COUNTIF 函数，在打开饿“函数参数” 对话框设置参数如图3-69 所示，单击【确定】按钮。 图 3-69 “周销售量在650 以上（含650 ）的图书种类”参数设置 6. 步骤 1：鼠标单击屏幕下方【插入工作表】按钮，