公式出錯,大概是最令表哥表姐們頭疼的一件事了。當然也分兩種:一是公式本身就有錯誤,這時主要依賴于排錯解決。另一種情況,則是公式本身沒錯,但由于原始數據等方面的緣故,不可避免產生了一些與期望值不符的結果。那么要想解決后一種情況,一般都有哪些方法呢?
1、IFERROR 函數
從函數的名稱也能看出,這家伙其實就是專為公式出錯而設計的。簡單來說,它的使用只需要兩組變量,“判斷哪個單元格出錯”以及“出錯后顯示什么?”。舉個例子,當我們要制作一份考勤表時,通常會使用“實際出勤數 / 應該出勤數”來求取“出勤率”,但這里往往會涉及一個問題,即當“應該出勤數”還未填寫時,單元格就會彈出一個“除零錯誤”(#DIV/0!)。
▲用 IFERROR 函數解決原始數據出錯
解決這個問題的方法很多,比如先通過 IF 函數做個判斷,如果“應該出勤數”已經填寫,就按規則計算,如果尚未填寫,就先顯示個“0”占個坑,
不過你也看到了,這個方法很笨,而且會把公式變得冗長,相比之下,IFERROR 只要告訴它出錯后,應該顯示什么就可以了(本例即出錯時顯示“空白”,不出錯按原公式計算),既簡單又輕便,
▲自定義出錯提示詞
此外你也可以通過修改 IFERROR 后面的“出錯值”,來實現一些更“人性化”的提示。比如當數據出錯時,顯示“還沒數據呢”(記得提示文字一定要用引號括起來)等等。總之方法到位了,怎么喜歡就怎么來吧!
2、“0”值處理
除了單獨的出錯提示外,有時我們也會看到一大堆“0”值。這個原因其實和上文差不多,也是由于原始數據未填寫(更新)所致。不過由于不是出錯提示,IFERROR 在這里已經不起作用,我們需要更換一個思路,
▲取消“在具有零值的單元格中顯示零”勾選
點擊“文件”→“選項”→“高級”,將右面板的進度條下拉,取消“在具有零值的單元格中顯示零”前面的勾選,就能在 Excel 中禁止“0”的顯示,
不過和 IFERROR 僅處理出錯公式不同,“禁零法”則會連帶著將正常的“0”值禁用。因此在使用這個方法時,需要更為謹慎一點。
3、數據驗證
對于某些容易輸錯的字段(比如身份證號),我們通常會使用“數據有效性”加以控制,一般來說,如果所輸的內容不合規,就會彈出一個“此值與此單元格定義的數據驗證限制不匹配”的提示。沒錯!這個提示很微軟,
▲修改出錯提示文字
能不能讓提示文字更易懂一點呢?很簡單,只要在設置數據有效性時,點擊旁邊的“出錯警告”,然后再把你琢磨好的提示文字輸入進去即可。這樣當輸入內容不符合規范時,我們至少能看到一個能看懂的提示了,
▲修改后的出錯提示
4、重復輸入提醒
除了上面這些后知后覺的提醒以外,我們也可以對一些重復錄入進行限制。實現這一功能同樣也要借助“數據有效性”,只不過重點是將“驗證條件”改為“自定義”,并在公式欄中輸入“=COUNTIF (B:B,B1)=1”,
這段公式的作用,是當 B 列中出現重復數據時(即 COUNTIF 值 > 1),停止錄入并彈出提示,當然你也可以借助上文那個方法,對這個提示自定義一下,
▲通過“數據有效性”防止重復錄入
寫在最后
以上四種方法是平時最常用的,基本上可以涵蓋掉日常使用的方方面面。其實出錯本身并不可怕,真正可怕的是明明出了錯,卻沒有任何提醒。好了,以上就是本期要和大家分享的幾個小技巧,喜歡的話就點個贊吧!