2015年5月18日 星期一

Excel-找出儲存格中是否含有指定字串並標示(FIND,COUNTIF)

網友想要由一個基本表(下圖右)中查詢在下圖左裡每個料號是否已登錄,還是為一個新產品?即只要比對前幾碼不符合者,就是一個新的料號,在新產品欄位中標示「NEW」。該如何處理?

【設計公式】

(1)

依據圖示,每個料號之後有一個「-」符號,用以找尋料號。

儲存格B2:=IF(COUNTIF($D$2:$D$25,LEFT(A2,FIND("-",A2)-1)),"","NEW")

FIND("-",A2):找尋「-」在儲存格內容中的第幾個字。

LEFT(A2,FIND("-",A2)-1):找出料號。

COUNTIF($D$2:$D$25,LEFT(A2,FIND("-",A2)-1)):判斷儲存格中的料號在料號資料表中出現的次數,如果為0,代表是一個未曾記錄的料號。

最後透過 IF 函數,將上式等於 0 者,標示「NEW」。

複製儲存格B2,往下各列貼上。

(2)

另,網友提到,由於料號資料表會一直新增資料,該如何修改公式?(如下圖)

如果要判斷的儲存格個數會變動,則需要藉助 COUNTA 函數來計算有幾個儲存格含有資料,再用 OFFSET 函數取得儲存格範圍。因此,公式修改如下:

儲存格B2:=IF(COUNTIF(OFFSET($D$2,,,COUNTA($D$2:$D$999),),
LEFT(A2,FIND("-",A2)-1)),"","NEW")

其中,假設料號中的儲存格數量不會超過 999 個。

複製儲存格B2,往下各列貼上。

沒有留言:

張貼留言

好康東東