Excel2016的应用完整课件.pptx

1、Excel2016【Excel2016工作界面】目录CONTENTS创建班级学生信息表美化学生信息表编辑与修改学生信息表设置学生信息表数据验证制作学生期末成绩表并排名次0102030405筛选学生期末成绩表汇总分析学生月考成绩表汇总分析学生月考成绩表创建图表分析学生期末成绩表06070809Excel 2016的基本操作任务一 创建班级学生信息表【明确任务】学校有新生入学，作为学生会干部的你，要用Excel制作一份学生信息表，方便了解学生基本信息。【知识准备】工作簿、工作表、单元格工作簿是在Excel电子表格中用来存储并处理数据的文件,其扩展名是.xlsx。工作表是用于存储和处理数据的表格页，

2、它构成了工作簿文件，在一个工作簿文件中可以有多个工作表，系统默认一个工作簿中有3个工作表。行与列交叉形成的方格称为单元格，表中的每个单元格都有一个唯一的名称，称为单元格地址。【知识准备】新建工作簿Excel与Word类似，可以通过“开始菜单”单击目录下的Excel启动并新建工作簿。也可通过鼠标右击桌面空白处新建工作簿。启动Excel后在“文件”选项卡下选择“新建”，可新建一空白工作簿【知识准备】保存工作簿与Word类似，在编辑Excel的时候要时常单击左上角“保存”按钮，防止数据丢失。对于修改Excel数据同时想保留初始数据 的情况下，可以通过“文件”选项卡下的“另存为”来实现。【知识准备】输

3、入数据文本鼠标左键单击选择单元格后直接输入，默认为左对齐一般数字与输入文本操作相同，默认为右对齐负数直接在数字前面输入“-”或者给数字添加圆括号“（）”分数输入“0”和空格在输入分数，或者转换为文本格式可直接输入长数字最多显示11位数字，更多位数字以科学计数法的形式显示以文本格式输入数字输入法在英文格式下输入“”和数字【任务实施】新建学生信息表工作簿鼠标定位到桌面，单击“右键”选择“新建”，选择Microsoft Excel工作表，并将其命名为学生信息表。【任务实施】输入文本鼠标左键双击新建的Excel工作簿将其打开，并在sheet1工作表中A1：J15位置输入以下文本【检查评价】评价项目教师

4、评价自我评价 表格内容是否完整 表格格式是否美观【课堂小结】1234工作簿、工作表、单元格新建工作簿保存工作簿输入数据表格的基本设置任务二 美化学生信息表【复习回顾】学校有新生入学，作为学生会干部的你，要用Excel制作一份学生信息表，方便了解学生基本信息。工作簿、工作表和单元格有神区别与联系？如何新建工作表？如何在Excel单元格中输入分数？【明确任务】创建完学生信息表后，你觉得自己制作的表格不够美观，因此尝试美化学生信息表【知识准备】设置文本格式在开始选项卡下字体选项组中可设置字体格式，包括字体样式、字号、加粗、倾斜、下划线、边框、字体颜色等功能。【知识准备】设置对齐方式对齐方式在Word

5、中属于一种段落格式，在Excel中则有一个独立的对齐方式选项组，可以设置竖直和水平方向的对齐方式，此外还有自动换行和合并单元格等功能。【知识准备】表格数据选择选择单个单元格选择相邻单元格区域选择不相邻的单元格区域选择大量数据中页面无法显示全的数据【知识准备】练习打开内容拓展一练习，选择表格中的A1:J130区域【任务实施】设置字体格式鼠标定位到学生信息表带数据的表格任意位置，使用“Ctrl+A”快捷键（同时按住Ctrl键和A键）全选文本，在字体选项卡下设置字体效果为“宋体”，字号为“12号”选择A1单元格，按住鼠标左键不放拖动至J1单元格，从而选中A1：J1单元格区域，设置其字号为“14号”并

6、加粗【任务实施】设置字体格式选择F2：F15单元格，在字体选项卡下将文字变为“倾斜”【任务实施】设置对齐方式鼠标定位到工作表中任意文本，使用“Ctrl+A”全选，在对齐方式选项卡下设置文本对齐方式为“垂直居中”和“居中”选择“E2：E15”单元格，设置其对齐方式为“左对齐”【任务实施】添加边框和底纹鼠标定位到工作表中任意文本，使用“Ctrl+A”全选，在字体选项卡下单击“边框”，在下拉菜单中选择“所有框线”【任务实施】添加边框和底纹选择“A1：J1”单元格区域，在字体选项卡下选择“填充颜色”，在打开的下拉列表中选择“绿色”【检查评价】评价项目教师评价自我评价 信息表文件内容是否完整 表格格式是

