软件下载

EXCEL公式有问题怎么排查(EXCEL公式出错怎么找出错误原因)

软件下载 投稿 2022-04-20 09:34:02 浏览

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

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

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

案例:

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

效果如下图 2 所示。

Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路 解决方案:

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

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

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

Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

遗憾的是结果出错了。

Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

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

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

Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

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

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

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

公式的最外层是 sumproduct 函数:

sumproduct 的作用是将两个数组的元素相乘并求和; 如果遇到错误值,无论是乘法还是接下来的加法都无法得出结果,至此大家就能理解为何公式最终结果出错了。 Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

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

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

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

在 search 函数外面包了一个 iferror 公式,旨在将查找不到的结果转换成 0 值,从而让最外层的 sumproduct 函数可以加总所有销售部的总分。 Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

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

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

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

Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

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

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

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

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

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

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

与之前的公式区别就是:在每段 search 公式外面分别套一个 iferror 函数,将查找结果转换成 0、1 组成的数组; 然后再将两个数组相加求和,得出“或”关系的正确查找结果; 最后用 sumproduct 相乘求和就得到了正确结果 Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

Excel 公式出错很常见,怎么找出错误原因并 debug 得好好学本文思路

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

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

「真诚赞赏,手留余香」

求资源网

真诚赞赏,手留余香

使用微信扫描二维码完成支付

版权声明

求资源网所发布的一切破解补丁,软件,以及其他分析文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途。
否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。如果您喜欢该程序,请支持正版软件,购买注册,得到更好的正版服务。如有侵权请邮件与我们联系处理。

继续浏览有关excel的文章
发表评论
留言与评论(共有 0 条评论)
   
验证码: