Excel实训教案.docx

1、Excel实训教案实训一： 建立与编辑工作表一、实验目的1. 掌握启动和退出Excel 2003的各种方法，掌握工作簿的创建、打开、保存及关闭的各种方法。2. 熟练掌握工作表中各种数据类型(包括文本、数字、日期/时间、公式和函数、批注)的输入方法和技巧。3. 熟练掌握工作表中数据的编辑(复制、移动、清除和修改等)及工作表的编辑(插入、删除单元格、行和列)方法，了解行和列的隐藏与锁定、窗口的拆分与还原。二、实验内容1. 使用至少四种方法启动、退出Excel 2003；熟悉Excel 2003窗口，注意与Word 2003窗口的不同之处。2. 在硬盘D:区下创建名为“学生”的文件夹，并在该文件夹下

2、建立一个文件名为“计算机一班”的工作簿，在工作表Sheet1中创建一个如图3-1所示的工作表。图3-13. 编辑修改“成绩表”工作表。三、实验步骤1. 使用至少四种方法启动、退出Excel 2003；熟悉Excel 2003窗口，注意与Word 2003窗口的不同之处。1)分别使用Excel 2003中的各种启动方法：(1)通过双击Excel 2003的桌面快捷方式启动。(2)选择“开始”“程序”“Microsoft Excel”命令启动。(3)通过打开Excel 2003文件启动。(4)通过“开始”菜单中的“运行”命令(在“运行”对话框中输入“excel”)启动。2)分别使用Excel 20

3、03中的各种退出方法：(1)单击Excel 2003窗口中标题栏最右端的“关闭”按钮。(2)双击Excel 2003窗口中标题栏最左端的控制菜单图标。(3)单击“文件”菜单中的“退出”命令。(4)单击标题栏最左端的控制菜单图标，再单击其中的“关闭”命令。(5)使用快捷键Alt+F4。3)认识Excel 2003窗口的特有之处：(1)选择某个单元格，试着在单元格内直接输入数据以及使用编辑栏输入数据。(2)选择“工具”菜单中的“选项”命令，打开“选项”对话框，选择“常规”选项卡，如图3-3所示。将“新工作簿内的工作表数”改为“9”。然后练习使用工作表控制按钮。图3-32. 在硬盘D:区下创建名为“

4、学生”的文件夹，并在该文件夹下建立一个文件名为“计算机一班”的工作簿，在工作表Sheet1中创建一个如图1所示的工作表。1)文本型和数值型数据的输入：单击A1单元格，可以直接输入“入学日期”，按Tab键或单击B1单元格，然后输入“学号”，其他文本型数据(姓名、计算机、高等数学、英语、体育、总分、平均分和各个姓名)均可直接输入。单击D2，直接输入“98”，其他单科成绩都可直接输入。输入过程中及时保存。2)日期型数据的输入：单击A2单元格输入“2009-1”后回车，即可输入此日期。然后再选定A2，使用自动填充功能，拖动到A4，即可在A3、A4自动填入“2009-2”、“2009-3”。选定A4，使

5、用自动填充功能，按Ctrl键拖动到A6，即可在A5、A6自动填入“2009-3”、“2009-3”。选定A3，选择“编辑”菜单中的“复制”命令，然后分别在A7、A10单元格使用“编辑”菜单中的“粘贴”命令，复制填入“2009-2”，最后单击A3单元格结束复制。A8、A9、A11中的“2009-1”也使用此法复制填入。输入过程中及时保存。3)各个学号的输入：单击B2，输入数字“1”后回车，然后再选定B2，按Ctrl键使用自动填充功能，拖动填充到B11，填入学号“2”“10”。4)总分公式的输入：方法一：直接在H2单元格输入“=D2+E2+F2+G2”后回车，即可计算出刘欣文的总分346，然后使用

