在Excel中,数字有两种保存方式:纯数字和文本型数字。
数字有时是以文本形式保存的,必须转换为真正的数字才能进行数学及汇总计算。以文本型数字保存的各类编码,不允许进行数学及汇总计算,仅仅是用作分类。
对于数字的两种保存方式,需要针对不同的工作表做不同的处理。当数字作为文本形式保存到工作表中时,如果是手工输入,可以先把单元格格式设置为文本,再输入数字;或者先输入英文单引号“'”,再输入数字。
某列的数字编码位数相同,但有的是数字,有的是文本,现在需要把整列数据统一转换为文本格式。此时可以使用分列工具,即在文本分列向导的第3步中选中“文本”单选按钮,如图2-90所示。
图2-90 将纯数字转换为文本型数字
需要注意的是,不能使用将单元格格式设置为文本的方法来转换,因为这样仅仅是改变了单元格格式,并没有改变已经输入到单元格中的数字。
某列的数字编码长度不一,现在需要把这些编码统一为固定位数,位数不够的在前面补足0。此时可以使用TEXT函数将图2-91中A列的数字编码统一为6位,在C2单元格中输入如下公式。
=TEXT(A2,"000000")
利用TEXT函数转换完之后,采用选择性粘贴将转换后的结果复制到源数据的位置,然后删除含转换公式的C列。
图2-91 利用TEXT函数统一数字格式
如果遇到如下问题:表格中用SUM函数得到的结果是0,将系统导出的数据直接做数据透视表时结果都是计数,设置为求和也不行,这就需要仔细检查表格中的数据到底是不是数字了。
在某些系统导出的数据表格中,数字可能并不是数值,而是文本型数字,这样的数字无法使用函数进行求和汇总,需要将文本型数字转换为数值。
将文本型数字转换为数值的方法主要有5种,分别是利用智能标记、选择性粘贴、利用分列工具、利用VALUE函数、利用公式。
下面是将文本型数字转换为数值的5种方法的简要描述,详细操作请观看视频。
利用智能标记将文本型数字转换为数值的方法非常简单,首先选择要进行数据转换的单元格或单元格区域,单击单元格旁边的智能标记,在展开的下拉列表中选择“转换为数字”命令即可,如图2-92所示。
图2-92 利用智能标记将文本型数字转换为数值
利用智能标记的方法非常简单,但是只能用在有智能标记的场合。在有些情况下,并没有出现智能标记,这时就要采用其他方法。
利用智能标记转换的本质是循环选定区域中的每个单元格进行转换,比较耗时间,如果有20列10万行数据,要转换的单元格就多达200万个,使用这种方法可能出现死机的情况,此时可以使用选择性粘贴的方法。
这种方法也比较简单,适用性更广,具体的操作步骤如下。
步骤1 在某个空白单元格中输入数字1。
步骤2 复制这个数字1所在的单元格。
步骤3 选择要进行数据转换的单元格或单元格区域。
步骤4 打开“选择性粘贴”对话框,选中“数值”单选按钮,并选中“乘”或者“除”单选按钮,如图2-93所示,然后单击“确定”按钮完成设置。
图2-93 利用选择性粘贴将文本型数字转换为数值
当然,也可以先在空白单元格中输入数字0,然后就需要在“选择性粘贴”对话框中选中“加”或“减”单选按钮。
一般情况下,从系统中导出的文本型数字都可以使用分列工具快速转换,具体方法是:选择某列,再选择“数据”→“数据工具”→“分列”命令,打开“文本分列向导–第1步,共3步”对话框,单击“完成”按钮即可。
使用这种方法,每次只能选择一列进行转换(因为分列工具的本意就是把一列分成几列,因此操作时只能选择一列)。如果有几十列文本型数字要进行转换,就需要执行几十次相同的操作,此时还是适合使用选择性粘贴的方法。
假设B2单元格中是文本型数字,在C2单元格中输入公式“=VALUE(B2)”,即可将文本型数字转换为数值。
Excel有一个计算规则,在对文本型数字进行加、减、乘、除运算时,就可以将文本型数字转换为数值。因此可以利用这个规则,使用公式对文本型数字进行转换。
假设B2单元格中是文本型数字,在C2单元格中输入如下公式,即可将文本型数字转换为数值。
= 1*B2 乘以1 = B2/1 除以1 = --B2 两个负号,负负得正,相当于乘以1
在使用Excel时,绝大多数人都会混淆单元格和数据这两个概念。单元格是单元格,数据是数据,两者是截然不同的。单元格是容器,数据是容器中装的东西。
单元格可以设置为各种格式,但不论怎样设置,单元格中的数据永远是不变的。
通过“设置单元格格式”命令对单元格进行的任何设置,都是在改变单元格本身的格式,从而把单元格中的数据显示为不同的形式,但单元格中的数据仍然是原来的数据,并没有发生实质性的改变。也就是说,改动的是单元格,并没有改动单元格中的数据。
如图2-94所示的表格,就是通过设置单元格格式,突出显示超预算或预算内的差异和执行率,这里,“差异”列和“完成率”列中的数据都是使用公式计算得到的结果,但显示成带上/下角形标记的效果,这里是使用了自定义数字格式。
图2-94 设置单元格格式标识数据