2.6　转换数字格式

在Excel中，数字有两种保存方式：纯数字和文本型数字。

数字有时是以文本形式保存的，必须转换为真正的数字才能进行数学及汇总计算。以文本型数字保存的各类编码，不允许进行数学及汇总计算，仅仅是用作分类。

对于数字的两种保存方式，需要针对不同的工作表做不同的处理。当数字作为文本形式保存到工作表中时，如果是手工输入，可以先把单元格格式设置为文本，再输入数字；或者先输入英文单引号“'”，再输入数字。

2.6.1　将数字转换为原位数的文本型数字

某列的数字编码位数相同，但有的是数字，有的是文本，现在需要把整列数据统一转换为文本格式。此时可以使用分列工具，即在文本分列向导的第3步中选中“文本”单选按钮，如图2-90所示。

图2-90　将纯数字转换为文本型数字

需要注意的是，不能使用将单元格格式设置为文本的方法来转换，因为这样仅仅是改变了单元格格式，并没有改变已经输入到单元格中的数字。

2.6.2　将数字转换为固定位数的文本型数字，位数不够就补零

某列的数字编码长度不一，现在需要把这些编码统一为固定位数，位数不够的在前面补足0。此时可以使用TEXT函数将图2-91中A列的数字编码统一为6位，在C2单元格中输入如下公式。

    =TEXT(A2,"000000")

利用TEXT函数转换完之后，采用选择性粘贴将转换后的结果复制到源数据的位置，然后删除含转换公式的C列。

图2-91　利用TEXT函数统一数字格式

2.6.3　将文本型数字转换为数值的技巧

如果遇到如下问题：表格中用SUM函数得到的结果是0，将系统导出的数据直接做数据透视表时结果都是计数，设置为求和也不行，这就需要仔细检查表格中的数据到底是不是数字了。

在某些系统导出的数据表格中，数字可能并不是数值，而是文本型数字，这样的数字无法使用函数进行求和汇总，需要将文本型数字转换为数值。

将文本型数字转换为数值的方法主要有5种，分别是利用智能标记、选择性粘贴、利用分列工具、利用VALUE函数、利用公式。

案例2-11　转换数字格式

下面是将文本型数字转换为数值的5种方法的简要描述，详细操作请观看视频。

1．利用智能标记

利用智能标记将文本型数字转换为数值的方法非常简单，首先选择要进行数据转换的单元格或单元格区域，单击单元格旁边的智能标记，在展开的下拉列表中选择“转换为数字”命令即可，如图2-92所示。

图2-92　利用智能标记将文本型数字转换为数值

利用智能标记的方法非常简单，但是只能用在有智能标记的场合。在有些情况下，并没有出现智能标记，这时就要采用其他方法。

利用智能标记转换的本质是循环选定区域中的每个单元格进行转换，比较耗时间，如果有20列10万行数据，要转换的单元格就多达200万个，使用这种方法可能出现死机的情况，此时可以使用选择性粘贴的方法。

2．选择性粘贴

这种方法也比较简单，适用性更广，具体的操作步骤如下。

步骤1 在某个空白单元格中输入数字1。

步骤2 复制这个数字1所在的单元格。

步骤3 选择要进行数据转换的单元格或单元格区域。

步骤4 打开“选择性粘贴”对话框，选中“数值”单选按钮，并选中“乘”或者“除”单选按钮，如图2-93所示，然后单击“确定”按钮完成设置。

图2-93　利用选择性粘贴将文本型数字转换为数值

当然，也可以先在空白单元格中输入数字0，然后就需要在“选择性粘贴”对话框中选中“加”或“减”单选按钮。

3．利用分列工具

一般情况下，从系统中导出的文本型数字都可以使用分列工具快速转换，具体方法是：选择某列，再选择“数据”→“数据工具”→“分列”命令，打开“文本分列向导–第1步，共3步”对话框，单击“完成”按钮即可。

使用这种方法，每次只能选择一列进行转换（因为分列工具的本意就是把一列分成几列，因此操作时只能选择一列）。如果有几十列文本型数字要进行转换，就需要执行几十次相同的操作，此时还是适合使用选择性粘贴的方法。

4．利用VALUE函数

假设B2单元格中是文本型数字，在C2单元格中输入公式“=VALUE(B2)”，即可将文本型数字转换为数值。

5．利用公式

Excel有一个计算规则，在对文本型数字进行加、减、乘、除运算时，就可以将文本型数字转换为数值。因此可以利用这个规则，使用公式对文本型数字进行转换。

假设B2单元格中是文本型数字，在C2单元格中输入如下公式，即可将文本型数字转换为数值。

    = 1*B2  乘以1
    = B2/1  除以1
    = --B2  两个负号，负负得正，相当于乘以1

2.6.4　容易混淆的概念：单元格和数据

在使用Excel时，绝大多数人都会混淆单元格和数据这两个概念。单元格是单元格，数据是数据，两者是截然不同的。单元格是容器，数据是容器中装的东西。

单元格可以设置为各种格式，但不论怎样设置，单元格中的数据永远是不变的。

通过“设置单元格格式”命令对单元格进行的任何设置，都是在改变单元格本身的格式，从而把单元格中的数据显示为不同的形式，但单元格中的数据仍然是原来的数据，并没有发生实质性的改变。也就是说，改动的是单元格，并没有改动单元格中的数据。

如图2-94所示的表格，就是通过设置单元格格式，突出显示超预算或预算内的差异和执行率，这里，“差异”列和“完成率”列中的数据都是使用公式计算得到的结果，但显示成带上/下角形标记的效果，这里是使用了自定义数字格式。

图2-94　设置单元格格式标识数据 mOoOBhJ6dYmwSceZX6FJ8AYY/mMP/qLXJHEy2fiEoytPnHTnIEAAXnT+rqEDd/W5