學不完．教不停．用不盡

在 Excel 裡，如果要限制儲存格的輸入內容，該如何處理？

通常是利用「資料驗證」方式來設計，以下有二種不同的設計。

【以下拉式清單選取】

如果提供下拉式清單讓使用者選取，不以輸入文字的方式來輸入。

選取儲存格A4:A12，設定資料驗證準則：

儲存格內允許：清單

來源：甲,乙,丙,丁,戊,己,庚,辛,壬,癸

【限制能輸入的字元】

如果想限制儲存格能輸入的文字為「甲,乙,丙,丁,戊,己,庚,辛,壬,癸」，則選取儲存格C4:C12，設定資料驗證準則：

儲存格內允許：自訂

公式：=ISNUMBER(SUMPRODUCT(FIND(MID(C4,ROW($1:$10),1),"甲乙丙丁戊己庚辛壬癸")))

公式解析：

公式中有使用 SUMPRODUCT 函數，可以使用陣列概念來處理。

(1) MID(C4,ROW($1:$10)

利用 ROW 函數和 MID 函數將儲存格C4的內容取出。(本例的原始字串「甲,乙,丙,丁,戊,己,庚,辛,壬,癸」，最多10個字。)

(2) FIND(MID(C4,ROW($1:$10),1),"甲乙丙丁戊己庚辛壬癸")

利用 FIND 函數將第(1)式傳回的每一個字和「甲乙丙丁戊己庚辛壬癸」比對是否有相符者，若「有」則傳回位置(數字)，若「無」則傳回錯誤訊息。

(3) SUMPRODUCT(FIND(MID(C4,ROW($1:$10),1),"甲乙丙丁戊己庚辛壬癸"))

將第(3)式傳回的陣列以 SUMPRODUCT 函數予以加總並傳回一個數字。

(4) ISNUMBER(第3式)

以 ISNUMBER 函數判斷第(3)式是否傳回一個「數字」。表示儲存格C4裡的每一個字都是「甲乙丙丁戊己庚辛壬癸」其中之一，若是含有一個以上的錯誤訊息，則不會傳回數值。

【參考資料】

 SUMPRODUCT 函數參考微軟提供的說明網頁：SUMPRODUCT 函數

 FIND,FINDB 函數參考微軟提供的說明網頁： FIND,FINDB 函數