編按：
對比兩組產(chǎn)品的異同包括找出它們的相同處和不同處。如果數(shù)據(jù)量少，手工就可以做。如果數(shù)據(jù)量多，則可以借助Excel實現(xiàn)自動對比。本質(zhì)上來說，對比產(chǎn)品的異同與核對兩組數(shù)據(jù)的差別是一回事。借助一對多查詢，我們可以快速獲得任意兩個產(chǎn)品或者兩組數(shù)據(jù)的異同點。

正文：

日常的工作中我們經(jīng)常會遇到這樣的情況：對比并查詢兩款不同型號的產(chǎn)品在功能上的異同點。過去我們都是拿著產(chǎn)品手冊來查詢的，不僅效率低下，而且還非常容易出錯。

本著“懶是社會進步的源動力”這一原則，我們直接用EXCEL來替我們查找異同點。

下面是某公司產(chǎn)品手冊上的產(chǎn)品功能圖，已經(jīng)做了一定的數(shù)據(jù)處理。

表中，如果單元格為空，表示沒有某項功能；如果單元格為“√”，表示具有某項功能并且參數(shù)為默認值；如果單元格為其他值，表示某項功能的具體參數(shù)。

我們希望在下圖中的單元格B2和F2中輸入兩個不同型號的產(chǎn)品如A1和A2后，EXCEL能自動列出兩種型號的相同點和差異點。

那如何實現(xiàn)上面的自動對比效果呢？
兩個產(chǎn)品的異同對比，實際就是比較它們功能參數(shù)的異同，按功能比較I列數(shù)據(jù)和J列數(shù)據(jù)的差別。

為了方便大家，我將規(guī)格表和查詢表放在了一起。

在單元格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)))&""

三鍵回車并向下拖曳即可。

函數(shù)解析：

本質(zhì)上講，這個公式依舊是一個一對多的查詢公式。相同功能項需同時滿足兩個條件：條件1，參數(shù)相等；條件2，不為空值。判斷兩個條件是否同時滿足，可以將兩個條件的判斷結(jié)果相乘來實現(xiàn)。

1.   MATCH(B$2,$I$1:$R$1,)和MATCH(F$2,$I$1:$R$1,)部分，定位產(chǎn)品A1和產(chǎn)品A2在產(chǎn)品表中的列數(shù)值。

2.   INDEX($I$2:$R$21,,MATCH(B$2,$I$1:$R$1,))部分求得產(chǎn)品A1所在列的所有參數(shù)清單，其結(jié)果為{"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,))的結(jié)果為{"5200ml";"200W";"2000Pa";"√";0;"√";"√";0;"√";"√";0;0;"√";0;0;0;0;0;0;0}。

3.   用邏輯符號“=”判斷參數(shù)是否相等的結(jié)果為{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,))<>""部分是判斷參數(shù)是否為空，其結(jié)果為{TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。

5.   把上述兩個判斷相乘的結(jié)果是{1;1;1;1;0;1;0;0;1;1;0;0;1;0;0;0;0;0;0;0}。參數(shù)相等且不為空的都為1，其他則都為0。

6.   用IF函數(shù)賦值，等于1的，返回相應的行號；等于0的，返回4^8，也就是將不相等和均等于空值的賦予了極大值。

7.   用SMALL函數(shù)將IF函數(shù)的結(jié)果從小到大依次返回，不符合條件的自然排在了后方。

8.   為何最后要鏈接空值""？是為了將INDEX函數(shù)返回的0變?yōu)榭铡?/span>

相同功能找到后，再把功能的參數(shù)查找出來，這時用VLOOKUP函數(shù)就可以解決了。

圖四

在單元格B5中輸入“=IF(A5="","",VLOOKUP(A5,H:R,MATCH(B$2,$H$1:$R$1,),))”并向下拖曳即可。這個公式比較簡單，我們不再詳細介紹了。

接下來我們來看看如何提取差異點。

圖五

這里所謂的差異點，即兩種產(chǎn)品中的不同功能點，譬如有的功能只在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)))&""

三鍵回車并向下拖曳即可。

函數(shù)解析：

這次是要尋找不同點，因此使用了“<>”,然后利用一對多查詢公式即可返回需要的清單了。

最后，我們需要把參數(shù)提取出來。它們都很簡單：

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)&"","")

好了，今天和大家分享的就是這些內(nèi)容！

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關注公眾號，可隨時隨地學習Excel：

相關推薦：

不懂excel中的數(shù)組公式，怎么晉升高手？

MATCH：函數(shù)哲學家，找巨人做伴。新出道必學！

INDEX:函數(shù)中的精確制導導彈，最強大的瘸子

怎么用vlookup在兩個查找區(qū)域里查找？

版權申明：

文本作者EXCEL應用之家；同時部落窩教育享有專有使用權。若需轉(zhuǎn)載請聯(lián)系部落窩教育。