Excel 公式出錯很常見,怎麼找出錯誤原因並debug 得好好學本文思路


有一個好學勤奮的學員,在學習了很多函數用法以後,嘗試著解一道按模糊條件查找並求和的案例,公式倒是寫得挺像模像樣,但是結果總是出錯,折騰了一天也不得其法,於是求助。

今天就借著這個案例,給大家講解一下一些比較容易出錯的思路,尤其重要的是要教會大家,當公式不正確的時候,如何把複雜嵌套公式逐一分解至最小單位查找錯誤根源,從而debug。

這個過程非常重要,學會了自己查找原因,才算是會靈活變通地運用公式。

案例:

下圖1 的左側是公司各部門同事的年終評分,請按E、F 列的要求計算相關的總分。

效果如下圖2 所示。

解決方案:

學員想到的是下面這個公式,說實話,乍一看,好像沒什麼毛病,而且能用到這些函數的人,已經是下功夫認真學習過了。

1. 學員在E2 單元格中輸入了以下公式:

=SUMPRODUCT(SEARCH(“銷售*”,A2:A13),C2:C13)

遺憾的是結果出錯了。

為了搞清楚到底為什麼出錯,我們來看一下分解步驟結果。

2. 在公式欄中選中SEARCH(“銷售*”,A2:A13) –> 按F9

於是就顯示出了這段公式的結算結果:

  • search 函數的作用是在第二個文本字符串中查找第一個文本字符串,並返回第一個文本字符串的起始位置的編號;找不到的則返回錯誤值;
  • “銷售*”:表示所有以「銷售」開頭的字符串;
  • 因此這段公式的結果是由錯誤值和「1」組成的一個數組
推薦文章  Google再揮軍元宇宙市場! AR頭戴裝置專案,計劃2024 年問世

有關search 函數的用法,可參閱Excel 如何按設定條件提取單元格內的字符串?

公式的最外層是sumproduct 函數:

  • sumproduct 的作用是將兩個數組的元素相乘並求和;
  • 如果遇到錯誤值,無論是乘法還是接下來的加法都無法得出結果,至此大家就能理解為何公式最終結果出錯了。

通過公式分解理解了原理,那接下來就不難想到修復這個公式的辦法了:如果能把search 的結果中的所有錯誤值替換成0,那麼sumproduct 就能正常計算了。

3. 將E2 單元格的公式修改如下:

=SUMPRODUCT(IFERROR(SEARCH(“銷售*”,A2:A13),0),C2:C13)

  • 在search 函數外麵包了一個iferror 公式,旨在將查找不到的結果轉換成0 值,從而讓最外層的sumproduct 函數可以加總所有銷售部的總分。

接下來要查找兩個部門並求和,我們的學員非常會舉一反三,在上述公式結構不變的情況下,將search 公式替換成了兩個search 公式相加SEARCH(“研發*”,A2:A13)+SEARCH(“測試*”,A2:A13),「+」表示「或」關係;邏輯貌似正確,但結果卻為0。

4. 學員在F2 輸入的公式如下:

推薦文章  國產手機支持國產手機產業鏈的發展? 蘋果揭開了它們的遮羞布

=SUMPRODUCT(IFERROR(SEARCH(“研發*”,A2:A13)+SEARCH(“測試*”,A2:A13),0),C2:C13)

照例,我們還是分步驟查看結果來找原因。

5. 在公式欄中選中其中的一段search 公式–> 按F9

  • 公式結果仍是一組以錯誤值和「1」組成的數組,這段前面已經解釋過,不複述;
  • 但是接下來的運算優先級是跟另一個search 公式的結果相加,即錯誤值和數值兩兩相加,最終得出一個全部由錯誤值組成的數組;
  • 然後,外層的iferror 公式將數組中的所有元素都變成0;
  • 最後的乘積求和結果自然就是0 了。

針對上述出錯原因,其實也很好解決,只要把相加的計算優先級放到iferror 之後就行了。

6. 將F2 單元格的公式修改如下:

=SUMPRODUCT(IFERROR(SEARCH(“研發*”,A2:A13),0)+IFERROR(SEARCH(“測試*”,A2:A13),0),C2:C13)

  • 與之前的公式區別就是:在每段search 公式外面分別套一個iferror 函數,將查找結果轉換成0、1 組成的數組;
  • 然後再將兩個數組相加求和,得出「或」關係的正確查找結果;
  • 最後用sumproduct 相乘求和就得到了正確結果

很多同學會覺得Excel 單個案例講解有些碎片化,初學者未必能完全理解和掌握。不少同學都希望有一套完整的圖文教學,從最基礎的概念開始,一步步由簡入繁、從入門到精通,系統化地講解Excel 的各個知識點。

現在終於有了,以下專欄,從最基礎的操作和概念講起,用生動、有趣的案例帶大家逐一掌握Excel 的操作技巧、快捷鍵大全、函數公式、數據透視表、圖表、列印技巧等……學完全本,你也能成為Excel 高手。

Scroll to Top