編按：

在Excel的日常操作中，我們最容易碰到的便是求和公式，但是在給數(shù)字求和時(shí)，總會(huì)遇到各種各樣的問題，讓人丈二和尚摸不著頭腦。今天就讓我們來系統(tǒng)性地給大家講解一下，單純求和中，最容易出現(xiàn)的一些小失誤，希望大家可以舉一反三。

今天和大家來聊聊Excel的幾個(gè)關(guān)于求和的問題。可能有的同學(xué)就納悶了，求和這么簡(jiǎn)單還用聊嗎？不就是SUM函數(shù)。

如果真的這么簡(jiǎn)單肯定就沒啥聊的了，不信就看看下面這些問題你會(huì)幾個(gè)？

Ps：今天討論的都是單純的求和問題，并沒有那種合并單元格求和、篩選求和、特殊的條件求和等等復(fù)雜的問題。

問題一：數(shù)字求和結(jié)果為0

假如有一列數(shù)字，使用SUM函數(shù)求和時(shí)卻發(fā)現(xiàn)結(jié)果為0，通常有兩種可能。

1.數(shù)字是文本格式

例如下圖中的這種情況：

對(duì)于這種情況有三種方法。

方法1：數(shù)字的單元格左上角有個(gè)綠色小三角，可以通過小三角將數(shù)據(jù)轉(zhuǎn)為數(shù)值類型，求和結(jié)果就正確了。

操作要點(diǎn)：一定要從第一個(gè)有綠色三角的單元格開始選擇，是最簡(jiǎn)單的一種方法。

方法2：選中這一列，用分列功能進(jìn)行處理。

操作要點(diǎn)：分列的時(shí)候直接點(diǎn)完成即可。

方法3：直接用公式=SUMPRODUCT(B2:B12*1)求和

公式中的（B2:B12*1）這部分是通過乘法運(yùn)算將文本型的數(shù)字轉(zhuǎn)換為數(shù)值，再利用SUMPRODUCT函數(shù)可以對(duì)數(shù)組求和的特性來解決問題的。

2.有不可見字符

這種問題常見于系統(tǒng)導(dǎo)出的數(shù)據(jù)，數(shù)據(jù)不是文本格式，但是求和結(jié)果也是零，例如下圖演示的情況。

這種只是不可見字符中的一種類型，還有些不可見字符更加隱蔽，在編輯欄也看不出問題，遇到這種情況可以先使用LEN函數(shù)做個(gè)檢測(cè)。

LEN函數(shù)的功能是得到單元格里的字符數(shù)，檢查結(jié)果表示單元格里數(shù)據(jù)的長(zhǎng)度，明顯可以看出比實(shí)際數(shù)據(jù)多了一個(gè)字符，說明有一個(gè)不可見字符。

遇到這類情況，可以直接用公式=SUMPRODUCT(CLEAN(F2:F12)*1)求和，CLEAN(F2:F12)的作用是清除單元格中的不可見字符，再利用*1將清除后的數(shù)據(jù)變成一個(gè)數(shù)組，由SUMPRODUCT函數(shù)完成求和。

問題二：對(duì)帶單位的數(shù)字求和

有些同學(xué)總是習(xí)慣在數(shù)據(jù)后面加上單位，當(dāng)然也可能領(lǐng)導(dǎo)要求這樣做的，造成的后果就是無法求和。

對(duì)于這類問題，正確的解決方法是先把單位去掉，然后用自定義格式加上需要的單位，這樣就不會(huì)影響求和了，操作方法看動(dòng)畫演示。

當(dāng)然也可以直接用公式=SUMPRODUCT(SUBSTITUTE(F2:F12,"元","")*1)進(jìn)行求和。

這個(gè)公式首先是用SUBSTITUTE(F2:F12,"元","")將"元"字替換為空得到一組數(shù)字，然后再利用*1變成數(shù)組后由SUMPRODUCT完成求和。

上面的兩個(gè)方法對(duì)于大多數(shù)情況來說都可以搞定的，如果你遇到的情況更加復(fù)雜，可以參考之前的教程：

https://mp.weixin.qq.com/s/tU_2rBy9XQA3OvXfu0u8RQ

問題三：數(shù)據(jù)中有錯(cuò)誤值

