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

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

找引用函数

在Excel 2010版中将内置函数分成12大类，除了所谓不需要学习都会的逻辑函数外， 最常用的就是查找与引用函数，几乎每个用户在工作中都接触过此类函数。

查找与引用函数的应用有一定的难度，很多应用都涉及到数组公式，甚至还需要应用 多维引用技术。这里仅就一些常见的函数及基础的应用做一些讲解，带领读者初识查找引 用函数。

5.1 认识函数中的大众情人

查找与引用函数的普及和其中的一个函数有关。谈到函数公式，绝大多数的Excel用 户会感到很头痛，觉得很专业，也不愿意花时间深入学习。但有一个函数例外，这个函数 难度中等但普及率相当高，可以说几乎所有接触过函数公式的用户都知道这个函数，很多 人了解运用函数公式就是从这个函数开始的。

这个函数就是人见人爱的函数领域中的大众情人——VLOOKUP。

为什么说VLOOKUP函数是函数领域的大众情人？原因在于这个函数的确非常管用， 尤其在处置大量数据的查找时效率可谓惊人。下面以一个实例来认识一下这个神秘的函数。

5.1.1 一个典型的单条件查找示例

某公司月底结账时需要编制进销存报表，进销存报表中D列“本期销售”需要从H列 “销售汇总”中取数，匹配的原则为A列“货号”与G列“货号”相符，如图5.1所示。

图5.1 进销存报表示例

第2篇 计算数据 问题分析：这是一个非常典型的查找问题，D列需要填列的数据在H列已有，仅需要 根据A列与G列货号匹配后返回。在函数应用没有普及的时候，很多人的做法是一个个手 工查找，然后把找到的数值录入电脑。这种做法现在早已成为笑谈。

5.2 所示的结果。 解决方案：在 D2 单元格录入一个函数公式然后向下复制，得到如图

=VLOOKUP(A2,$G$1:$H$15,2,0)

图5.2 VLOOKUP函数查找返回的结果

公式解析：由上述公式可见VLOOKUP函数有4个参数，VLOOKUP函数的语法结构 如下：

这个函数的含义就是在查找区域中的第1列查找，如有找到则返回该值在查找区域中 对应列的值，4个参数的含义如表5.1所示。

表5.1 VLOOKUP函数4个参数的理解

参数顺序 第1参数 第2参数 第3参数 第4参数 参数含义 要查找的值 查找的区域 查找区域中返 2 回的列 查找模式 0 A2 上例中的取值 在查找区域的第1列中查找 如第4参数是精确匹配则区域第1列不需要排序， $G$1:$H$15 如是模糊匹配则需要对区域的第 1 列升序排序 返回查找值在查找区域中所在行第2列的值 等价于FALSE，精确匹配，如果为1或者TRUE， 模糊匹配 VLOOKUP(要查找的值,查找的区域,查找区域中返回的列,查找模式) 注 意 事 估计很多初学者看了上面这个表格后仍很难理解透彻，刚接触函数时一下子要理解这

D2 单元格返回的值 么多概念的确不容易。我们可以先跳过难以理解的理论部分内容，从

来看一下函数公式的运算原理。遇到问题时再回过来看对于函数参数的解释。

以D2单元格中的公式为例，就是在查找区域$G$1:$H$15的第1列中查找A2单元格 的值“E091”，结果在G6单元格中查到了结果，这时候返回查找区域$G$1:$H$15对应行 （第 6 行）第 2 列的值。查找演示如图 5.3 所示。

·128 ·

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

图5.3 查找结果演示

备注：第4参数在保留第3个参数和第4个参数之间的“,”，后面省略内容和使用0 值或者False是等价的，以下两个公式也能返回类似的结果：

职场点睛：演示的示例中只有10多行，实际工作中的数据源可能有成百上千行，一 样只要用一个公式向下复制就能解决问题。学会这个函数后在实际工作中遇到类似问题可 以大幅度提高工作效率。