6、自动填充功能拖动到H11，其他总分分别填入。方法二：直接在H2单元格输入“=SUM(D2:G2)”后回车，即可计算出刘欣文的总分346，然后使用自动填充功能拖动到H11，其他总分则分别填入。方法三：选定H2单元格后使用“插入”菜单中的“函数”或使用工具栏中的“粘贴函数”按钮，在弹出的“粘贴函数”对话框中选择“SUM”函数，使用粘贴函数也可在H2单元格计算出刘欣文的总分346。使用自动填充功能拖动到H11，其他总分则分别填入。方法四：选定H2单元格后单击工具栏中的“自动求和”按钮，然后回车，即可计算出刘欣文的总分346。使用自动填充功能拖动到H11，其他总分则分别填入。5)平均分的计算输入：方法

7、一：直接在I2单元格输入“=AVERAGE(D2:G2)”后回车，即可计算出刘欣文的平均分86.5，然后使用自动填充功能拖动到H11，其他平均分则分别填入。方法二：选定I2单元格后使用“插入”菜单中的“函数”或使用工具栏中的“粘贴函数”按钮，在弹出的“粘贴函数”对话框中选择“AVERAGE”函数，使用粘贴函数也可在I2单元格计算出刘欣文的平均分86.5。使用自动填充功能拖动到I11，其他平均分则分别填入。6)工作表的管理：工作表的选择：单击可选择某个工作表；按Ctrl键时逐个单击，可不连续选择某几个工作表；选择单击第一个工作表，按Shift键单击最后一个工作表，可连续选择几个工作表。重命名：双

8、击或右击Sheet1使用快捷菜单中的“重命名”命令，将工作表名改为“成绩表”。删除工作表：选择Sheet4Sheet9，右击，使用快捷菜单中的“删除”命令将其删除。插入新工作表：选择工作表Sheet2，选择“插入”菜单中的“工作表”命令，插入新工作表Sheet1。复制、移动工作表：拖动Sheet1移动到Sheet3后，将Sheet1移动到最后。按Ctrl键拖动Sheet1到Sheet3之前，复制得到Sheet1(2)。以上操作是使用拖动的方法，也可使用剪贴板(使用“编辑”菜单中的“复制或移动工作表”命令)实现复制或移动。保存文件，关闭工作簿。3. 编辑修改“成绩表”工作表。1)插入一行、一列、

9、几行或几列：(1)打开文件夹“D:学生”下文件名为“计算机一班”的工作簿，单击“成绩表”工作表。(2)选择第一行，使用“插入”菜单中的“行”命令，即在此行上方插入一行。(3)选择“成绩表”工作表中的A1单元格，输入“计算机一班期末成绩”。选择一列，可在此列左面插入一列，方法同插入一行类似。选择几行或几列，可插入几行或几列。2)删除行或列：选择新插入的行或列，使用“编辑”菜单中的“删除”命令，将其删除。3)修改数据：打开文件夹“D:学生”下文件名为“计算机一班”的工作簿，单击“成绩表”工作表。选择“成绩表”工作表中的D3单元格，将“98”改为“89”，观察总分和平均分的变化。4)行或列的隐藏：隐

10、藏行或列的方法有三种：方法一：右击需要隐藏的行号(列标)，在弹出的快捷菜单中单击“隐藏”命令。方法二：选定需要隐藏的行或列，在“格式”菜单上，指向“行”或“列”，再单击“隐藏”命令。方法三：将指针指向要隐藏的行号下边界或列标的右边界使用鼠标向上或向左拖动。分别用上述三种方法隐藏“成绩表”工作表中的第3行、第9行和D列。显示被隐藏的行或列，与前面对应，也有三种方法。分别用对应的方法显示被隐藏的第3行、第9行和D列。5)锁定(冻结)行或列：选定“成绩表”工作表中第3行，选择“窗口”菜单中的“冻结窗格”命令将1、2行冻结。拖动垂直滚动条向下移，观察冻结的效果。选择“窗口”菜单中的“撤消窗口冻结”命令

