数据类型、各类型数据之间的计算与转换

1. 数据类型

EXCEL中主要涉及四种类型的数据：数值型、文本型、日期型和逻辑型。

默认状况下，EXCEL的单元格为常规型。这并不是说存在常规型的数据，而是指在常规型的单元格中录入数据时，EXCEL会“智能”地确定录入数据的数据类型。智能化是EXCEL发展的一个趋势，比如2013版之后增加的CTRL+E功能键。

数值型

EXCEL单元格格式中的分数、百分数、货币等，本质上都是数值型的，只是改变了显示格式而已。

文本型

文本可以是文字、数字和符号，注意空格、=号等也是文本哦。很多时候，我们需要将数字设置为文本型的，比如身份证号码、以0开头的编号等。先输入单引号再输入数字，可以快捷地实现数字以文本方式来保存。

逻辑型

只有两个值，TRUE和FALSE。

日期型

（1）日期型数据其实也是数字。在WINDOWS系统中，EXCEL中规定1900年1月1日为1，1900年1月2日为2，并由此建立了对应关系。但是，在WINDOWS系统下EXCEL只默认1999/1/1到9999/12/31是日期。超过这个范围的，EXCEL视其为文本。

（2）有些时候在作计算时，我们本来想获得日期型的计算结果，但显示的却是一个整数，这时就需要将单元格格式调整为日期型。从下图中我们可以看到，不单单是在单元格宽度不够时，单元格中才会显示为####。

（3）DATEDIF是一个常用的日期函数，但是键入该函数时，不会出现提示信息，也没有帮助。我们把EXCEL中这样的函数称为隐藏函数。那EXCEL为什么要设置隐藏函数呢？对DATEDIF而言，一种可能的解释是，该函数存在不好解决的BUG。见下图：

所以，在使用DATEDIF来计算年和月时，起点如果是闰年的2月29日、大月的31号，就要当心计算错误哦。也许，正是存在这样的争议性问题，而DATEDIF函数又很有用，EXCEL才将其隐藏起来的吧。

2. 各类数据的计算

数值型

（1）数值型数据与文本型数据之间进行连接运算时，数值型数据被自动转换为文本。比如，=2016&”版本”是”2016版本”。

（2）可以使用文本函数直接对数值型数字进行运算，这时数值型数据也被自动转换为文本。比如，=RIGHT(654321)的结果是1，相当于截取出个位数，但这个结果是文本型的数字哦。。

文本型

（1）文本之间的连接使用符号&。

（2）文本类型的数字，彼此之间做算数四则运算，或者与数值型的数字之间做四则运算时，文本数字自动转换为数值数字。比如，=”4”+”6”和=“4”+6的结果都是10。

（3）SUM函数只统计单元格区域中的数值（见下图）。所以在用SUM函数作总工资之类的统计时，一定要保证所有的工资都是数值型的数字。

（4）当使用MID、LEFT、REPLACE等文本函数对文本数据进行计算时，结果是文本。于是，

公式=IF(LEFT("600668",1)=6,1,0)的结果是0

公式=IF(LEFT("600668",1)=”6”,1,0)的结果才是1。

日期型

先看下面的动图：

输入1900/1/10，显示的是日期型的日期。输入=1900/1/10时，结果是190，原因是EXCEL把/号当作除号处理了。而输入=”1900/1/10”时，显示的日期则为文本型的日期。直观上看，当没有设置单元格的对齐方式时，日期型日期靠右，而文本型日期靠左。

也就是说，在我们平常使用的EXCEL表格中，实际上存在两种类型的日期数据。一种是日期型的，一种是文本型的。比如我们在使用身份证号码生产日期时，用DATE函数生成的是日期型的，而用TEXT函数生成的则是文本型的。

在EXCEL的智能处理之下，大多数时候，文本型的日期可以和日期型的日期一样使用。比如在使用YEAR等日期型函数时，EXCEL会把文本型日期当作日期型日期处理。

而在使用LEFT等文本函数的时候，EXCEL则把日期型日期先视为一个数，自动将其转换为文本型数字后，再进行计算。由此，我们就能够理解上面动图中LEFT的计算结果了。

值得指出的是，在使用数据透视时，文本型数据是不能创建组的。于是，当我们企图对文本型日期创建组时，就会弹出不能创建组的警示信息（见下图）。这种情况下，就需要将文本型的日期转换为日期型的日期。

逻辑型

逻辑型数据之间的计算，本来只有AND、OR和NOT函数三种。但是在EXCEL中，视TRUE为1，FALSE为0。于是，逻辑型数据之间就可以做算数四则运算了。比如，=TRUE*0的结果是0，=TRUE+FALSE的结果是1。不过正如前文已经指出的那样，SUM函数统计的是单元格中数值的总和。对逻辑型数据使用SUM函数时，同样遵守这一规则。

知道了EXCEL对于逻辑型数据的上述处理规则，我们就能够理解下面的数组公式了。

统计人数时，(C:C=”财务部”)数组运算的结果是一系列TRUE或FALSE，乘以1之后（加或减0，除以1也可以），就变成了1和0，然后SUM才能够计算出正确的结果。而统计总工资时，D2:D40本身就是数字，因此就不需要再乘以1了。

3. 数据类型转换

当不同类型的数据之间进行混合运算时，实际上就已经实现了类型的转换。在EXCEL中，这种转换往往是在计算时自动进行的，而且一般是朝我们希望的方向进行转换。比如根据身份证号码倒数第二位判断性别，MID函数取出来的实际上是文本型的数字，当使用MOD函数判断奇偶时，EXCEL就会自动地将文本型数字转换成为数值型数字。

在进行数据处理的时候，如果数据类型不能满足需要，就需要进行转换。

（1）文本型日期转日期型日期

前文曾经提到，在进行数据透视的时候，文本型的日期数据是不能创建组的。那么，如何将其转换为日期型数据呢？

除了使用乘除1和加减0，EXCEL还专门规定，可以用两个减号即--来实现转换。负负得正，两个减号的作用相当于乘以1。

划重点：使用--可以将文本型数字和逻辑型数据，转换为数值型的数字。

（2）文本型数字转数值型数字

当我们从数据库中将数据导出到EXCEL中时，数字有可能是文本型的（表现为单元格的左上角有小三角）。而且，对这类带小三角的文本型数字，通过改变单元格格式是不能实现类型转换的。

有效的转换方法，除了使用算数四则运算，以及使用--符号，还可以通过鼠标操作来进行（见下面的动图）。

把全部带小三角的文本型数字选中后（选中第一行文本型数字，再使用组合键CTR+SHIFT+↓），然后点开感叹号，就可以实现全部转换了。

总结

为了方便使用，降低使用者的门槛，EXCEL在处理各种类型的数据时，常常自动完成不同类型数据之间的转换。但是，这种“智能”有时候也会给我们带来麻烦。深入了解EXCEL的主要数据类型，以及它们之间做计算、作转换时的规则，对于用好EXCEL是很有必要的！

传播计算机知识，我们的责任！