信息技术基础 课件 第4章 Excel 2016应用

第四章

Excel2016应用4.1Excel2016概述P34.2公式和函数的应用

P354.3数据分析处理P604.4图表操作P72目录CONTENTS4.5Excel2016综合实例P804.1Excel2016概述

4.1.1Excel2016界面窗口4.1.2Excel启动和退出4.1.3工作簿的操作4.1.4工作表和单元格的操作4.1.5输入数据4.1.6数据填充4.1.7数据编辑4.1.8修饰工作表4.1.9样式4.1.10打印工作表4.1Excel2016概述

Excel2016是一个功能非常强大的专门用来制作电子表格的软件，是Office办公自动化软件的重要组件之一。它具有强大的数据处理功能，可以同时制作多张表格，还可以进行复杂的数据组织、计算、分析和统计，快速生成图表及数据透视表，可广泛应用于财务、信息管理、人事管理、统计、金融等领域。Excel20164.1Excel2016概述4.1.1Excel2016界面窗口4.1Excel2016概述4.1.2Excel2016启动和退出方法1：使用Windows操作系统中“开始”菜单启动Excel2016：选择Windows中的【开始】→【所有应用】→【Excel2016】命令启动。方法2：快捷键启动：在桌面空白处单击鼠标右键，选择【新建】→【快捷方式】为Excel2016在桌面上建立快捷方式后，双击桌面上的Excel图标来启动。若桌面上有Excel2016的快捷方式，直接双击图标启动即可。方法3：文件打开：在操作系统中找到Excel2016的路径，直接双击文件夹中的Excel文件自动启动。1、启动Excel2016

启动Excel2016的方法很多，现介绍常用的3种启动方法。4.1Excel2016概述4.1.2Excel启动和退出2、退出Excel2016和退出其他应用程序一样，方法有多种，现介绍常用的以下4种方法。方法1：单击Excel2016窗口右上角的【退出】按钮。方法2：在Excel2016左上角的【文件】选项卡中选择【关闭】命令方法3：单击Excel2016窗口，按<ALT+F4>功能区组合键退出。方法4：单击Excel2016窗口，按<CTRL+W>功能区组合键退出。4.1Excel2016概述4.1.3工作簿的操作1.新建工作簿

新建工作簿步骤：启动Excel2016后，【空白工作簿】→【创建】，新创建的工作簿默认文件名为“工作簿1”、“工作簿2”…。4.1Excel2016概述4.1.3工作簿的操作2.保存工作簿对于工作簿和工作表，用户对表中的数据进行操作后，需要对工作簿的内容进行保存。Excel2016提供了“保存”和“另存为”两种保存方法。方法1：单击快速访问工具栏中的【保存】命令；或者使用【文件】选项卡中的【保存】命令；或者使用快捷键<Ctrl+S>。常用于保存新建的工作簿。方法2：使用【文件】选项卡中的【另存为】命令，常用于创建工作簿的副本。4.1Excel2016概述4.1.3工作簿的操作3.打开工作簿方法1：在操作系统中找到Excel2016存在的路径，双击要打开的Excel2016文件即可打开。方法2：用前面介绍过打开Excel2016的三种方法之一打开Excel2016，通过【文件】选项卡中的【打开】命令，在对话框中选择要打开的文件，单击【打开】按钮即可。4.1Excel2016概述4.1.4工作表和单元格的操作

工作表是显示在工作簿窗口中的表格。一个工作表可以由

1048576（220）行和16384（214）列构成。行的编号依次用从1到1048576的数字表示，列号则由左到右采用字母A，B，C，…表示，当超过26列时用两个字母AA，AB，…，AZ表示，当超过256列时，则用AAA，AAB，…XFD表示。行号显示在工作簿窗口的左边，列号显示在工作簿窗口的上边。1.工作表4.1Excel2016概述4.1.4工作表和单元格的操作1）.新建工作表下面介绍工作表的相关操作。功能区【插入工作表】命令

快捷菜单【插入】命令4.1Excel2016概述4.1.4工作表和单元格的操作2）.重命名工作表方法1：右键单击需要修改的工作表标签，在弹出快捷菜单中选择【重命名】命令，此时工作表标签进入编辑模式，直接输入新名称，然后按<Enter>键即可。方法2：双击需要修改的工作表标签，此时进入编辑模式，输入新名称后按<Enter>键即可。4.1Excel2016概述4.1.4工作表和单元格的操作3）.选定和切换工作表

单击工作表标签即可选定需要操作的工作表，也可选定工作表与Windows操作相同，使用Ctrl和Shift键。

