[Excel]如何限制儲存格輸入格式? 以"字首為英文字母大寫，其餘為數字，共15碼"為範例

 

需求:

當一個excel表格需要由多位使用者來輸入資料時，難免會出現資料輸入錯誤問題，尤其是料號，品號或是訂單號的長類型編碼。若是問題屬於輸入錯誤"資料"但格式未錯誤，則可能需要讓excel做編碼遞增或遞減等等參照編碼原則的方式來檢核，這得視編碼原則內容而定;若是問題屬於輸入錯誤"格式"，則可利用excel中資料驗證功能的自訂函式來設定，同時自訂函式可依需求設定輸入格式提示描述和錯誤格式提示描述。本文處理的是輸入錯誤"格式"問題。

解法:

利用資料驗證的自訂函式來限制儲存格輸入格式，詳細設定方式可參考上述影片。本文主要說明定義"字首為英文字母大寫，其餘為數字，共15碼"的函式。

1.字首為英文字母大寫

利用LEFT函式來抓取輸入內容的左邊第一位字元，同時利用UPPER定義該字元為大寫。經過EXACT函式比對LEFT($A2, 1)和UPPER(LEFT($A2, 1))，若兩者相同，則EXACT函式輸出結果為TRUE，反之為FALSE。EXACT函式會將同一個英文字母的大小寫視為不同，因此EXACT函式用在該需求，非常合適。

EXACT(LEFT($A2, 1),UPPER(LEFT($A2, 1)))

2.總長度共15碼

LEN函式用於計算儲存格的字元數，所以若該儲存格的字元總長度為15，LEN($A2)=15輸出結果為TRUE。

LEN($A2)=15

3.除了字首為英文字母大寫，其餘為數字

利用RIGHT函式來抓取扣除左邊第一位字元的所有儲存格內容，再利用VALUE函式將內容轉為數字，再用ISNUMBER函式來判斷VALUE函式輸出是否為數字，若是，則輸出TRUE。若該儲存格內容不全為數字，VALUE函式則輸出#NAME，ISNUMBER函式判斷#NAME則為FALSE。

ISNUMBER(VALUE(RIGHT($A2, LEN($A2)-1)))

最終用AND函數定義上述三個函式需均為True，該儲存格的格式才能通過檢核，完整函式如下。

AND(EXACT(LEFT($A2, 1),UPPER(LEFT($A2, 1))), LEN($A2)=15,ISNUMBER(VALUE(RIGHT($A2, LEN($A2)-1))))