資料驗證的允許選項

看到這四個選項，哪麼問題來了，我們知道日期和時間本身就是整數和小數的數值，為什麼還要增加的日期和時間，是不是可以推測出驗證選項不光區分資料，還區分單元格的格式呢？我們會在之後的操作中驗證這個問題！

回看需求，必須為數值就只有整數和小數兩個選項了，整數通常用於物品的數量，以個，匹，科，棵，頭，只等為單位你就選”整數”，而小數的應用就廣泛多了：比如以重量，金額，%，功率，溫度，長度等單位型別的物品或屬性上，一般就要選”小數”了，這裡要說明2個問題，

選擇”整數”我們照樣可以輸入1.00這樣數值，這就說明驗證的檢測的單元格最後計算的結果；

選擇任意一項，在【資料(D)】選擇任意邏輯關係後，且”小數”的範圍值可為整數；

       

整數選項對應的邏輯關係有哪些

哪如果都選”小數”會有什麼不好影響嗎？

在日常的使用中，我們不難發現，Excel在資料型別上採用了弱型別的設計，也就是在Excel的世界裡，根本就沒有整數這個理念，所有的數值都是採用了浮點型(始終帶有小數部分)儲存，為了看起來整數，是經過了取整操作，而如果驗證方式選”小數”後，就有可能會忽略部分的取整操作，會導致運算中有誤差，雖然很小很小，但一旦帶入多層級運算，就會導致類似天氣預報中下雨和晴天這樣兩種完全不同的結果並且這個結果並可以預測。（詳解Excel基礎知識-微軟都無法修復MOD中的BUG，我們該怎麼做）。

自帶的選項確實能幫我們省去設計的時間，單它的功能也相對單一併不能滿足我們日常的需求，接下來就需要我們自己設計驗證規則了，【允許(A)】選【自定義】選項，接下來我們就來盤盤它。

需求：支援輸入為數值，不管是不是數值格式 （技巧等級：中級）

當我們選擇【自定義】介於選項為灰色，輸入項變為【公式】，在錄入框支援常量和公式的錄入，而驗證判斷的標準是你錄入內容最終返回結果是真還是假，結果為真的常量有數值≠0，邏輯值的TURE,其它的的文字，0，false結果都為假;這個知識點會在之後用到，公式的錄入知識有兩點：

1. 公式通用引導符號“＝”函式附加的引導符號“@”；
2. 公式也分常量，函式，表示式，當前的需求需要用到表示式作為函式引數使用，使用數學運算子負號（-）、加減0和乘除1來講文字數值轉化成數值，再用數值判斷函式來判斷；

具體操作如下：

選擇要使用驗證單元格的範圍或整列，點選【資料】選項卡下的資料驗證按鈕，彈出的【資料驗證】彈窗，【允許(A)】選擇“自定義”選項，然後在“公式(F)”輸入=isnumber(--c1)、=isnumber(c1*1)、=isnumber(c1/1)、=isnumber(c1 0)或@isnumber(c1-0)；

說明：至於在輸入框中的表示式或函式輸入哪個單元格需要看選擇範圍後，當前啟用的單元格為哪個（白色單元格）！

       

配置資料驗證公式的步驟

如果你的錄入的單元格設定了這樣的驗證，必須在編寫函式運算公式的時候引用這些單元格計算的時候，必須先做文字數值轉數值的操作，否則會導致運算的最後的結果不正確，因為函式求和或其他運算時，文字數值會被看做0 帶入計算的！

需求：最大限度的保證手機號的正確性 （技巧等級：高階）

要做好這個功能，必須先思考一個問題，什麼樣的數字才能稱為手機號呢？也可以說手機號有什麼獨有的特徵呢？你總結的特徵越多，這個功能完成就越高，然後將特徵轉為表示式就是驗證輸入的內容啦。

及格特徵：

1.長度為11，2.第1個數字為1，3.第2位數不可能為0，1，2；（注：三個特徵來自手機號段資訊彙總）

如果用資料驗證實現這三個方面的限制，在手機號碼的驗證上就算合格了；三個特徵轉為公式為：特徵1：len(d1)=11,特徵2：left(d1,1)*1=1; 特徵3；mid(d1,2,1)*1 > 2，三者的邏輯是且關係，也就是三個特徵都必須滿足的情況才算是手機號；要實現3條件的且的邏輯，我們通常會想到用if函式來完成，公式=if(len(d1)=11,if(len(d1)*1=1,if(mid(d1,2,1)*1>2,ture,false),false),false),你也可以通過數學的方式來，比較表示式返回TURE和FALSE,再數學表示式中轉為1和0，而數值0可以轉化為邏輯值FALSE,非零的數值則為TURE;用數學表示式編寫公式為：=(len(d1)=11)*( left(d1,1)*1=1)*( mid(d1,2,1)*1 > 2)；這兩種的方式你更喜歡哪種呢？

