​

文：老祝

今天和大家分享一组常用的函数套路，大家遇到类似问题可以直接拿来套用即可。

1、常规查询

如下图所示，要根据G2单元格姓名，在A~E数据区域中查询对应的年龄。

http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmw2vkTqHqQCGnYfqfUWN9RXHNlmMTMhJsI9chXSO8a2P0Rgib3zlI79JxUQR7rgRoEUYFD1Yh0XIgg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

经典套路：

=VLOOKUP(G2,B1:E6,4,0)

套路指南：

第三参数，指定要返回查询区域中第几列的内容，不是整个工作表的第几列。

2、逆向查询

如下图所示，要根据G2单元格姓名，在A~E数据区域中查询对应的工号。

http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmw2vkTqHqQCGnYfqfUWN9RX0dXbmlcufe5NW0n6h2kA9MxFE8Un1Tvs5gHiatzrROjjYEHgVD03aOQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

经典套路：

=LOOKUP(1,0/(G2=B2:B6),A2:A6)

套路指南：

=LOOKUP(1,0/(条件区域=指定条件),要返回的区域)

可以根据需要，将公式中的 0/(条件区域=指定条件)，写成：

 0/((条件区域1=指定条件1)*(条件区域2=指定条件2)*……)

从而实现任意角度的多条件查询。

3、查询万金油

如下图所示，要根据H2单元格姓名，查询所在的部门。

http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmw2vkTqHqQCGnYfqfUWN9RXdDiciaIxH6ISXEoEN20aANpngpxcntsaoOicEsmQgGNjaHFbzjBicax6kg/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

经典套路：

=INDEX(B1:F1,MATCH(H2,B2:F2,))

套路指南：

由MATCH函数找到查询值的精确位置，然后由INDEX函数返回指定区域中，对应位置的内容。可以实现上下左右任意方向的查询。

4、年龄计算

如下图所示，要根据C列的出生年月计算年龄。

http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmw2vkTqHqQCGnYfqfUWN9RXFxjUarzCzf1eElKHAFR0ibArEdTH5tzWW6ahyicib3zG1fbo9h4wMmh3g/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

经典套路：

=DATEDIF(C2,TODAY(),"y")

套路指南：

虽是隐藏函数，却早已是众人皆知的秘密。

第一参数是开始日期，第二参数是结束日期，第三参数是返回的数据类型。

使用Y，表示返回整年数。

使用M，则表示返回整月数。

5、身份证计算

如下图所示，要根据C列的身份证号码计算出生年月。

http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmw2vkTqHqQCGnYfqfUWN9RXNyx7zXjCtPefaicRERW0DaAz1VC6Q0ic0DARJLFTZtqAFK2BAOHdviaWA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

经典套路：

=--TEXT(MID(C2,7,8),"0-00-00")

套路指南：

先使用MID函数，从C2单元格提取出表示出生年月的8位数字，再用TEXT函数将其转换为日期样式的文本。

最后使用两个负号进行运算，变成真正的日期序列值。

6、个人所得税计算

如下图所示，要根据E列工资额计算个税。

http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmw2vkTqHqQCGnYfqfUWN9RXsiaTTN684X0ZLAK1icLE5gObuJiaMI3EDPKWZnpwZN7wMaeLRCicpsiaSaw/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

经典套路：

=ROUND(MAX((E2-3500)*0.1*{0.3,1,2,2.5,3,3.5,4.5}-5*{0,21,111,201,551,1101,2701},0),2)

套路指南：

只要把公式中的E2换成实际的单元格引用即可，其他不用管它。

7、根据个税倒推税前工资

如下图所示，要根据E列个税金额计算税前工资。

http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmw2vkTqHqQCGnYfqfUWN9RX5icMchbsNGfywBdbWkQ06gaTpyKsubAzh6AREiaRSfvFviac9z61YiaA4Q/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

经典套路：

=ROUND(MIN(((E2 5*{0,21,111,201,551,1101,2701})/({0.3,1,2,2.5,3,3.5,4.5}/10)) 3500),2)

套路指南：

注意个税额不能为0。

工资还不够纳税起点的，蓝瘦不？

8、中国式排名

如下图所示，要对E列的成绩进行中国式排名，也就是相同成绩不占用名次。

http://mmbiz.qpic.cn/mmbiz_png/BAbVqibwwtmw2vkTqHqQCGnYfqfUWN9RXOnmOyEnWNBHZAugM5dZQkvYUgwbTacubr69A4xruBiaPngroqJ1UNXQ/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1

经典套路：

=SUMPRODUCT((E$2:E$6>E2)/COUNTIF(E$2:E$6,E$2:E$6)) 1

套路指南：

运算过程比较复杂，三言两语说不清了。

使用的时候，只要把公式中的单元格地址换成实际的数据区域即可。

---

​更多表格技巧，请关注：@EXCELers

​