excel中的資料有效性怎麼用？

excel中的資料有效性的作用就是控制資料在一定有效匯圍內，可以防止誤操作輸入錯誤的資料值。

如圖，選定資料表，點資料欄，選資料有效性：

針對學生成績的情況，設定資料有效範圍在 0--100之間的小數：

設定輸入資料時的提示資訊：

設定資料輸入出錯以後的警告資訊：

如圖，在輸入時有提示資訊顯示：

輸入超出範圍數值會有出錯警告：

excel中的資料有效性就這麼用，很簡單，試一下吧。

Excel中的資料有效性怎麼用？

什麼是資料有效性？在Excel新版中它叫資料驗證，老版中才叫資料有效性，作用是什麼？如下圖的表格，比如在性別選項中，肯定是男女選項吧，偏偏有些奇葩總是喜歡填公母，雌雄......填個年齡吧，非得寫個12.5，186......輸身份證號也有人能輸多一位，少一位的，我也真的是想說一句......

好啦！說正題，怎麼才能避免這種情況，操作如下：

在視窗中的允許點選序列，

如圖所示：

性別選項的操作同上：

接著，我們看，

怎麼給年齡輸入資料有效性：

先選中需要進行操作的單元格，然後點選資料選單中的資料有效性，如圖所示：

在視窗中的允許選擇整數，如圖所示：

比如，要求客戶的年齡在1~120歲之間，就可以在最小值最大值中輸入數值，如圖所示：

同樣的，在資料選單中點選資料有效性，如圖所示：

在允許中選擇文字長度，在資料中選擇等於，如圖所示：

因為身份證號的長度都是18位嘛，所以，長度的數值就輸入18就可以了，如圖所示：

好的，資料有效性的操作就完成了，但是以上的操作是在輸入值之前進行資料驗證，如果這時候的資料是別人拿已經弄好的資料要你來驗證，怎麼操作呢？

如圖，先選中需要驗證的單元格。

同樣的，在資料選單中點選資料有效性，如圖所示：

在視窗中設定需要的值，如圖所示：

這樣，在Excel表格中，錯誤的資料就被全出了，只需要改正就OK啦！

我將從以下幾個方面回答這個問題：

什麼是資料有效性；

應用例項：限定輸入性別；

應用例項：限定輸入身份證；

應用例項：限定輸入工齡；

配合公式使用，什麼樣的需求，都能滿足

擴充套件

什麼是資料驗證（有效性）

從規則列表中進行選擇以限定可以在單元格輸入的內容，說白了，那就是規範使用者輸入。

資料驗證的方式很多，選單點開資料驗證，可以根據需要直接選擇限制的方式，還可以選擇給使用者的提示資訊，出錯時的提醒等，如下所示：

應用例項：限定輸入性別

先來一個簡單的案例，性別錄入。

選擇區域；

選單選擇資料驗證；

則單元格中提供下拉列表供使用者選擇輸入，若輸入其他內容，則Excel會有一個錯誤提醒。如下所示：應用例項：限制輸入的文字長度

如限定錄入身份證，那就可以使用驗證限定錄入18位數的文字，如下所示：

應用例項：限定輸入工齡

可以限定0~50的整數，如下所示：

資料驗證+公式

函式是Excel中最為強大的模組之一，工作中許多的工作都需要公式來完成，那，資料驗證，自然也要支援公式的。

如很常用的拒絕錄入重複項，如下設定：

再來個複雜的，驗證中的序列將在Excel介面上提供下拉列表，也就是選單。透過配合名稱管理器和函式，還可以實現更為複雜聯動式多級選單，具體實現步驟，大家可以找下我的文章！

Excel中的資料處理都是遵循一定的規律的，當資料內容雜亂無序的時候，就會增加資料整理、分析的難度，如何保證資料的一致性和有效性呢？我們就可以透過excel“資料一致性”功能來實現了。

1、固定單元格選項

同一個內容不同人有不同的表達，如“銷售部”和“銷售部門”，雖然意思相同，但輸入內容不同，在刷選資料的過程中就會出現問題，所以，我們就需要透過“資料有效性”來輸入固定資料。

2、避免重複錄入

對於一些具有唯一性的資料，如合同單號、表單號等，我們就可以透過“資料有效性”功能來避免錄入重複值。

操作：在有效性條件中選擇“自定義”，然後在公式框中，輸入“=COUNTIF(E:E,E1)<=1”其中“E”代表資料所在的列。

3、限制文字長度

身份證號、手機號等資料都有明確的長度，在輸入的時候，我們可以透過限制文字長度，來減少輸入錯誤的可能。

操作：在“有效性條件”中選擇“文字長度”，資料欄中選擇“等於”，長度選為“18”。

4、出錯警告

不同的輸入有不同的錯誤，我們還可以在“出錯警告”中表明該列資料錄入標準，從而更好的進行資料校對。

操作：資料驗證—出錯警告，輸入“標題”和“錯誤資訊”提示即可。

excel中的資料有效性有不少的用法，下面簡單介紹一下常見的應用。

1、資料有效性配合名稱建立二級下拉選單。

1.1、設定資料來源，如下圖：

1.2、設定名稱。公式--根據所選內容建立--首行

1.3、建立部門序列

1.4、建立二級選單，輸入公式：=INDIRECT($B8)

2、用於考勤登記。

2.1、基礎資料：

2.2、資料有效性序列設定：

2.3、資料有效性提示設定

2.4、結果：

3、防止輸入無效資料。

N1單元格輸入公式：=COUNTIF($M$1:$M$5,N1)>0，然後下拉。

