您的当前位置:首页正文

第3章 Excel2010典型试题解析

2021-07-10 来源:易榕旅网


第3章 Excel2010典型试题解析

3.1 Excel一级典型试题解析 3.1.1 欧洲10个国家月失业人口统计表

1. 2. 3. 4. 5.

求出Sheet1表中每个月的合计数并填入相应单元格中; 将Sheet1复制到Sheet2中;

求出Sheet2表中每个国家的月平均失业人数(小数取2位)填入相应单元格中; 将Sheet1表的A3:A15和L3:L15区域的各单元格“水平居中”及“垂直居中”; 在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:在“函数参数”对话框的第一行输入“B16>5”,第二行输入“高”,第三行输入“低”(注意:在输入后会自动添加西文的双引号)如图 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. 2. 3. 4. 5.

求出Sheet1表中每项产品全年平均销售量,并填入“平均”行相应单元格中(小数取2位); 将Sheet1复制到Sheet2,求出Sheet2中每月总销售量并填入“总销售量”列相应单元格中; 将Sheet2表内容按总销售量降序排列(不包括平均数); 将Sheet1表套用表格格式为“表样式中等深浅3”(不包括文字“2012年全年销售量统计表”); 在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函数参数对话框。

,插入SUMIF函数,如图 3-43

图 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单元格,单击编辑栏的【插入函数】按钮如图 3-47所示,单击【确定】按钮打开函数参数对话框。

,插入函数COUNTIFS函数,

图 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单元格,单击编辑栏的【插入函数】按钮数”对话框,设置如图 3-57所示的参数,单击【确定】按钮。

,插入COUNTIF函数。在打开“函数参

图 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单元格,单击编辑栏的【插入函数】按钮数参数”对话框,设置参数如图 3-62所示,单击【确定】按钮。

,插入COUNTIF函数,在打开的“函

图 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单元格,单击编辑栏的【插入函数】对话框设置参数如图 3-69所示,单击【确定】按钮。

,插入COUNTIF函数,在打开饿“函数参数”

图 3-69 “周销售量在650以上(含650)的图书种类”参数设置

6.步骤1:鼠标单击屏幕下方【插入工作表】按钮,在Sheet3工作表后添加新工作表,命名为Sheet4。 步骤2:切换到Sheet2,选中A2:J2区域单元格,按住CTRL键的同时选中A126:J126区域单元格,单击鼠标右键,选择“复制”命令;切换到Sheet4,单击A1单元格,单击鼠标右键,选择“粘贴”命令。 7.步骤1:在Sheet4中,选中G列到J列,单击鼠标右键,选择“删除”命令。 步骤2:单击A1单元格,输入“图书”。

步骤3:单击A1:F2区域的任意一个单元格,切换到“插入”选项卡,单击“图表”选项组的【饼图】按钮,在弹出的下拉列表中选择“三维饼图”中的“分离型三维饼图”,如图 3-70所示,生成图表,图例项为“星期一、星期二、……、星期五”。(提示:此时同时打开了“‘图表工具’设计”选项卡、“‘图表工具’布局”选项卡、“‘图表工具’格式”选项卡。)

图 3-70插入“分离型三维饼图”

步骤4:单击图表标题,将其改为“图书合计”;在“‘图表工具’布局”选项卡中单击【数据标签】按钮,在弹出的下拉菜单中选择“其他数据标签选项”(如图 3-71所示),打开“设置数据标签格式”对话框。

图 3-71添加“数据标签”

步骤5:在“设置数据标签格式”对话框中:勾选“标签包括”的“值”和“百分比”复选框,如图 3-72所示。

图 3-72“设置数据标签格式”对话框

步骤6:单击【关闭】,生成如图 3-73所示图表,鼠标拖曳,调整图表位置到区域A6:G20。单击快速访问工具栏的【保存】按钮保存结果。

图 3-73完成的“图书合计”图表

3.1.10 档案表

1. 2. 3. 4. 5. 6. 7.

将Sheet1复制到Sheet2和Sheet3中,并将Sheet1更名为“档案表”; 将Sheet2第3至第7行、第10行以及B、C和D三列删除; 将Sheet3中的“工资”每人增加10%;

将Sheet3中“工资”列数据保留两位小数,并降序排列;