11、将其撤消。6)插入、复制、清除批注：选定“成绩表”工作表中的D2单元格，在“插入”菜单中选择“批注”命令，在弹出的批注框中输入批注文本“学分：6分”。选定有批注的单元格D2，单击“常用”工具栏中的“复制”按钮；选定要粘贴批注的单元格F2，单击“编辑”菜单中的“选择性粘贴”命令；在弹出的“选择性粘贴”对话框中选择“批注”，单击“确定”按钮。分别用指向批注和用“视图”菜单中的“批注”命令来显示这两个单元格的批注内容，然后取消显示。选择要清除批注的单元格D2，单击“编辑”菜单中的“清除”选项，再选择其级联菜单中的“批注”命令。7)清除单元格数据：选择“成绩表”工作表中的F2单元格，使用“格式”工具栏

12、将其字号、字体分别设置为三号、隶书。然后选择“编辑”菜单中的“清除”命令；选择其级联菜单(包括“全部”、“格式”、“内容”、“批注”四项)中的“批注”，则只清除批注。选择“内容”，则只清除内容。然后再输入“英语”，观察格式的情况。选择“格式”，则只清除单元格中自定义的格式化信息，而采用缺省的格式化信息。选择“全部”，则清除单元格中的所有信息，包括内容、格式和批注；然后单击工具栏中的“撤消”按钮，撤消这一步。最后“成绩表”工作表如图3-4所示。实训二：格式化工作表一、实验目的掌握对工作表进行格式设置、排版、修饰的操作，使工作表美观悦目。二、实验内容1. 在硬盘D:区下“学生”的文件夹下建立一个文

13、件名为“消费调查表”的工作簿，在工作表Sheet1中创建一个如图3-5所示内容的工作表(输入数据和公式后，将数据全部设置为加粗)。图3-52. 将“消费调查表”工作簿中的工作表Sheet1的内容复制到Sheet2，并将Sheet2工作表格式化成如图3-6所示的工作表(其中，第一行的内容作为表格标题居中，并设置为隶书18号；将A2:F2单元格的底纹设置为宝石蓝；设置数据货币格式；设置数据对齐；设置数据区的单元格边框)。图3-6三、实验步骤1. 在硬盘D:区下“学生”的文件夹下建立一个文件名为“消费调查表”的工作簿，在工作表Sheet1中创建一个如图3-5所示内容的工作表(输入数据和公式后，将数据

14、全部设置为加粗)。启动Excel 2003，在工作表Sheet1中创建一个如图5所示内容的工作表：输入数据、公式(E3的公式为：=D3/C3，F3的公式为：=C3-D3)和平均函数后，将数据全部设置为加粗)。2. 将“消费调查表”工作簿中的工作表Sheet1的内容复制到Sheet2，并将Sheet2工作表格式化成如图3-6所示的工作表(其中，第一行的内容作为表格标题居中，并设置为隶书18号；将A2:F2单元格的底纹设置为宝石蓝；设置数据货币格式；设置数据对齐；设置数据区的单元格边框)。在Sheet1拖动选择A1:F16区域，单击工具栏中的“复制”按钮；单击选择Sheet2，再单击Sheet2中

15、的A1，单击工具栏中的“粘贴”按钮，将工作表Sheet1的内容复制到Sheet2，然后进行以下格式化操作：1)标题居中显示：单击A1，拖动选择A1:F1区域后单击“格式”工具栏中的“合并及居中”按钮，使标题相对于表格居中。2)设置标题为隶书字体、18号字号：选择标题，然后在“格式”工具栏中将字体设为隶书，字号设为18。3)设置数据区字体、字号：拖动选择A2:F16区域，选择“格式”菜单中的“单元格”命令(或者在右击弹出的快捷菜单中选择“设置单元格格式”命令)，弹出“单元格格式”对话框，选择“字体”选项卡，将此区域数据字体设置为楷体，字号为12号。4)调整行高、列宽：拖动选择A2:F16区域，将

