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

從工作表中可以明顯看出，這兩個(gè)區(qū)域中都含有“Excel”，因此有重復(fù)值。

如何使用公式來(lái)判斷呢？

先不看答案，自已動(dòng)手試一試。

公式思路

將單元格區(qū)域A1:A3中的每個(gè)值與C1:C3中的每個(gè)值比較，若都不相同，則沒(méi)有重復(fù)值，否則就存在重復(fù)值。

公式解析

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

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

其值為T(mén)RUE，表明兩個(gè)區(qū)域中存在重復(fù)值。如下圖所示。

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

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

該數(shù)組作為OR函數(shù)的參數(shù)，獲取比較后的結(jié)果。只要數(shù)組中有一個(gè)值為T(mén)RUE，OR函數(shù)的返回值就為T(mén)RUE。本例中，數(shù)組中有一個(gè)值為T(mén)RUE，因此返回TRUE，則表明存在重復(fù)值。

應(yīng)用擴(kuò)展

擴(kuò)展1：要判斷有無(wú)重復(fù)值的單元格區(qū)域方向不同

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

這種情形下，公式更簡(jiǎn)單！在單元格C3中輸入數(shù)組公式：

=OR(A1:A3=C1:G1)

由于這兩個(gè)區(qū)域沒(méi)有重復(fù)值，因此其結(jié)果為FALSE，如下圖所示。

單元格區(qū)域A1:A3中的每個(gè)值逐一與單元格區(qū)域C1:G1中的每個(gè)值相比較，共進(jìn)行15次比較，得到數(shù)組：

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

作為OR函數(shù)的參數(shù)。由于數(shù)組中全為FALSE，因此結(jié)果為FALSE，表明兩個(gè)區(qū)域中沒(méi)有重復(fù)值。

擴(kuò)展2：要比較的單元格區(qū)域具有更多的行列數(shù)

上述比較判斷兩個(gè)單元格區(qū)域是否具有重復(fù)值的方法，要求兩個(gè)區(qū)域的方向不同，一個(gè)是逐行，一個(gè)是逐列。然而，對(duì)于Excel 2003來(lái)說(shuō)，工作表列數(shù)為256列，對(duì)于Excel 2007及以上版本來(lái)說(shuō)，工作表列數(shù)為16384列，若數(shù)據(jù)量超過(guò)這些列數(shù)，就不能使用上面的方法了。

此時(shí)，可以使用MATCH函數(shù)與COUNT函數(shù)組合來(lái)判斷兩個(gè)單元格區(qū)域是否有重復(fù)值。

例如，對(duì)于前面的例子，可以使用數(shù)組公式：

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

如果結(jié)果為0，那么表明兩個(gè)單元格區(qū)域沒(méi)有重復(fù)值。如果結(jié)果為數(shù)字，那么該數(shù)字就表示這兩個(gè)區(qū)域具有的重復(fù)值個(gè)數(shù)。

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

MATCH函數(shù)在區(qū)域Data2中查找區(qū)域Data1中的值，若沒(méi)有找到則產(chǎn)生錯(cuò)誤值#N/A，若找到則獲取該值所在的列號(hào)。這樣，生成由#N/A或數(shù)字組成的數(shù)組。將該數(shù)組作為COUNT函數(shù)的參數(shù)，統(tǒng)計(jì)數(shù)組中的數(shù)字。若沒(méi)有數(shù)字，表明沒(méi)有重復(fù)值，則為0。若有數(shù)字，則返回?cái)?shù)字的個(gè)數(shù)，也即重復(fù)值的個(gè)數(shù)。

小結(jié)

要比較兩個(gè)區(qū)域中的數(shù)據(jù)，這兩個(gè)區(qū)域的方向應(yīng)不同。若兩個(gè)區(qū)域的方向相同，應(yīng)使用TRANSPOSE函數(shù)將其中一個(gè)區(qū)域轉(zhuǎn)置。TRANSPOSE函數(shù)的用法詳情參見(jiàn)《Excel函數(shù)學(xué)習(xí)23：TRANSPOSE函數(shù)》。

上述方法比較的次數(shù)，也就是說(shuō)產(chǎn)生的數(shù)組中元素個(gè)數(shù)取決于兩個(gè)區(qū)域所占的行列數(shù)，例如第1個(gè)例子比較的次數(shù)為3×3=9次，第2個(gè)例子比較的次數(shù)為3×5=15次。

Excel工作表中列的數(shù)據(jù)代表數(shù)組中的每行，即每個(gè)數(shù)據(jù)后都是分號(hào)；而工作表中的行數(shù)據(jù)代表數(shù)組中的每列，即每個(gè)數(shù)據(jù)后都是逗號(hào)，最后才是分號(hào)。

COUNT函數(shù)不會(huì)統(tǒng)計(jì)#N/A值，只返回非#N/A值的數(shù)量。