在Sheet3表中利用公式统计已婚职工人数,并把数据放入G2单元格;

在Sheet3工作表后添加工作表Sheet4,将“档案表”的A到E列复制到Sheet4。

对Sheet4数据进行筛选操作,要求只显示“已婚”的工资在3500到4000之间(含3500和4000)的信息行。

 操作步骤如下:

1. 参考3.1.5中的题1。 2. 参考3.1.4中的题2。

3.步骤1:切换到Sheet3,单击F2单元格,输入“=E2*1.1”,确定;双击F2单元格的填充柄,填充该列的数据。 步骤2:选中区域“F2:F101”,单击鼠标右键,选择“复制”命令;选中D2单元格,单击鼠标右键,选择“粘贴选项”的第2项“值”粘贴,如图 3-74所示,删除第F列过渡数据。

图 3-74 “值”粘贴

4.步骤1:在Sheet3中,选中区域“E2:E101”,参考3.1.1中的题3设置小数位数。 步骤2:参考3.1.6中的题4的“步骤3”进行排序。 5.在Sheet3中,单击G2单元格,单击编辑栏的【插入函数】按钮参数”对话框,设置如图 3-75所示参数。单击【确定】按钮。

,插入COUNTIF函数。在打开的“函数

图 3-75 COUNTIF函数参数设置

6. 参考3.1.4中的题6。

7.步骤1:在Sheet4中,单击任意一个有数据的单元格,切换到“数据”选项卡,单击“排序和筛选”选项组的【筛选】按钮(此时各字段均出现了筛选下拉按钮)。单击“婚否”列的筛选下拉按钮,在弹出的下拉框中,勾选“已婚”复选框,如图 3-76所示,单击【确定】按钮。

图 3-76筛选“已婚”数据

步骤2:单击“工资”列的筛选下拉按钮,选择“数字筛选”→“介于”,如图 3-77所示。

图 3-77“工资”列筛选

步骤3:在“自定义自动筛选方式”对话框,进行参数设置,如图 3-78所示,单击【确定】按钮。单击快速访问工具栏的【保存】按钮保存结果。

图 3-78 “工资”在3500到4000之间的参数设置

3.1.11玩具等货物单

1. 2. 3. 4. 5. 6. 7.

将Sheet1复制到Sheet2中,并将Sheet1更名为“进货单”; 将Sheet2中“名称”、“单价”和“货物量”三列复制到Sheet3中; 对Sheet3中的内容按“单价”升序排列;

将Sheet2中的“波波球”的“单价”改为38.5,并重新计算“货物总价”;

在Sheet2中,利用公式统计单价低于50元(不含50元)的货物种类数,并把数据存入I2单元格。 在Sheet3工作表后添加工作表Sheet4,将Sheet2的A到F列复制到Sheet4。 对Sheet4:设置B列宽度为28,所有行高为“自动调整行高”;对货物总价列设置条件格式:凡是小于10000

的,一律显示为红色;凡是大于等于100000的,一律填充黄色背景色。

 操作步骤如下:

1. 参考3.1.5中的题1。

2.切换到Sheet2,鼠标单击列号“B”选中B列,按住CTRL键,单击列号“D”,单击列号“E”,选中这三列,点右键“复制”,切换到Sheet3,在A1单元格点右键,选择“粘贴”。 3. 参考3.1.6中的题4的“步骤3”。

4.步骤1:参考3.1.8中的题2的“步骤1”使用“查找”命令,在“查找和替换”对话框输入“波波球”,单击“查找下一个”按钮,光标会停在找到数据的单元格上,如图 3-79所示,关闭。

图 3-79查找“波波球”所在的行

步骤2:单击D44单元格,输入38.5,单击F44单元格,输入“=D44*E44”,按回车确定。 5.在Sheet2中,单击I2单元格,单击编辑栏的【插入函数】按钮数”对话框,设置参数,如图 3-80所示,确定。

,插入COUNTIF函数。在打开的“函数参

图 3-80“统计单价低于50元(不含50元)的货物种类数”设置参数

6. 参考3.1.4中的题6。

7.步骤1:参考3.1.6中的题4的“步骤1”,设置“列宽”为“28”,确定。

