Execl学习 VLOOKUP函数详解
VLOOKUP
参考文章：
[1] 运营菌:怎样快速掌握 VLookup？
[2] EXCEL入门IF函数
0x01 基本介绍
在Excel函数中，有那么的一个万金油Excel函数，它既可以正向查找，逆向查找，多条件查找，还能模糊匹配，它就是Vlookup。Vlookup与IF、SUM、sumifs/countifs誉为四大必学Excel函数。先看看它的语法：
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
# lookup_value:用谁找
# table_array:在哪里找
# col_index_num:返回第几列
# range_lookup:匹配类型
FALSE:精确查找
TRUE:近似查找
### 在函数里面的标点要使用英文输入法下的标点，在Excel上，SUM的参数个数是不固定的，区域和数值的参数也能混合使用。
说明：
我们所有的案例都是基于这张表格：
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | ID | Name | 力量 | 敏捷 | 话术 | 魅惑 | ID |
| 2 | 121601 | 尼尔 | 90 | 90 | 90 | 70 | 121601 |
| 3 | 121602 | 芙蕾雅 | 40 | 60 | 10 | 90 | 121602 |
| 4 | 121603 | 弗朗哥 | 80 | 80 | 60 | 90 | 121603 |
| 5 | 121604 | 塞拉 | 80 | 60 | 70 | 90 | 121604 |
| 6 | 121605 | 凯林 | 30 | 90 | 80 | 80 | 121605 |
0x02 基本用法
我们要在成绩单中找到尼尔、芙蕾雅、凯林的话术分别是多少？
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | ID | Name | 力量 | 敏捷 | 话术 | 魅惑 | ID | Name | 话术 |
| 2 | 121601 | 尼尔 | 90 | 90 | 90 | 70 | 121601 | 尼尔 | ？？ |
| 3 | 121602 | 芙蕾雅 | 40 | 60 | 10 | 90 | 121602 | 芙蕾雅 | ？？ |
| 4 | 121603 | 弗朗哥 | 80 | 80 | 60 | 90 | 121603 | 凯林 | ？？ |
| 5 | 121604 | 塞拉 | 80 | 60 | 70 | 90 | 121604 | ||
| 6 | 121605 | 凯林 | 30 | 90 | 80 | 80 | 121605 |
在此我们构造一个函数：
=VLOOKUP(H2,$B$2:$F$6,4,FALSE)
- 需要注意的是，返回第几列指的不是以原表为参考系的，而是以table_array确定的范围为参考系，因此这里返回的列数是B2:F6的第四列，也就是话术这一列
- 匹配类型有两种：FALSE(0)是精确匹配，TRUE(1)是模糊匹配。
0x03 拓展用法
所有的拓展用法都是对基本用法中的参数进行改造。
03.01 查找多列
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ID | Name | 力量 | 敏捷 | 话术 | 魅惑 | ID | Name | 力量 | 敏捷 | 话术 |
| 2 | 121601 | 尼尔 | 90 | 90 | 90 | 70 | 121601 | 尼尔 | ？？ | ？？ | ？？ |
| 3 | 121602 | 芙蕾雅 | 40 | 60 | 10 | 90 | 121602 | ||||
| 4 | 121603 | 弗朗哥 | 80 | 80 | 60 | 90 | 121603 | ||||
| 5 | 121604 | 塞拉 | 80 | 60 | 70 | 90 | 121604 | ||||
| 6 | 121605 | 凯林 | 30 | 90 | 80 | 80 | 121605 |
在此我们构造一个函数：
=VLOOKUP($H2,$B$2:$F$6,COLUMN(B1),FALSE)
和基本用法不同的是：
- lookup_value：这里我们使用的混合引用，限制了列号不允许改变
- col_index_num：这里我们利用了一个函数的返回值来作为参数，因为一个公式中的常量，在拖动的时候不会改变
- COLUMN()返回的是一个单元格的列号，因此 COLUMN(B1)=2，COLUMN(C1)=3
03.02 逆向查找
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | ID | Name | 力量 | 敏捷 | 话术 | 魅惑 | ID | Name | ID |
| 2 | 121601 | 尼尔 | 90 | 90 | 90 | 70 | 121601 | 尼尔 | ？？ |
| 3 | 121602 | 芙蕾雅 | 40 | 60 | 10 | 90 | 121602 | ||
| 4 | 121603 | 弗朗哥 | 80 | 80 | 60 | 90 | 121603 | ||
| 5 | 121604 | 塞拉 | 80 | 60 | 70 | 90 | 121604 | ||
| 6 | 121605 | 凯林 | 30 | 90 | 80 | 80 | 121605 |
这个要求看似很简单，如果我们构造函数如下：
=VLOOKUP(H2,$A$2:$B$6,1,FALSE)
则得到返回值#N/A
这是因为VLOOKUP函数默认将 lookup_value 与 table_array 的第一列匹配，当函数将“尼尔”与“ID“列进行匹配时，自然找不到对应的元素，报错返回#N/A
那么正确的方法是什么呢？
我们接下来试试如下函数：
=VLOOKUP(H2,IF({1,0},B2:B6,A2:A6),2,0)
通过 IF({1,0},B2:B6,A2:A6)我们能把B列与A列的位置互换，从而使B列变成第一列，A列变成第二列。需要注意的是，VLOOKUP的第三个参数也要写成新构造的逆向区域的列数”2“
输入后记得是按[Ctrl+Shift+Enter]，因为里面是含有数组运算的，均需要按三键结束才能达到正确答案。
具体的IF({1,0})的解释我放在后面的补充说明部分了，感兴趣的可以看一下。
03.03 多重查找
我们现在有新表如下：
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | ID | Name | 力量 | 敏捷 | 模组 | ID | Name | 模组 | 力量 |
| 2 | 121603 | 弗朗哥 | 20 | 30 | 达克夏尔 | 121601 | 弗朗哥 | 达克夏尔 | ？？ |
| 3 | 121606 | 弗朗哥 | 90 | 90 | 多莉丝 | 121602 |
我们有两个弗朗哥，单凭一个条件已经不能筛选出我们要的数据了，我们可以通过模组这个元素来进一步筛选：
我们构造函数如下：
=VLOOKUP($G2&$H2,IF({1,0},$B$2:$B$3&$E$2:$E$3,$C$2:$C$3),2,0)
其中，构造的区域长这样：
| Name模组 | 力量 |
|---|---|
| 弗朗哥达克夏尔 | 20 |
| 弗朗哥多莉丝 | 90 |
另外：如果多重条件查找返回的结果是数值，可以通过sumifs函数来匹配出结果值，这样省去写很长公式的麻烦。
0x0X 补充说明
0X.00 TRUE OR FALSE ?
excel使用0代替假，但并非只有1可以代替真，所有非1的数值都可以代替真。
因此：
TRUE == 0
FALSE != 0
0X.01 '$'表示绝对引用
在公式中采用$表示对数据区域的绝对引用。
相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时，当将公式向旁边复制时，地址是如何变化的。
具体情况举例说明：
- 相对引用：复制公式时地址跟着发生变化，如C1单元格有公式：=A1+B1
当将公式复制到C2单元格时变为：=A2+B2
当将公式复制到D1单元格时变为：=B1+C1 - 绝对引用：复制公式时地址不会跟着发生变化，如C1单元格有公式：=$A$1+$B$1
当将公式复制到C2单元格时仍为：=$A$1+$B$1
当将公式复制到D1单元格时仍为：=$A$1+$B$1 - 混合引用：复制公式时地址的部分内容跟着发生变化，如C1单元格有公式：=$A1+B$1
当将公式复制到C2单元格时变为：=$A2+B$1
当将公式复制到D1单元格时变为：=$A1+C$1
- 一句话总结，在Excel中移动公式位置，引用内容会跟着改变，如果加了$，则该部分不会改变
0X.02 column函数
column函数的作用：返回所选择的某一个单元格的列数
column函数的语法：
=column(reference)
# 如果省略reference，则默认返回函数column所在单元格的列数。
0X.03 数组“{ }”
Excel用花括号“{}”来表示数组：
数组表示一类元素的集合：
例如有数组：{1,3,5},则有：
1 + {1,3,5} = {2,4,6}
三键：即加按shift+ctrl+enter三键
0X.04 IF函数
if函数的作用：根据判断条件执行不同的语句，而且能配合很多函数共同工作(能嵌套)
- 官方说明：IF函数判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值
if函数的语法：
=IF(logical_test, value_if_true, [value_if_false])
# 例如：
# =IF(A1>60,及格,不及格)
# 如果A1单元格的数字大于60，则在当前单元格输出“及格”，否则输出“不及格”。
0X.05 if({0,1}) OR if({1,0}):
先前我们了解了数组的用法和IF函数的用法，一旦组合起了，立即就看不明白了。
但是只要我们清楚了两者的用法其实还是很好理解的：
- 对数组的操作就是对数组的每一个元素分别进行操作，再将操作结果再次组成一个数组
- IF函数先判断条件，然后根据结果的正误返回不同的值
然后我们看下面的例子：
=IF(1,B2:B9,A2:A9)
# 表示返回 B2:B9
=IF(0,B2:B9,A2:A9)
# 表示返回 A2:A9
那么如果将条件变为一个数组{1,0}呢？
=IF({1,0},B2:B3,A2:A3)
# 函数先对数组的第一个元素'1'进行操作，返回结果 B2:B3
# 函数再对数组的第二个元素'0'进行操作，返回结果 A2:A3
# 然后将两个返回结果再次组成一个数组，返回结果{B2:B3,A2:A3}
# {B2:B3,A2:A3} 既是区域A2:B3的逆向区域。
-
A2:B9
+---+---+ | a | 1 | | b | 2 | +---+---+ -
{B2:B3,A2:A3}
+---+---+ | 1 | a | | 2 | b | +---+---+