Excel 2016应用大全pdf

Excel 2016应用大全 内容简介

Microsoft Excel是Microsoft Office办公组件中的重要组成部分，在企业日常办公中被广泛应用。本书根据现代企业日常办公的需要，详细地介绍了Microsoft Excel的新版本Excel 2016的基本操作，公式、函数、图表以及数据处理等内容。同时在各章的讲解中安排了应用实例，以提升读者的操作水平。

本书内容翔实，实例丰富，步骤详细，可操作性强，无论是初学者还是对Excel有一定了解的用户都可以通过本书轻松掌握Excel 2016的使用。本书适用于企业办公人员自学，也可作为Excel 2016的培训教材使用。

Excel 2016应用大全 目录

第1章 Excel 2016基本操作

1.1 Excel 2016的启动与退出及工作界面

1.1.1 启动Excel 2016

1.1.2 熟悉Excel 2016工作界面

1.1.3 退出Excel 2016

1.2 工作簿的基本操作

1.2.1 创建新工作簿

1.2.2 开始输入数据

1.2.3 保存和关闭工作簿

1.2.4 打开保存的工作簿

1.2.5 保护工作簿

1.3 工作表的基本操作

1.3.1 选取工作表

1.3.2 重命名工作表

1.3.3 添加与删除工作表

1.3.4 复制与移动工作表

1.3.5 保护工作表

1.4 单元格的基本操作

1.4.1 选取单元格

1.4.2 插入单元格

1.4.3 清除与删除单元格

1.4.4 复制与剪切单元格

1.5 格式化工作表

1.5.1 调整表格行高与列宽

1.5.2 设置字体格式

1.5.3 设置对齐方式

1.5.4 添加表格边框

1.5.5 自动套用格式

1.6 综合实例：制作客户信息表

第2章 数据处理

2.1 数据类型的简单认识

2.1.1 数值

2.1.2 日期和时间

2.1.3 文本

2.1.4 逻辑值

2.1.5 错误值

2.1.6 公式

2.2 输入和编辑数据

2.2.1 在单元格中输入数据

2.2.2 编辑单元格内容

2.2.3 日期和时间的输入

2.2.4 为单元格添加批注

2.3 数据输入实用技巧

2.3.1 自动换行

2.3.2 在多个单元格同时输入数据

2.3.3 分数输入

2.3.4 输入指数上标

2.3.5 自动输入小数点

2.3.6 记忆式键入

2.3.7 为中文添加拼音标注

2.4 填充与序列

2.4.1 自动填充功能

2.4.2 序列

2.5 综合实例：制作客户加盟信息表

2.5.1 输入客户加盟信息

2.5.2 添加批注以标记电话类型

第3章 公式与函数基础

3.1 认识公式与函数

3.1.1 什么是公式

3.1.2 什么是函数

3.2 单元格引用

3.2.1 相对引用

3.2.2 绝对引用

3.2.3 混合引用

3.3 公式中的运算符及其优先级

3.3.1 算术运算符

3.3.2 比较运算符

3.3.3 文本运算符

……

第4章 逻辑函数

第5章 日期与时间函数

第6章 数学与三角函数

第7章 信息函数

第8章 文本函数

第9章 数据库与Web函数

第10章 查找和引用函数

第11章 统计函数

第12章 财务函数

第13章 工程函数

第14章 创建与编辑图表

第15章 格式化与自定义图表

第16章 使用图片与图形

第17章 数据透视表分析数据

参考文献

Excel 2016应用大全 精彩文摘

10.2 查找函数

查找函数的主要功能是快速地确定和定位所需要的信息。在实际应用中，可以在工作表或者在多个工作簿中获取需要的信息及数据。本节主要讲解各个查找函数的功能。

10.2.1 应用ADDRESS函数以文本形式返回引用值

功能：根据指定行号和列号获得工作表中的某个单元格的地址。

格式：ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])。

参数：row_num（必需）。指定要在单元格引用中使用的行号。

column_num（必需）。指定要在单元格引用中使用的列号。

abs_num（可选）。指定要返回的引用类型，可用值如表10-8所示。

表10-8 abs_num参数后的引用类型

abs_num 返回的引用类型

1或省略 绝对URL

2 绝对行号，相对列标

3 相对行号，绝对列标

4 相对单元格引用

a1（可选）。一个逻辑值，指定A1或R1C1引用样式。在A1样式中，列和行将分别按字母和数字顺序添加标签。

sheet_text（可选）。一个文本值，用于指定要用作外部引用的工作表的名称。

在给出指定行号和列标的情况下，可以使用ADDRESS函数获取工作表单元格的地址。例如，ADDRESS(2,3)返回$C$2，ADDRESS(77,300)返回$KN$77。可以使用其他函数（如ROW和COLUMN函数）为ADDRESS函数提供行号和列号参数（参数：为操作、事件、方法、属性、函数或过程提供信息的值）。如果参数abs_num是表10-8指定的4个数字之外的任意数字，函数都将返回错误值#VALUE!。

