计算机应用基础实验指导EXCEL

1、Excel 2003 操作电子表格软件Excel是用于数据处理的软件，它可以对由行和列构成的二维表格总的数 据进行管理，能运算、分析、输出结果，并能制作出图文并茂的工作表格。电子表格中的每 一个单元格可以存储的数据并不是简单的数值，而是字符、数值、变量、公式、图像等信息。 实验一：Excel基本操作一、实验目的掌握Excel文档的编辑和格式化，利用公式和函数实现数据处理。二、实验任务与要求掌握电子表格的基本概念、中文Excel的功能、运行环境、启动和退出掌握工作簿和工作表的基本概念、工作表的创建、数据输入、编辑和排版掌握工作表的插入、复制、移动、更名、保存和保护等基本操作熟练掌握单元格的绝对地

2、址和相对地址的概念，工作表中公式的输入与常用函数的 使用。掌握工作表的格式化、页面设置、打印预览和打印。三、知识要点工作簿、工作表和单元格编辑栏公式和函数四、实验步骤与操作指导【题目1】数据的基本操作要求：在Sheetl和Sheet2中输入图3.1所示的原始数据。对Sheet2实现删除班级所在 列；将专业列移动到学号列之前，成为A列；将所有的“公共事业管理”替换为“公管”。ABCDEFG1入据输数200137.88888E+1241/25MonTu已WedThuFriSatSun61248163264E23234567891011121314151学号 姓名20312001邵志平 203120

3、02fPB 120312003洪雅丽 20312004 月文 我3120沱徐玄华 20312006方顺幸 20312007fetCW20312008 张伟 20313001晓晓 20313002榷 明 203130031Ik拼拼用用用用- 专公公公公公公公1979218678196831768328796283782738828995165521576896848163987290701222军事理论高等数学计算机理理理理理理理理 管管管管管管管管 业业业业业业业业9467749795868993782031300520313006伟雯6499869375(b) Sheet2(a)Sheetl

4、图3.1 Sheetl和Sheet2原始数据操作步骤如下:在Sheetl中输入数据，注意不同数据的输入方法在Sheet2中输入数据。Sheet2中学号、专业、班级可以使用填充，其他照原样输入。删除列移动列替换【题目2】工作表操作要求：复制sheet2工作表到最后；并将新复制的工作表取名为“成绩”；删除工作表Sheet3；在Sheetl和Sheet2之间插入2张空白工作表，并将它们命名为 “表1”和“表2”；复制Sheetl为Sheetl（2），并对工作表Sheetl（2）施加保护。【题目3】公式和函数操作要求：在sheet2的Gl:Jl添加“总分”、“平均分”、“等级”、“军事高数均及格”列；

5、下面 各行分别为每位同学的总分、平均分（四舍五入，保留小数l位）、等级（分为优、合格和 不合格，平均分85以上为优），军事高数均及格（分为合格和不合格）的具体数据；最后把 “学号”、“姓名”和“平均分”的内容复制到“表l”中ABCDEFGHIJ1专业学号姓名军亭理论商学数学计算机总分平均分等级军亭商裁2公共管理事业管理20312001用志平97929428394.3优秀会格3公共管理亭业管理20312002郑国梁86786723177会格会格4公共管理亨业管理20312003洪雅丽96837425384.3合格合格5公共管理亨业管理20312004鸿文76839725685.3优秀会格7漱育技

6、术学20313005吴华伟90819326488优秀会格15教育技术学2031300670637520869.3合格合格1617最奇平均分97. 718囊低平埼分6319平均分高于75人教11图3.2利用公式计算数据操作步骤如下：求总分1）在 G2 中输入：=SUM（D2:F2）2）鼠标指针移动到G2的填充柄上，拖动鼠标至Gl5后面有关求平均分、等级、均及格的操作都类似于求总分操作，所以只给出公式四舍五入求平均分在H2中输入公式并填充，公式：=ROUND（AVERAGE（D2:F2）,l）求等级在I2中输入公式并填充，公式：=IF（H2=85,”优”，IF（H2=60,”合格”，“不合格”）求

