excel 常用函数

VLOOKUP

• 语法格式

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP（要查找的值，查找区域，要返回的结果在查找区域的第几列，精确匹配或近似匹配）

1. 精确查找
   1.1 根据姓名查找对应部门

输入公式：=VLOOKUP(G2,A:C,3,0)

G2	要查找的内容
A:C	查找区域，注意查找区域的首列要包含查找的内容
3	要返回的结果在查找区域的第3列
0	精确查找

2. 近似查找
   2.1 根据分数查找对应等级

输入公式：=VLOOKUP(B2,E:F,2,1)

B2	要查找的内容
E:F	查找区域，注意查找区域的首列要包含查找的内容
2	要返回的结果在查找区域的第2列
1	近似查找

注意查找区域中的首列内容必须以升序排序

3. 格式不一致的查找
   3.1 查找数据为4的数量

输入公式：=VLOOKUP(D2,A:B,2,0)

D2	要查找的内容
A:B	查找区域，注意查找区域的首列要包含查找的内容
2	要返回的结果在查找区域的第2列
0	精确查找

3.2 返回错误值#N/A原因 -- 格式一致
- 解决方法
- 将D2单元格内容设成文本格式，与A列格式一致
- 变公式
- 查找值数字型 查找区域文本型 公式：=VLOOKUP(D2&"",A:B,2,0) 将查找值连接空（&""）变为文本
- 查找值文本型 查找区域数字型 公式：=VLOOKUP(D2^1,A:B,2,0) ^1是将查找值转换成和查找区域一致的格式
- 其它转换方法多种：--、+0、-0、*1、/1...等等

4. 通配符查找

• 根据简称查找对应应收账款

输入公式：=VLOOKUP("*"&D2&"*",A:B,2,0)

星号（*）匹配任意一串字符

5. 带"~"的查找

• 根据姓名查找对应部门

• 返回错误值#N/A的原因是因为内容带波形符（~）

输入公式：=VLOOKUP(SUBSTITUTE(G2,"~","~~"),A:C,3,0)

在查找包含通配符其本身内容时，需在通配符前键入“_{”用函数`SUBSTITUTE`将"}"替换成"~~"

6. 取消合并单元格

• 内容为数值，取消合并单元格

输入公式： =VLOOKUP(9E+307,A$2:A2,1,1)

9E+307是科学记数，表示9*10^307，是Excel允许键入的最大数值

• 内容为文本，取消合并单元格

输入公式： =VLOOKUP("座",E$2:E2,1,1)

7. 查找第一次价格

• 根据物料名称查找对应第一次价格

输入公式： =VLOOKUP(F2,B:D,3,0)

当查找区域首列出现有两个或更多值与查找值匹配时，函数VLOOKUP返回第一次出现的对应值

8. 交叉查询

• 根据产品和地区查找对应销量

输入公式：=VLOOKUP(A12,A2:G8,MATCH(B12,A1:G1,0),0)

MATCH(B12,A1:G1,0)部分找到B12单元格内容“华北地区”在区域A1:G1中的位置5，把它作为VLOOKUP函数的第3参数；

公式就是：=VLOOKUP(A12,A2:G8,5,0)

查找A12单元格内容“产品D”
返回值在区域A2:G8中的第5列，即E列
即E5单元格中的值6945

9. 反向查找

• 根据工号查找对应姓名

函数VLOOKUP可以借助IF{1,0}与IF{0,1}、CHOOSE{1,2}与CHOOSE{2,1}等等结构将逆序转换为顺序，从而实现查找

函数VLOOKUP+ IF{1,0}结构

输入公式：=VLOOKUP(D2,IF({1,0},B2:B11,A2:A11),2,0)

IF({1,0},B2:B11,A2:A11)部分
当为1时条件成立返回B2:B11
当为0时条件不成立返回A2:A11
可以将IF({1,0},B2:B11,A2:A11)部分抹黑按F9键查看

就是两列顺序对换，将逆序转换为顺序

• 函数VLOOKUP+ IF{0,1}结构
  

输入公式：=VLOOKUP(D2,IF({0,1},A2:A11,B2:B11),2,0)

• 函数VLOOKUP+CHOOSE{1,2}结构

输入公式：=VLOOKUP(D2,CHOOSE({1,2},B2:B11,A2:A11),2,0)

函数CHOOSE：根据给定的索引值，从参数串中选出相应值或操作。

CHOOSE(index_num, value1, [value2], ...)

如果第一参数为1，则CHOOSE返回value1；如果第一参数为2，则CHOOSE返回value2
CHOOSE({1,2},B2:B11,A2:A11)部分
当条件为1时，返回B2:B11
当条件为2时，返回A2:A11

• 函数VLOOKUP+CHOOSE{2,1}结构

输入公式：=VLOOKUP(D2,CHOOSE({2,1},A2:A11,B2:B11),2,0)

CHOOSE({2,1},A2:A11,B2:B11)部分
当第一参数为2时，则CHOOSE返回对应B2:B11中的值；
当第一参数为1时，则CHOOSE返回对应A2:A11中的值。
把CHOOSE({2,1},A2:A11,B2:B11)部分抹黑按F9键查看

AB两列顺序对换，将逆序转换为顺序，再用函数VLOOKUP查找

10. 查找返回多列数据

输入公式：=VLOOKUP($F2,$A:$D,COLUMN(B1),0)，右拉填充

公式右拉返回结果在第2、3、4列
用函数COLUMN构造
COLUMN(B1)=2，公式右拉变成COLUMN(C1)、COLUMN(D1)得到3、4

11. 按指定次数重复

输入公式：=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET(B$2,,,ROW($1:$4)),"<>"),A$2:A$5),2,0),E2)&""

