|
相信经常使用EXCEL的小伙伴们，对VLOOKUP函数并不陌生。这个函数是我们最常用的几个函数之一。相信，小伙伴们，也经常会用到这个函数。这里，咱们玩点其他的。 首先，给大家准备了源数据。如图： <1> 这里我们通过VLOOKUP函数来实现反向查询。  图1 数据源 如图1，G2 和H2 是我们的需求。要求都使用VLOOKUP函数完成。 H2很好完成，公式H2=VLOOKUP(F2,$B$2:$C$11,2,0). G2，因为姓名列在源数据中是在员工列的后面，直接使用VLOOKUP函数肯定不行，这里EK给大家把公式直接展示出来以后为小伙伴们分析一下。方便更好地理解。公式 G2 =VLOOKUP(B2,IF({1,0},B2:B11,A2:A11),2,0) 1、 如何理解公式首先，我们来拆解G2的公式，IF({1,0},B2:B11,A2:A11)表示的是2维数组，10行2列。如下图：  图2 数组表现图 2、通过图2，小伙伴们很清楚地理解了。我们只是将B列和A列的顺序通过IF函数来进行调换位置，来达到我们的查询目的。 效果图：  图4 查询效果图 到这里，反向查询已经完成。 <2> 查询一个部门的所有员工 首先，准备了一个数据源：  图5 数据源与需求 这里，给大家准备了2种方法。 第1种方法，需要在A列中增加一列辅助列。 效果图如图所示：  图6 查询部门员工效果图 这里，先分别把公式贴出来，A2=COUNTIF(A2:$A$2,$F$2)，表示，在A2到A2单元格中满足F2的个数，往下填充至A11，这里A11的公式变为，A11=COUNTIF($A$2:A11,$F$2) ， 表示，在A2到A11单元格中满足F2的个数. H列公式为：H2=IFERROR(VLOOKUP(ROW($A1),$A:$C,COLUMN(B1),0) ,'') I列公式为：I2=IFERROR(VLOOKUP(ROW($A1),$A:$C,COLUMN(C1),0),'') 公式分析：看到，H2和I2的公式都是差不多的。ROW($A1),返回1，ROW（$A2) 返回2，这里可以使我们的公式更好的重用.由此我们通过VLOOKUP和辅助列，将查找员工号和姓名，转换成查找部门在D2：D当前行号的F2出现的次数ROW。由此达到我们的查找F2部门所有员工的目的。 第2种方法： 如图：  图7 效果图 这里，没有增加辅助列，使用的是数组函数  图8 数组公式 我们可以看到图8 中G2的公式外面有花括号，这里花括号不是手输的。而是在写好的公式上通过三个组合键CTRL+SHIFT+ENTER一起按得到的。 G2的公式：=IFERROR(VLOOKUP($E$2&ROW(A1),IF({1,0},$C$2:$C$11&COUNTIF(INDIRECT('c2:c'&ROW($2:$11)),$E$2),A$2:A$11),COLUMN($B$1),),'')，在公式写完以后CTRL+SHIFT+ENTER一起按。 同样，H2的公式：=IFERROR(VLOOKUP($E$2&ROW(B1),IF({1,0},$C$2:$C$11&COUNTIF(INDIRECT('c2:c'&ROW($2:$11)),$E$2),B$2:B$11),COLUMN($B$1),),'')，在公式写完以后CTRL+SHIFT+ENTER一起按。 然后一起往下填充。效果如图8 所示。 分析公式： 1、H列和G列的公式类似，这里只分析G2，分析之前将G2公式分解。INDIRECT('c2:c'&ROW($2:$11)) ,返回的是单元格引用C2：C2，G3则返回C2：C3。 2、我们将组合函数 $C$2:$C$11&COUNTIF(INDIRECT('c2:c'&ROW($2:$11)),$E$2),B$2:B$11) 是数组函数，我们在I列给大家展示一下。如图所示：  图9 组合函数的数组元素列表 3、IF({1,0},$C$2:$C$11&COUNTIF(INDIRECT('c2:c'&ROW($2:$11)),$E$2),A$2:A$11)，第一个案例里已经说过，我们知道的是返回一个二维数组。如图：  图10 对应的二维数组 4、VLOOKUP($E$2&ROW(B1),IF({1,0},$C$2:$C$11&COUNTIF(INDIRECT('c2:c'&ROW($2:$11)),$E$2),B$2:B$11),COLUMN($B$1),)，G2对应的$E$2&ROW(B1) 值为武技部1，G3对应的$E$2&ROW(B2)值为武技部2，这里相信小伙伴们就能理解了，在图10 的数组中利用VLOOKUP函数查找出对应的工号。 5、最后利用IFFEROR函数对整个函数进行防错，错误值显示“”，对G2和H2中公式往下拉可以得到图8的效果，目的完成。 分析完毕，如果小伙伴们还是不清楚的话，可以在评论区告诉我。我会尽最大努力帮助大家哦。最后，感谢小伙伴们的观看，我是EK。如果上述内容能够帮助到你们，希望你们能点赞，关注，评论，你们不清楚的地方我会尽力为小伙伴们解答，谢谢小伙伴们的支持。我会给小伙伴们带来更多关于EXCEL的小技巧。 |
|
|
