超牛的Excel动态查询表！不管老板想对比哪两组数据，秒速给他结果！

编按：让Excel实现动态查询，这是数据分析所必备的技能之一。老板们也都很爱这样直观又方便的查询办法。今天，小E给大家带来的就是如何快速实现这个操作的方法……每天一点小技能，职场打怪不得怂！

正文：

日常的工作中我们经常会遇到这样的情况：对比并查询两款不同型号的产品在功能上的异同点。过去我们都是拿着产品手册来查询的，不仅效率低下，而且还非常容易出错。

本着“懒是社会进步的源动力”这一原则，我们直接用EXCEL来替我们查找异同点。

下面是某公司产品手册上的产品功能图，已经做了一定的数据处理。

表中，如果单元格为空，表示没有某项功能；如果单元格为P，表示具有某项功能并且参数为默认值；如果单元格为其他值，表示某项功能的具体参数。

我们希望在下图中的单元格B2和F2中输入两个不同型号的产品如A1和A2后，EXCEL能自动列出两种型号的相同点和差异点。

 

01

那如何实现上面的自动对比效果呢？

为了方便大家，我将规格表和查询表放在了一起。

在单元格A5中输入公式=INDEX(H:H,SMALL(IF((INDEX($I$2:$R$21,,MATCH(B$2,$I$1:$R$1,))=INDEX($I$2:$R$21,,MATCH(F$2,$I$1:$R$1,)))*(INDEX($I$2:$R$21,,MATCH(B$2,$I$1:$R$1,))<< span="">>""),ROW($2:$21),4^8),ROW(A1)))&""

三键回车并向下拖曳即可。

函数解析：

本质上讲，这个公式依旧是一个一对多的查询公式。相同功能项需同时满足两个条件：条件1，参数相等；条件2，不为空值。判断两个条件是否同时满足，可以将两个条件的判断结果相乘来实现。

1.MATCH(B$2,$I$1:$R$1,)和MATCH(F$2,$I$1:$R$1,)部分，定位产品A1和产品A2在产品表中的列数值。

2.INDEX($I$2:$R$21,,MATCH(B$2,$I$1:$R$1,))部分求得产品A1所在列的所有参数清单，其结果为{"5200ml";"200W";"2000Pa";"√";"√";"√";0;0;"√";"√";0;"√";"√";0;0;0;0;0;0;0}；同理，INDEX($I$2:$R$21,,MATCH(F$2,$I$1:$R$1,))的结果为{"5200ml";"200W";"2000Pa";"√";0;"√";"√";0;"√";"√";0;0;"√";0;0;0;0;0;0;0}。

3.用逻辑符号“=”判断参数是否相等的结果为{TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}。

4.INDEX($I$2:$R$21,,MATCH(B$2,$I$1:$R$1,))<>""部分是判断参数是否为空，其结果为{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。

5.把上述两个判断相乘的结果是{1;1;1;1;0;1;0;0;1;1;0;0;1;0;0;0;0;0;0;0}。参数相等且不为空的都为1，其他则都为0。

6.用IF函数赋值，等于1的，返回相应的行号；等于0的，返回4^8，也就是将不相等和均等于空值的赋予了极大值。

7.用SMALL函数将IF函数的结果从小到大依次返回，不符合条件的自然排在了后方。

8.为何最后要链接空值""？是为了将INDEX函数返回的0变为空。

02

相同功能找到后，再把功能的参数查找出来，这时用VLOOKUP函数就可以解决了。

在单元格B5中输入“=IF(A5="","",VLOOKUP(A5,H:R,MATCH(B$2,$H$1:$R$1,),))”并向下拖曳即可。这个公式比较简单，我们不再详细介绍了。

03

接下来我们来看看如何提取差异点。

 

这里所谓的差异点，即两种产品中的不同功能点，譬如有的功能只在A1中有，也有的功能只在A2中有。

我们在单元格D5中输入公式

=INDEX(H:H,SMALL(IF((INDEX($I$2:$R$21,,MATCH(B$2,$I$1:$R$1,))<>INDEX($I$2:$R$21,,MATCH(F$2,$I$1:$R$1,))),ROW($2:$21),4^8),ROW(A1)))&""，并三键回车并向下拖曳即可。

函数解析：

这次是要寻找不同点，因此使用了“<>”,然后利用一对多查询公式即可返回需要的清单了。

04

最后，我们需要把参数提取出来。它们都很简单：

1.在E5单元格输入公式=IFERROR(VLOOKUP(D5,H$1:R$21,MATCH(U$2,H$1:R$1,),0)&"","")

2.在F5单元格输入公式=IFERROR(VLOOKUP(D5,H$1:R$21,MATCH(F$2,H$1:R$1,),0)&"","")

好了，今天和大家分享的就是这些内容！