7、否美观【课堂小结】123设置文本格式设置对齐方式表格数据选择工作表行、列的添加和删除任务三 编辑与修改学生信息表【复习回顾】学校有新生入学，作为学生会干部的你，要用Excel制作一份学生信息表，方便了解学生基本信息。如何设置文本格式？如何设置文本对齐方式？如何在表格中选择不相邻的单元格区域？【明确任务】开学后班级内出现学生转专业、调整班级或者统计数据重复等情况，需要对学生信息表的行和列进行调整，这个想时候就需要对工作表的行和列进行添加和删除，这个任务班主任交给了作为班委的你。【知识准备】插入单元格插入单元格包括活动单元格移动和插入行或列，在开始选项卡下，单元格选项组中点击插入选项可进行调整。此

8、外选中单元格后执行右键-插入也可实现插入单元格。【知识准备】调整单元格的大小 单元格行高默认为14，列宽默认为8.09。大多数情况下我们输入的数据与单元分的大小是不相匹配的，这个时候就需要我们调整单元格的大小。在开始选项卡单元格选项组中选择格式选项可调整行高和列宽。【任务实施】插入行将鼠标移送至文本“高娣”左侧“13”位置，点击鼠标右键，选择“插入”，会在13行上面插入新的一行。【任务实施】输入文本在“A13：J13”区域输入文本内容如下【任务实施】插入列同样，鼠标右键单击文本“性别”上方的“B”，选择“插入”，会在B列左侧插入新的一列。在新插入的单元格中输入文本内容如图【任务实施】拓展练习尝

9、试在身份证号一列中输入任意18位身份证号，并将身份证号显示出来【任务实施】删除单元格对于个别单元格或单元格区域中的文本，需要删除的直接鼠标左键选中单元格或单元格区域，按“Delete”键即可清除文本内容；如若需要删除一行或一列单元格，可选中行标或列标右键单击，然后选择“删除”即可。【检查评价】评价项目教师评价自我评价 表格内容是否完整 表格格式是否美观【课堂小结】12插入单元格调整单元格的大小 数据验证设置任务四 设置学生信息表数据验证【复习回顾】学校有新生入学，作为学生会干部的你，要用Excel制作一份学生信息表，方便了解学生基本信息。如何在表格中插入行和列？删除单元格的方式有哪些？如何调整

10、单元格的大小？【明确任务】你创建的学生信息表中要输入新生的户口性质，由于依次输入非常麻烦，你决定用数据验证的方式提高工作效率。【知识准备】数据验证数据验证是指给单元格中输入的数据设置一个范围，当我们输入数据的时候可以在设置的选项中进行选择，从而避免重复输入、类型错误等情况。在数据选项卡，数据工具选项组中选择数据验证即可设置。【任务实施】新建工作表用鼠标左键点击“Sheet1”右侧“+”按钮，新建一个工作表“Sheet2”【任务实施】输入数据在A1：A3单元格中输入数据如图【任务实施】设置数据验证在“Sheet1”中选择F2：F16区域，单击“数据”菜单下的“数据工具”选项卡中的“数据验证”，在

11、下拉菜单中选择“数据验证”【任务实施】设置数据验证打开如图所示数据验证对话框【任务实施】设置数据验证验证条件下的“允许”选择“序列”【任务实施】设置数据验证验证条件下的“允许”选择“序列”【任务实施】设置数据验证来源选择Sheet2工作表A1：A3区域，设置完成后点击确定按钮。此时鼠标左键选中“Sheet1”工作表“户口性质”下任意单元格，在右侧会出现一下拉菜单按钮，将“王忠诚”的户口性质由农村改为县镇。【检查评价】评价项目教师评价自我评价 表格内容是否完整 表格格式是否美观使用公式、函数、自动填充、排序功能运用任务五 制作学生期末成绩表并排名次【公示与函数】公式公式与函数是Excel处理数据

12、的核心内容之一。与数学公式类似，Excel中的公式也满足一定的标注规则，主要包含了运算符、常量、函数以及单元格引用等内容。其最主要的特点是以等号开始，以单元格名称为变量。例如我们要求一个圆的面积，公式展示如图在B2单元格中输入面积公式=PI()*A22，其中PI（）为函数，返回值为的大小，A2单元格为自变量半径。【公示与函数】函数上面我们提到了PI函数，实际上Excel中的公示与函数是相辅相成的，函数最终可以表示为一个公式，公式中可以包含多个函数。Excel中的函数种类和数量纷繁复杂，除了基本的加减乘除外还包含很多财务函数工程函数等，我们可以通过插入函数对话框下的“有关该函数的帮助”链接到微软

