案例实践五：Excel(合并版).ppt

电子报表处理软件Excel,主要内容,工作薄、工作表和单元格 数据的输入 设置数据有效性与数据填充自定义序列 区域选择、单元格格式、条件格式和自动套用格式 公式与函数以及单元格引用 数据表的排序、筛选与汇总 图表的建立,案例描述,一、Excel 2003 工作表的建立与编辑 任务1：新建一个工作簿文件，并将其以“学生成绩管理.xls”为文件名保存到“D:”，在该文件中新建一张名为“学生信息”的工作表，参照效果图，输入各行数据。 其中：“学生姓名”可以通过建立自定义序列（工具-选项-自定义序列）方便自动填充输入；“专业”通过设置数据有效性（利用“基础数据”表中的数据源将专业定义为名称，插入-名称-定义；需要输入专业的列设置“数据有效性”，数据-有效性），通过下拉列表进行选择填入。 任务2：在“学生信息”表列标题所在行前插入标题，内容为“学生个人信息表”，为姓名为“陈一鸣”的同学在姓名单元格上添加一批注，其内容为“学生会副主席”。,案例描述,一、Excel 2003 工作表的建立与编辑（续） 任务3：新建学生各门课程的成绩表（课程不少于5门），参照效果图输入5次平时成绩、期中以及期末成绩。 任务4：将“学生信息”表中的“学号”、“姓名”2列数据（包括列标题）复制到sheet8中，将sheet8命名为“成绩汇总表”。 任务5：为“成绩汇总表”建立一个备份，放在最后，将其命名为“成绩表备份”。,预备知识,Excel 2003 的启动和退出 Excel 2003 窗口基本组成（软件环境介绍） Excel 2003 窗口组成 工作簿 工作表 单元格 不同数据类型的数据输入说明 数值数据及其输入 文本数据及其输入 日期、时间数据及其输入 工作表与单元格的选择 设置数据有效性与数据填充 查找与替换 操作的撤销与恢复,任务完成,任务1 步骤1：启动Excel 2003后，系统自动新建一个工作簿文件，默认名称为“Book1”，若原来Excel已启动，可单击主窗口上“文件”菜单中的“新建”命令。 （若该“学生成绩管理”文件已经存在，选择“打开”菜单打开该文件。案例中为方便后续操作，已新建该文件，并已有“基础数据”工作表。 步骤2：选择工作表Sheet1右击，在弹出的快捷菜单中选择“重命名”，或双击工作表标签，此时直接在光标显示位置上输入“学生信息”。 步骤3：选择“基础数据”表，选定G2:G7数据区域，单击“插入”名称”定义”，在弹出的“定义名称”对话框中输入名称“专业”，再单击“确定”按钮。 步骤4：“学生信息”工作表：“专业”列，单击菜单“数据”有效性”，在弹出的“数据有效性”对话框中“设置”，设置允许“序列”，来源“=专业”，单击“确定”按钮。此时选中专业列各单元格，右边出现下拉小箭头，单击下拉选择专业进行输入。,任务完成,任务1（续） 步骤5：选择“基础数据”表，选定C2:C31数据区域，单击“工具”-“选项”-“自定义序列”，建立填充柄数据。 步骤6： “学生信息”工作表：“姓名”列，输入第一个学生的姓名后，其他姓名可以通过自动填充输入。其他各列对照效果图在“学生信息”工作表中正确输入相应的数据。 步骤7：单击主窗口上“文件”菜单下的“保存”命令，或单击“常用”工具栏上的“保存”按钮，此时系统弹出“另存为”对话框，在该对话框中选择D盘，输入正确的文件名“学生成绩管理”。,任务完成,任务2 步骤1：选定第一行或光标移至第一行中，利用“插入”菜单下的“行”命令插入一空行；或直接在该行上右击，从其快捷菜单中选择“插入”命令，打开“插入”对话框，在对话框中选定“整行”后单击“确定”按钮，系统将插入一空行。 步骤2：在插入的行中的第一个单元格（即A1单元格）中输入标题文本“学生个人信息表”。 步骤3：单击“编辑”菜单中的“查找”命令，打开“查找和替换”对话框，在该对话框的“查找”选项卡中”查找内容”输入为“陈一鸣”，单击“查找下一个”按钮并单击“关闭”按钮。 步骤4：单击“插入”菜单中的“批注”命令，在弹出的文本框中输入文本“学生会副主席”，然后单击批注框外任意一个单元格。,任务完成,任务3 步骤1：右键单击最后一张工作标签，在弹出的快捷菜单中选择“插入”命令，在弹出的“插入”对话框中选择“插入工作表”，单击“确定”按钮完成插入。右键单击刚插入的工作标签，在弹出的快捷菜单中选择“重命名”命令，将其重命名为“高等数学成绩”。 步骤2：选中“学生信息”表中的A2:B32单元格区域，利用“编辑”菜单下的“复制”命令；或单击常用工具栏中的“复制”按钮（或Ctrl+C） 。选中“高等数学成绩”表，光标移到A1单元格，选择“编辑”菜单下的“粘贴”命令；或单击常用工具栏中的“粘贴”按钮（或Ctrl+V） 。 步骤3：对照效果图，在“高等数学成绩”表中相应位置插入列，并输入相关内容。 步骤4：依次重复步骤1、步骤2和步骤3，完成另4门课程的成绩表。,任务完成,任务4 步骤：重复任务3中的步骤1和步骤2，创建“成绩汇总表”。,任务完成,任务5 步骤1：选择“成绩汇总表”（工作表标签），右键弹出快捷菜单，选择“移动或复制工作表”命令，在弹出的“移动或复制工作表”对话框中选择位置，本任务中“移至最后”，同时选择“建立副本”，最后单击“确定”按钮。 步骤2：右键单击“成绩汇总表（2）”工作表标签，选择“重命名”命令，将其重命名为“成绩表备份”。,案例描述,二、Excel 数据计算（参考效果图） 任务1：在各“*成绩表”中计算“平时成绩”、计算各位同学的总评，其计算规则为：平时成绩15%、期中成绩15%、期末成绩70%。并按总评对其进行单科排名，总分和名次放入所对应的单元格。 任务2：在每个课程成绩工作表中，计算课程各分数段分布人数及比例、平均分、 标准方差。计算平均分和标准方差时，小数点后保留2位小数。 任务3：参考效果图，从各课程成绩表中引用总评成绩，并填入“成绩汇总表”中各相应单元格。计算单科平均分、各科总分。 任务4：参考效果图，在“成绩汇总表”中，按总分进行排名，并利用函数判断各科是否超过单科平均分。,预备知识,Excel中的计算公式 公式的使用 运算符 单元格引用（绝对引用、相对引用、混合引用） 出错信息 函数 函数基本知识 常用函数（SUM、AVERAGE、MAX/MIN、COUNT） 选择性粘贴数据,任务完成,任务1 步骤1：选择“*成绩表”，用AVERAGE公式求得平时成绩填入H列相应单元格，其余同学的平时成绩可利用填充柄来完成计算。选择H3单元格，将鼠标指针移动至该单元格右下角位置，出现黑色十字形时拖动至相应需要填充的最后一个单元格。 步骤2：选中单元格K3，在编辑栏内输入“=ROUND（H3*15%+I3*15%+J3*70%）,2”。按“回车”键确认，求得总评成绩（四舍五入，保留整数），用填充柄向下填充，完成所有学生总评成绩的输入。 步骤3：选中单元格L3，输入“=RANK(K3,$K$3:$K$32,0)”，回车确认，求得的排名依次填入相应单元格。,任务完成,任务2 步骤1：在每个课程成绩工作表中，在学生成绩列表下方建立各成绩段分布人数及比例平均分和标准方差的相应单元格。 步骤2：利用SUMPRODUCT函数计算各个分数段分布人数，比如“=SUMPRODUCT(K3:K32=90)*(K3:K32=100)”可以计算90-100分段的人数。同时计算该分数段的人数分布比例。 步骤3：选定K3:K32，用AVERAGE公式求得该课程的平均分。 步骤3：选定K3:K32，用STDEV公式求得该课程的标准方差。,任务完成,任务3 步骤1：选择“成绩汇总表”，用VLOOKUP公式从各课程成绩表中引用总评成绩并填入相应单元格。比如，在编辑栏中输入“=VLOOKUP(A3,高等数学成绩!$A$3:$K$32,11,0)”，回车确认，再利用填充柄完成其余同学的总评成绩。 步骤2：重复步骤1，完成其他四门课程总评成绩的输入。 步骤3：选中单元格C33，单击编辑栏中的“插入函数”按钮，在弹出的“插入函数”对话框中选择“AVERAGE”函数，在打开的AVERAGE“函数参数”对话框中的第一个参数“Number1”文本框中输入C3：C32（也可以通过选择区域完成输入），单击“确定”按钮完成第一位同学高等数学平均分的计算。 步骤4：其余同学可利用填充柄来完成计算。 步骤5：总分的计算可参考步骤3和步骤4，将AVERAGE函数换成SUM函数，并修改相应的数据区域C3:G3即可。,任务完成,任务4 步骤1：选中单元格I3，在编辑栏内输入“=RANK(K3,K$3:K$32,0)。按“回车”键确认，求得按总分的排名，用填充柄向下填充，完成所有学生的排名。 步骤2：选中单元格J3，在编辑栏内输入=IF(AND(E3=$E$33,F3=$F$33,G3=$G$33,H3=$H$33,I3=$I$33),“是“,“否“)， IF函数是判断条件（第一个参数），条件为真返回“是”，为假返回“否”。以此求得单科是否超过平均分。用填充柄向下填充，完成对所有同学的判断。,案例描述,三、工作表格式化 任务1：设置“学生信息”表标题格式为隶书、22号字，并要求合并及居中。 任务2：将“成绩汇总表”中的“单科平均分”保留两位小数，并将各列以“最合适列宽”显示。 任务3：将“成绩汇总表”中各科成绩低于60分的学生成绩用红色的字体。黄色的底纹显示。 任务4：为“学生信息”表中数据所在区域加田字框：粗线外框，细线内框。,预备知识,工作表的格式化 数字的格式化 字体的设置 设置列宽与行高 数据对齐方式设置 设置工作表的边框与底纹 自动套用格式设置 条件格式的设置,任务完成,任务1 步骤1：选定“学生信息”表。 步骤2：选定A1：G1单元格区域，利用“格式”菜单下的“单元格”命令，或直接右击选中的单元格区域，在弹出的快捷菜单中选择“设置单元格格式”，弹出“单元格格式”对话框。 步骤3：在“单元格格式”对话框中，单击“字体”选项卡，设置相应的字体为隶书，字号为22。 步骤4：单击“对齐”选项卡，水平对齐方式选择“居中”，并选中其中的“合并单元格”复选框，最后单击“确定”按钮。,任务完成,任务2 步骤1：选定“成绩汇总表”。 步骤2：选定C33：G33单元格区域，单击“格式”菜单下的“单元格”命令，或直接右击选中的单元格区域，在弹出的快捷菜单中选择“设置单元格格式”，弹出“单元格格式”对话框。 步骤3：在“单元格格式”对话框中，单击“数字”选项卡，在“分类”列表框中选中“数值”，设置小数位数为2，单击“确定”按钮。 步骤4：选定整张“成绩汇总表”中的数据区域，单击“格式”菜单下的“列”“最合适的列宽”命令，即可完成最合适的列宽的设置。,任务完成,任务3 步骤1：选定“成绩汇总表”。 步骤2：选定C3：G32单元格区域，单击“格式”菜单下的“条件格式”命令，弹出“条件格式对话框”。 步骤3：在“条件格式”对话框中的“条件1”栏中的3个组合框中分别选择或输入“单元格数值”、“小于”、“60” 。 步骤4：单击“条件格式”对话框中的“格式”按钮，在弹出的“单元格格式”对话框的“字体”选项卡中选择红色字体，在“图案”选项卡中选择黄色底纹，最后单击“确定”按钮。,任务完成,任务4 步骤1：选定“学生信息”表。 步骤2：选定数据区域A1：G32，单击“格式”菜单下的“单元格”命令，或直接右击“设置单元格格式”命令，弹出“单元格格式”对话框。 步骤3：在“单元格格式”对话框中选择“边框”选项卡，在该选项卡中先选择粗线的线条样式，再单击“预置”栏内的“外边框”按钮；同样再选择细线的线条样式，单击“预置”栏内的“内部”按钮，最后单击“确定”按钮。,作业要求,1、虚拟一些学生的信息（不少于20人），新建学生信息工作表，包括：学号、姓名、性别、出生年月、年龄、专业、联系方式； 2、新建学生各个课程的成绩工作表（课程不少于5门），每个表包括：平时成绩（5次）、平时成绩总评、期中、期末、期末总评； 3、输入数据的有效性（成绩采用百分制，符合常规要求）