按<Ctrl+Shift+Enter>三键结束

12. 结果引用合并单元格内容

• A列区域为合并单元格，根据业务员查找对应的区域

输入公式：=VLOOKUP("座",OFFSET(A2,,,MATCH(D2,B2:B14,0)),1,1)

MATCH(D2,B2:B14,0)部分找到业务员“阿文”在区域B2:B14中的位置11
OFFSET（基点，偏移行数，偏移列数，行高，列宽）
OFFSET(A2,,,11)是以A2单元格为基点，偏移0行0列，返回行高为11的新区域A2:A12的引用。
OFFSET部分抹黑按F9键得到：

用“座”等较大的汉字查找区域中最后一个单元格内容，即返回“华北地区”

13. 有合并单元格的查找

• A列产品为合并单元格，查找A列产品对应的单价

输入公式：=VLOOKUP(VLOOKUP("座",A$2:A2,1,1),F:G,2,0)

比如D5单元格公式=VLOOKUP(VLOOKUP("座",A$2:A5,1,1),F:G,2,0)
A$2:A5部分返回{"产品1";"产品3";0;0}
VLOOKUP("座",A$2:A5,1,1)部分用"座"查找最后一个单元格内容，即返回“产品3”
外层再套个VLOOKUP精确查找
即D5单元格公式就是=VLOOKUP("产品3",F:G,2,0)，返回单价12

14. 与T+IF的组合应用

输入公式：=SUM(VLOOKUP(T(IF({1},A2:A8)),D2:E8,2,0)*B2:B8)

数组公式，按<Ctrl+Shift+Enter>三键结束
IF({1},A2:A8)部分构成三维内存数组
VLOOKUP函数第一参数不能直接为数组
函数T起降维作用，将三维引用转换为一维数组，其返回的结果仍为数组，用函数SUM求和

15. 多条件查找

• 与反向查找一样，可以借助 IF{1,0}与IF{0,1}、CHOOSE{1,2}与CHOOSE{2,1}等结构

输入公式：=VLOOKUP(E2&F2,IF({1,0},A2:A11&B2:B11,C2:C11),2,0)

数组公式，按<Ctrl+Shift+Enter>三键结束

16. 一对多查找

输入公式：=IFERROR(VLOOKUP($E$2&ROW(A1),IF({1,0},$A$2:$A$11&COUNTIF(INDIRECT("A2:A"&ROW($2:$11)),$E$2),B$2:B$11),2,),"")

数组公式，按<Ctrl+Shift+Enter>三键结束

17. 效果图

• 动态图表
  -【数据】→【数据验证】

输入公式：=VLOOKUP($A9,$A$2:$G$5,COLUMN(B1),0) ，右拉填充

• 【插入】→【插入柱形图】