如何在sum時視一些前面幾列帶過來的#N/A為0?
RT
如同程序的異常,Excel中也要特別注意防範,否則追蹤起來特別麻煩,常見異常或者錯誤包括:
- #N/A:使用查找功能的函數(HLOOKUP、VLOOKUP、LOOKUP),找不到匹配的值
- #Value!:一般是公式中的錯誤參數所致,比如:運算的時候使用了非數值的單元格,公式不符合函數語法或者使用大括弧的時候不要忘了按快捷鍵ctrl+shift+enter
- #REF!:表示無效的單元格引用。比如:刪除了公式引用的單元格。比如:「A2=A1-1」,若A1被刪除了,則顯示此錯誤
- #DIV/0!:除數為「0」
- #NUM!:公式或函數中使用無效數字值。特別注意哪些從txt或者cvs拷貝過來的數字,看起來是數字,其實不是,比如數字後面多了一個空格,「12 」這樣的
- #Name?:使用的函數名稱不正確或者區域名稱不正確,前者好理解,後者情況如下:=average(data),其中data可能是一個無效引用
- #NULL!:公式或函數中的區域運算符或單元格引用不正確,比如:「=SUM(A1:A5 B1:B5)」應寫成「=SUM(A1:A5,B1:B5)」,否則就會出現#NULL!錯誤提示
因此在Excel中也可以照搬程序中常見的Throw Exception機制,只不過Excel裡面是利用Iserror函數。常見的模式是:前者是將其設置為0而後者是將其設置為空。
if(iserror(單元格地址),0,單元格地址)
if(iserror(單元格地址),「」,單元格地址)
不過,我一直覺得這種寫法特別冗長,但是苦於沒找到更優美的做法。
...更多文章請到數據冰山 - 知乎專欄...更多回答請看何明科的主頁
{Sum(if(iserror(x:x),0,x:x))}
不請自來
提問寫得不是很明白,如果我沒理解錯的話,你應該是要忽略錯誤值求和。
忽略錯誤值求和的公式:
=SUMIF(B2:B6,"&<9E307")
=AGGREGATE(9,6,B2:B6)
這個問題很普遍,特意為這個問題寫了篇專欄文章,詳見
求和區域有錯誤值,沒法求和,怎麼辦? - Excel偷懶的技術 - 知乎專欄
作為一隻excel小能手,沒理解錯的話用Aggregate,AGGREGATE function
如圖
用{sum(iferror(A:A,0))}也可以,但是這個方法對於Min,Max不夠好用吧。
大家都說了wrap error的方法來解決這個問題,但問題是wrap error的公式寫起來太慢,好比幾個區域原本的公式不一樣,就得分開寫wrap error;寫一個VBA可以解決對一個區域內所有的公式wrap error,如果工作量夠大還是很划算的:
Excel 2007
Wrap Selected Formulas
Sub Add_IFERROR_Selection()
Dim myCell As Range
For Each myCell In Selection.Cells
If myCell.HasFormula And Not myCell.HasArray Then
myCell.Formula = "=IFERROR(" Right(myCell.Formula, Len(myCell.Formula) - 1) ",0)"
End If
Next
End Sub
http://www.excelitems.com/2011/03/wrap-iferror-iserror-formulas-add.html
或者直接用別人寫好的插件,可以一鍵對選定區域wrap error,做comps的利器:Free Financial Modeling and Excel Resources
Download Macabacus Add-ins for Excel, PowerPoint, and Word
以及CIQ的插件
第一個免費,後兩個付費,第二個有免費的閹割版,能不能實現wrap error的功能記不清了,付費版肯定可以
iferror(sum(),0)配合使用
把input的前面加個iferror(x,0)
看到有答主寫了if函數裡面嵌套一個iserror函數,個人覺得不太好看。好像是10版還是13版開始Excel有了iferror函數
我個人一般是在可能出現錯誤結果的時候就替換,就像 @何明科 說的,#N/A是在vlookup等函數匹配不到的時候返回的,那就在用vlookup函數的時候就用iferror函數替換
=iferror(vlookup(),要替換為什麼內容)
然後再用sum函數
刪除
推薦閱讀:
※excel 方程的解釋?
※excel 鍵盤操作技巧?
※請教,在EXCEl中,如何利用VBA篩選求和?
※EXCEL中如何快速合併單元欄並求和?
※在Excel中如何使工作簿內不同工作表單內的相同數值用條件格式篩選出來?
TAG:MicrosoftExcel | Excel公式 | MicrosoftExcel2007 | Excel使用 | Excel技巧 |