Excel公式不起作用的10个原因[以及如何解决它们]

1

“Oh My God” Excel公式在我的报告中不起作用。

听起来很熟悉吧?

如果是这样的话, 不要’t worry,您只是经常遇到此问题的许多excel用户之一。

考虑一下。

已使用各种逻辑和相当复杂的公式为销售预测建立了一个excel模型。

[‘IF’具有64个条件的公式– 开玩笑]

当您尝试使用该模型时,您会看到一些公式不起作用或看起来很破损。

excel公式无法正常工作教程与各种示例

基本上,这里可以发生两件事:

您可能想大声说些什么[我不要使用这个词]。

第二,则需要付出更多的努力并修复所有公式。

因此,为避免所有麻烦,我们撰写了这篇深入的文章,涵盖了excel公式无法正常工作的大多数原因。

以及如何修复它们。


Excel公式不起作用:为什么& How to fix them


根据华盛顿邮报 关于Excel错误的文章, “这位澳大利亚研究人员发现,这些论文中大约有五分之一包含其基因列表中的错误,这是由于Excel自动将基因名称转换为日历日期或随机数之类的结果所致。”

最初的研究是由 期刊基因组生物学 发现在许多错误方面,呈逐年上升趋势。

你可以下载 完整的研究资料 可以从SourceForge网站(开放源代码)中进一步阅读。

excel错误研究论文

上面的研究论文可能没有直接链接到在excel中不起作用的公式,但是这里有一点:

建造得很好&经过测试的软件(例如MS Excel)在理解我们的数据方面可能会出错 你呢& me?

所以,这是我们的十大理由 [让聚会开始]

下载我们的 10点清单 适用于Excel公式不起作用

#1计算选项[Excel公式未更新的最可能原因]


将计算选项更改为‘手册‘是最佳做法,但使用时需要非常小心。

默认情况下,“计算”选项将设置为 自动 在Excel中。

Excel公式不起作用-计算选项

但是在某些情况下,您可能已经将其转换为‘m每年的‘加快流程。

如果您启用此选项‘‘那么您的公式将不会更新 自动地,这反过来又可以提高电子表格的处理速度。

完成后,您需要点击 保存 要么 Refresh workbook 重新计算。如果你没有’这样做会导致结果不正确。

如何解决此问题:

转到公式→计算→单击立即计算/计算表选项。

您也可以使用F9快捷键来刷新工作簿。

这是我们的视频指南 Excel计算选项.

提示:

  • 养成按Ctl + S的习惯[将强制执行计算]
  • 除非每个文件都有大量公式,否则不要在每个工作簿上都使用此选项
  • 此选项特定于工作簿,因此您需要在要使用的每个工作簿中打开/关闭

#2 Excel公式不只显示公式就显示[显示公式]


显示公式是excel中的一个选项,只需单击一次即可显示所有工作簿公式。

该选项对于查找工作表中的所有公式非常有用。

话虽如此,我们在使用时需要小心一点 显示公式选项,否则,您可能会得到如下所示的结果。

您能看到“今天”公式显示为公式而不是显示实际日期吗?

Randbetween公式也是如此。

在Excel中显示公式选项

如何解决此问题:

It’s very simple:

转到公式→在公式审核下→单击“Show Formulas”(随着第一次点击的公式出现,第二次点击它们将隐藏在单元格后面并开始工作)

[请参考上面的截图]

另请阅读: 51个最佳Excel技巧和窍门 


#3额外空间令人头疼


您一直在使用CSV(逗号分隔值)格式提取数据,然后’将数据大部分暴露在不需要的空间中。

您知道使用Vlookup公式时多余的空格会引起很多问题吗?

有时候’很难识别这些空间。

例如,让’s表示如果您要使用员工姓名作为基础进行Vlookup,那么您的员工姓名如下所示

Excel中的空格是Vlookup无法正常工作的原因之一

你注意到了吗?

尽管这两个名称看起来都与我们的肉眼相似,但第二个名称中有一个空格。

这将不符合Vlookup公式中的完全匹配条件,因此导致 #N /A error.

如何解决此问题:

多余的空间问题可以使用解决 修剪公式.

通常,Trim公式会在单词之间找出多个空格并将其删除。

提示:

  • It’总是好主意 修剪公式 在两个表中的Vlookup之前(查找& data table)
  • 修剪功能将忽略字母之间的空格(仅适用于单词)

#4摆脱不可打印/隐藏的字符


与前导空格类似,不可打印字符也是excel公式不起作用的原因之一。

如果您有包含这些字符的数据,’最好先将它们取下再继续工作。

如何解决此问题:

与修剪类似,您可以使用CLEAN公式删除这些字符。

[请参考下面的例子]

使用干净的公式删除不可打印或隐藏的字符-分钟

小费: It’同时使用Trim是一个好主意&一起清洁配方

 #5 Excel格式– 不要’尝试将苹果与橘子进行比较 


It’使用前正确格式化非常重要 像Vlookup这样的公式,Hlookup和Match& 在dex.

格式化为文本的数字是公式在excel中不起作用的常见原因之一。

尽管它们看起来像数字,但是却不是。

不要将数字格式化为文本

例如,在左侧,您可以看到客户ID看起来与数字相似。

但是,如果您参考上面的格式化部分,您可能会看到它们已被格式化为文本。

反过来,这会在添加Vlookup公式时触发#N / A错误。

最糟糕的是’很难识别。

如何解决此问题:

确保所有数字的格式均设置为数字而不是文本。它’在使用Vlookup公式之前,值得重新设置查阅列的格式。

提示:

  • 通常,数字将始终与单元格的右侧对齐(但在我们上面的示例中,数字不是)
  • 有时您会在左上角看到绿色的小提示,这表示该单元格有问题