下面实例，学生举行了一个庆元旦活动游戏，最后根据得奖序列号得到学生学号的单元格，具体操作步骤如下。

输入得奖的序列号，选择目标单元格D3，如图10-1所示。

单击“公式”>“函数库”>“查找与引用”>“ADDRESS”选项，弹出“函数参数”对话框，分别输入使用的行号、列号和返回的引用类型，如图10-2所示，单击“确定”按钮。

图10-1 输入得奖的序列号 图10-2 输入参数

在公式编辑栏输入公式=ADDRESS(6,1,1)，学生学号的单元格地址显示在目标单元格中，如图10-3所示。

图10-3 显示学生学号的单元格地址

提示

在Excel中，引用类型有绝对引用、相对引用和混合引用三种类型。另外引用样式有a1和R1C1两种。

若要更改Excel所使用的引用样式，可单击“文件”选项卡，选择“选项”>“公式”命令，在“使用公式”选项组中选中或清除“R1C1 引用样式”复选框。

10.2.2 应用AREAS函数计算引用中的区域个数

功能：使用AREAS函数返回引用中的区域个数。区域是指连续的单元格或者单个单元格。

格式：AREAS(reference)。

参数：reference（必需）。对某个单元格或单元格区域的引用，可同时包含多个区域。

在引用多个不连续的单元格区域时，一定要用大括号将引用区域括起来。

下面实例为某学校将每个校区的人数统计在不同的单元格内，现统计校区的总数，具体操作步骤如下。

输入各个校区的信息，选择目标单元格B3，如图10-4所示。

单击“公式”>“函数库”>“查找与引用”>“AREAS”选项，弹出“函数参数”对话框，直接输入每个校区的统计情况单元格区域，如图10-5所示，单击“确定”按钮。

图10-4 输入各个校区的信息 图10-5 输入参数

生成公式=AREAS((A5:B6,D5:E6,A8:B9,D8:E9))，校区的总数显示在目标单元格中，如图10-6所示。

图10-6 计算校区总数

提示

如果需要将几个引用指定为一个参数，则必须用括号括起来，以免Excel将逗号视为字段分隔符。

10.2.3 应用GETPIVOTDATA函数返回存储在数据透视表中的数据

功能：使用GETPIVOTDATA函数返回存储在数据透视表中的数据。如果报表中的汇总数据可见，则可以使用函数GETPIVOTDATA从数据透视表中检索汇总数据。

格式：GETPIVOTDATA(data_field, pivot_table, [field1, item1, field2, item2], ...)。

参数：data_field（必需）。包含要检索数据的数据字段名称，用引号引起来。

pivot_table（必需）。数据透视表中的任何单元格、单元格区域或命名区域的引用。

field1、Item1、field2、Item2…（可选）。描述要检索的数据的第1到126个字段名称对和项目名称对。

在函数GETPIVOTDATA的计算中可以包含计算字段、计算项及自定义计算方法。

下面实例中超市员工根据销售表做出一个冰红茶的销售数据透视表，表中包含日期、商品名称、金额、日期合计与商品合计，具体操作步骤如下。

输入销售数据表，计算每种商品的销售总额，填入“金额”列中，如图10-7所示。

使用销售数据生成数据透视表，选择目标单元格B22，用来计算销售金额，如图10-8所示。

图10-7 输入销售数据表 图10-8 计算销售金额

单击“公式”>“函数库”>“查找与引用”>“GETPIVOTDATA”选项，弹出“函数参数”对话框，在对话框中设置相应的参数，如图10-9所示，单击“确定”按钮。

生成公式=GETPIVOTDATA("金额",A13,A19,B19,A20,B20)，在目标单元格返回日期为“2013/1/1”、商品名称为“冰红茶”的销售金额，如图10-10所示。

图10-9 输入参数 图10-10 “冰红茶”的销售金额

同理，生成公式=GETPIVOTDATA("金额",A13,A19,B19)，获得日期为“2013/1/1”这一天所有的销售金额，如图10-11所示。

同理，生成公式=GETPIVOTDATA("金额",A13,A20,B20)，获得“冰红茶”的所有销售金额，如图10-12所示。

图10-11 2013/1/1销售金额 图10-12 “冰红茶”销售金额

提示

如果pivot_table并不代表数据透视表的区域，则函数GETPIVOTDATA将返回错误值#REF!。

10.2.4 应用CHOOSE函数从列表中选择数值

功能：使用CHOOSE函数返回数值参数列表中的数值。使用CHOOSE可以根据索引号从最多254个数值中选择一个。

格式：CHOOSE(index_num, value1, [value2], ...)。

参数：index_num （必需）。用于指定所选定的数值参数。index_num 必须是介于1到254之间的数字，或是包含 1到254之间的数字的公式或单元格引用。