16、鼠标指向“格式”菜单中的“行”子菜单，然后选择“行高”命令并输入所需的高度值“15”，最后单击“确定”按钮。拖动选择A2:F16区域，将鼠标指向“格式”菜单中的“列”子菜单，然后选择“列宽”命令并输入所需的宽度值“12”，最后单击“确定”按钮。此时，F列的数据显示不开(宽度不够)，双击列标右边的边界，使列宽适合单元格中的内容(即与单元格中内容的宽度一致)。拖动F列标右边界来设置更宽的列宽。5)设置底纹：拖动选择A2:F2区域，单击“格式”工具栏中的“填充色”按钮右侧的下拉三角箭头，选择“宝石蓝”颜色。撤消此步操作，选择“格式”菜单中的“单元格”命令(或者在右击弹出的快捷菜单中选择“设置单元格格

17、式”命令)，弹出“单元格格式”对话框，选择“图案”选项卡，选择“宝石蓝”颜色后单击“确定”按钮。6)设置数据货币格式：(1)拖动选择C3:D16区域，按Ctrl键再拖动选择F3:F16区域；(2)选择“格式”菜单中的“单元格”命令，出现“单元格格式”对话框，在“数字”选项卡中选择货币符号￥(人民币符号)，不保留小数，最后单击“确定”按钮。7)设置数据对齐：拖动选择A2:F2区域，单击“格式”工具栏中的“居中对齐”按钮；拖动选择A3:F15区域，单击“格式”工具栏中的“左对齐”按钮；拖动选择B16:F16区域，选择“格式”菜单中的“单元格”命令，弹出“单元格格式”对话框，在“对齐”选项卡中将水平

18、对齐方式设置为“靠右”。8)设置数据区的单元格边框：(1)拖动选择A2:F16区域；(2)单击“格式”工具栏中的“边框”按钮右侧的下拉箭头，选择。最后Sheet2中所有数据的格式如图3-6所示。实训三： 数据库管理功能一、实验目的1. 熟练掌握数据的排序操作。2. 掌握数据的筛选操作。3. 掌握对数据清单进行分类汇总的操作。二、实验内容1. 在硬盘D:区下“学生”的文件夹下建立一个文件名为“实验三”的工作簿，在工作表Sheet1中创建一个如图3-7所示内容的数据清单(输入数据和公式后，将数据全部设置为加粗)。然后把Sheet1的数据清单复制到Sheet2、Sheet3，使Sheet1、Shee

19、t2和Sheet3内容相同。图3-72. 对Sheet1的数据清单进行查看、添加、删除等操作。3. 对Sheet1的数据清单按“总销售额”进行排序。4. 对Sheet2的数据清单按“出版社”进行排序，然后按“出版社”分类汇总。5. 对Sheet3的数据清单进行“自动筛选”，并按“销售数量”自定义筛选方式：大于等于25。三、实验步骤1. 在硬盘D:区下“学生”的文件夹下建立一个文件名为“实验三”的工作簿，在工作表Sheet1中创建一个如图7所示内容的数据清单(输入数据和公式后，将数据全部设置为加粗)。然后把Sheet1的数据清单复制到Sheet2、Sheet3，使Sheet1、Sheet2和Sh

20、eet3内容相同。在硬盘D:区下“学生”的文件夹下建立一个文件名为“实验三”的工作簿，在工作表Sheet1中创建一个如图7所示内容的数据清单：输入数据和公式(单元格E3的公式为=C3*D3，其他公式复制填充算出；并将数据全部设置为加粗)，然后把Sheet1的数据清单复制到Sheet2、Sheet3：在Sheet1中选定A1:E12区域，单击工具栏中的“复制”按钮；切换到Sheet2，单击选中A1，再单击工具栏中的“粘贴”按钮；切换到Sheet3，单击选中A1，再单击工具栏中的“粘贴”按钮。2. 对Sheet1的数据清单进行查看、添加、删除等操作。(1)单击工作表Sheet1，选择A2:E12区

21、域，或单击选中A2:E12的任一单元格。(2)选择“数据”菜单中的“记录单”命令，出现“Sheet1”对话框。(3)单击“Sheet1”对话框中的“下一条”、“上一条”，查看数据清单中的每一条记录；拖动垂直滚动条，查看数据清单中的每一条记录。(4)单击“Sheet1”对话框中的“新建”按钮，输入“石油大学”(出版社)、“VFP”(图书系列)、“66”(销售数量)、“26”(销售单价)，单击“关闭”按钮，即添加一条新记录。回到数据清单中观察记录的添加情况，并注意新记录的“总销售额”，体会“公式是Excel 2003的灵魂”。(5)打开“Sheet1”对话框，选择一条记录，单击“Sheet1”对话

