上机指导67.doc

第六单元 Excel基本操作 83 第六单元 Excel基本操作6.1 第1题一、操作要求：1. 打开样文6-1.xls。2. 将标题设为楷体、20号字、合并居中；表头设为楷体、红色、粗体、黄色底纹；“工资”一列数据应用货币格式、居右，表头和其余各列居中；“部门”一列中所有的“市场部”添加灰-25%底纹；表格内外框线设为单线，表头下框线设为双线。3.将Sheet1工作表重命名为职员登记表，并将其复制到Sheet2中，在Sheet2中将职员登记表“部门”一列移动到“员工编号”一列之前；在“部门”一列之前插入一个空列，并添入“序号”。 二、操作提示：1. 选中标题所在的一行A1到G1单元格，设置字体为楷体，字号20。右击设置单元格格式，如图6-1。在“单元格格式”窗口中，选择“对齐”选项卡，水平对齐方式设为“居中”，选中“合并单元格”一项，如图6-2所示。 图6-1 图6-22. 选中表头一行A2到G2单元格，设置单元格格式，在“字体”选项卡中设置楷体、红色、加粗；在“图案”选项卡选黄色底纹。3. 选中“工资”一列数据（G3到G20单元格），设置单元格格式，在“数字”选项卡中，选择分类为“货币”；在“对齐”选项卡，选靠右的对齐方式。表头和其余各列设置居中，可分别选中、设置，也可按住Ctrl键结合鼠标一起选中设置。4. “部门”一列中所有的“市场部”添加灰-25%底纹：选中“部门”一列数据，选择“格式”“条件格式”命令，在“条件格式”窗口中，设置单元格数值等于“市场部”，在“格式”中完成添加灰-25%底纹的颜色设置，如图6-3。图6-35. 边框线的设置：选中表格相应区域，右击“设置单元格格式”：在“边框”选项卡中，设置外边框为粗线，内边框为细线。6. 在工作簿中，右击Sheet1的工作表标签，选择“重命名”命令，将表重命名为“职员表”，如图6-4。复制该表中内容，到Sheet2中粘贴。7. 将“部门”一列移动到“员工编号”一列之前：选择“部门”一列数据，剪切，然后选中“员工编号”一列数据，右击“插入已剪切的单元格”，如图6-5。8. 选中“部门”所在列，右击“插入”命令，即可插入一列。在表头位置录入“序号”，在相应单元格录入序号“1”，选中该单元格，鼠标指针移动到该单元格的右下角，使指针从一个粗的空心十字变成一个细的实心十字，按住Ctrl键并拖动鼠标至相应单元格后释放，即可完成序号的升序填充。 图6-4 图6-5三、操作结果如样文所示：1. 职员表的操作结果：2. Sheet2表的操作结果：6.2 第2题一、操作要求：1. 打开样文6-2.xls。2. 将标题设为黑体、18号字、合并居中，字型：粗体，字体颜色：深蓝，浅黄色底纹；所有数据设为会计专用格式，应用货币符号；表头和其余各列居中；表格内框线设为细单线，表格外框线设为粗单线，表头下框线设为粗单线。3. 将Sheet1工作表重命名为销售额，并将其复制到Sheet2中。二、操作结果如样文所示：6.3 第3题一、操作要求：1. 打开样文6-3.xls。2. 将标题设为楷体、22号字、合并居中，字型：斜体，字体颜色：绿色，淡紫色底纹；所有数据保留1位小数；所有单元格数据居中；表格内框线设为细单线，表格外框线设为粗单线，表头下框线设为双线。3. 将Sheet1工作表重命名为成绩表，并将其复制到Sheet2中。二、操作结果如样文所示：6.4 第4题一、操作要求：1. 打开样文6-4.xls。2. 将标题设为仿宋体、22号字、合并居中，字型：粗斜体，字体颜色：黄色，-50%灰色底纹；表头行高设为30，所有单元格数据居中；表格内框线设为细单线，表格外框线设为粗单线，表头下框线设为双线。3. 将Sheet1工作表重命名为比赛积分表，并将其复制到Sheet2中。二、操作结果如样文所示：6.5 第5题一、操作要求：1. 打开样文6-5.xls。2. 将表头设为仿宋体、16号字、居中，字型：粗斜体，字体颜色：绿色，粉红色底纹；表中各列列宽设为12，所有单元格数据居中；表格内框线设为细单线，表格外框线设为粗单线，表头下框线设为粗单线。3. 将Sheet1工作表重命名为市场份额表，并将其复制到Sheet2中。二、操作结果如样文所示：6.6 第6题一、操作要求：1. 打开样文6-6.xls。2. 将标题设为楷体、20号字、合并居中，字型：粗斜体，字体颜色：红色，天蓝色底纹；所有单元格数据中数值设置为数值格式，保留2位小数，右对齐，其余数据居中；表格内框线设为细单线，表格外框线设为粗单线，表头下框线设为双线。3. 将Sheet1工作表重命名为股票行情表，并将其复制到Sheet2中。二、操作结果如样文所示：6.7 第7题一、操作要求：1. 打开样文6-7.xls。2. 将标题设为黑体、18号字、合并居中，字型：粗体，字体颜色：白色，深蓝色底纹；所有单元格数据中数值设置为会计格式，应用货币符号，右对齐，其余数据居中；表格内框线设为细单线，表格外框线设为粗单线，表头下框线设为双线，其他设置请参考样文。3. 将Sheet1工作表重命名为预算工作表，并将其复制到Sheet2中。二、操作结果如样文所示：6.8 第8题一、操作要求：1. 打开样文6-8.xls。2. 将标题设为仿宋体、18号字、合并居中，字型：粗体，字体颜色：黄色，淡紫色底纹；所有单元格数据中数值设置为会计格式，应用货币符号，右对齐，其余数据居中；将钢材所在的单元格添加浅黄色底纹；表格内框线设为细单线，表格外框线设为粗单线，表头下框线设为粗单线。3. 将Sheet1工作表重命名为生产资料表，并将其复制到Sheet2中。二、操作结果如样文所示： 6.9 第9题一、操作要求：1. 打开样文6-9.xls。2. 将标题设为楷体、18号字、合并居中，字型：粗体，字体颜色：黑色，粉红色底纹；所有单元格数据中数值设置为数值格式，保留4位小数，右对齐，其余数据居中；所有列宽调为7；表格内框线设为细单线，表格外框线设为粗单线，表头下框线设为双线。3. 将Sheet1工作表重命名为指数函数表，并将其复制到Sheet2中。二、操作结果如样文所示：6.10 第10题一、操作要求：1. 打开样文6-10.xls。2. 将标题设为仿宋体、18号字、合并居中，字型：粗斜体，字体颜色：黄色，淡紫色底纹；所有单元格数据中数值设置为数值格式，保留3位小数，右对齐，其余数据居中；表格内框线设为细单线，表格外框线设为粗单线，表头下框线设为双线。3. 将Sheet1工作表重命名为溶解度测定表，并将其复制到Sheet2中。二、操作结果如样文所示： 第七单元 Excel的公式、函数和图表 97 第七单元 Excel的公式、函数和图表7.1 第1题一、操作要求：1. 利用样文7-1a.xls求出所有员工的平均年龄，并将大于等于30岁的员工设为红色、粗斜体。（注意：请将工作簿中的Sheet1中的内容复制到Sheet2、Sheet3、Sheet4中，用于每个小题的操作，以后各题也一样。）2. 利用样文7-1a.xls按年龄进行升序排列；筛选出工资大于1500的记录；按照“部门”分类，将“年龄”和“工资”进行“均值”分类汇总。3. 利用样文7-1a.xls使用工资一列数据创建一个三维饼图，如图7-1所示。图7-14. 利用样文7-1b.xls ，在“统计表”中进行“求和”合并计算。二、操作提示：1. 可在C21单元格键入“平均年龄”，选中D21单元格，利用工具栏上求平均值的函数，选择相应区域后，按Enter即可，如图7-2，7-3。 图7-2 图7-32. 选中年龄一列数据，“格式”“条件格式”，设置“单元格数值”大于30，在格式中设字体红色、粗斜体，如图7-4。图7-43. 按年龄进行升序排列：在Sheet2中，选中相应数据，“数据”“排序”，设置按关键字“年龄”升序排序，如图7-5。4. 筛选出工资大于1500的记录：“数据”“筛选”“自动筛选”，点击“工资”下拉列表中的“自定义”，如图7-6，在筛选方式窗口中设置工资大于1500，如图7-7，确定即可。 图7-5 图7-65. 分类汇总：在Sheet3中，先按分类字段“部门”排序，然后选定工作表，“数据”“分类汇总”，在打开的对话框中设置分类字段为“部门”，汇总方式为“平均值”，选定汇总项“年龄”和“工资”，如图7-8，确定即可，折叠后效果如图7-9。 图7-7 图7-8图7-96. 在Sheet4中，使用工资一列数据创建一个三维饼图：“插入”“图表”，在“图表向导”的第一步骤中选择图表类型为“饼图”，子图表类型“选三维饼图”，如图7-10；第二步利用鼠标拖动选择数据区域=Sheet1!$G$3:$G$20，如图7-11；第三步根据样图设置标题为“工资比例图”，不显示图例，数据标签选中“类别名称”和“百分比”，不显示引导线，如图7-12，完成。 图7-10 图7-11图7-127. 打开7-1b.xls，选中进行合并计算的C14单元格，“数据”“合并计算”，在弹出对话框中进行相应的设置：在“函数”下拉列表框中选择“求和”选项，在“引用位置”列表框选择Sheet1!$A$2:$B$7区域，单击“添加”按钮，将“引用位置”添加到“所有引用位置”列表框中，再在“引用位置”列表框选择 Sheet1!$D$3:$E$7区域，同样添加到“所有引用位置”列表框中。在“标签位置”选中“最左列”复选框，如图7-13，单击“确定”即可完成合并计算，结果如图7-14。 图7-13 图7-147.2 第2题操作要求：1. 利用样文7-2a.xls，在Sheet1中求出每个季度几种产品的最大销售额，并将销售额大于50万元的产品设为绿色、粗斜体。2. 利用样文7-2a.xls，在Sheet2中按第一季度销售额进行降序排列，并筛选出第二季度销售额大于50万的记录；在Sheet3中按照“类别”分类，将“第一季度”至“第四季度”进行“最大值”分类汇总。硬件部 2009 年销售额类别第一季第二季第三季第四季总计合计 最大值42100491558426480954738318793便携机 最大值5155005454778789526847654工控机 最大值2232014878741454528140000网络服务器 最大值7500041282144245645645678微机 最大值7585201266005482114565989总计最大值421004915584264809547383187933. 利用样文7-2a.xls，在Sheet4中使用汇总结果创建一个簇状柱型图，如图7-15所示。图7-154. 利用样文7-2b.xls，在“汇总表”中进行“求和”合并计算。汇总表部门工资开发部3400测试部3400文档部2400市场部37007.3 第3题操作要求：1. 利用样文7-3a.xls，在Sheet1中求出所有学生的平均成绩，并将成绩大于等于75分的设为红色、粗斜体。2. 利用样文7-3a.xls，在Sheet2中按CJ1进行降序排列，并筛选出CJ3成绩小于75的记录；，在Sheet3中按照“专业”分类，将“CJ1”至“CJ8”进行“最小值”分类汇总。成绩统计表学号专业CJ1CJ2CJ3CJ4CJ5CJ6CJ7CJ8数学 最小值8071677764727571物理 最小值5662767666546760化学 最小值7474767660726760总计最小值56626776605467603. 利用样文7-3a.xls，在Sheet4中使用使用学号为904107创建一个数据点折线图，如图7-16所示。图7-164. 利用样文7-3b.xls，在“某企业费用支出汇总（万元）”中进行“求和”合并计算。某企业费用支出汇总（万元）支出情况交通技术投入书籍翻译设备差旅费其他预算8.633256482718实际6.633.717.33.136.924.215.97.4 第4题操作要求：1. 利用样文7-4a.xls，在Sheet1中求出每一季度的最大值，并将大于等于20岁的设为黄色、粗斜体。2. 利用样文7-4a.xls，在Sheet2中按第四季度进行升序排列，并筛选出第四季度大于等于20的记录；在Sheet3中按照“地区”分类，将“第一季度”至“第四季度”进行“最大值”分类汇总。全国X球比赛积分表 队名地区第一季度第二季度第三季度第四季度北部队 最大值25232122南部队 最大值23252623总计最大值252526233. 利用样文7-4a.xls，在Sheet4中使用第二季度数据创建一个圆环图，如图7-17所示。图7-174. 利用样文7-4b.xls，在“统计表”中进行“计数”合并计算。统计表BMXM劳资科2企管办2油材科6生产科27.5 第5题操作要求：1. 利用样文7-5a.xls，在Sheet1中求出所有记录的最小利润值，并将利润大于5000的设为加双线的下划线。2. 利用样文7-5a.xls，在Sheet2中按利润进行升序排列，并筛选出利润小于等于3000的记录；在Sheet3中按照“方案”分类，将“概率”和“利润”进行“最大值”分类汇总。方案市场情况年份概率利润乙方案 最大值0.77000甲方案 最大值0.68000总计最大值0.780003. 利用样文7-5a.xls，在Sheet4中使用使用第2至4行数据创建一个三维簇状条形图，如图7-18所示。图7-184. 利用样文7-5b.xls根据样文数据，在“盈利概率分析”中进行“最大值”合并计算。盈利概率分析年份概率利润第一年0.66000第二年0.780007.6 第6题操作要求：1. 利用样文7-6a.xls，在Sheet1中求出所有日期的开盘价的最高值，并将小于等于C8单元格的数值设为红色、加单下划线。2. 利用样文7-6a.xls，在Sheet2中按收盘价升序排列，并筛选出最高价大于等于16.17的记录。3. 利用样文7-6a.xls，在Sheet3中根据股票行情表创建一个开盘-盘高-盘低-收盘的股价图，如图7-19所示。图7-194. 将样文7-6b.xls中数据，按照“月份”分类，将各城市进行“均值”分类汇总。世界各大城市16月份气温平均值 ( )月份最高/最低北京香港巴黎伦敦莫斯科纽约东京一月 平均值-4.515.534.5-12.50.54二月 平均值-21544.5-915三月 平均值517.56.510-458四月 平均值1421.510.58.55.510.513.5五月 平均值2025.513.51213.516.513六月 平均值2327.5171615.521.521.5总计平均值9.2520.416679.0833339.251.59.16666710.833335.利用样文7-6b.xls，在“世界各大城市1-6月份气温最低值()”中进行“最小值”合并计算。世界各大城市1-6月份气温最低值 ( )月份最高/最低北京香港巴黎伦敦莫斯科纽约东京一月-101302-16-3-1二月-81312-13-20三月-11629-813四月71954169五月1323878124六月152611111017187.7 第7题操作要求：1. 利用样文7-7a.xls，在Sheet1中计算总销售额、平均销数额，并将销售额大于等于2000的数据设为红色、粗斜体。2. 利用样文7-7a.xls，在Sheet2中按日期进行升序排列，并筛选出销售额大于1000的记录；在Sheet3中按照“销售地区”分类，将“销售额”进行“求和”分类汇总。 建筑产品销售情况 （万元） 日期产品名称销售地区销售额东北 汇总4942.05华北 汇总2555.4华南 汇总2218.5西北 汇总2459西南 汇总1124.2总计13299.153. 利用样文7-7a.xls，在Sheet4中使用预算工作表中“调配拨款”一列数据创建一个分离型饼图，如图7-20所示。图7-204. 利用样文7-7b.xls在“统计表”中进行“求和”合并计算。统计表产品名称销售额塑料5942.05钢材3901.5木材3455.67.8 第8题操作要求：1、利用样文7-8a.xls，在Sheet1中求出总课时，并将总课时小于100的加双下划线。2. 利用样文7-8a.xls，在Sheet2中按授课人数进行降序排列，并筛选出授课人数小于160的记录；在Sheet3中按照“授课班数”分类，将“授课人数”和“课时”进行“求和”分类汇总。姓名授课班数课程名称授课人数课时(每班)总课时3 汇总233894 汇总107476 汇总941249 汇总5871总计4923313. 利用样文7-8a.xls，在Sheet4中在“课时统计表”中进行“求和”合并计算。课时统计表 授课班数授课人数课时(每班)323389410747958716941244. 利用样文7-8b.xls使用生产资料表中，钢材各行的数据创建一