案例实践五：excel(合并版)

《案例实践五：excel(合并版)》由会员分享，可在线阅读，更多相关《案例实践五：excel(合并版)（25页珍藏版）》请在金锄头文库上搜索。

1、电子报表处理软件 Excel主要内容J工作薄、工作表和单元格 J数据的输入 J设置数据有效性与数据填充自定义序列 J区域选择、单元格格式、条件格式和自动套 用格式 J公式与函数以及单元格引用 J数据表的排序、筛选与汇总 J图表的建立案例描述一、Excel 2003 工作表的建立与编辑任务1：新建一个工作簿文件，并将其以“学生成绩管理.xls”为文件 名保存到“D:”，在该文件中新建一张名为“学生信息”的工作表，参照效 果图，输入各行数据。其中：“学生姓名”可以通过建立自定义序列（工具-选项-自定义 序列）方便自动填充输入；“专业”通过设置数据有效性（利用“基础数 据”表中的数据源将专业定义为名

2、称，插入-名称-定义；需要输入专业的列设置“数据有效性”，数据-有效性），通过下拉列表进行选择填入。任务2：在“学生信息”表列标题所在行前插入标题，内容为“学生个人 信息表”，为姓名为“陈一鸣”的同学在姓名单元格上添加一批注，其内容 为“学生会副主席”。 案例描述一、Excel 2003 工作表的建立与编辑（续）任务3：新建学生各门课程的成绩表（课程不少于5门），参照效果图输入5次平时成绩、期中以及期末成绩。任务4：将“学生信息”表中的“学号”、“姓名”2列数据（ 包括列标题）复制到sheet8中，将sheet8命名为“成绩汇 总表”。任务5：为“成绩汇总表”建立一个备份，放在最后，将其命名为

3、“成绩表备份”。 预备知识J Excel 2003 的启动和退出 J Excel 2003 窗口基本组成（软件环境介绍） Excel 2003 窗口组成 工作簿 工作表 单元格 J 不同数据类型的数据输入说明 数值数据及其输入 文本数据及其输入 日期、时间数据及其输入 J 工作表与单元格的选择 J 设置数据有效性与数据填充 J 查找与替换 J 操作的撤销与恢复任务完成任务1l 步骤1：启动Excel 2003后，系统自动新建一个工作簿文件，默认名称为 “Book1”，若原来Excel已启动，可单击主窗口上“文件”菜单中的“新建”命令。 （若该“学生成绩管理”文件已经存在，选择“打开”菜单打开该

4、文件。案例中为方便 后续操作，已新建该文件，并已有“基础数据”工作表。l 步骤2：选择工作表Sheet1右击，在弹出的快捷菜单中选择“重命名”，或双击 工作表标签，此时直接在光标显示位置上输入“学生信息”。l 步骤3：选择“基础数据”表，选定G2:G7数据区域，单击“插入”名称” 定义”，在弹出的“定义名称”对话框中输入名称“专业”，再单击“确定”按钮。l 步骤4：“学生信息”工作表：“专业”列，单击菜单“数据”有效性”，在弹出 的“数据有效性”对话框中“设置”，设置允许“序列”，来源“=专业”，单击“确定” 按钮。此时选中专业列各单元格，右边出现下拉小箭头，单击下拉选择专业进行输 入。任务完

5、成任务1（续）l 步骤5：选择“基础数据”表，选定C2:C31数据区域，单击“工具”- “选项”-“自定义序列”，建立填充柄数据。l 步骤6： “学生信息”工作表：“姓名”列，输入第一个学生的姓名后 ，其他姓名可以通过自动填充输入。其他各列对照效果图在“学生信息” 工作表中正确输入相应的数据。 l 步骤7：单击主窗口上“文件”菜单下的“保存”命令，或单击“常用”工 具栏上的“保存”按钮，此时系统弹出“另存为”对话框，在该对话框中选 择D盘，输入正确的文件名“学生成绩管理”。任务完成任务2l 步骤1：选定第一行或光标移至第一行中，利用“插入”菜单下的“行” 命令插入一空行；或直接在该行上右击，从