步骤2:选中所有行,切换到“开始”选项卡,单击“单元格”选项组的【格式】按钮,在弹出的下拉菜单中选择“自动调整行高”。

步骤3:单击列号“F”,选中该列,参考3.1.1中的题7的“步骤1、2”选择“条件格式”“小于”,输入参数“10000”。在“设置为”的下拉框中选择“自定义格式”,打开“设置单元格格式”对话框,在其“字体”选项卡中,设置“颜色”为“红色”,参见图 3-20所示,确定所做设置。

步骤4:继续选中F列,切换到“开始”选项卡,单击“样式”选项组的【条件格式】按钮,在弹出的下拉菜单中选择“突出显示单元格规则”下“其他规则” 命令(如图 3-81所示),打开“新建格式规则”对话框。

图 3-81“条件格式”设置

步骤5:在“新建格式规则”对话框,设置参数如图 3-82所示。

图 3-82“大于等于100000的”规则设置

步骤6:单击【格式】按钮,打开“设置单元格格式”对话框。在“填充”选项卡中,设置“背景色”为“黄色”,如图 3-83所示,确定所有设置。单击快速访问工具栏的【保存】按钮保存结果。

图 3-83填充黄色背景色。

3.1.12服装等货物单

1. 将Sheet1复制到Sheet2和Sheet3中,并将Sheet1更名为“出货单”; 2. 将Sheet3表的第5至第7行以及“规格”列删除;

3. 将Sheet3中单价低于50(不含50)的商品单价上涨10%(小数位取两位),将上涨后的单价放入“调整价”

列, 根据“调整价”重新计算相应“货物总价”(小数位取两位); 4. 将Sheet3表中的数据按“货物量”降序排列; 5. 在Sheet2的G列后增加一列“货物量估算”,要求利用公式统计每项货物属于量多,还是量少。条件是:如

果货物量≥100,则显示“量多”,否则显示“量少”。

6. 在Sheet3工作表后添加工作表Sheet4、Sheet5,将“出货单”的A到G列分别复制到Sheet4和Sheet5。 7. 对Sheet4,删除“调整价”列,进行筛选操作,筛选出单价最高的30项。 8. 对Sheet5,进行筛选操作,筛选出名称中含有“垫”(不包括引号)字的商品。

 操作步骤如下:

1. 参考3.1.5中的题1。 2. 参考3.1.4中的题2。

3.步骤1:参考3.1.5中的题7“步骤1、4、5”进行筛选,参数设置如图 3-84所示,确定。

图 3-84筛选“单价低于50(不含50)”的数据

步骤2:单击E2单元格,输入“=D2*1.1”,确定;参考3.1.1中的题3设置E2单元格的小数位数;双击E2

单元格的填充柄,填充该列的数据。

步骤3:单击G2单元格,输入“=E2*F2”,确定;参考上一步设置G2单元格的小数位数;填充该列的数据。 4. 参考3.1.6中的题4的“步骤3” 对“货物量”降序排列。

5.切换到Sheet2,单击H1单元格,输入“货物量估算”,单击H2单元格,单击编辑栏的【插入函数】按钮