22、框中的“删除”按钮，将其删除。3. 对Sheet1的数据清单按“总销售额”进行排序。方法一：单击选择工作表Sheet1，然后选择数据清单中E列的任一单元格，最后单击“常用”工具栏上的“升序”按钮或“降序”按钮，对Sheet1的数据清单按“总销售额”进行排序。方法二：单击Sheet1数据清单中E列的任意一个单元格，选择“数据”菜单中的“排序”命令，出现“排序”对话框，在“主要关键字”下拉列表中选择所选列的字段名，并且在它的右侧选中“递减”或“递增”，单击“确定”按钮。4. 对Sheet2的数据清单按“出版社”进行排序，然后按“出版社”分类汇总。(1)单击选择工作表Sheet2，然后选择数据清单中

23、A列的任一单元格，最后单击“常用”工具栏上的“升序”按钮，对Sheet2的数据清单按“出版社”进行排序。(2)选择“数据”菜单中的“分类汇总”命令，弹出“分类汇总”对话框。(3)在“分类字段”下拉列表中选择“出版社”，这是要分类汇总的字段名；在“汇总方式”下拉列表中选择“求和”；在“选定汇总项”下面的列表中选中“总销售额”复选框；因为结果要显示在数据列表的下面，所以选中“汇总结果显示在数据下方”复选框。(4)定义完毕，单击“确定”按钮得到如图3-8所示的结果。图3-85. 对Sheet3的数据清单进行“自动筛选”，并按“销售数量”自定义筛选方式：大于等于25。(1)单击Sheet3数据清单中任

24、一单元格，在“数据”菜单中，指向“筛选”，选择“自动筛选”命令。观察数据清单的变化。(2)单击数据清单中A2的下拉箭头，选择“高教”，即按“高教”筛选；再选择“(全部)”。(3)单击数据清单中C2的下拉箭头，选择“(自定义)”，弹出“自定义自动筛选方式”对话框，选择“大于或等于”，输入25，单击“确定”按钮。筛选出销售数量大于等于25的记录。实训四：文档编排与打印一、实验目的掌握页面设置、插入分页符、打印预览及工作表打印。二、实验内容1. 将实验二中“D:学生消费调查表.xls”中的Sheet2工作表以如图3-9所示的格式打印预览出来。图3-9三、实验步骤1. 将实验二中“D:学生消费调查表.

25、xls”中的Sheet2工作表以如图3-9所示的格式打印预览出来。1) 设置页面：打开D:学生消费调查表.xls，单击Sheet2工作表，选择“文件”菜单中的“页面设置”命令，弹出如图3-10所示的对话框，进行以下设置：(1)在“方向”中选择“纵向”单选按钮；(2)在“缩放”中选择“缩放比例”单选按钮，在其右面的文本框中选择100%正常尺寸；(3)在“纸张大小”右边的下拉列表框中选择A4纸。图3-102) 设置页边距：在“页面设置”对话框中，单击“页边距”标签，进行以下设置：(1)上、下边距设置为2.4厘米，左、右边距设置为1.8厘米；(2)页眉、页脚设置为1.3厘米；(3)在“居中方式”选项

26、区选择“水平居中”复选框。3) 设置页眉、页脚：在“页面设置”对话框中，单击“页眉/页脚”标签，进行以下设置：(1)单击“自定义页眉”，出现“页眉”对话框；(2)在“左”文本框中输入“共页”，将光标移到“共”“页”中间，单击“页眉”对话框中的“总页数”按钮，按回车换行，输入“制表人：刘允涛”；(3)将光标移到“中”文本框，单击“页眉”对话框中“工作表标签名”按钮；(4)将光标移到“右”文本框，单击“页眉”对话框中“页码”按钮，输入“页”，按回车换行，单击“页眉”对话框中“日期”按钮，如图3-11所示；图3-11(5)单击“确定”按钮，返回到“页面设置”对话框。4) 设置工作表：在“页面设置”对

