有網友想要在 Excel 工作表的儲存格中輸入資料時，希望只能接受特定格式的內容，其餘顯示錯誤訊息，該如何處理呢？。例如：(參考下圖)
編號的編碼規則
(1) 全部 5 碼
(2) 第 1 碼：B(必須為大寫)
(3) 第 2 碼：英文字(大小寫皆可)
(4) 第 3, 4, 5 碼：數字
通常這類的問題，大多使用「資料驗證」的手法來完成。
【參考做法】
(1) 選取想要輸入資料的儲存格。
(2) 選取[資料／資料驗證]功能表，再選取「資料驗證」選項。
(3) 在[設定]標籤下設定如下：
儲存格內允許：自訂。
公式：=AND(LEN(A4)=5,LEFT(A4,1)="B",CODE(UPPER(MID(A4,2,1)))<=90,
CODE(UPPER(MID(A4,2,1)))>=65,ISNUMBER(VALUE(MID(A4,3,3))))
LEN(A2)=5：設定「條件一／全部 5 碼」。
LEFT(A2,1)="B"：設定「條件二／第 1 碼：B(必須為大寫)」
CODE(UPPER(MID(A4,2,1)))<=90：設定「條件三／第 2 碼：英文字(大小寫皆可)」
利用 MID 函數取得儲存格中的第 2 個字元，利用 UPPER 函數將該字轉換為大寫，利用 CODE 函數將該字元轉換為 ASCII 碼。
因為大寫英文字母的 ASCII 碼是介於 65 到 90 之間。所以，設定CODE(UPPER(MID(A4,2,1)))<=90 和 CODE(UPPER(MID(A4,2,1)))>=65 這兩個條件。
(關於 ASCII 碼可參考：http://isvincent.pixnet.net/blog/post/30226102)
ISNUMBER(VALUE(MID(A4,3,3)))：設定「條件四／第 3, 4, 5 碼：數字」。因為 MID 函數取得儲存格A4中的第 3 碼起始的 3 碼，這三個字為文字，所以再透過 VALUE 函數將其轉換為數字，並且利用 ISNUMBER 來判斷三個連起來的文字是組合為一個數字。
最後，透過 AND 函數將以上的判斷式做邏輯 AND 的運算。
留言列表