多位網友問到：在 Excel 中如何使用下拉式清單時，已經選過的項目不再顯示，即如何才能不選到重覆的項目？
參考下圖，已被選過的項目，不會在下拉式清單中出現，所以不會再被選到。
【公式設計與解析】
我沒有好的解決方案，只能利用一個輔助欄位（E欄）來將未被選取者，列出資料清單。
儲存格D2：{=IFERROR(OFFSET($C$1,SMALL(IF(COUNTIF($A$2:$A$11,
$C$2:$C$11)=0,ROW($C$2:$C$11),""),ROW(1:1))-1,0),"")}
這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。
複製儲存格E2，貼至儲存格E2:E11。
(1) COUNTIF($A$2:$A$11,$C$2:$C$11)
在陣列公式中，利用 COUNTIF 函數計算原始資料中的每一個項目，在已選欄位中出現的次數。（若為 0，表示尚未被選，若為 1，表示已經被選取。 ）
(2) IF(第(1)式=0,ROW($C$2:$C$11),"")
在陣列公式中，利用 ROW 函數將第(1)式的結果為 0 者，傳回其列號。（若為 1 者，傳回空字串。）
(3) SMALL(第(2)式,ROW(1:1))
當公式向下複製時，利用 SMALL 函數依序取出由小至大列號。（其中 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。）
(4) OFFSET($C$1,第(3)式-1,0)
將第(3)式傳回的列號代入 OFFSET 函數取得對應的儲存格內容。
(5) IFERROR(第(4)式,"")
若有傳回錯誤訊息，則以 IFERROR 函數將錯誤訊息轉換為空字串。
接著，要來設計下拉式清單。
先選取儲存格A2:A11，進入「資料驗證」對話框中，設定：
資料驗證準則：清單。
來源：=OFFSET($E$2,0,0,COUNTA($E$2:$E$11)-COUNTIF($E$2:$E$11,""),1)
(1) COUNTA($E$2:$E$11)-COUNTIF($E$2:$E$11,"")
COUNTA($E$2:$E$11)：利用 COUNTA 函數計算E欄中的全部資料數量；
COUNTIF($E$2:$E$11,"")：利用 COUNTIF 函數計算E欄中內容為空字串的數量；
兩者相減的結果，即為有內容（非空字串）的項目數量。
(2) OFFSET($E$2,0,0,第(1)式,1)
將第(1)式代入 OFFSET 函數以取得未選資料項目的儲存格範圍。
留言列表