具體操作步驟：d列為手機號輸入列，選擇整列，然後調出【資料驗證】視窗，選【自定義】並在公式錄入框輸入上面編寫的If或數學表示式公式中的任意一個，點確定。

為什麼這樣做才只能算及格呢？

上述的方法從對應的位數上是否出現的數字作為檢測標椎，確實能達到過濾部分的輸錯的可能，但相當一部分的資料通過這種方法是無法避免輸錯的，而實際的可以確認的號段也就40多個，而且上述方法組合70種組合，每一種會增加上億個錯誤可能性，而且一旦號段有所增加，需要重新彙總資料規律，重理邏輯，再編寫公式，相當繁瑣；接下來我們就一起玩一下滿分的做法！（設計思想：重配置，輕邏輯，易維護，可擴充套件性高）

滿分特徵：1.長度為11，2.前三位號碼段，3.4-7位為地區編號；

實現步驟：新建配置工作表，名稱配置資訊；然後在網上找到號段資訊，填入到配置資訊表第一行表格中，通過統計擷取錄入手機號的前三位出現的次數，為0則輸入錯誤，為1則通過特徵2的判斷，公式為=COUNTIF(配置資訊!$1:$1,LEFT(Sheet1!D1,3))再乘上(len(d1)=11) *(isnumber(d1*1))就是填入【資料驗證】視窗的完整公式了，一旦有新增號段，只需填入新的號碼段就行，再用同樣的方法增加地區編碼驗證就能滿分了！雖然這樣做能滿分，但它的實際體驗並不好，於是我就做了改良，將部分的驗證公式改用條件格式來做，同事反饋體驗良好。

操作步驟：選中d列，點選【開始】選單中的【條件格式】按鈕，在彈出的下拉選單中，選【新建規則】，在【新建規則視窗】中選擇【使用公式確定要設定的單元格】，輸入=COUNTIF(配置資訊!$1:$1,LEFT(Sheet1!D1,3))=0，點選格式按鈕，設定紅色字型並加粗，點確定；

       

條件格式實現操作步驟

然後將資料驗證修改的公式修改成=(len(d1)=11)*(isnumber(d1*1)),手機為11位數字這個必須的，至於號段或手機號錄入錯觸發的概率太高了，這樣只需標註出來，提到提醒的作用就可以了！

注意：【資料驗證】當公式返回結果為false的時候彈出錯誤提示，而【條件格式】則是公式的結果為TRUE時使用自定義的格式，所以兩者的公式不能通用，是不是眼尖你的早就發現！

接著一起盤一下【資料驗證】彈窗的其它三個選項卡：

【輸入資訊】作用：提示說明，當滑鼠單擊有資料驗證的單元格，會彈出提示資訊；實用性：確實在輸入有一定的提示作用而大量的同事抱怨在錄入時，非常礙眼，後去掉，補充做法：表格右側空白地方增加提示資訊區域或直接將輸入錄入說明製作成單獨檔案下發給同事；

       

輸入資訊的操作介面以及對應的顯示位置

【出錯警告】作用：當錄入者輸入錯誤時，會彈出提示錯誤，實用性：由於輸入錯誤不一定是人為，誤操作導致的，不論怎麼輸入一直提示錯誤，後來讓我過去看看，原來是設定了文字格式，這種方法也並是太好用！

       

出錯警告操作介面以及顯示位置

【輸入法模式】作用：讓輸入法自動切換為英文輸入或中文輸入，我的環境：windows8.1系統，office 2016,只有QQ輸入法，設定完成後，不能起到任何作用！

       

輸入法模式的操作介面

解決方法：需要在控制面板-語言-新增語言-英語，因為只有一個輸入法，是不能滿足【輸入法模式】開啟的條件的！，還需要修改被QQ輸入法中英文切換佔用的【ctrl 空格】快捷鍵為其它，經過這些操作之後才能開啟功能！

       

輸入法模式無效的解決方法示意圖

實用性：如果一個功能依賴的條件太多，肯定不發普及和推廣，因為大部分對系統配置操作不是太瞭解，雖然它的功能上有益錄入中英文的資料效率，不過我覺的還是用輸入法自帶shift切換來的更直接有效！你覺得呢？

在文章的最後需要說明一點：任何的強大的功能都有缺點，資料驗證的缺點就是它會阻斷錄入過程，但有些阻斷是必要的，有些阻斷了就會給錄入造成很大麻煩，所以我們在使用【資料驗證】的時候，需要注意有些對於資料準確性要求不是很嚴格的資料，使用【條件格式】這種有識別的格式就好，不是所有的場景都適合使用，比如資料驗證並不能作用於設定之前已有的資料；文末彩蛋：我們可以用來限制他人修改已有的資料，只需在自定義的公式中輸入0或FALSE，不論別人輸入什麼都會彈出錯誤提示！通過這個例子想告訴你，只要我們平時多思考，多用發現的眼光觀察，缺點也是有它的用武之地的，好了今天就先寫到這了，如果對你有所幫助記得關注我啊，我是愛極客的小胖子！