为什么excel公式显示错误|excel公式错误怎么解决

为什么excel公式显示错误|excel公式错误怎么解决）

有一个好学勤奋的学员，在学习了很多函数用法以后，尝试着解一道按模糊条件查找并求和的案例，公式倒是写得挺像模像样，但是结果总是出错，折腾了一天也不得其法，于是求助。

今天就借着这个案例，给大家讲解一下一些比较容易出错的思路，尤其重要的是要教会大家，当公式不正确的时候，如何把复杂嵌套公式逐一分解至最小单位查找错误根源，从而 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 高手。