绝了！Excel可以这样用 - 数据处理、计算与分析 - IT168文库(5)

第5章 海量数据分析的利器——初识查找引用函数 1）问题分析

初看像是一个查找问题，使用查找函数如VLOOKUP虽然可以很方便地查找指定业务 员某个月份的销售额，但如要计算多个月份的销售额，就需要在VLOOKUP函数的第3参

OFFSET 仅是一个 数中使用数组，涉及到构建数组的技巧，略有点麻烦。而使用引用函数

最基础的应用。

2）解决方案

SUM 函数求和， C10 单元格公 可以使用引用函数返回满足条件的数据区域，再外套 式如下：

=SUM(OFFSET(A1,MATCH(A10,A2:A7,),1,,B10)) 3）公式解析

公式中使用了OFFSET函数，然后外套SUM函数求和。OFFSET函数有5个参数， 第 2 个参数使用了 MATCH 函数，查找引用的业务员姓名（ A10 单元）在 A2 ： A7 单元格 区域中的位置。整个OFFSET函数的含义是以A1单元格为起点，以MATCH函数计算结 果向下偏移，再向右偏移一个单元格，然后返回一个1行若干列（B10单元格的取值）的 区域。整个过程如图 5.27 所示。

图5.27 查询返回区域演示结果

OFFSET函数有5个参数，是Excel中参数最多的函数之一，也是超级难解的函数之 一。本章仅讲解最基础的应用，先从函数的5个参数开始理解，如表5.4所示。

表5.4 OFFSET函数5个参数的理解

参数顺序 参 数 含 义 事 项 起点，可以是单元格 第 1 参数 A1 或区域 第2参数 行方向偏移 上例中的取值 注使用区域时以左上角单元格为偏 正数（代表在起始引用的下方）或负数（代 表在起始引用的上方） 正数（代表在起始引用的右边）或负数（代 表在起始引用的左边） 省略 可以省略，负数代表向左方取数 MATCH(A10,A2:A7,) 1 第 3 参数 列方向偏移 第4参数 第5参数 返回引用区域的行数 可以省略，负数代表向上方取 备注：Excel的帮助文件中在提到OFFSET函数的第4～5参数时有错，这两个参数可 以使用负数。

·147·

返回引用区域的列数 B10

第2篇 计算数据 初次接触OFFSET函数会比较难理解，这里将以几个示意图来帮助理解。 1．省略第4、5参数

图 5.28 中使用的公式为“ =OFFSET(A1,5,4) ”， OFFSET 函数以第 1 参数 A1 单元格为 起点，按第2参数向下偏移，按第3参数向右偏移，在省略了第4、5参数的情况下，返回 区域同第1参数的大小。

图5.28 OFFSET函数演示1

2．省略第5参数

图5.29中使用的公式为“=OFFSET(A1,5,4,3)”，这个公式有点类似于“查找返回区 域”的案例，OFFSET函数以第1参数A1单元格为起点，按第2参数向下偏移，按第3 参数向右偏移，在第 5 参数省略的情况下，以第 4 参数为返回区域的高度，以第 1 参数的 宽度为返回区域的宽度，返回3 *1 的区域。

图5.29 OFFSET函数演示2

图5.29中A12单元格返回值出错，原因在于函数返回的是一个单元格区域而不是一个 单元格，把单元格区域放在单元格中，大小不匹配所以报错。使用F9键的结果为{65;75;85}， 这个运算结果可以作为其他函数的参数，如再外套SUM函数等。

3．省略第4参数

图5.30中使用的公式为“=OFFSET(A1:C3,5,4,,4)”，OFFSET函数第1个参数使用了

·148 ·

第5章 海量数据分析的利器——初识查找引用函数 区域A1：C3，但仍以区域左上角单元格A1为起点，按第2参数向下偏移，按第3参数向 右偏移，在第4参数省略时以第1参数的高度作为返回区域的高度，返回区域的宽度使用 第5参数。

图5.30 OFFSET函数演示3

通过以上3个演示，可以得出关于OFFSET函数的以下几个规律：