全选所有工作表：右键工作表标签，选定全部工作表即可。同时选定多个工作表4.1Excel2016概述4.1.4工作表和单元格的操作4）.移动、复制工作表（1）同一个工作表内的移动和复制操作。选中Sheet1标签移动到原Sheet3标签（2）不同一个工作簿间的移动和复制工作表操作。4.1Excel2016概述4.1.4工作表和单元格的操作5）.删除工作表方法2：右键单击要删除的工作表标签，在弹出快捷菜单中选择【删除】命令也可删除选中的工作表。方法1：首先选中要删除的工作表，然后打开【开始】选项卡，在【单元格】功能区中单击【删除】按钮下拉列表中的【删除工作表】命令即可。4.1Excel2016概述4.1.4工作表和单元格的操作2.单元格单元格是组成Excel工作簿的最小单位，是表格中行与列的交叉部分。一个工作表由1048576*16384个单元格构成，其中，带有绿色粗框的单元格是当前活动单元格，用于数据的输入与编辑。4.1Excel2016概述4.1.4工作表和单元格的操作下面介绍有关单元格的基本操作，包括选定单元格、编辑单元格。在单元格上单击鼠标，即可选定一个单元格；按住“Shift”键，选定一定范围的连续单元格；按住“Ctrl”键，选定非连续单元格；单击行标，选定一行，单击列标，选定一列；选定连续的多行，可在行标上拖动鼠标；选定连续的多列，在列标上拖动鼠标；全选可用“Ctrl+A”组合键，也可点击位于行标1上方，列标A左侧的全选符号;使用名称框选择;

选定单元格，在单元格上右击，即可在弹出的快捷菜单上选择插入、复制、

粘贴、删除行或列的命令以编辑单元格。4.1Excel2016概述4.1.5输入数据文本包括汉字、英文字母、特殊符号、数字、空格以及其他符号。在

Excel2016中，文本默认对齐方式是左对齐，一个单元格内最多可容纳32767个字符。在