13、官网了解所有公式和函数的功能。【明确任务】期末成绩是学生评奖评优的一项重要参考，为此需要对同学们的期末成绩进行排名，你作为班长，在搜集到同学们的期末成绩后，决定用Excel公式和函数进行排名。【知识准备】公式的概念数学上我们通常称公式是包含了常量、运算符、变量等元素的方程式，与数学上的概念类似，Excel中的公式也包含了运算符、常量等元素，但它的变量通常以单元格引用的形式出现，同时所有公式都以“=”开始。例如：=1+3【知识准备】公式中的运算符1算数运算符加、减、乘、除等常见的数学运算符。2比较运算符用于比较两个数值，如“=”、“”、“”等。3文本运算符连接符“&”，可以理解为与、和的意思，表

14、示将数据连接到一起。【知识准备】单元格引用1相对引用相对引用是指引用对象随公式所在单元格位置变动而变动的一种引用方式，在公式复制或填充时引用单元格随着移动的位置上相应变化。形式为“A1”、“C2:F3”等2绝对引用绝对引用是指引用对象为固定单元格，就算公式复制填充位置变动，引用单元格位置也不会发生变动。绝对引用是在相对引用的基础上，在引用单元格的行标和列标前同时添加“$”符号。例如“$F$6”、“$E$4:$G$6”等。3混合引用混合引用是指在公式中同时使用相对引用与绝对引用。【知识准备】编辑公式1复制公式与复制普通文本一样，使用“Ctrl+C”和“Ctrl+V”即可。2填充公式与填充数据一样

15、，选中包含公式和需要填充的单元格区域后，选在开始选项卡编辑选项组下的填充，选择填充方式即可。此外拖动填充柄也可实现公式填充。【知识准备】函数SUM求和AVERAGE求平均数MAX求最大值MIN求最小值IF条件函数，判断指定条件真假，常以嵌套函数的形式出现COUNT计数RANK排序【知识准备】插入函数对话框界面介绍【知识准备】插入函数对话框界面介绍【任务实施】工作表初步设置在桌面新建Excel工作簿，命名为“学生期末成绩表”，并输入数据如图【任务实施】工作表初步设置表头为宋体，14号，加粗；内容为宋体，12号；文本对齐方式全部为“垂直居中”、“居中”；表头对齐方式再设置“自动换行”，如图“Ctr

16、l+A”全选表格文本，设置边框格式为“所有框线”【任务实施】工作表初步设置在A1单元格中添加科目和姓名，样式设置如图A1单元格的行高为35，列宽为10【任务实施】使用“SUM”函数求每名学生的总成绩SUM函数用于求解单元格区域中所有数值的和，通常用于求解一行或一列的和，也可用于求解不连续区域的和。将鼠标定位到J2单元格，选择插入函数，如图【任务实施】使用“SUM”函数求每名学生的总成绩在打开的插入函数对话框中选择“SUM”函数，如图【任务实施】使用“SUM”函数求每名学生的总成绩在Number1中输入“B2：I2”，点击确定，就可以求出第一个同学“姜旭”的总分，如图【任务实施】使用“SUM”函

17、数求每名学生的总成绩将鼠标指于J2单元格右下角黑色填充柄位置，如图【任务实施】使用“SUM”函数求每名学生的总成绩按住鼠标左键不放向下拖动至J21单元格，即可完成其他学生总成绩的计算，我们称这个操作为自动填充。如图【任务实施】使用“RANK”函数计算班级学生成绩排名RANK函数用于求解某一数字在一列数字中的大小排名，其衍生函数还有RANK.AVG和RANK.EQ。其中RANK.AVG适用于多个数值排名相同，返回平均值排名；RANK.EQ适用于多个数值排名相同，返回改组数值的最佳排名。选中K2单元格，执行插入函数，选择“RANK”函数，在打开的RANK函数对话框中设置Number为J2，Ref为

18、J2:J21，点击确定即可求出“姜旭”的成绩排名。如图【任务实施】使用“RANK”函数计算班级学生成绩排名选择K2单元格，将公式中的J2:J21改为$J$2:$J$21，并将K2单元格的公式向下填充，即可得到班级学生成绩排名。如图【知识准备】拓展练习通过网络检索，学习单元格相对引用、绝对引用和混合引用相关知识，写出在上面学习的“RANK”函数中使用了哪种引用方式。【检查评价】评价项目教师评价自我评价执行公式公式审核显示公式操作，检查公式输入是否正确表格内容是否完整函数运用是否准确【课堂小结】123公式函数单元格引用自动筛选、高级筛选的运用任务六 筛选学生期末成绩表【复习回顾】在Excel中什么

