2010年1月14日 星期四

Excel-找出第幾個重覆

在Excel資料表中(如下圖),如果想要找出那一個內容重覆,該如何處理呢?例如:先建立一組流水號(公式中會用到),在內容的儲存格則顯示大寫英文字母,來檢驗與第幾個儲存格重覆。

C1儲存格中的公式為陣列公式(要按Ctrl+Alt+Enter):

{=IFERROR(SMALL(IF((($B$2:$B$21=$B2)*($A$2:$A$21<>$A2)),$A$2:$A$21),1),"")}

在IF函數中的「*」,代表AND運算。

$B$2:$B$21=$B2會產生B2在B2:B21中是否相同的邏輯值陣列

(True;False;False;…..;True;False;False)

$A$2:$A$21<>$A2會產生A2在A2:A21中是否不相同的邏輯陣列

(False;False;False;……;True;False;False)

此舉在找出是否為自己和自己重覆

($B$2:$B$21=$B2)*($A$2:$A$21<>$A2)會得到一個AND後的運算結果

(True;False;False;…..;True;False;False) AND (False;False;False;……;True;False;False)

=(Fasle;False;Fasle;False;……;True;Fasle;Fasle)

公式變為:

{=IFERROR(SMALL(IF(((Fasle;False;Fasle;False;……;True;Fasle;Fasle),$A$2:$A$21),1),"")}

因為第18個為True,所以公式結果為:

{=IFERROR(SMALL(18,1),"")}

=18

其中SMALL函數的用途為如果有多個重覆時,會顯示第一個重覆的位置。

而IFERROR函數的用途為如果都沒有重覆時,可以不顯示錯誤訊息。

沒有留言:

張貼留言

好康東東