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

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

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

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

效果如下圖 2 所示。

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

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

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

遺憾的是結果出錯了。

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

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

于是就顯示出了這段公式的結算結果：

• search 函數的作用是在第二個文本字符串中查找第一個文本字符串，并返回第一個文本字符串的起始位置的編号；找不到的則返回錯誤值；
• "銷售*"：表示所有以“銷售”開頭的字符串；
• 因此這段公式的結果是由錯誤值和“1”組成的一個數組

有關 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 高手。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!