網友問到：在 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，往下各列貼上。
留言列表