为什么数字与“空”单元格相加出错 如图 15‑1所示,在计算退休年龄时,C列根据B列的性别判断延迟年数,女性职员不延迟显示为“空”,公式为“=IF(B3='男',5,'')”,为什么D5单元格=C5+50得到#VALUE!错误? 图15‑1 数字与“空”相加得到#VALUE!错误 → 解决方案: 了解空文本与空单元格的区别,使用N函数将空文本转为0参与加法计算或使用SUM函数求和。 → 操作方法 在D3单元格输入公式1或公式2并向下复制: 公式1 =50+N(C3) 公式2 =SUM(C3,50) → 原理分析 C5单元格得到的计算结果为'',即“空文本”而非“空单元格”,而D5单元格的公式需要使用数字相加,空文本没有转换为正确的数据类型,所以返回#VALUE!错误。 使用N函数,可以将文本转换为0,数值返回其本身的值;使用SUM函数,可以忽略文本求和。因而可以计算正确结果。 SUM函数语法为: SUM(number1,[number2],...]) 该函数可以有255个参数number,第2个至第255个参数为可选参数。如果参数是一个数组或引用,则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。如果任意参数为错误值或为不能转换为数字的文本,Excel 将会显示错误。 → 知识扩展 空文本与空单元格的区别 文本在Excel中的表示方法是用一对半角双引号包含,空文本是指双引号''中间什么内容也没用的文本,其字符长度为0。在公式中,经常使用空文本作为一个计算结果以代替用户不希望出现的FALSE、0等结果,例如本案例中,不想显示女性职工延迟年数为0,用了空文本''可以达到目的。 空单元格是指一个单元格中未曾输入任何数据,或者数据已被清空。 因为设置公式时,经常用类似A1=''来判断A1单元格是否为没有数据,初学者容易混淆''与空单元格。事实上两者不是在什么情况下都划等号的,如表格 15‑1所示,A5、A7单元格中输入=''来返回空文本,A3、A4、A6都是空单元格,在B列输入几个公式后,可以从返回的不同结果说明空文本与空单元格的区别。 表格15‑1 空文本与空单元格的区别
此外,“空格”也是经常与空文本、空单元格混淆的一个概念,空格是指按下键盘上的 公式中的&''有什么作用 如表格 15‑1所示,空单元格在参与公式运算时,默认返回0,但与文本进行比较、合并运算时则视为空文本。利用这个特性,在较为复杂的查找引用公式中,引用一个空单元格并与空文本合并,返回空文本,以此屏蔽引用空单元格返回的0,从而减少条件判断而缩短公式。 如图 15‑2所示,要求列出A列姓名为张三的对应的B列的订单号。 图15‑2 公式中&''的妙用 在D3单元格输入公式1并按下 =IF(ROW(1:1)>COUNTIF(A:A,'张三'),'',INDEX(B:B,SMALL(IF(A$3:A$10='张三',ROW($3:$10)),ROW(1:1)))) 或者用同样的方法输入公式2: =INDEX(B:B,SMALL(IF(A$3:A$10='张三',ROW($3:$10),2^20),ROW(1:1)))&'' 其中,公式2对不满足IF条件的返回2^20(即Excel 2010的最大行数1048576),因而当罗列完满足条件的记录之后,全部引用B列最后一行空单元格,并与空文本进行合并得到空文本,比公式1更简洁实用。 注意:使用&''后公式计算结果为文本。 版权所有 转载须经Excel技巧网许可 【 Excel 】:Countif,必会的基础函数! |
|