6、其快捷菜单中选择“插入”命 令，打开“插入”对话框，在对话框中选定“整行”后单击“确定”按钮，系 统将插入一空行。l 步骤2：在插入的行中的第一个单元格（即A1单元格）中输入标题文 本“学生个人信息表”。l 步骤3：单击“编辑”菜单中的“查找”命令，打开“查找和替换”对话框 ，在该对话框的“查找”选项卡中”查找内容”输入为“陈一鸣”，单击“查 找下一个”按钮并单击“关闭”按钮。l 步骤4：单击“插入”菜单中的“批注”命令，在弹出的文本框中输入文 本“学生会副主席”，然后单击批注框外任意一个单元格。任务完成任务3l 步骤1：右键单击最后一张工作标签，在弹出的快捷菜单中选择“插入”命令 ，在弹出的

7、“插入”对话框中选择“插入工作表”，单击“确定”按钮完成插入。右 键单击刚插入的工作标签，在弹出的快捷菜单中选择“重命名”命令，将其重命名 为“高等数学成绩”。l 步骤2：选中“学生信息”表中的A2:B32单元格区域，利用“编辑”菜单下的 “复制”命令；或单击常用工具栏中的“复制”按钮（或Ctrl+C） 。选中“高等数 学成绩”表，光标移到A1单元格，选择“编辑”菜单下的“粘贴”命令；或单击常 用工具栏中的“粘贴”按钮（或Ctrl+V） 。l 步骤3：对照效果图，在“高等数学成绩”表中相应位置插入列，并输入相关 内容。l 步骤4：依次重复步骤1、步骤2和步骤3，完成另4门课程的成绩表。任务完成

8、任务4l步骤：重复任务3中的步骤1和步骤2，创建“成绩汇总表”。任务完成任务5l 步骤1：选择“成绩汇总表”（工作表标签），右键弹出快捷菜单，选择“移动或复制工作表”命令，在弹出的“移动或复制工作表”对话框中选择位置，本任务中“移至最后”，同时选择“建立副本”，最后单击“确定”按钮。l 步骤2：右键单击“成绩汇总表（2）”工作表标签，选择“重命名”命令，将其重命名为“成绩表备份”。案例描述二、Excel 数据计算（参考效果图）任务1：在各“*成绩表”中计算“平时成绩”、计算各位同学的总评， 其计算规则为：平时成绩15%、期中成绩15%、期末成绩70%。并按 总评对其进行单科排名，总分和名次放入

9、所对应的单元格。任务2：在每个课程成绩工作表中，计算课程各分数段分布人数及比 例、平均分、 标准方差。计算平均分和标准方差时，小数点后保留2位小 数。任务3：参考效果图，从各课程成绩表中引用总评成绩，并填入“成绩 汇总表”中各相应单元格。计算单科平均分、各科总分。任务4：参考效果图，在“成绩汇总表”中，按总分进行排名，并利用 函数判断各科是否超过单科平均分。预备知识J Excel中的计算公式公式的使用 运算符 单元格引用（绝对引用、相对引用、混合引用） 出错信息 J 函数函数基本知识 常用函数（SUM、AVERAGE、MAX/MIN 、COUNT） 选择性粘贴数据任务完成任务1l 步骤1：选择

10、“*成绩表”，用AVERAGE公式求得平时成绩填入H列 相应单元格，其余同学的平时成绩可利用填充柄来完成计算。选择H3单 元格，将鼠标指针移动至该单元格右下角位置，出现黑色十字形时拖动至 相应需要填充的最后一个单元格。l 步骤2：选中单元格K3，在编辑栏内输入“=ROUND（ H3*15%+I3*15%+J3*70%）,2”。按“回车”键确认，求得总 评成绩（四舍五入，保留整数），用填充柄向下填充，完成所有学生 总评成绩的输入。l 步骤3：选中单元格L3，输入“=RANK(K3,$K$3:$K$32,0)”， 回车确认，求得的排名依次填入相应单元格。任务完成任务2l 步骤1：在每个课程成绩工作

11、表中，在学生成绩列表下方建立各成绩 段分布人数及比例平均分和标准方差的相应单元格。l 步骤2：利用SUMPRODUCT函数计算各个分数段分布人数，比如 “=SUMPRODUCT(K3:K32=90)*(K3:K32=$E$33,F3=$F$33,G3=$G$33,H3=$ H$33,I3=$I$33),“是“,“否“)， IF函数是判断条件（第一个参数 ），条件为真返回“是”，为假返回“否”。以此求得单科是否超过平均分 。用填充柄向下填充，完成对所有同学的判断。案例描述三、工作表格式化任务1：设置“学生信息”表标题格式为隶书、22号字， 并要求合并及居中。任务2：将“成绩汇总表”中的“单科平均