Excel2016中，经常需输入账号、身份证号、邮政编码及以“0”开头的序号等以文本格式存放的特殊数字，可输入英文单引号“’”作为前导符，再输入数字；例如，单元格内容为01001，则需要输入“’01001”。或单击“开始”→“数字”右侧的启动器按钮，打开“设置单元格格式”对话框，选择“数字”→“分类”，设置单元格格式为“文本”类型，再输入数字。1.输入文本4.1Excel2016概述4.1.5输入数据输入数字的默认对齐方式是右对齐。如需输入分数，需在分数前输入“0”，并且在“0”和分子之间用空格间隔，否则会被识别为日期型数据，例如，要输入分数“4/5”，需输入“04/5”。如需输入负数，可在数字前加“-”号，也可将数字置于“()”中，例如，“-1”和“（1）”均表示“-1”。2.输入数字3.输入日期和时间输入日期时，可以用“/”或“-”间隔年、月、日。也可选择“设置单元格格式”→“数字”→“分类”，设置单元格格式为“日期”，并选择一种日期类型，再输入。快速输入当前日期，可使用“Ctrl+；”组合键，快速输入当前时间，可使用“Ctrl+Shift+；”组合键。4.1Excel2016概述4.1.5输入数据4.自定义格式输入日期时，可以用“/”或“-”间隔年、月、日。也可选择“设置单元格格式”→“数字”→“分类”，设置单元格格式为“日期”，并选择一种日期类型，再输入。快速输入当前日期，可使用“Ctrl+；”组合键，快速输入当前时间，可使用“Ctrl+Shift+；”组合键。5.输入公式、符号在Excel2016中，输入公式可通过单击“插入”→“符号”→“公式”/“符号”下拉按钮完成。6.输入多行数据7.多个单元格输入相同数据4.1Excel2016概述4.1.6数据填充在Excel2016中，有规律的数据可采用自动填充方式输入，当前单元格右下角绿色的小点叫作填充柄，将鼠标指针移动到该点，待鼠标指针变为实心十字光标，按住鼠标左键下拉，完成数据的自动填充。4.1Excel2016概述4.1.6数据填充采用Excel2016的自动填充可完成序号、日期、星期、月份、季度等数据的自动填充；也可选择“开始”→“编辑”→“填充”→“序列”选项，在“序列”对话框中设置步长值，选择等差序列或等比序列填充等。单击“文件”→“选项”，打开“Excel选项”对话框，单击“高级”→“常规”→“编辑自定义列表”按钮，打开“自定义序列”对话框。4.1Excel2016概述4.1.7数据编辑1.复制数据使用“Ctrl+C”组合键或选中要复制的单元格或表格区域，单击“开始”→“剪贴板”→“复制”按钮；或右击，在弹出的快捷菜单中选择“复制”命令。3.数据的清除如果想保留原有的单元格，只清除单元格的内容或者格式，则应该使用清除操作。单击“开始”→“编辑”→“清除”下拉按钮，即可看到多种选项。2.粘贴数据使用“Ctrl+V”组合键或将光标移动到需要粘贴的位置，单击“开始”→“剪贴板”→“粘贴”按钮；或右击，在弹出的快捷菜单中选择“粘贴”命令。4.1Excel2016概述4.1.7数据编辑4.数据的查找与替换对单元格内容进行查找和替换时，选择“开始”→“编辑”→“查找和选择”→“查找”选项，或使用“Ctrl+F”组合键,将出现“查找和替换”窗口，在“查找内容”中输入要查找的内容，单击“查找全部”或“查找下一个”按钮，包含查找内容的单元格就会显示出来。在“替换为”中输入要替换的内容，单击“替换”或“全部替换”按钮，即可实现某一处或全部相匹配的内容替换。4.1Excel2016概述4.1.7数据编辑5.选择性粘贴选中需要复制的单元格区域，右击被选中的区域，在打开的快捷菜单中选择“复制”命令。定位到目标粘贴位置，右击该区域左上角的单元格，然后在打开的快捷菜单中选择“选择性粘贴”命令，弹出“选择性粘贴”对话框。1）选择性粘贴-乘2）选择性粘贴-减3）选择性粘贴-加4）选择性粘贴-转置4.1Excel2016概述4.1.8修饰工作表1.设置单元格格式选择需要设置格式的单元格或单元格区域，选择“开始”→“单元格”→“格式”→“设置单元格格式”命令，或右击，在弹出的快捷菜单中选择“设置单元格格式”命令，均可打开“设置单元格格式”对话框。1）设置对齐方式2）设置单元格字体3）设置单元格边框4）设置单元格填充效果5）为单元格设置保护4.1Excel2016概述4.1.8修饰工作表2.设置行高、列宽设置行高时，选定需设置的一行或多行，将鼠标指针移动到行号间的边框上，当鼠标指针变为上下双向箭头时，按住鼠标左键拖动以调整行高；或右击，在弹出的快捷菜单上选择“行高”命令，输入行高值以调整行高。设置列宽同理。4.1Excel2016概述4.1.9样式1.条件格式选中需要设定条件格式的单元格区域，单击“开始”→“样式”→“条件格式”下拉按钮，弹出“条件格式”下拉菜单。Excel2016为用户提供了“条件格式”“套用表格格式”“单元格样式”等多种样式。在“开始”选项卡的“样式”组中可设置与修改样式。4.1Excel2016概述4.1.9样式2.套用表格格式打开需要套用格式的工作表，单击“开始”→“样式”→“套用表格格式”下拉按钮，选择表格样式，鼠标悬停即可显示样式名称。单击所需样式，打开“套用表格式”对话框，设置“表数据的来源”，设置是否包含标题，单击“确定”按钮即可完成表格样式的套用，套用表格样式后自动打开数据筛选功能。4.1Excel2016概述4.1.9样式3.单元格样式选中目标单元格，单击“开始”→“样式”→“单元格样式”按钮，弹出“单元格样式”下拉菜单，选择单元格样式，即可完成。4.1Excel2016概述4.1.10打印工作表1.打印设置1）设置页边距、页眉／页脚、工作表选项2）设置纸张方向3）设置纸张大小4）设置缩放比例4.1Excel2016概述4.1.10打印工作表2.打印单击“文件”→“打印”，选择打印机，查看打印预览效果并进行打印设置，预览效果满意后单击“打印”按钮，即可打印文档。公式和函数的应用4.24.2.1公式概述4.2.2公式的基本操作4.2.3单元格引用4.2.4函数4.2.5

错误值4.2公式和函数的应用

Excel2016的强大功能之一是公式与函数，它是Excel2016数据处理的核心。使用公式和函数可以对工作表中的数据进行各种复杂的计算。Excel20164.2公式和函数的应用4.2.1公式概述1.运算符1）算术运算符

