6.4 Excel 2016的公式与函数

6.4.1 公式的概念

Excel中的公式即对工作表中的数据进行计算的等式，以“=（等号）”开始，通过各种运算符号，将值或常量和单元格引用、函数返回值等组合起来，形成公式表达式。

6.4.2 公式的使用

1．输入公式
选择要输入公式的单元格，在单元格或编辑栏中输入“=”，接着输入公式内容，如“=B3+C3+D3+E3”，完成后按“Enter”键或单击编辑栏上的“输入”按钮即可。

2．编辑公式
选择含有公式的单元格，将文本插入点定位在编辑栏或单元格中需要修改的位置，按“Backspace”键删除多余或错误的内容，再输入正确的内容，完成后按“Enter”键确认即可完成公式的编辑，编辑完成后，Excel将自动对新公式进行计算。

3．填充公式
选择已添加公式的单元格，将鼠标指针移至该单元格右下角的控制柄上，当其变为形状时，按住鼠标左键不放并拖动至所需位置，释放鼠标，即可在选择的单元格区域中填充相同的公式并计算出结果。

4．复制和移动公式
在复制公式的过程中，Excel会自动调整引用单元格的地址，避免手动输入公式的麻烦，提高工作效率。
移动公式即将原始单元格的公式移动到目标单元格中，公式在移动过程中不会根据单元格的位移情况发生改变。

6.4.3 单元格的引用

1．单元格引用类型
相对引用：输入公式时直接通过单元格地址来引用单元格。相对引用单元格后，如果复制或剪切公式到其他单元格，那么公式中引用的单元格地址会根据复制或剪切的位置而发生相应改变。

绝对引用：无论引用单元格的公式位置如何改变，所引用的单元格均不会发生变化。绝对引用的形式是在单元格的行列号前加上符号“＄”。

混合引用：包含相对引用和绝对引用。有两种形式，一种是行绝对、列相对，如“B＄2”，表示行不发生变化，但是列会随着新的位置发生变化；另一种是行相对、列绝对，如“＄B2”，表示列保持不变，但是行会随着新的位置而发生变化。

2．同一工作簿不同工作表的单元格引用
在同一工作簿中引用不同工作表中的内容，需要在单元格或单元格区域前标注工作表名称，表示引用该工作表中该单元格或单元格区域的值。

3．不同工作簿不同工作表的单元格引用
在Excel中不仅可以引用同一工作簿中的内容，还可以引用不同工作簿中的内容，为了操作方便，可将引用工作簿和被引用工作簿同时打开。

6.4.4 函数的使用

1．Excel 中的常用函数

SUM函数、AVERAGE函数、IF函数、COUNT函数、MAX/MIN函数

2．插入函数
选择要插入函数的单元格后，单击编辑栏中的“插入函数”按钮，在打开的“插入函数”对话框中选择函数类型后，单击“确定”按钮即可插入。

选择要插入函数的单元格后，在【公式】/【函数库】组中单击“插入函数”按钮，在打开的“插入函数”对话框中选择函数类型后，单击“确定”按钮即可插入。

选择要插入函数的单元格后，按“Shift+F3”组合键，打开“插入函数”对话框，在其中选择所需函数类型后，单击“确定”按钮即可插入。

6.4.5 快速计算与自动求和

1．快速计算
选择需要计算单元格之和或单元格平均值的区域，在Excel操作界面的状态栏中将可以直接查看计算结果，包括平均值、单元格个数、总和等。

2．自动求和
选择需要求和的单元格，在【公式】/【函数库】组中单击“自动求和”按，此时，即可在当前单元格中插入求和函数“SUM”，同时Excel将自动识别函数参数，单击编辑栏中的“输入”按钮或按“Enter”键，完成求和计算。

6.5 Excel 2016的数据管理

6.5.1 数据排序

1．快速排序
如果只对某一列进行简单排序，可以使用快速排序法来完成。选择要排序列中的任意单元格，单击【数据】/【排序和筛选】组中的“升序”按钮或“降序”按钮，即可实现数据的升序或降序操作。

2．组合排序
在对某列数据进行排序时，如果遇到多个单元格数据值相同的情况，可以使用组合排序的方式来决定数据的先后。组合排序是指设置主、次关键字升序排序。

3．自定义排序
自定义排序可以通过设置多个关键字对数据进行排序，并可以通过其他关键字对相同排序的数据进行排序。Excel提供了内置的日期和年月自定义列表，用户也可根据实际需求自己设置。

6.5.2 数据筛选

1．自动筛选
自动筛选数据即根据用户设定的筛选条件，自动显示符合条件的数据，隐藏其他数据。通过【数据】/【排序和筛选】组中的“筛选”按钮进行自动筛选。

2．自定义筛选
自定义筛选建立在自动筛选基础上，可自动设置筛选选项，更灵活地筛选出所需数据。

3．高级筛选
如果想要根据自己设置的筛选条件来筛选数据，则需要使用高级筛选功能。高级筛选功能可以筛选出同时满足两个或两个以上约束条件的数据。

6.5.3 分类汇总

1．单项分类汇总
在创建分类汇总之前，应先对需分类汇总的数据进行排序，然后选择排序后的任意单元格，单击【数据】/【分级显示】组中的“分类汇总”按钮，打开“分类汇总”对话框，在其中对“分类字段”“汇总方式”“选定汇总项”等进行设置，设置完成后单击“确定”按钮。

2．嵌套分类汇总
在完成基础分类汇总后，单击【数据】/【分级显示】组中的“分类汇总”按钮，打开“分类汇总”对话框，在“分类字段”下拉列表框中选择一个新的分类选项，再对汇总方式、汇总项进行设置，撤销选中“替换当前分类汇总”复选框，单击“确定”按钮，即可完成嵌套分类汇总的设置。

