1、实验三 电子表格Excel的公式和函数一、实验目的1.掌握Excel工作表的基本操作2.掌握Excel中数据的输入、编辑方法。3.掌握Excel中公式和函数的使用。4.掌握Excel工作表格式化的方法。二、实验内容将工作簿“学生信息表.xlsx”复制为“excel1-班级-学号-姓名.xlsx”（用自己的真实信息代替）。打开复制的该工作簿，完成下面的操作并及时保存。1.工作表的操作将“专业”表复制到“课程成绩”的前面，并重命名为“计算机专业”。2.数据有校性检验选择工作表“学生基础信息”，设置“性别”一列（单元格区域D2:D26）的输入只能是“男”或“女”。【提示】选定要限定数据有效性范围的单
2、元格区域：“D2:D26”，然后单击“数据|数据工具|数据有效性”，选择“数据有效性”，打开该对话框，在“设置”选项卡的“允许”下拉列表中选择“序列”；在来源中输入“男,女”，需要注意的是“男,女”之间的分隔符“,”是英文的字符，而不是中文的“，”。单击“确定”。如图7-1所示。这样在选定的性别输入项中输入“男”、“女”以外的字符都是非法的。图7-1 数据有效性对话框该对话框里的复选框“提供下拉箭头”，如果选中，允许用户在工作表中输入数据时可以从下拉列表中选择要输入的选项，这样可以快速地输入数据。3.数据的输入选择工作表“学生基础信息”，在第26行输入本人的姓名、学号、身份证号、性别信息，验证
3、“性别”数据有效性设置是否正确。在第27行输入当前日期和时间。【提示】 学号、身份证号为数字作为文本处理，输入时在数字前加英文单引号“”，如输入“201711111001”。日期的输入：“2017-10-10”或“2017/10/10”。按快捷键“Ctrl+；”可以快速输入当前日期；按快捷键“Ctrl+Shift+；”可以快速输入当前时间。4.数据填充练习等差序列、等比序列有规律的填充：先在两个相邻单元格输入数值，然后选中两个单元格，使用填充柄拖曳，产生若干个有规律的数据。或者选择“开始|编辑”组里的填充按钮的下拉列表进行相应的填充。5.简单的公式和函数的使用需要特别强调的是，在excel中使
4、用公式时，必须首先输入一个“=”，输入公式时，使用的各种符号要用英文半角的。选择“课程成绩”工作表，完成下面的操作。(1)计算每个学生的总分，平均分，并求出各科目的最高分、平均分;【提示】对于函数的输入，可打开“开始|编辑”组里求和的下拉列表，显示常用的函数或者选择“其他函数”打开“插入函数”对话框；或者利用插入函数按钮打开“插入函数”对话框；也可直接输入公式实现。利用填充柄填充提高效率。例如，计算总分时在G2单元格输入“=SUM(C2:F2)”，注意公式以“=”开始，并使用英文下的符号。G2输入结束后按住G2的填充柄向下填充。平均分利用AVERAGE（）函数，最高分的计算用MAX（），最低分
5、计算用MIN（）。(2)计算每个学生的名次。【提示】名次一列，在I2输入“=RANK(G2,$G$2:$G$25)”或者“=RANK(G2,G$2:G$25)”，注意这里的第二个参数的地址是绝对地址或混合地址。其余单元格利用填充柄快速填充。这里同学们可以试一下公式中使用相对地址表示总分的区域范围后利用填充柄填充后的错误效果。(3)对每人的学习成绩进行等级划分，前5名的学生为“优秀”，其余的为“一般”。【提示】等级的划分用到“IF”函数。单击“插入函数”按钮，打开“插入函数”对话框，在“或选择类别”中选择“逻辑”类的IF函数。在IF函数的参数对话框中输入图7-2所示内容。注意其中的“优秀”和“一
6、般”的双引号不需要手工输入。图7-2 IF函数参数示例其余学生的等级通过填充柄方式实现。(4)嵌套IF的使用。在“大学计算机等级”一列将“大学计算机”的百分制成绩转换成“优、良、中、差、不及格”五个等级。【提示】转换工作通过嵌套IF语句实现，如“=IF(C2=90,优,IF(C2=80,良,IF(C2=70,中,IF(C2=60,差,不及格)”，注意公式中的符号用英文的。(5)统计优秀生人数，将结果存放在C29单元格。【提示】利用CountIF函数实现，如“=COUNTIF(J2:J25,优秀)”。 6.函数的嵌套使用在excel中使用公式时，有时需要函数的嵌套使用。选择“学生基础信息”工作表
7、，完成下面的操作。（1）身份证号的第17位奇数表明男性，偶数表明女性，利用身份证号计算并填充“计算性别”一列。 【提示】MOD()为求余函数，MID()在字符串中指定的位置取出指定个数的字符。在H2输入“=IF(MOD(MID(C2,17,1),2)=1,男,女)”。（2）校验“性别”和“计算性别”一列是否相同，相同的在“校验性别”一列显示“正确”，否则显示“错误”。将出现错误的学生的“性别”一列的值修改过来，使得“校验性别”一列全部显示正确。【提示】利用“=IF(D2=H2,正确,错误)”比较两列的性别是否一致。（3）学生学号的前4位表明“入学年份”，学号的第7、8两位表明“专业”，专业的编
8、码和名称见“计算机专业”工作表。请填充专业名称和入学年份。【提示】计算入学年份可以利用“=LEFT(B2,4)”。计算专业名称可以利用VLOOKUP函数，如：“=VLOOKUP(MID(B2,7,2),计算机专业!$A$2:$B$4,2)”。这里要熟悉不同工作表数据的引用方法。VLOOKUP是查找函数，基本语法：VLOOKUP(查找目标，查找范围，返回值的列数，精确或模糊查找)。该函数给定查找目标，从查找范围查找该目标，返回该目标所在行对应的需要返回的列数的值。如果第4个参数为TRUE或省略，则查找范围需要按照第一列的值升序排序，否则函数可能无法返回正确的值。（4）计算并填充“年龄”列【提示】
9、身份证号的7-10位表明出生年份，利用当前年份减去出生年份可以计算年龄，如“=YEAR(NOW()-MID(C2,7,4)”。NOW()返回系统当前的日期和时间。（5）统计“计算性别”一列计算出来的男生人数和女生人数。【提示】利用公式“=COUNTIF(H2:H26,男)”和“=COUNTIF(H2:H26,女)”统计人数。（6）将奖学金一列从100，依次递增40填充。（7）计算女生获得奖学金总额。【提示】利用公式“=SUMIF(H2:H26,女,J2:J26)”计算。（8）统计比“J22”奖学金多的学生的人数。【提示】利用“=COUNTIF(J2:J26,&J22)”统计，这里需要注意的是，
10、需要用“&”连接符号连接“”和表示单元格的值的单元格名称J22。7.工作表编辑和格式化。选择“课程成绩”工作表完成以下操作。（1）在工作表的表格上方插入表标题：“学生成绩表”，设置标题文字格式为“华文彩云、加粗、24磅”，在A1：K1区域合并居中。（2）设置“平均分”列的单元格数值保留1位小数。（3）设置表格列标题行行高30，图案样式“12.5%，灰色”。【提示】“图案样式”在单元格格式的“填充”选项卡中。（4）调整表格各列的列宽为“自动调整列宽”。（5）设置“姓名”列的列宽为15，“学号”列的列宽为20。（6）将表格内容按照“等级”进行升序排序，等级相同的，按照“名次”降序排序。（7）使用条
11、件格式，设置各科成绩中小于60分的为红色，加粗显示。【提示】设置条件格式时，先选定各科成绩区域C3:F26，再利用“开始|样式|条件格式|”下拉列表中的“突出显示单元格规则”设置条件。（8）设置A2：K26区域中所有内容垂直方向和水平方向均居中，为该区域加“蓝色，强调文字颜色1，双线外边框”、“黑色单线内边框”。【提示】利用“开始|字体|”或者“开始|单元格|格式|设置单元格格式”修改边框。8.转置复制将“课程成绩”工作表中等级为“优秀”的学生的信息转置复制到A52单元格处【提示】转置是指将表格转90度，即行变列、列变行。实现的方法是选中要复制的表格区域进行复制；然后插入点定位到目标区起始单元
12、格，在“开始”选项卡的“粘贴”下拉列表中选择“转置（T）”。然后删除不符合条件的等级为“一般”的单元格。实验三 图表格式化以及数据的管理一、实验目的1.掌握图表的创建、编辑及格式化方法。2.掌握数据清单的排序、筛选方法。3.掌握数据的分类汇总方法。4.掌握数据透视表的操作方法。5.掌握对页面的页眉和页脚等的设置方法。二、实验内容将实验七的工作簿“excel1-班级-学号-姓名.xlsx”复制为“excel2-班级-学号-姓名.xlsx”（用自己的真实信息代替）。打开复制的该工作簿，完成下面的操作并及时保存。1.插入图表建立“图表”工作表，将“课程成绩”工作表A2：A10区域和D2：E10区域中
13、的数据复制到“图表”工作表。然后按照要求创建图表并设置相应的格式。以复制的数据为数据源，在数据表的下方生成一个簇状圆柱图。【提示】对数据进行图表化，首先要明确需要选中工作表中的哪些数据？然后明确要创建图表的类型是什么？操作步骤：首先选中要绘图的数据，然后利用“插入|图表”组中相应的绘图命令或者“插入|图表”组的对话框启动器，选择绘图的类型和子类型。2.格式化图表（1）设置图表布局为“布局1”，图表标题为“程序设计基础和高等数学成绩”，图表高度为6厘米、宽度设为10厘米。【提示】利用“图表工具|布局|标签|图表标题”修改或添加图表标题。利用“图表工具|格式|大小”修改图表的宽度和高度。（2）将图
14、表中“程序设计基础”和“高等数学”的数据系列次序对调。【提示】对图表进行编辑，首先要清楚图表中的各个对象，选中所需的对象，然后通过快捷菜单进行相应的操作。右击图表选择“选择数据”对话框，打开“选择数据源”对话框，在“图例项（系列）”中对选定的图例项单击上移或下移按钮进行数据系列的上移或下移操作。（3）将“程序设计基础”数据系列增加以值显示的数据标记。【提示】对图表进行格式化时，操作的对象不同，出现的菜单也不同。首先明确操作的对象，然后选中对象右击，在快捷菜单中选择所需要的命令。这里可以选择“程序设计基础”数据系列，右击在快捷菜单中选择“添加数据标签”。可以利用“设置数据标签格式”对话框对标签选
15、项进行设置。（4）修改图表边框线。将图表边框设置为边框样式“5磅、三线”；边框颜色“实线、深蓝，文字2”。（5）图例边框的设置。边框颜色：“实线、橙色，强调文字颜色6”；边框样式：“2磅、双线”。阴影：“右下对角透视”。（6）将图表区域颜色填充设置为“渐变填充”，预设颜色为“碧海青天”。（7）将创建的“程序设计基础和高等数学成绩”图表复制到图表的下方，并将图表的图表类型更改为“带数据标记的折线图”。（8）将姓名和程序设计基础2列前面6位学生成绩复制到“图表”工作表，创建“分离型三维饼图”。图表布局：“布局6”；图表样式：“样式2”；饼图上设置数据标签：“值，百分比”；对最高分加“插入|插图|形
16、状”中的“云形标注”并添加文字“最高分”。【提示】“图表工具”有设计、布局和格式3个子选项卡，其中的“图表工具|设计|”选项卡中有“图表布局”、“图表样式”功能组。3.排序（1）在所有工作表的后面创建“分类汇总”工作表，将“学生基础信息”工作表中的姓名、性别、专业、奖学金的数据复制到该工作表。（2）将“分类汇总”工作表的数据按“专业名称”进行升序排序，“专业名称”相同的按照“性别”升序排序，“性别”相同的按照“奖学金”降序排序。4.筛选对“分类汇总”工作表进行筛选操作。筛选出计科专业的女生记录，并将筛选的结果复制到新建的“筛选”工作表中。5.分类汇总对“分类汇总”工作表取消筛选操作。对数据清单
17、进行分类汇总操作，要求按照“专业名称”进行分类，汇总各专业的奖学金总和。【提示】对于分类汇总，首先要对分类的字段进行排序，然后进行分类汇总，否则分类汇总的结果不正确。对于分类汇总，要清楚对谁进行分类，即分类的字段是什么；然后清楚汇总的方式，是计数、求均值、还是求和？最后要清楚对哪些字段或项目进行汇总？利用“数据|分级显示|分类汇总”命令进行分类汇总。6.嵌套分类汇总在原分类汇总的基础上，再汇总出各个专业男生和女生的人数。【提示】在原分类汇总的基础上，再进行汇总，就是嵌套分类汇总。这时只需要在原来汇总的基础上再进行分类汇总，但是要取消对“替换当前分类汇总”复选框的选择。操作如图8-1所示。图 8-1嵌套时分类汇总的对话框7.建立数据透视表将“分类汇总”的所有数据清单复制到新建立的工作表“数据透视表”中，对“数据透视表”工作表的数据建立数据透视表。【提示】利用“插入|表格|数据透视表”可以建立数据透视表。建立数据透视表同分类汇总类似，也需要清楚行标签、列标签，显示的数据字段和方式。如图8-2 所示编辑数据透视表的行标签，设定汇总的字段为奖学金，汇总的方式为求和，单击奖学金右边的下拉箭头，出现选项，可以进行值字段的设置。图8-2 数据透视表的设置8.页面设置对“课程成绩”工作表设置页眉“计算机学院成绩”，设置页脚显示当前页码和总的页数，当前日期。
