购买
下载掌阅APP,畅读海量书库
立即打开
畅读海量书库
扫码下载掌阅APP

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 设置单元格格式标识数据 ZvjS4y2b912xog13KqZw7YmB2nErqPybb+scRqNgZtQgJGV2d5QvFtuP5NO79H83

点击中间区域
呼出菜单
上一章
目录
下一章
×