當輸入“非法”資料的時候，資料有效性報警。

輸入“合法”資料，則不報警，見N1單元格。

4、標識無效的資料。

4.1、資料表格如下：需要標識小於60的資料。

4.2、資料有效性設定如下：

4.3、使用資料有效性的圈釋無效資料，意為將小於60的資料圈起來。結果如下圖：

資料有效性有多種多樣的應用，以上為一些補充。

資料有效性在Excel 2013以上版本中叫做資料驗證，它的作用是從規則列表中進行選擇以限制可以在單元格中輸入的資料型別。

透過資料驗證，可以完成許多有用的功能，我們來看一下都有哪些神奇作用：

02、禁止重複輸入

有時我們需要設定在某些區域不能重複輸入，比如在姓名列就要求不能有重複，如何在能在規則方面進行限制呢？答案就是使用資料驗證。

Step2：在【允許】中選擇【自定義】，在【公式】中輸入公式：=COUNTIF(A:A,A2)=1

注意這個公式中有兩個等號，其中第一個等號是為了告訴Excel，接下來要輸入的內容是公式；第二個等號是用於判斷COUNTIF(A:A,A2)是否等於1。

這個公式的最終作用就是，在A列中輸入的內容，他們的數量被限度為1個，也就是說內容不能重複。

03、只能輸入特定的日期

同樣在【資料驗證】介面，在【允許】中選擇【日期】，在【資料】中選擇【介於】，也就是說限定日期只能在兩個日期之間，在【開始日期】中輸入想要設定的起始日期，在【結束日期】中設定結束的日期。

這樣設定完成之後，只能在資料區域輸入起始於結束日期之間的日期，其他時間段的日期都不允許輸入，如圖所示，當輸入其他日期則會提示警告：

04、輸入提示

針對一些沒有特定規律的內容，資料驗證也有妙招，比如我們需要採集員工資訊，其中地址一欄，需要填寫家庭地址而非公司地址，我們可以這樣進行提示。

這樣，當用戶選中單元格輸入內容時，就會時時提醒輸入正確的內容。

2、指定資料只能從下拉列表選取

假設我們要限制開支型別只能從“房租、水電、交通費、餐飲費、隨禮”中選擇。

（1）定義開支型別

（2）點橢圓形單元格，選單中【資料】、【資料驗證】（老版本的office叫資料有效性）、【資料驗證】

3、資料限制在指定範圍或只能輸入數字、字母等

第（1）、（2）步同上面的例子，

第（3）步：驗證條件選擇【整數】，最小值1，最大值1000

（4）輸入2000測試一下，因為2000不在1~1000中，會彈出驗證錯誤

首先，先明白資料有效性的作用，在excel表格中使用資料有效性主要是能有效的限制資料的錄入。對於符合條件的資料，允許輸入，不符合條件的則不允許輸入，可以規範資料錄入的正確性。

那麼如何使用資料有效性呢？

一、比如我們想控制B2:B7單元格區域中只能錄入指定資料：“小螃蟹”,“曉曉”,“安琳”,“派大星”

直接可以在單元格右下角展開下拉選單選擇資料，或者手動輸入這四個指定姓名。

如果輸入的不是這四個指定姓名則會彈出警告，這樣就不用擔心資料錄入錯誤的問題了。

二、如果我們要控制錄入的數字只能是6位數，則可以在驗證條件中選擇【文字長度】，資料選擇【等於】，長度輸入6，就能規範錄入的數字個數了。

一、資料唯一性------禁止輸入重複資料

有效條件我們選擇自定義，然後輸入公式 “=COUNTIF(A:A,A1)=1“

在使用資料唯一性的時候要先注意選中區域後在進行操作，後面的錯誤提示我們可以自定義。就是你怎麼告訴你怎麼來。

二、資料的選擇------相同內容選擇

我們可以在EXCEL中設定下拉選單，以方便我們輸入相同內容。同樣可以節省大量時間和出錯率

注意：這裡的逗號是英文狀態下輸入的。

三、資料準確性------限制資料的型別長度

資料的準確性我們可以限制我們輸入資料的位數來確保資料準確。當然我這這裡使用的是身份證號舉例子，手機號也可以限制11位。類似的還有很多。我就不一一列舉。

四、資料的連線性------限制跳行輸入

在日常工作中有填寫報表類的會有人跳行填寫，為避免資料不連線我們同樣可以用公司限制。

公式：=COUNTA(A$1:A1)=ROW(A1) （即為計算的是當前非空單元格的數目如果不等於逐步增加的行號，就限制輸入。）

=COUNTA(A$1:A1)為非空的的數目

其中ROW(A1)是提取的是從A1單元格開始的行號。

五、資料的日期規範

對日期進行規範我們需要先設定單元格格式。然後在進行資料有效性的處理。

以上就是日常在處理報表對資料有效性的規範，提高工作效率。並且提高資料的有效性。從而達到報表的準確性。

更：忘了放gif 看到提示上首頁了才看見

介紹一個數據有效性的例項：如何按單元格對應的填充色來求和。

案例：

下表是學生成績表，每個顏色代表了不同的分數區間，現在需要對每種顏色的分數求和，如何實現？

解決方案：

4. 此時可以看到所有黃色的單元格都已經找到

5. 全選找到的黃色單元格 --> 關閉查詢對話方塊

7. 在新彈出的對話方塊，在名稱欄中輸入“黃色”-->“確定”

8. 現在回到工作表，在需要對黃色單元格求和的區域輸入以下公式即可：=sum(黃色)

9. 依照上述步驟依次對其他顏色求和