學不完．教不停．用不盡

網友問到：在 Excel 的工作表中有個資料的原始清單，如何根據排除項目的清單，重新列出排除後的資料清單？

如下圖，排除清單裡已扣掉了排除項目。

 

【公式設計與解析】

根據A欄的原始清單儲存格範圍，定義名稱：原始清單。

根據D欄的排除項目儲存格範圍，定義名稱：排除項目。

本例需要一個輔助欄位（B欄），

儲存格B2：=COUNTIF(排除項目,A2)

公式用以計算儲存格B2在「排除項目」項目中出現的次數。

（0表示不在排除名單中，1表示在排除名單中）

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

根據B欄的輔助欄位項目儲存格範圍，定義名稱：輔助。

儲存格C2：

{=IFERROR(OFFSET($A$1,SMALL(IF(輔助=0,ROW(輔助),""),ROW(1:1))-1,0),"")}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

(1) IF(輔助=0,ROW(輔助),"")

在陣列公式中，判斷輔助欄位裡的儲存格是否為「0」，若是，則傳回列號，若否，則傳回空字串。

(2) SMALL(IF(輔助=0,ROW(輔助),""),ROW(1:1))

在陣列公式中，根據第(1)式傳回的列號陣列，利用 SMALL 函數由小至大依序取出列號。

當公式向下複製時，ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

(3) OFFSET($A$1,SMALL(IF(輔助=0,ROW(輔助),""),ROW(1:1))-1,0)

在陣列公式中，將第(2)式所得的列號代入 OFFSET 函數取得對應的儲存格內容。

(4) IFERROR(OFFSET($A$1,SMALL(IF(輔助=0,ROW(輔助),""),ROW(1:1))-1,0),"")

在陣列公式中，若 SMALL 函數傳回錯誤訊息時，利用 IFERROR 函數將此錯誤訊息顯示為空字串。

複製儲存格C2，往下各列貼上。