? OFFSET函数有5个参数，其中第4、5参数是可以省略的； ? OFFSET函数第1参数可以是一个单元格区域，但仅以该区域左上角单元格为偏移 的起点； ? 在省略第4、5参数的情况下，以第1参数的大小作为返回区域的大小； ? OFFSET函数返回的结果可以是一个单元格，也可以是一个单元格区域。

5.7.2 谈谈查找函数和引用函数

MATCH 是查找函数， 本章中重点讲述了 6 个函数，其中 VLOOKUP 、 LOOKUP、INDEX、INDIRECT和OFFSET属于引用函数，同属查找与引用函数大类，其实并不完全 一样。

区分查找与引用函数，可以看一下如下示例，如图5.31，计算业务员D在6月份的销 售额。

图5.31 多种公式查找业务员销售额

·149·

第2篇 计算数据 这个问题可以使用VLOOKUP、INDEX、OFFSET和INDIRECT4种函数组合，公式 分别如下：

=VLOOKUP(A11,A2:G7,MATCH(B11&\月\ =INDEX(B2:G7,MATCH(A11,A2:A7,),MATCH(B11&\月\ =OFFSET(A1,MATCH(A11,A2:A7,),MATCH(B11&\月\ =INDIRECT(\月\ 除了最后一个公式使用了INDIRECT函数的R1C1引用样式外，其他的函数组合并不

D 在 1 陌生，返回结果也一样。但如需要利用这个公式继续计算业务员 ～ 6 月的销售额，

查找和引用函数的效果就不一样了。

以B5单元格使用区域运算符（冒号）连接上述公式，再外套SUM函数求和，仅在使 用引用函数时能返回正确的结果，查找函数甚至无法计算，公式直接报错。正确的公式 如下：

=SUM(B5:INDEX(B2:G7,MATCH(A11,A2:A7,),MATCH(B11&\月\ =SUM(B5:OFFSET(A1,MATCH(A11,A2:A7,),MATCH(B11&\月\ =SUM(B5:INDIRECT(\月\ 从这个例子可以看出查找函数和引用函数还是有差异的，这里建议读者了解以下几点：

? 在返回值为单个单元格时，使用查找函数和引用函数的结果没有区别； ? 查找函数能做的工作，使用引用函数也能完成；

? 反之，一部分引用函数的工作就不能用查找函数来实现。 关于查找函数与引用函数的差异，在后续章节中还会举例讲解。 本节示例文件：《 5.7 查找返回指定区域 .xlsx 》。

5.8 查找结果报错的处理

使用函数出错是难免的，关于各种错误返回值的形成原因已在第4章中讲述，这里再 介绍一下具体的处理方法。

5.8.1 使用IFERROR函数消错

，原因在于某些货号在本期没有销 如图 5.32 所示的例子，查找结果中出现了“ #N/A”售，在“销售汇总”中查找不到就返回了错误值。

对于查找函数返回的错误，不加处理不仅表格不够美观，而且会影响到之后的计算结 果（如上例的“期末结存”列）。

从2007版开始，Excel提供了一个全新的消错函数IFERROR，使用这个函数可以快 速屏蔽函数计算结果中的错误。

在上例中，在D2单元格中录入如下公式，并向下复制，对于之前显示错误值的地方， 全部返回 0 值，也不会影响到 E 列“期末结存”的计算结果。

·150 ·

第5章 海量数据分析的利器——初识查找引用函数

图5.32 VLOOKUP查找未消错

=IFERROR(VLOOKUP(A2,$G$1:$H$13,2,0),0) IFERROR函数有2个参数，第1个参数是需要消错的公式，第2个参数是第1参数计

1 参数的结果出错则返回第 2 参数，否 算结果出错时返回的内容。运算结果可以理解为第

则就返回第1参数的运算结果。

5.8.2 2003版中的消错方法

如果用户尚未升级到2007版以上，消错会麻烦很多，可以先用IS类函数判断是否存 在错误，然后再外套IF函数处理，上述公式需更改为 …… 此处隐藏：1929字，全部文档内容请下载后查看。喜欢就下载吧 ……