单元4 Excel基本应用（课件）-《信息技术（基础模块）》同步教学（电子工业出版社）

Excel基本应用 本单元内容 02 03 使用公式制作单科成绩表 04 使用函数统计成绩总表 05 使用图表分析成绩统计表 01 使用格式制作学籍表 使用透视表管理成绩总表 2 单击此处编辑母版文本样式 第二级 第三级 第四级 第五级 使用格式制作学籍表 1 使用格式制作学籍表 启动和退出Excel ， 一、启动 Excel 二、退出 Excel 1.“文件”选项卡，选择“关闭”； 2.按“Alt+F4”组合键； 3.按标题栏右侧“关闭”按钮 ； 4.右击任务栏上的Excel图标，在弹出的快捷菜单中选择“关闭窗口”； Excel 工作界面 使用格式制作学籍表 工作簿的使用 （1）新建工作簿 （2）保存工作簿 （3）打开工作簿 （4）关闭工作簿。 （5）保护具有重要数据的工作簿 使用格式制作学籍表 工作表的使用 （1）工作表的重命名 （2）工作表的切换 使用格式制作学籍表 单元格数据的输入 （1）文本的输入 （2）数值的输入 （3）日期和时间的输入 （4）公式和批注的输入 （5）自动填充功能 （6）快速填充功能 （7）限定数据输入 （8）利用记录单输入数据 使用格式制作学籍表 单元格基本操作 （1）选择单元格 （2）移动单元格 （3）复制单元格 （4）插入与删除单元格 （5）清除单元格 （6）调整单元格行高列宽 使用格式制作学籍表 美化 工作表 （1）数字格式化 （2）文字格式化 （3）设置文本的对齐方式 （4）设置单元格边框 （5）设置单元格底纹 使用格式制作学籍表 项目效果展示 使用格式制作学籍表——项目实现 创建“学籍表”工作表 任务1.打开Excel，保存文件为“学生管理.xlsx”，并将表名改为“学籍表”。 任务3.在“学籍表”工作表中利用记录单输入数据。 任务2.在空白“学籍表”工作表中输入各种不同类型的数据。 使用格式制作学籍表——项目实现 美化“学籍表”工作表 任务1.对“学籍表”进行格式化和美化，要求：标题“华文中宋”18磅，表头“黑体”14磅和淡黄色底纹，表内“仿宋”14磅，蓝色外边框中实线，黑色内部细实线，背景图片。 使用格式制作学籍表——项目实现 使用公式制作单科成绩表 2 使用公式制作单科成绩表 工作表的引用 15 如果是当前工作簿或工作表，引用时可以省略工作簿或工作表的名称。如果是其他的工作簿或工作表，引用时需要在工作簿或工作表的名称后面加上“！”。 譬如，当前工作表是Sheet1，想引用Sheet2工作表的A3单元格，则可以写成Sheet2！A3。 条件格式 16 所谓条件格式就是在工作表中设置带有条件的格式。当条件满足时，单元格将应用所设置的格式。 选中需要设置条件格式的单元格区域，单击“开始”选项卡，在“样式”组中单击“条件格式”按钮，弹出“条件格式”面板，在列表中选择需要设置的选项进行条件的设置即可。 使用公式制作单科成绩表 公式 和函数 17 （1）公式的使用 运算符、单元格的引用 （2）函数的使用 函数的格式、函数的分类、函数的引用 （3）通配符的使用 （4）常用函数 SUM()函数、AVERAGE()函数、MOD()函数、ROW()函数。 使用公式制作单科成绩表 项目效果展示 使用公式制作单科成绩表——项目实现 创建单科成绩表 任务1.打开“学生管理.xlsx”工作簿，创建“英语成绩表”工作表，并输入数据。 使用公式制作单科成绩表——项目实现 计算成绩表 任务1.在“英语成绩表”中，计算所有学生的总评成绩，这里：总评成绩=平时成绩×40%+期末成绩×60%。 任务2.在“英语成绩表”中，利用函数AVERAGE()计算“大学英语”这门课程的平均成绩，并保留小数点后两位。 使用公式制作单科成绩表——项目实现 格式化 成绩表 任务1.合并标题并居中，并设置标题“幼圆，加粗，18磅，黑色”，设置表头“中宋，14磅，蓝色，水平居中”，设置数据区数据“楷体，12磅，水平垂直居中”。 任务2.将成绩表的外边框设置为“双细线，黑色”，内边框设置为“单细线，蓝色”。 任务3.将成绩表的表头区域套用单元格样式，将其“行高”设置为30。 使用公式制作单科成绩表——项目实现 格式化 成绩表 任务4.利用条件格式和MOD()函数、ROW()函数将成绩表的奇数行填充为浅绿色。 任务5.将成绩表表头中的“平时成绩、期末成绩、总评成绩”，在单元格中分两行显示，并将所有列的列宽，调整为最适合的宽度。 使用公式制作单科成绩表——项目实现 使用函数统计成绩总表 3 使用函数统计成绩总表 工作表的移动 24 移动操作可以调整当前的工作表排放次序。 （1）在同一个工作簿中移动工作表。 （2）在不同工作簿中移动工作表。 工作表的复制 25 复制操作可以将一张工作表中的内容复制到另一张工作表中，避免了对相同内容的重复输入，从而提高了工作效率。 （1）在同一工作簿中复制工作表。 （2）将工作表复制到其他工作簿中。 使用函数统计成绩总表 插入与删除工作表 26 在实际工作中往往需要建立多张工作表。利用“插入工作表”命令即可在当前工作表之前插入一张新的工作表。 （1）删除单张工作表。 （2）同时删除多张工作表。 使用函数统计成绩总表 保护 工作表 27 为了防止他人对工作表进行编辑，最好的办法就是设置工作表密码。 使用函数统计成绩总表 常用函数 28 （1）MAX()函数 （2）MIN()函数 （3）RANK.EQ()函数 （4）COUNT()函数 （5）COUNTA()函数 （6）IF()函数 使用函数统计成绩总表 数据排序 29 排序是将数据列表中的记录按照某个字段名的数据值或条件从小到大或从大到小地进行排列。用来排序的字段名或条件称为排序关键字。 （1）单个关键字排序 （2）多关键字排序 使用函数统计成绩总表 数据筛选 30 筛选是查找和处理单元格区域中数据子集的快捷方法。 （1）自动筛选 （2）自定义筛选 （3）高级筛选 （4）快速筛选 使用函数统计成绩总表 项目效果展示 使用函数统计成绩总表——项目实现 由多工作表生成成绩总表 任务1.利用工作表移动和复制操作分别将给定的“高等数学”、“基础会计”、“计算机基础”3门单科成绩表复制到“学生管理”工作簿中。 任务2.利用复制操作将给定的“英语成绩表”、“数学成绩表”、“会计成绩表”、“计算机成绩表”4门单科成绩表生成成绩总表。 任务3.在“学生管理”工作簿中，将“数学成绩表”工作表移至“英语成绩表”工作表之前，在“成绩总表”工作表中，将“高等数学”列移至“大学英语”列之前。 使用函数统计成绩总表——项目实现 成绩总表的统计计算 任务1.在“成绩总表”中增加“总分”列和“平均分”列，计算每位学生的总分和平均分。 任务2.在“成绩总表”中增加“名次”列，计算每位学生的总分排名，并修改每位学生的总分排名。 任务3.在“成绩总表”中，计算各门课程的平均分，结果四舍五入保留2为小数。 任务4.在“成绩总表”中，计算各门课程的最高分和最低分。 使用函数统计成绩总表——项目实现 评定奖学金等级 任务1.在“成绩总表”中，根据名次评出上学期奖学金获等级。评选比例：一等奖5%，二等奖10%,三等奖15% 。 使用函数统计成绩总表——项目实现 美化 成绩总表 任务1.套用表格格式，美化“成绩总表”工作表。 使用函数统计成绩总表——项目实现 排序单科 成绩表 任务1.在“英语成绩表”工作表中，按“总评成绩”的降序排列。 任务3.在“会计成绩表”工作表中，以“总评成绩”为关键字降序排列，并利用套用表格的汇总行计算基础会计平均分。 任务2.在“数学成绩表”工作表中，以“性别”为主要关键字升序排列，以“总评成绩”为第2关键字降序排列，以姓名为第3关键字升序排列，并套用表格样式美化该工作表。 使用函数统计成绩总表——项目实现 筛选 成绩总表 任务1.将“成绩总表”工作表复制一份，并将复制的工作表改名为“自动筛选”。在“自动筛选”工作表中筛选满足以下条件的数据记录：姓“刘”或姓名中最后一个字为“华”，其“计算机基础”的成绩在90分（含90）以上，“名次”在前5的“女”同学。 任务2.将“成绩总表”工作表复制一份，并将复制的工作表改名为“高级筛选”。在“高级筛选”工作表中筛选总分小于350分的男生或总分大于等于375的女生。 任务3.将“学生管理”工作簿复制一份，并将复制的工作簿改名为“学生管理（主题）”，将“学生管理（主题）”工作簿中所有工作表使用“主题”来统一风格。 使用函数统计成绩总表——项目实现 使用图表分析成绩统计表 4 使用图表分析成绩统计表 统计函数 39 （1）COUNTIF()函数 （2）COUNTIFS()函数 插入与编辑图表 40 （1）图表 （2）图表的种类 （3）图表的类型 （5）创建图表 （6）为图表添加标签 （7）为图表添加趋势线 （8）为图表添加误差线 使用图表分析成绩统计表 项目效果展示 使用图表分析成绩统计表——项目实现 创建成绩统计表 任务1.打开成绩总表，利用工作表引用操作，生成成绩统计表。 使用图表分析成绩统计表——项目实现 统计年级最高分和最低分 任务1.打开成绩总表，利用单元格引用将4门课程的年级平均分、最高分和最低分引用到成绩统计表中。 使用图表分析成绩统计表——项目实现 统计考试不同情况的人数 任务1.打开成绩统计表，利用COUNT、COUNTA函数统计不同情况的考试人数。 使用图表分析成绩统计表——项目实现 统计不同分数段人数 任务1.打开成绩统计表，利用COUNTIF、COUNTIFS函数统计4门课程不同分数段人数。 使用图表分析成绩统计表——项目实现 统计优秀率和及格率 任务1.打开成绩统计表，计算4门课程的优秀率和及格率。 使用图表分析成绩统计表——项目实现 制作课程等级表 任务1.打开成绩总表，利用单元格复制操作创建等级表结构并删除单元格数据，利用IF函数的嵌套并依据成绩总表数据所在范围转换为等级值，要求90分以上为A等，75-90为B级，60-75为C级，60以下为D级。 任务2.在课程等级表中，利用条件格式将4门课程中所有B等的单元格设置为“浅红色填充深红色文本”，将所有A等的单元格设置为“黄色底纹绿色加粗字体”。 使用图表分析成绩统计表——项目实现 利用条件格式设置成绩总表格式 任务1.打开成绩总表，复制工作表，重命名为“成绩表”，利用条件格式将获得一等奖的女学生以浅红色背景显示。 任务2.在“成绩表”工作表中，利用条件格式的图标集将前10名用 表示，后10名用 表示，其余名次用 表示。 使用图表分析成绩统计表——项目实现 利用图表分析成绩统计表数据 任务1.在“成绩统计表”中，根据年级平均分、最高分和最低分制作图表。要求图表类型为“簇状柱形图”，图表布局为“布局9”，图表样式为“样式14”。 任务2.在“成绩统计表”中，为图表添加“模拟运算表”，并将“图例”的位置移至图表顶部。 任务3.在“成绩统计表”中，将图表类型改为“簇状圆柱图”，将“图表”移至新工作表中，将工作表命名为“成绩统计图”。 使用图表分析成绩统计表——项目实现 利用图表分析成绩统计表数据 任务5.在“成绩统计图”工作表中，设置“图表区”、“背景墙”的填充效果，修饰图表标题。 任务6.在“成绩统计图”工作表中，为图表添加数据标签。 任务4.在“成绩统计图”工作表中，调整图表三维视图的角度，修改图例颜色，将纵向坐标轴标题横向。 使用图表分析成绩统计表——项目实现 使用透视表管理成绩总表 5 使用透视表管理成绩总表 常用函数 52 （1）VLOOKUP()函数。 （2）SUMIF()函数 （3）SUMIFS()函数 名称 的定义 53 在工作表中，可以使用“列标”和“行号”引用单元格，也可以用“名称”来表示单元格或单元格区域。使用名称可以使用公式更容易理解和维护，使更新、审核和管理这些名称更方便。 使用透视表管理成绩总表 数据透视表及切片器 54 数据透视表是一种交互式工作表，用于对现有数据列表进行汇总和分析。创建数据透视表后，可以按不同的需要，依不同的关系来提取和组织数据。 （1）创建数据透视表 （2）设置数据透视表字段 （3）美化数据透视表 使用透视表管理成绩总表 数据透视图 55 数据透视图是以图表的形式表示数据透视表中的数据。 （1）创建数据透视图 （2）设置数据透视图格式 使用透视表管理成绩总表 使用透视表管理成绩总表——项目实现 项目效果展示 创建各专业成绩表 任务1.打开“学籍管理”工作簿，利用“学籍表”和“成绩总表”，通过复制工作表和VLOOKUP函数生成“各专业成绩表”。 使用透视表管理成绩总表——项目实现 计算不同专业各门课程总分 任务1.在“各专业成绩表”工作表中，利用SUMIF函数计算不同专业不同课程的总分。 使用透视表管理成绩总表——项目实现 计算不同专业不同辅导员各门课程总分 任务1.在“各专业成绩表”工作表中，利用SUMIFS函数计算不同辅导员所带不同专业4门课程的总分。 使用透视表管理成绩总表——项目实现 用分类汇总方法统计每个辅导员管理的学生人数 任务1.在“各专业成绩表”工作表中，利用分类汇总统计每个辅导员所管理的学生人数。 任务2.在“分类汇总各辅导员”工作表中，用嵌套分类汇总统计每个专业的辅导员所管理的班级学生人数。 任务3.在“分类汇总各辅导员”工作表中，用嵌套分类汇总统计每个辅导员所负责专业的平均总分 。 使用透视表管理成绩总表——项目实现 用数据透视表一位辅导员所带不同专业的平均成绩 任务1.在“分类统计各辅导员”工作表中，选择黄艺明老师所负责两个专业的平均成绩制作“蔟状柱形图”图表。 使用透视表管理成绩总表——项目实现 用数据透视表统计不同专业的人数及平均成绩 任务1.在“各专业成绩表”工作表中，用数据透视表统计不同专业的人数和平均成绩。 任务2.在“各专业成绩表”工作表中，用数据透视表统计不同辅导员管理的学生人数和平均成绩。 使用透视表管理成绩总表——项目实现 利用数据透视图统计每个专业4门课程的平均分数 任务1.在“各专业成绩表”工作表中，用数据透视图统计不同专业4门课程的平均分。 使用透视表管理成绩总表——项目实现 掌握Excel 的启动和退出 熟悉Excel 工作界面 掌握工作簿的基本操作 掌握工作表的基本操作 掌握工作表的输出 了解单元格中数据的输入 掌握单元格的基本操作 掌握单元格中数据的编辑 掌握单元格格式的设置 掌握公式的构成及使用 掌握函数的分类及引用 掌握常用函数的格式、功能及应用 掌握数据的排序、筛选及分类汇总 掌握使用图表、透视表、透视图进行数据分析 单元小结 1 $$