电子表格软件Excel（PPT38页)

电子表格软件Excel2016目录4.1制作学生成绩表4.2制作职工工资分析表4.3制作职工职称工资分析表4.4制作销售情况分析图4.5制作学生成绩分析表4.6对工作表中的数据进行高级筛选4.7创建职工学历信息数据透视表和数据透视图电子表格软件Excel2016—教学目标掌握新建空白工作簿、数据填充、合并单元格、设置单元格格式的方法;掌握各种常用函数的使用方法。熟悉各种重要函数的用法；掌握设置各种单元格格式的方法。掌握自动筛选、排序、分类汇总的操作方法；掌握高级筛选的操作方法。掌握数据透视表和数据透视图的作用及操作方法。4.1制作学生成绩表——项目要求新建一个空白工作簿，并以“学生成绩表”为名称进行保存。在A1单元格中输入“计算机应用1班成绩表”，然后在A2：G2单元格中输入相关数据。在A3单元格中输入“1”，然后拖动单元格“填充柄”进行序列填充。使用上述相同的方法输入学号列的数据，然后依次输入姓名以及各科成绩。合并A1：G1单元格区域，设置单元格格式为“黑体、18号”。选择A2：G2单元格区域，设置单元格格式“宋体、12号、居中对齐”，设置底纹为“茶色、背景2、深色25%”。自动设置各列列宽，手动调整各行行高。本任务要求了解计算机网络的发展，掌握计算机网络的概念、作用和分类。4.1制作学生成绩表——相关知识Excel2016工作界面与Word2016工作界面基本相似，由快速访问工具栏、标题栏、功能区、编辑栏和工作表编辑区等部分组成，如图所示，下面介绍编辑栏、名称栏等术语及其作用。新建工作簿•在Excel2016中，工作簿是存储数据的文件，其默认扩展名为“xlsx”。Excel在启动后会自动创建一个名为“工作簿1”的空白工作簿，在未关闭“工作簿1”之前，再新建工作簿时，系统会自动命名为“工作簿2”、“工作簿3”…。保存工作表•当完成一个工作簿的创建、编辑后，就需将工作簿文件保存起来，Excel2016提供了“保存”和“另存为”两种方法用于保存工作簿文件，其操作步骤如下。•（1）选择“文件”→“保存”命令，此时如果要保存的文件是第一次存盘，将弹出“另存为”对话框，在该对话框中可选择“保存位置”设置，输入文件名；如果该文件已经被保存过，则不弹出“另存为”对话框，同时也不执行后面的操作。•（2）选择“文件”→“另存为”命令，将为已保存过的文件再保存一个副本。工作表的插入、删除与重命名右击“工作表标签”，在弹出的快捷菜单中选择“插入”命令，即可在所选工作表之前插入一个新的工作表；选择“删除”命令，即可删除所选工作表；选择“重命名”命令，即可给所选“工作表”重新命名。工作表区域•工作表区域即用来记录数据的区域。单元格每个单元格的位置由交叉的“行标签”和“列标签”表示，如“A5”、“B6”。每个单元格中可以存放多达3200个字符的信息，单元格是表格的最小单位。活动单元格•活动单元格是当前正在操作的单元格，被文本框框住。7．工作表标签工作表标签默认为“Sheet1”，用于显示工作表的名称，当前活动的工作表是白色的，其余为灰色，利用标签可切换显示工作表。状态栏•状态栏位于窗口的底部，可显示操作信息。数据输入•单击要输入数据的单元格，可输入所需数据。Excel工作表的单元格中，可以输入“数值型”、“字符型”、“日期时间型”等不同类型的数据。下面分别对不同类型数据的输入方法进行介绍。•1．输入数值型数据•数值型数据是类似于“100”、“3.14”、“-2.418”等形式的数据，它表示一个数量的概念。其中的正号“+”会被忽略，当用户需要输入普通的实数类型的数据时，只需直接在单元格中输入，其默认对齐方式是“右对齐”。输入的数据长度超过单元格宽度时（多于11位的数字，其中包括小数点和类似“E”和“+”这样的字符），Excel会自动以科学计数法表示。•2．输入字符型数据•字符型数据是指字母、数字和其他特殊字符的任意组合，如“ABC”、“汉字”、“@￥%”、“010-88888888”等形式的数据。•当用户输入的字符型数据超过单元格的宽度时，如果右侧的单元格中没有数据，则字符型数据会跨越单元格显示；如果右侧的单元格中有数据，则只会显示未超出部分数据。•如果用户需要在单元格中输入多行文字，那么可以在一行输入结束后，按“Alt+Enter”组合键实现换行，然后输入后续的文字，字符型数据的默认对齐方式是“左对齐”。•3．输入日期时间型数据•对于日期时间型数据，按日期和时间的表示方法输入即可。输入日期时，用连字符“-”或斜杠“/”分隔日期的年、月、日；输入时间时用“：”分隔。例如，“2004-1-1”、“2004/1/1”、“8:30:20AM”等均为正确的日期型数据。当日期时间型数据太长而超过列宽时，会显示“####”，表示当前列宽太窄，用户只要适当调整列宽就可以正常显示数据。数据自动填充•输入一个工作表时，经常会遇到有规律的数据。例如，需要在相邻的单元格中填入序号1、3、5、7等序列，这时就可以使用Excel的自动填充功能。自动填充是指将数据填写到相邻的单元格中，是一种快速填写数据的方法。1．使用鼠标左键填充•使用鼠标自动填充时，需要用到填充柄，“填充柄”位于选定单元格区域的右下角，如图4-6所示。•填充的具体操作方法是：选择含有数据的起始单元格，移动鼠标指针到填充柄，当鼠标指针变成实心十字形“”时，按住鼠标左键拖动鼠标到目标单元格。2．使用鼠标右键填充•按住鼠标右键拖动填充的方式，提供了更为强大的填充功能，其操作步骤如下。•（1）选定待填充区域的起始单元格，然后输入序列的初始值并确认。•（2）移动鼠标指针到初始值的单元格右下角的填充柄（指针变为实心十字形“”）。•（3）按住鼠标右键拖动填充柄，经过待填充的区域，在弹出的快捷菜单中选择要填充的方式。合并单元格•选中要合并的单元格，右击，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对话框，单击“对齐”选项卡，选择“文本控制”组中的“合并单元格”复选框，再单击“确定”按钮。行高和列宽的调整•工作表中的行高和列宽是Excel默认设定的，如果需要调整，可以手动完成。•1．调整行高•（1）第一种方法•把鼠标指针移动到行与上下行边界处，当鼠标指针变成“”形状时，拖动鼠标调整行高，这时Excel会自动显示行的高度值。•（2）第二种方法•选择需要调整的行或行所在的单元格，单击“开始”功能区，在“单元格”组中单击“格式”下拉菜单，选择“行高”命令，在弹出的“行高”对话框中输入行的高度值。•2．调整列宽•（1）第一种方法•把鼠标指针移动到列与左右列的边界处，当鼠标指针变成“”形状时，拖动鼠标调整列宽，这时Excel会自动显示列的宽度值。•（2）第二种方法•选择需要调整的列或列所在的单元格，单击“开始”功能区，在“单元格”组中单击“格式”下拉菜单，选择“列宽”命令，在弹出的“列宽”对话框中输入列宽的宽度值。4.1制作学生成绩表——项目实施步骤1：新建并保存工作簿步骤2：输入工作表数据步骤3：设置单元格格式步骤4：调整行高与列宽返回4.2制作职工工资分析表——项目要求使用公式在E2：E4单元格区域求出职工工资的总工资。使用求和函数SUM在C5：D5单元格区域计算职工工资的总计。使用平均值函数AVERAGE在C6：D6单元格区域计算原来工资和浮动工资的平均值，并保留两位小数。使用最大值函数MAX在C7：D7单元格区域计算原来工资和浮动工资的最大值。使用最小值函数MIN在C8：D8单元格区域计算原来工资和浮动工资的最小值。给表格添加外边框和内边框。7.2制作职工工资分析表——相关知识公式的输入•Excel通过引进公式，增强了对数据的运算分析能力。在Excel中，公式在形式上由等号“=”开始，其语法可表示为“=表达式”。•当用户按“Enter”键确认公式输入完成后，单元格显示的是公式的计算结果。如果用户需要查看或者修改公式，则可以双击单元格，在单元格中查看或修改公式。使用函数•函数是Excel中预先定义好、经常使用的一种公式。Excel提供了200多个内部函数，当需要使用时，可按照函数的格式直接引用，函数的输入有“手工输入”和“使用粘贴函数输入”两种方式。手工输入•对于一些比较简单的函数，用户可以用输入公式的方法直接在单元格中输入函数。例如，可在对应单元格中直接输入“=SUM(E4:G4)”，然后按“Enter”键确认，即可得到学生的总成绩。使用粘贴函数输入•对于参数较多或比较复杂的函数，一般采用“粘贴函数”按钮来输入，常用的函数有求和（SUM）、求平均值（AVERAGE）、计算“指定字符串”的个数（COUNT）、求参数的最大值（MAX）、求参数的最小值（MIN）等。4.2制作职工工资分析表——项目实施步骤1：新建工作表并利用公式计算出职工工资总工资步骤2：使用函数求和、求平均值、求最大值和最小值步骤3：设置平均值单元格格式，并对工作表进行命名和添加边框返回4.3制作职工职称工资分析表——项目要求使用公式在E2：E9单元格区域求出职称工资占总工资的百分比，用百分比类型，保留两位小数。使用RANK函数在F2：F9单元格区域按降序计算“工资”列的内容。使用IF函数在G2：G9单元格区域对工资级别进行分类，工资大于等于8000的设置为“高工资”，其他设置为“一般工资”。使用COUNTIF函数在J4：J6单元格区域分别求出助教、讲师及教授的人数。使用SUMIF函数在K4：K6单元格区域求出各职称职工的平均工资。4.3制作职工职称工资分析表——相关知识单元格引用用于标识工作表中单元格或单元格区域，它在公式中指明了公式所使用数据的位置。在Excel中有相对引用、绝对引用及混合引用，它们分别适用于不同的场合。•1．相对引用•Excel默认的单元格引用为相对引用。相对引用是指某一单元格的地址是相对于当前单元格的相对位置，是由单元格的行号和列号组成，如A1、B2、E5等。在相对引用中，当复制或移动公式时，Excel会根据移动的位置自动调节公式中引用单元格的地址。例如，E5单元格中的公式“=C5*D5/10”，在被复制到E6单元格时会自动变为“=C6*D6/10”，从而使得E6单元格中也能得到正确的计算结果。•2．绝对引用•绝对引用是指某一单元格的地址是其在工作表中的绝对位置，其构成形式是在行号和列号前面各加一个“$”符号。例如，$A$2、$B$4、$H$5都是对单元格的绝对引用。其特点在于，当把一个含有绝对引用的单元格中的公式移动或复制到一个新的位置时，公式中的单元格地址不会发生变化。例如，若在E5单元格中有公式“=$B$3+$C$3”，如果将其复制到E6单元格中，则E6单元格中的公式还是“=$B$3+$C$3”，可以用于分数运算时，使分母的值固定不变。•3．混合引用•在公式中同时使用相对引用和绝对引用，称为混合引用。几个常用函数•1．RANK函数•返回数字在一列数字中相对于其他数值的大小排名，可按降序排列，也可按升序排列。•1．RANK函数•返回数字在一列数字中相对于其他数值的大小排名，可按降序排列，也可按升序排列。•2．IF函数•判断是否满足某个条件，如果满足则返回一个值，如果不满足则返回另一个值。•3．COUNTIF函数•计算某个区域中满足给定条件单元格的数目。•4．SUMIF函数•对满足条件的单元格中的数据求和。4.3制作职工职称工资分析表——项目实施步骤1：利用公式计算职工工资占总工资的百分比步骤2：使用RANK函数按工资降序计算“工资”列的内容步骤3：使用If函数对工资级别进行分类步骤4：使用COUNTIF函数统计职称人数步骤5：使用SUMIF函数求各职称职工平均工资4.4制作销售情况分析图——项目要求将表格中所有数据居中。将各种设备销售额单元格的格式设置为“货币”，货币符号为“￥”，小数位数为0。选取“设备销售情况分析表”的“设备名称”和“销售额”两列内容建立“簇状柱形图”。X轴为“设备名称”，图表