excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)

为何你的Excel公式总是返回乱码?弄清这8种出错原因,让乱码从此不敢出来捣乱!

小伙伴们在Excel中编写公式时,是不是会经常因操作不慎导致公式运算返回五花八门的“乱码”,如“#DIV/0!”“#REF!”“#VALUE!”等,相信你对这些“乱码”既觉得眼熟,也感到头痛。由于大多数人只知其然,却不知其所以然,只知道这是公式出错,Excel 也会根据返回的错误值简要提示出错原因,但从未深究到底错在哪里,所以只会盲目地反复测试、修改公式,直到正确为止。

如此一来,不仅影响工作效率,也让很多人对于学好 Excel 函数公式的信心受到不同程度的打击。其实,只要预先对这些公式错误值的含义及发生原因进行一个全面、正确的认识和了解,就可以在编写公式时有效避免出错,即使偶尔出现乱码,也能够迅速查清出错根源,对症下药,及时纠正错误。

一般情况下,Excel公式返回的最常见的错误值主要包括8种,如下图所示。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(1)

#N系列错误值

#N/A、#NAME?、#NUM!、#NULL! 这四种错误值的拼写均以“#N”开头,因此我们将其划分为一组,命名为“#N 系列错误值”进行介绍,便于读者记忆和理解。公式返回这四种错误值的主要原因如下图所示。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(2)

1.#N/A 错误值

公式中引用的某个单元格中的数值对函数或公式不可用时,即会返回“#N/A”错误值。这种错值通常会出现在查找引用类函数公式中。例如,在<库存汇总表>中,L2:N4单元格区域是为查询指定商品的相关信息而添加的辅助表格。其中,M2单元格中的公式“=VLOOKUP($L2,A:I,9,0)”是指根据L2单元格中的数值查找A:I区域里A列中与之完全相同的数值,并返回第9列(期末结存数量)中与之匹配的数值。N2单元格中的公式同理查找引用期末结存金额。

由于L2单元格为空,公式无法查找到匹配的数据,因此M2和N2单元格都返回了“#N/A”错误值。L3单元格输入的存货名称错误,同样返回这种错值。而L4单元格中的存货名称正确,公式即会返回正确结果,如下图所示。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(3)

2.#NAME? 错误值

#NAME? 错误值名称中包含英文单词“NAME”,其中文意思是“名字”“名称”等,顾名思义,主要原因就是公式中的某些名称出现错误。例如,函数名称、引用的单元格或单元格区域的“名称”、定义的名称出错,或者公式中引用了文本,但未添加双引号,都会返回 #NAME? 错误值。如下图所示,我们在“库存汇总表 1”中添加辅助表格,对于公式返回 #NAME? 错误值的公式表达式、返回结果及出错原因分析与正确公式进行对比,以便让大家更清晰地了解出错原因。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(4)

3.#NUM! 错误值

#NUM! 错误值也可从其名称中理解出错原因,“NUM”是“Number”的缩写,代表数字。如果函数公式中包含无效数字或数值,就会返回 #NUM! 错误值。例如,SQRT 是一个非负实数的平方根函数,要求参数不能为负数,否则返回错误值,如左下图所示。

如果数字的长度超过 Excel 限定的最大长度,也会返回 #NUM! 错误值。例如,POWER 函数的作用是返回指定数字的乘幂,如右下图所示,运算结果远远超出 Excel 限定的数字长度,因此返回 #NUM! 错误值。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(5)

4.#NULL! 错误值

#NULL! 错误值名称中的“NULL”代表零值、空值。如果公式中使用了不正确的运算符或引用的单元格区域的交集为空,就会返回 #NULL! 错误值。

例如,单个空格如果运用在公式中,就作为引用运算符,表示两个区域之间的交集部分。如下图所示,公式“=SUM(A1:A5 C1:C5)”引用了 A1:A5 和 C1:C5 单元格区域,二者之间没有交集, 因此返回了 #NULL! 错误值。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(6)

#DRV系列错误值

同样为了方便大家记忆和理解,我们将另外四种错误值 #####、DIV/0!、#REF!、#VALUE! 名称中第一个符号或字母合并称为“#DRV 系列错误值”。公式返回这四种错误值的主要原因如下图所示。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(7)

1.##### 错误值

如果 Excel 中出现了 ##### 错误值,主要从以下两方面追查原因。

(1)单元格列宽小于其中数字或文本的长度。

从严格意义上讲,这并不算是一种错误,是因为列宽不够导致的,如左下图所示。可以通过以下三种方式解决:① 手动调整列宽;② 在【设置单元格格式】对话框中的【对齐】选项卡中选中【缩小字体填充】复选框,如中下图所示;③ 单击【开始】选项卡【单元格】组中的【格式】按钮,在弹出的下拉列表中选择【自 动调整列宽】选项即可,如右下图所示。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(8)

(2)日期与时间的公式、数值或格式不正确。

正确逻辑下,日期与时间数值不能为负数。所以,如果在 Excel 中的日期格式单元格中输入了负数,则会返回长串的 #####……错误值。如左下图所示,C1 单元格公式“=A1-B1”计算 2019 年 9 月1 日的前两天的日期,但计算结果为负数,所以返回 ##### 错误值。正确的公式如右下图所示。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(9)

如果计算两个日期的间隔天数,当结果为负数,单元格格式为日期格式时,也会返回 ##### 错误值,如左下图所示。此时将单元格格式调整为“常规”格式即可解决,效果如右下图所示。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(10)

2.#DIV/0! 错误值

#DIV/0! 错误值名称中的“DIV/0”的意思是“除以零”。数字 0 不能作为除数,这是众所周知的一个数学知识,Excel 中自然也不会例外,如果公式中把空白单元格或单元格中的数字 0 作为除数,就会返回 #DIV/0! 错误值,如左下图和右下图所示。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(11)

3.#REF! 错误值

Excel 中的公式结果出现 #REF! 错误值的原因通常是删除了公式引用的单元格或单元格区域。如左下图所示,C1 单元格中设置了正确的公式。删除 B1 单元格后,原公式引用的单元格已不存在,所以返回 #REF! 错误值,如右下图所示。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(12)

4.#VALUE! 错误值

#VALUE! 错误值主要是由于引用的单元格的数值类型不一致导致的。如下图所示,A1 单元格中的数值为数字类型,B1 单元格中的符号“-”为文本类型,C1 单元格引用前两个类型不同的单元格数值进行运算,所以返回了 #VALUE! 错误值。

excel运用公式后往下拉变乱码(为何Excel公式总是返回乱码)(13)

小伙伴们掌握了以上以上公式中的8种常见错误值及其出错原因,相信这些“乱码”从此消声匿迹,再也不敢出来捣乱!

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页