Excel 3個簡單技巧輕松避免格式不規范數據出錯（excel函數公式大全）

很多經常需要做Excel統計的童鞋都知道，一份表發給各部門的同事去填寫，收上來的表格通常都是五花八門的。格式不規范不說，就是數據本身也經常會出現錯誤。很多童鞋雖然苦口婆心要求填寫的同事按照要求處理，但效果總是不那么如意。那么有沒有更好的辦法來避免這些問題呢？今天給大家簡單介紹3個簡單的技巧。

重點關注優先級為1的項目

規范數據錄入格式

這是一切問題的根源，如果我們能從源頭上對分發下去的表格做好相應的設置，就會避免掉很多后續處理方面的問題。我們就以下面的表格作為例子，一一說明如何設置才能讓填表人按照我們的要求去填寫。

汽車銷售記錄表

這是一份簡單的汽車銷售記錄表，我們要求姓名列的所有單元格最好不要有重復記錄（因為很少有人一次買兩臺車的）；身份證號碼的長度要么15位，要么18位，長度不對即為錯誤，為了提高錄入準確性，我們可設置身份證號碼列文本長度為15位或者18位……

1.制作下拉列表的小技巧

如果要填入某些列的數據比較固定，只有有限的幾項填寫內容。這樣的列我們可利用Excel數據有效性設置下拉選項，填入時只需選擇即可。這即可提高數據錄入的效率，更可以避免不規范的數據格式（便于統計，格式不統一通常會導致統計結果錯誤）。下面我們就來看看設置下拉列表：

1)選中C2:C10單元格區域，在“數據”選項卡 中單擊“數據驗證”按鈕，打開“數據驗證”對話框；

2)單擊“設置”選項卡，在“允許”下拉列表中選擇“序列”類別；注意“允許”下拉列表右側的“提供下拉箭頭”一定要勾選；

3)鼠標直接選取表中的G2:G7區域，單擊“展開”按鈕，返回“數據驗證”對話框；

4)單擊“確定”按鈕即可。

可使用快捷鍵Alt+A+V+V

忽略空值和提供下拉箭頭處于勾選狀態

2.杜絕單元格中空格的技巧

很多同事輸入兩個字的姓名時，喜歡在姓與名之間加上空格，已達到同其他三個字的姓名對齊（美觀）的目的，但這樣的操作卻會給我們的統計帶來很大的麻煩。我們可設置“單元格中禁止空格輸入”達到規范輸入的目的。按以下步驟操作吧：

1)選中A2:A10單元格區域，在“數據”選項卡 中單擊“數據驗證”按鈕，打開“數據驗證”對話框；

2)單擊“設置”選項卡，在“允許”下拉菜單中選擇“自定義”，“公式”框中輸入公式：=ISERROR(FIND(" ",$A2))

3)單擊“出錯警告”選項卡，左側“樣式”下拉菜單選擇“停止”，右側框中按圖所示輸入內容，標題輸入：“姓名之間請勿插入空格”，內容輸入：“請檢查是否在性名之間插入了空格！”自己想怎么輸入就怎么輸入咯。

4.單擊“確定”即可。

打開數據驗證的必要步驟

自定義功能非常強大，接下來你會看到更多的例子

出錯警告！

輸入內容中如果有空格將無法輸入

出錯警告中的樣式有3種，“停止”、“警告”以及“信息”。選擇“停止”，你將無法再單元格中輸入不符合規范的內容；“警告”會提示你是否繼續輸入；“信息”只是提示你是否取消輸入。

3.規范錄入“車牌號碼”的技巧

貴陽的車牌號碼為7位，以“貴A”開頭，我們在錄入的時候，可以簡單設置，避免錄入錯誤或者無效的數據；跟上文相同的操作步驟我不再贅述，直接進入設置--自定義--公式，公式中錄入：=AND(LEFT(D2,2)="貴A",LEN(D2)=7)。

公式千萬別寫錯了

根據自己的需要可選擇其他樣式

規避或者圈釋無效數據的技巧

無效數據沒用，而且還會影響我們的統計結果。如何在制表之初就將這個問題解決掉，避免后續諸多的麻煩？且看一下技巧：

1.限定輸入本月日期的技巧

我們統計提成或者銷售額也好一般都是按月統計的，那么我們每月收上來的表格所有的內容就應該是當月的內容。下面的技巧可以避免員工錄入其他月份的日期或者其他不在范圍內的日期。

1)單擊“數據”--“數據驗證”--“數據驗證”，打開“數據驗證”對話框；

2)單擊設置--允許下拉列表選擇“日期”--輸入開始日期：=DATE(YEAR(TODAY()),MONTH(TODAY()),1)，也可以直接輸入本月的1號--輸入結束日期：=DATE(YEAR(TODAY()),MONTH(TODAY())+1,0)，也可以直接輸入本月的最后一天；--出錯警告--停止--輸入內容--確定即可。

3)此栗子中可直接輸入本月的開始日期：2016-05-01，結束日期：2016-05-30。但是這樣輸入非常簡單，但每個月都得更改一遍，遠遠不如用公式來得方便。請仔細琢磨本例中的公式。

注意公式的設置技巧

順便你輸咯！

2.提高身份證錄入準確率的小技巧

盡可能減少身份證號碼錄入的錯誤率，下面是一個小技巧。本例中身份證號碼的驗證規則包括身份證號碼長度必須為15位或者18位。

1)打開“數據驗證”對話框“設置”窗口，“允許”下拉菜單選擇“自定義”;

2)公式框中輸入：=or(len($B2)=15,len($B2)=18)

3)填寫“出錯警告”內容后點擊確定，如圖所示：

多學Excel函數吧，很實用！

不再贅述

3.Excel錄入避免重復值的技巧

Excel中如何避免同列錄入重復值？請參考以下設置技巧，請按圖中步驟操作即可，設置的公式為：=countif(A:A,$A2)=1。

出錯警告再重復就沒有意思啦

4.圈釋無效數據的技巧

數據驗證只是針對數據錄入的，但一個表格已經填寫完整，我們只是設置“數據驗證”是無法找出“無效數據”的。如果別人給到我們一個表格，如何快速地標示出其中的不符合規范或者無效的數據呢？這里就要用到“詮釋無效數據”的技巧。

1)首先，請根據需要設置“數據驗證”；

2)使用數據詮釋：數據--數據驗證--詮釋無效數據。

3)紅色標示出的即是“無效數據”。如圖：

設置數據有效性

圈釋無效數據

最終效果

數據驗證的刪除技巧

設置了數據有效性，但是有些驗證不需要了如何刪除呢？首先呢，我們要找出表格中或者選區中設置了數據有效性的單元格，然后再執行刪除的操作。

1)利用定位功能查到設置了數據有效性的單元格區域：選擇中A2:E10,按下Ctrl+G或者F5打開“定位”對話框；

2)單擊定位條件，進入“定位條件”，選擇“數據驗證”。單元格中的數據有效性即被選中；

3)按Alt+A+V+V,進入"數據驗證“--”設置“，點擊最下方的”

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。