算术运算符可完成基本的算术运算，包括加（+）、减（-）、乘（*）、除（/）、乘方（^）、百分号(%)等。如“=A1*8”、“=B1/100”。2）比较运算符比较运算符用于比较两个数值或单元格引用，并产生逻辑值True和False，包括等于（=）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）、不等于（<>）等。如“=A1>5”、“=A1<>0”。比较运算符多用于“IF”函数中的逻辑判断。4.2公式和函数的应用4.2.1公式概述1.运算符3）文本运算符文本运算符“&”用于连接一个或多个文本字符串，以生成一段文本。当用“&”连接数字或单元格引用时，数字串、单元格引用不加双引号，但对于连接字母、字符串和文本时，字母、字符串和文本必须加英文双引号。如“="计算机信息工程"&"系"”、“=A2&"公斤"”。4）引用运算符引用运算符可以将单元格区域合并运算，包括区域运算符（:）、联合运算符（，）、交叉运算符（空格）。联合运算符即两个区域运算符的并集，交叉运算符即两个区域运算符的交集，如“=SUM(A1:A8)”，运算结果8；“=SUM(A1:A8,B1:B8)”，运算结果24；“=SUM(A1:A8A1:B8)”，运算结果8。4.2公式和函数的应用4.2.1公式概述2.运算顺序对于同级运算，从等号开始从左到右运算。当公式中同时包含两种及以上的运算时，优先级由高到低依次为：引用运算符（区域运算符→联合运算符→交叉运算符）→负号→百分比→乘方→乘、除→加、减→文本运算符→比较运算符。4.2公式和函数的应用4.2.2公式的基本操作方法：首先将光标定位在相应单元格，然后输入“=”，在“=”后面输入运算公式即可。如图所示，求数据区域“D3:G3”的和，在H3单元格中输入“=D3+E3+F3+G3”按回车键（或单击编辑栏）即可自动得到运算结果。一个公式由“=”、单元格引用、运算符和常量等元素组成。1.输入公式4.2公式和函数的应用4.2.2公式的基本操作当Excel2016中同一列或同行使用相同的公式时，我们就可以使用公式填充功能。操作如下：选中单元格，先输入公式，再将鼠标移动到单元格右下角的小方格上，按住鼠标左键不放，向下拖动到指定单元格，松开鼠标可自动填充。2.填充公式4.2公式和函数的应用4.2.3单元格引用相对引用指向相对于公式所在单元格相应位置的单元格。当该公式被复制到别的单元格时，Excel可以根据复制的位置调节引用单元格。引用格式如：“A1”。

根据引用单元格的公式被复制时，新公式引用的单元格位置是否发生改变，可将引用类型分为相对引用、绝对引用和混合引用。1.相对引用2.绝对引用绝对引用指向工作表中固定位置的单元格，不随公式位置的变化而改变，就要使用绝对引用。引用格式如：“$A$1”。4.2公式和函数的应用4.2.3单元格引用3.混合引用混合引用是指公式中既有相对引用，又有绝对引用。有锁（$）的就不变，无锁（$）的就相对变化。列绝对的混合引用采用$A1、$B1等形式。行绝对的混合引用采用A$1、B$1等形式。4.跨工作表引用

跨工作表引用，需在单元格地址前加上工作表名称，工作表名称与单元格地址之间用“！”间隔即可。如“=Sheet1!A2+Sheet2!A2”。4.2公式和函数的应用4.2.4函数1.函数概述

1）函数分类

Excel2016中的函数一共有13类，分别是数学函数、统计函数、文本函数、日期函数、逻辑函数、财务函数、查找与引用函数等等。2）函数的使用方法使用函数时，只需要在指定单元格中输入“=”，后接函数名和函数参数。Excel2016中函数的使用主要有三种方法：

（1）直接输入函数名。

（2）使用快捷函数。

（3）使用函数向导。4.2公式和函数的应用4.2.4函数2.定义名称

单击“公式”→“定义的名称”→“定义名称”按钮，打开“新建名称”对话框，输入名称，确定引用位置，然后单击“确定”按钮即可。4.2公式和函数的应用4.2.4函数3.常见函数及使用

1）求最大值函数MAX()语法：MAX(Number1,Number2,…)功能：返回一组值中的最大值。例如：=MAX(A1:A6)，返回A1:A6之间的最大值。（1）常用函数2）求最小值函数MIN()语法：MIN(Number1,Number2，…)功能：返回一组值中的最小值。例如：=MIN(A1:A6)，返回A1:A6之间的最小值。3）四舍五入函数ROUND()语法：ROUND(Number,Num_digits)。功能：按指定的位数Num_digits对参数进行四舍五入。例如：=ROUND(12.155,1)，返回12.2。4）向下取整函数INT()语法：INT(Number)。功能：将数字向下舍入到最接近的整数。例如：=INT(7.9)，返回7。4.2公式和函数的应用4.2.4函数3.常见函数及使用

5）绝对值函数ABS()语法：ABS(Number)。功能：求参数的绝对值。例如：=ABS(-2)，返回2。（1）常用函数（6）求行号函数ROW()语法：ROW(reference)。功能：返回当前单元格或参数单元格的行号。例如：=ROW()，返回单元格所在行数。（7）随机数函数RAND()语法：RAND()。功能：返回大于等于0及小于1的均匀分布随机实数，每次计算工作表时都将返回一个新的随机实数。例如：=RAND(),返回大于等于0及小于1的随机实数；=INT(RAND()*60+40),返回60~100之间的随机整数；

