Excel的資料有效性工具

Excel的資料有效性是一個十分有用的工具，不僅可以防止在單元格輸入無效的資料，還能夠有效提高資料錄入的效率。

點選「資料」選項卡，點選「資料有效性」工具，然後選擇「資料有效性」選單，便可開啟「資料有效性」對話方塊。

首先來看看「設定」標籤，點選「允許」下拉框，可以檢視對單元格進行資料有效性設定的種類，如果選擇其中的「整數」、「小數」、「日期」、「時間」或者「文字長度」，系統會要求同時設定資料的大小起始或者長短等，則相應單元格只能輸入限制範圍內的該資料類型，否則系統報錯。

如果選擇「序列」，則需要給定一個清單，單元格只能在給定的清單中選擇輸入，當游標移至該單元格時，會出現供選擇的下拉框。

使用者也可以選擇在「自定義」，利用函數公式進行更加靈活的資料有效性設定。

點開「輸入資訊」標籤，可以在「輸入資訊」中備註錄入提醒等，游標選中單元格時可以顯示這些提醒資訊。

最後再看看「出錯警告」標籤。通過選擇「樣式」可以設定單元格輸入無效資料後的報錯方式：「停止」表示如果輸入無效資料必須重新輸入否則不能繼續下一步；選擇「警告」和「資訊」可以忽略錯誤繼續下一步操作。使用者還可以在「錯誤資訊」框中輸入自定義的錯誤資訊替代系統預設的「錯誤資訊」。

「資料有效性」按鈕之下還有「圈釋無效資料」和「清除無效資料標識圈」選單，其作用是用紅色橢圓標識已經設定資料有效性但錄入了無效資料的單元格和清除該標識。

接下來，我們用例項說明。

1、限制只能輸入早於今天的日期

游標選中單元格或者單元格區域，如前所述，開啟「資料有效性」對話方塊，「允許」下拉框中選擇「日期」，「資料」下拉框選擇「小於或等於」，在「結束日期」處鍵入公式「=Today()」，表示只允許錄入小於等於今天即錄入日的日期。

Excel中的日期實質其實就是數字，一般採用的是1900年日期系統，即數字1 代表1900/1/1，日期每增加一天，數字加1，所以日期也是可以比較大小的。

2、限制只能輸入數字

除了類似前一個例子的方法，在「允許」下拉框中選擇「整數」或者「小數」，然後設定數值的範圍，還可以用函數公式。

在「允許」下拉框中選擇「自定義」，然後在公式處鍵入公式「=ISNUMBER(H2)」。ISNUMBER函數判斷參數是否為數值並返回邏輯值，返回TRUE時允許錄入，返回FALSE則限制錄入。

3、一個單元格有兩個或以上限制條件

譬如錄入手機號碼，有兩個限制條件：只能錄入數字且長度為11位。

在「允許」下拉框選擇「自定義」，在公式處鍵入「=AND(ISNUMBER(F2),LEN(F2)=11)」，AND函數表示「與」的關係，需要參數均為TRUE時才會返回TRUE，否則返回FALSE限制錄入。

如果除了11位的手機號碼，也允許錄入8位數的座機號碼，將公式改為「=AND(ISNUMBER(F2),OR(LEN(F2)=8,LEN(F2)=11))」即可。OR函數表示「或」的關係，只要參數有一個為TRUE，函數即返回TRUE。（關於邏輯函數，請參閱Excel邏輯函數的使用技巧）

4、限制只能輸入不重複資料

在「允許」下拉框選擇「自定義」，在公式處鍵入「=COUNTIF(I:I,I2)=1」，COUNTIF函數統計第一個參數區域內等於第二個參數的單元格數量，等於1返回TRUE，否則說明有重複資料，限制錄入。

5、生成下拉框，從列表中選擇錄入

在「允許」下拉框中選擇「序列」，然後在「來源」處鍵入序列內容，可以如圖鍵入一個引用區域「=$J$2:$J$16」，也可以直接鍵入文字內容，譬如「肖瑤,周桃,鄭開,趙六,張三......」，中間用英文逗號分開，還可以使用名稱（後一個例子將用到）。

錄入時在下拉框中選擇即可。

6、生成多級下拉框

即根據上一級的錄入資料動態生成下一級下拉框內容。譬如，區域處選擇"北區"，省份下拉框中便只有北區的省份。

省份選擇了「天津」，城市下拉框便只有天津的區域。

實現多級下拉框需要創建名稱、使用函數公式和資料有效性。

首先要準備好創建名稱的列表即多級下拉框中的內容，紅色單元格區域是區域，黃色是省份，綠色是城市，且區域是省份的列標題，省份是城市的列標題，這樣才能實現三者的聯動。

然後創建名稱，首先創建第一級「區域」的名稱，點選「公式」選項卡，點選「名稱管理器」，在對話方塊中點選「新建」按鈕彈出「新建名稱」對話方塊，名稱處鍵入「區域」，引用位置處選擇單元格區域「=data!$A$1:$E$1」。

接下來創建第二級「省份」的名稱，選中單元格區域A1:E16，點選名稱管理器旁的「根據所選內容創建」。

在彈出的對話方塊中，只勾選「首行」的複選框，則批量創建了一批以區域為名稱省份為內容的名稱。

同樣方法批量創建以省份為名稱城市為內容的名稱。

所前所述，所有下一級的名稱必須與上一級的內容一致，這是實現多級下拉框的關鍵。在「名稱管理器」對話方塊可以檢視、修改已創建名稱（關於名稱，請參閱Excel中越用越香的「名稱」工具）。

名稱創建完成後，開始設定資料有效性。選中區域單元格，開啟「資料有效性」對話方塊，「允許」處選擇「序列」，來源處鍵入「=區域」，其中「區域」是剛才建立的名稱。

選中省份單元格，「允許」處選擇「序列」，來源處鍵入公式「=INDIRECT($B2)」。INDIRECT函數的功能是返回字元串指定的引用，當如圖用一個地址當作參數時，將讀取地址中的內容作為字元串地址，因為地址中的內容與系統中已創建名稱同，因此係統會將它識別為一個名稱，並且賦值給「序列」。

游標選中城市單元格，來源鍵入「=INDIRECT($C$2)」，原理同上（關於INDIRECT函數，請參閱Excel中，利用Indirect()函數輕鬆引用工作表資料）。

多級下拉框便完成了。