excel 常用函数
VLOOKUP
- 语法格式
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
VLOOKUP(要查找的值,查找区域,要返回的结果在查找区域的第几列,精确匹配或近似匹配)
- 精确查找
1.1 根据姓名查找对应部门
输入公式:=VLOOKUP(G2,A:C,3,0)
G2 | 要查找的内容 |
A:C | 查找区域,注意查找区域的首列要包含查找的内容 |
3 | 要返回的结果在查找区域的第3列 |
0 | 精确查找 |
- 近似查找
2.1 根据分数查找对应等级
输入公式:=VLOOKUP(B2,E:F,2,1)
B2 | 要查找的内容 |
E:F | 查找区域,注意查找区域的首列要包含查找的内容 |
2 | 要返回的结果在查找区域的第2列 |
1 | 近似查找 |
注意查找区域中的首列内容必须以升序排序
- 格式不一致的查找
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...等等
- 通配符查找
- 根据简称查找对应应收账款
输入公式:=VLOOKUP("*"&D2&"*",A:B,2,0)
星号(*)匹配任意一串字符
- 带"~"的查找
- 根据姓名查找对应部门
- 返回错误值#N/A的原因是因为内容带波形符(~)
输入公式:=VLOOKUP(SUBSTITUTE(G2,"~","~~"),A:C,3,0)
在查找包含通配符其本身内容时,需在通配符前键入“”用函数`SUBSTITUTE`将""替换成"~~"
- 取消合并单元格
- 内容为数值,取消合并单元格
输入公式: =VLOOKUP(9E+307,A$2:A2,1,1)
9E+307是科学记数,表示9*10^307,是Excel允许键入的最大数值
- 内容为文本,取消合并单元格
输入公式: =VLOOKUP("座",E$2:E2,1,1)
- 查找第一次价格
- 根据物料名称查找对应第一次价格
输入公式: =VLOOKUP(F2,B:D,3,0)
当查找区域首列出现有两个或更多值与查找值匹配时,函数VLOOKUP返回第一次出现的对应值
- 交叉查询
- 根据产品和地区查找对应销量
输入公式:=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
- 反向查找
- 根据工号查找对应姓名
函数
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
查找
- 查找返回多列数据
输入公式:=VLOOKUP($F2,$A:$D,COLUMN(B1),0),右拉填充
公式右拉返回结果在第2、3、4列
用函数COLUMN
构造
COLUMN(B1)=2
,公式右拉变成COLUMN(C1)
、COLUMN(D1)
得到3、4
- 按指定次数重复
输入公式:=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET(B$2,,,ROW($1:$4)),"<>"),A$2:A$5),2,0),E2)&""
按<Ctrl+Shift+Enter>
三键结束
- 结果引用合并单元格内容
- 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键得到:
用“座”等较大的汉字查找区域中最后一个单元格内容,即返回“华北地区”
- 有合并单元格的查找
- 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
- 与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
求和
- 多条件查找
- 与反向查找一样,可以借助
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>
三键结束
- 一对多查找
输入公式:=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>
三键结束
- 效果图
- 动态图表
-【数据】→【数据验证】
输入公式:=VLOOKUP($A9,$A$2:$G$5,COLUMN(B1),0) ,右拉填充
- 【插入】→【插入柱形图】
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· .NET10 - 预览版1新功能体验(一)