如果index_num为1，则CHOOSE函数返回value1；如果为2，则CHOOSE函数返回value2，以此类推。

如果index_num小于1或大于列表中最后一个值的索引值，则CHOOSE函数返回#VALUE!错误值。

如果index_num为小数，则在使用前将被截尾取整。

value1, value2, ... value1是必需的，后续值是可选的。1到254个数值参数，CHOOSE函数将根据 index_num 从中选择一个数值或一项要执行的操作。参数可以为数字、单元格引用、已定义名称、公式、函数或文本。

使用index_num返回数值参数列表中的数值。使用CHOOSE函数可以根据索引值从最多254个数值中选择一个。例如，如果value1到value7表示一周的7 天，如果将1到7之间的数字用作index_num，则CHOOSE返回其中的某一天。

下面实例中选民只需要输入选票代码，根据选民的选票代码显示出被选人，并将结果填写在指定的单元格中，具体操作步骤如下。

输入各选民及选票代码，选择目标单元格C3，如图10-13所示。

单击“公式”>“函数库”>“查找与引用”>“CHOOSE”选项，弹出“函数参数”对话框，在参数中输入选择结果和三个选项，如图10-14所示，单击“确定”按钮。

图10-13 输入各选民及选票代码 图10-14 输入参数

生成公式=CHOOSE(B3,"科振涛","李福晋","章昊")，选择结果显示在目标单元格中，如图10-15所示。

图10-15 显示选择结果

提示

index_num可以为数组或引用：如果index_num为一个数组，则在计算CHOOSE函数时，将计算每一个值。

CHOOSE函数参数可以为区域引用：函数CHOOSE的数值参数不仅可以为单个数值，也可以为区域引用。

10.2.5 应用HLOOKUP函数实现水平查找

功能：使用HLOOKUP函数在表格的首行或数值数组中查找值，然后返回表格或数组中指定单元格的值。

格式：HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])。

参数：lookup_value（必需）。要在第一行中查找的值。可以为数值、引用或文本字符串。

table_array（必需）。在其中查找数据的信息表。使用对区域或区域名称的引用。

table_array第一行的数值可以为文本、数字或逻辑值。

如果range_lookup为TRUE，则table_array第一行的数值必须按升序排列：...-2、-1、0、1、2、…、A-Z、FALSE、TRUE；否则，函数HLOOKUP将不能给出正确的数值。如果range_lookup为FALSE，则table_array不必进行排序。

row_index_num（必需）。table_array中将返回的匹配值的行号。Row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。如果row_index_num小于1，则HLOOKUP返回错误值#VALUE!；如果row_index_num大于table_array的行数，则HLOOKUP返回错误值#REF!。

range_lookup（可选）。一个逻辑值，指定希望HLOOKUP查找精确匹配值还是近似匹配值。如果为TRUE或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。如果Range_lookup为FALSE，函数HLOOKUP将查找精确匹配值，如果找不到，则返回错误值#N/A。

如果函数HLOOKUP找不到lookup_value，且range_lookup为TRUE，则使用小于lookup_value的最大值。

下面实例，公司销售A、B、C三种产品，根据Excel第一行中三种产品的销售单价，利用HLOOKUP函数填充下表对应的产品单价，具体操作步骤如下。

输入产品的单价，选择目标单元格C6，如图10-16所示。

单击“公式”>“函数库”>“查找与引用”>“HLOOKUP”选项，弹出“函数参数”对话框，分别输入数据表第一行中查找的数值、查找数据的数据表、行号和逻辑值，如图10-17所示，单击“确定”按钮。

图10-16 输入产品的单价 图10-17 输入参数

生成公式=HLOOKUP(A6,$A$2:$C$3,2, FALSE)，在目标单元格中返回销售表中的单价，如图10-18所示。

提示

HLOOKUP函数与VLOOKUP函数：当比较值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数HLOOKUP；当比较值位于要查找的数据左边的一列时，可使用函数VLOOKUP。

10.2.6 应用HYPERLINK函数创建快捷方式（跳转）

功能：创建快捷方式或跳转，以打开存储在网络服务器、Intranet或Internet上的文档。

格式：HYPERLINK(link_location, [friendly_name])。

参数：link_location（必需）。要打开的文档的路径和文件名。

friendly_name（可选）。单元格中显示的跳转文本或数字值。

HYPERLINK函数可以创建快捷方式或跳转，用以打开存储在网络服务器、Intranet或Internet中的文档。

下面实例将一些网站的名称设置超链接，设为该网站的网址，具体操作步骤如下。

输入网站的网址，选择目标单元格C3，如图10-19所示。

单击“公式”>“函数库”>“查找与引用”>“HYPERLINK”选项，弹出“函数参数”对话框，分别输入网址所在单元格和网站名，如图10-20所示，单击“确定”按钮。