我是拉小登，一個會設(shè)計表格的 Excel 老師~

昨天有個同學(xué)的提問，讓我非常的頭禿。

公式的長度有 6 行！??！

拉登老師的頭發(fā)也是一根一根長出來的，不是充話費送的！我們來一起保護(hù)它，好嗎？

類似的奇葩問題并不少，上周還給一個采購的同學(xué)解答了一個 SUM 函數(shù)的問題，問題很簡單，但是我卻花了 1 個小時才搞定。

為了避免類似低效工作，在你身上重演，請務(wù)必看完這篇文章。

問題描述

年底采購小姐姐做數(shù)據(jù)盤點的時候，遇到了一個很奇怪的事情。

表格 E 列中明明有數(shù)據(jù)，但是 SUM 求和的結(jié)果卻是 0。

這樣的問題我見的太多了，看一眼我就知道問題在哪里。

問題原因：

數(shù)字求和為 0，通常是因為是數(shù)據(jù)被保存成了文本類型，而導(dǎo)致的。

解決方法：

非常簡單，使用分列功能，把文本轉(zhuǎn)成數(shù)字就可以了。

本以為是一個很簡單的問題，我就要過來表格，隨手幫她解答一下，結(jié)果，這是一個無底洞，前前后后花了我 1 個小時時間。

我跟大家回顧一下這個過程。

公式嵌套錯誤排查


我們從頭開始，一步一步還原整個排查的過程。

▋分列功能，文本轉(zhuǎn)數(shù)字

前面說過，SUM 求和為 0 的問題，常規(guī)方法是使用分列功能，把文本轉(zhuǎn)成數(shù)字就可以搞定。

不過，很顯然，這個操作沒有成功，求和結(jié)果還是 0。每個單元格的格式都已經(jīng)是「常規(guī)」。

那么問題肯定是出現(xiàn)在某個單元格里，沒辦法，只能逐個的排查了。

每個單元格，是前兩列數(shù)據(jù)相減得出來的，那么排查過程中，免不了還要檢查前面引用的兩列數(shù)據(jù)，這就麻了煩了。

▋SUM 求和錯誤排查

這樣做顯然是行不通的，數(shù)據(jù)有 45 行，再乘以前面兩列，那就是 45*3=135 個單元格，沒個半小時搞不定。

換個思路！

既然是 SUM 求和出錯的，那么就每個單元格都相加測試一下，看哪個單元格開始算錯了。
這讓我想到了行列鎖定中的「拉燈模式」用法。

在第 1 個數(shù)據(jù)旁邊的空白單元格里輸入 SUM 公式：

公式如下：

• =SUM($E$3:E3)
  

這樣填充公式后，可以實時查看，截止到當(dāng)前單元格的求和結(jié)果，容易排查出錯誤。

果不其然，在第 17 行的時候，我們發(fā)現(xiàn)了錯誤：

SUM 函數(shù)計算錯誤，數(shù)據(jù)沒有累加。再檢查左側(cè)的單元格里的公式：

• = C17-D17

• = 0 - 161740.71

• = 0
  

這個計算顯然是不對的，然后，我再對應(yīng)檢查左側(cè)的兩個單元格。這兩個單元格分別引用了下面兩個單元格：

• =秋葉!J685

• =秋葉!R685

  
  

到這里，第 1 個 SUM 函數(shù)的錯誤就排查完畢了，錯誤的原因指向了「秋葉」這個工作表。

我們繼續(xù)往下排查。

循環(huán)引用排查


來到「秋葉」這個工作表里查看一下，依次排查這兩個單元格。

R685 也是一個 SUM 函數(shù)，計算結(jié)果正常。

J685 這個單元格就不對了，和第 1 步出現(xiàn)了相同的問題。SUM 函數(shù)引用了一大堆的數(shù)據(jù)，計算結(jié)果也是 0。

• =SUM(J101+J393+J426+J431+J478+J497+J535+J544+J576+J623+J656+J663+J684+J569+J587)
  

沒辦法，一個一個排查單元格吧。

▋逐個排查錯誤

一個很大的問題，「秋葉」這個表里的數(shù)據(jù)更多，有 600 多行，一個一個排查的話，要累死人。

怎么準(zhǔn)確地排查 SUM 函數(shù)里對應(yīng)的公式呢？

我想了一個好辦法，把前面的 SUM 函數(shù)，拆分成單獨的 TYPE 函數(shù)，判斷每個單元格的值，是數(shù)字還是文本：

• =TYPE(J101)

• =type(J393)

• =type(J426)

• =type(J431)

• =type(J478)

• =type(J497)

• =type(J535)

• =type(J544)

• =type(J576)

• =type(J623)

• =type(J656)

• =type(J663)

• =type(J684)

• =type(J569)

• =type(J587)
  

拆分的方法，也是費了一番周折，把公式粘貼到 Word 中，使用通配符完成替換，具體如下：

然后把公式，復(fù)制粘貼到 Excel 中，就可以檢查單元格值，是文本還是數(shù)字了。

這樣，我們很快的就找到了錯誤的單元格，錯誤指向 J426 單元格。

▋萬人坑循環(huán)引用

再繼續(xù)順藤摸瓜，找到 J426 單元格，又是相同的問題，SUM 函數(shù)求和，有數(shù)據(jù)但是求和結(jié)果為 0。

這一次的情況，和前面第 1 步是一樣的，是對連續(xù)的數(shù)據(jù)求和，所以排查方法，也是一樣的。

在旁邊增加一個輔助列，使用 SUM 函數(shù)+拉燈模式，排查是哪個單元格出了問題。

然后我們順利的找到了錯誤的單元格 J414，再檢查一下錯誤單元格里的公式。

罪魁禍?zhǔn)捉K于找到了，J414 里的公式又引用了自己，造成了循環(huán)引用，然后產(chǎn)生了連鎖反應(yīng)，所以和 J414 相關(guān)的計算，全部都出錯了。

總結(jié)

今天的內(nèi)容有點燒腦，罪魁禍?zhǔn)拙褪枪降难h(huán)引用。

在實際工作中，應(yīng)當(dāng)避免公式中的循環(huán)引用，在 Excel 中還有一個功能，可以一鍵檢查循環(huán)引用↓↓↓

在【公式】選項卡中，點擊【錯誤檢查】-【循環(huán)引用】，就可以快速找到循環(huán)引用的單元格。

除此之外，我們還學(xué)習(xí)到了下面幾種，排查 SUM 函數(shù)錯誤的方法：

? 分列功能，可以快速完成文本到數(shù)字的轉(zhuǎn)換。

? 公式排查方法，使用拉燈模式，查找替換法，可以對單個單元格進(jìn)行公式計算排查。

? 循環(huán)引用，要盡可能的避免循環(huán)引用的計算，一旦發(fā)現(xiàn)不了，隨著公式嵌套的越來越多，排查類似錯誤的難度會越來越大。

好了，今天的內(nèi)容就是這樣，下課！

上面的技巧，哪個是你不會的呢？

如果你想和我實時交流 Excel 問題，可以參加《3 天 Excel 集訓(xùn)營》，上課方式是直播+錄播，有名師帶學(xué)、助教答疑。

秋葉 Excel 讀者專享

官網(wǎng)價?99?

現(xiàn)在僅需 1 元

你也有可能成為?Excel 高手

點擊下方小程序即可報名

??????

↑↑↑

現(xiàn)在報名免費獲取

307 個函數(shù)清單

35 個常用函數(shù)說明

……

*廣告