=INT(RAND()*2000+50),返回2000~2050之间的随机年份。4.2公式和函数的应用4.2.4函数3.常见函数及使用

1）求和函数SUM()语法：SUM(Number1,Number2,…)。功能：返回参数或由参数给出的单元格区域的和。例如，单元格A2：E2包含2，20，30，40和50，则：SUM(A2:C2)等于52；SUM(B2:D2,15)等于105；SUM(A2,D2)等于42。（2）求和函数2）条件求和函数SUMIF()语法：SUMIF（range,criteria,sum_range）功能：按给定条件对符合条件的数据求和。4.2公式和函数的应用4.2.4函数3.常见函数及使用

3）多条件求和函数SUMIFS()语法：SUMIFS(sum_range,criteria_range1,criteria1[,criteria_range2,criteria2]…)。（2）求和函数4）积和函数SUMPRODUCT()语法：SUMPRODUCT(array1,array2,array3…)。功能：先计算出各个数组或区域内位置相同的元素之间的乘积，然后再计算出他们的和。4.2公式和函数的应用4.2.4函数3.常见函数及使用

1）平均值函数AVERAGE()语法：AVERAGE(Number1,Number2…)。功能：返回参数或由参数给出的单元格区域的平均值。例如，如:A1：A5,其中的数值分别为2、5、3、10和15，那么：=AVERAGE(A1:A5)等于7。（3）平均值函数2）条件平均值函数AVERAGEIF()语法：AVERAGEIF(range，criteria，Average_range)。（3）多条件平均值函数AVERAGEIFs()语法：AVERAGEIFs(Average_range，criteria_range1,criteria1…)。功能：返回满足多重条件的所有单元格的平均值（算术平均值）。4.2公式和函数的应用4.2.4函数3.常见函数及使用

（4）计数函数1）统计单元格个数函数COUNT()语法：COUNT(Value)。功能：统计指定区域中包含数值的单元格个数，只对包含数值的单元格计数。2）统计单元格个数函数COUNTA()语法：COUNTA(Value)。功能：统计指定区域中不为空的单元格的个数。3）条件计数函数COUNTIF()语法：COUNTIF(range,criteria)。功能：按给定条件对符合条件的数据计数。例如，在“学生信息表”中统计J2:J31中性别为“男”的学生人数，可使用如下公式：=COUNTIF(J2:J31,"男")。4）多条件计数函数COUNTIFS()语法：COUNTIFS(range1，criteria1，range2，criteria2)功能：将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。4.2公式和函数的应用4.2.4函数3.常见函数及使用

（5）逻辑函数语法：IF(logical_test,value_if_true,value_if_false)。功能：进行条件判断，根据逻辑判断的真假值，返回不同结果。例如：IF(F2>=60,"及格","不及格")，表示当F2单元格的值大于等于60时，函数返回值为“及格”，否则为“不及格”。4.2公式和函数的应用4.2.4函数3.常见函数及使用

（6）排名函数语法：RANK(Number，ref，[order])。功能：求某一个数值在某一区域内的排名。例如，在“学生成绩表”中学生总分在D2:D10单元格，若要把总分D2进行班级总分排名，可使用如下公式：=RANK(D2,$D$2:$D$10)。4.2公式和函数的应用4.2.4函数3.常见函数及使用

（7）纵向查找函数语法：VLOOKUP（lookup_value,table_array,col_index_num,[range_lookup]）功能：查找值，查找区域，返回查找区域的第几列的值，[查找方式：近似true、精确false]）模糊查找时，列是按升序排列，才能找到小于查找值的最大值。（8）INDEX函数语法：INDEX(array,row_num,[column_num])。解释：INDEX（数组或区域，行号，列号）。功能：INDEX函数返回表格或区域中的值或值的引用4.2公式和函数的应用4.2.4函数3.常见函数及使用

（9）MATCH函数语法：MATCH(lookup_value,lookup_array,[match_type])功能：(1)确定列表中某个值的位置；(2)对某个输入值进行检验，确定这个值是否存在某个列表中；(3)判断某列表中是否存在重复数据；(4)定位某一列表中最后一个非空单元格的位置。查找文本值时，函数MATCH不区分大小写字母。4.2公式和函数的应用4.2.4函数3.常见函数及使用

