學不完．教不停．用不盡

多位網友問到：在 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 函數以取得未選資料項目的儲存格範圍。