2015年2月4日 星期三

Excel-剔除重覆者(OFFSET,SMALL,COUNTIF,ROW,陣列公式)

網友想要在 Excel 的資料表中 A, B兩組找出重覆者剔除,也就是說要留下完全沒有重覆者的名單,該如何處理呢?(參考下圖)


【準備工作】

1. 將A組資料和B組資料分別複製到C欄的「合併」欄位下。

2. 選取儲存格C1:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:合併。

 

【設計公式】

儲存格D2:{=OFFSET($C$1,SMALL(IF(COUNTIF(合併,合併)=1,ROW(合併),999),ROW(1:1))-1,)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{、}」

COUNTIF(合併,合併):在陣列公式中找出「合併」欄位中的每一個儲存格在C欄中出現幾次,若為 1 則出現一次,若大於 1 代表重覆出現。

IF(COUNTIF(合併,合併)=1,ROW(合併),999):在陣列公式中,若儲存格內容沒有重覆出現,則傳回「合併」的列號(例如:「辛」傳回 2 ),若有重覆則傳回「999」。(在此,999 只是設定一個較大的數待用而已。)

SMALL(IF(COUNTIF(合併,合併)=1,ROW(合併),999),ROW(1:1)):利用 SMALL 函數在公式向下複製時,可以取得第 1, 2, 3, … 小的數值,這是不重覆名單的第 1, 2, 3, … 小的列號。

最後,透過 OFFSET 函數,根據上式求得的列號來取得儲存格內容。

沒有留言:

張貼留言

好康東東