判斷兩個Excel單元格區域是否有重復值

判斷兩個Excel單元格區域是否有重復值

如下圖所示的工作表，使用公式來判斷單元格區域A1:A3與C1:C3中是否有重復值。

從工作表中可以明顯看出，這兩個區域中都含有“Excel”，因此有重復值。

如何使用公式來判斷呢？

先不看答案，自已動手試一試。

公式思路

將單元格區域A1:A3中的每個值與C1:C3中的每個值比較，若都不相同，則沒有重復值，否則就存在重復值。

公式解析

在單元格E1中輸入下面的數組公式：

=OR(TRANSPOSE(A1:A3)=C1:C3)

其值為TRUE，表明兩個區域中存在重復值。如下圖所示。

公式中，使用TRANSPOSE函數將A1:A3轉置成水平方向的數組，即{“ Excel”,”excelperfect”,”Excel”}，然后，C1:C3中的值{“Office”;”Excel”;”Word”}逐一與轉置的數組相比較，即C1中的值“Office”與{“ Excel”,”excelperfect”,”Excel”}比較，C2中的值“Excel”與{“ Excel”,”excelperfect”,”Excel”}比較，C3中的值與{“ Excel”,”excelperfect”,”Excel”}比較，總共進行9次比較。若值相等，則返回TRUE，否則為FALSE。最后得到一個3行3列的數組：

{FALSE,FALSE,FALSE;FALSE,FALSE,TRUE;FALSE,FALSE,FALSE}

該數組作為OR函數的參數，獲取比較后的結果。只要數組中有一個值為TRUE，OR函數的返回值就為TRUE。本例中，數組中有一個值為TRUE，因此返回TRUE，則表明存在重復值。

應用擴展

擴展1：要判斷有無重復值的單元格區域方向不同

上文中判斷重復值的單元格區域都在列中，然而如果要判斷有無重復值的一個區域在列中，一個區域在行中，如下圖所示的工作表，判斷單元格區域A1:A3與C1:G1是否有重復值，那么如何編寫公式呢？

這種情形下，公式更簡單！在單元格C3中輸入數組公式：

=OR(A1:A3=C1:G1)

由于這兩個區域沒有重復值，因此其結果為FALSE，如下圖所示。

單元格區域A1:A3中的每個值逐一與單元格區域C1:G1中的每個值相比較，共進行15次比較，得到數組：

{FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE;FALSE,FALSE,FALSE,FALSE,FALSE}

作為OR函數的參數。由于數組中全為FALSE，因此結果為FALSE，表明兩個區域中沒有重復值。

擴展2：要比較的單元格區域具有更多的行列數

上述比較判斷兩個單元格區域是否具有重復值的方法，要求兩個區域的方向不同，一個是逐行，一個是逐列。然而，對于Excel 2003來說，工作表列數為256列，對于Excel 2007及以上版本來說，工作表列數為16384列，若數據量超過這些列數，就不能使用上面的方法了。

此時，可以使用MATCH函數與COUNT函數組合來判斷兩個單元格區域是否有重復值。

例如，對于前面的例子，可以使用數組公式：

=COUNT(MATCH(A1:A3,C1:G1,0))

如果結果為0，那么表明兩個單元格區域沒有重復值。如果結果為數字，那么該數字就表示這兩個區域具有的重復值個數。

注：可以給要比較的兩個區域命名，例如Data1和Data2，然后使用名稱代稱公式里的區域，例如=COUNT(MATCH(Data1,Data2,0))，這樣更靈活。

MATCH函數在區域Data2中查找區域Data1中的值，若沒有找到則產生錯誤值#N/A，若找到則獲取該值所在的列號。這樣，生成由#N/A或數字組成的數組。將該數組作為COUNT函數的參數，統計數組中的數字。若沒有數字，表明沒有重復值，則為0。若有數字，則返回數字的個數，也即重復值的個數。

小結

要比較兩個區域中的數據，這兩個區域的方向應不同。若兩個區域的方向相同，應使用TRANSPOSE函數將其中一個區域轉置。TRANSPOSE函數的用法詳情參見《Excel函數學習23：TRANSPOSE函數》。

上述方法比較的次數，也就是說產生的數組中元素個數取決于兩個區域所占的行列數，例如第1個例子比較的次數為3×3=9次，第2個例子比較的次數為3×5=15次。

Excel工作表中列的數據代表數組中的每行，即每個數據后都是分號；而工作表中的行數據代表數組中的每列，即每個數據后都是逗號，最后才是分號。

COUNT函數不會統計#N/A值，只返回非#N/A值的數量。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。