“Oh My God” Excel公式在我的报告中不起作用。
听起来很熟悉吧?
如果是这样, 不要’t worry,您只是经常遇到此问题的许多excel用户之一。
考虑一下。
您 已经使用各种逻辑和相当复杂的公式为销售预测建立了一个excel模型。
[‘IF’具有64个条件的公式– 开玩笑]
当您尝试使用该模型时,您会看到一些公式不起作用或看起来很破损。
基本上,这里可以发生两件事:
一 您可能想大声说些什么[我不要使用这个词]。
第二,则需要付出更多的努力并修复所有公式。
因此,为避免所有麻烦,我们撰写了这篇深入的文章,涵盖了excel公式无法正常工作的大多数原因。
以及如何修复它们。
Excel公式不起作用:为什么& How to fix them
根据华盛顿邮报 关于Excel错误的文章, “这位澳大利亚研究人员发现,这些论文中大约有五分之一包含其基因列表中的错误,这是由于Excel自动将基因名称转换为日历日期或随机数之类的结果所致。”
最初的研究是由 期刊基因组生物学 发现在许多错误方面,呈逐年上升趋势。
你可以下载 完整的研究材料 可以从SourceForge网站(开放源代码)中进一步阅读。
上面的研究论文可能没有直接链接到在excel中不起作用的公式,但是这里有一点:
建造得很好&经过测试的软件(例如MS Excel)在理解我们的数据方面可能会出错 你呢& me?
所以,这是我们的十大理由 [让聚会开始]
#1计算选项[Excel公式未更新的最可能原因]
将计算选项更改为‘手册‘是最佳做法,但使用时需要非常小心。
默认情况下,“计算”选项将设置为 自动 在Excel中。
但是在某些情况下,您可能已经将其转换为‘m每年的‘加快流程。
如果您启用此选项‘上‘那么您的公式将不会更新 自动地,这反过来又可以提高电子表格的处理速度。
完成后,您需要点击 救 要么 Refresh workbook 重新计算。如果你没有’这样做会导致结果不正确。
如何解决此问题:
转到公式→计算→单击立即计算/计算表选项。
您也可以使用F9快捷键来刷新工作簿。
这是我们的视频指南 Excel计算选项.
提示:
- 养成按Ctl + S的习惯[将强制执行计算]
- 除非每个文件都有大量公式,否则不要在每个工作簿上都使用此选项
- 此选项是特定于工作簿的,因此您需要在要使用的每个工作簿中打开/关闭
#2 Excel公式不只显示公式就显示[显示公式]
显示公式是excel中的一个选项,只需单击一次即可显示所有工作簿公式。
该选项对于查找工作表中的所有公式非常有用。
话虽如此,我们在使用时需要小心一点 显示公式选项,否则,您可能会得到如下所示的结果。
您能看到“今天”公式显示为公式而不是显示实际日期吗?
Randbetween公式也是如此。
如何解决此问题:
It’s very simple:
转到公式→在公式审核下→单击“Show Formulas”(随着第一次点击的公式出现,第二次点击它们将隐藏在单元格后面并开始工作)
[请参考上面的截图]
另请阅读: 51个最佳Excel技巧和窍门
#3额外空间令人头疼
您一直在使用CSV(逗号分隔值)格式提取数据,然后’将数据大部分暴露在不需要的空间中。
您知道使用Vlookup公式时多余的空格会引起很多问题吗?
有时候’很难识别这些空间。
例如,让’s表示如果您要使用员工姓名作为基础进行Vlookup,那么您的员工姓名如下所示
你注意到了吗?
尽管这两个名称看起来都与我们的肉眼相似,但第二个名称中有一个空格。
这将不符合Vlookup公式中的完全匹配条件,因此导致 #N /A error.
如何解决此问题:
多余的空间问题可以使用解决 修剪公式.
通常,Trim公式会在单词之间找出多个空格并将其删除。
提示:
- It’总是好主意 修剪公式 在两个表中的Vlookup之前(查找& data table)
- 修剪功能将忽略字母之间的空格(仅适用于单词)
#4摆脱不可打印/隐藏的字符
与前导空格类似,不可打印字符也是excel公式不起作用的原因之一。
如果您有包含这些字符的数据,’最好先将它们取下再继续工作。
如何解决此问题:
与修剪类似,您可以使用CLEAN公式删除这些字符。
[请参考下面的例子]
#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错误]
=”约翰 “&”史密斯”=这是正确的方法。
条件公式(如IF,AND,OR)也是如此。它’在双引号中指定文本非常重要。
否则,这是excel公式不起作用的原因之一。
#8 宝玛仕 –每次计算的基本规则
您是否知道excel中的每个计算都基于‘BODMAS’ rule?
哦等一下 宝玛仕 rule?
It’用于确定操作顺序的数学规则。
好吧,让’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公式从一个单元格复制到另一个单元格时不起作用的主要原因之一。
例如,在上表中,我们使用绝对单元格引用来简化公式的复制。
但是,我们犯了一个错误。
而不是使用部分冻结,我们使用了F4 快捷键 并固定整个范围。
那’确实导致错误的结果。
因此,正确的方法应该只对行使用绝对引用’s == SUM(D $ 3:D $ 6)。
如何解决此问题:
不幸的是,没有针对此类错误的快速修复。您必须通过分析所有公式手动进行操作。
但是,到目前为止,我发现的最好方法是对公式进行交叉检查&我使用绝对的结果&相对引用。
请让我知道您目前的解决方法。
提示:
- 不要使用F4快捷键进行部分冻结
- 尝试在部分范围内手动添加美元符号($)
#10不正确的公式参数
不正确的公式参数可能会对公式结果产生负面影响。
只是为了给您一些视角,让’s 下stand below vlookup语法 特别是最后一个 [范围查询]。
范围查找参数有两个选项可供选择。
- 真正– 近似匹配
- 假– Exact 比赛
就我个人而言 feel using “Approximate 比赛” 选择就像自杀。
因为 近似匹配 永远不会返回准确的结果。
在上面的示例中,我们在单元格F3中添加了Vlookup公式以找出相应的收入金额。
如果您仔细观察,则客户ID 6378993的收入金额25k不正确。应该是65k。
那么,出了什么问题?
如前所述,我’ve used ‘Approximate 比赛’ 下 范围查询 论据。
的 “match” 是另一个公式,您可以选择类似的参数。
如何解决此问题:
永远不要使用‘近似匹配’ 在您的vlookup公式中。
现在它’s 您r Turn
您认为Excel公式无法正常工作还有其他原因吗?
或者,也许您有个问题要问。
无论哪种方式,请在下面我评论’会尽快回应。
列出了大量的Excel技巧和窍门文章,请加入我们的免费新闻通讯,直接将其放入收件箱
[唐’不用担心,我们讨厌垃圾邮件&我们尊重您的隐私].