7、军事理论高等数学均及格在J2中输入公式并填充，公式：=IF（AND（D2=60,E2=60），”合格”，“不合格”）求最高平均分、最低平均分和平均分高于75的人数=MAX（H2:H15）, =MIN（H2:H15）, =COUNTIF（H2:H15, “=75”）把“学号”、“姓名”和“平均分”的内容复制到“表1”附：常用函数求和函数SUM（参数1,参数2,.）算术平均值函数AVERAGE（参数1,参数2,.）最大值函数MAX（参数1,参数2,.）最小值函数MIN（参数1,参数2,.）四舍五入函数ROUND （数值型参数，n）功能：返回对“数值型参数”进行四舍五入到第n位的近似值条件计数COU

8、NTIF （条件数据区，“条件”）功能：统计“条件数据区”中满足给定“条件”的单元格的个数统计个数函数COUNT（参数1,参数2,.）功能：求一系列数据中数值型数据的个数COUNTA（参数1,参数2,.）功能：求“非空”单元格的个数COUNTBLANK（参数1,参数2,.）功能：求“空”单元格的个数【题目4】格式化工作表专业公共管理事业寥页 公共管理事业褊 公共管理事业管理公共管理亨业管理 公共管理事业管理 公共管理亨业宥 公共管理事业丽字号处31戏01W31四冥 处31戏0320312006 戏 312W7 31 如。8姓名郁志平郑国梁洪雅丽徐文华 方顺平张伟军来理论8696花87弗7389

9、-学生成绩表计算猫946774死明9?高等教学78弗爵9678演954处313001姚说睦65.20313002 明575278686420313003 郑瑛98969920313004 陈立7284865 6 一I 一I20313005吴华伟9081639375要求：调整sheet2的A列为“最适合的列宽”；在第1行前插入一行作为标题行，文字 为“学生成绩表”并对其格式化；为从“专业”到最后一名同学的“军事高数均及格”的单元格区 域的各单元格加上边框；将军事理论、高等数学、计算机中小于60的数据用红色倾斜显示， 大于等于90的用浅蓝色背景显示；BCDEF（2）选择“格式”菜单的“条件格式”命

10、令项（3）在条件格式对话框中完成相应的格式设置5.设置“顶端标题行”（1）文件一页面设置在“页面设置”对话框中完成设置。五、操作题操作题1： Excel工作表的基本编辑和格式化1、启动Excel，创建学生成绩表，以“成绩表.xls”为文件名保存在自己的文件夹中， 原始数据如下所示：学生成绩表姓名高数英语计算机总分平均分总评程小婉988091王一平768992李广明867889段小林896889李宁787480王建国677787吴学军878889苏林798777合计平均分最高分2、先计算每个学生的“总分V “平均分”，并求出每门课的“合计V “平均分”、“最高 分”和“最低分”。3、在总评栏利用

11、IF函数计算平均分大于85分的“优秀”学生。4、将表格标题“学生成绩表”合并居中。5、将“优秀”学生数据复制到sheet2工作表中。6、 将sheetl工作表改名为“原始成绩表”，sheet2工作表改名为“优秀成绩表”。7、以任一种方式拆分当前工作表窗口，然后再撤消拆分。8、将sheetl工作表复制到sheet3工作表中，并对sheet3工作表进行格式化。将表格标 题与表格空一行，将标题设置为隶书、红色、加粗、20磅、加双下划线；将表格各栏标题 设置成粗体、居中，再将表格中的其他内容居中，平均分保留一位小数，平均分少于85分 的设置为宝石蓝图案、红色字体。9、对sheet2工作表自动套用“三维

12、效果1”格式，将表格填充色改为浅灰色，字体改 为黄色，加双线外框。实验二：Excel高级操作一、实验目的掌握Excel电子表格的高级操作二、实验任务与要求掌握图表的创建掌握数据清单的操作三、知识要点图表的一些概念数据清单数据透视表四、实验步骤与操作指导【题目1】创建图表、编辑图表要求：为实验一中的Sheet2创建一个如图所示的图表；将图表移动到A22： H38的区域；然后将图表类型改为数据点折线图；修改标题，使标题文字为“公关专业学生成绩图表”;将计算机成绩添加到图表中；在图表中添加文字“1班”。创建图表利用图表向导来生成图表移动图表、更改图表大小改变图表类型图表一图表类型一折线图修改标题图表