（10）日期时间函数常见的日期时间函数如下表函数功能示例结果NOW()返回当前日期和时间的序列号=NOW()2023/6/1116:50YEAR()返回某日期对应的年份=YEAR(2023-06-11)2023MONTH()返回以序列号表示的日期中的月份=MONTH(2023-06-11)6DAY()返回以序列号表示的日期中的天数=DAY(2023-06-11)11HOUR()返回时间值的小时数=HOUR(NOW())16MINUTE()返回时间值中的分钟，为一个介于0到59之间的整数。=MINUTE(NOW())50TODAY()返回当前系统日期=TODAY()2023/06/114.2公式和函数的应用4.2.4函数3.常见函数及使用

（11）文本类函数常见的文本类函数如下表函数功能示例结果MID()截取字符串函数=MID(20230611,5,2)06LEFT()左侧截取字符串函数=LEFT(20230611,2)20RIGHT()右侧截取字符串函数=RIGHT(20120611,2)11CONCATENATE()将几个文本项合并为一个文本项=CONCATENATE(F2,“分”)80分TRIM()删除空格函数=TRIM(计算机)计算机LEN()字数个数函数=LEN(计算机)34.2公式和函数的应用4.2.5错误值

Excel公式与函数错误值一般以“#”开头，错误值出现原因及举例如下表。错误值错误值出现原因举例#DIV/0除数为0=5/0#N/A函数或公式中没有可用数值

#NAME?出现了不能识别的文本=SUN(A1:A5)#NULL!交集为空=SUM(A1:A5B1:B5)#NUM!数据类型不正确=SQPT(-5)#REF!删除了由其他公式引用的单元格引用的单元格被删除#VALUE!不正确的参数或运算符=1+“a”########单元格宽度不够，加宽即可数据分析处理4.31.建立和删除数据列表2.获取外部数据3.数据排序与筛选4.数据工具5.分类汇总6.数据透视表7.数据透视图4.3

数据分析处理1.

建立和删除数据列表1)建立数据列表

单击“插入”→“表格”→“表格”按钮，弹出“创建表”对话框。在表数据的来源中选择需管理和分析的数据区，单击“确定”，完成数据列表的建立。2)删除数据列表删除数据列表时，单击“表格工具-设计”→“工具”→“转换为区域”按钮即可。4.3

数据分析处理2.获取外部数据1)获取网站数据

2)获取文本数据

4.3

数据分析处理3.数据排序与筛选(1)排序

1）简单排序2）多关键字排序3）自定义序列排序

选中数据区域中任意单元格，单击【数据】选项卡的【排序和筛选】功能区中的【排序】按钮，选择【主要关键字】和【升序】或【降序】次序即可完成筛选。4.3

数据分析处理3.数据排序与筛选(2)筛选

1）自动筛选2）高级筛选4.3

数据分析处理4.数据工具1）删除重复项

单击“数据”→“数据工具”→“删除重复项”按钮，即可打开“删除重复项”对话框。选择一个或多个包含重复值的列，即可删除该列重复数据。分列是将单列文本拆分为多列。分列前，选中需分列的数据，单击“数据”→“数据工具”→“分列”按钮，打开“文本分列向导第1步，共3步”对话框，选择最合适的分列类型，单击“下一步”按钮，设置分隔符号或设置字段宽度（列间隔），单击“下一步”按钮，设置导入列的数据格式及目标区域，也可选中“不导入此列”单选按钮放弃部分数据的导入，单击“完成”按钮即可。2）分列

4.3

数据分析处理4.数据工具3）数据验证

选择单元格或单元格区域，单击“数据”→“数据工具”→“数据验证”按钮，即可打开“数据验证”对话框。单击“设置”选项卡，在验证条件中选择对应的允许值。4)合并计算

Excel2016的合并计算可实现求和、求平均值、求最大值等一系列合并功能。选中“合并”工作表的A1单元格，单击“数据”→“数据工具”→“合并计算”按钮，打开“合并计算”对话框。4.3

数据分析处理5.分类汇总

分类汇总是实现数据快速汇总的一种方法。利用分类汇总，Excel2016可根据分类字段汇总相关内容并自动分级显示。1）数据分类2）创建分类汇总3）删除分类汇总4.3

数据分析处理6.数据透视表1）创建数据透视表2）修改数据透视表3）删除数据透视表4）数据透视表的使用7.数据透视图打开工作表，单击“插入”→“图表”→“数据透视图”按钮，打开“创建数据透视图”对话框，与创建数据透视表步骤相同，之后将所需字段拖拽添加到“轴”“图例”和“值”区域，即可完成创建。图表操作4.44.4.1迷你图的创建和编辑4.4.2图表的创建和编辑4.4.1

