Excel电子表格制作

实验3 Excel电子表格制作【实验目的】1. 掌握Excel文件的建立、保存与打开。2. 掌握工作表的选择、添加、删除、重命名、复制与移动。3. 掌握单元格的输入、编辑、删除、修改、插入、复制与移动。4. 掌握工作表的修饰、公式与函数的应用。5. 掌握文本的版面格式设计。6. 掌握工作表的高级应用实验3.1 工作表的基本操作【实验内容与要求】1. 创建如图3.1内容的工作表文件。图3.1 工作簿文件内容2. 在A1单元格输入标题“部分城市降水量”，在A2单元格输入“城市”。3.在B2单元格输入“第一季”后，拖动填充柄填充C2F2中的内容。其余单元格按所给内容输入。4. 将A1G1合并及居中，表格自动套用格式为“彩色2”，为数据清单加“粗外边框、细内边框”。5. 标题字体格式设置为：楷体_GB2312、14磅、加粗、浅青绿色。6. 工作表sheet1重命名为“降水量表”。7. 利用函数计算北京市的年总降水量和季平均降水量。（均保留小数点后两位小数）；拖动填充柄填充F4F7和G4G7单元格中的内容。8. 按“年总量”列数值降序排序。9. 第二行表头区设文字水平居中，字体为粗体。10. B3E7数据设条件格式，数值65的设为红色、粗体。拖动方向图3.2 填充柄【实验步骤】1. 数据填充。用鼠标指针指向填充柄，光标变为细十字，拖动鼠标即可，如图3.2所示。 2. 合并及居中。鼠标拖过A1G1，然后按格式工具栏上的“合并及居中”按钮（如图3.3所示）；或者执行“格式/单元格”对齐选项卡命令，按图3.4设置。3. 表格自动套用格式。执行“格式/自动套用格式”命令，在图3.6对话框中单击“彩色2”，按确定按钮。图3.3合并及居中操作图3.4 合并及居中设置图3.5 字体格式设置4. 标题格式设置。在图3.5中字体 选项卡中进行各种选择设置。5. 计算年总量。单击F3单元格，执行“插入/函数”命令，在图3.7插入函数对话框中单击SUM求和函数，确定后，用鼠标拖选计算范围，如图3.7所示，回车或按编辑栏上绿色的对号，完成F3单元格的计算。向下拖动填充柄直至F7单元格。图3.6 表格“自动套用格式”图3.7 插入函数注：常用函数可以用“常用”工具栏上的按钮及旁边的小箭头调出，也可以单击编辑栏左侧的fx按钮调出“插入函数”对话框。图3.8 求和函数的用法6.计算季均量。单击G3单元格，仿照年总量的计算方法，此次函数要选AVERAGE。7.工作表中数据区加表格线。用鼠标选取A1G7区域，执行“格式/单元格”命令调出如图3.10边框 选项卡；在“线条样式”区单击粗线，在“预置”区单击“外边框”；在“线条样式”区单击细线，在“预置”区单击“内部”。8.排序。单击数据区中任一单元格，执行“数据/排序”命令，按图3.11 排序对话框设置，主要关键字选“年总量”，降序。图3.9 求平均值函数的用法图3.10 数据区加边框图3.11 排序对话框图3.12 最终完成结果图实验3.2 公式和函数的灵活运用【实验内容与要求】完成下表计算内容。图3.13 计算用表及完成结果1. 用函数计算成绩列的结果，四个科目的平均分数60为通过，否则为不通过。2. 用函数统计不及格的人数。3. 用函数计算及格率，及格率=及格的人数/总人数。4. 用百分比表示及格率，保留小数1位。5. 工作表标签重命名为“数据1”。【实验步骤】 建立图3.13成绩表，其中F3F9、A10E11区域空白，需计算得出。图3.14 IF函数的参数 成绩列的计算。根据所给条件：如果“四个科目的平均分数60为通过，否则为不通过”，选择函数IF，计算公式为：“=IF(AVERAGE(B3:E3)<60,"未通过","通过")”。操作方法：单击F3单元格，直接输入公式即可，或者采用如下步骤。 单击F3单元格；， 执行“插入/函数”命令，从图3.7“插入函数”对话框中选择IF函数；IF函数有3项参数：一是判断条件，二是条件为真时的取值，三是条件为假时的取值， 依次按图所图3.14所示填写； 在Logical_test一栏中，光标插入<60之前，再按编辑栏×号左侧的箭头，选择AVERAGE函数，如图3.15；图3.15 IF函数嵌套AVERAGE函数的操作方法 AVERAGE函数参数选择可以输入或用鼠标拖选，如图3.16，按确定；图3.16 AVERAGE函数的参数 拖动F3单元格的填充柄向下至F9，成绩计算完毕。 统计不及格的人数。此为条件统计，需要使用统计类函数中的COUNTIF函数。计算公式为“=COUNTIF(B3:B9,"<60")”。操作方法： 单击B10单元格；， 执行“插入/函数”命令，从图3.7“插入函数”对话框中选择COUNTIF函数； 按下图填写参数，按确定按钮。图3.17 COUNTIF函数的参数 计算及格率。及格率=及格的人数/总人数，需要使用函数COUNTIF统计及格总人数和函数COUNT统计总人数。计算公式为“=COUNTIF(B3:B9,">=60")/COUNT(B3:B9)”。操作方法： 单击B11单元格； 执行“插入/函数”命令，从图3.7“插入函数”对话框中选择COUNTIF函数； 仿照图3.17示例，第二行参数改为“>=60”，按确定按钮完成公式第一部分； 在编辑栏公式后输入“/”号，再从左侧函数列表中选COUNT函数；图3.18 选择COUNT函数 填写参数如下图，或用鼠标拖选要计算的区域，按确定完成整个计算。图3.19 COUNT函数的参数 百分比样式。执行“格式/单元格”命令，在数字选项卡中设置。也可以按格式工具栏上的“%”百分比样式按钮和增加 | 减少小数位数按钮进行处理。图3.20 设定百分比格式 拖动B11单元格的填充柄向右至E11，及格率计算完毕。 工作表改名。执行“格式/工作表/重命名”，键入新的名称即可。更简单的方法是双击工作表标签，直接更名。最终完成效果如图3.13。实验3.3 绝对引用和混合引用【实验内容与要求】完成下表的计算，学习绝对引用的概念和方法。前面已经熟悉了对单元格地址的相对引用方式，可以非常方便地完成大多数自动填充的计算任务，不过有时需要从固定的位置取数据，就需要用到绝对引用。任务：利用公式计算出各类项目的百分比，百分比=差额/各项目总差额。（格式为百分比样式，保留小数点后两位小数）。图3.21 计算用表【实验步骤】第一种方法：单击E10单元格，用SUM函数计算总差额；单击F10单元，输入“=”，单击E3，输入“/”，单击E10，若此时结束输入，图3.22 输入公式图3.23 向下拖动填充柄出现相对引用错误（除数为零）F3单元格可以得到正确结果，但用填充柄向下拖动则回出现“#DIV/0!”错误，这是因为填充公式引用了E10以下单元格的地址，产生了被0除的错误。我们希望填充柄向下拖动时对E10单元格的引用能够固定，解决的方法是将公式改写为“=E3/$E$10”，$E$10这种写法为绝对应用。在公式中相对引用的单元格地址随填充柄的拖动而改变，而绝对引用的地址则固定不变，从而得到正确的结果。图3.24 绝对引用获得正确结果第二种方法：单击F10单元格，输入“=”；单击E3，在编辑栏公式后输入“/”号，再从左侧函数列表中选SUM函数；图3.25 调用SUM函数按确定按钮后，把编辑栏内公式改为“=E3/SUM($E$3:$E$9)”；图3.26 SUM函数参数采用绝对引用 单击格式工具栏上的“%”百分比样式按钮，数值变成百分比样式。 向下拖动填充柄直至F9单元格，完成计算。注意，因为向下拖动填充柄时列不发生变化，所以SUM的计算区域也可以写成SUM(E$3:E$9)这种混合引用的模式。图3.27 SUM函数参数采用混合引用实验3.4 图表制作【实验内容与要求】按图3.28表格内容制作成绩分布图和平均成绩分布图，图表样式为簇状柱型图。图3.28 成绩分布图【实验步骤】1成绩分布图。选取数据区域B2:E9，执行“插入/图表”命令，按图表向导指引逐步完成图表的制作。完成效果见图3.28成绩分布图。2平均成绩分布图制作方法： 用鼠标选取B2:E2区域，按住Ctrl再鼠标选取B10:E10区域；图3.29 选取数据区域 执行“插入/图表”命令，如图3.30选取簇状柱型图，按下一步按钮； 选择系列产生在“行”，如图3.31，按下一步按钮； 如图3.32填写标题信息，按下一步按钮； 图3.33数据标志显示为“值”，按下一步按钮； 在“图表向导- 4 步骤之 4 图表位置”对话框中选“作为其中的对象插入”； 按完成按钮，得到图3.34平均成绩分布图。用鼠标拖拽坐标轴文字“科目”和“分数”到相应位置； Y轴坐标文字“分数”需要顺时针旋转90度，在文字“分数”上右击鼠标，在菜单中选“图表标题格式”，出现对话框，选“对齐选项卡”，调整“方向”为0度。图3.30 簇状柱型图图3.31 系列产生在行图3.32 填写标题信息图3.33 数据标志显示为“值”图3.34 平均成绩分布图实验3.5 数据的自动筛选和高级筛选【实验内容与要求】图3.35 自动筛选1按图3.37所给样表，用自动筛选方法查找出计算机优秀和不及格的学生的记录。2用高级筛选方法查找出计算机优秀而物理不及格的记录。【实验步骤】1自动筛选。 执行“数据/筛选/自动筛选”，在列标题旁出现小箭头，单击“计算机”字段旁的小箭头，在出现的下拉列表中选“自定义”； 按图3.36填写筛选条件，按确定按钮，即可获得结果。图3.36 填写筛选条件2高级筛选图3.37 学生成绩原始数据表 先在工作表空白处建立筛选条件区，如图3.38，条件为“与”的关系；图3.38 高级筛选的筛选条件区，条件“与”图3.39 确定高级筛选的3个区域 光标放到数据清单区中任一格； 执行“数据/筛选/高级筛选”，如图3.39选择“条件区域”和“复制到”的位置；（注：因为事先并不清楚筛选出来的记录有多少，所以复制到的位置并不需要用鼠标精确框选，只需要确定放置位置的左上角单元格就可以了。） 按确定按钮完成。高级筛选可以完成比较复杂的或用自动筛选无法完成的筛选任务。图3.40 高级筛选完成结果（计算机优秀而物理不及格）实验3.6 分类汇总【实验内容与要求】根据图3.37实验数据样表，分别求出各班学生各个科目的平均成绩。本实验需要采用分类汇总的方法完成。分类汇总前需要先按分类字段排序，然后才能进行数据的处理。操作步骤如下：【实验步骤】 光标放置于欲排序列中任一单元格，执行“数据/排序”，选择排序字段；图3.41 分类汇总前先按分类字段排序 排序后，再执行“数据/分类汇总”，在对话框中选择分类字段、汇总方式和汇总项，按确定按钮。