Excel是一个“弱”类型的应用程序,这就意味着在Excel中,数据类型其实没有那么重要。微软将各种因素考虑得很好,让使用者在不关心数据类型的状况下,也可以很好地使用Excel的各种功能。
不过,要想更好地掌握Excel,还是应该详细了解一下这一方面的各种概念。
区分Excel中的单元格格式和数据类型
很多人认为Excel中有很多数据类型,包括:数值,货币,会计,日期和时间,逻辑值等等。
之所以会这么认为,是受了单元格格式的影响:
其实,在这个对话框中设置的是单元格格式,并不是数据类型。
设置单元格格式意味着你改变的只是显示方式,实际上该单元格的值并没有改变。
有些朋友在说明这个问题的时候会举例说,你将一个数字的格式从0.5改成分数1/2,改变的只是单元格的显示值,在公式栏中存的是它的真实的值:
但是,这也不一定。比如,将单元格格式修改为百分比,
尽管公式栏中也显示50%。但是这个单元格的数值其实仍然是0.5。
使用TYPE函数返回数据类型
要了解一个单元格的数据类型,可以使用TYPE函数。
TYPE函数的语法特别简单:
TYPE(数值)。
其中,
数值 => 指需要进行判断的数值。
这个函数只能返回6个值,代表了6种数据类型:
1 => 数字类型
2 => 文本类型
4 => 逻辑类型
16 => 错误值
64 => 数组
128 => 复合类型
我们来看一下,不同格式的数据使用TYPE函数后的返回值:
可以看到,在所有的单元格格式中,是由数字格式和文本格式真正对应到了数据类型上,其余的格式跟数据类型都不一致。
这里,百分比,分数,货币,会计,日期,时间,科学计数法格式的数据实质上都是数字,这比较容易理解。
需要特殊说明的有两个地方,一个是第14行,那么长的一个非常像文本,但是实际上是数字,它用的就是特殊格式中的中文小写数字:
另一个就是日期和时间。
Excel中的日期和时间
从上一节的图中可以看到,日期格式和时间格式的数据的真实数据类型都是数值。(这一点对于有一定经验的Excel使用者来说都是常识了。所以,如果你对这一部分很熟悉,就可以略过这一节。)
Excel中对日期的处理是这样的:
日期在Excel中被存储为数字,而且是整数,一天就是数字1。
系统的第一天,是1900/1/1。
每过一天,数字就加1。所以,如果输入一个日期:2022/1/1,Excel中存储的数字实际上就是从1900/1/1开始的天数:44562。
两种日期系统
实际上,在Excel中支持两种日期系统:
1900系统
1904系统
1900系统是指日期从19001/1开始。缺省的Windows版本的Excel就是采用这个系统。
1904系统是指日期从1904/1/1开始。这主要用在以前的苹果系统中。不过现在苹果系统的Excel缺省也是采用1900系统。
如果你的文件从1900系统传到1904系统中(或者反过来),很多日期计算都会出错。你可以将你的Excel日期系统进行重新设置,这样计算就没有问题了。
设置方法是在文件->选项->高级中,勾选或者去掉使用1904日期系统选项
还是回来接着说1900系统吧。
Excel支持的日期格式主要是“/”分隔或“-”分隔:
2022/1/1
2022-1-1
都可以作为日期输入,Excel都认识,但是显示只用其中一种。到底采用哪一种格式显示,是在电脑系统的区域设置中的配置决定的。
但是,不是所有的用/或者-分隔的类日期格式都会被识别,例如:
尽管2022/5/0非常像个日期,但是Excel没有办法识别,所以,只能作为文本存储。
类似的还有:2022/6/31,1899/12/31。
前者是因为6月没有31号,而后者是因为1899/12/31已经早于Excel中的第一天:1900/1/1。
不过有一个例外:1900/1/0。
非常出乎意外的是,这竟然被Excel识别为日期:
当然,你不能直接输入:1900/1/0。你需要在单元格中输入0,然后修改单元格格式为日期。
记住这一个特例,有一些非常莫名其妙的跟日期相关的错误就是它引起的。
因为日期实际上是数值,所以可以直接进行加减运算。这个就不多说了。
再回头说时间。
时间也是以数值的形式存储的。不过时间值在1天之内变化。因为1天=整数1,所以,1天按分为24等份,每小时代表1/24,
进一步,每分钟=1/(24*60)=1/1440。
每秒=1/(24*60*60)=1/86400。
这样,数值1.5,就代表1900/1/1的中午12点,如果改为日期和时间格式的话,就是下面的结果:
内置自定义格式实现特定数字格式
现在我们知道,在设置单元格格式对话框中可以设置的数字格式:包括货币,会计专用,日期,时间,百分比,分数,科学记数,特殊等,实质都是数值格式。
在设置单元格格式对话框中仔细观察,就会发现,Excel实际上是通过内置的自定义格式来实现上述格式的。
比如,我们看一个日期的格式:
打开对话框时,一定是这样的。
如果你接下来点击“自定义”:
你会发现,实际上这个格式就是内置的自定义格式:
[$-en-US]yyyy/m/d h:mm AM/PM;@
每个Excel文件在创建时都内置了很多自定义格式,这些自定义格式就是用来帮助Excel实现类似日期,时间,分数等等数值格式的。
待续
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
联系客服