19、是公式、什么是函数？公式与函数的联系与区别是什么？单元格引用的方式有哪些？【明确任务】任课老师需要利用Excel将成绩表中满足一定条件的数据筛选出来，方便对全班成绩进行分析，应当如何操作？【知识准备】自动筛选筛选功能在数据选项卡排序与筛选选项组目录下，此外在开始选项卡编辑选项组下也可调用。基本的筛选包括项目选择及简单条件，包括等于、大于、小于等。自动筛选主要包括列表值、格式和条件三种类型，当我们需要定义两个筛选条件时需要用到“与”与“或”，其中“与”表示同时满足条件，“或”表示满足条件之一。【知识准备】高级筛选在原有区域筛选结果指筛选结果显示在当前位置，原数据被覆盖将筛选结果复制到其他位置指将

20、筛选结果复制到其他单元格区域，包括其他工作表列表区域指要进行筛选的单元格区域条件区域指包含筛选条件的单元格区域，筛选条件要提前设置复制到指放置筛选结果的单元格区域选择不重复记录选中该项可取消筛选结果中的重复值【知识准备】筛选选中B1：K1单元格，点击数据菜单中排序和筛选选项卡下的筛选按钮，会发现每个单元格右下角会出现一个下拉菜单按钮，如图【知识准备】筛选接下来我们以企业财务会计成绩为例，讲解筛选的基本知识点击计算机基础下拉菜单按钮，可以选择单独显示任意成绩，比如只选择60.5，点击确定，会只显示计算机基础考60.5的学生的成绩【知识准备】筛选也可在下拉菜单中通过数字筛选，选择符合一定条件的学生

21、成绩【知识准备】筛选比如说我们要查找计算机基础90分以上的学生，可在数字筛选中选择“大于”输入值为“90”，点击确定即可，如图这样就可以筛选出计算机成绩大于90的学生【知识准备】高级筛选如果想在班级中筛选出各科成绩都在70分以上的学生，应当如何进行筛选呢？这就涉及到高级筛选的操作。将表头复制到A27：K27单元格【知识准备】高级筛选新建Sheet2工作表，输入文本【知识准备】高级筛选在Sheet1工作表中选择排序和筛选选项卡下的高级按钮，如图【知识准备】高级筛选在高级筛选对话框中选择列表区域为A1：K21，如图【知识准备】高级筛选条件区域为Sheet2工作表的A1：K2区域，如图【知识准备】高

22、级筛选接下来选择将筛选结果复制到其他位置，复制到设置为A27：K27区域，点击确定按钮，即可筛选出各科成绩70分以上的同学。如图【检查评价】评价项目教师评价自我评价筛选操作步骤是否正确筛选结果是否符合要求【课堂小结】12自动筛选高级筛选分类汇总的运用任务七 汇总分析学生月考成绩表【明确任务】期末了，学生各月月考的成绩是任课老师给学生平时表现打分的依据，现有某班级9、10、11三个月月考的成绩，我们可以通过分类汇总的方式方便老师打分。【知识准备】数据排序排序功能在数据选项卡排序与筛选选项组目录下，此外在开始选项卡编辑选项组下也可调用。除了简单的升序降序排列外，对于多条件、多排序依据等需要使用自定

23、义排序。排序依据以实际情况而定，单元格颜色、字体颜色都可排序，此外也可根据需要自定义排序规则。【知识准备】分类汇总分类汇总是对数据进行统计汇总的常用方式，创建分类汇总之前必须对数据进行排序，从而实现数据分类，在此基础上再对数据进行汇总。【任务实施】排序将第2行、第8行和第14行单元格删除，如图【任务实施】排序选中A2：I16单元格区域，单击数据菜单，排序和筛选选项卡下的排序按钮，如图【任务实施】排序在打开的排序对话框中，设置主要关键字为“姓名”，点击确定按钮，如图【任务实施】排序在打开的排序对话框中，设置主要关键字为“姓名”，点击确定按钮，如图【任务实施】分类汇总选择A1：I16区域，单击数据