入IF,在打开的“函数参数”对话框,设置参数如图 3-85所示(也可以直接在H2单元格输入“=IF(F2>=100,\"量多\量少\")”),单击【确定】按钮。双击H2单元格的填充柄,填充该列的数据。

图 3-85 IF函数参数对话框的设置

6. 参考3.1.4中的题6。

7.步骤1:切换到Sheet4,在列号“E”上单击鼠标右键,选择“删除”命令。

步骤2:单击任意一个有数据的单元格,切换到“数据”选项卡,单击“排序和筛选”选项组的【筛选】按钮(此时各字段均出现了筛选下拉按钮)。单击“单价”列筛选下拉按钮,在弹出的下拉菜单中选择“数字筛选”下的“10个最大的值”(如图 3-86所示),打开“自动筛选前10个”对话框。

图 3-86自动筛选前10个

步骤3:在“自动筛选前10个”对话框中,设置参数如图 3-87所示,单击【确定】按钮。

图 3-87筛选最高的30项

8.步骤1:切换到Sheet5,单击任意一个有数据的单元格,切换到“数据”选项卡,单击“排序和筛选”选项组的【筛选】按钮(此时各字段均出现了筛选下拉按钮);单击“名称”列筛选下拉按钮,在弹出的下拉菜单中选择“文本筛选”下的“包含”(如图 3-88所示),打开“自定义自动筛选方式”对话框。

图 3-88选择“文本筛选”下的“包含”

步骤2:在“自定义自动筛选方式”对话框中,设置参数如图 3-89所示,单击【确定】按钮。单击快速访问工具栏的【保存】按钮保存结果。

图 3-89 筛选出名称中含有“垫”字的商品

3.1.13化学物质表

1. 将Sheet1复制到Sheet2和Sheet3中,并将Sheet1更名为“材料表”; 2. 将Sheet3中“物质编号”和“物质名称”分别改为“编号”和“名称”,为“比重”(D1单元格)添加批注,

文字是“15.6至21℃”并将所有比重等于1的行删除; 3. 在Sheet2表的A90单元格插入“平均值”,并求出D、E二列相应的平均值; 4. 在Sheet2表的第1行前插入标题行“常用液体、固体、气体比重-比热表”,并设置为“楷体,字号20,合

并A1至E1单元格,及水平对齐方式为居中”,并设置A列至E列列宽为12。 5. 在Sheet2中,利用公式统计液态物质种类,并把统计数据放入G1单元格。

6. 在Sheet3工作表后添加工作表Sheet4,将“材料表”复制到Sheet4。 7. 对Sheet4采用高级筛选,筛选出比重在1~1.5之间(含1和1.5),或比热大于等于4.0的数据行。

 操作步骤如下:

1.参考3.1.5中的题1。

2.步骤1:切换到Sheet3,双击A1单元格,删除“物质”二字;双击B1单元格,删除“物质”二字,确定。 步骤2:单击D1单元格,切换到“审阅”选项卡,单击“批注”选项组的【新建批注】按钮(如图 3-90所示),打开批注框。

图 3-90【新建批注】按钮

步骤3:在批注框中输入批注内容“15.6至21℃”,如图 3-91所示。

图 3-91输入批注内容

步骤4:参考3.1.5中的题7“步骤1、4、5”进行筛选,筛选出“比重等于1” 的三行数据,选中这三行数据,单击右键“删除行”;单击【筛选】按钮,取消筛选。 3.切换到Sheet2,单击A90单元格,输入“平均值”,单击D90单元格,输入“=AVERAGE(D2:D89)”,确定(也可以使用【Σ自动求和】按钮的下拉菜单选项中的“平均值(A)”来完成),拖曳D90单元格的填充柄至E90,完成自动填充。

4.步骤1:在Sheet2中,在行号“1”上单击鼠标右键,选择“插入”命令;单击A1单元格,输入“常用液体、固体、气体比重-比热表”。

步骤2:选中区域A1:E1,单击鼠标右键,在弹出的下拉菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框:在“字体”选项卡,设置“字体”为“楷体”,“字号”为“20”;在“对齐”选项卡,合并单元格,设置“水平对齐方式”为“居中”。

步骤3:选中A列至E列,参考3.1.6中的题4的“步骤1”,设置“列宽”为“12”。 5.在Sheet2中,单击G1单元格,单击编辑栏的【插入函数】按钮数”对话框,设置参数如图 3-92所示,单击【确定】按钮。

,插COUNTIF函数,在打开的“函数参

图 3-92统计“液”态物质种类的函数设定

6. 参考3.1.4中的题6。

7.步骤1:在Sheet4中,在空白区域创建筛选条件,如图 3-93所示。

图 3-93高级筛选条件

步骤2:单击 Sheet4 工作表中 A1:E89单元格区域中的任一单元格,切换到“数据”选项卡,单击“排序和筛选”选项组的【高级】按钮

,打开“高级筛选”对话框,“列表区域”文本框中已自动填入数据清

单所在的单元格区域,如图 3-94所示。

图 3-94设置“列表区域”

步骤3:将光标定位在“条件区域”文本框内,用鼠标拖选前面创建的筛选条件单元格区域,“条件区域”文本框内会自动填入该区域地址,如图 3-95所示,再单击【确定】按钮,完成高级筛选。单击快速访问工具栏的【保存】按钮保存结果。

图 3-95设置“条件区域”

3.1.14基础课成绩表

1. 在Sheet1表前插入工作表Sheet2和Sheet3,使三张工作表次序为Sheet2、Sheet3和Sheet1,并将Sheet1复

制到Sheet2中;

2. 在Sheet2第A列之前增加一列:“学号,0001,0002,0003,……,0100”(其中……为具体编号); 3. 在Sheet2第F列后增加一列“平均成绩”,在最后一行后增加一行“各科平均”(A102),并求出相应平均值

(不包括G列);

4. 将Sheet2复制到Sheet3中,并对Sheet2中的学生按“平均成绩”降序排列(“各科平均”行位置不变); 5. 在Sheet3的第G列后增加一列“通过否”,利用公式给出具体通过与否的数据:

如果平均成绩≥80,则给出文字 “通过”,否则给出文字“未通过”(不包括引号)。

6. 在Sheet1工作表后添加工作表Sheet4,将Sheet3中除“各科平均”行外的A到H列复制到Sheet4。 7. 在Sheet4中进行分类汇总,按通过否统计学生人数(显示在“学号”列),要求先显示通过的学生人数,再

显示未通过的学生人数,显示到第2级(即不显示具体的学生信息)。

 操作步骤如下:

1. 步骤1:鼠标单击屏幕下方【插入工作表】按钮,在Sheet1工作表后添加Sheet2;再次单击该按钮,在Sheet2工作表后添加Sheet3;选中Sheet1内所有内容,“复制”后,“粘贴”到Sheet2中。 步骤2:在工作簿底端,利用鼠标拖动调整三张工作表次序为Sheet2、Sheet3和Sheet1。

2. 步骤1:切换到Sheet2,在行号“A”上单击鼠标右键,选择“插入”命令;单击A1单元格,输入“学号”。 步骤2:鼠标右键单击A2单元格,在弹出的快捷菜单中选择“设置单元格格式”,打开“设置单元格格式”对话框,在其“数字”选项卡中设置“文本”,单击【确定】按钮;在A2单元格输入“0001”,拖动A2单元格的填充柄,向下填充。

(也可在A1单元格中输入“’00001” ,A2单元格中输入“’00002”,同时选中这个两个单元格,向下填充) 3.步骤1:在Sheet2中,单击G1单元格,输入“平均成绩”;单击G2单元格,输入“=AVERAGE(C2:F2)”,确定;双击G2单元格的填充柄,填充该列的数据。 步骤2:单击A102单元格,输入“各科平均”;单击C102单元格,输入“=AVERAGE(C2:C101)”,确定;拖曳C102单元格的填充柄至F102,完成自动填充。 4.步骤1:参考3.1.1中的题2,完成工作表的复制。

步骤2:切换到Sheet2,参考3.1.6中的题4的“步骤3”,按“平均成绩”降序排列。

5.切换到Sheet3,单击H1单元格,输入“通过否”,单击H2单元格,单击编辑栏的【插入函数】按钮

,插入

函数IF函数,在打开的“函数参数”对话框,设置参数如图 3-96所示(也可以直接在H2单元格输入“=IF(G2>=80,\"通过\未通过\")”),单击【确定】按钮。双击H2单元格的填充柄,填充该列的数据。

图 3-96“IF”函数参数设置

6.步骤1:参考3.1.4中的题6进行工作表的添加和复制。 步骤2:删除Sheet4中第102行(“各科平均”)的数据。

7.步骤1:在Sheet4中,鼠标单击“H”列任意一个有数据的单元格,切换到“开始”选项卡,单击“编辑”选项组的【排序和筛选】按钮,在弹出的下拉菜单中选择“升序”,使得“通过否”列按照“通过”在前,“未通过”在后排序;鼠标单击任意一个有数据的单元格,切换到“数据”选项卡,单击“分级显示”选项组的【分类汇总】

按钮 ,在“分类汇总”对话框设置参数,如图 3-97所示,单击【确定】按钮。

图 3-97“分类汇总”参数设置

步骤2:在屏幕左侧分级显示处单击“2”,如图 3-98所示,“分类汇总”后的结果如图 3-99所示。单击快速访问工具栏的【保存】按钮保存结果。

图 3-98显示到第2级

图 3-99“分类汇总”后的结果

因篇幅问题不能全部显示,请点此查看更多更全内容