27、话框中，单击“工作表”标签，进行以下设置：(1)选择“打印区域”：A2:F16；(2)选择“打印标题”区中的“顶端标题行”：$1:$1；(3)选择“打印顺序”中的单选按钮“先行后列”；(4)单击“确定”。5) 打印预览：(1)回到Sheet2工作表，选择“文件”菜单中的“打印预览”命令，观察是否与图3-9一致； (2)练习使用放大镜及上下页、缩放、分页预览等按钮。实训五： 透视表及数据透视图一、实验目的主要介绍创建和编辑数据透视表和数据透视图，数据透视表格式的设置，数据透视表和透视图的相互关系。二、实验内容1.创建数据透视表2.编辑数据透视表3.建立了行列交叉列表，并可以通过行列转换以查看源数

28、据的不同统计结果。 基本数据表如图3-12所示。图3-12 基本数据表三、实验步骤以图3-12数据源为例，建立一个数据透视表，按学生的籍贯和学科分类统计出英语和数学的平均成绩。基本步骤：1.在数据库区域内选定任意一个单元格，选菜单【数据】【数据透视表和数据透视图】，弹出“数据透视表和数据透视图向导-3步骤之1”，如图3-13。在图中，指定数据源的类型为“Microsoft Excel数据列表或数据库”，创建的报表类型为“数据透视表”，按【下一步】按钮。图3-13 数据透视表和数据透视图向导-3步骤之12.在跟着弹出“数据透视表和数据透视图向导-3步骤之2”对话框(图3-14)中，选定要建立数据

29、透视表的数据区域。一般来说，Excel会自动选择光标所在的数据库区域。按按【下一步】按钮。图3-14 数据透视表和数据透视图向导-3步骤之23.在跟着弹出“数据透视表和数据透视图向导-3步骤之3”对话框(图3-15)中，按【布局】按钮，弹出如图3-16的“布局”对话框。图3-15 数据透视表和数据透视图向导-3步骤之34.在“布局”对话框中，右边是一组字段按钮，每一个都对应着数据库中的一个字段名。通过将字段按钮拖进四个布局区域(页、行、列、数据)中的任何一区来设计数据透视表的布局。在本例中，将“籍贯”拖到“行”，“学科”拖到“列”，“英语”和“数学”拖到“数据”区。这时，“数据”区中就有两个按

30、钮【求和项：英语】和【求和项：数学】。图3-16 数据透视表和数据透视图向导之布局5.分别双击这两个按钮，在弹出的“数据透视表字段”对话框(图3-17)中，设置“汇总方式”为“平均值”。按【确定】按钮返回“布局”对话框，在“布局”对话框中再按【确定】按钮返回图3-15。图3-17 数据透视表字段图3-18一个生成的数据透视表6.在图3-15中，选择在“新建工作表”中显示数据透视表，按【完成】按钮，即可在一个新的工作表中创建一个数据透视表，结果见图3-18。在该数据透视表中，可以任意地拖动交换行、列字段，数据区中的数据会自动随着变化。通过“数据透视表”工具栏上的“图表向导”按钮，自动在新的工作表

31、中生成数据透视图。“数据透视表“工具栏，可通过选菜单【视图】【工具栏】【数据透视表】来显示。数据透视表生成后，还可以方便地对它进行修改和调整。实训六：数据图表化一、实验目的1熟悉图表的创建过程；2掌握图表的编辑；3掌握图表的格式化。二、预备知识图表是Excel最常用的对象之一，它是依据选定的工作表单元格区域内的数据按照一定的数据系列而生成的，是工作表数据的图形表示方法。图表能形象地反映出数据的对比关系及趋势，可以将抽象的数据形象化。在Excel中可以生成柱形图、条形图、折线图、饼形图等。图表的基本组成部分包括：图表区、绘图区、图表标题、数据分类、数据标记、坐标轴等。三、实验内容1对每门课程的成绩进行分段统计，统计结果如表3-1所示。表3-1：成绩分段统计结果4. 以“分段统计”结果为数据源创建如下的图3-19：图3-19 成绩分段统计图图3-19中的标题设为隶书、倾斜、18号字、双下划线。横坐标标题和纵坐标标题