数据分析：EXCEL明星函数下篇

上一篇《数据分析：EXCEL明星函数上篇》讲解了Excel函数的定义、作用、学习方法，以及统计类、文本类函、时间类函数的基础运用，在此相信大家也不再会害怕函数带来的淡淡忧伤。

接下来继续讲解函数的最后一部份，如果比较熟悉大可跳过该部分，大家若有好的建议与意见方可留言。所有应用场景图文均来自工作实践运用，今天继续数据分析之EXCEL函数之旅：逻辑函数与查询匹配类函数。

‘’1

逻辑函数

常用的逻辑函数有AND（并且）、OR（或则）、NOT（否则）、TRUE（真）、FALSE（假）、IF（假设）。

IF函数语法：

IF（逻辑值条件,条件为真即true时返回的结果,条件为假即false返回的结果）。

举一个简单例子理解该函数：“如果你每天贪吃贪睡，你长到300斤，否则保持苗条身材”。

示例一：

要求是少数名族且信用评分大于500分的标注为“满足”，否则标注为“不满足”。

分析：

逻辑值条件是“AND（B2”汉”,C2>500）“,这里AND 表示并且满足两个条件，“”表示不等于符号，在公式中“汉”打上双引号，因为它是字符串；真值是“满足”，假值是“不满足”

注意：

所有字符串参与在公式中都需要打上双引号，若为数字不用打，后面500就不用打，函数均是采用英文标点。

计算公式如下：

=IF（AND（B2”汉”,C2>500）,”满足”,”不满足”），往下拖拽公式。

结果如下：

2

VLOOKUP函数

查询匹配类函数在实际工作中运用的非常之多，主要有VLOOKUP、INDEX、MATCH、LOOKUP、HLOOKUP。

VLOOKUP函数使用频率非常高因为简单易用，处理数据查找效率堪称完美。然而也有瑕疵，就如人无完人一样，接下来通过实际运用展示它的优点和缺陷，以及缺陷的突破机制。

VLOOKUP语法：

VLOOKP（查找线索,查找区域,查找区域要返回的值,查询参数0或1），0代表精确匹配，1模糊匹配，为了更加简单明了理解，实际操作如下。

示例二：

原始贷款数据源如下：

根据数据源中档案号匹配出贷款额度：

分析：

通过线索F2在查找区域A1：D6查询贷款额度，贷款额度在档案号的右边第4列，并且要精确匹配。查找区域加上美元符号；另外档案号M3在数据查询区域不存在所以返回#N/A。

VLOOKUP局限性：

（1）查找值必须位于查找区域的第一列。比如上表查找值档案号就是位于查找区域A1：D6的第一列；如果不位于与第一列就查询不了，比如在上表通过不同贷款额度匹配查询出姓名，这时数据区域在B2：D6，因为贷款额度不在该区域的第一列；

（2）只能够处理单个条件查找的问题；

（3）不能通过一个函数返回所有满足条件值。

在这里一定注意局限性第一点，如何突破局限性常见有三种巧妙办法，接下来学习了其他函数给出讲解。

3

MATCH

函数

MATCH函数用于查询表中某个值所在的位置行数或则列数，常常和INDEX函数搭配使用，两者合一威力无比，不仅可以秒杀VLOOKUP函数，还能够秒杀VLOOKUP函数局限性的”第一条”。

MATCH函数语法：MATCH（查询线索,数据区域,0或1或-1）和VLOOKUP类似，0代表精确匹配，实际工作中常需要精确匹配。

示例三：

数据源如下：

要求查询H3、L9所在单元格位置。

4

INDEX函数

INDEX函数用于查询某行某列下对应的值。

INDEX函数语法：

INDEX(查找区域,行坐标,列坐标)。

示例四：

要求查找下表3行4列的对应的值：

公式如下：

“=INDEX($A$2:$D$6,3,4)”，返回结果1200。

示例五：

根据型号与规格找出B8单元格的价格。

分析：

首先找到型号A011所在的行数，再找到规格SC03所在的列数，此时可以借助MATCH函数；找到行和列后再通过INDEX函数返回值。

步骤一：

AO11行数：“MATCH(B6,$A$1:$A$4,0)”，返回3，即A011在锁定区域的第3行

步骤二

：SC03列数：“=MATCH(B7,$A$1:$D$1,0)”，返回3，即SC03在锁定区域的第3列

步骤三：

求第3行第3列对应价格“=INDEX($A$1:$D$4,3,3)”或则对公式进行组合“=INDEX($A$1:$D$4,MATCH(B6,$A$1:$A$4,0),MATCH(B7,$A$1:$D$1,0))”，返回结果为22。

到此是否已经有所启发，可以用INDEX+MATCH函数替换VLOOKUP函数。后面会具体讲解实际运用中如何干掉VLOOKUP。

5

LOOKUP

函数

LOOKUP函数属于一个比较优雅的函数，不仅具有神秘美而且还神通广大，很多人不会运用来解决实际问题，高明之处在于可以替代INDEX+MATCH组合函数、VLOOKUP函数来做精确匹配查询。

LOOKUP语法：

“=LOOKUP(1,0/(条件区域=条件),返回值区域)”。

示例六：

原始贷款数据如下：

要求根据姓名查找对应档案号：

此时通过用LOOKUP函数已经解决了VLOOKUP的局限性“第一条。

6

HLOOKUP函数

HLOOKUP用于在表格的首行查找指定的值，返回指定区域的行的值，略和VLOOKUP函数相反。用法比较拗口，用一个在工作中曾经用过的实例做演示。

HLOOKUP语法：

HLOOKUP（查找线索,查找区域,行位置,精确匹配0）。

示例七：

数据源如下：

要求根据数据源匹配出相应日期业绩如下表：

公式如下：

B8处输入公式

=HLOOKUP(B$7,$B$1:$E$5,COUNTA($A$1:$A2),0)。

拖动公式则有：

分析：

1、不得不讲单元格引用3种类型：

（1）$A$1：绝对引用

复制拖拽公式时不改变引用单元格地址；

（2）A$1：行绝对列相对引用

纵向复制拖拽公式不改变引用单元格地址，横向复制会改变；

（3）$A1：行相对列绝对

纵向复制公式时会改变引用单元格地址，横向复制公式时不改变；

（4）按住F4在公式中切换，即不同美元符号

在此望大家根据上述3种引用类型看上面的一连串公式就容易明白。

2、这里的COUNTA表示非空计数，用来判定查找值在数据源中的哪一行。

7

总结

孰能生巧，在此做一个总结，通过上述示列或许你隐约感觉到以上的匹配查询都有多种解法，选择自己喜欢的才是最好的。在此总结运用多种方法匹配想要的数据。

数据源如下：

根据用户昵称运用多种函数方法匹配出起始日期：

函数基础用法讲解到此，后期根据实际情况还会有一个终极函数提高篇，譬如如何进行多条件匹配，解决实际工作中可能会遇到的情形。