2015年11月6日 星期五

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

在 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 p="">
使用 WEEKDAY 函數可以將星期幾用數字來表示,參數 2 是為了使用星期一以 1 表示,連續至星期日以 7 表示。所以 WEEKDAY(C6,2)<6 1="" 5="" p="">
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
關於這個公式的說明,請參考前一篇文章:

沒有留言:

張貼留言

檢視其他文章

好康東東