歡度春節之後，利用寒假開學前，來讓學校同仁，再次有機會親近 Excel，看看能否利用試算表工具來讓工作效能提升，所以製作了研習的範例。本篇是關於「資料驗證準則」，並提供了15個巧妙應用。
資料驗證可以藉由各種資料類型設定驗證準則後，讓使用者避免輸入錯誤的資料。
資料驗證的預設準則是「任意值」，也就是沒有設定任何的條件。
可以用以設定驗證準則的資料類別有：
整數：限輸入不具小數者，可以限制整數的範圍。
實數：可輸入包含小數者，可以限制整數的範圍。
清單：儲存格預設會以下拉式清單方式來顯示「來源」方塊中定義的內容。使用者可以自行輸入所有清單項目，其中每個項目以「,」隔開。或是給予已經輸入內容的儲存格範圍，當作清單項目。
如果不想以下拉式清單方式呈現，則可以取消勾選「儲存格內的下拉式清單」。
日期：輸入日期格式的數值，並可以限制日期的範圍。
時間：輸入時間格式的數值，並可以限制時間的範圍。
文字長度：可輸入文字並限制文字的長度範圍。
自訂：自行設計公式，只要公式結果傳回「TRUE」，即符合驗證準則，表示可以輸入該內容。若公式結果傳回「FALSE」，即不符合驗證準則，表示該輸入內容不被接受。
其實不管是整數、實數、日期、清單、時間、文字長度、自訂，都可以：
(1) 在指定文字方塊中輸入定數之外
(2) 將內容或公式寫在儲存格中，將後再選取該儲存格。
(3) 直接在文字方塊中輸入公式。
以下要針對驗證準則中以公式設計來實作練習，並提供15項常見的巧妙應用：
【公式設計】
1.限定不能輸入0
準則公式：=D2<>0
2.限定只能輸入數字
準則公式：=ISNUMBER(D3)
ISNUMBER 函數用以判斷儲存格內容是否為數值，並傳回 TRUE／FALSE。
3.限定只能輸入指定文字(例：男、女)
準則公式：=OR(D4="男",D4="女")
OR 函數用以判斷參數執行邏輯 OR 運算的結果，並傳回 TRUE／FALSE。
4.限定只能輸入偶數
準則公式：=MOD(D5,2)=0
MOD 函數用以傳回兩數相除的餘數。
5.限定輸入不得超過n個字(例：6)
準則公式：=LEN(D6)<=6
LEN 函數用以計算儲存格內容的字元數量，並傳回一個數值。
6.限定同一欄輸入唯一值
準則公式：=COUNTIF(D2:D100,D7)=1
COUNTIF 函數用以計算在儲存格範圍內和儲存格D7相同的數量是否為1，若傳回 TRUE，表示輸入唯一值，若傳回 FALSE，表示有重覆值。
7.限定只能輸入小數點位數(例：2)
準則公式：=ROUNDDOWN(D8,2)=ROUNDDOWN(D8,3)
ROUNDDOWN 函數用以將儲存格D8的數值無條件捨去至指定的小數點位數。本例中，若取小數點 2 位數的結果和取小數點 3 位的結果相同，則表示該數值的小點數位數未超過 2 位。
8.限定只能輸入質數
準則公式：=SUMPRODUCT(--(MOD(D9,ROW(INDIRECT("2:"&(D9-1))))=0))=0
公式的解析可參考：Excel-判斷是否為質數(SUMPRODUCT,MOD,ROW,INDIRECT)
9.限定輸入第一個字為英文字
準則公式：
=AND(CODE(UPPER(LEFT(D10,1)))<=90,CODE(UPPER(LEFT(D10,1)))>=65)
(1) 利用 LEFT 函數將儲存格D10內容取左邊第一個字（函數中的參數1）。
(2) 再利用 UPPER 函數將(1)式的傳回文字轉換為大寫英文字。
(3) 再利用 CODE 函數取得(2)式的傳回文字之 ASCII 碼數值。
(4) 利用 AND 函數判斷(3)式傳回的 ASCII 碼是否介於 65 和 90 之間。
圖片來源：Excel-製作ASCII碼對照表
10.限定輸入的文字中不能有空格
準則公式：=SUBSTITUTE(TEXT(D11,"")," ","")=TEXT(D11,"")
(1) 利用 TEXT 函數將儲存格C11的內容轉換為文字（使用者可能輸入數值）。
(2) 再以 SUBSTITUTE 函數將空格「" "」置換為空字串「""」。
若置換前和置換後的字串兩者相同，表示該字串中未含有空格。
11.限定輸入Email郵件(例：xx@yy.zz)
準則公式：
=AND(SUBSTITUTE(D12,"@","")<>D12,SUBSTITUTE(D12,".","")<>D12)
因為 Email 的郵件地址中必須包含「@」和「.」，所以：
(1) SUBSTITUTE(D12,"@","")<>D12：
將儲存格D12內容刪除「@」，必須和儲存格D12原內容不相同。
(2) SUBSTITUTE(D12,".","")<>D12：
將儲存格D12內容刪除「.」，必須和儲存格D12原內容不相同。
(3) 再以 AND 函數判斷(1)式和(2)式是否兩者同時成立，並傳回 TRUE／FALSE。
12.限定只能輸入早上時間
準則公式：=AND(D13>=0,D13<0.5)
因為在 Excel 中是數值 1 代表一天，所以如果是00:00~11:59的數值是 0~0.5(不含)，如果是12:00~23:59的數值是 0.5~1(不含)。
13.限定不能輸入未來日期
準則公式：=D14<=TODAY()
TODAY 函數會傳回今天的日期，所以若儲存格C14的日期數值若比今天大，則表示是未來日期，將不被允許。
14.限定只能輸入非假日的日期
準則公式：=WEEKDAY(D15,2)<6
WEEKDAY 函數可以取得星期幾對應的數值，本例中使用參數「2」，表示星期到星期日會對應傳回 1 到 7。所以如果 WEEKDAY 函數傳回值比 6 小，表示非假日的日期。
15.限定只能輸入當月的日期
準則公式：
=AND(YEAR(D16)=YEAR(TODAY()),MONTH(D16)=MONTH(TODAY()))
(1) 利用 YEAR 函數求得儲存格D16和今天(TODAY())的年度。
(2) 利用 MONTH 函數求得儲存格D16和今天(TODAY())的月份。
(3) 再利 AND 函數判斷(1)式和(2)式是否都成立，並傳回 TRUE／FALSE。
綜合結果如下：
參考其他研習範例：
留言列表