12、分”保留两位小数 ，并将各列以“最合适列宽”显示。任务3：将“成绩汇总表”中各科成绩低于60分的学生成 绩用红色的字体。黄色的底纹显示。任务4：为“学生信息”表中数据所在区域加田字框：粗线外框，细线内框。 预备知识J 工作表的格式化数字的格式化字体的设置设置列宽与行高数据对齐方式设置设置工作表的边框 与底纹自动套用格式设置条件格式的设置任务完成任务1l 步骤1：选定“学生信息”表。l 步骤2：选定A1：G1单元格区域，利用“格式”菜单下的“单元格”命令，或直接右击选中的单元格区域，在弹出的快捷菜单中选择“设置单元格格式”，弹出“单元格格式”对话框。l 步骤3：在“单元格格式”对话框中，单击“字

13、体”选项卡，设置相应的字体为隶书，字号为22。l 步骤4：单击“对齐”选项卡，水平对齐方式选择“居中”，并选中其中的“合并单元格”复选框，最后单击“确定”按钮。任务完成任务2l 步骤1：选定“成绩汇总表”。l 步骤2：选定C33：G33单元格区域，单击“格式”菜单下的“单元格”命令，或直接右击选中的单元格区域，在弹出的快捷菜单中选择“设置单元格格式”，弹出“单元格格式”对话框。l 步骤3：在“单元格格式”对话框中，单击“数字”选项卡，在“分类”列表框中选中“数值”，设置小数位数为2，单击“确定”按钮。l 步骤4：选定整张“成绩汇总表”中的数据区域，单击“格式”菜单下的“列”“最合适的列宽”命令

14、，即可完成最合适的列宽的设置。任务完成任务3l 步骤1：选定“成绩汇总表”。l 步骤2：选定C3：G32单元格区域，单击“格式”菜单下的“条件格式”命令，弹出“条件格式对话框”。l 步骤3：在“条件格式”对话框中的“条件1”栏中的3个组合框中分别选择或输入“单元格数值”、“小于”、“60” 。l 步骤4：单击“条件格式”对话框中的“格式”按钮，在弹出的“单元格格式”对话框的“字体”选项卡中选择红色字体，在“图案”选项卡中选择黄色底纹，最后单击“确定”按钮。任务完成任务4l 步骤1：选定“学生信息”表。l 步骤2：选定数据区域A1：G32，单击“格式”菜单下的“单元格”命令，或直接右击“设置单元

15、格格式”命令，弹出“单元格格式”对话框。l 步骤3：在“单元格格式”对话框中选择“边框”选项卡，在该选项卡中先选择粗线的线条样式，再单击“预置”栏内的“外边框”按钮；同样再选择细线的线条样式，单击“预置”栏内的“内部”按钮，最后单击“确定”按钮。作业要求1、虚拟一些学生的信息（不少于20人），新建学生 信息工作表，包括：学号、姓名、性别、出生年月 、年龄、专业、联系方式； 2、新建学生各个课程的成绩工作表（课程不少于5 门），每个表包括：平时成绩（5次）、平时成绩总 评、期中、期末、期末总评； 3、输入数据的有效性（成绩采用百分制，符合常规 要求）； 4、平时成绩总评(平时成绩平均值)、期末总评(平 时成绩总评*15%+期中成绩*15%+期末成绩*70%)根据 公式计算得分； 作业要求5、在每个课程成绩工作表中，计算课程各分数段分 布人数及比例、平均分、标准方差；计算平均分和 标准方差时，小数点后保留2位小数； 6、最后生成学生成绩汇总表，包括每个学生的各科 成绩期末总评、总分、排名，并对每个学生判断“ 各科成绩是否均超过平均分”； 7、新建专业分析汇总表，对各个专业的学生进行分 类汇总，要求分专业计算各科成绩平均分，同时建 立图表（柱状图）来 显示各专业课程平均分比较情 况。