求和區域有錯誤值,沒法求和,怎麼辦?

當單元格區域有錯誤值時,無法求和,遇到這種情況怎麼辦呢?是一個個將錯誤值刪除,還是返回去加IFERROR函數消除錯誤,除了這二種選擇,還有第三種更偷懶的方法,欲知詳情,請閱讀本文。

一、最常見的錯誤值及原因

Excel錯誤值有很多種,最常見的錯誤值有二種:

第一種是"找不到對象"錯誤值#N/A

這種一般是查找不到值時會出現,比如使用vlookup查找引用時。

第二種是"除零錯誤值"#DIV/0!

這種是在分母為零時就會出現。

二種錯誤碼見下圖

二、遇到錯誤值,沒法求和怎麼辦?

有三種方法:

粉飾太平法

從根源上入手,在出錯的原公式外嵌套IFERROR函數,假設前面圖片中的B3單元格的原公式為

=VLOOKUP(A3,$I$2:$J$4,2,0)

那麼,我們可以將公式修改為:

=IFERROR(VLOOKUP(A3,$I$2:$J$4,2,0),"")

IFERROR是2007版才增加的函數,還在用老古董2003版的表親可以用下面的公式:

=IF(ISERROR(VLOOKUP(A3,$I$2:$J$4,2,0)),"",VLOOKUP(A3,$I$2:$J$4,2,0))

龍逸凡註:

在編製大型表格時,如全面預算表格,在最初就使用此方法消除錯誤值,可能不利於檢查公式錯誤和缺陷,建議先不要給公式加IFERROR,而是在設計完整套表格並做完測試,發現公式基本無誤後,再最後給公式添加IFERROR函數。

暴力清除法

為了不影響其他運算,我們可以使用生殺予奪的大權,直接將這些錯誤值清除。方法:

按F5功能值-定位(錯誤值),可選中錯誤值的單元格,然後按DEL清除其公式。

直接無視法

前面二種方法都不太好,第一種,並不是將錯誤扼殺,而是粉飾太平,用IFERROR營造了一派歌舞昇平的太平假象,而暴力清除法太過暴力,將公式清除後,當源數據更新後,數據無法隨之更新。

所以,前二種方法都不是十全十美,最好的辦法是既保留錯誤值,又對單元格區域進行求和(待表格所有的公式完成測試後,再添加IFERROR函數)。

具體方法如下,可以在B7求和單元格使用下面的公式:

=SUMIF(B2:B6,"<>

求和條件中的9E307是採用科學計演算法的一個數字,就是9乘10的307次方,接近Excel能處理的最大數字。

或者使用2010新增的AGGREGATE函數,

=AGGREGATE(9,6,B2:B6)

AGGREGATE英文單詞的含義是合計、總數的意思。這個函數和Subtotal函數類似,是一個多面手,能替補SUM、COUT、AVERAGE等19個函數出場,一個頂十九個!夠牛吧?

更牛的是:它還會自動忽略計算區域中嵌套的AGGREGATE函數結果、忽略錯誤值、忽略隱藏行。

在這裡,我們利用的是它能忽略錯誤值的特點。

該函數有三個參數,第一個參數從1到19,分別代表不同的函數

第二個參數是用於忽略什麼值,具體指代含義如下:


推薦閱讀:

長江三角洲地區區域規劃(全文)11
8000年前紅山文化區域的先民最先吃小米
什麼是 EB5? 什麼是 EB5區域中心
如何在區域活動中滲透環境教育
張憶東:港股曙光初現,處於戰略性底部區域!【格隆匯】(16.4.21)

TAG:錯誤 | 怎麼 | 區域 |