接上文-理解Excel中的数据类型(一)
Excel中的文本类型和数值类型
Excel中的文本类型非常简单,在理解了那些乱七八糟的“格式”都是数值之后,数值类型也不复杂。
再强调一遍,一般情况下,在Excel中你可以不关心数据类型。你只要输入就可以了:
你输入:123,Excel自动认识这是数值。
你输入:2022/1/1,Excel自动认识这是日期,然后计算并内部存储相应的数值,并且在单元格中显示日期。
你输入:5 1/2,Excel自动认识这是分数,然后计算并内部存储相应的数值,并且在单元格中显示分数。
你输入:其他的Excel不能翻译成给定格式的内容:比如“ABC”,Excel就将这个当作文本。
所以从这个意义上来说,所谓文本类型,就是所有不能被Excel识别为数值(包括日期等类型)和逻辑值的内容。
所以这两种类型本身没有太多需要介绍的。只有一个注意事项提醒初学者注意:那就是身份证号之类的全部由数字组成的内容:
110101200010018924
这样的身份证号实际上在输入时会自动变成科学计数法:
实际上只要输入的数字位数超过11位,就会变成科学计数法。
我们知道,科学计数法其实是数值,所以只要将单元格格式改为数值就可以了。
但是,这里有一个重点:Excel中的数值类型最多支持15位精度。
如果一个数值超过15位,那么15位之后的就被当作0了。
当然,一般情况下,15位的数字可以表示非常巨大的数值,远远超过我们对数字的使用需求。
但是,遇到身份证号这类情况就不行了。因为你输入的身份证号超过15位了,而Excel又自动识别为数值,所以最后3位就变成0了。即使你改成数值格式也于事无补了:
这种情况下,你是不可能找回后三位的。只能在输入身份证号之前将单元格格式设置为文本,或者在输入身份证号之前先输入一个英文的单引号:
然后再开始输入身份证号:
这样Excel就知道你接下来要输入的所有内容都是文本。
这个单引号会一直显示,但是不要担心,它并不是单元格内容的一部分。
注:数值类型的15位精度是包括小数位数的。
EXCEL中的逻辑值
逻辑值只有两个:TRUE,FALSE。全部大写。你可以在Excel中输入小写的true,Excel会自动变成大写的TRUE。
一般来说,我们很少在单元格中输入TRUE/FALSE,它们多数是函数或公式的返回值,比如,公式:
A1>=5
就返回逻辑值。如果A1中的值是10,返回值为TRUE。如果A1中的值是2,返回值为FALSE。
或者作为参数出现。比如IF函数中的条件,FILTER函数中的条件等。
作为逻辑值,是可以进行运算的。这些运算在Excel中是通过逻辑函数实现的:
逻辑值可以用数值代替
这些逻辑值本身可以像数值一样进行预算。
逻辑值与数值进行混合运算
在逻辑值与数值进行混合运算的时候,TRUE=1,FALSE=0
逻辑值与逻辑值进行加减乘除运算
在逻辑值之间进行加减乘除运算时,TRUE=1,FALSE=0
数值作为逻辑值
实际上数值可以当作逻辑值用。此时,0=FALSE,非0=TRUE。
在上图中,我们使用了公式:
=IF(B3,"A","B")
我们将B3用作了条件,所以就是将B3作为了逻辑值。从图中可以看到,所有的非0值都被当作TRUE。
将逻辑值转换为数值
一般来说,将逻辑值转换为数值有点多余。因为从上面的介绍中我们知道逻辑值可以当作数值使用。不过并不是在所有的情况下都是这样的。比如:
=SUMPRODUCT(B3:B7,B3:B7>3)
这个SUMPRODUCT公式中,计算B3:B7中所有大于3的数值之和。很明显,第二个参数B3:B7>3就是逻辑值。
但是这个公式并没有按照我们的期望得到正确的结果:
这是因为,SUMPRODUCT函数并不支持逻辑值。
我们可以使用一个函数:N进行转换:
N(TRUE)=1
N(FALSE)=0
=SUMPRODUCT(B3:B7,N(B3:B7>3))
就可以得到正确结果:
这个N函数可以通过一个运算:--代替(实际上相当于两个减号,负负为正):
=SUMPRODUCT(B3:B7,--(B3:B7>3))
错误值
错误值是一种单独的数据类型。Excel中的错误值都是由于公式计算产生的,
关于这些错误值,请参见:
Excel也提供了IFERRO函数,IFNA函数帮助我们处理错误值。
注:在单元格中直接输入错误值,比如:#N/A,Excel也会自动识别为错误值。
待续
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
联系客服