24、菜单，分级显示选项卡下的分类汇总按钮，如图【任务实施】分类汇总在弹出的分类汇总对话框中，设置分类字段为姓名，汇总方式为求和，选定总项除姓名外全部选择，点击确定按钮。如图【检查评价】评价项目教师评价自我评价分类汇总操作步骤是否正确分类汇总结果是否符合要求【课堂小结】12数据排序分类汇总合并计算的运用任务八 汇总分析学生月考成绩表【明确任务】图中三个月月考的成绩分别分布在三个工作表中，要想把他们汇总到一个工作表中就需要使用合并计算。【知识准备】合并计算合并计算位于数据选项卡数据工具选项组中，指将相互独立的数据合并到一起计算。主要包括按位置进行合并计算和按类别进行合并计算。按位置合并需要注意数据在源

25、区域中具有相同的顺序，并使用相同的标签。按类别进行合并计算则用于源区域中的数据不以相同的顺序排列但使用相同的标签的情况。【任务实施】合并计算选择月考总成绩表A1单元格，点击数据菜单栏，数据工具选项卡下的合并计算按钮。如图【任务实施】合并计算在打开的合并计算对话框中，函数选择求和，如图【任务实施】合并计算单击引用位置右侧蓝边框箭头，鼠标左键点击3月月考工作表，并按“Ctrl+A”组合键选中整个工作表。如图【任务实施】合并计算点击“合并计算-引用位置”对话框右侧蓝色箭头，选中第一个引用区域，如图【任务实施】合并计算点击添加按钮，将引用位置添加到下方白框中，如图【任务实施】合并计算重复以上操作，将1

26、月和2月月考也添加到“所有引用位置”当中。如图【任务实施】合并计算勾选标签位置下的首行和最左列，点击确定，如图【任务实施】拓展练习产品销售业绩统计与分析原文件为某家具城3月份销售相关情况的统计表，现要求对表格进行美化、使用函数计算销售额和利润并使用合并计算求出每人的销售额和利润。效果如图其中标题“家具城销售额统计”字体为黑体，字号为20号；A2：G2区域字体为宋体，字号为14号，填充颜色为浅绿；内容文本字体为宋体，字号为12号；使用公式或函数求解总销售额和利润；分类汇总以“业务员”为分类字段，以求和为汇总方式，以“总销售额”和“利润”为选定汇总项。【检查评价】评价项目教师评价自我评价合并计算操

27、作步骤是否正确合并计算结果是否准确图表的创建、编辑任务九 创建图表分析学生期末成绩表【明确任务】当我们想比较每个学生各科成绩的时候，会发现单纯以表格的形式展示数据并不直观，这个时候我们可以使用图表来分析学生成绩。【知识准备】推荐的图表Excel可根据用户选中的数据智能的推荐图表，在不知道该用哪种图表样式的时候可选择该选项。【知识准备】组合图表组合图表是指多种图表样式的叠加，包括簇状柱形图-折线图、簇状柱形图-次坐标轴上的折线图、堆积面积图-簇状柱形图三种基本形式，此外用户也可根据实际需求选择两种基本类型的组合。【知识准备】图表数据编辑图表数据编辑主要包括对现有数据编辑和添加数据，当我们插入图表

28、后，使用鼠标左键选中图表，在图表工具-设计-数据中可进行数据设置。这里需要注意图例项和水平轴标签的设置。【知识准备】设置图表布局图表布局在图表工具-设计-图表布局中进行设置，除在快速布局中提供的8中预设布局样式外，通常我们使用添加图表元素功能来设置图表布局，坐标轴、图标标题、数据标签等元素都可进行设置。【知识准备】设置图表样式图表样式在图表工具-设计-图表样式中进行设置，Excel提供了多种预制样式供用户选择，此外可通过更改颜色功能改变图表颜色。【任务实施】图表创建与简单编辑双击打开学生期末成绩表源文件选中A1：F7单元格区域，选择“插入”“图表”，单击插入柱形图或条形图，如图【任务实施】图表

29、创建与简单编辑在下拉菜单中选择三维柱形图三维簇状柱形图，如图【任务实施】图表创建与简单编辑将图表拖动到适当位置，修改图表标题为“期末成绩”，如图【任务实施】图表创建与简单编辑双击鼠标左键选中安全对应柱子，如图【任务实施】图表创建与简单编辑选择图表工具设计添加图标元素数据标签数据标注，即可将姜旭的安全成绩标注到图标中。同理，单击鼠标左键可选中一类柱子进行标注，如图【任务实施】图表的美化选择图表工具设计图标样式更改颜色彩色调色板4，如图【任务实施】图表的美化选择图表样式下拉菜单中的样式9，如图【检查评价】评价项目教师评价自我评价图表制作步骤是否正确图表类型选择是否合理图表格式是否美观【课堂小结】123Excel中常用图表及其种类设置图表布局设置图表样式