迷你图的创建和编辑1.迷你图的创建选中要绘制迷你图的一行数据，单击“插入”→“迷你图”→“折线图”按钮，在随即打开的“创建迷你图”对话框中，设置正确的数据范围和位置范围，最后，单击“确定”按钮关闭对话框。4.4图表操作2.迷你图的编辑迷你图创建以后，功能区会显示“迷你图工具-设计”选项卡，利用它可以对迷你图进行丰富的格式化操作。4.4

图表操作Excel2016为用户提供了15种图表类型，包括柱形图、折线图、饼图、条形图、面积图、XY散点图、股价图、曲面图、雷达图、树状图、旭日图、直方图、箱形图、瀑布图和组合等。4.4.2

图表的创建和编辑4.4.2

图表的创建和编辑1.图表的创建在Excel中切换到“插入”选项卡，设置正确的数据范围，在“图表”组中单击想插入的图表类型，基于选中数据所绘制的图表自动显示在该工作表中。4.4图表操作2.图表的编辑图表创建的同时，功能区中就会显示“图表工具”选项卡，利用它可以对图表进行丰富的格式化操作。如：图表布局、图表样式、更改图表类型、更改图表样式。Excel2016综合实例

4.51.制作“教工基本信息表”2.对“教工基本信息表”进行数据处理3.制作“学生期末考试成绩表”图表4.5Excel2016综合实例1.制作“教工基本信息表”教务管理部门需要使用Excel2016工作簿来保存学校所有教工的基本信息，要制作一张教工基本信息表，效果如下图所示。下面介绍制作“教工基本信息表”的相关操作。1）新建工作表运行Excel2016，新建“学校信息管理”工作簿，将Sheet1工作表重命名“教工基本信息表”。2）输入信息在A1单元格中输入“教工基本信息表”；在A2:K2单元格区域分别输入教工号、姓名、性别、部门、职务、职称、参加工作时间、出生日期、身份证号、联系电话、基本工资；在B3:B10单元格区域输入教工姓名，如下图所示。4.5Excel2016综合实例3）填充教工号使用自动填充方式输入工号。在A3单元格中输入第一个人的工号“202301”,将鼠标移到A3单元格右下角，会出现实心十字形，按住Ctrl键向下拖动鼠标，会自动填充系列，实现快速完成其他工号的输入，如下图所示。4.5Excel2016综合实例4）填充职称使用自动填充方式输入职称。202301-202303号教师的职称是“教授”，在F3单元格中输入“教授”，将鼠标移到F3单元格右，当出现实心十字形时，按住鼠标向下拖动，完成三位“教授”的职称输入。注意，此时不用按Ctrl键，填充区域右下角会出现智能标记按钮

，如图所示，可以单击

