点击蓝字【秋叶 Excel】
发送【礼包】
免费领办公神器、Office 模板和免商字体!
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
本文作者:明镜在心
本文编辑:卫星酱
在 Excel 函数界,名气最大的要属查找引用函数 V 哥了。
关于它,民间还流传着这样一句话,它是人见人爱,花见花开,车见车爆胎的家伙。
比如,工作中最常用的数据查找引用,非它不可。
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
问题描述
如下图:是一张集团公司的升职加薪表。
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
现在需要将集团公司中部分人员的岗位工资、薪级工资、岗位补贴和合计等内容引用到一张新表中。
正常情况下,我们会这样写公式:
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
【L2】单元格输入如下公式:
=VLOOKUP($K2,$B:$H,COLUMN(D1),0)
公式解析:
以【K2】单元格的内容: 「武胜」 作为查找值,在 数据源区域 B 到 H 列 中进行查找,如果查找到就返回第 4 列对应的值。
公式向右拖动一个单元格, COLUMN(D1) (即数字 4) 会变成 COLUMN(E1) (即数字 5) ,以此类推。
但是,大家有没有发现,他只返回了每个人员第一次出现的值,如果,我们现在只需要返回升职加薪之后的数据,那该怎么办呢?
这个时候,就轮到它的二师弟猪哥 (Hlookup) 上场了。
那我们一起来看看它的表演吧。
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
解决问题
Hlookup 这个函数与 Vlookup 函数是一对孪生兄弟,
Vlookup 是垂直方向查找,返回列对应的值。
而 Hlookup 是水平方向查找,返回行对应的值。
我们在【L2】单元格输入如下公式:
=HLOOKUP(L$1,$B$2:$H$14,MATCH($K2,$B$2:$B$14,0)+1,0)
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
公式解析:
这个函数也有四个参数,与 vlookup 完全相同。
语法如下:
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
第❶参数是 lookup_value查找值,
第❷参数是 table_array查找区域,
第❸参数是 row_index_num返回对应的行值。
第❹参数是 range_lookup为精确查找。
查找值为:【L1】单元格的内容 【岗位工资】 ,
查找区域为: $B$2:$H$14 ,即首列,必须包含查找值。
返回对应的行值,用了一个 Match 来配合下,
MATCH($K2,$B$2:$B$14,0)
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
第一参数查找值 【 K2】 单元格中的值 「武胜」。
第二参数查找区域 【$B$2:$B$14】 ,这个姓名所在的单元格区域中查找,并返回在这个区域中是排到第几个单元格。比如排到第 4,就返回数字 4。
第三参数为 0,表示精确查找。
因为 MATCH 函数也是返回第一查找到的值所对应的数字,所以让它的返回值+1,就返回了升职加薪之后的所在行的数字了。即 4+1=5。
于是 Hlookup 函数的结果如下:
=HLOOKUP("岗位工资",$B$2:$H$14,5,0)
如下图所示:
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
备注:Match 函数也将其中合并单元格中的空白单元格算在内。
数据处理太复杂,公式不会写,怎么办?
学会使用插件和 AI,平时要好几个小时才能完成的工作,现在 10 分钟就能搞定!
如果你想学,今天可免费加入《秋叶 Excel 3 天集训营》,学习提效新技能!↓
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
知识扩展
某些小伙伴可能很好奇,是否一定不能用 Vlookup 函数解决这个问题,
或者有的小伙伴会很痴迷于用 Vlookup 函数,一定要用其解决。这倒也不是不行。
但是,这里需要结合 IF 函数,并进行数据区域重新构造。 只有满足 Vlookup 的查找要求才能让其正确返回值。 如下图:
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
=VLOOKUP($K2,IF({1,0},$B$3:$B$13,E$4:E$14),2,0)
公式解析:
与常规的 Vlookup 函数的第二参数不同的是,
这里用 IF 函数进行了两个区域的重新构造。
IF 的第二参数是:查找的区域,即: $B$3:$B$13
IF 的第三参数是:返回的区域,即: E$4:E$14
而且是错行排列的。正好符合 Vlookup 函数返回第一个查找值的要求。
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
另外: IF 的第三参数是:返回的区域,即: E$4:E$14 ,是一个 行绝对引用,列相对引用, 这样在向右拖动公式的时候,可以返回正确的列。
因为这个公式是数组公式,所以还需要按三键【Ctrl+Shift+Enter】结束 (PS.Office 365 按【Enter】即可) 。
上面的问题到此就解决了。
But,领导们的想法是会随时变化的。
比如,现在的领导的要求是:
不仅需要看升职加薪之后的明细数据,也需要同时查看升职加薪之前的数据。那该怎么办呢?就 像下图这样:
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
这种情况下,如果要用函数的话,还真是要猪哥上场了。
而且原来的公式基本不用改动,只需要增加一个求余函数+返回行号的函数就行。
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
公式如下:
公式解析:
第三参数那里增加 MOD(ROW(A2),2)。 其中:
ROW(A2),返回行号 2 (即:ROW(A2)=2) ,作为 MOD 的函数参数。
MOD(2,2) ,然后对 2 求余额数即为:0。 (即:MOD(2,2)=0)
然后 MATCH 查找到的值再加上 0 值,还是返回 MATCH 的值。
公式向下拖动即返回: MOD(ROW(A3),2)。 其中: ROW(A3) ,返回行号 3 (即:ROW(A3)=3) ,作为 MOD 的函数参数。
MOD(3,2) , 然后对 3 求余额数即为:1。 (即:MOD(3,2)=1)
最终 MATCH 的返回值+1。
这里的关键点是:
用 MOD 函数来代替原来公式中的+1 这个值,调节返回 0 和 1 两个数字。
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
今天我们分享了 Vlookup 函数二师弟,猪哥 Hlookup 函数的用法。
在大部分情况下,Vlookup 的查找还是非常方便实用的,但是在某些情况下,真不如它的师弟 Hlookup 函数 。
另外在上面的例子中,虽然 Vlookup 函数结合 IF 函数进行数据区域重新构造也能解决问题, 但是在 实际工作中,不建议使用 。
因为,它是一个数组公式,在数据比较多的情况下,会导致表格很卡。
所以,尽量不使用数组公式,只使用简单公式的组合,而且组合越少越好,计算次数越少越好,这才是工作中需要实现的目标。
好了,今天我们就分享到这里,如果喜欢此篇文章,欢迎点赞&转发!
除了上面介绍的 Hlookup 函数,Excel 里还有很多的函数,比如 Lookup、Xlookup、Sumif 等等。
如果你想学习更多关于函数的知识,更多 Excel 高效办公技巧!那么今天就是一个很好的机会:
小编为大家申请了专属福利,原价99元的《秋叶 Excel 3 天集训营》,现在报名,只需 0 元!
秋叶 Excel3 天集训营
0 基础也能学
报名额外赠送
100 套职场必备图表模板
35 个常用函数说明
学到就是赚到
名额有限,快来免费加入
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
![](https://static.ws.126.net/163/frontend/images/2022/empty.png)
以上内容包含广告