6.5.4 分组显示

1．显示或隐藏
单击按钮。

单击“展开”按钮，显示该分类中的明细数据；单击“折叠”按钮，隐藏该分类中的明细数据。

单击级别条，将隐藏该分类中的明细数据。

2．自行创建分组显示
对于未进行分类汇总的工作表，可以自行创建分组显示，其操作方法为在数据列表的任意位置单击定位，对进行分组的数据列进行排序，然后选择同一级别的数据行或列，在“数据”/“分级显示”组中单击“创建组”按钮，所选行或列将关联为一组。在“数据”/“分级显示”组中单击“创建组”按钮下方的下拉按钮，在打开的下拉列表中选择“自动建立分级显示”选项，可以自动创建分组显示。

3．删除分组显示
在包含了分组显示的工作表中，在“数据”/“分级显示”组中单击“取消组合”按钮下方的下拉按钮，在打开的下拉列表中选择“清除分级显示”选项。

4．复制分组显示的数据

6.5.5 合并计算

如果需要将几张工作表中的数据合并到一张工作表中，可以使用Excel的合并计算功能。

6.6 Excel 2016的图表

6.6.1 图表的概念

数据系列：图表中的相关数据点，代表着表格中的行、列。图表中每一个数据系列都具有不同的颜色和图案，且各个数据系列的含义将通过图例体现出来。在图表中，可以绘制一个或多个数据系列。

坐标轴：度量参考线，X轴为水平轴，通常表示分类，Y轴为垂直坐标轴，通常表示数据。

图表标题：图表名称，一般自动与坐标轴或图表顶部居中对齐。

数据标签：为数据标记附加信息的标签，通常代表表格中某单元格的数据点或值。

图例：表示图表的数据系列，通常有多少数据系列，就有多少图例色块，其颜色或图案与数据系列相对应。

6.6.2 图表的建立与设置

1．创建图表
图表是根据Excel表格数据生成的，在插入图表前，需要先编辑Excel表格中的数据。选择数据区域，在【插入】/【图表】组中单击“推荐的图表”按钮，打开“插入图表”对话框，在“推荐的图表”选项卡中选择所需的图表类型后，单击“确定”按钮，即可在工作表中创建图表。

2．设置图表
在默认情况下，图表将被插入到编辑区中心位置，需要对图表位置和大小进行调整。选择图表，将鼠标指针移动到图表中，按住鼠标左键不放可拖动调整其位置；将鼠标指针移动到图表4个角上，按住鼠标左键不放可拖动调整图表的大小。

6.6.3 图表的编辑

1．编辑图表数据
如果表格中的数据发生了变化，如增加或修改了数据时，Excel会自动更新图表。如果图表所选的数据区域有误，则需要用户手动进行更改。

2．设置图表位置
选择【图表工具 设计】/【位置】组，单击“移动图表”按钮，打开“移动图表”对话框，单击选中“新工作表”单选项，即可将图表移动到新工作表中。

3．更改图表类型
选择图表，再选择【图表工具 设计】/【类型】组，单击“更改图表类型”按钮，在打开的“更改图表类型”对话框中重新选择所需图表类型。

4．设置图表样式
Excel为用户提供了多种预设布局和样式，可以快速将其应用于图表中，其方法为选择图表，选择【图表工具 设计】/【图表样式】组，在列表框中选择所需样式即可。

5．设置图表布局
除了可以为图表应用样式外，还可以根据需要更改图表的布局，其方法为选择要更改布局的图表，在【图表工具 设计】/【图表布局】组中选择合适的图表布局即可。

6．编辑图表元素
选择【图表工具 设计】/【图表布局】组，单击“添加图表元素”按钮，在打开的下拉列表中选择需要调整的图表元素，并在子列表中选择相应的选项即可。

6.6.4 快速突显数据的迷你图

选择需要插入的一个或多个迷你图的空白单元格或一组空白单元格，在【插入】/【迷你图】组中选择要创建的迷你图类型，在打开的“创建迷你图”对话框的“数据范围”数值框中输入或选择迷你图所基于的数据区域，在“位置范围”数值框中选择迷你图放置的位置，单击“确定”钮，即可创建迷你图。

6.6.5 数据透视表

1．创建数据透视表
创建数据透视表后，在指定的工作表区域可查看创建的数据透视表，它主要由数据透视表布局区域和数据透视表字段列表构成，二者的特点及作用分别如下。

• 数据透视表布局区域：指生成数据透视表的区域，可以在字段列表区域中选中字段名旁边的复选框，或右键单击某个字段名并选择该字段要移动到的位置。
• 数据透视表字段列表：数据透视表字段列表区域用于显示数据源中的列标题。每个标题都是一个字段，例如，“日期”“原料”“费用”等。

2．设置数据透视表
（1）更改字段
更改数据透视表字段，实质上就是对数据透视表中已经添加的字段进行移动或删除。
（2）排序和筛选
在数据透视表中对字段进行排序与筛选的方法主要有以下5种。
自动排序、其他排序、在下拉列表框中筛选数据、通过标签筛选数据、通过值筛选数据
（3）刷新数据
当源数据发生变动后，在数据透视表中的数据不会同时更改，而需要对数据进行刷新操作。

6.6.6 数据透视图

1．创建数据透视图

• 使用原始数据创建
• 使数据透视表创建

2．设置数据透视图

更改图表类型、更改图表布局、添加图表元素、筛选图表中的数据、更改数据源