来修改自动填充方式。其他教师的职称可以使用同样的方法输入。4.5Excel2016综合实例5）填充参加工作日期输入参加工作日期。输入日期时，年、月、日之间的间隔可以是“-”，也以是“/”、即输入“1995-08-30”和“1995/8/30”结果一样，都会以默认日期格式“1995/8/30”显示。使用有效性控制方法输入职务、部门信息。由于学校的职务和部门设置都只有几个固定的值，所以可以使用有效性控制方法输入。选中E3单元格，单击“数据”选项卡→“数据工具”组→“数据验证”按钮，打开如下图所示对话框。在“设置”选项卡中，“允许”选择“序列”，在“来源”框中输人“教师,班主任,行政人员”，注意逗号是半角符号；勾选“提供下拉箭头”复选框。6）填充职务、部门信息4.5Excel2016综合实例4.5Excel2016综合实例单击“确定”按钮后，回到E3单元格，其右侧会出现一个按钮，单击它即可通过选择的方式来给单元格输入值，如下图所示。将鼠标移到E3单元格右下角，当出现实心十字形时，按住鼠标向下拖动进行自动填充，使每个教工的职务都能通过下拉列表方式选择输入。用相同的方法输人各个教工所在部门的值。6）填充职务、部门信息4.5Excel2016综合实例输入身份证号。身份证号是18位的数字字符，输入时，在数字前面要加英文半角的单引号“’”；为防止输入错误，设置数据验证：选中准备输入身份证号的单元格区域，单击“数据”选项卡→“数据工具”组→“数据验证”按钮，设置数据验证条件，并在“出错警告”选项卡中设置出错警告。当输入身份证号的长度不够或多于18位时，会弹出警告信息。7）填充身份证号信息4.5Excel2016综合实例8）填充性别信息从身份证号中提取性别信息到性别列。身份证号的第17位代表性别，偶数为女，奇数为男，故在C3单元格中输入公式“=IF(MOD(MID(I3,17,1),2)=0,"女","男")”，按Enter键或单击编辑栏上的“√”，如下图所示。利用自动填充方式复制公式到C4：C10单元格区域。4.5Excel2016综合实例9）填充出生日期信息从身份证号中提取出生日期到出生日期列。身份证号中的第7~10位是出生年份，第11~12位是月份，第13~14位是日，故可在H3单元格中输入公式:“=DATE(MID(I3,7,4),MID(I3,11,2),MID(I3,13,2))”，如下图所示。确定后，再利用自动填充方式复制公式到H4：H10单元格区域。4.5Excel2016综合实例10）填充联系电话信息输入联系电话。电话号码是数字字符，在输入之前，选中J列，选择“设置单元格格式”的数字为“文本”。为减少输入错误，可以设置联系电话区域的数据验证条件为“文本长度”，以及相应的错误警告信息。设置完成后，再输入数据。11）填充基本工资信息输入基本工资值并设置工资数据格式。选中要输入工资值的单元格区域，执行“数据”选项卡中的“数据验证”命令，在打开的对话框中设置验证条件为允许“小数”、介于“3000-30000，设置出错警告信息为“输入的值超出学校工资范围!”。选中“基本工资”列的数据区域，在“设置单元格格式”对话框中的“数据”选项卡中，选择“货币”类，小数位数设为“2”，货币符号为“¥”。4.5Excel2016综合实例12）高亮工资信息将工资高于8000的数据用红色字体显示。可以通过条件格式化快速实现，选中“基本工资”列的数据，单击“开始”→“样式”组→“条件格式”按钮，在下拉菜单中选择“大于(G)…”，在弹出的所示对话框中输入“8000”，在格式下拉框中选择要设置的格式，如“红色文本”。13）美化表格利用“样式”快速美化表格。选中A2:K10单元格区域，单击“开始”→“样式”组→“套用表格格式”按钮，在预览下拉框中单击“表样式浅色9”，弹出“套用表格格式”对话框，单击“确定”按钮后，效果如下图所示。4.5Excel2016综合实例4.5Excel2016综合实例2.对“教工基本信息表”进行数据处理1）对“教工基本信息表”的数据进行排序，要求按照主要关键字“部门”，次序为升序排序，按照次要关键字“职务”次序为升序进行排序。具体要求如下所示：2）把教工基本信息表内容复制到Sheet2中，并对数据进行自动筛选，要求筛选出所有职务为“教师”，性别为“男”的教师信息。3）

把教工基本信息表内容复制到Sheet3中，并对数据进行高级筛选，要求筛选出所属部门为“计算机”，并且职务为“教师”的教工信息，条件区域在“A19:B20”单元格中，在原有区域显示筛选结果。下面介绍对“教工基本信息表”进行数据处理的相关操作。4.5Excel2016综合实例1）排序打开“教工基本信息表”，选取数据区域任意单元格，单击【数据】选项卡的【排序和筛选】功能区中的【排序】按钮，点击【添加条件】按钮添加次要关键字，设置【主关键字】为“部门”、排序依据默认次序为【升序】，【次要关键字】为“职务”、排序依据默认次序为【升序】。如下图所示。4.5Excel2016综合实例2）自动筛选添加新工作表Sheet2，把“教工基本信息表”所有数据复制到Sheet2工作表，选取数据区域任意单元格，在【数据】选项卡的【排序和筛选】功能区中选择【筛选】按钮，在每个字段名后会出现一个下拉按钮，单击“职务”字段右边下拉按钮，在弹出的列表中只勾选“教师”项，单击【确定】按钮。再单击“性别”字段右边下拉按钮，在弹出的列表只勾选“男”项，单击【确定】按钮即可，筛选后的结果如下图所示。4.5Excel2016综合实例3）高级筛选添加新工作表Sheet3，把“教工基本信息表”所有数据到Sheet3工作表，在“A12:B13”单元格中设置高级筛选条件。选取数据区域任意单元格，单击【数据】选项卡的【排序和筛选】功能区中的【高级】按钮，在弹出对话框中设置：选择【在原有区域显示筛选结果】、列表区域“A2:K10”、条件区域：“A12:B13”，单击【确定】按钮即可，高级筛选结果如下图所示。4.5Excel2016综合实例3.制作“学生期末考试成绩表”图表1）按下图所示建立“学生期末考试成绩表”，主要包括输入标题、学号、班级等列名和数据，将标题行按照下图中所示合并单元格。具体要求如下所示：4.5Excel2016综合实例4.5.3制作“学生期末考试成绩表”图表2）利用函数学生成绩总分、平均分、每门课程的最高分、最低分。其中平均分为数值型数据，保留小数点后0位有效数字。4.5Excel2016综合实例3）利用每名学生各科成绩，