數(shù)據(jù)中有錯(cuò)誤值的情況簡(jiǎn)直是太常見了，有匹配不到結(jié)果返回錯(cuò)誤值的，也有分母為零造成錯(cuò)誤值的，還有用了一些復(fù)雜公式產(chǎn)生錯(cuò)誤值的，這不是我們今天討論的要點(diǎn)，我們只討論當(dāng)求和的數(shù)據(jù)中出現(xiàn)錯(cuò)誤值時(shí)該怎么辦？

下面這個(gè)圖里模擬了三種錯(cuò)誤值，直接求和的話結(jié)果也是錯(cuò)誤值。

遇到這樣的情況當(dāng)然是要找出每種錯(cuò)誤出現(xiàn)的原因，從源頭去解決問題。

但是如果想直接在保留錯(cuò)誤值的情況求和的話，也有三個(gè)公式可以使用。

公式1：=SUM(IFERROR(B2:B12,0))

利用IFERROR函數(shù)將區(qū)域中的錯(cuò)誤值變成0，然后再用SUM函數(shù)求和，注意這個(gè)公式是數(shù)組公式，需要同時(shí)按Ctrl、shift和回車鍵完成輸入。

公式2：=SUMIF(B2:B12,"<9e307")

公式中的9e307，表示9乘以10的307次冪，是一個(gè)非常大的數(shù)值，SUMIF只對(duì)小于9E307的數(shù)值部分進(jìn)行求和，并且自動(dòng)忽略區(qū)域中的錯(cuò)誤值。本例中因?yàn)榍蠛蛥^(qū)域和條件區(qū)域相同，所以可以省略SUMIF函數(shù)的第三個(gè)參數(shù)。

公式3：=AGGREGATE(9,6,B2:B12)

AGGREGATE函數(shù)第一參數(shù)使用9，表示匯總方式為求和，第二參數(shù)使用6，表示忽略錯(cuò)誤值。也就是在忽略錯(cuò)誤值的前提下，對(duì)B2:B12區(qū)域進(jìn)行求和。

問題四：循環(huán)引用導(dǎo)致求和結(jié)果為零

這是一種特殊的人為錯(cuò)誤，從表面上看不出任何問題。

沒有文本格式的數(shù)字，也沒有不可見字符，但是求和結(jié)果是0。

實(shí)際上在編輯公式的時(shí)候，或者打開這個(gè)文件的時(shí)候，Excel會(huì)出現(xiàn)一個(gè)提示：

這就是在告訴你計(jì)算不正確的原因是因?yàn)榇嬖诹搜h(huán)引用。

如果你不知道是哪個(gè)單元格出錯(cuò)的話，可以依次點(diǎn)擊【公式】-【錯(cuò)誤檢查】-【循環(huán)引用】，就能看到有問題的單元格了。

然后再去檢查公式，就能發(fā)現(xiàn)B13單元格里的公式是=SUM(B2:B13)，求和的范圍包含了B13，只要將B13改成B12就OK了。

問題五：時(shí)間的求和問題

最后一類問題是涉及到時(shí)間的求和，例如在對(duì)一個(gè)人的加班時(shí)長(zhǎng)求和時(shí)，結(jié)果可能不是你所希望看到的。

最后合計(jì)的加班時(shí)長(zhǎng)竟然只有3:40，結(jié)果肯定有問題。

出現(xiàn)這種問題的原因是因?yàn)樵?span>Excel中時(shí)間累計(jì)到24小時(shí)以上就會(huì)自動(dòng)向日期進(jìn)位，也就是24小時(shí)變成1天，只有不足24小時(shí)的部分才會(huì)以時(shí)間的形式顯示。

解決方法也有兩個(gè)。

公式1：=TEXT(SUM(B2:B12),"d天h小時(shí)m分")可以將合計(jì)時(shí)長(zhǎng)以x天x小時(shí)x分的形式顯示。

公式2：=TEXT(SUM(B2:B12),"[h]小時(shí)m分")可以將合計(jì)時(shí)長(zhǎng)以x小時(shí)x分的形式顯示，[h]兩邊加上方括號(hào)就表示小時(shí)這部分不用向上進(jìn)位。

今天分享的內(nèi)容就是這么多，回到開頭的那個(gè)話題，這些求和的問題你會(huì)幾個(gè)呢？

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

將excel中的數(shù)據(jù)按照指定數(shù)量進(jìn)行重復(fù)，非得用VBA才能實(shí)現(xiàn)嗎？

如何在特定位置批量插入空行等12種實(shí)用辦公技巧

4種刪除excel重復(fù)值的小妙招，速收藏

Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能！

版權(quán)申明：

本文作者老菜鳥；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。