如何用Excel对比两类产品在功能上的异同点？

作者：EXCEL应用之家来源：部落窝教育发布时间：2020-12-24 16:42:58点击：7090

分享到:

收藏收藏人气：0人

版权说明：原创作品，禁止转载。

编按：
对比两组产品的异同包括找出它们的相同处和不同处。如果数据量少，手工就可以做。如果数据量多，则可以借助Excel实现自动对比。本质上来说，对比产品的异同与核对两组数据的差别是一回事。借助一对多查询，我们可以快速获得任意两个产品或者两组数据的异同点。

正文：

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

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

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

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

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

那如何实现上面的自动对比效果呢？
两个产品的异同对比，实际就是比较它们功能参数的异同，按功能比较I列数据和J列数据的差别。

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

在单元格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,))<>""),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变为空。

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

图四