2014年9月18日 星期四

Excel-限定儲存格輸入的格式(資料驗證)

有網友想要在 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 的運算。

沒有留言:

張貼留言

好康東東