=VLOOKUP(A2,$G$1:$H$15,2,) =VLOOKUP(A2,$G$1:$H$15,2,FALSE) 5.1.2 VLOOKUP函数的局限性

大众情人往往并不完美，实际生活中如此，函数领域中亦如此。VLOOKUP函数在大 数据量查找中具有优势，但也有其局限性。使用VLOOKUP函数最常见的困惑有如下几种：

? 查找值必须位于查找区域的第一列；

? 仅能处理单条件查找的问题；

? 只能返回满足查找条件的第一个值；

? 不能用一个公式返回所有满足条件的查找值。

我们没有必要为难 VLOOKUP 函数，退一步海阔天空，思路开阔一些，运用其他函数

也能轻松搞定查找问题。本章后续将介绍MATCH、INDEX、LOOKUP、OFFSET、 INDIRECT、ROW、COLUMN等查找与引用函数。正如一个人的能力是有极限的，一个 函数的能力也是有限的，运用函数组合能够解决更多的问题。

本节示例文件：《5.1 认识函数领域的大众情人.xlsx》。

5.2 突破VLOOKUP函数反向查找的限制

接触函数领域的大众情人——VLOOKUP函数后，既惊艳其在海量数据查找中的高效， 也有一些遗憾，比如查找值必须位于被查找区域的第一列。

·129·

第2篇 计算数据 实际工作中需要查找的值未必就在被查找区域的第一列，这类问题称之为“反向查 找”。直接用VLOOKUP函数无法解决，需要做一些技术处理。

5.2.1 一个反向查找的例子

如图5.4所示，学生的学号与姓名是一一匹配的，如已知学号要查找姓名，可以用 VLOOKUP 函数解决， F2 单元格公式如下：

=VLOOKUP(E2,A2:B10,2,0) 但反过来已知姓名要查找学号，直接用

VLOOKUP 函数就不行了。

图5.4 已知姓名查询学号

1．问题分析

这里直接用VLOOKUP不行，原因在于在查找区域中需要返回的“学号”在“姓名” 的左边，也就是不符合需要查找项目“姓名”在查找区域A2：B10的第一列的条件。

2．解决方案1

知道了问题的所在，解决就容易了。如果在“学号”的左侧有一列“姓名”，这个问

题就迎刃而解了。一个笨方法是在 A 列“学号”之前插入一列，复制原 B 列“姓名”的内 容，这样就可以直接使用VLOOKUP函数了。

这种方法思路是对的，但操作方法不可取。不建议随意更改数据源，用函数完全能达 到插入一个辅助列，重构数据源的效果。在F5单元格录入如下公式：

公式解析：整个公式外层还是 VLOOKUP 函数，关于这个函数的用法不再赘述。 公式中使用了一个IF函数作为VLOOKUP函数的第2参数，在编辑栏中选取这一段 函数“IF({1,0},B2:B10,A2:A10)”，按F9键，返回结果为：

返回结果中的花括号代表数组，逗号表示同行不同列，分号表示换行。整个返回结果

·130 ·

=VLOOKUP(E5,IF({1,0},B2:B10,A2:A10),2,) {\管莺菲\石永绍\越俊\崔亨\于成中\狄影淑\糜彩青\秋彩伊\裘健栋\

第5章 海量数据分析的利器——初识查找引用函数 代表一个9行2列的内存数组。

如果直接用F9键看不容易理解，还可以将这个IF函数的运算结果放到单元格中。选 取H2：I10单元格区域，录入公式“=IF({1,0},B2:B10,A2:A10)”，按Ctrl+Shift+Enter三 键结束，返回结果如图 5.5 所示。

图5.5 IF函数返回结果分析

由上图可见，使用IF函数，返回了一个内存数组，相当于重构了一个区域，而这个重 …… 此处隐藏：2185字，全部文档内容请下载后查看。喜欢就下载吧 ……