在 Excel 中『資料驗證』是一個好用的工具,讓你在輸入資料時,可以設計一些防錯的機制,增加資料輸入的準確性。

以下例舉10個不同的『資料驗證』應用,大部分都有結合公式運算和一些判斷式。

要啟動『資料驗證』,必須先選取儲存格,然後在[資料/資料工具]功能表中,選取「資料驗證」。

Excel-10個資料驗證的巧妙應用

 

1. 限定只能輸入數字

選取儲存格C2,設定『資料驗證』的準則:

儲存格內允許:自訂

公式:=ISNUMBER(C2)

使用 ISNUMBER 函數來判斷儲存格內容是否為數值。

 

2. 限定只能輸入2位小數點位數

選取儲存格C3,設定『資料驗證』的準則:

儲存格內允許:自訂

公式:=ROUNDDOWN(C3,2)=ROUNDDOWN(C3,3)

ROUNDDOWN 函數可以執行無條件捨去某些小數點位數,所以如果把儲存格內容取小數點 2 位和 3 位之後的數字分別無條件捨去而仍相同者,表示沒有小數點第 3 位的內容,也就是輸入的是小數點 2 位的數值。

Excel-10個資料驗證的巧妙應用

 

3. 限定只能輸入偶數

選取儲存格C4,設定『資料驗證』的準則:

儲存格內允許:自訂

公式:=MOD(C4,2)=0

使用 MOD 函數來計算餘數,一個數除以 2 的餘數為 0,表示該數為『偶數』。

Excel-10個資料驗證的巧妙應用

 

4. 限定不能輸入未來日期

選取儲存格C5,設定『資料驗證』的準則:

儲存格內允許:自訂

公式:=C5<=TODAY()

TODAY 函數會傳回今天的日期。

Excel-10個資料驗證的巧妙應用

 

5. 限定只能輸入非假日的日期

選取儲存格C6,設定『資料驗證』的準則:

儲存格內允許:自訂

公式:=WEEKDAY(C6,2)<6

使用 WEEKDAY 函數可以將星期幾用數字來表示,參數 2 是為了使用星期一以 1 表示,連續至星期日以 7 表示。所以 WEEKDAY(C6,2)<6 表示傳回值必須符合 1 ~ 5。

0112

 

6. 限定輸入11月裡的日期

選取儲存格C7,設定『資料驗證』的準則:

儲存格內允許:日期

資料:介於

開始日期:=DATE(2015,11,1)

結束日期:=DATE(2015,11,30)

使用 DATE 函數將『年、月、日』參數轉換為日期數值。

 

7. 限定輸入不得超過6個字

選取儲存格C8,設定『資料驗證』的準則:

儲存格內允許:自訂

公式:=LEN(C8)<=6

使用 LEN 函數可以傳回儲存格內的文字有幾個字。

Excel-10個資料驗證的巧妙應用

 

8. 限定同一欄輸入唯一值

選取儲存格C9,設定『資料驗證』的準則:

儲存格內允許:自訂

公式:=COUNTIF(C2:C27,C9)=1

利用 COUNTIF 函數判斷在儲存格C2:C27範圍內和儲存格C9相同者只有 1 個,就具有唯一性。

 

9. 限定輸入第一個字為英文字

選取儲存格C10,設定『資料驗證』的準則:

儲存格內允許:自訂

公式:
=AND(CODE(UPPER(LEFT(C10,1)))<=90,CODE(UPPER(LEFT(C10,1)))>=65)

利用 UPPER 函數將文字轉成大寫,使用 CODE 函數可以將字元轉換為 ASCII 碼,而大寫英文字母的 ASCII 碼是介於 65 到 90 之間(參考下下圖)。

Excel-10個資料驗證的巧妙應用

 

10. 限定只能輸入質數

選取儲存格C11,設定『資料驗證』的準則:

儲存格內允許:自訂

公式:=SUMPRODUCT(--(MOD(C11,ROW(INDIRECT("2:"&(C11-1))))=0))=0

關於這個公式的說明,請參考前一篇文章:

http://isvincent.pixnet.net/blog/post/45518167

vincent 發表在 痞客邦 留言(3) 人氣()