13、一图表选项一标题向图表中添加数据在图表中添加文字1201008040200学生成绩图表军事理论高等数学邵志平郑国梁洪雅丽 冯文 徐文华方顺平姓名【题目2】数据清单操作：排序和分类汇总要求：将实验一Sheet2中的A2： J16区域复制到“表2”中形成一个数据清单，对表2 中的数据按“姓名”升序排序；重新对表2中的数据进行排序，要求以总分为第一关键字、 数学成绩为第二关键字按降序排序；复制“表2”工作表，新表改名为“表3”，对表3数据 按“专业”分类汇总，求出各专业的各项数值型字段的平均值。复制并排序多级排序数据一 “排序”分类汇总1）先对分类列排序，即对专业排序2）数据一分类汇总3）在分类汇总

14、对话框中进行设置【题目3】数据清单操作：筛选数据要求：复制“表2”工作表，新表改名为“表4”，按总人数的20%选出总分较高的数据 行；复制“表2”工作表，新表改名为“表5”，显示军事理论和数学均大于等于80的数据 行；对“表5”重新做筛选，显示语文大于等于90或小于60的数据行。复制“表2”工作 表，新表改名为“表6”，制作高级筛选：筛选出军事理论或数学大于等于90的数据行。筛选总分前20%的数据行，如图所示“数据”菜单一 “筛选”一 “自动筛选”显示军事理论和数学均大于等于80的数据行显示军事理论大于等于90或小于60的数据行筛选出军事理论或数学大于等于90的数据行在同一项使用“或”操作，在

15、不同项采用“与”操作，就可以使用自动筛选。现要 求不同项之间使用“或”操作，可以采用高级筛选。1）在“表6”中与数据清单不相邻的单元格中输入如图所示的条件说明：在高级筛选中“与”操作条件放在同一行，“或”操作条件放在不同行2）使用“数据”菜单一 “筛选”一 “高级筛选”，结果如图2.6图2.6高级筛选实验结果五、操作题操作题1：图表的应用1、启动Excel，打开“成绩表xls”，从中复制以下数据后，保存为文件“图表xls”。数 据如下：姓名高数英语计算机程小婉988091王一平768992李广明867889段小林896889李宁7874802、对表格中的所有学生数据，在当前工作表中创建嵌入的三

16、维簇状柱形图图表，标题 为“学生成绩表”。3、对嵌入式图表进行编辑操作：对图表添加分类轴标题“姓名”数值轴标题“成绩” 将表中高数的数据系列位于计算机数据系列之后。4、对图表进行格式化操作。（1）对图表区字体格式设置为12号，图案设置为最粗的圆角边框（2）将图表标题“学生成绩表”设置为粗体、14号、双下划线；分类轴标题“姓名” 设置为粗体、11号；数值轴标题“成绩”设置为加粗倾斜、11号、方向为50。（3）将高数数据标记的字号设置为14号，上标。（4）将数值轴的主要刻度设置为20，字体10号。（5）将图例中的字体设置为10号、边框带阴影，并将图例移至图表区右侧靠上位置。效果如下所示：5、根据原

17、始数据，建立程小婉、王一平三门课程的折线图。操作题2：数据管理和分析1、启动Excel，打开“成绩表.xls”，修改成绩表，在“姓名”后添加一列“性别”，并 以“成绩数据表.xls ”文件保存。2、将原始数据复制到sheet2工作表中，进行下列排序操作：（1）对工作表中数据按性别排序。（2）先按工作表中数据按性别排序，性别相同的按英语成绩降序排序。3、将原始数据复制到sheet3工作表中，然后进行筛选操作。（1）在工作表中筛选出男同学纪录。（2）在工作表中筛选出三门课至少有一门课大于90分的男同学纪录。4、将原始数据复制到sheet4工作表中，然后进行分类汇总操作。（1）按性别分别求出男、女同学的平均分。（2）在原有分类汇总的基础上，再汇总出