Fork me on GitHub

在vlookup中用数字和文本参数混合查找数据项

2022-04-03 中文

技术

Excel vlookup

三个参数查找数据，如果参数既有数字又有文本，如何快速匹配对照表的数据项呢？

题目:按年龄工龄学历计算工资

假设有这么一份工资等级对照表，先不管它是否科学，总之有这么个对照表。

年龄工龄学历工资
21~300~5本科以下2000
本科及以上2500
6~10本科以下3000
本科及以上3500
10+本科以下4000
本科及以上4500
31~400~5本科以下3000
本科及以上3500
6~10本科以下4000
本科及以上4500
10+本科以下5000
本科及以上5500
41~500~5本科以下4000
本科及以上4500
6~10本科以下5000
本科及以上5500
10+本科以下6000
本科及以上6500
51~600~5本科以下5000
本科及以上5500
6~10本科以下6000
本科及以上6500
10+本科以下7000
本科及以上7500

以及这么一份员工名单，麻烦你算一下工资。

ABCDEF
工号姓名年龄工龄学历工资
SCREW001王菲348本科?
SCREW002刘德华5923小学?
SCREW003金城武5615初中?
SCREW004黎明4220高中?
SCREW005郭富城210硕士?
SCREW006佘诗曼5824博士?
SCREW007黄晓明4810本科?
SCREW008袁弘277小学?
SCREW009张歆艺3915初中?
SCREW010张艺兴221初中?
SCREW011张涵予4222高中?
SCREW012张雨绮5430大专?
SCREW013刘涛324本科?
SCREW014刘敏涛287小学?
SCREW015胡歌251大专?
SCREW016靳东366本科?
SCREW017王凯4413高中?
SCREW018刘学义319本科?
SCREW019檀健次230高中?
SCREW020陆展博4625硕士?
SCREW021金世佳4216博士?
SCREW022胡一菲347硕士?
SCREW023张伟6030博士?
SCREW024福尔康3616大专?

平常用VLOOKUP查找的时候，只能查找一个参数，但是这里有三个参数，而且既有数字又有文本，那么怎样才能同时查找这么多参数呢？

其实无法一次做到的哈，但我们可以把它分解一下，从年龄、工龄的数字变成相应的等级文本，再组合起来，变成只查找一个文本参数，这样就行了。

在给定的数值范围内查找数字

我们先来查找年龄和工龄，此时需要手工做2个等级对照表

GH
年龄年龄等级
2121~30
3131~40
4141~50
5151~60
IJ
00~5
66~10
1110+

小贴士 : VOOKUP里面查找数值范围的时候，公式里的第四个参数要为 TRUE。

查找年龄等级的公式 =VLOOKUP(C2,G:H,2,TRUE)

查找工龄等级的公式 =VLOOKUP(D2,I:J,2,TRUE)

查找文本

现在需要查找学历等级，我们手工做一个等级对照表

KL
学历学历等级
小学本科以下
初中本科以下
高中本科以下
大专本科以下
本科本科及以上
硕士本科及以上
博士本科及以上

小贴士 : VOOKUP里面查找文本的时候，如果不想被近似数据干扰，公式里的第四个参数要为 FALSE。

查找学历等级的公式 =VLOOKUP(E2,K:L,2,FALSE)

混合查找数字和文本

这时候我们需要再手工做一个综合工资等级对照表。

先把原来的工资等级对照表里的空栏填满之后，在右边新建一个辅助列，然后在公式里用 & 符号得出的组合后的文本，由于有些数字连起来之后比较难辨认，而且可能影响Excel处理，建议用符号进行分隔，比如括号。

ABCDE
年龄工龄学历工资组合公式
20~300~5本科以下2000="("&A2&")("&B2&")("&C2&")"

然后把结果粘贴到前面的年龄和工资等级对照表之后

MN
对照表组合工资
(20~30)(0~5)(本科以下)2000
(20~30)(0~5)(本科及以上)2500
(20~30)(6~10)(本科以下)3000
(20~30)(6~10)(本科及以上)3500
(20~30)(10+)(本科以下)4000
(20~30)(10+)(本科及以上)4500
(31~40)(0~5)(本科以下)3000
(31~40)(0~5)(本科及以上)3500
(31~40)(6~10)(本科以下)4000
(31~40)(6~10)(本科及以上)4500
(31~40)(10+)(本科以下)5000
(31~40)(10+)(本科及以上)5500
(41~50)(0~5)(本科以下)4000
(41~50)(0~5)(本科及以上)4500
(41~50)(6~10)(本科以下)5000
(41~50)(6~10)(本科及以上)5500
(41~50)(10+)(本科以下)6000
(41~50)(10+)(本科及以上)6500
(51~60)(0~5)(本科以下)5000
(51~60)(0~5)(本科及以上)5500
(51~60)(6~10)(本科以下)6000
(51~60)(6~10)(本科及以上)6500
(51~60)(10+)(本科以下)7000
(51~60)(10+)(本科及以上)7500

然后我们来写最终的计算工资的公式

F2 内填入的公式为

=VLOOKUP("("&VLOOKUP(C2,G:H,2,TRUE)&")("&VLOOKUP(D2,I:J,2,TRUE)&")("&VLOOKUP(E2,K:L,2,FALSE)&")",M:N,2,TRUE)

最终表格预览

做好的表格是这样的

image

你可以先保存成Excel模板，然后把公式保存成值，再删除辅助列，另存为交给领导的最终表格，这次的任务就完成啦！

下次再需要计算同样要求的工资表的时候，就可以直接打开Excel模板进行套用了，非常方便。