欢迎来到电脑知识学习网,专业的电脑知识大全学习平台!

手机版

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

软件应用 发布时间:2022-07-13 16:20:45

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

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

这个过程非常重要,学会了自己查找原因,才算是会灵活变通地运用公式。

案例:

下图 1 的左侧是公司各部门同事的年终评分,请按 E、F 列的要求计算相关的总分。

效果如下图 2 所示。

为什么excel公式显示错误(excel公式错误怎么解决)(1)

为什么excel公式显示错误(excel公式错误怎么解决)(2)

解决方案:

学员想到的是下面这个公式,说实话,乍一看,好像没什么毛病,而且能用到这些函数的人,已经是下功夫认真学习过了。

1. 学员在 E2 单元格中输入了以下公式:

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

为什么excel公式显示错误(excel公式错误怎么解决)(3)

遗憾的是结果出错了。

为什么excel公式显示错误(excel公式错误怎么解决)(4)

为了搞清楚到底为什么出错,我们来看一下分解步骤结果。

2. 在公式栏中选中 SEARCH("销售*",A2:A13) --> 按 F9

为什么excel公式显示错误(excel公式错误怎么解决)(5)

于是就显示出了这段公式的结算结果:

  • search 函数的作用是在第二个文本字符串中查找第一个文本字符串,并返回第一个文本字符串的起始位置的编号;找不到的则返回错误值;
  • "销售*":表示所有以“销售”开头的字符串;
  • 因此这段公式的结果是由错误值和“1”组成的一个数组

有关 search 函数的用法,可参阅 Excel 如何按设定条件提取单元格内的字符串?

公式的最外层是 sumproduct 函数:

  • sumproduct 的作用是将两个数组的元素相乘并求和;
  • 如果遇到错误值,无论是乘法还是接下来的加法都无法得出结果,至此大家就能理解为何公式最终结果出错了。

为什么excel公式显示错误(excel公式错误怎么解决)(6)

通过公式分解理解了原理,那接下来就不难想到修复这个公式的办法了:如果能把 search 的结果中的所有错误值替换成 0,那么 sumproduct 就能正常计算了。

3. 将 E2 单元格的公式修改如下:

=SUMPRODUCT(IFERROR(SEARCH("销售*",A2:A13),0),C2:C13)

  • 在 search 函数外面包了一个 iferror 公式,旨在将查找不到的结果转换成 0 值,从而让最外层的 sumproduct 函数可以加总所有销售部的总分。

为什么excel公式显示错误(excel公式错误怎么解决)(7)

为什么excel公式显示错误(excel公式错误怎么解决)(8)

接下来要查找两个部门并求和,我们的学员非常会举一反三,在上述公式结构不变的情况下,将 search 公式替换成了两个 search 公式相加 SEARCH("研发*",A2:A13)+SEARCH("测试*",A2:A13),“+”表示“或”关系;逻辑貌似正确,但结果却为 0。

4. 学员在 F2 输入的公式如下:

=SUMPRODUCT(IFERROR(SEARCH("研发*",A2:A13)+SEARCH("测试*",A2:A13),0),C2:C13)

为什么excel公式显示错误(excel公式错误怎么解决)(9)

为什么excel公式显示错误(excel公式错误怎么解决)(10)

照例,我们还是分步骤查看结果来找原因。

5. 在公式栏中选中其中的一段 search 公式 --> 按 F9

为什么excel公式显示错误(excel公式错误怎么解决)(11)

  • 公式结果仍是一组以错误值和“1”组成的数组,这段前面已经解释过,不复述;
  • 但是接下来的运算优先级是跟另一个 search 公式的结果相加,即错误值和数值两两相加,最终得出一个全部由错误值组成的数组;
  • 然后,外层的 iferror 公式将数组中的所有元素都变成 0;
  • 最后的乘积求和结果自然就是 0 了。

为什么excel公式显示错误(excel公式错误怎么解决)(12)

针对上述出错原因,其实也很好解决,只要把相加的计算优先级放到 iferror 之后就行了。

6. 将 F2 单元格的公式修改如下:

=SUMPRODUCT(IFERROR(SEARCH("研发*",A2:A13),0)+IFERROR(SEARCH("测试*",A2:A13),0),C2:C13)

  • 与之前的公式区别就是:在每段 search 公式外面分别套一个 iferror 函数,将查找结果转换成 0、1 组成的数组;
  • 然后再将两个数组相加求和,得出“或”关系的正确查找结果;
  • 最后用 sumproduct 相乘求和就得到了正确结果

为什么excel公式显示错误(excel公式错误怎么解决)(13)

为什么excel公式显示错误(excel公式错误怎么解决)(14)

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。

现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。

责任编辑:电脑知识学习网

软件应用