#6循环引用(由于无穷大的圈而导致的错误)


简而言之,您的公式范围处于无限循环中或’s in a circle.

根据excel计算规则,您的公式不应引用结果单元格作为范围。如果这样做, 循环参考误差 会出现。

就我个人而言’ve多次因此类错误而苦苦挣扎。

例如,在下面的“支出预算”表中,我们需要添加一些插头(调整编号)以与目标匹配。

因此,我们已经在第13行中计算了差异金额,现在我们只需要将它们放在第9行中。

一种简单的方法是在第9行中链接第13行单元格。

如果这样做,您的链接公式将无法工作,您可以看到 也许你也可以看到 蓝色箭头 指向单元格引用。

[请参考下面]

循环参考错误示例

通常,它应该可以工作。

但是从技术上讲,这将是一个错误,因为我们的公式单元格引用处于循环中。

如何解决此问题:

首先,我们需要确定错误单元。

公式标签 →点击 错误检查 下拉箭头→将光标放在 单元格引用→ 现在您将看到错误范围

如何识别循环参考误差范围

其次,现在您知道了循环引用范围,转到每个错误单元并修复了公式。


#7错误地使用双引号– human error


我们经常使用 双引号 在编写公式时。

但它’了解何时使用和何时不使用非常重要。

例如,要将两个数字相加,我们可以使用如下公式。

=1234&5678 = 12345678

或者,要连接两个文本字符串,您可以’t要做同样的事情,您需要在双引号中指定文本。

=约翰&史密斯:错误的使用方式 [你会 最有可能的 看到#NAME错误]

=”约翰 “&”史密斯”=这是正确的方法。

在Excel公式中使用引号的正确方法

条件公式(如IF,AND,OR)也是如此。它’在双引号中指定文本非常重要。

否则,这是excel公式不起作用的原因之一。


#8 宝玛仕 –每次计算的基本规则


您是否知道excel中的每个计算都基于‘BODMAS’ rule?

哦等一下 宝玛仕 rule?

It’一个确定操作顺序的数学规则。

settingbox -BODMAS规则

好吧,让’s simplify further.

您能猜出下面的答案是什么吗?

10 + 10 * 2 =?

是那40岁,恭喜您错了。

您可以在Excel中尝试相同的计算吗?

您很可能会看到30,确实’s the correct answer… but how?

根据BODMAS规则,计算将基于以下顺序进行:即B-括号,O-顺序,D-除法,M-乘法,A-加法& S- subtraction.

因此,上式的顺序为→10 * 2 = 20→20 + 10 = 30。

如果不这样做,您的公式可能会出错’t consider 宝玛仕.

如何解决此问题:

It’非常简单,您只需要使用以下括号更改顺序即可。

=(30 + 40)* 2

因此,方括号中的任何内容都将首先计算,然后再进行下一个操作。


#9错误使用“absolute” referencing


使用Excel的每个人大多数都知道绝对引用是什么。

如果不, 请在这里停下来 并了解它。这是有关的快速教程 单元格引用.

在...的帮助下 绝对 referencing,您可以固定公式范围,以便可以从一个单元格复制到另一个单元格。

那’s indeed a very useful feature. 那 being said, if you 不要’如果不知道如何使用它,那么您的公式将无法正常使用。

当然,这是excel公式从一个单元格复制到另一个单元格时不起作用的主要原因之一。

绝对 referencing errors

例如,在上表中,我们使用了绝对单元格引用来简化公式的复制。

但是,我们犯了一个错误。

而不是使用部分冻结,我们使用了F4 快捷键 并固定整个范围。

那’确实导致错误的结果。

因此,正确的方法应该只对行使用绝对引用’s == SUM(D $ 3:D $ 6)。

如何解决此问题:

不幸的是,没有针对此类错误的快速修复。您必须通过分析所有公式手动进行操作。

但是,到目前为止,我发现的最好方法是对公式进行交叉检查&我使用绝对的结果&相对引用。

请让我知道您目前的解决方法。

提示:

  • 不要使用F4快捷键进行部分冻结
  • 尝试在部分范围内手动添加美元符号($)

#10不正确的公式参数


不正确的公式参数可能会对公式结果产生负面影响。

只是为了给您一些视角,让’s 下stand below vlookup语法 特别是最后一个 [范围查询]。

vlookup公式语法以及详细说明

范围查找参数有两个选项可供选择。

  1. 真正– 近似匹配
  2. 假– Exact 比赛

就我个人而言 feel using “Approximate 比赛” 选择就像自杀。

因为 近似匹配 永远不会返回准确的结果。

近似匹配导致的Vlookup问题

在上面的示例中,我们在单元格F3中添加了Vlookup公式以查找相应的收入金额。

如果您仔细观察,则客户ID 6378993的收入金额25k不正确。应该是65k。

那么,出了什么问题?

如前所述,我’ve used ‘Approximate 比赛’ 范围查询 论据。

“match” 是另一个公式,您可以选择类似的参数。

如何解决此问题:

永远不要使用‘近似匹配’ 在您的vlookup公式中。


现在它’s 您r Turn

您认为Excel公式无法正常工作还有其他原因吗?

或者,也许您有个问题要问。

无论哪种方式,请在下面我评论’会尽快回应。

列出了大量的Excel技巧和窍门文章,请加入我们的免费新闻通讯,直接将其放入收件箱

[唐’不用担心,我们讨厌垃圾邮件&我们尊重您的隐私].

加入我们的时事通讯

关于作者

Sudheer Tammini

你好,
我是“ Excel爱好者”和技术的早期采用者。我想学习新事物,并使用技术解决最棘手的业务问题(相信我,这很有趣)。除了运营此网站外,我还是一名全职员工,并拥有金融学硕士学位。

订阅我们的: 通讯
了解更多: 关于我