大学计算机基础实验指导书(ecxel).doc

大学计算机基础实验指导Excel 2003 基本操作实验实验一 Excel工作表的创建和编辑一、实验目的1.掌握Excel工作簿建立、保存与打开2.掌握工作表中数据的输入3.掌握数据的编辑修改4.掌握工作表的插入、复制、移动、删除和重命名5.掌握工作表数据的自定义格式化和自动格式化6.掌握公式和函数的使用二、实验内容任务1 新建一个“学生成绩表.xls”工作簿文件，在它的工作表Sheet1中建立内容如下：(1)新建工作簿Book1。【提示】启动Excel 2003，系统自动新建一个工作簿文件Book1.xls。(2)将工作簿文件“Book1.xls”更名为“学生成绩表.xls”。【提示】选择“文件|保存”或“文件|另存为”命令，打开“另存为”对话框（注：新建文件首次保存时，也将打开“另存为”对话框），选择好“保存位置”后，在下方的“文件名”编辑框中输入:学生成绩表，在“保存类型”编辑框中选定：Microsoft Office Excel 工作簿(*.xls)，并单击“保存”按钮。(3)在工作簿的第一张工作表Sheet1中，从A1单元格开始照样输入数据。【提示】输入批量数据时，建议不要采取单击编辑栏后再输入，因为这样效率低，即费时又费力。可先在A1单元格中输入表标题“学生成绩表”后，压【Enter】键使插入光标移至A2，输入“计科1班”，在A3输入“制表日期：“2011.05.07”，在A4:E4区域中依次输入表头文字“学号”、“姓名”等，在A5中输入“061401001”（纯数字文本，其余学号的输入可利用填充柄，注意纯数字文本的输入时要加英文半角状态下的单引号。）输入后请认真观察数据有无变化，不同数据类型的对齐方式是什么。任务2 工作表的格式化(1)合并单元格并居中表标题。【提示】选定区域A1:E1，单击工具栏上的“合并并居中”按钮；同理操作A2:E2。(2)改变数据的字体、字号和颜色。【提示】选定欲改变字体的表标题单元格，单击“格式|单元格”命令打开“单元格格式”对话框；选择“字体”选项卡；设置“字体”为“宋体”，“字形”为“加粗”，“字号”为“12”。(3)数据的对齐方式。合并A3:E3并设置为水平左对齐；对A4:F4区域或第三行设置为水平居中；对姓名值各单元格（即B5:B12）设置为水平分散对齐；各科成绩设置为居中。(4)对A列设置最适合的列宽。选择“格式-列最适合的列宽”。 (5)为附标题“计科1 班”所在单元格A2加底纹。【提示】选中单元格A2，打开“单元格格式”对话框，在其“图案”选项卡页面选择单元格底纹的“颜色”为“浅黄”即可。注意区分底纹的颜色和图案的颜色。(5)为工作表加边框。经过以上格式化操作后工作表Sheet1结果如下。任务3 工作表的复制与重命名(1)将工作表Sheet1的数据复制到Sheet2中。”【提示】选中Sheet1，选择其数据区域（即A1:E12）,压【Ctrl+C】键，或单击工具栏上的“复制”按钮，使其被跑动的虚线框住；再选中Sheet2，使用“粘贴”命令即可。(2)将工作表Sheet1和Sheet2，分别更名为“1班成绩表”和“1班成绩分析表”。【提示】右击Excel主窗口下方的工作表表名（如Sheet1），从弹出的快捷菜单中选择 “重命名”命令，此时表名呈反显（被黑色覆盖），输入新名字即可。任务4 工作表数据的编辑与修改(1)在工作表中增添新字段。即对工作表“1班成绩分析表”添加“总分”、“平均分”、“绩点”、“总评”四个字段。(2)为工作表添加新的行。即在区域B13:B15各单元格中依次添加各科成绩“学分”、“最高分”和“平均分”。在“学分”字段分别输入3、2、2，分别代表各门成绩的学分。如下图所示。（3）调整表的格式。效果如下： 任务5 利用公式和函数进行计算 (1) 先计算每个学生的总分和平均分，平均分保留两位小数。再求出各科目的最高分、平均分。【提示】总分、平均分的计算可利用Sum、Average函数，也可直接输入计算公式，请注意使用函数法和公式法的区别。如本题用函数法计算总分的话，通过编辑栏左边的fx插入函数SUM,点击“折叠”按钮选择数据范围C5：E5（红色选中部分），如下图所示：选定完点击“确定”后即可。若是使用公式法，鼠标先选中单元格F5，然后手动的在编辑栏中输入公式，如：“=C5+D5+E5”（公式法要以等号开始，以回车键结束）。其余单元格的计算可以通过拖动填充柄实现。最高分和平均分计算使用Max、Average函数。保留两位小数的设置：选定要设置的单元格,“右键|设置单元格格式|数字|数值”。（2）计算每个学生的成绩绩点，保留两位小数。其中绩点=（数学成绩*数学学分+英语成绩*英语学分+计算机成绩*计算机学分）/总学分。【提示】由于计算每个学生的绩点成绩都要用到C13、D13、E13 这三个单元格，所以这里要涉及到绝对引用的使用。先来理解相对引用。课本P135中提到，所谓相对引用，是指当公式在复制时，公式中的引用单元格地址会随之改变，如我们上一个小题中的使用公式法来求总分，一个同学的总分是“C5+D5+E5”，使用填充柄后，第二个同学的总分随着单元格往下，也相应的变成“C6+D6+E6”,直到最后一个同学的总分为：“C12+D12+E12”。这种公式中单元格地址会自动调整的引用就是相对引用。所谓绝对引用，指被引用的单元格与引用的单元格的位置关系是绝对的，公式不随位置的改变而变化。本小题中绩点的计算要求单元格C13、D13、E13固定不变，所以要使用绝对引用。Excel中通过在行号和列号前面添加“$”符号来实现，即在单元格H5中输入“=(C5*$C$13+D5*$D$13+E5*$E$13)/7”（注意单元格前面的符号）。计算第一个学生的绩点后，其余学生的绩点通过填充柄方式实现。可以通过编辑栏来查看每个学生的绩点成绩的计算公式。试比较如果没用使用绝对引用，即没有在相应单元格前面加“$”符号，结果会是：很明显，得到的结果（红色圈中部分）是错误的。(3)评出优秀学生，平均分在85分以上（包括85分）的为优秀，在总评栏上填写“优秀”。【提示】本题对第一个学生的总评公式存放在H5单元格中，点击插入if函数，在logical_test栏中填写if函数的判断条件，其余学生的总评通过填充柄方式实现。特别提醒：其它的显示空白，则要在value_if_false中输入空格键，若无任何输入的话，默认的情况是自动填充了“false”。结果如下图所示：（4）求计算机成绩在90分以上的学生的数学成绩之和，结果放在单元格J5里面。【提示】本小题要用到条件求和函数SUMIF，详细见下图：Range用于条件判断的单元格区域，即计算机的成绩；criteria为判断的标准，输入“=90”；sum_range为求和的范围，即满足条件的学生的数学成绩。结果如下图所示：（5）使用排位函数RANK求学生李萍的英语成绩在所有学生英语成绩中的排名（按升序），结果放在单元格J8中。【提示】排位函数RANK的使用要注意其order的值。若order为0或省略，表示排位按降序；若非0则为升序。具体操作如下图：在number中选择该学生的英语成绩，Ref选中所有学生的英语成绩，order中填入一个非0的数即可，得到结果为该学生的英语成绩排位3（升序）。表格的最终内容如下：任务6 工作表的复制、插入、删除与修改(1)在Sheet3工作表前插入一张工作表Sheet4，将“1班成绩表”复制到Sheet4中A1起始的区域。(2)删除Sheet3工作表。并将Sheet4工作表移动到最前面，成为第一张工作表。【提示】指向Sheet3表名，击右键，从弹出的快捷菜单中选择“删除”命令；然后指向Sheet4表名，压住鼠标左键拖动到第一张表位置即可。任务7 工作表中部分数据的突出显示(1)在1班成绩分析表中，利用条件格式，将学生的每门课中最高分以粗体、蓝色字、图案为6.25%灰色、颜色为红色突出显示。【提示】选中存放“数学”成绩的8位学生的成绩，利用“格式|条件格式”命令，在如图所示的“条件格式”对话框中，输入条件（$C$13表示存放数学最高分的单元格），再单击“格式”按钮对满足条件的单元格进行相关的格式设置。 (2)将工作表sheet4中的内容自动套用“彩色1”格式，然后将该表格的内框线改为红色细线。【提示】选中内容后，利用“格式|自动套用格式”，选择“彩色1”即可。实验二 Excel图表和数据管理一、实验目的1.掌握嵌入图表和独立图表的创建2.掌握图表的整体编辑和对图表中各对象的编辑3.掌握图表的格式化4.掌握数据清单的排序、筛选和分类汇总等的应用方法二、实验内容任务1 嵌入图表的创建与编辑(1)打开“学生成绩管理.xls”文件，选中“2班成绩表”，为它创建嵌入的三维簇状柱形图图表，系列产生在列，图例位置在底部，图表标题为“学生成绩表”，其它为默认设置。【提示】对数据进行图表化，首先要识图，即对要求已知的图表明确要选中工作表中的哪些数据（本题可选中“2班成绩表”中的B4:E12区域），图表的类型是什么等。(2)对上面创建的“学生成绩表”图表进行移动、放大到A15:G30区域。(3)将图表中数学的数据系列删除，然后再将计算机与外语的数据系列次序对调。【提示】系列的删除要先选中该系列，再通过“右键|删除”即可；系列次序的调整，要先选中任意一个系列，然后通过“格式|数据系列|系列次序”调整次序。(4)为图表中“计算机”的数据系列增加以值显示的数据标记。【提示】选中图表，选择“图表”菜单“图表选项”，在对话框中选择“数据标志”(5)为图表添加分类轴标题“姓名”。【提示】选中图表，选择“图表”菜单“图表选项”，在对话框中选择“标题”【提示】对图表进行编辑，首先要搞清楚图表中的各个对象，如“图表标题”、“绘图区”、“数据系列”、“分类轴”、“图例”等；选中所需的对象通过快捷菜单(一般为“在图标区域选中/右键”，然后选择相应的操作选项)进行相应的操作。（6）将上面的图表类型改为折线图。【提示】选中图表，然后选择“右键|图表类型|折线图”。 (7)保存“学生成绩管理.xls”文件，退出Excel 2003。任务2 利用记录单管理数据(1) 打开“学生成绩表.xls”文件，选中“1班成绩表”。在B列后插入2列，列名分别为“性别”、“专业”，并输入相应数据，如图所示： （2）选中整个数据清单A4：G12，利用“数据”菜单的“记录单”命令，在对话框中点击“新建”按钮，输入一行新的数据。并利用“删除”按钮将它删除。 【提示】第一次按“新建”后输入，输入完毕后再次按“新建”按钮将新数据添加到数据清单中。在删除数据前，可用“上一条”、“下一条”按钮浏览并选择数据。任务3 排序、筛选数据（1）选中整个数据清单A4：G12，点击“数据”菜单，“排序”命令。在对话框中的主要关键字选择“性别”，次要关键字选择“学号”，点击确定。 【提示】在排序中，先按主要关键字排序，当遇到主要关键字一样的情况下，才会用次要关键字。同理在次要关键字一样的情况下，用第三关键字。（2）选中整个数据清单A4：G12，点击“数据”菜单，“筛选”-“自动筛选”命令，如图所示：（3）筛选出数学75分的男同学记录：在“性别”下拉按钮中选择“男”，在“数学”下拉按钮中选择“自定义”，并且选择“大于”、输入“80”。（4）点击“数据”菜单，“筛选”“自动筛选”命令，删除筛选结果。 任务4 分类汇总（求各个专业的数学平均分，外语平均分和计算机总分）【提示】做分类汇总前，要认真分析分类汇总的三个要素：分类字段（本次汇总按什么分类）、汇总方式（求和，平均，计数等）、汇总项（要计算的字段）。本例中，分类字段为专业，汇总方式有：求平均，求和。汇总项是数学，外语，计算机。 如果一个分类汇总的汇总方式只有一个，则一次分类汇总就可以完成，如果有多个汇总方式，则需要进行多次分类汇总操作，如本例，要分成2次汇总：1、对各个专业求数学，英语平均分。2、对各个专业求计算机总分。（1）对数据清单进行排序操作，关键字为“专业”。 【注意】在分类汇总前，要以分类字段为关键字进行排序操作。排序的目的是让分类字段相同的各行数据排在一起，避免出现多个汇总结果，所以升序和降序不重要。（2）选择整个数据清单A4：G12，点击“数据”菜单，“分类汇总”命令。在对话框中选择“专业”、“平均值”、“数学”和“英语”后，点击确定。结果如图所示：（3）选择整个数据清单A4：G16，点击“数据”菜单，“分类汇总”命令。在对话框中选择“专业”、“求和”、“计算机”。【注意】在同一个数据清单中执行多次分类汇总，要把“替换当前分类汇总”选项去掉。结果如图所示：（4）删除分类汇总：选择整个数据清单A4：G20，点击“数据”菜单，“分类汇总”命令。在对话框中选择“全部删除”按钮。【思考】当分类汇总里包含多个分类字段时，通过多次分类汇总操作能不能完成？任务5 数据透视表 求各个专业数学、外语、计算机平均分并且统计男女生人数。【提示】在数据透视表操作前，要以分类汇总的思路去分析，分类字段，汇总方式和汇总项。（1） 选中整个数据清单A4：G12，选择“数据”菜单，“数据透视表”命令。在对话框中点击“下一步”，使之到达第三个步骤。点击“布局”按钮。（2） 在布局对话框中，有四个区域，其中页，行，列3个区域是用于放分类字段，把本例的2个分类字段按钮“性别”和“专业”分别拖到行和列区域。（3） 把汇总项：“数学”，“外语”，“计算机”和“姓名”拖到数据区域。结果如图所示： （4） 观察自动